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

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

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

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

dstage_user

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

Answer Question  Select Best Answer

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.

Abhinandan

• Nov 20th, 2015

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

map stageVar to marks

Answer Question  Select Best Answer

I need help validating rows. 1st. I have a sequential file that has 139 rows, 2 columns (TABLENAME, CLOB). Of those rows, two (in TABLENAME column) are named "HEADER" and "TRAILER". I do not want these in my output. How do I keep the 137 rows and cut off the HEADER & TRAILER. 2nd. In the TRAILER row (TABLENAME column), there is a number that reads 00000000137 (in the CLOB column), which is the 137...

Mahesh R

• Nov 18th, 2015

You can use constraints facility in transformer stage, to cut-off rows with HEADER and TRAILER. If you mention the column name with IN condition the rows will get dropped off.

Rudra

• Nov 17th, 2015

One thing is not clear from you question i.e. the record count will be written on 138th record I think. As part of sequential stage ignore the 1st line as header. Use a shell script to get the record ...

Answer Question  Select Best Answer

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

sai3689

 Profile Answers by sai3689 Questions by sai3689

• Nov 18th, 2015

Thanks Rohit

Rohit

• 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

Answer Question  Select Best Answer

What is the difference between Filter and External Filter stages?

shrushti

• Nov 12th, 2015

FILTER EXTERNAL FILTER Filters based on some condition datastage level filter------ Uses UNIX commands to filter Can have optional re...

nagoosk

 Profile Answers by nagoosk Questions by nagoosk

• May 29th, 2008

Filter is used to pass the records basing on some condition.We will have multiple where conditions to specify.We can send to different output linksExternal filter is used to execute commands of unix.You can give the commands in the specified box with the corresponding arguements.

Answer Question  Select Best Answer