Datastage Stuff

Scenario13

noreply@blogger.com (Anonymous) — Mon, 2 Apr 2012 11:40:00 +0530

source has 2 fields like

COMPANY             LOCATION
IBM                  HYD
TCS                  BAN
IBM                  CHE
HCL                  HYD
TCS                  CHE
IBM                  BAN
HCL                  BAN
HCL                  CHE

LIKE THIS.......

THEN THE OUTPUT LOOKS LIKE THIS....

Company loc count

TCS HYD 3
       BAN
         CHE
IBM HYD 3
         BAN
         CHE
HCL HYD 3
       BAN
         CHE

Performance Tuning techniques in datastage

noreply@blogger.com (Anonymous) — Mon, 12 Mar 2012 12:16:00 +0530

Apply the filter conditions at source level to avoid unnecessary data flow in the job design.
Extract the only required columns from source.
Create large no.of datastage jobs with less no.of stages rather then less no.of jobs with more no.of stages.
Use dataset stage to store temporary data.
Avoid using the transformer stage as much as possible because it needs to have additional C++ compiler to execute the ETL Program.
Insted of using Transformer stage use copy stage to drop the unwanted columns or to send it to multiple stages.
use filter stage to apply conditions.
use surrogate key generator stage to generate sequence number.
prefer database sorts than data stage sorts.
use transformer stage to avoid aggregator stage.

Some useful tips of how to debug parallel jobs in datastage.

noreply@blogger.com (Anonymous) — Sat, 3 Mar 2012 00:52:00 +0530

Enable the following environment variables in DataStage Administrator:
* APT_PM_PLAYER_TIMING – shows how much CPU time each stage uses
* APT_PM_SHOW_PIDS – show process ID of each stage
* APT_RECORD_COUNTS – shows record counts in log
* APT_CONFIG_FILE – switch configuration file (one node, multiple nodes)
* OSH_DUMP – shows OSH code for your job. Shows if any unexpected settings were set by the GUI.
* APT_DUMP_SCORE – shows all processes and inserted operators in your job
* APT_DISABLE_COMBINATION – do not combine multiple stages in to one process. Disabling this will make it easier to see where your errors are occurring.
Use a Copy stage to dump out data to intermediate peek stages or sequential debug files. Copy stages get removed during compile time so they do not increase overhead.
Use row generator stage to generate sample data.
Look at the phantom files for additional error messages: c:\datastage\project_folder\&PH&
To catch partitioning problems, run your job with a single node configuration file and compare the output with your multi-node run. You can just look at the file size, or sort the data for a more detailed comparison (Unix sort + diff commands).

What are the Main Features in DataStage

noreply@blogger.com (Anonymous) — Sat, 3 Mar 2012 00:22:00 +0530

DataStage has the following features to aid the design and processing required to
build a data warehouse:
• Uses graphical design tools. With simple point-and-click techniques you
can draw a scheme to represent your processing requirements.
• Extracts data from any number or types of database.
• Handles all the meta data definitions required to define your data warehouse. You can view and modify the table definitions at any point during
the design of your application.
• Aggregates data. You can modify SQL SELECT statements used to extract
data.
• Transforms data. DataStage has a set of predefined transforms and functions you can use to convert your data. You can easily extend the functionality by defining your own transforms to use.
• Loads the data warehouse

Scenario12

noreply@blogger.com (Anonymous) — Tue, 28 Feb 2012 23:10:00 +0530

There are 5 columns in a flat file, How to read 2nd and 5th columns only..

Scenario11

noreply@blogger.com (Anonymous) — Tue, 28 Feb 2012 23:08:00 +0530

Difference between Trim, Trim B, Trim L, Trim T, Trim F, Trim Leading Trailing, Strip White Spaces..

Scenario10

noreply@blogger.com (Anonymous) — Tue, 28 Feb 2012 23:05:00 +0530

How to abort the job after 50 rows are processed (assume there are 500 records available in a flat file)..

Scenario9

noreply@blogger.com (Anonymous) — Tue, 28 Feb 2012 23:02:00 +0530

How many ways are there to perform remove duplicates function with out using Remove duplicate stage..

Scenario8

noreply@blogger.com (Anonymous) — Sat, 25 Feb 2012 12:15:00 +0530

How to find out First sal, Last sal in each dept with out using aggrigater stage

Scenario7

noreply@blogger.com (Anonymous) — Sat, 25 Feb 2012 12:12:00 +0530

How to calculate Sum(sal), Avg(sal), Min(sal), Max(sal) with out using Aggrigator stage..

Scenario6

noreply@blogger.com (Anonymous) — Sat, 25 Feb 2012 12:08:00 +0530

Input is like this:
file1
1
2
3
4
5
6
7
8
9
10

Output is like:
file2(odd) file3(even)
1    2
3                      4
5                      6
7                      8
9                      10

Scenario5

noreply@blogger.com (Anonymous) — Sat, 25 Feb 2012 12:04:00 +0530

Input is like this:
file1
10
20
10
10
20
30

Output is like:
file2 file3
10 30
20

Scenario4

noreply@blogger.com (Anonymous) — Fri, 17 Feb 2012 00:34:00 +0530

Input is like:
file1
10
20
10
10
20
30

Output is like Multiple occurrences in one file and single occurrences in one file:
file2 file3
10 30
10
10
20
20

Scenario3

noreply@blogger.com (Anonymous) — Fri, 17 Feb 2012 00:27:00 +0530

Input is like this:
file1
10
20
10
10
20
30

Output is like:
file2 file3(duplicates)
10 10
20 10
30 20

Scenario2

noreply@blogger.com (Anonymous) — Fri, 17 Feb 2012 00:00:00 +0530

input is like this:
no,char
1,a
2,b
3,a
4,b
5,a
6,a
7,b
8,a

But the output is in this form with row numbering of Duplicate occurence

output:

no,char,Count
"1","a","1"
"6","a","2"
"5","a","3"
"8","a","4"
"3","a","5"
"2","b","1"
"7","b","2"
"4","b","3"

Scenario1

noreply@blogger.com (Anonymous) — Thu, 16 Feb 2012 23:43:00 +0530

Input is like this :
i/p
col1,col2
101,a
102,b
103,c
104,d

Output need to be:
o/p
col1,col2
101,d
102,c
103,b
104,a

Differences between Filter & Switch

noreply@blogger.com (Anonymous) — Wed, 15 Feb 2012 00:58:00 +0530

Filter:

It receives the data from one input link and send it to any no.of out put links.
It filters the records based on one or more where clause conditions.
Here every incoming record checks all the where clause conditions. So one input record goes to multiple output links.
Fallowing keywords are valid in defining where clause conditions =,<,>,<>,<=,>=,AND,OR,NOT,BETWEEN
Output Reject = True or False.

Switch:

It receives the data from one input link and send up to 128 output links.
It filters the records based on one or more case conditions.
Here every incoming record goes from one case condition only.
It supports only for equality operator.
If Not Found =Drop / Fail / Output.

Difference between Lookup, Join and Merge

noreply@blogger.com (Anonymous) — Tue, 14 Feb 2012 01:01:00 +0530

Lookup stage supports:

One stream input link
'n' reference links(n=1 to ...)
one output link
one optional reject link

Join stage supports:

one Left set
one Right set
'n' intermediate links(n=0 to ....)
one output link
no reject links

Merge stage supports:

one Master link
n-update links(n=1 to ...)
m-optional Reject links(m=0 to n)
one output link

In Lookup we can apply Conditions. But in Join & Merge not possible to apply Conditions
In Lookup joining columns may not be with same name. But in Join & Merge Joining columns must be with same name