Performance Tuning in IBM InfoSphere DataStage

Performance is a key factor in the success of any data warehousing project. Care for optimization and performance should be taken into account from the inception of the design and development process. Ideally, a DataStage® job should process large volumes of data within a short period of time. For maximum throughput and performance, a well performing infrastructure is required, or else the tuning of DataStage® jobs will not make much of a difference.

One of the primary steps of performance tuning is to examine the end-to-end process flow within a DataStage® job and understand which steps in the job are consuming the most time and resources. This can be done in the several ways:


1. The job score shows the generated processes, operator combinations, data sets, frame-work inserted sorts, buffers and partitions in the job. Score can be generated by setting the APT_DUMP_SCORE environment variable to TRUE before running the job. It also provides information about the node-operator combination. A score dump can help detect redundant operators, which can be used in modification of the job design to remove them.

2. The job monitor can be accessed through IBM Infosphere DataStage® Director. It provides a snapshot of job’s performance (data distribution/skew across partitions, CPU utilization) at runtime. APT_MONITOR_TIME and APT_MONITOR_SIZE are the two environment variables that control the operation of the job monitor, which takes a snapshot every five seconds by default. This can be changed by changing the value of APT_MONITOR_TIME.

3. Performance Analysis, a new capability beginning in DataStage® 8.x, can be used to collect information, generate reports and view detailed charts about job timeline, record throughput, CPU utilization, job memory utilization and physical machine utilization (shows processes other than the DataStage® activity running on the machine). This is very useful in identifying the bottlenecks during a job’s execution. Performance Analysis can be enabled through a job property on the execution tab, which collects data at runtime. (Note: By default, this option is disabled)

4. Resource Estimation, a toolbar option available in DataStage® 8.x, can be used to determine the system requirements needed to execute a particular job based on varying source data volumes and/or to analyze whether the current infrastructure can support the jobs that have been created.


There are several key aspects that could affect the job performance and these should be taken into consideration during the job design:

• Parallel configuration files allow the degree of parallelism and resources used by parallel jobs to be set dynamically at runtime. Multiple configuration files should be used to optimize overall throughput and to match job characteristics to available hardware resources.
• A DataStage® job should not be overloaded with stages. Each additional stage in a job reduces the resources available for the other stages in that job, which affects the job performance.
• Columns that are not needed should not be propagated through the stages and jobs. Unused columns make each row transfer from one stage to the next more expensive. Removing these columns minimizes memory usage and optimizes buffering.
• Runtime column propagation (RCP) should be disabled in jobs to avoid unnecessary column propagation.
• By setting the $OSH_PRINT_SCHEMAS environment variable, we can verify that runtime schemas match the job column definitions. Avoid unnecessary data type conversions.
• Proper partitioning significantly improves overall job performance. Record counts per partition can be displayed by setting the environment variable, $APT_RECORD_COUNTS. Ideally, these counts should be approximately equal. Partitioning should be set in such a way so as to ensure an even data flow across all partitions, and data skew should be minimized. If business rules dictate otherwise, then repartitioning should be done as early as possible to have a more balanced distribution which will lead to improved performance of downstream stages.
• DataStage® attempts to combine stages (operators) into a single process, and operator combination is intended to improve overall performance and reduce resource usage. Avoid repartitioning and use ‘Same’ partitioning for operator combination to occur. However, in some circumstances, operator combination may negatively impact performance and in such cases, all the combinations should be disabled by setting $APT_DISABLE_COMBINATION=TRUE.
• Do not sort the data unless necessary. Sorts done on a database (using ORDER BY clause) are usually much faster than those done in DataStage®. Hence, sort the data when reading from the database if possible instead of using the Sort Stage or sorting on the input link.
• Sort order and partitioning are preserved in parallel datasets. If data has already been partitioned and sorted on a set of key columns, check the ″Don’t sort, previously sorted″ option for the key columns in the Sort Stage. When reading from these data sets, partitioning and sorting can be maintained by using the ‘Same’ partitioning method.
• Datasets store data in native internal format (no conversion overhead) and preserve partitioning and sort order. They are parallelized unlike sequential files and hence, are much faster. Datasets must therefore be used to land intermediate results in a set of linked jobs.
• Use Join Stage as opposed to Lookup for handling huge volumes of data. Lookup is most appropriate when the reference data is small enough to fit into available physical memory. Sparse lookup is appropriate if the driver to the Lookup is significantly smaller than the reference input (1:100).
• Avoid using multiple Transformer Stages when the functionality could be incorporated into a single stage. Use Copy, Filter, or Modify stages instead of Transformer for simple transformation functions like renaming or dropping columns, type conversions, filtering out based on certain constraints, mapping a single input link to multiple output links, etc.
• As much as possible, minimize the number of stage variables in a Transformer Stage as that affects performance, and also avoid unnecessary function calls.
• If existing Transformer-based jobs do not meet performance requirements and a complex reusable logic needs to be incorporated in the job, consider building your own custom stage.
• Data should not be read from Sequential files using ‘Same’ partitioning.
• Sequential files can be read in parallel by using the ‘Multiple readers per node’ option. Setting $APT_IMPORT_BUFFER_SIZE and $APT_EXPORT_BUFFER_SIZE environment variables may also improve performance of Sequential files on heavily loaded file servers.
• Use Hash method in Aggregators only when the number of distinct key column
values is small. A Sort method Aggregator should be used when the number
of distinct key values is large or unknown.
• SQL statements in Database stages can be tuned for performance. Appropriate indexes on tables guarantee a better performance of DataStage® queries.
• ‘Array Size’ and ‘Record Count’ numerical values in Database stages can be tuned for faster inserts and updates. Default values are usually very low and may not be optimal.
• Best choice of database stages is to use Connector stages for maximum parallel performance and functionality.
Conclusion: Performance issues can be avoided by following the above best practices and performance guidelines. ‘Performance Analysis’ and ‘Resource Estimation’ functionalities can be used to gather detailed performance related data, and assist with more complicated scenarios.



