Announcing Db2 12 function level 504

Paul McWilliams

Announcing Db2 12 function level 504

The documentation for Db2 12 function level 504 is now available in IBM Knowledge Center, and you can also read about function level 504 in two Db2 News from the Lab blog posts:

 


Paul A. McWilliams

Db2 for z/OS Technical Writer IBM Silicon Valley Laboratory

Knowledge CenterDoc PDFs | Doc feedback | Db2 for z/OS News from the Lab

Edited By:
Paul McWilliams[Organization Members] @ Mar 06, 2019 - 05:02 PM (America/Eastern)
Paul McWilliams[Organization Members] @ Mar 06, 2019 - 05:03 PM (America/Eastern)

James Campbell

Announcing Db2 12 function level 504
(in response to Paul McWilliams)
> * "You can prevent the creation of new deprecated objects in Db2 12 function level 504"
> http://ibm.biz/Bd2CwC

and if you do and you have QMF, do consider Q.PROFILES.SPACE.

James Campbell


On 6 Mar 2019 at 14:59, Paul McWilliams wrote:

>
> The documentation for Db2 12 function level 504 is now available in IBM Knowledge Center:
> https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_fl_v12r1m504
> .html
> You can also read about function level 504 in two Db2 News from the Lab blog posts:
> * "Announcing Db2 12 function level 504!" https://ibm.biz/Bd2CwL
> * "You can prevent the creation of new deprecated objects in Db2 12 function level 504"
> http://ibm.biz/Bd2CwC
>  
>
> Paul A. McWilliams
> Db2 for z/OS Technical Writer IBM Silicon Valley Laboratory
> Knowledge Center | Doc PDFs | Doc feedback | Db2 for z/OS News from the Lab



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

Perry Spada

RE: Announcing Db2 12 function level 504
(in response to Paul McWilliams)

So based on the previously referenced article,  "You can prevent the creation of new deprecated objects in Db2 12 function level 504", if the IBM program, DSNTEP2, was bound under function level 504 or later due to maintenance, it would generate an error if it attempted to issue CREATE TABLE statement  in an existing classic segmented or simple table space. Under M504, DSNTEP2 would also generate an error if it an encountered a CREATE TABLESPACE with just the SEGSIZE parm specified (for segmented tablespace) or CREATE TABLESPACE with NUMPARTS and SEGSIZE 0 specified (for classic partitioned tablespace). If you cannot readily convert your table spaces to one of the universal table spaces, then specifying the following DDL statement "SET CURRENT APPLICATION COMPATIBILITY ='V12R1M503' (or less) first would allow you to create the deprecated objects using DSNTEP2 bound under M504 or higher. Am I interpreting this correctly?

Phil Grainger

Announcing Db2 12 function level 504
(in response to Perry Spada)
Yes

(Long question, short answer!)

Sent from my iPhone

On 13 Mar 2019, at 17:49, Perry Spada <[login to unmask email]<mailto:[login to unmask email]>> wrote:


So based on the previously referenced article, "You can prevent the creation of new deprecated objects in Db2 12 function level 504", if the IBM program, DSNTEP2, was bound under function level 504 or later due to maintenance, it would generate an error if it attempted to issue CREATE TABLE statement in an existing classic segmented or simple table space. Under M504, DSNTEP2 would also generate an error if it an encountered a CREATE TABLESPACE with just the SEGSIZE parm specified (for segmented tablespace) or CREATE TABLESPACE with NUMPARTS and SEGSIZE 0 specified (for classic partitioned tablespace). If you cannot readily convert your table spaces to one of the universal table spaces, then specifying the following DDL statement "SET CURRENT APPLICATION COMPATIBILITY ='V12R1M503' (or less) first would allow you to create the deprecated objects using DSNTEP2 bound under M504 or higher. Am I interpreting this correctly?

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

Phil Grainger

Announcing Db2 12 function level 504
(in response to Phil Grainger)
But people should be aware of the increasingly less-than-subtle hints about deprecated objects and plan for their replacement before you are forced to do so

Sent from my iPhone

On 13 Mar 2019, at 17:51, Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Yes

(Long question, short answer!)

Sent from my iPhone

On 13 Mar 2019, at 17:49, Perry Spada <[login to unmask email]<mailto:[login to unmask email]>> wrote:


So based on the previously referenced article, "You can prevent the creation of new deprecated objects in Db2 12 function level 504", if the IBM program, DSNTEP2, was bound under function level 504 or later due to maintenance, it would generate an error if it attempted to issue CREATE TABLE statement in an existing classic segmented or simple table space. Under M504, DSNTEP2 would also generate an error if it an encountered a CREATE TABLESPACE with just the SEGSIZE parm specified (for segmented tablespace) or CREATE TABLESPACE with NUMPARTS and SEGSIZE 0 specified (for classic partitioned tablespace). If you cannot readily convert your table spaces to one of the universal table spaces, then specifying the following DDL statement "SET CURRENT APPLICATION COMPATIBILITY ='V12R1M503' (or less) first would allow you to create the deprecated objects using DSNTEP2 bound under M504 or higher. Am I interpreting this correctly?

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

