Select Max for surrogate key in EE

The surrogate key stage in 8.0 can be used with DB Sequences or using a Flat File to manage the keys (set the 'Source Type' option to Flat File). Fortunately, seeding the flat file with the max value of the table is easy, unfortunately it can't be done in the same job as the job that generates the keys.

There are 2 ways to do it --

1. Create a job that does a 'select max(..)' from the target table, connected to a surrogate key stage (set the 'Update Action' to 'Update'). This will seed the state file with the max value. In the job that needs the keys, use the surrogate key stage and this state file to generate keys (SK stage takes 1 input link, and produces 1 output link with the SK column appended). If this is the only thing that inserts rows into the table, you don't need run the 'Update' job again, the state file will remember where it left off for next time.

2. When using a Flat File to manage keys, you can supply an initial value in the surrogate key stage. This value can be a job parameter, so you can hook this together with something that does the select max() and sets the job param in a job sequencer.

BTW -- the new surrogate key operator was designed for the parallel execution environment, so key generation is handled without the use of @PartitionNum or any of that. It also supports multiple jobs (running in parallel) that are getting keys from the same state file.

And finally... you can also use the surrogate key generation functionality directly in a transformer (rather than using the SKG stage). It requires a little set up in the transformer stage properties, then you can use the utility function 'NextSurrogateKey()' as the derivation for the SK column.

If the any of this sounds like something that you want to try, let me know and I can set you up with some simple examples.

Send File as email attachment from Datastage?

Question
Does anyone have an example of sending a file as an email attachment from Datastage???

Answer

Check the below links to get some idea:

http://www.shelldorado.com/articles/mailattachments.html

http://www.sharewareconnection.com/titles/uuencode.htm

Sequential file stage File Name Column option

Questions:
In the sequential file stage on the parallel canvas there is an option to specify a File Name column. This, in theory, allows you to read from multiple files (if the Read Method is set to "File Pattern") and to populate the file name in the specified outbound column.

However, if I specify a wildcard in the File Pattern such as C:\Test_Data\*, I don't get the individual file names in the outbound column - I get C:\Test_Data\* for the file name outbound column. That's rather lame and is a defect if you ask me - anyone else experience this? This is on a windows implementation.

Answer
By default the sequential file stage takes all the files returned by the pattern and cats them together reading from one big stream of data, so it is not possible to determine an individual file name for each record.

You can get the individual file names by setting APT_IMPORT_PATTERN_USES_FILESET. This will change the behavior of sequential file stage patterns so it will create a file set with the returned files. This has the advantages of better parallelism depending on configuration and leaves the file names available to populate a file name column.

Shared library (dsdb2.so) failed to load / IS 8.0 on AIX

Issues
main_program: Fatal Error: Fatal: Shared library (dsdb2.so) failed to load: errno = (2), system message = ( 0509-022 Cannot load module /opt/IBM/InformationServer/Server/DSComponents/bin/dsdb2.so.
0509-150 Dependent module libdb2.a(shr.o) could not be loaded.
0509-022 Cannot load module libdb2.a(shr.o).
0509-026 System error: A file or directory in the path name does not exist.
0509-022 Cannot load module /opt/IBM/InformationServer/Server/DSComponents/bin/dsdb2.so.
0509-150 Dependent module /opt/IBM/InformationServer/Server/DSComponents/bin/dsdb2.so could not be loaded.)

Resolution
Looks like that you are using DB2 plugin. I think that you should put the library path in the dsenv. You will need to stop and re-start the DataStage to pick up the changes in dsenv.