Special Thanks to These References:
1.Information Server Documentation – Parallel Job Advanced Developer Guide
2.http://www.element61.be/e/resourc-detail.asp?ResourceId=188
3.http://DataStage®developer.blogspot.com/2008/01/DataStage®-performance-tuning.html

Read more ...>>

Monitoring Datastage Jobs

The Monitor window in Datastage Director -

Datastage Job Monitor is accessible through Datastage Director.
This option appears by right clicking on any Job name in Datastage Director client.

OR

Select the Job on the Director list window. Go to Tools --> View Monitor and select the job.

This basically displays summary information about relevant stages in a job that is being run or validated. It has a tree structure containing stages in a job and associated links. For server jobs active stages are shown (active stages are those that perform processing rather than ones reading or writing a data source). For parallel jobs, all stages are shown.

You can display more information about a stage and set the server update interval.

If you are monitoring a parallel job, and have not chosen to view instance information, the monitor displays information for Parallel jobs as follows:

• If a stage is running in parallel, then x N is appended to the stage name, where N gives how many instances are running.
• If a stage is running in parallel then the Num Rows column shows the total number of rows processed by all instances. The Rows/sec is derived from this value and shows the total throughput of all instances.
• If a stage is running in parallel then the %CP may be more than 100 if there are multiple CPUs on the server. For example, on a machine with four CPUs, %CP could be as high as 400 where a stage is occupying 100% of each of the four processors, on the other hand, if the stage is occupying only 25% of each processor the %CP would be 100%.

From the figure above, we see several columns which are elaborated below:

Stage name:
This column displays the names of stages that perform processing (for example, Transformer stages). Stages that represent data sources or data marts are not displayed.

Link Type:
When you have selected a link in the tree, displays the type of link as follows:

• <<Pri primary input link
• <Ref input link
• >Out output link
• >Rej output link for rejected rows

Status:
The status of the stage. The possible states are:

• Aborted. The process finished abnormally at this stage.
• Finished. All data has been processed by the stage.
• Ready. The stage is ready to process the data.
• Running. Data is being processed by the stage.
• Starting. The processing is starting.
• Stopped. The processing was stopped manually at this stage.
• Waiting. The stage is waiting to start processing.

Num rows:
This column displays the number of rows of data processed so far by each stage on its primary input.

Started at:
This column shows the time that processing started on the server.

Elapsed time:
This column shows the elapsed time since processing of the stage began.

Rows/sec:
This column displays the number of rows that are processed per second.

%CP:
The percentage of CPU the stage is using (you can turn the display of this column on and off from the shortcut menu)




Parallel Job Instance Information:
 To monitor instances of parallel jobs individually, choose Show Instances from the shortcut menu. The monitor window will then show each instance of a stage as a sub-branch under the ‘parent’ stage, The monitor displays the information for all stage instances under the ‘parent’ stage. Only relevant information is shown for each stage instance as follows:

Summary:

The job monitor provides a useful snapshot of a job's performance at a moment of execution, but does not provide thorough performance metrics. That is, a job monitor snapshot should not be used in place of a full run of the job, or a run with a sample set of data. Due to buffering and to some job semantics, a snapshot image of the flow might not be a representative sample of the performance over the course of the entire job.

The CPU summary information provided by the job monitor is useful as a first approximation of where time is being spent in the flow. However, it does not include any sorts or similar that might be inserted automatically in a parallel job. For these components, the score dump can be of assistance. See "Score Dumps".

A worst-case scenario occurs when a job flow reads from a data set, and passes immediately to a sort on a link. The job will appear to hang, when, in fact, rows are being read from the data set and passed to the sort.


The operation of the job monitor is controlled by two environment variables: APT_MONITOR_TIME and APT_MONITOR_SIZE. By default the job monitor takes a snapshot every five seconds. You can alter the time interval by changing the value of APT_MONITOR_TIME, or you can have the monitor generate a new snapshot every so-many rows by following this procedure:

1.Select APT_MONITOR_TIME on the InfoSphere DataStage Administrator environment variable dialog box, and press the set to default button.
2.Select APT_MONITOR_SIZE and set the required number of rows as the value for this variable.

Read more ...>>

Quick Datastage Tips