Perry Spada

RE: Announcing Db2 12 function level 504
(in response to Phil Grainger)

Another question I had regarding the deprecated object feature of Function Level M504 involves the BIND process and the deprecated objects. We know that if you specify the VALIDATE(BIND) parameter, the BIND will fail if  the program is trying to access a missing object or does not have the necessary authorization to access the object. What happens if you perform a BIND with VALIDATE(BIND) parameter under M504 and the program attempts to create a table in a classic segmented or partitioned table space? Will the BIND fail because the program is attempting to issue a CREATE TABLE in a deprecated table space or will it complete successfully? The current DB2 V12 documentation in Knowledge Center for the BIND VALIDATE() parameter does not seem to specifically address this situation like in other areas of DB2 V12 documentation where it has an M504 denoted.

Roy Boxwell

Announcing Db2 12 function level 504
(in response to Perry Spada)
OK, in my test FL504 environment I have a segmented space with one table and when I try and run this DDL



CREATE TABLE BOXWELL.TABLE (COL1 CHAR(1) NOT NULL)

IN MIVPD1.MIVPS11

;

---------+---------+---------+---------+---------+---------+---------+---------+

DSNT408I SQLCODE = -20008, ERROR: ATTEMPT TO USE UNSUPPORTED FEATURE 'CREATE

IN NON-UTS'. REASON CODE: 8

DSNT418I SQLSTATE = 560A9 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNXICTB SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 403 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'00000193' X'00000000' X'00000000' X'FFFFFFFF'

X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION



So now I embedded this code in a little program and attempted to BIND it :



READY

DSN SYSTEM(xxxx)

DSN

BIND PACKAGE(MDB2VNEX_TEST) MEMBER(O2TESTB) OWNER(PROD) QUALIFIER(MVNXTEST) CURRENTDATA(NO) RELEASE(COMMIT) VALIDATE(

BIND) ACTION(REPLACE) EXPLAIN(YES)

DSNT254I -xxxx DSNTBCM2 BIND OPTIONS FOR

PACKAGE = ZD00xxxx.MDB2VNEX_TEST.O2TESTB.()

ACTION ADD

OWNER PROD

QUALIFIER MVNXTEST

VALIDATE BIND

EXPLAIN YES

ISOLATION

RELEASE COMMIT

COPY

APREUSE

APCOMPARE

APRETAINDUP

BUSTIMESENSITIVE YES

SYSTIMESENSITIVE YES

ARCHIVESENSITIVE YES

APPLCOMPAT V12R1M504

DESCSTAT YES

APREUSESOURCE

DSNT255I -xxxx DSNTBCM2 BIND OPTIONS FOR

PACKAGE = ZD00xxxx.MDB2VNEX_TEST.O2TESTB.()

SQLERROR NOPACKAGE

CURRENTDATA NO

DEGREE 1

DYNAMICRULES

DEFER

NOREOPT VARS

KEEPDYNAMIC NO

IMMEDWRITE INHERITFROMPLAN

DBPROTOCOL DRDA

OPTHINT

ENCODING EBCDIC(01141)

PLANMGMT OFF

PLANMGMTSCOPE STATIC

CONCURRENTACCESSRESOLUTION

EXTENDEDINDICATOR

PATH

DSNT275I -xxxx DSNTBCM2 BIND OPTIONS FOR

PACKAGE = ZD00xxxx.MDB2VNEX_TEST.O2TESTB.()

QUERYACCELERATION

GETACCELARCHIVE

ACCELERATOR

CONCENTRATESTMT

DSNT232I -xxxx SUCCESSFUL BIND FOR

PACKAGE = ZD00xxxx.MDB2VNEX_TEST.O2TESTB.()

DSN

END

READY

END



Return code 0 all is well...



When I run this in a normal DB2 12 it creates the object, when I run it in a FL504 I get



SQL error SQLCODE -20008







So the answer is:



VALIDATE(BIND) does *not* validate if the program contains static SQL that violates deprecated object support.



Run time does.



