REBIND APREUSE(WARN) in DB2 V11 zOS CM does not work

Nicolas Bran

REBIND APREUSE(WARN) in DB2 V11 zOS CM does not work

Hi,

 

We are in DB2 V11 CM momently and we have tried this WE to rebind some packages with the keyword APREUSE(WARN). RC of the job was 0 but unfortunately, the access paths have changed.

 

Do you have an idea. Does APREUSE(WARN) only work correctly with DB2 V11 NFM?

 

Thanks in advance,

Nicolas Bran

 

 

 

Walter Janißen

AW: REBIND APREUSE(WARN) in DB2 V11 zOS CM does not work
(in response to Nicolas Bran)
Hi Nicolas

APREUSE(WARN) does not prevent that the access path changes. Db2 tries to reuse the previous access path, but if it can’t it generates a new one and issues a warning message. You can find the reason, why Db2 couldn’t reuse the access path, in column REMARKS of the appropriate PLAN_TABLE.
If you don’t want that Db2 creates a new access path you have to specify APREUSE(ERROR). In this case the whole rebind will fail.

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: Nicolas Bran [mailto:[login to unmask email]
Gesendet: Montag, 25. September 2017 10:56
An: [login to unmask email]
Betreff: [DB2-L] - REBIND APREUSE(WARN) in DB2 V11 zOS CM does not work


Hi,



We are in DB2 V11 CM momently and we have tried this WE to rebind some packages with the keyword APREUSE(WARN). RC of the job was 0 but unfortunately, the access paths have changed.



Do you have an idea. Does APREUSE(WARN) only work correctly with DB2 V11 NFM?



Thanks in advance,

Nicolas Bran







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

  • image001.png (2.6k)

Nicolas Bran

RE: AW: REBIND APREUSE(WARN) in DB2 V11 zOS CM does not work
(in response to Walter Janißen)

Hi Walter,

 

Thanks for your answer. This is the output of my rebind:

 

REBIND PACKAGE(ROUTCICC.GE9KT071.(*)) DBPROTOCOL(DRDA) APREUSE(WARN);

DSNT286I -PD1P DSNTBBP2 REBIND FOR PACKAGE = PD01.ROUTCICC.GE9KT071,

USE OF APREUSE RESULTS IN:

23 STATEMENTS WHERE APREUSE IS SUCCESSFUL

0 STATEMENTS WHERE APREUSE IS EITHER NOT SUCCESSFUL

OR PARTIALLY SUCCESSFUL

0 STATEMENTS WHERE APREUSE COULD NOT BE PERFORMED

0 STATEMENTS WHERE APREUSE WAS SUPPRESSED BY OTHER HINTS.

 

As you may see, all the access paths should have been reused what is not case. In some access paths, we can see a switch from one index to another one and unfortunately, we encounter a performance degradation.

 

Nicolas

Walter Jani&#223;en

AW: AW: REBIND APREUSE(WARN) in DB2 V11 zOS CM does not work
(in response to Nicolas Bran)
Hi Nicolas

In this case I would open a PMR.

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: Nicolas Bran [mailto:[login to unmask email]
Gesendet: Montag, 25. September 2017 11:09
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: REBIND APREUSE(WARN) in DB2 V11 zOS CM does not work


Hi Walter,



Thanks for your answer. This is the output of my rebind:



REBIND PACKAGE(ROUTCICC.GE9KT071.(*)) DBPROTOCOL(DRDA) APREUSE(WARN);

DSNT286I -PD1P DSNTBBP2 REBIND FOR PACKAGE = PD01.ROUTCICC.GE9KT071,

USE OF APREUSE RESULTS IN:

23 STATEMENTS WHERE APREUSE IS SUCCESSFUL

0 STATEMENTS WHERE APREUSE IS EITHER NOT SUCCESSFUL

OR PARTIALLY SUCCESSFUL

0 STATEMENTS WHERE APREUSE COULD NOT BE PERFORMED

0 STATEMENTS WHERE APREUSE WAS SUPPRESSED BY OTHER HINTS.



As you may see, all the access paths should have been reused what is not case. In some access paths, we can see a switch from one index to another one and unfortunately, we encounter a performance degradation.



Nicolas

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

  • image001.png (2.6k)