While designing/coding any kind or level of job, few tips may become very handy.
This would result into designing of Datastage jobs efficiently. More the efficiency, better would be the performance of the job once executed. These handy tips are also useful in reducing the job designing/coding time and better debugging approach.


Partitioning

Partitioning methods should be used widely in parallel jobs to achieve optimal performance.  Repartitioning of data within a job should be avoided as much as possible.
- Same partitioning should not be used to read data from a partitioned database, if the number of partitions in the database is very high compared to the number of nodes specified in the APT configuration file. “Same” partitioning will override the partitioning specified in the APT configuration file and the job will become resource intensive.
- In Lookup stages if the reference data is significantly lesser in volume compared to the input dataset then “Entire” partitioning should be used in the reference link and the input link partitioning should be left as “Auto” (default).
- In Lookup stages if the input & reference data are of comparable volumes, then hash partitioning should be used in both the links
- The input dataset to a Remove Duplicate stage should be hash partitioned and sorted on the key used for deduplication
- The partitioning of the input dataset to a Join stage should be either left as “Auto” or should be hashed & sorted on the key fields used to join the data.
- The partitioning in the input reference links should be set to “Entire” if the lookup type is “Normal” and the volume of reference data is significantly low(in thousands).
- The partitioning in the input and reference links should be “Hash” when the volume is about equal in both the links


Sequential File Stage

Particular attention must be taken when importing fields using the Sequential File stage
- If the incoming columns are variable-length data types (eg. INTEGER, DECIMAL, VARCHAR), the field width column property must be set to match the fixed-width of the input column. Double-click on the column number in the grid dialog to set this column property.
- If a field is nullable, you must define the null representation (null field value, null field length) in the “Nullable” section of the column property. Double-click on the column number in the grid dialog to set these properties.
- Care should be taken when reading delimited, bounded-length VARCHAR columns (varchars with the length option set). If the source file has fields with values longer than the maximum VARCHAR length, these extra characters will be silently discarded.

The following points should be taken into account to get an optimal performance in jobs using sequential file stages.
- Use multiple node reads or multiple readers per node to increase the performance of extracts from fixed width sequential files
- Add a reject link to capture the records that do not conform to the metadata definitions
- Avoid reading from sequential files using the Same partitioning method. Unless you have specified more than one source file, this will result in the entire file being read into a single partition, making the entire downstream flow run sequentially unless you explicitly repartition.


Database Stages

In general, it is best to use the native parallel database stages (DB2/UDB Enterprise, Oracle Enterprise) whenever possible for maximum parallel performance and features.


DB2 Stages

The DB2/UDB Enterprise (native parallel) stage should be used for reading from, performing lookups against, and writing to a DB2 Enterprise Server Edition database with Database Partitioning Feature
- As a native, parallel component, the DB2/UDB Enterprise stage is designed for maximum performance and scalability against very large partitioned DB2 UNIX databases.
- The DB2/API (plug-in) stage should only be used to read from and write to DB2 databases on other platforms (such as mainframe editions through DB2-Connect). DB2 API stage should be used to access data from DB2 database, installed in a different operating system than the ETL server. Sparse Lookup is not supported through the DB2/API stage.
- A DataStage job can connect to only one database instance and if both DB2 Enterprise stage and DB2 API stages are used in the same jobs then the instance information provided in the DB2 stage (DB2API or DB2 ENT) that comes first in the job will be picked up. Hence care should be taken to make sure that the database used in the later DB2 stage is present in the catalog of the first instance.
-  Queries that involve joining data from collocated tables in a multi partition database should be optimized by using NODENUMBER clause wherever applicable. The queries submitted through DB2 Enterprise stage would bypass DB2 optimizer and hence if the query is not optimized the extraction process will spawn too many unnecessary connections across the partitions available in the database.


Oracle Stage

- Oracle stages in DataStage EE will always use table definition as defined in the underlying database, regardless of explicit job design metadata (Data Type, Nullability, etc)
- When building jobs that use Oracle sources or targets, always import Oracle table definitions using the “orchdbutil option” in DataStage Designer/Manager to avoid unexpected data type conversions.
- If the password has a special character in it, then the password should be enclosed within double quotes.


Dataset Stage

- Dataset stages can be used as intermediate stages to land data and the data stored in the dataset is partitioned. So when accessing the data from a dataset the read process will be quicker
- While using datasets created by preceding jobs, care should be taken to ensure that the same APT configuration file is used by both the jobs. If different configuration files are used the job may not read the entire data present in the dataset.
- All the datasets should be stored in the appropriate “work” directory
- Datasets created for landing the intermediate data and that are not necessary for the next run of the job should be purged. This would release the memory occupied by the datasets and increase the scratch disk space.

Processing Stages

This sectin will describe the best practices that can be applied for almost every individual stage that can be used in DataStage EE to transform the input data.

Transformer Stage

Within DataStage EE, the Transformer stage always generates “C” code which is then compiled to a parallel component. For this reason, it is important to minimize the number of transformers, and to use other stages (Copy, Filter, Switch, etc) when derivations are not needed.

