DB2 z/OS v11 NFM -243 on sensitive scroll cursor

Tim Wilkins

DB2 z/OS v11 NFM -243 on sensitive scroll cursor

We had an odd thing occur.   A developer changed a programs cursors to be sensitive scroll-able and the compile and bind on the test systems did not get a -243. 

When they went to migrate to production we do a pre bind against production tables and the pre bind failed with a -243.   I can see why because they have an order by which would cause DB2 to create a interim work results table.

The question is why no -243 on the test systems that have several hundred thousand rows in the stats and table.  The production table has 28 million rows.   How is DB2 determining when to throw a -243 at bind time and not to?  

I checked the order by column stats in production and the test systems thinking there was only one value on the test systems and I did not see that.  The number of distinct values matched the production table. 

 

Walter Janißen

AW: DB2 z/OS v11 NFM -243 on sensitive scroll cursor
(in response to Tim Wilkins)
Hi Tim

Did you check the access paths? I would guess that in the test system DB2 chooses an index, which avoids a sort and in production it doesn’t or it couldn’t. Did you use FOR FETCH ONLY. If not that could also make a difference. In general an insensitive cursor forces DB2 to avoid a sort, if possible. I talked to Terry several times about insensitive scroll cursors and avoiding a sort, if the right index exists. The compromise was: If there is an ORDER BY you also have to use FOR FETCH ONLY. But GROUP BY is still not supported, even if there is an appropriate index.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Tim Wilkins [mailto:[login to unmask email]
Gesendet: Dienstag, 16. Mai 2017 19:52
An: [login to unmask email]
Betreff: [DB2-L] - DB2 z/OS v11 NFM -243 on sensitive scroll cursor


We had an odd thing occur. A developer changed a programs cursors to be sensitive scroll-able and the compile and bind on the test systems did not get a -243.

When they went to migrate to production we do a pre bind against production tables and the pre bind failed with a -243. I can see why because they have an order by which would cause DB2 to create a interim work results table.

The question is why no -243 on the test systems that have several hundred thousand rows in the stats and table. The production table has 28 million rows. How is DB2 determining when to throw a -243 at bind time and not to?

I checked the order by column stats in production and the test systems thinking there was only one value on the test systems and I did not see that. The number of distinct values matched the production table.



-----End Original Message-----
Attachments

  • image003.png (2.6k)

Tim Wilkins

RE: AW: DB2 z/OS v11 NFM -243 on sensitive scroll cursor
(in response to Walter Janißen)

I compared the access path in test and production and they are the same.  DB2 uses the same index with 4 matching columns.  

What they did was add a for update only and that worked along with the order by.  

Edited By:
Tim Wilkins[Organization Members] @ May 17, 2017 - 10:50 AM (America/Central)