Hope that helps!



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]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Perry Spada [mailto:[login to unmask email]
Sent: Wednesday, June 26, 2019 6:43 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Announcing Db2 12 function level 504



Another question I had regarding the deprecated object feature of Function Level M504 involves the BIND process and the deprecated objects. We know that if you specify the VALIDATE(BIND) parameter, the BIND will fail if the program is trying to access a missing object or does not have the necessary authorization to access the object. What happens if you perform a BIND with VALIDATE(BIND) parameter under M504 and the program attempts to create a table in a classic segmented or partitioned table space? Will the BIND fail because the program is attempting to issue a CREATE TABLE in a deprecated table space or will it complete successfully? The current DB2 V12 documentation in Knowledge Center for the BIND VALIDATE() parameter does not seem to specifically address this situation like in other areas of DB2 V12 documentation where it has an M504 denoted.



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

Attachments

  • smime.p7s (5.1k)

Perry Spada

RE: Announcing Db2 12 function level 504
(in response to Roy Boxwell)

Roy,

 

Thanks for conducting your test. I was wondering if the BIND process would detect a violation of the deprecated object rules under M504 which would be better than finding out at run time. However, your testing concludes that it does not.

 

Best Regards,

Perry

Roy Boxwell

Announcing Db2 12 function level 504
(in response to Perry Spada)
Indeed! I guess that at bind time db2 cannot know if that static sql is ever going to be even executed so it just ignores it knowing that the run time will catch it.

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung

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

> On 27 Jun 2019, at 01:00, Perry Spada <[login to unmask email]> wrote:
>
> Roy,
>
>
>
> Thanks for conducting your test. I was wondering if the BIND process would detect a violation of the deprecated object rules under M504 which would be better than finding out at run time. However, your testing concludes that it does not.
>
>
>
> Best Regards,
>
> Perry
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
Attachments

  • smime.p7s (3.9k)

Ruediger Kurtz

AW: Announcing Db2 12 function level 504
(in response to Roy Boxwell)
Roy,

not even a warning being issued?

Regards

Ruediger


Rüdiger Kurtz
Abteilung Informatik - Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44148
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas.
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Donnerstag, 27. Juni 2019 07:10
An: [login to unmask email]
Betreff: [DB2-L] - RE: Announcing Db2 12 function level 504

Indeed! I guess that at bind time db2 cannot know if that static sql is ever going to be even executed so it just ignores it knowing that the run time will catch it.
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de
Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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

On 27 Jun 2019, at 01:00, Perry Spada <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Roy,



Thanks for conducting your test. I was wondering if the BIND process would detect a violation of the deprecated object rules under M504 which would be better than finding out at run time. However, your testing concludes that it does not.



Best Regards,

Perry

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

Roy Boxwell

AW: Announcing Db2 12 function level 504
(in response to Ruediger Kurtz)
Here’s the COBOL code snippet:



DO-SQL SECTION.

*---------------------------------------------------------------*



EXEC SQL

CREATE TABLE BOXWELL.TABLE (COL1 CHAR(1) NOT NULL)

IN MIVPD1.MIVPS11

END-EXEC

IF SQLCODE NOT EQUAL ZERO

MOVE SQLCODE TO SQLCODE-Z

DISPLAY 'SQL error'

' SQLCODE ' SQLCODE-Z

END-IF





And return code 0 with not even a warning with BIND(VALIDATE)

At run time it fails 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]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Kurtz, Rüdiger [mailto:[login to unmask email]
Sent: Thursday, June 27, 2019 7:16 AM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - AW: RE: Announcing Db2 12 function level 504



Roy,

not even a warning being issued?

Regards

Ruediger



Rüdiger Kurtz
Abteilung Informatik - Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg


Telefon:

09561 96-44148


Telefax:

09561 96-44104


E-Mail:

[login to unmask email] <mailto:[login to unmask email]>


Internet:

www.huk.de http://www.huk.de

_____

HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Klaus-Jürgen Heitmann (Sprecher), Stefan Gronbach, Dr. Hans Olav Herøy, Dr. Jörg Rheinländer (stv.), Sarah Rössler, Daniel Thomas.

_____

Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.

_____

Von: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Donnerstag, 27. Juni 2019 07:10
An: [login to unmask email] <mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: Announcing Db2 12 function level 504



Indeed! I guess that at bind time db2 cannot know if that static sql is ever going to be even executed so it just ignores it knowing that the run time will catch it.

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.

-Product Development-

Heinrichstrasse 83-85

40239 Düsseldorf/Germany

Tel. +49 (0)211 96149-675

Fax +49 (0)211 96149-32

Email: [login to unmask email] <mailto:[login to unmask email]>

http://www.seg.de

Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz



Software Engineering GmbH

Amtsgericht Düsseldorf, HRB 37894

Geschäftsführung: Gerhard Schubert, Ulf Heinrich


On 27 Jun 2019, at 01:00, Perry Spada <[login to unmask email] <mailto:[login to unmask email]> > wrote:

Roy,



Thanks for conducting your test. I was wondering if the BIND process would detect a violation of the deprecated object rules under M504 which would be better than finding out at run time. However, your testing concludes that it does not.



Best Regards,

Perry



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



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

Attachments

  • smime.p7s (5.1k)