Types of Jobs in DataStage

Server jobs

• Executed by the DataStage server engine
• Compiled into Basic
• Runtime monitoring in DataStage Director
• No parallel capability

Maximum number of DataStage Jobs in a project

Filesystems can have a hard limit on number of subdirectories that can be contained in a directory, for example 32766 (32K -2) for Linux

For every job, DataStage will create hash files as subdirectories in the project directory. Here is a list of the subdirectories created for every job. In this list "nn" represents the number (or id) of the job:

DS_TEMPnn

RT_CONFIGnn

RT_STATUSnn

RT_LOGnn

RT_BPnn

RT_BPnn.O

RT_SCnn (only for parallel jobs)

Unlocking XMETA locks from jobs

How can I unlock an XMETA locked job?

The job cannot be exported and when attempting to open the job, the following error appears:

Unable to open the Job
The Job 'TestJob' is locked by user 'dsadm'
(SessionID '8F1B0326-4F7E-45A8-B8EC-EAA8C4780528')

Do you want to continue and open a read-only view of the Job?

This issue arises when a lock still remains in XMETA for the corresponding job. Information Server will lock the job to ensure that there is no indeterminancy regarding the editing and saving of a job.
The most common causes of this is when a user has closed a client window unexpectedly (e.g. Force Terminate).

Trigger types in Sequencer Job

While designing Sequencer jobs when we are using any Sequencer Stage ( Job activity, Command activity or others ), it has a tab named 'trigger' which actually determine the actions that occur when the activity runs. Each activity can output different trigger types. 

Three types of triggers are available, with some types having subtypes.

Conditional

A conditional trigger runs the target activity if the source activity fulfills the specified condition. The condition is defined by an expression, and can be one of the following types:

Script to UnLock DataStage Jobs

This script will help you to unlock the all jobs, if you are a DS admin, or your datastage jobs , if a DS developer, without doing human error while executing command on Datastage server UV shell or Datastage Administrator Client.

Script is quite simple, Execute it , it will ask for Project Name and Job Name and Done if you have appropriate access on Datastage server.

May be need a modification as per your Datastage platform or if you need some fancy stuff to add on.  Happy Scripting !!

Get Datastage Job Lock List by Script

** This script will fetch all locks currently on Datastage Server irrespective of Projects.
** We can modify this script to correct the data display format as per our need
** Can modify sort command as it changes its behavior in different Linux flavour

Read Sequential File with SCHEMA file

In Datastage, Schemas are an alternative way for you to specify column definitions for the data used by parallel jobs. You do not need to define any column and data format for reading a file. We are using this method when we needed a generic job for scenario like reading multiple files having different metadata defined.

Here, I am going to show you how to read the seq file with help of schema

A. Design

I am using below design to demonstrate this functionality, I am reading the data and dumping it to another file with different format.

Create Fixed Width Data in DataStage

In day to day life of DataStage, you are facing lot of business scenario and technical scenario. Today, we are going to discuss how to generate Fixed Width data from variable length data in datastage.


Input :
23
3345
546576
535
80
2342


Output :
0000023
0003345
0546576
0000535
0000080
0002342

DataStage Warning : When checking operator: Operator of type "APT_TSortOperator": will partition despite the preserve-partitioning flag on the data set on input port 0

DataStage Warning : When checking operator: Operator of type "APT_TSortOperator": will partition despite the preserve-partitioning flag on the data set on input port 0.

These warnings are obtained when we are using some specific partitioning method in a stage in the job e.g hash/same etc but in the preceding stage , preserve portioning(also default) is set in the stage>advanced tab.

DataStage Warning - Agg,1: Hash table has grown to 16384 entries

Sometime when we are running Datastage job in which we are using Aggregator stage, we get below warning -

Agg,1: Hash table has grown to 16384 entries.

Agg,0: Hash table has grown to 16384 entries. 


Solution :

Read Fixed Width Flat File with Sequential File Stage

If we have a fixed width Flat file, we can read it via Sequential File stage only, no need to use Import Stage.   For reading a Fixed Width Flat file, you should know the format of Fixed width data. How are the data splitting into two or more output columns.

Here, I demonstrate How to read a Fixed Width Flat file from Sequential File Stage

A. Input File :

Below is the Input file which we are going to use in our job. This file contains a column RollNo which is concatenation of 4 fixed width columns which we have to split and read.

Peek Stage in DataStage - 3

In today's post, we will discuss a important property of PEEK stage. What if you want to peek some data as well as want to take action or wanna do something with data after peeking. For enabling this, datastage gives you a option to do this.

1. Job Design :

for this demo, we will use below job design to explain, as you can see, There are 2 link coming out from PEEK stage. 

First Link  --> This link contain the PEEK data as per property defined in peek stage. (here you can see 40 rows ( 10 rows from each partition x 4 nodes )

Second Link --> this link contain the Whole stream data.  ( All 100 rows )

Some DataStage Job design

Updating the Master Reports List (MRL) in Datastage

Create a Job which has a Row Generator stage linking to an ODBC or DB2 stage. Change the parameter in the Row Generator stage to output zero records. Write the SQL to update the MRL table as 'After SQL' in the output ODBC or DB2 stage.

DataStage Scenario - Design9 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem9

Solution Design :

a) Job Design :   
Below design will achieve the output as per our requirement.

Count Rows and Calculate Sum in same Aggregator

Generally, we are using Fork n Join method ( divide the data into 2 links which goes to 2 Aggregator ) to calculate Sum and Count Rows of Data because Aggregator Stage is providing one type of aggregation at a time, You can Count the rows or can do some other aggregation.
            Here, I have describe a way in which we can Count Rows and Calculate Sum in a Single Aggregator Stage with some limitations :-)

Design :

As per design, we used Column Generator and Aggregator Stage to calculate and count.

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".

Surrogate Key Generator - Delete State File

Adding this post as well with Surrogate Key walk through, In this post we will see how to delete a Surrogate Key State file. Its quite simple as creation.

a) Design :
Again, for the deletion of state file, we need only 1 stage in design and that is Surrogate Key Stage.

Surrogate Key Generator - Create/Update State File

In last post ( Surrogate Key Generator - Create State File ) we have seen how to generate the surrogate key file, In this post we will discuss about what are the other ways to generate it and how to update state file

Update Surrogate State File (Flat File) :-
To update the state file, add a Surrogate Key Generator stage to a job with a single input link from another stage.

 

Surrogate Key Generator - Create State File

Surrogate keys are numeric keys that can replace business keys in the datawarehouse.  A surrogate key has the following characteristics:

1) It is typically an integer.
2) Surrogate keys are unique.
3) Surrogate keys allow you to combine data from tables with incompatible keys.
4) It has no meaning. You will not be able to know the meaning of that row of data based on the surrogate key value.
5) It is not visible to end users. End users should not see a surrogate key in a report.

I will not go in details of surrogate key, I will post it later, But we will see how to generate surrogate keys in DataStage.

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.