I have two columns in the source , COl A and Col B .
Input is like
Cola Colb
100 ABCDEF
I should achieve the output as
Cola Colb
100 A
100 B
100 C
100 D
100 E
100 F

Note : Colb is not of fixed length .

Yash

• Jan 7th, 2015

Its pretty much easy using loop.

With below loop conditions in transformer.You can easily achieve it

Loop Condition: @ITERATION

praveen

• Nov 25th, 2014

Use pivot stage

Answer Question  Select Best Answer

i have one source file which contains the below data
input column: DOB varchar(8)
20000303
20000409
1999043
1999047
validate the Date if valid date else pass the default-1999-12-31,convert varchar to date

Sam

• Dec 17th, 2014

Hi, Check for valid date as below: 1.> Len of the string =8 then Date < sysdate else default date 2.> We use a) "Iconv" function - Internal Convertion. b) "Oconv" function - Exter...

sonali

• Dec 6th, 2014

In transformer stage use stringtodate(datecol,format)

Answer Question  Select Best Answer

What is data granularity? Explain.

Nutan Raut

• Dec 15th, 2014

High level of detail-low level of granularity.& low level of detail-high level of granularity.

pari

• Apr 26th, 2014

Explain data granularity and how it is applicable to data ware house?

Answer Question  Select Best Answer

Having single input in the source wants the repetition of the input in the column of the target using only copy stage.

@sheesh

• Dec 4th, 2014

Use entire partition and run on multiple nodes

Answer Question  Select Best Answer

What are the Datastage native operators??
C
C++
BASIC
OSH

Could you brief this please

srinivas

• Nov 26th, 2014

OSH (orchestrate ) is the answer , when we develop any parallel job it converts into OSH script in the back ground , of course C++ because when we use transformer stage the code generated in the background is C++.

Answer Question  Select Best Answer

If we increase the nodes wt will happens?

barak

• Nov 21st, 2014

Increasing node will not always increase speed it might create lock for example in mass update .

Indudhara V

• Oct 8th, 2014

Node is just a process. Its a logical thing used to increase the efficiency of the jobs by running them in parallel just like multi processing in operating system. Each node processes may run on the same processor or different processors.

Answer Question  Select Best Answer

Say I have 5 rows in a source table ‎and for each rows 10 rows matching in a lookup table and my range is for lookup is 9 to 99. what will be the row count in output table?

barak

• Nov 21st, 2014

That depends on , if you set up "more than one row match" you will get you "Cartesian Product"

ramireddy

• Oct 17th, 2014

You will get same no of records in the source even there are duplicates in look up data

Answer Question  Select Best Answer

I have table(Emp) with the columns Eid,Ename,Sal,month(sal),year(sal) and DOB (say 15th-Jan-1981).
Design a job such that the output displays Ename,year(sal),tot(sal) and current age i.e. For Ex: 18 yrs

barak

• Nov 21st, 2014

Using Transformer Days Between and divided by 365 - we can check for year with 366 if that's a big deal .

Answer Question  Select Best Answer

Suppose i am having a source file and 3 output tables and I want first row to be written to first table second row to second table, third row to third table likewise how can we achieve this using datastage without using partitioning?

Dheerendra

• Nov 14th, 2014

Use SEQ---> Transformer stage ----> 3 SEQ Files In transformer stage add constraints as Mod(@INROWNUM,3) =1, Mod(@INROWNUM,3) =2, Mod(@INROWNUM,3) =0

Pratheeksha

• Nov 12th, 2014

For this, one option could be :
Get the row number column from the source seq file, in the transformer use stage variable which is defined as below:

if mod(i/p col,3)=0 then
if mod(i/p col,2)=0 then
"2"
else
"3"
else "1"

Answer Question  Select Best Answer

How can we identify updated records in datastage? Only updated records without having any row-id and date column available.

Vikram Singh

• Nov 11th, 2014

I believe you can use the change capture stage which will show you the data before any update change made and after the update change was made. Should work, if there are no other constraints.

Answer Question  Select Best Answer

what is slowly changing dimension(SCD)? How do we rectify it in datastage.

sabz R

 Profile Answers by sabz R Questions by sabz R

• Nov 7th, 2014

We can use surrogate key generator to rectify this problem in datastage..

