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

To further this, it is probably simple, but :

TABLENAME(Column) CLOB(Column)
HEADER(first row) PROVIDER12082015
MISCROWS IN BETWEEN
TRAILER(last row) 00000000669

If I have three columns created in the transformer (HeadName, HeadDate, RecCount), how would I put this in the derivations?
HeadName is supposed to be equal to PROVIDER
HeadDate...

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 am getting a file in and I want to be sure that it has specific traits. The first record must say HEADER, the second record must say PROVIDER, the last record must say TRAILER, Record Count must equal TRAILER count (HEADER - TRAILER = Record Count; TRAILER COLUMN has number of Records)