- Transformer should never be used as a placeholder to ease job maintenance. Instead use the Copy stage as a job placeholder.
- Transformer should never be used just to split the input dataset into multiple output datasets based on the values of certain input columns. Filter stage should be used to do such operations.
- Transformer should never be used just to do null handling operations. Modify stage should be used to perform such operations.
- When evaluating expressions for output derivations or link constraints, the Transformer will discard any row that has a NULL value used in the expression. This is because the rules for arithmetic and string handling of NULL values are by definition undefined.
Always test for null values before using a column in an expression
         Eg. If ISNULL(link.col) Then… Else…
- It may be possible to implement complex derivation expressions using regular patterns by Lookup tables instead of using a Transformer with nested IF THEN ELSE derivations.
Eg. If A=0,1,2,3 Then B=”X” If A=4,5,6,7 Then B=”C”
- Output derivations are evaluated BEFORE any type conversions on the assignment. Therefore, it is important to make sure type conversion has taken place before a row reaches the Transformer.
Eg. TrimLeadingTrailing(string) works only if string is a VarChar field. Thus, the incoming column must be type VarChar before it is evaluated in the Transformer.
- If constraints are used in all the output links of a transformer then add a reject link to capture the records that does not match the constraints.


Stage Variables

Stage variables should be used wisely only when it is required
- It can be used when the same output derivation is used in multiple output links. This would eliminate the expression being executed multiple times.
- It can be used when the transformation logic involves accessing the values of preceding records
- Care should be taken to use the correct data type when defining stage variables

Lookup Stage

Lookup stage should be used to join the source data with the reference data, when the volume of data in the reference table is small enough to fit into available physical memory. Each lookup reference requires a contiguous block of physical memory.
- The condition feature could be used to validate the key fields used for the lookup

- While doing a lookup, if the situation is such that you have the same column name in the input link and reference link and if you want to map the reference value to the output column then the output column should be given a different name. Then the column could be renamed by using a subsequent Copy stage. If the column is NOT renamed then the source column will be mapped to the output column irrespective of the column mapping done in the lookup stage.

The following diagrams represent the situation described above

Normal lookup

The volume of data that can be referenced using a normal lookup depends on the memory available in the server. The rule of thumb is to use normal lookup for volumes lesser than 1 million records.
- If the reference data is very less when compared to the source data then the “entire” partitioning could be used for the reference link. This would increase the performance significantly
- If the reference data is almost equal to the source data then “Hash” partitioning should be done on the lookup keys on both the input & reference links


Sparse Lookup

If the reference to a Lookup is directly from a DB2 or Oracle table, and the number of input rows is significantly smaller (e.g. 1:100 or more) than the number of reference rows, a Sparse Lookup may be appropriate.
- In Sparse lookups the query in the lookup stage will be executed as many number of times as the number of input records. So the query in the lookup stage should be fairly simple. If the query in the lookup stage is complicated & time consuming (with inner queries, etc..) then a join stage should be used.

Join Stage

- Join stage should never be used for joining huge volume of source data with significantly lesser volume of reference data. This would slow down the entire process
- Join stage should be used when data in both source & reference datasets are huge (in millions)
- If explicit hash partitioning is done in the input links of a join stage, then data has to be repartitioned if there is a possibility of skew in the input/reference datasets.
- Join stage does not provide reject handling for unmatched records (such as in an InnerJoin scenario). If un-matched rows must be captured or logged, an OUTER join operation must be performed. In an OUTER join scenario, all rows on an outer link (eg. Left Outer, Right Outer, or both links in the case of Full Outer) are output regardless of match on key values. During an Outer Join, when a match does not occur, the Join stage inserts NULL values into the unmatched columns. Care must be taken to change the column properties to allow NULL values before the Join. This is most easily done by inserting a Copy stage and mapping a column from NON-NULLABLE to NULLABLE.  After the joining operation the columns from the outer link can be checked for null values and the records having null values can be rejected.
- In some cases, it is simpler to use a Column Generator to add an ‘indicator’ (DUMMY) column, with a constant value, to each of the outer links and test that column for the constant after performing the join.

Joining input with reference data (Join vs Lookup)

There are multiple ways of joining the input data with the reference data. The optimal way of referring data should be selected by considering the following criteria
- Input data volume
- Reference data volume
- Join criteria
- Reject handling criterion
- Performance

- If the input and reference data is collocated in the database, joining the data within the SQL will give the optimal performance.
- In certain cases if the input data is significantly lesser when compared to the reference data then loading the input (processed) data into a work table and then joining it with reference data will give a much better performance when compared to DataStage joins.

If the data is not collocated or if the join has to happen between processed data then DataStage provides the following options.

