Disable auto insertion of Partition and Sort

Partitioner insertion and sort insertion each make writing a flow easier by alleviating the need for a user to think about either partitioning or sorting data. By examining the requirements of operators in the flow, the parallel engine can insert partitioners, collectors and sorts as necessary within a data flow.

However, there are some situations where these features can be a avoided or not needed.
If data is pre-partitioned and pre-sorted, and the InfoSphere DataStage job is unaware of this, you could disable automatic partitioning and sorting for the whole job by setting the following environment variables while the job runs:

Windows Command Prompt setup for DataStage Command Line

As we know that for using Datastage Command Line in Linux system, we need to setup linux shell session likewise we need to do same for Windows if server is installed on Windows server.

Follow below steps to setup your Datastage command Line ---

Linux Shell session setup for DataStage Command Line

To setup Linux Shell for Datastage command line is very important if you are a DataStage Developer and want to work hassle free on DataStage Command Line.

Follow below steps to setup your profile ---

a) First, we will create .dshome file which is containing the path of DS Engine. If you know what it is, good, create a file in root dir with DS Engine path
otherwise execute below scripts to do it by itself

APT_CONFIG_FILE : Configuration File

APT_CONFIG_FILE is the file using which DataStage determines the configuration file (one can have many configuration files for a project) to be used. In fact, this is what is generally used in production. However, if this environment variable is not defined then how DataStage determines which file to use?

1)If the APT_CONFIG_FILE environment variable is not defined then DataStage look for default configuration file (config.apt) in following path:

1)Current working directory.

2)INSTALL_DIR/etc, where INSTALL_DIR ($APT_ORCHHOME) is the top level directory of DataStage installation.

What are the different options a logical node can have in the configuration file?

Performance Tunings in DataStage

JOB LEVEL

• Parametrize all the inputs needed for the jobs; avoid hard coding of parameters like username, password, directory paths etc.
• By using the environment variable ‘APT_CONFIG_FILE’ as a job parameter, user can dynamically change the number of nodes being used for processing a particular job.
• For reading/writing data from large tables/files, make use of the environment variable ‘APT_BUFFER_MAXIMUM_MEMORY’. It can be used to change the memory buffer size being used for each stage.
• It is recommended to set the environment variable $APT_DUMP_SCORE to a value of 1. When this environment variable is set, an entry is placed in the WebSphere DataStage job log showing the actual runtime structure (processes, their associated internal operators, datasets, nodes, etc) used to execute the job flow.

Datastage Coding Checklist

1. Ensure that the null handling properties are taken care for all the nullable fields. Do not set the null field value to some value which may be present in the source.
2. Ensure that all the character fields are trimmed before any processing. Normally extra spaces in the data may lead to some errors like lookup mismatch which are hard to detect.
3. Always save the metadata (for source, target or lookup definitions) in the repository to ensure re usability and consistency.

How to find Agents Ports in IBM InfoSphere Server - Version.xml

The list of installed products can be obtained from the Version.xml file that is located in ISHOME directory on the client, engine and domain machines where InfoSphere Information Server is installed.

Default locations

UNIX /opt/IBM/InformationServer,
WINDOWS C:\IBM\InformationServer

Below is a example listing of the Version.xml file:

How to find the product installed with IBM Infosphere Server - Version.xml

The Installed product details we can get from Version.xml file, this file contains all the information about Server Installation, version etc.

Version.xml file is existing in $ISHOME path. So

 a) cd $ISHOME
 b) If this will not work, try below steps to get the DSEngine dir
    
 Execute below command to get ..
 cd `ps -ef| grep dsrpc | grep -v grep | awk '{print $NF}' | sed 's/.\{26\}$//'`

List of Environment Variables in DataStage

General Job Administration

APT_CHECKPOINT_DIR
APT_CLOBBER_OUTPUT
APT_CONFIG_FILE
APT_DISABLE_COMBINATION
APT_EXECUTION_MODE
APT_ORCHHOME
APT_STARTUP_SCRIPT
APT_NO_STARTUP_SCRIPT
APT_STARTUP_STATUS
APT_THIN_SCORE

