asked on

Mainframe z/OS High CPU, I/O, Getpage and 7 minutes run time observed for SQL Open Cursor with a two table join. How can this be reduced?

Hi all,

I have a SQL that joins two tables in a declare cursor statement in a COBOL program . During open cursor call, we are observing a high getpage count (millions of getpages), high CPU and I/O numbers and taking about 7 minutes to complete.

Can anyone share some thoughts and ideas for possible cause and potential solutions? What are the areas/things to research/review for possible causes and to remedy this performance slow-down?

Any question or more info, please let me know.

Thanks

SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Enyinnaya

ASKER

moni_sabag,
Thanks for your last post on this thread. Some other issues took my attention elsewhere.
Yes, I might be missing an index but I also did some analysis, run explain on the SQL after adding a another index on a suspect column of the table with the high getpage and CPU counts, reorg and runstat and in all, DB2 still chose the original index that it used ignoring thr index that I added.

I will provide the SQL in question (DECLARE CURSOR statements), the indexes on the table when I get in the office Friday morning. I hope you will and other may be able to help with this issue.

ASKER CERTIFIED SOLUTION

Tomas Helgi Johannsson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Enyinnaya

ASKER

Hi all, bear with me regarding this thread and the two request for more information. I will get it out on Monday. I've been swamped lately in the office....

Thanks for your patience..

Enyinnaya

ASKER

Thanks guys...Our problem seem to be periodic and I have not been able to reproduce it with the relevant index and column stats.