What is the minimum number of input and output required for Lookup ?

vibgyss

• Aug 24th, 2016

Min of 2 inputs is required by lookup stage and 1 output link is mandatory for any type of lookup i.e. we have 4 types of lookups from 8.0 version those Normal lookup, sparse lookup (only supports 2 inputs one is primary and second is reference), range lookup, caseless lookup

Answer Question  Select Best Answer

How to handle rejects in Transformer & How many rejects a xfm support?

vibgyss

• Aug 24th, 2016

Transformer
The transformer only rejects the data of a column which has nulls and that column is used in the expression then that type data is rejected by transformer stage

No other data is rejected by Transformer Stage
Only one reject link supported by transformer

Answer Question  Select Best Answer

Link Partitioner - Used for partitioning the data.Link Collector - Used for collecting the partitioned data.

Read Best Answer

Editorial / Best Answer

nikhilanshuman  

 Profile Answers by nikhilanshuman Questions by nikhilanshuman

• Member Since Nov-2009 | Dec 17th, 2010

---

Link Partitioner and collecter are basically used to introduce data parallellism in server jobs.link partitioner,splits the data on many links.Once the data is processed,link collector collects the data and passes it to a single link.These are used in server jobs.In datastage parallel jobs,these things are inbuilt and automatically taken care of.

karthik

• Aug 23rd, 2016

Link Partition and Link collector are used in Server jobs for to split data.
Partition technique can be done in server jobs by using these two stages.

nikhilanshuman

 Profile Answers by nikhilanshuman Questions by nikhilanshuman

• Dec 17th, 2010

Link Partitioner and collecter are basically used to introduce data parallellism in server jobs.link partitioner,splits the data on many links.Once the data is processed,link collector collects the da...

Improve Answer

I have 5 source files. In target I need to write it into a single file. But in the output file I need the corresponding input file name i.e which records are coming from which source file. How to achieve this?

sreeni

• Aug 8th, 2016

Hi Aloka we can use seq file properties -> Options -> File Name Column = FileNameCol
you will get source file name to each column. You can identify easily...

Ram

• Aug 4th, 2016

Hi
Use file name column option in seq file stage & do funnel then you will get required output.

Thanks !

Answer Question  Select Best Answer

We are using below command in Unix to run a Datastage Job:
dsjob run mode project_name job_name
When we are executing the above command in Unix what exactly it returns?

Aloka

 Profile Answers by Aloka Questions by Aloka

• Aug 8th, 2016

Hi,
May be a question for reviewers who wanted to know more on this.You can leave it.
Thanks !

Ram

• Aug 4th, 2016

Hi,
is it question for reviewers or your doubt ?
you got answer if you run the command.

Thanks !

Answer Question  Select Best Answer

I have an input file in below format
Name DOB
------ ------
A 10-05-1990
B 07-12-2000

Q1 -> How we will load the above file data into a target data base table in a simplest method without using Transformer ?

Q2 -> How will load the Date column into 3 splitted columns(DD|MM|YYYY). Like below
Name DOB
------ -------

amulya panda

• Aug 3rd, 2016

Use in transformer stage and use Ereplace(SOURCE_COLUMN_NAME,"-","|")

Gagandeep Singh

• Jul 29th, 2016

Define delimiter as - in Sequential file stage

Answer Question  Select Best Answer

I have a source file having 4 columns. How can I store first 2 columns values into one target and the next 2 columns values into another target? Can anyone suggest me here how can I achieve this using Copy stage and without using Copy Stage?

Ram

• Aug 4th, 2016

Hi

you can try with 1) copy stage & 2) transformer stage

Thanks!

Answer Question  Select Best Answer

Can we change the configuration file during run time? If then how?

hema

• Aug 3rd, 2016

First we need to add the environment variable(apt_config_file) to the job parameters

Answer Question  Select Best Answer

How to generate the occurrence no in out put file for the respective no of records in input file
I need a count in the target file how many times the same record is available in input file.

INPUT
-----

ID NAME LOC

20 B Y
10 A X
20 B Y
30 C Z
10 A X
10 A X
20 B Y
20 B ...

Aloka

 Profile Answers by Aloka Questions by Aloka

• Jul 22nd, 2016

Thanks Sri for your reply. After using 3 stage variable in xfm we can generate the count of the respective record.But after that i need no remove the duplicates to get the unique respective record in...

