Solve with steps in datastage

employee_id, salary
--------------------------
10, 1000
20, 2000
30, 3000
40, 5000

Display as

employee_id, salary, cumulative_sum
-----------------------------------
10, 1000, 1000
20, 2000, 3000
30, ...

varun negi

• Dec 8th, 2015

* You can have a single stage variable svSum with initial value = 0.
* And keep the derivation as svSum = svSum + Salary

dstage_user

• Nov 13th, 2015

You can use following steps :-
1. Create 2 stage variables
a. stgvar1=> salary+ stgvar2
b. stgvar2=> stgvar1
Note :- Set initial value of stgvar2=0.

Assign stgvar2 to cumulative_sum column.

Answer Question  Select Best Answer

You are given 2 jobs say job A and Job B with parameters x and y respectively. You need to create a sequence job. If you pass parameter x then Job A should run, If you pass parameter y then Job B should run, if you dont pass any parameter then Both Job A & B should run

Bharath

• Apr 3rd, 2016

create a sequence job. User_var_act ----> Nested_Condition_Activity----> In Nested condition Activity define two parameters with the values for example JOB1 and JOB2. Link the nested condition ac...

Rohit

• Sep 24th, 2015

This can be done by the use of user variable and nested stage.Define a user variable with the arguments as parameters x and y. After his pass this variable to a nested activity stage. Generate 3 outpu...

Answer Question  Select Best Answer

What is the process to split the large datastage job into smaller jobs?

sai3689

 Profile Answers by sai3689 Questions by sai3689

• Nov 18th, 2015

Thanks Rohit

Rohit

• Oct 16th, 2015

If there is a job having more no of stages say around 20 stages. Then generate the output to an intermediate file like a dataset using the first 10 stages. After generating this use this intermediate dataset to comibine with the remaining stages and then generate the final file

Answer Question  Select Best Answer

I need help validating rows. 1st. I have a sequential file that has 139 rows, 2 columns (TABLENAME, CLOB). Of those rows, two (in TABLENAME column) are named "HEADER" and "TRAILER". I do not want these in my output. How do I keep the 137 rows and cut off the HEADER & TRAILER. 2nd. In the TRAILER row (TABLENAME column), there is a number that reads 00000000137 (in the CLOB column), which is the 137...

Mahesh R

• Nov 18th, 2015

You can use constraints facility in transformer stage, to cut-off rows with HEADER and TRAILER. If you mention the column name with IN condition the rows will get dropped off.

Rudra

• Nov 17th, 2015

One thing is not clear from you question i.e. the record count will be written on 138th record I think. As part of sequential stage ignore the 1st line as header. Use a shell script to get the record ...

Answer Question  Select Best Answer

I have a file structure that I need to add things to and it is as follows:

TABLENAME
PROV_TB
ADDR_TB
ADDR_TB
AFFL_TB
TAX_TB
ETAX_TB
EPAY_TB
PROV_TB

