Please CLICK HERE to visit our movies section to watch all Telugu, Hindi & Tamil movies online, Daily updated with Lots & Lots of Movies.Thanks for visiting!

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

Popularity: 3% [?]