MVSFORUMS.com

[subho]

When I use platinum unload batch utility to unload a single DB2 table, it works great. But when I use this unload by joining two (2) DB2 tables, it runs for a long time and eventually I have to kill the job. Only change I have to make is from "SQL ACCESS NONE" to "SQL ACCESS ONLY". Is there anyway to unload effeciently by joing two tables? Do I have to make any changes to these parameters?

These two tables have 4-6 millions records each.

Here is the platinum unload card I am using:

[haatvedt]

Subho,

I would suggest that you unload each of the DB2 tables separately using SQL ACCESS NONE. then write a COBOL program to do the join by using file match logic. We do this quite regularly and it works very well. With 4 to 6 million rows I would guess that the unloads would finish in a couple of minutes each. The COBOL match program will probably run in about the same amount of time. The total time for the process should be 10 to 15 minutes depending on the availability of cpu resources.

to reduce the total elapsed time, the two tables could be unloaded in separate jobs which run in parallel. However, since the tables are quite small, its probably better to leave them in the same job.

good luck,
_________________
Chuck Haatvedt

email --> clastnameatcharterdotnet

(replace lastname, at, dot with appropriate
characters)

[subho]

Chuck,
Thanks for your suggestion. I have already thought (matter of fact, wrote the high level spec) in the same route if platinum join of two tables don't work efficiently. But I was wondering if there is a way to make this more efficient using Platinum, IBM or any other utilities. COBOL program will have very convoluted complex logic for match merge since it will have other WHERE clause criteria in this SQL which I have not mentioned for simplicity. It will definitely be less maintenance work if I use utility. In future, we may have to change the where clause depending on the business need. Then changing the COBOL program will be a nightmare!!

ANY help with utlity will be appreciated.


Thanks,
Subho

[haatvedt]

Subho,

each of the unloads could still specify the predicate restrictions as follows. Then after that the COBOL program will just be a simple match program.

This will be much faster than the DB2 join process. just test the two unload steps without the COBOL match program and see how fast the unloads are.

If Platinum complains about the use of the IN clause, just convert it into two NOT EQUAL clauses.

I'm between jobs right now so I can't run a check on the IN clause. And even if it doesn't like it, the IN clause will be easy to eliminate.

[subho]

Chuck,
Thanks for your reply. Yes, I will go in this route if i don't find any alternative to get the result using platinum or any utility directly. Again, I have two main concerns here.
1. Performance (Looking for the utility, not the COBOL program way)
2. Maintenance (This is an extremely critical SQL for financial performance process, it changes very frequently and support staff may change over the time. All these things together, it would be better to make the entire process in a single utility job).

In your approach, we have solved the issue number 1 , but not the number 2. A fine tuned utility will solve both issues.

Is there any thought from anybody else. Kolusu, do you have any suggestion?

Thanks,
Subho

[haatvedt]

Subho,

the issue is that the utility will basically just execute the query as dynamic sql. If you do an explain on the query I suspect that it is doing a nested loop join. I'm not sure about that but the DB2 join is much more costly than a sequential file matching process.

good luck.

I would suggest that you do an explain on the SQL query.
_________________
Chuck Haatvedt

email --> clastnameatcharterdotnet

(replace lastname, at, dot with appropriate
characters)

[subho]

Chuck,
Unloading multiple tables and match merging in COBOL program is a standard practise which we do a lot in our shop. My point is to avoid this process and get the platinum working.

Any help will be appreciated if anybody knows a way to do this effectively using Platinum or IBM utility.

Thanks,
Subho