Performance Issue with DataStage 9.1 Server Jobs
Moderators: chulett, rschirm, roy
Performance Issue with DataStage 9.1 Server Jobs
Hello All,
Thank for reading this read and here is the summary of the issue I am facing in production.
Recently we've move from datastage 8.5 server edition to 9.1.2 server edition. But few of the jobs are running longer than expected in 9.1.2 edition.
Summary:
1. Latest ODBC driver is used
2. Operational DB is not used
3. Temp folder and other clean up procedures are taken care of.
Job Design:
Source DB --> Dim hashed file lookup --> Surrogate key hashed file look up - Target ODS
The problem mostly appears when the no of jobs accessing the surrogate key hashed file hit around 10 at a time.
How can I improve the performance of hashed file lookup at this instance.
Please note that the schedule changes wouldn't work since the job schedule depends upon the file arrival. This is regulatory compliance and
Thank for reading this read and here is the summary of the issue I am facing in production.
Recently we've move from datastage 8.5 server edition to 9.1.2 server edition. But few of the jobs are running longer than expected in 9.1.2 edition.
Summary:
1. Latest ODBC driver is used
2. Operational DB is not used
3. Temp folder and other clean up procedures are taken care of.
Job Design:
Source DB --> Dim hashed file lookup --> Surrogate key hashed file look up - Target ODS
The problem mostly appears when the no of jobs accessing the surrogate key hashed file hit around 10 at a time.
How can I improve the performance of hashed file lookup at this instance.
Please note that the schedule changes wouldn't work since the job schedule depends upon the file arrival. This is regulatory compliance and
Hello Chulett,
The server jobs are running for a longer time than 8.5 edition.
here is the scenario,
1. If the jobs are not concurrent than the performance is good,
2. But when the jobs are running in parallel the run time is more than the 8.5 server edition. Please note, in 8.5 under the same scenario, the jobs are okay.
3. Yes. We did review the UVConfig files as per IBM recommendation and it looks good.
I have a hashed file which stores the last value of surrogate key and its being looked up at the same time by concurrent jobs. I believe, the hash file lock seems to be an issue. I'm, not sure how to overcome this issue.
Again, thanks for your help.
The server jobs are running for a longer time than 8.5 edition.
here is the scenario,
1. If the jobs are not concurrent than the performance is good,
2. But when the jobs are running in parallel the run time is more than the 8.5 server edition. Please note, in 8.5 under the same scenario, the jobs are okay.
3. Yes. We did review the UVConfig files as per IBM recommendation and it looks good.
I have a hashed file which stores the last value of surrogate key and its being looked up at the same time by concurrent jobs. I believe, the hash file lock seems to be an issue. I'm, not sure how to overcome this issue.
Again, thanks for your help.
Okay, thanks, but I meant specifics, telling us they run longer (twice) doesn't help to quantify the scope of the issue. Is it a small percentage longer? 2x longer? Hours longer? Trying to get an idea of how much longer here.
Your hashed file for surrogates, is it the official one and you are leveraging it via the supplied function with the "concurrency" option? This does in fact lock the record so the value can only be issued once before being incremented but I don't recall performance issues from using it which we did quite a bit. Or did you roll your own?
Your hashed file for surrogates, is it the official one and you are leveraging it via the supplied function with the "concurrency" option? This does in fact lock the record so the value can only be issued once before being incremented but I don't recall performance issues from using it which we did quite a bit. Or did you roll your own?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Here are my answers:
1. It runs almost twice as long as the 8.5 version.
2. We use our own surrogate key logic. I have routine, which calls the surrogate key file and generates the maximum value.
Hash file logic: Look up the surrkey hash value, if value present then dont create new key, if not go to routine and picks the max key.
1. It runs almost twice as long as the 8.5 version.
2. We use our own surrogate key logic. I have routine, which calls the surrogate key file and generates the maximum value.
Hash file logic: Look up the surrkey hash value, if value present then dont create new key, if not go to routine and picks the max key.
Thanks.
When you say "picks/generates the max value" does that mean something like a "select max(id) from table" in a database for each particular target? Or a hashed file with the last max that you lookup by source and increment by one? The former could get slower and slower over time regardless of version. For the latter, you should seriously look into the one supplied with the product as it works just fine. Either switch over to it or incorporate some of its design into yours. Otherwise would need to know more about the hashed file design - type, key, etc - to help in any way.
When you say "picks/generates the max value" does that mean something like a "select max(id) from table" in a database for each particular target? Or a hashed file with the last max that you lookup by source and increment by one? The former could get slower and slower over time regardless of version. For the latter, you should seriously look into the one supplied with the product as it works just fine. Either switch over to it or incorporate some of its design into yours. Otherwise would need to know more about the hashed file design - type, key, etc - to help in any way.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I'm working on the hashed file design. meanwhile, I'd like to get a feel around the UVConfig file values. I have listed the important values down below. Do you see any issues with that/=...?
DS8.5 DS9.1
MFILES 150 MFILES 200
T30FILE 512 T30FILE 600
OPENCHK 1 OPENCHK 1
WIDE0 3dc00000 WIDE0 3dc00000
UVSPOOL /tmp UVSPOOL /psoft/bnydata/etl/
UVTEMP /tmp UVTEMP /psoft/bnydata/etl/
LOGBLSZ 512 LOGBLSZ 512
LOGBLNUM 8 LOGBLNUM 8
LOGSYCNT 0 LOGSYCNT 0
LOGSYINT 0 LOGSYINT 0
TXMEM 32 TXMEM 32
OPTMEM 64 OPTMEM 64
SELBUF 4 SELBUF 4
ULIMIT 128000 ULIMIT 128000
FLTABSZ 11 FLTABSZ 11
GLTABSZ 75 GLTABSZ 150
RLTABSZ 150 RLTABSZ 300
SYNCALOC 0 SYNCALOC 0
MAXRLOCK 149 MAXRLOCK 299
MAXERRLOGENT 100 MAXERRLOGENT 100
JOINBUF 4095 JOINBUF 4095
64BIT_FILES 0 64BIT_FILES 0
TSTIMEOUT 60 TSTIMEOUT 60
PIOPENDEFAULT 0 PIOPENDEFAULT 0
NLSMODE 1 NLSMODE 1
DS8.5 DS9.1
MFILES 150 MFILES 200
T30FILE 512 T30FILE 600
OPENCHK 1 OPENCHK 1
WIDE0 3dc00000 WIDE0 3dc00000
UVSPOOL /tmp UVSPOOL /psoft/bnydata/etl/
UVTEMP /tmp UVTEMP /psoft/bnydata/etl/
LOGBLSZ 512 LOGBLSZ 512
LOGBLNUM 8 LOGBLNUM 8
LOGSYCNT 0 LOGSYCNT 0
LOGSYINT 0 LOGSYINT 0
TXMEM 32 TXMEM 32
OPTMEM 64 OPTMEM 64
SELBUF 4 SELBUF 4
ULIMIT 128000 ULIMIT 128000
FLTABSZ 11 FLTABSZ 11
GLTABSZ 75 GLTABSZ 150
RLTABSZ 150 RLTABSZ 300
SYNCALOC 0 SYNCALOC 0
MAXRLOCK 149 MAXRLOCK 299
MAXERRLOGENT 100 MAXERRLOGENT 100
JOINBUF 4095 JOINBUF 4095
64BIT_FILES 0 64BIT_FILES 0
TSTIMEOUT 60 TSTIMEOUT 60
PIOPENDEFAULT 0 PIOPENDEFAULT 0
NLSMODE 1 NLSMODE 1
Please see these similar topics.
viewtopic.php?t=152133
viewtopic.php?t=154037
Also, have you checked with Support? You are on an old version and are probably not the first to encounter this type of issue.
viewtopic.php?t=152133
viewtopic.php?t=154037
Also, have you checked with Support? You are on an old version and are probably not the first to encounter this type of issue.
Choose a job you love, and you will never have to work a day in your life. - Confucius
All of the suggestions were already tried in my project and it dint help.
viewtopic.php?t=152133
viewtopic.php?t=154037
Summary:
1. The issue is with the server jobs alone and thre are no parallel jobs in my project.
2. We are not using Operational DB in my project.
Thanks,
Saran.
viewtopic.php?t=152133
viewtopic.php?t=154037
Summary:
1. The issue is with the server jobs alone and thre are no parallel jobs in my project.
2. We are not using Operational DB in my project.
Thanks,
Saran.