Execution Steps in Transformer Stage - Explanation

 You can access Part1 Here -  Execution Steps in Transformer Stage

Certain constructs are inefficient if they are included in output column derivations, because they are evaluated once for every output column that uses them. The following examples describe these constructs:

The same part of an expression is used in multiple column derivations.

For example, if you want to use the same substring of an input column in multiple columns in output links, you might use the following test in a number of output columns derivations:

IF (DSLINK1.col1[1,3] = "001") THEN ...

In this case, the evaluation of the substring of DSLINK1.col1[1,3] is repeated for each column that uses it. The evaluation can be made more efficient by moving the substring calculation into a stage variable. The substring is then evaluated once for every input row. This example has thus stage variable definition for StageVar1:

Execution Steps in Transformer Stage

I've been asked this questions so many times in interviews and by different practitioner also that What are the data processing steps when datastage is processing transformer, So here I tried to compiled. Have a look -


To write efficient Transformer stage derivations, it helps to understand what items get evaluated and when.

Surrogate Key Generator - Generate Surrogate Key for Data

In this post, We will see how to generate surrogate key for data, where we have to use surrogate key stage.

A) Design :  
Below design is a demo design of job. Here our data source is a row generator which is generating rows. In real time scenario, Source can be a flat file, DB stages, Passive Stage or can be a Active stage also.
In Row Generator Stage, we are generating a col "Name".

DataStage Scenario - Design8 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem8

Solution Design :

a) Job Design : 
Below is the design which can achieve the output as we needed. Here, we are reading 2 seq file as a input, then data is passing through a Join and Filter stage to achieve the output.

DataStage Scenario - Design7- job1

DataStage Scenario Problem -->  DataStage Scenario - Problem7

Solution Design :

a) Job Design :  Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through a Transformer and Peek stage to achieve the output.

DataStage Scenario - Design6 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem6

  

Solution Design :

a) Job Design :

Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through a Transformer stage to achieve the output.

DataStage Scenario - Design5 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem5

  

Solution Design :

a) Job Design :

Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through a Sort and Transformer stage to achieve the output.

DataStage Scenario - Design4 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem4

Solution Design :

a) Job Design :

Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through a Sort and Transformer stage to achieve the output.

DataStage Scenario - Design3 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem3

Solution Design :

a) Job Design :

Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through a Sort and Transformer stage to achieve the output.

DataStage Parallel job: Retrieve sql codes on a failed upsert

When an enterprise database stage such as DB2 or Oracle is set to upsert it is possible to create a reject link to trap rows that fail any update or insert statements. By default this reject link holds just the columns written to the stage, they do not show any columns indicating why the row was rejected and often no warnings or error messages appear in the job log.

DataStage Scenario - Design2 - job2

DataStage Scenario Problem -->  DataStage Scenario - Problem2

Solution Design :


a) Job Design :
In job design, we are using Copy, Aggregator, Filter and Join stage to get the output.

Sample Stage in DataStage

Sample stage is yet another development stage. It can have a single input link and any number of output links when operating in percent mode and have one o/p link when operating in period mode.

a) Job Design

DataStage Scenario - Problem6

Goal : Get the count of Vowels in Columns

Input :

Akash Aggrawal
Priya Awasthi  
Anil chahal    
Diya Singh    
Kashish Patel 
Sunil Verma    
Rashid Patel    
Rashmi Arya   
Gopal Joshi     
Neha Tomar    

DataStage Scenario - Problem5

Goal : Count the occurrence of char

i/p
1,a
2,b
3,a
4,b
5,a
6,a
7,b
8,a

DataStage Scenario - Problem2

Goal : To get the Unique and Duplicates values from Input Data

 

Input :

There is a input file which contains duplicates data, Suppose :

DataStage Scenario - Problem1

Goal :

Process a text file which contains records arranged in blocks consisting of a header record, details (items, body) and a trailer. The aim is to normalize records and load them into a relational database structure.

Details :

Convert a single row into multiple rows ( horizontally pivoting ) with Pivot stage ?

In this example, the Pivot Enterprise stage is set up to horizontally pivot some data.

You can generate a pivot index that will assign an index number to each row within sets of horizontally pivoted data. The following tables provide examples of data before and after a horizontal pivot operation.


Input Data
REPID,last_name,Jan_sales,Feb_sales,Mar_sales
100,Smith,1234.08,1456.80,1578.00
101,Yamada,1245.20,1765.00,1934.22
102,Xing,2190.89,1287.98,2054.55
103,Anderson,1498.09,1287.23,3298.76

Optimzie your DataStage Job Performance with relevant Environment Variables

DataStage has many parameters which can be tweaked and used to optimize the performance of various DataStage Jobs. Even many available to collect more information during the event of crash to get more traces.

For any DataStage Job if you run into problem or want to get more details need to check following variables.

Start/Stop/Delete a Queue Manager

Starting a Queue Manager

Before we can use a Queue Manager, we need to start it, using the STRMQM command. The command to start a Queue Manager called QMA is:

$ strmqm QMA

You should see output similar to the following on your screen:

WebSphere MQ queue manager 'QMA' starting.

2108 log records accessed on queue manager 'QMA' during the log replay phase.

Log replay for queue manager 'QMA' complete.

Transaction manager state recovered for queue manager 'QMA'.

WebSphere MQ queue manager 'QMA' started.

Create/start/stop a Queue Manager

To create a Queue Manager, our userID needs to be part of the mqm group. If we do not have the appropriate authority, then we need to ask our MQ administrator to issue this command.

The command to create a Queue Manager is CRTMQM and there are various parameters available with this command:

crtmqm [-z] [-q] [-c Text] [-d DefXmitQ] [-h MaxHandles]

[-g ApplicationGroup] [-t TrigInt]

[-u DeadQ] [-x MaxUMsgs] [-lp LogPri] [-ls LogSec]

[-lc | -ll] [-lf LogFileSize] [-ld LogPath] QMgrName