DataStage Interview Questions

Showing Questions 1 - 20 of 690 Questions
First | Prev | Next | Last Page
Sort by: 
Jump to Page:

    Identify Header, Detail and Trailer Records

    Identify Header, Detail and Trailer records in the incoming file and verifying whether trailer count is equal to total number of detail records.

    My input is
    Header Columnname
    Detail row 1
    Detail row 2
    Detail row 3
    Trailer 3

    Here I want to see whether total number of detail records is equal to total number of trailer record.
    If so proceed...


    I would like to have output as like this 101 90 next line 102 65 next line 102 55 etc...

    11. Find The Output With out PIVOTE stage
    Source File: Target
    101, 70, 80, 90 101, 70
    102, 65, 55, 75 101, 80
    101, 90
    102, 65
    102, 55
    102, 75


    • Jun 22nd, 2016

    Buy using Pivot You can archive.
    In pivot properties write a derivation MATHS, PHYSICS, CHEMISTRY


    • Jul 7th, 2015

    Use @iteration function in transformer


    How to remove reverse duplicates in datastage ?

    Objective: The interface aims to consolidate the round-trip ticket cost of passengers.
    Description: The source file is a .txt file, train_route_src.txt, consolidated with trains from a particular source to destination. There are many repetitions in the source file (Reverse Duplication: eg. BLR --> BBS and BBS --> BLR). Remove the reverse duplicates and maintain the target in...


    • Jun 21st, 2016

    source ************** city1,city2,distance hyd,pune,500 del,mum,1000 hyd,pune,500 blore,viza,700 del,mum,1000 mum,pune,300 target ******************* city1,city2,distance hyd,pune,500...

    • Aug 10th, 2015

    See if this works

    2. SELECT a.Source1,a.destination,b.destination, 2*a.distance,2*a.fare
    3. FROM train A, train B
    4. WHERE (A.Source1=B.destination
    5. AND A.destination=B.Source1)


    Giving occurances

    can anyone pls solve in datastage.........
    source column is..
    ........want output columns(2 cols) as.........
    A 1
    A 2
    B 1
    B 2
    B 3
    C 1
    C 2
    D 1



    • Jun 17th, 2016

    Sv2=If Sv1=Sv3 then Sv4 Else 1

    Pass Sv2 in output column


    • Jun 22nd, 2015

    Svar=> input column
    svar1=> If svar=svar2 Then svar1+1 Else 1
    Svar2=> svar
    Next populate input column and svar1 in target


    How we can implement Bulk Collect methodology in Datastage

    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.


    • 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.


    Add 10 days to a particular date

    How to implement the scenario: Add 10 days to a particular date in Datastage PX

    Venkatesh Mekewar

    • May 31st, 2016

    You can use Date function named "DateFromDaySince()" present inbuilt in Datastage. Example: Consider Link.DaystoAdd contains 10 and Link.GivenDate contains 2016-10-31. Following function will retur...


    • Mar 5th, 2008

    extract the day part of the date by using the string function and then add how many days u want and then again add all those parts into single string and then convert the string into date object.


    Datastage job scenario question

    My input has a unique column-id with the values 10,20, can i get first record in one o/p file,last record in another o/p file and rest of the records in 3rd o/p file?

    Pradeep K Amboji

    • May 25th, 2016

    @Abhinav . Hope youve the answer already so posting for general info. you were getting two records because your config file has 2 nodes. when using system variables (such as @INROWNUM or @OUTROWNUM ) ...


    • Mar 30th, 2016

    Its simple.

    src --> Transform --> Targ1 & Targ2 & Targ3

    Derviation for Transform:
    Constrains :
    @inrownum=1 --> trg1
    Lastrow() --> trg2
    select Otherwise --> trg3


    Display with Commas

    Solve these with suitable logics in datastage

    Input is like..

    department_no, employee_name
    20, R
    10, A
    10, D
    20, P
    10, B
    10, C
    20, Q
    20, S

    want result like

    department_no, employee_list

    Shilpa Banerjee

    • May 26th, 2016

    Use Pivot enterprise stage and select vertical pivoting. Also perform sort in the stage using the partitioning as Hash. Take Dept_number as group by column and Employee_name as Pivot column and the output will be in the form of Dept_number|Employee list. For example:

    Vijayashree HN

    • May 11th, 2016

    First sort the columns Department_no and employee_name in ascending order. Then use the stage variables. current=department_no result= If current=prev then result:,:employee_name else employee_n...


    Maximum score details among 10 records using transformer stage

    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


    • Apr 30th, 2016

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


    • 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


    How to get top five rows in DataStage?

    How to get top five rows in DataStage? I tried to use @INROWNUM,@OUTROWNUM system variables in transformer..but they are not giving unique sequential numbers for every row...please help!

    Thanks in advance!!


    • Apr 26th, 2016

    You make sure to use single partition then you can use @INROWNUM , @OUTROWNUM system variable to get unique records of first top N.
    Try it.
    Thanks !


    • Apr 12th, 2016

    You can use head stage instead


    Downstream & Upstream

    What are these terms used for in Datastage - Downstream and Upstream?


    • Apr 22nd, 2016

    We can interpret Upstream as some thing flowing up like extracting data from source systems(similarly like extracting water from well) and downstream as data flowing into (like water flowing into canal).


    • May 17th, 2013

    Upstream means data channel from source system to ETL.
    there are verity of source system (RDBMS, Flat Files, XML, ODBC).this data is integrated by ETL.this is input to ETL.
    same as upstream.

    Downstream means data generated from ETL.


    How to achieve this?

    My input data:

    Field_Name Value
    Customer_ID 100
    Customer_Name ABC
    Customer_City Delhi
    Customer_ID 200
    Customer_Name BCD
    Customer_City Mumbai

    My output should look like
    Customer_ID Customer_Name Customer_City
    100 ABC Delhi
    200 ...

    Arpit jain

    • Apr 5th, 2016

    We can do this by using roll up component by using accumulation function in it .


    • Mar 10th, 2016

    Its simple..
    Src(file 2 cols) --> Transform (drop 1st col and create dummy field for group id) -->
    Pivot enterprise(type vertical and array size=3) --> copy (rename the column names as required) --> trg.
    Pls try , it will work..thanks !


    Sequence Job Creation Scenario

    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


    • Apr 2nd, 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...


    • 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...


    Find Rank Based On Salary

    How to find rank based on salaries?


    Sal Rank
    5000 1
    5000 1
    4000 2
    3000 3


    • Apr 1st, 2016

    3 stage variables:
    sal : stgVar1
    ((sal< >presal and sal > presal) or(sal=presal)) then 1 else 0 : StgVar2
    stgVar1 :presal
    1 :Rank
    if StgVar2=1 then Rank else Rank++ :RankVal


    • Mar 28th, 2016

    Src-->sort(desc)-->Trnsfrm(3 stage variables & generate Rank col) --> trg.

    Transform Derivation for Rank column:
    Initialize Stgv1,Stgv2,Stgv3=0
    stgv2=>if stgv1=stgv3 then stgv2 else stgv2+1


    Add a Sequence Number for a Table/Row

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


    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...


    • Mar 30th, 2016

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


    • Mar 28th, 2016

    Syntax is:


    SRC records

    SRC has 1 record I want 10records in target how is it possible ? plz explain me ?


    • Mar 30th, 2016

    Src --> Transform --> Trg

    Use Iteration < = 10 for the records...
    So you will 10 records in target.


    • Dec 14th, 2015

    Take a rowgen stage, limit number of rows=9, funnel it src file


    What are Stage Variables, Derivations and Constants? 

    Stage Variable - An intermediate processing variable that retains value during read and doesnt pass the value into target column. Derivation - Expression that specifies value to be passed on to the target column. Constant - Conditions that are either true or false that specifies flow of data with a link. 


    • Mar 28th, 2016

    In Transformer stage there are 3 Types: 1) Stage Variables are used for passing a value as a input value. 2) Derivation: In this stage all functions like logical, Mathematical, String, Date and Time ...


    • Feb 17th, 2016

    Order of execution is:
    Stage Variable
    Constraint then


    Convert Column Data into Row Data

    Solve in Datastage


    col1, col2, col3
    a, b, c
    d, e, f

    Output like



    • Mar 23rd, 2016

    Src --> colgen(Dummy) --> Pivot (type=Horizntal & derivaion col=col1,col2,col3) -->copy(drop dummy col) --> trg.

Showing Questions 1 - 20 of 690 Questions
First | Prev | Next | Last Page
Sort by: 
Jump to Page:


Have Interview Question?

Please select the most appropriate category and mention a brief question title along with clear question details.

Ask Question

Login to Ask Question or Register your free account   

Category :
Sub Category :
Question Title:
Question in Detail :
Job Role (Optional) :
Company (Optional) :

Optional Features

Register at GeekInterview

Register me.

Subscribe to GeekInterview Newsletter

Yes, Subscribe me to Interview & Career Tips