Join, Lookup and Merge stages perform the same function of joining reference data with the input data.
- Join stage can be used to perform inner and all types of outer joins. It does not have any volume constraints. It does not have a reject link and hence reject handling should be done by using some lookup columns or dummy columns in subsequent stages.  While joining huge volumes of data within DataStage memory space required/available in the scratch disk and other jobs running during that window should be taken into consideration and planned accordingly.
- Lookup stage can be used to perform inner and left outer joins. Normal lookups are in memory lookups and the reference data will be stored in the system memory and if the reference volume increases the data will be moved to the paging space. With increasing volumes the performance of Normal lookups will be poor. Sparse lookups should be used in the case of the input data being significantly lesser than the reference data (say 1:100). This will fire a query to the database for every input record and hence would be performance issue for increased number of input records.
- Using Merge stage we can perform inner and left outer joins. The input is considered as Master dataset and all the other reference datasets are considered as Update datasets. The advantage of using a Merge stage is having individual reject links for every Update dataset. Merge stage can be used to join data of comparable volumes in both input and reference datasets.

Aggregator Stage

- The aggregation field types should be defined as double instead of decimal types. This should improve the performance.
- If the aggregation file types have to be set to decimal then setting up the variables APT_DECIMAL_INTERM_PRECISION & APT_DECIMAL_INTERM_SCALE is recommended. When set, define precision and scale for intermediate decimal objects  If any of these variables are not set, the default value of DEFAULT_INTERM_PRECISION for precision and  DEFAULT_INTERM_SCALE for scale will be used
- Null values in the grouping columns will distort results. Include a transformer before the Aggregator to exclude nulls.

Copy Stage

- Copy Stage should be used as the placeholder stage whenever there is a need for splitting the input dataset into multiple outputs
- It can also be used for simple renaming of columns copy stage should be used instead of using the expensive Transformer stage

Filter Stage

- Filter stage should be used to split the input dataset in multiple output datasets.
- If null values are expected as values for the columns used in the Where Clause, “Nulls Value” option should be set to define whether null values are treated as 'Greater Than' or 'Less Than' other values.
- Filter stages should be moved to the beginning of the job as much as possible to avoid processing unwanted records.

Modify stage

- If the Source to Target transformations require just null handling and keeping/dropping of columns then a Modify stage should be used instead of using the Transformer stage.

Remove Duplicate Stage
Remove Duplicate stage is used to remove the duplicate records based on the key fields defined. - Input data has to be hash partitioned and sorted to apply the de-duplication logic properly.
- If the input records are not identical then proper de-duplication logic should be defined to meet the business requirements.
- The sorting and hash partitioning required for remove duplicate stage should be done in a separately in a sort stage preceding the remove duplicate stage. Performing the hash partitioning & sorting in the input link of the remove duplicate stage, causes some memory issues randomly.

Funnel Stage

The funnel requires all input links to have identical schemas (column names, types, attributes including nullability). The single output link matches the input schema.
-Type of the funnel should be defined as a “Continuous Funnel” unless there is a valid reason to specify that otherwise. Continuous file type will be having better performance when compared to the other types.
Target Stages
This section will describe the best practices for all the stages that can be used as a source stage in a parallel job

Sequential Files

- If Null values are expected as values for some of the input fields then care should be taken to define the Null Values in the formatting tab

DB2 Stage

Two types of DB2 stages (DB2 Enterprise & DB2 API) can be used to load data into any DB2 tables. The recommendation is to use DB2 Enterprise stage to load data into DB2 tables that reside in the same operating system as that of the ETL server. DB2 API stage should be used to load data into DB2 tables that are in a different operating system. E.g. Mainframe DB2 tables

DB2 Enterprise

- DB2 Enterprise stage will execute in parallel and the partitioning in the input link of the stage should be left as DB2 (default). Overriding this partitioning type will add to the overhead of the database
- Load option can be used in this stage if the requirement is just to insert huge volume of data into the target table. The load should be a non-recoverable load otherwise if the job fails the entire table space will be locked. To make the load as non-recoverable select the option ‘Non-Recoverable Transactions’ as ‘True’
- Upsert option can be used to insert/update data into the target table. Care should be taken in the case of updating a table with referential integrity. In this case the array size and row commit interval should be having the same value
- If the requirement is to commit the records at the completion of load, then the row commit interval should be set as a very high value

DB2 API 

- DB2 API stage runs sequentially and the throughput will be very less when compared to the Enterprise stage (max of 2000 rows/sec)
- The Database used in the DB2 API stage should be cataloged in the default DB2 instance (mentioned in the dsenv file) for the connections to be established properly
- When using dual server APT_CONFIG_FILE (say any *prod01_prod02*.apt) values, the DB2 client installation should be verified in both the servers

Oracle Stage

- If the password has a special character in it, then the password should be enclosed within double quotes.
- The partitioning of the input link of the Oracle stage should be left as “Auto” (default)
- Jobs that are updating/inserting/deleting rows on Oracle tables that contain bitmap indexes with low cardinality tend to create database deadlocks. In such scenarios the bitmap indexes should be changed to a b-tree index

External targets

In case the external target is invoking a C program which is interacting with a file larger than 2 GB; call open64() statement instead of calling open(). The same can be achieved by using the open() API but compiling  with the _LARGE_FILE_API macro defined.

Read more ...>>

DataStage FAQ and Best Practices

1. General Datastage issues


1.1. What are the ways to execute datastage jobs?
A job can be run using a few different methods:
* from Datastage Director (menu Job -> Run now...)
* from command line using a dsjob command
* Datastage routine can run a job (DsRunJob command)
* by a job sequencer



