DSXchange

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

Can you give us some specific idea what "running longer than expected" means here? And if this is a new server, have you compared the values in your uvconfig file between them, especially the ones related to hashed files?

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.

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?

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.

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.

saratha14 wrote:It runs almost twice as long as the 8.5 version.

So did it go from 1 min to 2 mins?

10 hours to 20 hours?

The performance with 8.5 version is 35 mins and 90 mins with 9.1.2 version.

Thanks,
Saran

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

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.

Thank you.! I have onboarded IBM on this topic. I will check the links and get back to you folks.

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.

Hello Everyone,

We are going to try the following options on the hashed files.

1. Minimum modulus size to be assessed and changed
2. Changing the group size for the dynamic hashed file to 2 from 1.