sabz

• Nov 5th, 2014

SCD is a common problem particular to database.This applies to cases where the attributes for a record varies over time.This can be solved by 3 types.type1,type2,type3

Answer Question  Select Best Answer

How we read comma delimiter file in sequential file stage and how we can remove the header and footer from a comma delimiter file?

deepakatsit

 Profile Answers by deepakatsit Questions by deepakatsit

• Oct 29th, 2014

For Removing Header file you can use SED Command of UNIX to remove Header.

deepakatsit

 Profile Answers by deepakatsit Questions by deepakatsit

• Oct 23rd, 2014

For Sequential file there is an option "First Row Column Name" set to true so Header is removed and File End Type You need to select through which Footer is Removed.
Now For Comma Delimiter Use : :"Field Defaults -> Delimiter = Comma"

Answer Question  Select Best Answer

i have a sequence of job in DataStage which is taking more than 4 hrs which is supposed to complete in less than 1 hr
so what could be the possibilities to take much longer time than expected?

RamyaSujith

• Oct 6th, 2014

Check if any stage is reading/processing data sequentially which could have been done in parallel.

Raveena Mittal

• Sep 11th, 2014

When sometimes ,some DML statements are executed in the db and they are not committed,then also it may keep the job running for long hours

Answer Question  Select Best Answer

pundlik

• Sep 5th, 2014

Compared to Informatica the Data Stage is more User friendly

sunrays_arun

 Profile Answers by sunrays_arun Questions by sunrays_arun

• Oct 26th, 2007

SAS DI studio is best when compared to Informatica and Datastage as it generates SAS code at the back end .SAS is highly flexible compared to other BI solution.

Answer Question  Select Best Answer

How to get a files from different servers to one server in datastage by using unix command?

Murali

• Aug 19th, 2014

Code
scp test.ksh dsadm@10.87.130.111:/home/dsadm/sys/

Answer Question  Select Best Answer

Ashok

• Aug 19th, 2014

You can display files date wise by doing normal ls -latr cmd

Answer Question  Select Best Answer

I have explored all the available functions in the transformer stage, but could not find the exact function to get the last day of the current month. Can you please show me which function is available for this logic.

Arunjith B Indivar

 Profile Answers by Arunjith B Indivar Questions by Arunjith B Indivar

• Jul 27th, 2014

DaysInMonth(CurrentDate())

venueksh

 Profile Answers by venueksh Questions by venueksh

• Apr 8th, 2014

Oracle has LAST_DAY function:

SELECT LAST_DAY(to_date(07/04/2014,MM/DD/YYYY)) from dual;
SELECT LAST_DAY(SYSDATE) from dual;

Answer Question  Select Best Answer

Routines are stored in the Routines branch of the DataStage Repository, where you can create, view or edit. The following are different types of routines:   1) Transform functions   2) Before-after job subroutines   3) Job Control routines

Chalapathirao Maddali

• Jul 11th, 2014

Datasatge has 2 types of routines ,Below are the 2 types. 1.Before/After Subroutine. 2.Transformer routines/Functions. Before/After Subroutines : These are built-in routines.which can be called in...

bvrp

 Profile Answers by bvrp Questions by bvrp

• Nov 1st, 2005

RoutinesRoutines are stored in the Routines branch of the DataStage Repository,where you can create, view, or edit them using the Routine dialog box. Thefollowing program components are classified as ...

Answer Question  Select Best Answer

Ramyapriya Sudhakar

• Jul 9th, 2014

operational data store : It is unlike real EDW ,data is refreshed near real time and used for routine business activity. It is used as an interim logical area for data warehouse. This is the pla...

Dharmendra

• Sep 22nd, 2006

An operational data store (or "ODS") is a database designed to integrate data from multiple sources to facilitate operations, analysis and reporting. Because the data originates from multiple sources,...

Answer Question  Select Best Answer

What is the purpose of using user defined environment variables and parameter sets. I m little bit confused. Could any one explain me in detail.?

Charmi

• Jul 1st, 2014

Hi,

Parameter Set is used when you want a set of user defined variables to be used many times in a project.

For example, Variables like Server Name, User Id, Password can be added in a parameter set and that can be used across the jobs , instead of including the three variables everytime.

Answer Question  Select Best Answer