1.2. How to invoke a Datastage shell command?
Datastage shell commands can be invoked from :
* Datastage administrator (projects tab -> Command)
* Telnet client connected to the datastage server


1.3. How to stop a job when its status is running?
To stop a running job go to DataStage Director and click the stop button (or Job -> Stop from menu). If it doesn't help go to Job -> Cleanup Resources, select a process with holds a lock and click Logout
If it still doesn't help go to the datastage shell and invoke the following command: ds.tools.It will open an administration panel. Go to 4.Administer processes/locks , then try invoking one of the clear locks commands (options 7-10).


1.4. How to run and schedule a job from command line?
To run a job from command line use a dsjob command
Command Syntax: dsjob [-file | [-server ][-user ][-password ]] [ ]
The command can be placed in a batch file and run in a system scheduler.

1.5. How to release a lock held by jobs?
Go to the datastage shell and invoke the following command: ds.tools
It will open an administration panel. Go to 4.Administer processes/locks , then try invoking one of the clear locks commands (options 7-10).


1.6. User privileges for the default DataStage roles?
The role privileges are:
* DataStage Developer - user with full access to all areas of a DataStage project
* DataStage Operator - has privileges to run and manage deployed DataStage jobs
* -none- - no permission to log on to DataStage


1.7. What is a command to analyze hashed file?
There are two ways to analyze a hashed file. Both should be invoked from the datastage command shell. These are:
* FILE.STAT command
* ANALYZE.FILE command


1.8. Is it possible to run two versions of datastage on the same pc?
Yes, even though different versions of Datastage use different system dll libraries.
To dynamically switch between Datastage versions install and run DataStage Multi-Client Manager. That application can unregister and register system libraries used by Datastage.


1.9. How to send notifications from Datastage as a text message (sms) to a cell phone.
There is a few possible methods of sending sms messages from Datastage. However, there is no easy way to do this directly from Datastage and all methods described below will require some effort.
The easiest way of doing that from the Datastage standpoint is to configure an SMTP (email) server as a mobile phone gateway. In that case, a Notification Activity can be used to send message with a job log and any desired details. DSSendMail Before-job or After-job subroutine can be also used to send sms messages.
If configured properly, the recipients email address will have the following format: 600123456@oursmsgateway.com
If there is no possibility of configuring a mail server to send text messages, you can to work it around by using an external application run directly from the operational system. There is a whole bunch of unix scripts and applications to send sms messages.
In that solution, you will need to create a batch script which will take care of sending messages and invoke it from Datastage using ExecDOS or ExecSh subroutines passing the required parameters (like phone number and message body).
Please keep in mind that all these solutions may require a contact to the local cellphone provider first and, depending on the country, it may not be free of charge and in some cases the provider may not support the capability at all.



2. Datastage development and job design


2.1. Error in Link collector - Stage does not support in-process active-to-active inputs or outputs.
To get rid of the error just go to the Job Properties -> Performance and select Enable row buffer.
Then select Inter process which will let the link collector run correctly.
Buffer size set to 128Kb should be fine, however it's a good idea to increase the timeout.


2.2. What is the DataStage equivalent to like option in ORACLE
The following statement in Oracle:
select * from ARTICLES where article_name like '%WHT080%';
Can be written in DataStage (for example as the constraint expression):
incol.empname matches '...WHT080...'


2.3. what is the difference between logging text and final text message in terminator stage.
Every stage has a 'Logging Text' area on their General tab which logs an informational message when the stage is triggered or started.
* Informational - is a green line, DSLogInfo() type message.
* The Final Warning Text - the red fatal, the message which is included in the sequence abort message


2.4. Error in STPstage - SOURCE Procedures must have an output link.
The error appears in Stored Procedure (STP) stage when there are no stages going out of that stage.To get rid of it go to 'stage properties' -> 'Procedure type' and select Transform


2.5. How to invoke an Oracle PLSQL stored procedure from a server job.
To run a pl/sql procedure from Datastage a Stored Procedure (STP) stage can be used.
However it needs a flow of at least one record to run.
It can be designed in the following way:
* source odbc stage which fetches one record from the database and maps it to one column - for example: select sysdate from dual
* A transformer which passes that record through. If required, add pl/sql procedure parameters as columns on the right-hand side of tranformer's mapping
* Put Stored Procedure (STP) stage as a destination. Fill in connection parameters, type in the procedure name and select Transform as procedure type. In the input tab select 'execute procedure for each row' (it will be run once).


2.6. Is it possible to run a server job in parallel?
Yes, even server jobs can be run in parallel.
To do that go to 'Job properties' -> General and check the Allow Multiple Instance button.
The job can now be run simultaneously from one or many sequence jobs. When it happens datastage will create new entries in Director and new job will be named with automatically generated suffix (for example second instance of a job named JOB_0100 will be named JOB_0100.JOB_0100_2). It can be deleted at any time and will be automatically recreated by datastage on the next run.


