[V8 z/OS] Excessive getpages/response time CICS and COBOL/DB2 cursors

Mark Vickers

[V8 z/OS] Excessive getpages/response time CICS and COBOL/DB2 cursors
Ever get that feeling you need to ask a question and you know you are
going to look stupid once it is out there ?
Oh well, I am drawing a blank so here goes...

Because a COBOL/CICS program terminates every time control is passed to
CICS, that also terminates open cursors and connections to DB2.
The problem stems from the fact that we offer too much on-line filtering
and then when scrolling, the program has to re-open the cursor every time
and I think the on-line query is even with optimize for N rows, gets DB2
to do much more than just the next/previous set of rows required.

I thought of Temporary tables, which would only get populated on a change
of the selection criteria, but they are only active for the duration of
the unit of work and the program essentially terminates when control is
passed back to CICS.

I am considering suggesting dynamic sql which can eliminate a lot of
superfluous predicates, but not sure of the overhead of the prepare
especially for on-line transactions, but this may only be a partial
solution.

Then I thought of clustering the data by the required sort order of the
on-line transaction, but what if there are more than one on-line sort
orders.
I considered a surrogate key, but that would only be useful for the
session as more orders could be coming in at any time.

Is there something I have missed or some magic solution to this?

thanks,
Mark Vickers
(hopefully not too red-faced right now :-).

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Vickers

Re: [V8 z/OS] Excessive getpages/response time CICS and COBOL/DB2 cursors
(in response to Mark Vickers)
Oops, I am on v7 not v8, so please do not respond, I will repost
correctly.




Mark E Vickers <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/12/2006 10:58 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[V8 z/OS] Excessive getpages/response time CICS and COBOL/DB2 cursors






Ever get that feeling you need to ask a question and you know you are
going to look stupid once it is out there ?
Oh well, I am drawing a blank so here goes...

Because a COBOL/CICS program terminates every time control is passed to
CICS, that also terminates open cursors and connections to DB2.
The problem stems from the fact that we offer too much on-line filtering
and then when scrolling, the program has to re-open the cursor every time
and I think the on-line query is even with optimize for N rows, gets DB2
to do much more than just the next/previous set of rows required.

I thought of Temporary tables, which would only get populated on a change
of the selection criteria, but they are only active for the duration of
the unit of work and the program essentially terminates when control is
passed back to CICS.

I am considering suggesting dynamic sql which can eliminate a lot of
superfluous predicates, but not sure of the overhead of the prepare
especially for on-line transactions, but this may only be a partial
solution.

Then I thought of clustering the data by the required sort order of the
on-line transaction, but what if there are more than one on-line sort
orders.
I considered a surrogate key, but that would only be useful for the
session as more orders could be coming in at any time.

Is there something I have missed or some magic solution to this?

thanks,
Mark Vickers
(hopefully not too red-faced right now :-).

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Bell

Re: [V8 z/OS] Excessive getpages/response time CICS and COBOL/DB2 cursors
(in response to Mark Vickers)
a Couple of answers -

1. DYNAMIC SQL is still very expensive compared to static (no suprise there)
Depending on the number of tables referenced you need to cut out 90% of the
data rows to justify this. I had a query that would run for about 40
minutes and the equivalent SPUFI was less than 2 seconds with local
variables instead of host variables but never got to make the change.
YMMV as usual. Dynamic SQL is harder to code but lots better than reading
rows and discarding them because DB2 didn't know about that filter.

2. You have just rediscovered the requirment for what IMS used to call SPA
(scratch pad area). Just create a table with key = user or term-id and put
already selected data in it. Problems include how to make sure the source
data hasn't changed, logging a large chunk of data each time the user hits
enter, making sure that the data in the scratch database matches the current
request, etc .

3. The single SQL with lots of WHERE col between :hv1 and :hv2 and you
either set the low and high value to 0 and large number or low-values and
high-values except for the one that actually has values is normally the
worst answer. That is usually a case for REOPTVARS which is the same as
dynamic SQL but without the extra coding.

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Mark E Vickers
Sent: Thursday, January 12, 2006 10:58 AM
To: [login to unmask email]
Subject: [DB2-L] [V8 z/OS] Excessive getpages/response time CICS and
COBOL/DB2 cursors

Ever get that feeling you need to ask a question and you know you are
going to look stupid once it is out there ?
Oh well, I am drawing a blank so here goes...

Because a COBOL/CICS program terminates every time control is passed to
CICS, that also terminates open cursors and connections to DB2.
The problem stems from the fact that we offer too much on-line filtering
and then when scrolling, the program has to re-open the cursor every time
and I think the on-line query is even with optimize for N rows, gets DB2
to do much more than just the next/previous set of rows required.

I thought of Temporary tables, which would only get populated on a change
of the selection criteria, but they are only active for the duration of
the unit of work and the program essentially terminates when control is
passed back to CICS.

I am considering suggesting dynamic sql which can eliminate a lot of
superfluous predicates, but not sure of the overhead of the prepare
especially for on-line transactions, but this may only be a partial
solution.

Then I thought of clustering the data by the required sort order of the
on-line transaction, but what if there are more than one on-line sort
orders.
I considered a surrogate key, but that would only be useful for the
session as more orders could be coming in at any time.

Is there something I have missed or some magic solution to this?

thanks,
Mark Vickers
(hopefully not too red-faced right now :-).

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: [V8 z/OS] Excessive getpages/response time CICS and COBOL/DB2 cursors
(in response to Mike Bell)
Mark,

I don't think Static, Dynamic or Static w/ reopt(vars) is the real issue
here.

