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
    SNO, MATHS, PHYSICS, CHEMISTRY SNO, MARKS
    101, 70, 80, 90 101, 70
    102, 65, 55, 75 101, 80
    101, 90
    102, 65
    102, 55
    102, 75

    sreeni

    • Jun 22nd, 2016

    Buy using Pivot You can archive.
    seq--->Pivot(Hor)--->seq
    In pivot properties write a derivation MATHS, PHYSICS, CHEMISTRY

    DEEPAK KUMAR

    • Jul 7th, 2015

    Use @iteration function in transformer

  •  

    How to remove reverse duplicates in datastage ?

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

    sreenivas

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

    bimaljsr@gmail.com

    • Aug 10th, 2015

    See if this works

    Code
    1.  
    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..
    A
    A
    B
    B
    B
    C
    C
    D
    ........want output columns(2 cols) as.........
    A 1
    A 2
    B 1
    B 2
    B 3
    C 1
    C 2
    D 1

    THANX IN ADVANCE.

    Mayilkannan

    • Jun 17th, 2016

    Sv1=Inputcolumn
    Sv2=If Sv1=Sv3 then Sv4 Else 1
    Sv3=Sv1
    Sv4=1+Sv2

    Pass Sv2 in output column

    Aparna

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

  •  

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

    nagoosk

    • 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,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?

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

    Ram

    • 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:
    10|A|B|C|D
    20|P|Q|R|S

    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

    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

  •  

    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.

  •  

    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

  •  

    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.

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   

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