2.7. Error in STPstage - STDPROC property required for stage xxx
The error appears in Stored Procedure (STP) stage when the 'Procedure name' field is empty. It occurs even if the Procedure call syntax is filled in correctly.
To get rid of error fill in the 'Procedure name' field.


2.8. Datastage routine to open a text file with error catching
Note! work dir and file1 are parameters passed to the routine.
* open file1
OPENSEQ work_dir : '\' : file1 TO H.FILE1 THEN
CALL DSLogInfo("******************** File " : file1 : " opened successfully", "JobControl")
END ELSE
CALL DSLogInfo("Unable to open file", "JobControl")
ABORT


2.9. Datastage routine which reads the first line from a text file
Note! work dir and file1 are parameters passed to the routine.
* open file1
OPENSEQ work_dir : '\' : file1 TO H.FILE1 THEN
CALL DSLogInfo("******************** File " : file1 : " opened successfully", "JobControl")
END ELSE
CALL DSLogInfo("Unable to open file", "JobControl")
ABORT
END
READSEQ FILE1.RECORD FROM H.FILE1 ELSE
Call DSLogWarn("******************** File is empty", "JobControl")
END
firstline = Trim(FILE1.RECORD[1,32]," ","A") ******* will read the first 32 chars
Call DSLogInfo("******************** Record read: " : firstline, "JobControl")
CLOSESEQ H.FILE1
END


2.10. How to test a datastage routine or transform?
To test a datastage routine or transform go to the Datastage Manager.
Navigate to Routines, select a routine you want to test and open it. First compile it and then click 'Test...' which will open a new window. Enter test parameters in the left-hand side column and click run all to see the results.
Datastage will remember all the test arguments during future tests.


2.11. When hashed files should be used? What are the benefits or using them?
Hashed files are the best way to store data for lookups. They're very fast when looking up the key-value pairs.
Hashed files are especially useful if they store information with data dictionaries (customer details, countries, exchange rates). Stored this way it can be spread across the project and accessed from different jobs.


2.12. How to construct a container and deconstruct it or switch between local and shared?
To construct a container go to Datastage designer, select the stages that would be included in the container and from the main menu select Edit -> Construct Container and choose between local and shared.
Local will be only visible in the current job, and share can be re-used. Shared containers can be viewed and edited in Datastage Manager under 'Routines' menu.
Local Datastage containers can be converted at any time to shared containers in datastage designer by right clicking on the container and selecting 'Convert to Shared'. In the same way it can be converted back to local.


2.13. Corresponding datastage data types to ORACLE types?
Most of the datastage variable types map very well to oracle types. The biggest problem is to map correctly oracle NUMBER(x,y) format.
The best way to do that in Datastage is to convert oracle NUMBER format to Datastage Decimal type and to fill in Length and Scale column accordingly.
There are no problems with string mappings: oracle Varchar2 maps to datastage Varchar, and oracle char to datastage char.