SK generator gaps (Key doesn't created sequentially)

Resolution

1. Depends on what you mean, but I guess the simple answer is, whenever you use the file option with a block size > 1. Which is the 2nd worst performing configuration for it.

2. However, IF you do not use the 'start from highest value' option, then the SKG will backfill the 'gaps' in subsequent runs.

Some Important Links (IS Specific) - IBM

Silent installation of DataStage clients

http://publib.boulder.ibm.com/infocenter/iisinfsv/v8r1/topic/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/topics/wsisinst_silent.html?resultof=%22%73%69%6c%65%6e%74%22%20

Installation & Uninstallation of DataStage

http://publib.boulder.ibm.com/infocenter/iisinfsv/v8r1/index.jsp?topic=/com.ibm.swg.im.iis.productization.iisinfsv.install.doc/topics/wsisinst_uninstall_manual_windows.htmls

Minimum system requirements for Information Server 8.1 are documented here:

http://www.ibm.com/software/data/infosphere/info-server/overview/requirements.html

DataStage Performance Tuning Tips

Some of the Key factors for the consideration

1. Staged the data coming from ODBC/OCI/DB2UDB stages or any database on the server using Hash/Sequential files for optimum performance also for data recovery in case job aborts.
2. Tuned the OCI stage for 'Array Size' and 'Rows per Transaction' numerical values for faster inserts, updates and selects.
3. Tuned the 'Project Tunables' in Administrator for better performance.
4. Used sorted data for Aggregator.
5. Sorted the data as much as possible in DB and reduced the use of DS-Sort for better performance of jobs
6. Removed the data not used from the source as early as possible in the job.
7. Worked with DB-admin to create appropriate Indexes on tables for better performance of DS queries
8. Converted some of the complex joins/business in DS to Stored Procedures on DS for faster execution of the jobs.
9. If an input file has an excessive number of rows and can be split-up then use standard logic to run jobs in parallel.
10. Before writing a routine or a transform, make sure that there is not the functionality required in one of the standard routines supplied in the sdk or ds utilities categories.
    Constraints are generally CPU intensive and take a significant amount of time to process. This may be the case if the constraint calls routines or external macros but if it is inline code then the overhead will be minimal.
11. Try to have the constraints in the 'Selection' criteria of the jobs itself. This will eliminate the unnecessary records even getting in before joins are made.
12. Tuning should occur on a job-by-job basis.
13. Use the power of DBMS.
14. Try not to use a sort stage when you can use an ORDER BY clause in the database.
15. Using a constraint to filter a record set is much slower than performing a SELECT … WHERE….
16. Make every attempt to use the bulk loader for your particular database. Bulk loaders are generally faster than using ODBC or OLE.
18. Minimise the usage of Transformer (Instead of this use Copy modify Filter Row Generator)
19. Use SQL Code while extracting the data
20. Handle the nulls
21. Minimise the warnings
22. Reduce the number of lookups in a job design
23. Use not more than 20stages in a job
24. Use IPC stage between two passive stages Reduces processing time
25. Drop indexes before data loading and recreate after loading data into tables
26. Gen\'ll we cannot avoid no of lookups if our requirements to do lookups compulsory.
27. There is no limit for no of stages like 20 or 30 but we can break the job into small jobs then we use dataset Stages to store the data.
28. IPC Stage that is provided in Server Jobs not in Parallel Jobs
29. Check the write cache of Hash file. If the same hash file is used for Look up and as well as target disable this Option.
30. If the hash file is used only for lookup then \ enable Preload to memory\ . This will improve the performance. Also check the order of execution of the routines.
31. Don\'t use more than 7 lookups in the same transformer; introduce new transformers if it exceeds 7 lookups.
32. Use Preload to memory option in the hash file output.
33. Use Write to cache in the hash file input.
34. Write into the error tables only after all the transformer stages.
35. Reduce the width of the input record - remove the columns that you would not use.
37. Cache the hash files you are reading from and writting into. Make sure your cache is big enough to hold the hash files.
38. Use ANALYZE.FILE or HASH.HELP to determine the optimal settings for your hash files

This would also minimize overflow on the hash file. (Need for Server Jobs)

1. If possible break the input into multiple threads and run multiple instances of the job.
2. Staged the data coming from ODBC/OCI/DB2UDB stages or any database on the server using Hash/Sequential files for optimum performance also for data recovery in case job aborts.
3. Tuned the OCI stage for 'Array Size' and 'Rows per Transaction' numerical values for faster inserts updates and selects.
4. Tuned the 'Project Tunables' in Administrator for better performance.
5. Used sorted data for Aggregator.
6. Sorted the data as much as possible in DB and reduced the use of DS-Sort for better performance of jobs
7. Removed the data not used from the source as early as possible in the job.
8. Worked with DB-admin to create appropriate Indexes on tables for better performance of DS queries
9. Converted some of the complex joins/business in DS to Stored Procedures on DS for faster execution of the jobs.
10. If an input file has an excessive number of rows and can be split-up then use standard logic to run jobs in parallel.
11. Before writing a routine or a transform make sure that there is not the functionality required in one of the standard routines supplied in the sdk or ds utilities categories.
    Constraints are generally CPU intensive and take a significant amount of time to process. This may be the case if the constraint calls routines or external macros but if it is inline code then the overhead will be minimal.
12. Try to have the constraints in the 'Selection' criteria of the jobs itself. This will eliminate the unnecessary records even getting in before joins are made.
13. Tuning should occur on a job-by-job basis.
14. Use the power of DBMS.
15. Try not to use a sort stage when you can use an ORDER BY clause in the database.
16. Using a constraint to filter a record set is much slower than performing a SELECT WHERE .
17. Make every attempt to use the bulk loader for your particular database. Bulk loaders are generally faster than using ODBC or OLE.