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

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