2.14. How to adjust commit interval when loading data to the database?
In earlier versions of datastage the commit interval could be set up in:
General -> Transaction size (in version 7.x it's obsolete)
Starting from Datastage 7.x it can be set up in properties of ODBC or ORACLE stage in Transaction handling -> Rows per transaction.


2.15. What is the use of INROWNUM and OUTROWNUM datastage variables?
@INROWNUM and @OUTROWNUM are internal datastage variables which do the following:
* @INROWNUM counts incoming rows to a transformer in a datastage job
* @OUTROWNUM counts oucoming rows from a transformer in a datastage job
These variables can be used to generate sequences, primary keys, id's, numbering rows and also for debugging and error tracing.
They play similiar role as sequences in Oracle.
If set to 0 the commit will be issued at the end of a successfull transaction.


2.16. Datastage trim function cuts out more characters than expected
By deafult datastage trim function will work this way:
Trim(" a b c d ") will return "a b c d" while in many other programming/scripting languages "a b c d" result would be expected.
That is beacuse by default an R parameter is assumed which is R - Removes leading and trailing occurrences of character, and reduces multiple occurrences to a single occurrence.
To get the "a b c d" as a result use the trim function in the following way: Trim(" a b c d "," ","B")


2.17. Database update actions in ORACLE stage
The destination table can be updated using various Update actions in Oracle stage. Be aware of the fact that it's crucial to select the key columns properly as it will determine which column will appear in the WHERE part of the SQL statement. Available actions:
* Clear the table then insert rows - deletes the contents of the table (DELETE statement) and adds new rows (INSERT).
* Truncate the table then insert rows - deletes the contents of the table (TRUNCATE statement) and adds new rows (INSERT).
* Insert rows without clearing - only adds new rows (INSERT statement).
* Delete existing rows only - deletes matched rows (issues only the DELETE statement).
* Replace existing rows completely - deletes the existing rows (DELETE statement), then adds new rows (INSERT).
* Update existing rows only - updates existing rows (UPDATE statement).
* Update existing rows or insert new rows - updates existing data rows (UPDATE) or adds new rows (INSERT). An UPDATE is issued first and if succeeds the INSERT is ommited.
* Insert new rows or update existing rows - adds new rows (INSERT) or updates existing rows (UPDATE). An INSERT is issued first and if succeeds the UPDATE is ommited.
* User-defined SQL - the data is written using a user-defined SQL statement.
* User-defined SQL file - the data is written using a user-defined SQL statement from a file.


2.18. Use and examples of ICONV and OCONV functions?
ICONV and OCONV functions are quite often used to handle data in Datastage.
ICONV converts a string to an internal storage format and OCONV converts an expression to an output format.
Syntax:
Iconv (string, conversion code)
Oconv(expression, conversion )
Some useful iconv and oconv examples:
Iconv("10/14/06", "D2/") = 14167
Oconv(14167, "D-E") = "14-10-2006"


2.19. ERROR 81021 Calling subroutine DSR_RECORD ACTION=2
Error message:
DataStage Repository Interface:
Error calling subroutine: DSR_RECORD (Action=2);
check DataStage is set up correctly in project
Development (Internal Error (81021))
Datastage system help gives the following error desription:
SYS.HELP. 081021
MESSAGE.. dsrpc: Error writing to Pipe.

The problem appears when a job sequence is used and it contains many stages (usually more than 10) and very often when a network connection is slow.
Basically the cause of a problem is a failure between DataStage client and the server communication.
The solution to the issue is:
# Do not log in to Datastage Designer using 'Omit' option on a login screen. Type in explicitly username and password and a job should compile successfully.
# execute the DS.REINDEX ALL command from the Datastage shell - if the above does not help

Oconv(14167, "D DMY[,A,]") = "14 OCTOBER 2006"
Oconv(12003005, "MD2$,") = "$120,030.05"
That expression formats a number and rounds it to 2 decimal places:
Oconv(L01.TURNOVER_VALUE*100,"MD2")
Iconv and oconv can be combined in one expression to reformat date format easily:
Oconv(Iconv("10/14/06", "D2/"),"D-E") = "14-10-2006"


2.20. How to check Datastage internal error descriptions
# To check the description of a number go to the datastage shell (from administrator or telnet to the server machine) and invoke the following command: SELECT * FROM SYS.MESSAGE WHERE @ID='081021'; - where in that case the number 081021 is an error number
The command will produce a brief error description which probably will not be helpful in resolving an issue but can be a good starting point for further analysis.


2.21. Error timeout waiting for mutex
The error message usually looks like follows:
... ds_ipcgetnext() - timeout waiting for mutex
There may be several reasons for the error and thus solutions to get rid of it.
The error usually appears when using Link Collector, Link Partitioner and Interprocess (IPC) stages. It may also appear when doing a lookup with the use of a hash file or if a job is very complex, with the use of many transformers.
There are a few things to consider to work around the problem:
- increase the buffer size (up to to 1024K) and the Timeout value in the Job properties (on the Performance tab).
- ensure that the key columns in active stages or hashed files are composed of allowed characters – get rid of nulls and try to avoid language specific chars which may cause the problem.
- try to simplify the job as much as possible (especially if it’s very complex). Consider splitting it into two or three smaller jobs, review fetches and lookups and try to optimize them (especially have a look at the SQL statements).


2.22. ERROR 30107 Subroutine failed to complete successfully
Error message:
Error calling subroutine:
DSR_RECORD (Action=2); or *DataStage*DSR_SELECT (Action=7);
check DataStage is set up correctly in project Development
(Subroutine failed to complete successfully(30107))
Datastage system help gives the following error desription:
SYS.HELP. 930107
MESSAGE.. DataStage/SQL: Illegal placement of parameter markers
The problem appears when a project is moved from one project to another (for example when deploying a project from a development environment to production).
The solution to the issue is:
# Rebuild the repository index by executing the DS.REINDEX ALL command from the Datastage shell


2.23. Datastage Designer hangs when editing job activity properties
The appears when running Datastage Designer under Windows XP after installing patches or the Service Pack 2 for Windows.
After opening a job sequence and navigating to the job activity properties window the application freezes and the only way to close it is from the Windows Task Manager.
The solution of the problem is very simple. Just Download and install the “XP SP2 patch” for the Datastage client.
It can be found on the IBM client support site (need to log in):https://www.ascential.com/eservice/public/welcome.do
Go to the software updates section and select an appropriate patch from the Recommended DataStage patches section.
Sometimes users face problems when trying to log in (for example when the license doesn’t cover the IBM Active Support), then it may be necessary to contact the IBM support which can be reached at WDISupport@us.ibm.com


2.24. Can Datastage use Excel files as a data input?
# Microsoft Excel spreadsheets can be used as a data input in Datastage. Basically there are two possible approaches available: Access Excel file via ODBC - this approach requires creating an ODBC connection to the Excel file on a Datastage server machine and use an ODBC stage in Datastage. The main disadvantage is that it is impossible to do this on an Unix machine. On Datastage servers operating in Windows it can be set up here:
Control Panel -> Administrative Tools -> Data Sources (ODBC) -> User DSN -> Add -> Driver do Microsoft Excel (.xls) -> Provide a Data source name -> Select the workbook -> OK
# Save Excel file as CSV - save data from an excel spreadsheet to a CSV text file and use a sequential stage in Datastage to read the data.

Read more ...>>