Sri

• Jul 20th, 2016

First sort the incoming rows by ID, NAME and LOC.
Then take three stage variables in the transformer

StageVar=inputcol
StageVar1=Stagevar
StageVar2=if StageVar=StageVar1 then StageVar2+1 else 1
map the StageVar2 to the output link column

Answer Question  Select Best Answer

In Data warehouse which table will load first and why? Fact or Dimension?

Sri

• Jul 20th, 2016

Dimension table will load first and then fact tables.

Ram

• Jun 30th, 2016

Hi Aloka,
Fact table is connect with different dimension tables with foreign key relationship. Fact table having only Ids & Measures. Dimension tables having detailed descriptions of the Ids.

Thank you !

Answer Question  Select Best Answer

We are using Bulk Collect methodology in Database to load the bunch of records at a time.How we will implement the same operation in Datastage. If my source having millions of records, I want to load the bunch of records in to target at a time. How?

Sri

• Jul 20th, 2016

Increase the array size in Target Table stage. Also use Parallel Hints while selecting the records.

Pavan Kumar

• Jul 8th, 2016

You can load the bunch of files at a same time using sequential file stage. In options just select the file location the DB and change the option in the seq file stage to "File Pattern".

Answer Question  Select Best Answer

How will you separate the records with special characters in a column. For example
If suppose the column contains
Name*
&Name
Na*me
$Name
Na$me

I need to separate these records alone.

Shrushti

• Jul 16th, 2016

Source - transformer - target.
In transformer,
Namesp --- If alphanum(Name) then 1 else 0
Constraint --- Namesp=0

Answer Question  Select Best Answer

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

Kumar

• 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
Stv3=Stv1
3.Output of transformer
Col1,Col2
Input column,Stv2

Answer Question  Select Best Answer

In your project Which Case you will go for star schema designing and which Case you will go for snow flake schema designing?

Purba

• Jul 4th, 2016

Snowflake schema is used for dimension Analysis. Eg: How many online accounts does an advertiser have.

Star schema is used for metric analysis. Eg: What is the revenue of a customer.

Ram

• Jun 30th, 2016

Star schema --> Fact table is connect with different Dimension tables.
Snowflake schema --> Fact table is connect with different dimension tables. And dimension table/tables refer to another dimensional table.

Thanks !

Answer Question  Select Best Answer

Basically which data model do we use in the project?

Pavan Kumar

• Jul 5th, 2016

Hi alok It totally depends on the project. 1) Entity Relation model or ER model is used when the data source is small as the number of joins are less. 2) Dimention model a) Star : Used for...

Answer Question  Select Best Answer

A sequential file has 8 records with one column, below are the values in the column separated by space,1 1 2 2 3 4 5 6In a parallel job after reading the sequential file 2 more sequential files should be created, one with duplicate records and the other without duplicates.File 1 records separated by space: 1 1 2 2File 2 records separated by space: 3 4 5 6How will you do it

Ram

• Jul 1st, 2016

Hi Pooja, Its absolutely possible.. Src --> Copy(linksort) ---> Aggr(count rows) another link from copy -----------------> Join (Copy & aggr) ---> Filter(count=1 for trg1 and co...

Pooja Trivedi

• Jun 30th, 2016

This will not give the desired output as the we want the duplicate records also n number of times where n is the number of record present in the file.

Answer Question  Select Best Answer

How we will generate Surrogate Key in Database not in Datastage.

Aloka

 Profile Answers by Aloka Questions by Aloka

• Jul 1st, 2016

Thanks Ram !

Ram

• Jun 30th, 2016

Hi Aloka,
we can generate surrogate key on database creating sequence object.
syntax: sql > create sequence < seq_object name >

Answer Question  Select Best Answer

In join stage how we will store the rejected record in one file?

Aloka

 Profile Answers by Aloka Questions by Aloka

• Jul 1st, 2016

Thanks Ram !

Ram

• Jun 30th, 2016

Hi Aloka,
Join stage doesnt support reject link. so just connect join stage --> Transformer stage and provide constraints on Transformer to get rejects in Join stage.

Answer Question  Select Best Answer

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

Ram

• Jun 30th, 2016

You need to do file validation part using shell or any other scripting as before subroutines.

Answer Question  Select Best Answer

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

Answer Question  Select Best Answer