DataStage Interview Questions

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

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

    Ram

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

    lavanya

    • Apr 12th, 2016

    You can use head stage instead

  •  

    Downstream & Upstream

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

    kamal

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

    mohan

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

  •  

    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

    Vijayashree HN

    • Apr 15th, 2016

    Use aggregator stage. Put the group by column as department_no and count column as employee_name.
    Propagate count column as employee_list which will gives the number of employs based on department_no.

  •  

    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

    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

  •  

    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 .

    Ram

    • 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

    Bharath

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

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

  •  

    Find Rank Based On Salary

    How to find rank based on salaries?

    Input
    ------
    sal
    5000
    5000
    4000
    3000

    Output
    ---------
    Sal Rank
    5000 1
    5000 1
    4000 2
    3000 3

    Reddy

    • 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

    Ram

    • Mar 28th, 2016

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

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

  •  

    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:

    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 30th, 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()

  •  

    SRC records

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

    Ram

    • Mar 30th, 2016

    Src --> Transform --> Trg

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

    Nikhil

    • Dec 14th, 2015

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

  •  

    Datastage job scenario question

    My input has a unique column-id with the values 10,20,30.....how 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?

    Ram

    • Mar 30th, 2016

    Its simple.

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

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

    abhinav

    • Feb 22nd, 2016

    I am getting two records instead of one for both first and last record.Is there anything related to partitioning as well?

  •  

    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. 

    venkat

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

    HIANSHU SINGH

    • Feb 17th, 2016

    Order of execution is:
    Stage Variable
    Constraint then
    Derivation

  •  

    Convert Column Data into Row Data

    Solve in Datastage

    Input:

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

    Output like

    Col
    ---
    a
    b
    c
    d
    e
    f

    Ram

    • Mar 23rd, 2016

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

  •  

    Count in Dataset

    How to get the dataset record count? with out using orchadmin command

    Priyanka Sinha

    • Mar 17th, 2016

    1) The Datastage Designer GUI (also available Manager and Director) provides a mechanism to view and manage data sets. It can be invoked in Tools -> Data set management. 2) orchadmin command-line uti...

  •  

    How to Seperate Repetating & non-Repetating data.

    I have data in Table as

    ID
    1
    2
    3
    1
    4
    3
    3
    5
    6
    6
    7

    Now I want to have Output as repeatating records in one table & non-repeatating in another table

    Table 1 :
    Id
    2
    4
    5
    7

    Table 2:
    ID
    1
    1
    3
    3
    3
    6
    6

    Rajasekhar Reddy Balu

    • Mar 15th, 2016

    Count=1 Seq File---->>copy----->>Filter--------------->>File1(2 4 5 7)Unique Rec | / | ...

    Ram

    • Mar 9th, 2016

    Method:
    Src-->copy (use link sort)-->aggr (count).
    2ndlink of copy---> join (copy & agg) -->filter (count=1 for 1st source and count > 1 for another src) -->2 sources.

  •  

    What are Sequencers?

    Sequencers are job control programs that execute other jobs with preset Job parameters.    

    Star Read Best Answer

    Editorial / Best Answer

    jagannimmagadda  

    • Member Since Jan-2006 | Jan 10th, 2006


    A sequencer allows you to synchronize the control flow of multiple activities in a job sequence. It can have multiple input triggers as well as multiple output triggers.The sequencer operates in two modes:ALL mode. In this mode all of the inputs to the sequencer must be TRUE for any of the sequencer outputs to fire.ANY mode. In this mode, output triggers can be fired if any of the sequencer inputs are TRUE

    Priyanka

    • Mar 10th, 2016

    @Rohit: Yes you can run the 2nd job even if the first one fails. For that you need to set the trigger option to unconditional. However, the control comes to the 2nd job only after the 1st job has run (pass or fail).

    Rohit Soni

    • Jul 20th, 2015

    @Bimal Pradhan: You mean to say we cant run the 2nd job if 1st job is failed? What if I wanna run 2nd job if 1st job got failed out of 4 jobs.. plz reply

  •  

    Datastage real time scenario

    I have source like this

    a,b,c,1,2,3 ( All this in one column)

    I wanna target following below

    a,b,c,1 ( Ist row)
    a,b,c,2 (2nd row)
    a,b,c,3 (3rd row)

    Thanks in advance.

    Ram

    • Mar 10th, 2016

    Hi Tulasi,
    This is for you..

    Src -->Trns (use Function Str(inputcolumn,@inputrow) --> Trg

    Thats all !

    Ram

    • Mar 10th, 2016

    Its simple.
    Src (read a single field) --> Trans(Use filed function) --> Pivot Enterprise--> Trg.

    It will work. Pls let me know if any concern.

  •  

    How to convert multiple rows into single row in datastage

    Hi,

    Can you please solve this in datastage..

    my input is
    name,city
    johnson,newyork
    johnson,mumbai
    johnson,delhi

    want output as
    name,city1,city2,city3
    johnson,newyork,mumbai,delhi

    pls explain with steps
    thanking in advance...

    Ram

    • Mar 7th, 2016

    You can do use pivot enterprise and give the properties as pivot for cty and group by name.
    It will work.
    Input --> Pivot Enterprise (vertical pivot) --> trg.

    Neha

    • Jan 2nd, 2016

    We can directly use pivot job just set the properties and you can get the desired output.

  •  

    Abort Job When Records Exceeds 100

    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

Showing Questions 1 - 20 of 687 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   

Name:
Email:
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