MVSFORUMS.com

mainframes530 · Beginner Joined: 13 Mar 2008 Posts: 4 Topics: 1

Hi.
my cobol pgm contains 2 cursors which are taking lot of time to execute.could any one help me in optimizing the below queries.

bauer · Posted: Sat Mar 15, 2008 2:23 pm Post subject:

mainframes530,

try to use EXPLAIN command .....

Indexstructure ?

Runstatsdata ?

CraigG · Posted: Sat Mar 15, 2008 7:18 pm Post subject:

How many rows in the tables? What is a lot of time?

I would suggest replacing this

mainframes530 · Beginner Joined: 13 Mar 2008 Posts: 4 Topics: 1

bauer,
thanks for your reply,
Could you please more specific about how to use explain?
provide material for the same.

mainframes530 · Beginner Joined: 13 Mar 2008 Posts: 4 Topics: 1

CraigG,
thnks.table contains 7 million records.it is taking around 5-6 hrs.

Nic Clouston · Posted: Sun Mar 16, 2008 6:14 am Post subject:

EXPLAIN - is explained in the manuals link. Select DB2 programmers guide and do a search on EXPLAIN with the search criteria set to EXACT and the first link tells you how to code it and the second how to use the results.

5 hours and 7 million rows - not very long.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

dr_te_z · Beginner Joined: 08 Feb 2007 Posts: 18 Topics: 3

bauer · Posted: Mon Mar 17, 2008 4:00 am Post subject:

Can anybody pls. explain, why the IN predicate in the shown situation is better than using the OR Syntax ?

CraigG · Posted: Mon Mar 17, 2008 7:16 am Post subject:

dr_te_z · Beginner Joined: 08 Feb 2007 Posts: 18 Topics: 3

No, I know. I did not say that. I just said that it can be re-coded that way.
You must look close at the content of you host-vars. Just add/subtrract 1 day where needed and you're done.

mainframes530 · Beginner Joined: 13 Mar 2008 Posts: 4 Topics: 1

@Nic Clouston
Thanks a lot,i will definitely do some research on EXPLAIN

@ CraigG,
thanks a lot craig,i completely agree with what ever u said regarding Code:
WHERE B.EFCT_DATE < :HV-END-DATE
AND B.EFCT_DATE > :HV-BGN-DATE
you are correct

@bauer
even i found the same in one of the DB2 manual I.e IN is better than OR for similar pedicates.

@dr_te_z
Dude,Had it been a WHERE B.EFCT_DATE <= :HV-END-DATE
AND B.EFCT_DATE >= :HV-BGN-DATE
then you would've been corect.

dr_te_z · Beginner Joined: 08 Feb 2007 Posts: 18 Topics: 3

So? Code like

bauer · Posted: Thu Mar 20, 2008 10:48 am Post subject:

......and what about the explain results ?

haatvedt · Posted: Sun Apr 13, 2008 11:29 pm Post subject:

Mainframes530,

I've used Platinum unloads with this size table to unload each table separately and then do the "joins" with COBOL programs having file matching logic. If the matches are one to one or on to many then its pretty straight forward. If the join is a cartesian join (many to many) then the COBOL program is a bit more complicated but can still be done if all the matching keys for one table can be loaded into an array. the array will hold all the rows for a single key value and will be reloaded each time a control break is encountered.

with this size of tables I would estimate that the entire process would take 20 to 30 minutes and that is a high estimate.

good luck

ps.. send me an email offline if you want to discuss this in more detail. the same process can be used for a 3 table join.
_________________
Chuck Haatvedt

email --> clastnameatcharterdotnet

(replace lastname, at, dot with appropriate
characters)

subho · Posted: Mon Apr 14, 2008 11:17 am Post subject:

Mainframes530,

Have you considered to add any more predicate? Have you considered to create a small set of table for the larger table? Have your SQL expedited using IN and BETWEEN?

Please let us know
1. Row count in ZLS_PER , Z_TRAN, ZRAN and ZRODUCT
2. Primary/clustered/secondary indexes for these table
3. Cardinality(1:1, 1:N, N:1) of these tables

This will help us to give the better solution.

Thanks,
Subho