How to setup environment variables in DataStage

In DataStage you can set environment variables using three different methods. The right method depends on how often you need to change the value of an environment variable and how specific this value is for a particular project or job. Here is a description of these methods:

Error : Datastage Job Aborts with "The record is too big to fit in a block"

To fix this error you need to increase the block size to accommodate the record size:

1. Log into Designer and open the job.

2. Open the job properties--> parameters-->add environment variable and select: 
   APT_DEFAULT_TRANSPORT_BLOCK_SIZE

ETL Job Design Standards - 1

When using an off-the-shelf ETL tool, principles for software development do not change: we want our code to be reusable, robust, flexible, and manageable. To assist in the development, a set of best practices should be created for the implementation to follow. Failure to implement these practices usually result in problems further down the track, such as a higher cost of future development, increased time spent on administration tasks, and problems with reliability.

Although these standards are listed as taking place in ETL Physical Design, it is ideal that they be done before the prototype if possible. Once they are established once, they should be able to be re-used for future increments and only need to be reviewed. 

Listed below are some standard best practice categories that should be identified on a typical project. 

Create a unique counter in datastage

This entry describes various ways of creating a unique counter in DataStage jobs.
A parallel job has a surrogate key stage that creates unique IDs, however it is limited in that it does not support conditional code and it may be more efficient to add a counter to an existing transformer rather than add a new stage.

In a server job there are a set of key increment routines installed in the routine SDK samples that offer a more complex counter that remembers values between job executions.
The following section outlines a transformer only technique.

How can I run the osh command of the InfoSphere Parallel Engine?

The osh command is the main program of the InfoSphere Parallel Engine. This command is used by DataStage to perform several different tasks including parallel job execution and dataset management. Normally, there is no need to run this command directly but sometimes it is useful to use it for troubleshooting purposes.

To run this command there are 3 environment variables that must be set. These are:

1. APT_ORCHHOME should point to Parallel Engine location
2. APT_CONFIG_FILE should point to a configuration file
3. LD_LIBRARY_PATH should include the path to the parallel engine libraries. Please note that the name of this environment variable may take a different name (such as LIBPATH in AIX or SLIB_PATH in HP-UX) depending on your Operating System. Note: This variable does not need to be set in Windows environments.

Buffering in DataStage

Buffering is a technique used in the Datastage jobs to ensure a constant and uninterrupted flow of data to and from stages in such a way that there is no potential dead lock or any fork join problems. It is been implemented in Datastage keeping in mind the fact that the data has to keep moving in the process with an optimized use of the memory in the server. As mentioned by IBM the ideal scenario is when the data flows through the stages without being written on the disk. As in the case of buffering in any system, the upstream operators should to wait for the downstream operators to consume their input before starting to create their records. This is the intention in Datastage too.

DataStage Job aborts with error: "The record is too big to fit in a block"

To fix this error you need to increase the block size to accommodate the record size:

1. Log into Designer and open the job.
2. Open the job properties--> parameters-->add environment variable and select: APT_DEFAULT_TRANSPORT_BLOCK_SIZE

Diagnosing DataStage Job Monitor problems

DataStage job run statistics (i.e. rows per second processed) do not update in DataStage Designer or Director clients.

 

Check these details :

This section contains a quick series of diagnosis steps for those familiar with DataStage and Job monitor. If more detail is needed for any step, please refer to the more detailed instructions in the "Resolving the Problem" section:
If customer has checked and verified that the following:

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.

Tips & Tricks for debugging a DataStage job

The article talks about DataStage debugging techniques. This can be applied to a job which

• is not producing proper output data or
• to a job that is aborting or generating warnings

1. Use the Data Set Management utility, which is available in the Tools menu of the DataStage Designer or the DataStage Manager, to examine the schema, look at row counts, and delete a Parallel Data Set. You can also view the data itself.
   

Specifying C++ compiler settings for DataStage in Windows

Today I am sharing how to specify the C++ compiler settings for DataStage in Windows Environment. From last few days I also face a lot of issue with this :P so thought it need to share with you all.

For :  DataStage 8.x