I need to know how to create a sequence number the tables in between the PROV_TB. The PROV_TB has been assigned a number I created (153365000 is the 1st, 153365001 is the...

Ram

• Mar 31st, 2016

Venkat@ How you will get seq 1 with algorithm for 2 partitions and seq 1,2 for 3 partitions
@INROWNUM * @NUMPARTITIONS + @PARTITIONNUM
can you please explain...?

venkat

• Mar 28th, 2016

Syntax is:
@INROWNUM * @NUMPARTITIONS + @PARTITIONNUM
or
Surrogatekey()

Answer Question  Select Best Answer

Given a file with 1000 records, design a job which will abort if it exceeds the record count more than 100

Rajendhar Reddy kuthuru

• Feb 29th, 2016

Use the transformer options Abort after rows by using the Following Logic. Use a stage variable to count the number of rejected records. Create a link in the transformer with the Constraint that th...

Rajendhar Reddy Kuthuru

• Feb 29th, 2016

Source->Transformer->Target

In transformer enable option When "Abort After Rows" is set in a Transformer, the job aborts when the threshold is reached, however no row output is sent downstream.

Keep the peek stage to write the reason for failure in job log

Answer Question  Select Best Answer

I have 5 records in student table. I want maximum score student details. How to do this with transformer stage.
Example:Input::: Student table
Sno smarks
1 50
2 45
3 34
4 50
5 33
I want output like this::
sno smarks
1 50
4 50

dhanu

• Apr 30th, 2016

Use a sorter transformation(sort -desc), add a sequence generator(nxt val=1).

srinivas

• Apr 15th, 2016

We can implement this scenario in transformer stage, for that create three stage variables.

Step1: In transformer stage properties sort the smarks field as descending order.

Step2: Create three stage variables sv1,sv2,sv3
In Sv1: derivation map the smarks column
In Sv2: Sv1

Answer Question  Select Best Answer

We are using Bulk Collect methodology in Database to load the bunch of records at a time.How we will implement the same operation in Datastage. If my source having millions of records, I want to load the bunch of records in to target at a time. How?

Sri

• Jul 20th, 2016

Increase the array size in Target Table stage. Also use Parallel Hints while selecting the records.

Pavan Kumar

• Jul 8th, 2016

You can load the bunch of files at a same time using sequential file stage. In options just select the file location the DB and change the option in the seq file stage to "File Pattern".

Answer Question  Select Best Answer

I have faced an interview question recently.

In database we are using Bulk Collect concept to load the bunch of records ata time.

How we will achieve the same process in Datastage ?

Can we use any paticular stage or any other methodology we can implement.

Can any one help me on this.

Thanks in advance.
Aloka

Avni

• Jun 10th, 2016

Bulk collect is retrieving multiple rows. With a single fetch.. Datastage is by default working in parellel and processing multiple rows at a time. As per my knowledge there is no such specific way to implement this

Deepak Sharma

• Jun 9th, 2016

I think your question is about bulk load of data. I have used Bulk Load method in Oracle Connector stage. Setting Bulk load property ensures that data gets loaded in the bulk load mode.

Answer Question  Select Best Answer

In join stage how we will store the rejected record in one file?

Aloka

 Profile Answers by Aloka Questions by Aloka

• Jul 1st, 2016

Thanks Ram !

Ram

• Jun 30th, 2016

Hi Aloka,
Join stage doesnt support reject link. so just connect join stage --> Transformer stage and provide constraints on Transformer to get rejects in Join stage.

Answer Question  Select Best Answer

In Data warehouse which table will load first and why? Fact or Dimension?

Sri

• Jul 20th, 2016

Dimension table will load first and then fact tables.

Ram

• Jun 30th, 2016

Hi Aloka,
Fact table is connect with different dimension tables with foreign key relationship. Fact table having only Ids & Measures. Dimension tables having detailed descriptions of the Ids.

Thank you !

Answer Question  Select Best Answer

How we will generate Surrogate Key in Database not in Datastage.

Aloka

 Profile Answers by Aloka Questions by Aloka

• Jul 1st, 2016

Thanks Ram !

Ram

• Jun 30th, 2016

Hi Aloka,
we can generate surrogate key on database creating sequence object.
syntax: sql > create sequence < seq_object name >

Answer Question  Select Best Answer

I have an input file in below format
Name DOB
------ ------
A 10-05-1990
B 07-12-2000

Q1 -> How we will load the above file data into a target data base table in a simplest method without using Transformer ?

Q2 -> How will load the Date column into 3 splitted columns(DD|MM|YYYY). Like below
Name DOB
------ -------

amulya panda

• Aug 3rd, 2016

Use in transformer stage and use Ereplace(SOURCE_COLUMN_NAME,"-","|")

Gagandeep Singh

• Jul 29th, 2016

Define delimiter as - in Sequential file stage

Answer Question  Select Best Answer

In your project Which Case you will go for star schema designing and which Case you will go for snow flake schema designing?

Purba

• Jul 4th, 2016

Snowflake schema is used for dimension Analysis. Eg: How many online accounts does an advertiser have.

Star schema is used for metric analysis. Eg: What is the revenue of a customer.

Ram

• Jun 30th, 2016

Star schema --> Fact table is connect with different Dimension tables.
Snowflake schema --> Fact table is connect with different dimension tables. And dimension table/tables refer to another dimensional table.

Thanks !

Answer Question  Select Best Answer

I have 5 source files. In target I need to write it into a single file. But in the output file I need the corresponding input file name i.e which records are coming from which source file. How to achieve this?

sreeni

• Aug 8th, 2016

Hi Aloka we can use seq file properties -> Options -> File Name Column = FileNameCol
you will get source file name to each column. You can identify easily...

Ram

• Aug 4th, 2016

Hi
Use file name column option in seq file stage & do funnel then you will get required output.

Thanks !

Answer Question  Select Best Answer

We are using below command in Unix to run a Datastage Job:
dsjob run mode project_name job_name
When we are executing the above command in Unix what exactly it returns?

Aloka

 Profile Answers by Aloka Questions by Aloka

• Aug 8th, 2016

Hi,
May be a question for reviewers who wanted to know more on this.You can leave it.
Thanks !

Ram

• Aug 4th, 2016

Hi,
is it question for reviewers or your doubt ?
you got answer if you run the command.

Thanks !

Answer Question  Select Best Answer

I have a job with 2 parameters sets, I added new parameters sets, is it necessary to compile the job or not?

Karthik

• May 26th, 2017

Yes the jobs needs to be compiled whenever we are making changes to Parameter Set

Amruta

• Aug 1st, 2016

Yes..The DS job needs to be compiled after adding new parameter sets.

Answer Question  Select Best Answer

I have a file having columns C1,C2,C3,C4,C5 with comma delimited.In target I want store the first two columns value into one column.

Like below

Input Output I need
-------------- ----------------------
C1 C2 C3 C4 C5 C1(C1 & C2 value) C3 C4 C5

That means my input is 5 columns & Output will be 4 columns.
Can...

Chikka

• Aug 27th, 2016

Create a new stage variable and using concatenation operator combine first two columns data and map it to a column.

subbu

• Aug 26th, 2016

You can try using copy & tx stages

Answer Question  Select Best Answer

Can we implement SCD stage using Lookup stage? If Yes then how?

Neha

• Feb 21st, 2017

We can Perform SCD using lookup stage and Change Capture stage depending upon the type of SCD.

vibgyss

• Oct 7th, 2016

Yes, We can impliment SCD by Lookup stage

Answer Question  Select Best Answer

While implementing SCD, there are two output links updating data to same table. One link for insert and one link for update. How it is possible? Will it not result a write lock on a table if two links accessing same table for writing?

Ammu

• Mar 30th, 2017

Thanks for the answer

sri

• Mar 27th, 2017

Table level lock applies only when we explicitly define during table creation. In the case you mentioned to update and insert, there will be row level lock implies on that table. Logically, Insert re...

Answer Question  Select Best Answer