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

Hi all,
I have 100 rows in a table and want to distribute the data by 4 equal parts and write to four sequential file. here the source data keeps changing. Example : if source data is having 1000 rows the target files have to get 250 rows each in the four files.
1000/4 = 250 each
100/4 =25 each

jallu

• May 27th, 2015

Read the data from sequential file and pass the through sample stage and put the 4 targets ,in sample stage set the properties percentage mode=25% we can get require output

Nish

• May 26th, 2015

source-> Transformer->Target. 4 targets from Transformer stage. Map the input to all the 4out put stages. now each link will have the following constraints respectively. Link1 :Mod(@INPUTROWNUM,4)=...

Answer Question  Select Best Answer

Can any one help....
I have single column in Source like.....
a
b
c
d
want target as.....
a,b,c,d
thanks in advance

D Hema

• Apr 23rd, 2015

Use the stag variable in the transformer
StageVar=StageVar:,: input column name
go to the constraints :-Lastrow()
map that stage variable to the output column

jallu

• Apr 11th, 2015

By using pivot stage in datastage

Answer Question  Select Best Answer

I came across this question many times in interview, In specific what can i answer..... Please help..

rstrainings

• Apr 15th, 2015

we can say like there is a staging area to store the data in the form of tables, and then it transforms to ETL stage (Here we will do all the conversions,remove duplicates,joining,merging etc) then di...

shiv

• Jun 11th, 2014

The above answer, is to architecture of Datastage, its not the architecture of project. Project architecture would be like: ***************************** You have: 1 Source--------> 1 Staging Area----...

Answer Question  Select Best Answer

input is
cola
1
2
3
this should be populated at the output as

cola
1
2 2
3 3 3

satish

• Apr 2nd, 2015

In Transformer use stage variable Str(%Input_column%,%Input_column%)

sai3689

 Profile Answers by sai3689 Questions by sai3689

• Mar 17th, 2015

@iteration

Answer Question  Select Best Answer