Db2 12 star * function levels

Aurora Emanuela Dellanno

Db2 12 star * function levels

so, I read in the What's New book that "Activating a lower star (*) function level by itself does not immediately disable all use of capabilities at higher function levels. Instead, it provides flexibility for limiting or disabling the use of capabilities at higher function levels, while the problems encountered in higher function levels are resolved."

 

can someone please explain this to me in plain English? is it to do basically with the default APPLCOMPAT?

 

This is confusing me even further: "applications that use capabilities at a higher function level can continue to do so if they are not related to the reason for activating the lower function level" - how does Db2 KNOW what the reason for a function level is? do you need to have a sit-down and friendly talk with it?

 

TIA for anything that gets me out of this maze...

 

Aurora

Roy Boxwell

Db2 12 star * function levels
(in response to Aurora Emanuela Dellanno)
What they mean is that if you have bound 10,000 Packages at , say, FL503 and then see that some SQLs are going wrong for whatever reason. You can then fall back the system to FL502 and REBIND the static packages that are causing trouble leaving the rest, if they are *not* causing grief. You do not want 10,000 AUTOBINDs do you? Dynamic SQL is a bit trickier as you have to bind the Dynamic SQL Access packages (SYSLHxx etc etc) also at the lower FL level to stop any “rogue” SQLs running at the bad FL level.



