To further this, it is probably simple, but :

TABLENAME(Column) CLOB(Column)
HEADER(first row) PROVIDER12082015
MISCROWS IN BETWEEN
TRAILER(last row) 00000000669

If I have three columns created in the transformer (HeadName, HeadDate, RecCount), how would I put this in the derivations?
HeadName is supposed to be equal to PROVIDER
HeadDate...

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

hi

i have sourse like
name,city
johny,newyork
johny,mumbai
johny,del

want output like
name,city1,city2,city3
johny,nework,mumbai,delhi

thanks in advance..

Sharali Sharna

• Nov 24th, 2015

You can use Pivot Enterprise stage to achive this.

Abhinandan

• Nov 20th, 2015

Use 2 stage variable in transformer
Stagevar: if Stagevar1=sno then marks:",":Stagevar Else marks
Stagevar1: sno

map stageVar to marks

Answer Question  Select Best Answer

I am getting a file in and I want to be sure that it has specific traits. The first record must say HEADER, the second record must say PROVIDER, the last record must say TRAILER, Record Count must equal TRAILER count (HEADER - TRAILER = Record Count; TRAILER COLUMN has number of Records)

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

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

What is the difference between Filter and External Filter stages?

shrushti

• Nov 12th, 2015

FILTER EXTERNAL FILTER Filters based on some condition datastage level filter------ Uses UNIX commands to filter Can have optional re...

nagoosk

 Profile Answers by nagoosk Questions by nagoosk

• May 29th, 2008

Filter is used to pass the records basing on some condition.We will have multiple where conditions to specify.We can send to different output linksExternal filter is used to execute commands of unix.You can give the commands in the specified box with the corresponding arguements.

Answer Question  Select Best Answer

There is a sequencer 1 scheduled at 12:30 that contains 2 jobs. After sequencer 1 finishes sequencer 2 will execute that again contains 2 jobs. Now if one of the jobs on sequencer 1 fails, then sequencer 2 will not execute. Sequencer 1 should exactly start at 12:30 AM every day. Write the jil script for these sequencers.

I have two columns of data
My input data
Field Name Value
Customer_ID 100
Customer_Name ABC
Customer_City Kolkata
Customer_ID 200
Customer_Name BCD
CUstomer_City Mumbai

Customer_ID 300
Customer_Name CDE
Customer_City Delhi

Output date should be
Customer_ID Customer_Name Customer_City
100 ...

Pavan Kumar

• Sep 18th, 2015

You can use pivot enterprise stage to achieve the desired output.
Source >> Pivot Enterprise >> Destination. In Pivot set array size as three to spit the data into three records. Note that you are doing vertical pivot here not horizontal.

Answer Question  Select Best Answer

Solve with steps

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

output for (previous row salary in the current row) as below

employee_id, salary, pre_row_salary
-----------------------------------
10, 1000, Null
20,...

Can anybody solve this interview question in DataStage?
My input is:
EMPNO, DEPTNO, SALARY
1 A 100
2 A 4000
3 A 400
4 B 500
5 B 150
6 B 700

I need extra column "Rank" in the output is:

EMPNO, DEPTNO, SALARY,...

surya

• Sep 16th, 2015

