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.

Answer Question  Select Best Answer

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

 Profile Answers by nagoosk Questions by 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.

Answer Question  Select Best Answer

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

Answer Question  Select Best Answer

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

Answer Question  Select Best Answer

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.

Answer Question  Select Best Answer

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 !

Answer Question  Select Best Answer

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 3rd, 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...

Answer Question  Select Best Answer

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

Answer Question  Select Best Answer

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 31st, 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()

Answer Question  Select Best Answer

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

Ram

• Mar 31st, 2016

Src --> Transform --> Trg

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

Nikhil

• Dec 15th, 2015

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

Answer Question  Select Best Answer

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

Answer Question  Select Best Answer

How to extract last 3 days data using SCD?

venkat

• Mar 28th, 2016

Use SCD Type 2 then get the last 3 days data.

Answer Question  Select Best Answer

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.

sai3689

 Profile Answers by sai3689 Questions by sai3689

• Oct 10th, 2015

In the properties Pivot type=horizontal Tell me which to take pivot column?

Answer Question  Select Best Answer

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

Mallikarjuna_G

 Profile Answers by Mallikarjuna_G Questions by Mallikarjuna_G

• Aug 10th, 2013

There are only two ways to this -

1. Use datastage dataset management utility[GUI].
2. Use orchadmin utility from commandline

Answer Question  Select Best Answer

How to integrate an Informatica Data Quality mapplet with DataStage?

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

Answer Question  Select Best Answer

INPUT:

SOURCE DESTINATION DISTANCE(KM)

HYD CHN 500
CHN HYD 500
BANG HYD 600
HYD BANG 600
PUN HYD 750
HYD PUN 750

OUTPUT :

Rudra

• Feb 9th, 2016

Source->Transformer Stage -> Target
In transformer use 2 stage variables. Use hash Partition in the source and select key field and select sort option only. STG1 -> if stg2= keyfield then 1 else 0
stg2 - keyfield
Then in filter put condition stg1=0

Ram

• Feb 5th, 2016

Simple:
Source --> Sort (key distance) --> RD (retains 1st occuance) --> Target

Please let me know if it is not work.

Answer Question  Select Best Answer

Vidhya

• Dec 21st, 2015

You can call the parallel routine in a parallel job using dsRoutine Function. To call the Server Routine in Parallel job use BASIC_Transformer stage. This stage usually wont present in the design pale...

kmalla

 Profile Answers by kmalla Questions by kmalla

• Jun 29th, 2006

In transformer stage we have to edit the field and click dsRoutines.It will prompt to select the routine .Thats it

Answer Question  Select Best Answer

I would like to know if you could help me out. I have a a file with 2 columns (TABLENAME and CLOB) and in the TABLENAME there are "Providers". I would like to know how to set them at number 5000 and then increment them, per provider. So if I have 5 providers per file, it would be 5000, 5001, 5002, 5003, 5004. This is a daily file so we will get a file everyday. So Day 2, the 1st provider must be 5005...

nikhil

• Dec 11th, 2015

From your question it appears that you want to generate a surrogate key with 5000 as seed value. This can be done through surrogate key generator using state file property and increment by 1. Set generate from last highest value also

Answer Question  Select Best Answer

I have heard of both Convert and Ereplace but I am stumped. I need to take every double dash (--) from my files and convert them to a single hyphen (-). What would be the best way to do this?

nikhil

• Dec 11th, 2015

Based on your need ereplace or change function will be better choice. Convert will treat (--) as 2 different characters.

Answer Question  Select Best Answer