Submitted Questions

  • Splitting a Large Job DataStage into Smaller Jobs

    What is the process to split the large datastage job into smaller jobs?


    • 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

  • 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 -------------------------------- 10, A 10, A,B 10, A,B,C 10, ...

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

  • Cumulative Sum Calculation

    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, 3000, 6000 40, 5000, 11000

    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


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

  • Display the Rank in asending & descending order for salary column

    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, RANK 1 A 100 3 2 A ...


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

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

    Amulya Kumar Panda

    • Jul 11th, 2016

    Job flow sequential_file-> eggregate->sort->tranformer-> sequential

    fast aggregate stage use count rows and add extra column seq_no
    next stage sort -key source column sort order ascending.
    tranformer stage use loop while ex-@ITERATION


    • Jul 8th, 2016

    Seq file>transformer>Seq file
    1.Mode of execution in transformer must be sequential and data must be sorted on col1
    2. Transformer logic:
    Stv1=Input column
    Stv2=if Stv1=Stv3 Then Stv2+1 Else 1
    3.Output of transformer
    Input column,Stv2

  • Single column with multiple rows should be converted into single row with commas in datastage

    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


    • Apr 11th, 2015

    By using pivot stage in datastage

  • Single column with multiple rows should be converted into single row with commas

    Can any one help.... Source is..... a b c d want target as..... a,b,c,d thanks in advance


    • Aug 9th, 2017

    You can create a other array with some size and insert data in single array from double array for more see the following code for(int i=0;i

    1. class TestClass{
    2.           public static void main(String l[])
    3.           {
    4.                   int [][]a={{1},{2},{3},{4},{5}};
    5.                   int a1[]=new int[6];
    6.                   int k=0;
    7.                   for(int i=0;i<a.length;i++)
    8.                   {
    9.                           for(int j=0;j<a[i].length;j++)
    10.                           {
    13.                                   a1[k]=a[i][j];
    14.                                   k++;
    15.                                 System.out.println(a[i][j]);
    16.                           }
    18.                   }
    22.  for(int i=0;i<a1.length;i++)
    23.  {
    24.         System.out.print(a1[i]);
    25.  }
    26.           }
    28.  }

  • convert multiple rows into single row

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


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

  • Convert multiple rows into columns in datastage

    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.


    • Nov 20th, 2015

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

    map stageVar to marks