ETL Job Design Standards - 2

Part 1 --> ETL Job Design Standards - 1

Parameter Management Standards

This section defines standards to manage job parameters across environments. Jobs should use parameters liberally to avoid hard coding as much as possible. Some categories of parameters include: 

• Environmental parameters, such as directory names, file names, etc.
• Database connection parameters
• Notification email addresses
• Processing options, such as degree of parallelism

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. 

How can I improve performance during an import into Datastage?

One way to improve performance of an import is to exclude the executables.
Here are three different ways to exclude executables from the import. 

• Exclude the executables from the export.
  In DataStage Designer export using the option export Jobs without executables.
  

• Create an xml file instead of a dsx file.
  Executables are not included in xml files.
  (The xml option is available with dscmdexport but only with /JOB option)
  

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.

How to use ISTOOL for EXPORT IMPORT in DataStage

Location of command:

UNIX: /opt/IBM/InformationServer/Clients/istools/cli
Windows: IBM\InformationServer\Clients\istools\cli



Export command sample:

domain name: SERVER.us.ibm.com
port no: 9080
DS Server name: SERVER
DS Project name: Test

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.

DataStage Naming Conventions

DataStage Naming Conventions follows the guidelines of ETL Naming Conventions.

Contents 1 Job Name Prefixes 2 Stage Names 3 Link Names

Using Configuration Files in Data Stage Best Practices & Performance Tuning

The configuration file tells DataStage Enterprise Edition how to exploit underlying system resources (processing, temporary storage, and dataset storage). In more advanced environments, the configuration file can also define other resources such as databases and buffer storage. At runtime, EE first reads the configuration file to determine what system resources are allocated to it, and then distributes the job flow across these resources.

When you modify the system, by adding or removing nodes or disks, you must modify the DataStage EE configuration file accordingly. Since EE reads the configuration file every time it runs a job, it automatically scales the application to fit the system without having to alter the job design.