Data Stage Designer Performance Tuning in Server jobs

1. In derivations, instead of calling routines, implement the logic in the derivation. This eliminates the overhead of the procedure call.
   
2. Implement the logic in a stage variable and then point the stage variable to the actual field.
   
3. Use Transforms rather than using routines.
   
4. While using the ODBC stage adjust the rows per transaction setting. Try setting to 1000, 5000, or 10000.
   
5. Adjust the array size setting. Try setting to 10, 100, or 1000.
   
6. If output rows are Inserts or Appends and not Updates, consider using a native bulk loader.
   
7. Eliminate unused columns.
   
8. Eliminate unused references.
   
9. Minimize using the stages like SORT, AGGREGATE which minimizes the performance of the job.
   
10. If more transformer Stages are used in sequence in a job, Enable the inter process buffering in the job properties or use the InterProcess Stage between Transformers which improves the performance.
    
11. Direct output to a sequential file compatible with the bulk loader. Then invoke the bulk loader using an after-job subroutine. The bulk loader for Oracle is SQLLDR.
    
12. Avoid using 'like' operator in user defined queries in ODBC stages
    
13. Avoid using stored procedures until and unless the functionality cannot be implemented in Data Stage jobs.
    
14. Tips while creating routines
15. Use variables in the routines.
16. Assign empty values to the variables before using them.
17. Routines will return Ans as return value. Instead of using ANS multiple times, use a variable .Implement the logic in that variable and assign that variable to ANS.
18. For Example: Ans = ''
    If ( Len(Trim(Name)) > 45) Then Ans = Ans : ',' : '24356' End
    Ans = Ans
    The above logic can be implemented using
    ErrStr = ''Ans = ''
    If ( Len(Trim(Name)) > 45) Then ErrStr := ',24356'End Ans = ErrStr
    

Duke Consulting Tips & Tricks for Datastage

Kim Duke has been providing wonderful tips and tricks for Ascential Datastage.You can find and gain lot of information on extracing etl job statistics ,routines,basic shell scripting and much more at Duke Consulting Tips & Tricks You can download all these documents and improve your job performance and capture job statistics. The starting point for performance tuning is to get and track row counts. Rows per second is the leading indicator of problems. If rows start slowing down over time then why. EtlStats.zip conatins jobs and documentation to get row counts. We run this job at the end of each job sequence. The results are stored in Oracle. The DDL to create the tables is included. Also included is DSaveAsBmp.bat which will create bmp images for each job. Makes for great documentation.

• Get row counts on all jobs in a sequence, all jobs or just one job.
• Installing EtlStats.
• Sample reports from EtlStats. Sample records for ETL_QA tables.
• Jobs and routines to extract information about jobs.
• Documentation for routines
• Routines for Generate create table scripts from DS_METADATA.,Display metadata mismatches,Put a standard long description on a job. ,Get last warning message from log file,Get shell based on OS (DOS or SH),Compare first row of sequential file with column names to lengths.
• Backup all DataStage projects on a server.
• Korn Shell, Perl, Universe and Vi help files.
• Xml Best Practices
• Tech Tips From Ascential now IBM

Tips and Guidelines in using Ascential Data Stage Server Edition

1. Logically create the folders / subfolders on the file server so that the files can be placed and accessed from the relevant folders/subfolders. 2. Group the Jobs logically into various categories / Subcategories. 3. Comment the Jobs by using Annotation stage which tells the users, the functionality implemented in the job. 4. Give descriptions in the properties of the stages used so that others can identify the functionality implemented in it. 5. Name passive stages with the Table/File Names they access in it. 6. Name active stages to match their function. 7. Name links to express the direction and type of data flowing through them. 8. Use job parameters where ever it is required. This makes the process easy while moving into production. 9. While using ODBC stages remember to remove the derivations in the columns. 10.Use ODBC stage to access relational tables. 11.Move constraints from Transform stages to input stage WHERE clauses, to reduce the number of rows the job has to process. 12.Use the in-built functions present in Data Stage rather than creating a new routine for implementing the same logic as of in-built function. Easy way to remove the column derivation 1.Open the transformer stage 2.Copy the columns from source(ODBC ) to target(Sequential File) 3.Delete the columns from source(ODBC) 4.Copy all the columns from target to the source. 5.Close the transformer. 6.Now we find that all the derivations are cleared from ODBC stage. Reference Lookups 1.Compare the number of input rows with the number of rows in the reference table. If the reference table is smaller than the number of input rows, pre-load the reference table into a hash file and then reference the hash file. 2.Consider moving reference lookups to a join within the input stage. All columns used to join the tables should be indexed to maximize performance. 3.If the number of rows in a hashed file is small, consider pre-loading the file into memory by checking the pre-load into memory checkbox in the Hash File stage. 4.Remove unused columns from transforms. This does not apply to columns in sequential files or output to hash files. 5.While mapping the input records with the Hash Look ups, remember that the fields getting mapped should be of same data type and of same length. 6.While loading the hash files, trim the data. Performance Tuning in Data Stage Designer 1.In derivations, instead of calling routines, implement the logic in the derivation. This eliminates the overhead of the procedure call. 2.Implement the logic in a stage variable and then point the stage variable to the actual field. 3.Use Transforms rather than using routines. 4.While using the ODBC stage adjust the rows per transaction setting. Try setting to 1000, 5000, or 10000. 5.Adjust the array size setting. Try setting to 10, 100, or 1000. 6.If output rows are Inserts or Appends and not Updates, consider using a native bulk loader. 7.Eliminate unused columns. 8.Eliminate unused references. 9.Minimize using the stages like SORT, AGGREGATE which minimizes the performance of the job. 10.If more transformer Stages are used in sequence in a job, Enable the inter process buffering in the job properties or use the InterProcess Stage between Transformers which improves the performance. 11.Direct output to a sequential file compatible with the bulk loader. Then invoke the bulk loader using an after-job subroutine. The bulk loader for Oracle is SQLLDR. 12.Avoid using “ like “ operator in user defined queries in ODBC stages 13.Avoid using stored procedures until and unless the functionality cannot be implemented in Data Stage jobs. Using Manager 1.Import the Table Definitions / Sequential File Definitions / Hash File definitions into appropriate folders. For Example,Identify the fact tables being used and import all the fact tables to the Facts Category. Facts |-Fact Table1 |-Fact Table2 Dimensions |-Dimension Table1 |-Dimension Table2 2.Categorize the routines into the relevant folders. 3.Give comments to the routines which describes the functionality of the routines. 4.Place the commonly used routines in a common folder to make it reusable 5.Take weekly back up of the jobs / projects. 6.Take the back up of the jobs before delivering the jobs to Client / Onsite Coordinators. Tips while creating routines 1.Use variables in the routines. 2.Assign empty values to the variables before using them. 3.Routines will return Ans as return value. Instead of using ANS multiple times, use a variable .Implement the logic in that variable and assign that variable to ANS. For Example: Ans = '' If ( Len(Trim(Name)) > 45) Then Ans = Ans : ',' : '24356'End Ans = Ans The above logic can be implemented like ErrStr = ''Ans = ''If ( Len(Trim(Name)) > 45) Then ErrStr := ',24356'End Ans = ErrStr