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:

Oracle table is Partitioned or Not ?

For getting whether Oracle table is Partitioned or not, we can use below query on ALL_TABLES -

Things need to consider while developing a Datastage job

Datasets
Datasets are the best when storing the results intermediately. Datasets will keep the partitions and sort order if set. This will save re-partitioning, sorting and would make the job more robust.

Performance of the job can be improved if:
1) Unnecessary column are removed from the up and down stream links.
2) Removing these unnecessary columns will help reducing the memory consumption.
3) Always specify the list of columns in the select statement when reading from database. This will not bring unnecessary column data in the job which will save memory and network consumption.
4) Use RCP very carefully.
5) Understand the data-type before using them in the job. Do the data profiling before bringing data in the job.

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.

Datastage Common Errors and Solutions

1.     While running ./NodeAgents.sh start command... getting the following error: “LoggingAgent.sh process stopped unexpectedly”

SOL:   needs to kill LoggingAgentSocketImpl

              Ps –ef |  grep  LoggingAgentSocketImpl   (OR)

              PS –ef |               grep Agent  (to check the process id of the above)

2.     Warning: A sequential operator cannot preserve the partitioning of input data set on input port 0

SOL:    Clear the preserve partition flag before Sequential file stages.

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

Head Stage in DataStage

Welcome to Basic Intro with Stage Series, We are going to look into HEAD stage ( Developmet/Dubug Categoty).  It can have a single input link and a single output link. 

The Head Stage selects the first N rows from each partition of an input data set and copies the selected rows to an output data set. You determine which rows are copied by setting properties which allow you to specify:

• The number of rows to copy
• The partition from which the rows are copied
• The location of the rows to copy
• The number of rows to skip before the copying operation begins.

Why Entire partition is used in LOOKUP stage ?

Entire partition has all data across the nodes So while matching(in lookup) the records all data should be present across all nodes.

14 design tips for better performance in Datastage

1) Avoid unnecessary type conversions: set the OSH_PRINT_SCHEMAS environment variable to verify that run time schemas match the job design column definitions. If you are using stage variables on a Transformer stage, ensure that their data types match the expected result types.

2) Use Transformer stages sparingly and wisely. Transformer stages can slow down your job. Do not have multiple stages where the functionality could be incorporated into a single stage, and use other stage types to perform simple transformation operations