We have recently implemented PCI compliance on AIX box where Information Server is installed. Situation is whever we are trying to call Db sequence(DB2) in transaformer stage it is taking ages (1.5 hrs) to generate keys for paltry 0.1 million records. For testing we have designed a simple test job
ROW GEN -> XFMR -> PEEK
1) DBA gave the opinion that nothing is problematic from their end.
2) Same sequence when called in an SQL query using Db2 connector is running very fast, no issues with it.
Can we trace the process through which the transformer is calling the Db sequence and probably look for bottleneck.
Any suggestion would be higly appreciated.
thabnnks
Surrogate Key (Db Sequence) using Transformer: Very slow
Moderators: chulett, rschirm, roy
-
anand_dafaria
- Premium Member
- Posts: 36
- Joined: Wed Dec 27, 2006 3:50 am
- Location: Phoenix
As far as tracing is concerned, I can not tell you much about it, may be you can contact your UNIX administrator.
Regarding surrogate key, it will be good if you can create the surrogate key with in the scope of datastage itself.Use surrogate key generator stage, create the sequence once through datastage job and then use it as many times as you want.You can store the key values onto your UNIX filesystem files.If you have a 32 bit UNIX, then the maximum value supported for surrogate key is 2 raise to power 32 minus 1.If it is 64 bit then this value will change to 2 raise to power 64 minus 1.
HTH
Regarding surrogate key, it will be good if you can create the surrogate key with in the scope of datastage itself.Use surrogate key generator stage, create the sequence once through datastage job and then use it as many times as you want.You can store the key values onto your UNIX filesystem files.If you have a 32 bit UNIX, then the maximum value supported for surrogate key is 2 raise to power 32 minus 1.If it is 64 bit then this value will change to 2 raise to power 64 minus 1.
HTH
Arvind