1. What is the difference between Oracle connector stage and Oracle enterprise stage. 2. If we can achieve Oracle enterprise stages task using Oracle connector stage then why Oracle enterprise stage exists?

Siva

• Feb 3rd, 2017

Oracle connector having test connection. Ie plugin which is not Present in the enterprise state

vibgyss

• Aug 24th, 2016

2) Ans: min of 3 stage variables are required according to my view. example: if the source has two columns cid, address. we want to remove the duplicates in cid, our job design follows like below se...

Answer Question  Select Best Answer

1.Without using stage variable how can we delete the duplicates using Transformer? 2.If we will remove duplicates using Transformer then minimum how many stage variable required for this?

Mokshada

• Jul 10th, 2021

The prerequisite for this is data should be partitioned by a key and sorted.
Later, follow the below steps:
S2 = if input.column = S1 then 0 else 1
S1 = input.column
In constraint : S2=1

And, you will get the expected result.

Karthik

• May 26th, 2017

In the transformer STAGE PROPERTIES --> select the INPUT table --> Under that select PARTITIONING --> select HASH --> Select the I/P column and select the Sort and Unique checkbox.
Compile and Run

Answer Question  Select Best Answer

I have column X which having 3 values A;B;C. How can I load these values into 3 diff cols in the target. If tomorrow I will receive 100 values for same single column I will load into 100 target cols respectively (Same de-limiter) ?

Roopa

• Mar 2nd, 2018

Using Field function. Field(column,delimeter,1) and so on

ABACHREK

• Mar 3rd, 2017

Loop through the incoming field using looping functionality in transformer. Extract the individual values using field function and the iteration number. Output the individual values to the output link...

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

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

By seeing a Datastage job how you will identify which one is Normal lookup and which one is Sparse Lookup?

Ram

• May 23rd, 2017

Sparse look up can be used only for DB stages as Source and Target where as Normal can be used for both files as well as DB stages

Sri

• Mar 30th, 2017

It can be viewed when you open the Oracle connector stage and where the lookup type is mentioned.
By default lookup type is normal

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

Can we implement SCD stage using Lookup stage? If Yes then how?

Neha

• Feb 21st, 2017

We can Perform SCD using lookup stage and Change Capture stage depending upon the type of SCD.

vibgyss

• Oct 7th, 2016

Yes, We can impliment SCD by Lookup 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

I have a file having columns C1,C2,C3,C4,C5 with comma delimited.In target I want store the first two columns value into one column. Like below Input Output I need -------------- ---------------------- C1 C2 C3 C4 C5 C1(C1 & C2 value) C3 C4 C5 That means my input is 5 columns & Output will be 4 columns. Can anyone suggest how Can I achieve this in DS job ?

Chikka

• Aug 27th, 2016

Create a new stage variable and using concatenation operator combine first two columns data and map it to a column.

subbu

• Aug 26th, 2016

You can try using copy & tx stages

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

I have an input file in the below format : NAME SAL ---- --- A 4000 B 3000 C 8000 A 2000 B 7000 C 5000 B 2000 C 9000 A 1000 If I will use Sort ---> Aggregator Group by then it will give 3 columns like NAME,MAX(),MIN() NAME MAX() MIN() ---- ----- ----- A 4000 1000 But my requirement is to generate the output which will give the maximum & minimum salary...

Sirisha d

 Profile Answers by Sirisha d Questions by Sirisha d

• Apr 18th, 2017

Job Design : Sequential File --> Aggregator --> Transformer --> Sequential File Aggregator : Group by - EmpName Col for Calculation - Salary Generate two columns - one for maximum salary (Max()...

Neha

• Apr 7th, 2017

Use Seq File->Aggregator Stage (group by Salary, Emp_Name and find min, max salary)->use Pivot(horizontal Pivot)

Answer Question  Select Best Answer

I have the 10 input file like F1,F2...F10 then I need to load these 10 input files into 10 target Output tables like T1,T2...T10. Here is the scenario for 10 tables But in future If i will receive 100 input files then I need to load it into respective 100 target tables. After loading the input files into target tables I need a confirmation in the respective target tables(By input File name) Please...

Reddy

• Jun 28th, 2017

This can be achieved by creating multiple instance job and parameterizing the source file name, table name and also need to enable run time column propagation.

shasank

• Apr 24th, 2017

We can do that allowing multiple instances for a single job in the job properties tab.

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 Y 30 C Z OUTPUT I NEED ----------------------- ID NAME LOC OCCURENCE 10 A X ...

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

Without using Sequencer how to design a Datastage job which normally receives input file to run. If tomorrow we will not receive any input file Job should not fail. It will run successfully without any warnings.

Ram

• Sep 6th, 2016

Hi Aloka,

you can achieve this by using unix shell scripting and schedule the job/script in any third party scheduling tool.

Thanks !

suresh

• Sep 2nd, 2016

Using JCL (Job Control Language) we can achieve this.

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

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 ------ ------- A 10|05|1990 B 07|12|2000

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

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

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

What will happen if we will change the partition to Auto in Join stage?

Huma

• Feb 18th, 2017

We usually go for join stage when the source input data is huge!!! If we select Hash partitioning only then it will improve the performance of job because Hash partitioning ensures that the data with ...

Aloka

 Profile Answers by Aloka Questions by Aloka

• Jul 1st, 2016

Thanks Suresh for your reply !
But when Hash partition used..It will also verify for all the partition.
It will be more helpful if you will elaborate more on this.

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

I have 2 files having different meta data and 2 files does not have any common key. How can I load the 2 files data into a single file without using Join stage?

mala

• Feb 9th, 2017

Can we use Merge?

Pavan Kumar

• Jul 11th, 2016

If Metadata is same then you can use "Funnel stage". If tables are different then, use column generator to create a common column for both the tables, then using join stage (Full outer join), join the two different tables.

Answer Question  Select Best Answer

Suppose I have designed a Datastage job using sequential file stage,It will run in sequential mode.How can I run it it Parallel mode.

Suresh

• Sep 13th, 2016

Aloka, There are two ways in your example. In sequential file stage, number of readers per node and multiple nodes. These options will run an sequential stage in parallel.

Sandhyaram

• Jul 24th, 2016

In sequential file stage, we have two options No. of readers per node and reading from multiple nodes. We can run job in parallel mode.

Answer Question  Select Best Answer

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