Question { 11271 }

Hi all, can u explain header and trailer records in file?
If a file has header and trailer /master-detail records how
will you read it in datastage?

Answer

Header: is nothing but Column names/Structure of the table
trailer: is something like FOOTER of the page/file

if file has Header, while reading the file set 'TRUE' for
'First Line is Column names'--

If set true, the first line of a file contains column names on
writing and is ignored on reading.

Question { TCS, 7358 }

A flat file contains 200 records. I want to load first 50
records at first time running the job, second 50 records at
second time running and so on, how u can develop this job?

Answer

1st Way:
1. Add 'row number' column in Seq File stage, so that each
record has a number associated with it.
2. Add a job param with which we can provide the number of
record from where we want to run the job. We can pass this
either using Sequence Start LOOP(List type variables-
50,100,150,200) or by shell script.
3. In the tfm, use a stage variable to run only from the
record number till 50 records by counting each record.

2nd way:
Design the job like this:
1. Add 'row number' column in Seq File stage, so that each
record has a number associated with it.
2. Use filter stage and write the conditions like this:
a. row number column<=50(in 1st link to load the records
in target file/database)
b. row number column>50 (in 2nd link to load the records
in the file with the same name as input file name, in
overwrite mode)


So, first time when your job runs first 50 records will be
loaded in the target and same time the input file records
are overwritten with records next first 50 records i.e. 51
to 200.
2nd time when your job runs first 50 records(i.e. 51-100)
will be loaded in the target and same time the input file
records are overwritten with records next first 50 records
i.e. 101 to 200.
And so on, all 50-50 records will be loaded in each run to
the target

Question { TCS, 5653 }

I am having two tables called MASTER and DETAIL. I want to
insert records to both tables. But one condition is that
whenever the insert for MASTER table is success then only
the records will inserted into the DETAIL table, otherwise
abort the job. How can u design this job?

Answer

First Load the MASTER table.
Next, Load the DETAIL table with 'look up' on MASTER table.
In the look up, set the look up condition as 'FAIL'.

For each record from SOURCE, If the look up record is not
loaded into the MASTER table.. the job will 'Abort' on look up
failure.

Question { IBM, 10004 }

HOW U CAN ABORT THE JOB IF THE DATA IS DUPLICATE?

Answer

SRC--->SORT--->TFM--->TGT

In the Sort Stage, mention the Sort Keys and Set
CreateKeyChangeColumn to TRUE. for each 1st of Key column,
value of the KeyChange column is '1' and for the duplicate
columns, value is '0'.
In the TFM Constraints, give constraint as 'KeyChange=0' and
set '1' for 'Abort After Rows' then the job will abort for 1st
duplicate record.

Question { TCS, 30684 }

how can we send even and odd records from a sequential file
to two different targets

Answer

SeqFile--->Transformer=====>TGTFile1,TGTFile2

1. in SeqFile, add the 'Row Number Column' property and
name it as 'Row_Number'
2. in the Transformer, give the constraint for TGTFile1
link:
'MOD(Row_Number,2)=1'----> all the ODD rows
give the constraint for TGTFile2 link:
'MOD(Row_Number,2)=0'----> all the even rows.


If we use INROWNUM in transformer, then we need to run the
transformer on 'Single' NODE configuration.

Question { IBM, 11145 }

which memory is used by lookup and join

Answer

-->LOOK-UP runs based on physical memory(RAM).

While doing Look-Up, Data will be loaded into the Physical Memory(RAM) which faster and better for LOW Valume of data.


-->JOIN Uses the Buffer Memory(disc memory)

Buffer Memory(disc memory)--> this path is specified in the Configuration file and parameter name is: SCRATCH DISK path

Question { Cap Gemini, 13567 }

how to transfer file from one system to another system in
unix? which cmd to be use?

Answer

using FTP:
1. FTP IP addr(remote machine IP address)
2. then cd PATH(to/from which directory, we need to copy
file)
3. lcd PATH(lcd-Locaal machine directory, to/from which
directory, file to be placed or selected)
4. PUT/GET file_name
If we want put file in remote machine, use PUT file_name
If we want get the file from remote machine, use GET
file_name.

Question { 8351 }

insequential file 2header avaliable,and 100 records
avaliable.how to skip the 2 headers and load 100 records
target?

Answer

sed -n '3,100p' filename
or
sed -n '1,2d' filename
Or
tail -98 filename

