Useful Guidelines in Designing Ascential Data Stage Server Jobs

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.
   
6. Name links to express the direction and type of data flowing through them.
   
7. Use job parameters where ever it is required. This makes the process easy while moving into production.
   
8. While using ODBC stages remember to remove the derivations in the columns.
   
9. Use ODBC stage to access relational tables.
   
10. Move constraints from Transform stages to input stage WHERE clauses, to reduce the number of rows the job has to process.
    
11. 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.

While using 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 file to 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.