APPLCOMPAT is really a package level attribute to peg packages at, say, FL501 even though you are happily running at FL503 – This gives you time to fix things like ICIs etc.



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Aurora Emanuela Dellanno [mailto:[login to unmask email]
Sent: Wednesday, October 10, 2018 3:26 PM
To: [login to unmask email]
Subject: [DB2-L] - Db2 12 star * function levels



so, I read in the What's New book that "Activating a lower star (*) function level by itself does not immediately disable all use of capabilities at higher function levels. Instead, it provides flexibility for limiting or disabling the use of capabilities at higher function levels, while the problems encountered in higher function levels are resolved."



can someone please explain this to me in plain English? is it to do basically with the default APPLCOMPAT?



This is confusing me even further: "applications that use capabilities at a higher function level can continue to do so if they are not related to the reason for activating the lower function level" - how does Db2 KNOW what the reason for a function level is? do you need to have a sit-down and friendly talk with it?



TIA for anything that gets me out of this maze...



Aurora



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

Attachments

  • smime.p7s (5.1k)

Javier Estrada Benavides

RE: Db2 12 star * function levels
(in response to Aurora Emanuela Dellanno)

Hi Auora

  An additional note is what happens with your tablespaces and other objects. If they were created to use capabilities of a new Function Level but you activate a lower function (going to a star Function Level), you will not be able to drop or redefine such applicable objects, however, these objects are still usable.

 

Regards,

Javier Estrada Benavides, Czech Republic / Mexico

IBM Champion for Analytics

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Database Administrator - DB2 11 DBA for z/OS

Aurora Emanuela Dellanno

Db2 12 star * function levels
(in response to Roy Boxwell)
Thanks Roy (and Javier).

Yes, that's what I thought, but here, from my understanding, you actually
take ALL the packages back to the lower * level, not just one or two, all
you do is disable the function of the new FL for ALL packages, and any new
objects CREATED in the lower * FL will have to be bound or rebound again at
rrhe higher FL once you go back to it.

So, again from what I understand, it's like a subsystem-wide APPLCOMPAT
(and any objects created in this mode will have to be bound/rebound with
the higher FL, as I said above).

Wrong?

On Thu, 11 Oct 2018 at 07:23, Boxwell, Roy <[login to unmask email]> wrote:

> What they mean is that if you have bound 10,000 Packages at , say, FL503
> and then see that some SQLs are going wrong for whatever reason. You can
> then fall back the system to FL502 and REBIND the static packages that are
> causing trouble leaving the rest, if they are **not** causing grief. You
> do not want 10,000 AUTOBINDs do you? Dynamic SQL is a bit trickier as you
> have to bind the Dynamic SQL Access packages (SYSLHxx etc etc) also at the
> lower FL level to stop any “rogue” SQLs running at the bad FL level.
>
>
>
> APPLCOMPAT is really a package level attribute to peg packages at, say,
> FL501 even though you are happily running at FL503 – This gives you time to
> fix things like ICIs etc.
>
>
>
> *Roy Boxwell*
>
> SOFTWARE ENGINEERING GMBH and SEGUS Inc.
> -Product Development-
>
>
> *Heinrichstrasse 83-8540239 Duesseldorf/Germany*
> *Tel. *
>
> *+49 (0)211 96149-675Fax +49 (0)211 96149-32Email: **[login to unmask email]*
> <[login to unmask email]>
> *http://www.seg.de* http://www.seg.de
>
>
>
> *Software Engineering GmbHAmtsgericht Düsseldorf, HRB
> 37894Geschäftsführung: Gerhard Schubert, Ulf Heinrich*
>
>
>
> *From:* Aurora Emanuela Dellanno [mailto:[login to unmask email]
> *Sent:* Wednesday, October 10, 2018 3:26 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Db2 12 star * function levels
>
>
>
> so, I read in the What's New book that "Activating a lower star (*)
> function level by itself does not immediately disable all use of
> capabilities at higher function levels. Instead, it provides flexibility
> for limiting or disabling the use of capabilities at higher function
> levels, while the problems encountered in higher function levels are
> resolved."
>
>
>
> can someone please explain this to me in plain English? is it to do
> basically with the default APPLCOMPAT?
>
>
>
> This is confusing me even further: "applications that use capabilities at
> a higher function level can continue to do so if they are not related to
> the reason for activating the lower function level" - how does Db2 KNOW
> what the reason for a function level is? do you need to have a sit-down and
> friendly talk with it?
>
>
>
> TIA for anything that gets me out of this maze...
>
>
>
> Aurora
>
>
> -----End Original Message-----
>

Roy Boxwell

Db2 12 star * function levels
(in response to Aurora Emanuela Dellanno)
Yep – You can definitely look at it that way! Just remember that the BIND/REBINDS going in both directions are optional...(The hope is that you will never ever actually have to fall back of course...)



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, October 11, 2018 12:50 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 12 star * function levels



Thanks Roy (and Javier).



Yes, that's what I thought, but here, from my understanding, you actually take ALL the packages back to the lower * level, not just one or two, all you do is disable the function of the new FL for ALL packages, and any new objects CREATED in the lower * FL will have to be bound or rebound again at rrhe higher FL once you go back to it.



So, again from what I understand, it's like a subsystem-wide APPLCOMPAT (and any objects created in this mode will have to be bound/rebound with the higher FL, as I said above).



Wrong?



On Thu, 11 Oct 2018 at 07:23, Boxwell, Roy <[login to unmask email] <mailto:[login to unmask email]> > wrote:

What they mean is that if you have bound 10,000 Packages at , say, FL503 and then see that some SQLs are going wrong for whatever reason. You can then fall back the system to FL502 and REBIND the static packages that are causing trouble leaving the rest, if they are *not* causing grief. You do not want 10,000 AUTOBINDs do you? Dynamic SQL is a bit trickier as you have to bind the Dynamic SQL Access packages (SYSLHxx etc etc) also at the lower FL level to stop any “rogue” SQLs running at the bad FL level.



APPLCOMPAT is really a package level attribute to peg packages at, say, FL501 even though you are happily running at FL503 – This gives you time to fix things like ICIs etc.



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Aurora Emanuela Dellanno [mailto:[login to unmask email] <mailto:[login to unmask email]> ]
Sent: Wednesday, October 10, 2018 3:26 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 12 star * function levels



so, I read in the What's New book that "Activating a lower star (*) function level by itself does not immediately disable all use of capabilities at higher function levels. Instead, it provides flexibility for limiting or disabling the use of capabilities at higher function levels, while the problems encountered in higher function levels are resolved."



can someone please explain this to me in plain English? is it to do basically with the default APPLCOMPAT?



This is confusing me even further: "applications that use capabilities at a higher function level can continue to do so if they are not related to the reason for activating the lower function level" - how does Db2 KNOW what the reason for a function level is? do you need to have a sit-down and friendly talk with it?



TIA for anything that gets me out of this maze...



Aurora



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



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

Attachments

  • smime.p7s (5.1k)

Michael Hannan

RE: Db2 12 star * function levels
(in response to Roy Boxwell)

In Reply to Roy Boxwell:

Yep – You can definitely look at it that way! Just remember that the BIND/REBINDS going in both directions are optional...(The hope is that you will never ever actually have to fall back of course...)

REBIND with APPLCOMPAT higher level seems to be needed for variable dynamic SQL Packages in order to make new function useable. REBIND of Static SQL Packages with a new APPLCOMPAT value does not seem to be required since the Package is already not using new functionality. Use of new function would require a new BIND and then would default to subsystem compat level. Can we assume that Static Packages will only need REBIND with a new APPLCOMPAT  value, when subsystem gets to a function level where we find old APPLCOMPAT value is not longer supported, e.g. APPLCOMPAT(V10R1) might no longer be supported at some point?

The manuals don't seem to give a lot of guidance on this topic. They state a static SQL REBIND with APPLCOMPAT change is possible, but not really why you would need to do it.

Terry Purcell assures me that Optimizer enhancements are not related to APPLCOMPAT, which is what would expect since they don't require New Function mode.

I fear the APPLCOMPAT thing is there, and therefore open to over usage, due to lack of full understanding. Certainly seems to be to avoid incompatibility problems between various software. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Daniel Luksetich

Db2 12 star * function levels
(in response to Michael Hannan)
The IDUG content committee will be devoting the month of November to application compatibility and continuous delivery. These will be must read articles! I myself am putting together a piece on Java type 4 connectivity for JDBC. It will be finished next month so all I can say for now is that if you think you know how dynamic SQL is going to work with application compatibility levels please test it beforehand. You may be surprised at what is actually happening! The way you would test it is to make a BIND COPY of the packages into a separate collection, and then use that collection for your testing. I have only tested JDBC type 4 and I set application compatibility and the packageset in the connection string. Sorry to be so inconclusive but I wanted to get the warning out there while I’m still working on the article.



Chris Crone has a redbook out that does a pretty good job of explaining things (he will be providing an article in November as well).



http://www.redbooks.ibm.com/redpapers/abstracts/redp5469.html?Open



Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Michael Hannan <[login to unmask email]>
Sent: Thursday, October 11, 2018 1:29 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 12 star * function levels



In Reply to Roy Boxwell:

Yep – You can definitely look at it that way! Just remember that the BIND/REBINDS going in both directions are optional...(The hope is that you will never ever actually have to fall back of course...)

REBIND with APPLCOMPAT higher level seems to be needed for variable dynamic SQL Packages in order to make new function useable. REBIND of Static SQL Packages with a new APPLCOMPAT value does not seem to be required since the Package is already not using new functionality. Use of new function would require a new BIND and then would default to subsystem compat level. Can we assume that Static Packages will only need REBIND with a new APPLCOMPAT value, when subsystem gets to a function level where we find old APPLCOMPAT value is not longer supported, e.g. APPLCOMPAT(V10R1) might no longer be supported at some point?

The manuals don't seem to give a lot of guidance on this topic. They state a static SQL REBIND with APPLCOMPAT change is possible, but not really why you would need to do it.

Terry Purcell assures me that Optimizer enhancements are not related to APPLCOMPAT, which is what would expect since they don't require New Function mode.

I fear the APPLCOMPAT thing is there, and therefore open to over usage, due to lack of full understanding. Certainly seems to be to avoid incompatibility problems between various software.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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

Peter Vanroose

Re: Db2 12 star * function levels
(in response to Michael Hannan)

In Reply to Michael Hannan:

[...] REBIND of Static SQL Packages with a new APPLCOMPAT value does not seem to be required since the Package is already not using new functionality. [...]

Actually, I can think of a situation where REBINDing a package to a higher APPCOMPAT *would* be useful:

We have had the "issue" of the CHAR() scalar function on decimal input, which started returning a different format since Db2 10. *Suppose* Db2 would (at some app compat level) decide to make CHAR() switch back to the old format by default (and introduce a CHAR10() function for the version 10 format ;-)
Then a REBIND of a particular package to that (future) app. compat. level would fix the "problem" for that package (instead of modifying its SQL to replace CHAR() by CHAR9()).

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

carol goldberg

Db2 12 star * function levels
(in response to Peter Vanroose)
Interesting thought!!!

On Fri, Oct 12, 2018, 02:57 Peter Vanroose <[login to unmask email]> wrote:

> In Reply to Michael Hannan:
>
> [...] REBIND of Static SQL Packages with a new APPLCOMPAT value does not
> seem to be required since the Package is already not using new
> functionality. [...]
>
> Actually, I can think of a situation where REBINDing a package to a higher
> APPCOMPAT *would* be useful:
>
> We have had the "issue" of the CHAR() scalar function on decimal input,
> which started returning a different format since Db2 10. **Suppose** Db2
> would (at some app compat level) decide to make CHAR() switch back to the
> old format by default (and introduce a CHAR10() function for the version 10
> format ;-)
> Then a REBIND of a particular package to that (future) app. compat. level
> would fix the "problem" for that package (instead of modifying its SQL to
> replace CHAR() by CHAR9()).
>
> -- Peter Vanroose
> *ABIS Training & Consulting,*
> * Leuven, Belgium.*
> https://www.abis.be/ https://www.abis.be/html/enDB2Calendar.html
>
> -----End Original Message-----
>

Michael Hannan

Re: Db2 12 star * function levels
(in response to Peter Vanroose)

In Reply to Peter Vanroose:

Actually, I can think of a situation where REBINDing a package to a higher APPCOMPAT *would* be useful:

We have had the "issue" of the CHAR() scalar function on decimal input, which started returning a different format since Db2 10. *Suppose* Db2 would (at some app compat level) decide to make CHAR() switch back to the old format by default (and introduce a CHAR10() function for the version 10 format ;-)
Then a REBIND of a particular package to that (future) app. compat. level would fix the "problem" for that package (instead of modifying its SQL to replace CHAR() by CHAR9()).

Ha Ha. Funny yes, but it isn't realistically  going to happen that IBM will back-out a functionality change like that, even if non compatible change was a tough decision. It surprised me that CHAR9 function was not delivered immediately in V10 though.

I hoped someone will give more practical examples of how APPLCOMPAT was actually needed to solve problems, on a REBIND. I have seen bulk REBINDs with APPLCOMPAT upward change and it did not make any logical sense to me. So I have to question why. Seemed like "to be sure, to be sure" but sure of what?

Makes most sense when backing out a previous down level REBIND of the APPLCOMPAT, which was hopefully only done with good reason (but not certain). However that is for dynamic SQL Packages.

I also found it a little annoying that some of the System Dynamic SQL packages SYSaannn had been bound with APPLCOMPAT V11 and then when I wanted to use V12 LISTAGG function in my Data Studio SQL, it failed, even though subsystem was upgraded sufficiently to use it. So REBIND the APPLCOMPAT back to a higher level, to allow it,  might have been forgotten or there was some incompatibility at some point between Client and Server software. Not quite clear.

Obviously when a site has long gone to the latest Db2 NFM, for using dynamic SQL, you want to be able to try it's new function capabilities without having them all eagerly APPLCOMPAT disabled. LOL Frustrating.

So when it comes to Static SQL Packages, can't see much point to REBIND to a higher APPLCOMPAT level, unless current level becomes unsupported. Perhaps if Rebinding to get new access paths, APPLCOMPAT level could be changed up at the same time (just to avoid ever getting too old), but not sure this is worthwhile.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Oct 14, 2018 - 08:24 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 08:26 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 08:30 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Oct 14, 2018 - 08:43 AM (Europe/Berlin)

James Campbell

Db2 12 star * function levels
(in response to Michael Hannan)
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/apsg/src/tpc/db2z_applco
mpatclients.html

describes one situation where rebinding at a higher applcompat would be desirable. (for lazy
people - dynamic sql is restricted by the applcompat level of the package executing the sql.)

James Campbell


On 13 Oct 2018 at 23:07, Michael Hannan wrote:

>
> In Reply to Peter Vanroose:
> Actually, I can think of a situation where REBINDing a package to a higher APPCOMPAT
> *would* be useful:
>
> We have had the "issue" of the CHAR() scalar function on decimal input, which started
> returning a different format since Db2 10. *Suppose * Db2 would (at some app compat
> level) decide to make CHAR() switch back to the old format by default (and introduce a
> CHAR10() function for the version 10 format ;-)
> Then a REBIND of a particular package to that (future) app. compat. level would fix the
> "problem" for that package (instead of modifying its SQL to replace CHAR() by CHAR9()).
> Ha Ha. Funny yes, but it isn't realistically  going to happen that IBM will back-out a functionality
> change like that, even if non compatible change was a tough decision. It surprised me that
> CHAR9 function was not delivered immediately in V10 though.
> I hoped someone will give more practical examples of how APPLCOMPAT was actually needed to
> solve problems, on a REBIND. I have seen bulk REBINDs with APPLCOMPAT upward change
> and it did not make any logical sense to me. So I have to question why. Seemed like "to be sure,
> to be sure" but sure of what?
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>


---
This email has been checked for viruses by AVG.
https://www.avg.com