Question { TCS, 10638 }

it is possible to load two tables data into one sequential
file?if possible how?plz share with me?

Answer

Lets discuss diff cases:
1. If the two tables having same key column,
then based on that key column we can able join the two
tables. It's horizantal joining.
Ex: Table1 has 10 columns +1Key col
Table2 has 5 columns +1Key col
in target SeqFile we get 10+5+1 columns

2. If the two tables having same metadata,
Then using funnel stage we can perform vertical joining.
whether it may continuous or sequence funnel.
In this case we can add extra columns 'INDICATOR'(1 for
Table1 and 2 for Table2 records in the select query) to
identify records

3. If the two tables not having same metadata & Key column,
We can generate 1 common Key column from Column Generator
Stage- 'DUMMY'(values: 1,2,3,4,...) for 2 tables
Then, we can join based on DUMMY column to get all the
columns from the both tables and load those columns to
SeqFile.

Question { 5937 }

i have source like
balance,drawtime
20000, 8.30
50000,10.20
3000,4.00

i want target like this
balance,drawtime
20000, 20.30
50000,22.20
3000,16.00

Answer

SRC--->Transformer--->TGT

In the TFM Output Columns Derivations:


(HoursFromTime((MyLnk.drawtime:"00"))+12): MinutesFromTime(MyLnk.drawtime)--->OutputColumn

(MyLnk.drawtime:"00")--->Convert Source Format to %hh:%nn:%ss
(HoursFromTime((MyLnk.drawtime:"00"))+12)--->Adding 12 hours to hours from source and Concatanating with Minutes from Source.

Question { IBM, 12842 }

how can find maximum salary by using Remove duplicate stage?

Answer

For RMD Stage we need Grouping Keys, Add one extra hard coded Column to each Input records in previous stage.
DUMMY=1

In RMD Stage,
HASH partition on DUMMY, Sort DESC on Salary

and select DUMMY as Key in properties tab.
"Duplicate to Retain"=First.

then you will get only Highest Salart only from RMD Stage

Question { TCS, 23972 }

How to read multiple files at different directories using a
single Sequential File stage in DS Enterprise Edition

Answer

If all files having same Metadata,

Select Read method as 'specific file(s)'
then u Can add as many files as you want to run by selecting 'file' property from the 'available properties to add'.
It locks like below:

File= /dsg/work/temp/Employee1.txt
File= /dsg/work/Employee2.txt
File= /dsg/Employee3.txt
Read Method= Specific file(s)

Question { TCS, 12013 }

i have seq file that contents 10 million records load to target any data base..
in that case it takes lot of time for loading..how do performance tuning in that situation...?

Answer

1.Increase the 'Numbers Readers per Node' to '2' in source file.
2. Use the Bulk Load Option in Target Oracle Connector.
3. set option Re-Build Index option, so that it will drop indexes before loading and It will create the indexes once load is completed.
4. Increase the 'Record count'(set 10,000, should be multiple of Array size) in TGT.
5. Increase 'Array size'(set 5,000, default is 2,000) in TGT.
6. SRC---->COPY---->TGT,
use COPY stage, so, it work like buffer whenever there is diff in number records processing at source side and at target side.

Question { TCS, 12013 }

i have seq file that contents 10 million records load to target any data base..
in that case it takes lot of time for loading..how do performance tuning in that situation...?

Answer

One more Option is split the file into small files and Load
all files and send those to funnel to combind.

Question { IBM, 4808 }

if we using two sources having same meta data and how to
check the data in two sources
is same or not? and if the data is not same i want to abort
the job ?how we can do this?

Answer

SeqFile1----->|
.................|ChangeCapture--->Transformer--->TGT
SeqFile2----->|

SeqFile1 & SeqFile2 are the inputs to Change Capture.
From the output Change Capture Stage we one extra column
‘Change Code’.
If, the record is same in both file, i.e. Copy Record.
Change Code=0
If, the record is only in SeqFile1, i.e. insert Record.
Change Code=1
If, the record is only in SeqFile2, i.e. delete Record.
Change Code=2
If, the record is in both files but with some modifications,
i.e. edit/update Record. Change Code=3

If all Change are '0' then 2 files having same Data.

In the transformer constraints, we can mention as ‘ change
code <>0’ then set ‘Abort After Rows’ to ‘1’.