1. Source-->sort(dept,sal), partition (hash(dept))
2. Transformer(rank=inrownum (inrownum will be rownumber in a partition which is nothing but rank)

Answer Question  Select Best Answer

Is it possible to find out the maximum salary by using remove duplicate stage? Can you any one explain?

Thanks in advance

deepakatsit

 Profile Answers by deepakatsit Questions by deepakatsit

• Sep 10th, 2015

Hello Balu,
You can achieve Maximum salary by using remove duplicate.

Input File --> sortstage( Ascending or descending) --> Removeduplicate ( In remove duplicate Retain last if sorting ascending or retain first if sorting in Descending order ) --> output file.

Hope this will help.

Answer Question  Select Best Answer

hi...

i have sourse like

name,city
johny,newyork
johny,mumbai
johny,del

want output like.....

name,city1,city2,city3
johny,nework,mumbai,delhi
-----------------------------------------
thanks in advance...

RAM

• Aug 27th, 2015

Without using Pivot stage..
Source --> COPY
COPY --> Head(1) --> File1
COPY --> Head(2) -->Tail(1) --> File2
COPY --> Tail(1)-->File3
now merge all file1,file2,file3 --> Target.
Pls check it may work.

sai3689

 Profile Answers by sai3689 Questions by sai3689

• Apr 1st, 2015

Thank u abishek

Answer Question  Select Best Answer

RAM

• Aug 27th, 2015

SMP is a shared Disk. Its having 62 or 124 CPUs.
MPP is a shared nothing or clustered systems . We can say combination of multiple SMPs.

Himanshu Maheshwari

• Apr 2nd, 2015

The environment in which you run your parallel jobs is defined by your systems architecture and hardware resources. All parallel processing environments are categorized as one of: SMP (...

Answer Question  Select Best Answer

sai3689

• Aug 24th, 2015

But how to create batch file Runbatchjobs.bat? Can you explain with steps

nikhilanshuman

 Profile Answers by nikhilanshuman Questions by nikhilanshuman

• Dec 17th, 2010

From command prompt batches can be created in following way :a) create a batch file say RunbatchJobs.batb) Open this file in notepad.c) Now,write the command "dsjob" with proper syntax for e...

Answer Question  Select Best Answer

I have 3 jobs A,B & C, which are dependent each other, I want to run A & C jobs daily and B job run only on Sunday. How can I do it?

sudheer

• Aug 20th, 2015

A Job ----> Routine Activity ---Sunday---> B Job ----> C Job
--- Not Sunday -----> C Job

bimaljsr@gmail.com

• Aug 10th, 2015

Sequence Design -

A Job---> (B sequence explained later) --> C Job

B sequence contents
Dummy job to check specific day ---> condition activity with 2 link --> if Sunday run job B, if not Sunday run dummy job and finish.

Answer Question  Select Best Answer

Why lookup is heavy weight and join is light weight operator?

sireesh vasantha

• Aug 3rd, 2015

LOOKUP use RAM to perform operations where as JOIN use buffer to perform operations

Answer Question  Select Best Answer

How can we load three different flat files(1 file .txt,2 file .csv,3 file xml) to sequential file at a time?

Narra

• Jul 21st, 2015

Ready all the 3 source as a fixed width file. Otherwise it wont possiible untill unless having the same format

Devesh Ojha

• Aug 11th, 2014

If Metadata is the same then we can load by doing the Union operation and if metadata is diffrent then first sync the metadata and then load them .

Answer Question  Select Best Answer

1. Is the Hash file is active or passive? If we take as source ?2. can u take seq file as look up?3. In hash file dynamic 30, there r two types:1)genaric2)specific what is the meaning?4. how to connect MERGE STAGE while source as two tables?5. what is the purpose of MERGE?6. how can DS job scheduled in Unix?7. how do u know how many rows rejected?8. use of universe stage?9. what is SEQ file buffer?10. diff...

jallu

• Jun 3rd, 2015

Scheduling the datastage jobs in unix using corntab utility (or) uc4

jallu

• Jun 3rd, 2015

Merge stage combine two tables based on key column and its implemented two joins 1.innerjoin,2.leftouter join. merge stage can handle large volume of data and it need less memory, the data must be partitioned and sorted also it capture the rejected data from updated source.

Answer Question  Select Best Answer

Murali

• Jun 2nd, 2015

SCD type 2 can be implemented by adding Surrogate Key. The existing Primary Key will be considered as a Natural Primary Key and the Surrogate Key will be your Primary Key.Also, Start date & End date (...

Answer Question  Select Best Answer