Item #2 that Mike mentions below - the "scratch pad area". Could you not
use CICS temp storage queues (TSQ) that operate just like the spa? Typical
q names are terminal id + tran id to make it a unique name. Does not solve
all your problesm but at least mnimizes the cursor opens you need to do.

Thanks,
Suresh


>From: Mike Bell <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: [DB2-L] [V8 z/OS] Excessive getpages/response time CICS and
>COBOL/DB2 cursors
>Date: Thu, 12 Jan 2006 11:31:42 -0600
>
>a Couple of answers -
>
>1. DYNAMIC SQL is still very expensive compared to static (no suprise
>there)
>Depending on the number of tables referenced you need to cut out 90% of the
>data rows to justify this. I had a query that would run for about 40
>minutes and the equivalent SPUFI was less than 2 seconds with local
>variables instead of host variables but never got to make the change.
>YMMV as usual. Dynamic SQL is harder to code but lots better than reading
>rows and discarding them because DB2 didn't know about that filter.
>
>2. You have just rediscovered the requirment for what IMS used to call SPA
>(scratch pad area). Just create a table with key = user or term-id and put
>already selected data in it. Problems include how to make sure the source
>data hasn't changed, logging a large chunk of data each time the user hits
>enter, making sure that the data in the scratch database matches the
>current
>request, etc .
>
>3. The single SQL with lots of WHERE col between :hv1 and :hv2 and you
>either set the low and high value to 0 and large number or low-values and
>high-values except for the one that actually has values is normally the
>worst answer. That is usually a case for REOPTVARS which is the same as
>dynamic SQL but without the extra coding.
>
>Mike
>HLS Technologies
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
>Of Mark E Vickers
>Sent: Thursday, January 12, 2006 10:58 AM
>To: [login to unmask email]
>Subject: [DB2-L] [V8 z/OS] Excessive getpages/response time CICS and
>COBOL/DB2 cursors
>
>Ever get that feeling you need to ask a question and you know you are
>going to look stupid once it is out there ?
>Oh well, I am drawing a blank so here goes...
>
>Because a COBOL/CICS program terminates every time control is passed to
>CICS, that also terminates open cursors and connections to DB2.
>The problem stems from the fact that we offer too much on-line filtering
>and then when scrolling, the program has to re-open the cursor every time
>and I think the on-line query is even with optimize for N rows, gets DB2
>to do much more than just the next/previous set of rows required.
>
>I thought of Temporary tables, which would only get populated on a change
>of the selection criteria, but they are only active for the duration of
>the unit of work and the program essentially terminates when control is
>passed back to CICS.
>
>I am considering suggesting dynamic sql which can eliminate a lot of
>superfluous predicates, but not sure of the overhead of the prepare
>especially for on-line transactions, but this may only be a partial
>solution.
>
>Then I thought of clustering the data by the required sort order of the
>on-line transaction, but what if there are more than one on-line sort
>orders.
>I considered a surrogate key, but that would only be useful for the
>session as more orders could be coming in at any time.
>
>Is there something I have missed or some magic solution to this?
>
>thanks,
>Mark Vickers
>(hopefully not too red-faced right now :-).
>
>----------------------------------------------------------------------------
>-----
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org.
>The IDUG List Admins can be reached at [login to unmask email]
>Find
>out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003
>
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003
>
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Cristian Molaro

Re: [V8 z/OS] Excessive getpages/response time CICS and COBOL/DB2 cursors
(in response to Suresh Sane)
Hi Mark,

I got a similar problem, as CICS screen offering "on-line filtering" can be
one of the biggest CPU consumers by the end of the day.
Dynamic SQL worked for me on a PL/I program where one of the cursors
contained more than 10 conditions as follows:

....
AND (COL2 = :HOST2 OR
'' = :HOST2)
...

I changed the program introducing dynamic SQL, where a variable called STMT
contains the query to be executed:

/* ------------------------------------------------------------------*/
/* OPTIONAL WHERE CLAUSE ELEMENTS */
/* ------------------------------------------------------------------*/
IF HOST2 ^= '' THEN
STMT = STMT
!! ' AND (COL2 = ''' !! HOST2 !! ''')';

In that way, only the fields really needed for the filtering are part of the
WHERE clause.
Access path changed and getpages dropped from 300.000 to 18 for the typical
case.
Of course, all depends on your current setup, and may not work for you, but
improving your queries could save you the exercise of finding alternatives
to cursor re-open.

Have a nice day!

Cristian Molaro
MConsulting.be


On Thu, 12 Jan 2006 10:58:18 -0600, Mark E Vickers
<[login to unmask email]> wrote:

>Ever get that feeling you need to ask a question and you know you are
>going to look stupid once it is out there ?
>Oh well, I am drawing a blank so here goes...
>
>Because a COBOL/CICS program terminates every time control is passed to
>CICS, that also terminates open cursors and connections to DB2.
>The problem stems from the fact that we offer too much on-line filtering
>and then when scrolling, the program has to re-open the cursor every time
>and I think the on-line query is even with optimize for N rows, gets DB2
>to do much more than just the next/previous set of rows required.
>
>I thought of Temporary tables, which would only get populated on a change
>of the selection criteria, but they are only active for the duration of
>the unit of work and the program essentially terminates when control is
>passed back to CICS.
>
>I am considering suggesting dynamic sql which can eliminate a lot of
>superfluous predicates, but not sure of the overhead of the prepare
>especially for on-line transactions, but this may only be a partial
>solution.
>
>Then I thought of clustering the data by the required sort order of the
>on-line transaction, but what if there are more than one on-line sort
>orders.
>I considered a surrogate key, but that would only be useful for the
>session as more orders could be coming in at any time.
>
>Is there something I have missed or some magic solution to this?
>
>thanks,
>Mark Vickers
>(hopefully not too red-faced right now :-).
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm