DB2 - L

I have some old user batch stream which creates deprecated objects and that they are slowly converting but as always, it will take a little while.  This is done either using DSNTEP2 or DSNTEP4.  After reading some of the IBM documentation on the subject: 

DSNTEP2 and DSNTEP4 sample programs.

I found out that it would be a slick solution to have a dual mode TEP2 & TEP4.  I followed the example and re-created the DSNTEP2 plan.  Because I used *.M503TEP2.* as the first entry in the PKLIST, the behaviour of TEP2 will behave the same today as it did yesterday.

Now to call DSNTEP2 I can do so to create deprecated objects
or allow for further SQL functionality

This worked great for DSNTEP2 but for DSNTEP4, when I use PKGSET in the PARMS as I would have expected to work based on the samples and documentation above, but I get "DSNT499I ERROR IN INPUT PARAMETER LIST"

Is there any way for DSNTEP4 to behave the same?

I created a new plan this time:


but when I try to use DSNTEP4 with the PKGSET in the PARMS I get that error.

The error I understand because when I look at the source of DSNTEP2 in SDSNSAMP there is nothing to handle PKGSET.

so my question then becomes, is there a way to toggle between which package is used when executing DSNTEP4 via TSO?

RUN PROGRAM(DSNTEP4) LIB('BDB2.LOAD') PLAN(DSNTEP4N) PARMS('/ALIGN(LHS) SQLTERM(;)') PKGSET(M508TEP4)
END

INVALID KEYWORD, PKGSET(M508TEP4) 

RUN PROGRAM(DSNTEP4) LIB('BDB2.LOAD') PLAN(DSNTEP4N) PARMS('/ALIGN(LHS) SQLTERM(;) PKGSET(M508TEP4)')
END

DSNT499I ERROR IN INPUT PARAMETER LIST 

seems like regardless of what I try, it doesn't seem to behave the same as DSNTEP2.

Any advice would be greatly appreciated.

------------------------------
_________________________
Chris Muncan IDUG NA CPC
------------------------------

1) Are you sure that your DSNTEP4 load module has been recreated after applying
UI79342? If it has I would expect 'PH41968' to appear in the load module and package
version id. Otherwise I'd report this as a bug.

One way of seeing what package you are actually executing is
SELECT GETVARIABLE('SYSIBM.PACKAGE_SCHEMA') || '.' ||
GETVARIABLE('SYSIBM.PACKAGE_NAME') || '.(' ||
GETVARIABLE('SYSIBM.PACKAGE_VERSION') || ')'
FROM SYSIBM.SYSDUMMY;

2) there are other ways of running at a back level APPLCOMPAT

James Campbell


On 1 Apr 2022 at 0:02, Chris Muncan via International DB2 Users wrote:

> I have some old user batch stream which creates deprecated objects and that they are slowly converting but as always, it will take a little while. This is done either using DSNTEP2 or DSNTEP4. After reading some of the IBM documentation on the subject:
>
> DSNTEP2 and DSNTEP4 sample programs

Small typo, I think you meant UI79341 as the PTF. Which introduced the ability to change your collection:

PH41968: PRODUCTIVITY-AID SAMPLE PROGRAMS CURRENT PACKAGESET ENHANCEMENT

DSNTEP2 and DSNTEP4 are sample dynamic SQL programs that can be used to issue dynamic SQL statements. DSNTIAUL is a sample application that is used primarily to unload data from Db2 tables to an externalized format. DSNTIAD is a sample program that can issue any dynamic SQL statements, except for SELECT statements.
This APAR provides a new parameter PKGSET(collection) to the above 4 programs:
PKGSET(collection) specifies that the program implicitly executes a SET CURRENT PACKAGESET statement to assign a value to the CURRENT PACKAGESET special register before processing the dynamic SQL statements in SYSIN. 'collection' specifies the value to be assigned, it can be up to 40 characters. Db2 will use the value as collection ID of the package that will be used to execute SQL statements. For example:
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
PARMS('SQL,PKGSET(COL1)')
RUN PROGRAM(DSNTEP2) PLAN(DSNTEPC1) -
PARMS('/ALIGN(MID) PKGSET(COL2)')
Alternatively, for DSNTEP2 and DSNTEP4 only, you can use the
--#SET PKGSET collection
control statement in your SQL statement stream. For example:
--#SET PKGSET M503TEP2
CREATE TABLESPACE SEGTS1...
CREATE TABLE TB1 IN SEGTS1...
--#SET PKGSET M508TEP2
ALTER TABLESPACE SEGTS1 MOVE TABLE TB1 TO TABLESPACE PBGTS1..



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-



Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
Web http://www.seg.de
Link zur Datenschutzerklärung

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


Original Message:
Sent: 3/31/2022 10:11:00 PM
From: James Campbell
Subject: RE: DSNTEP4 controlling package sets

1) Are you sure that your DSNTEP4 load module has been recreated after applying
UI79342? If it has I would expect 'PH41968' to appear in the load module and package
version id. Otherwise I'd report this as a bug.

One way of seeing what package you are actually executing is
SELECT GETVARIABLE('SYSIBM.PACKAGE_SCHEMA') || '.' ||
GETVARIABLE('SYSIBM.PACKAGE_NAME') || '.(' ||
GETVARIABLE('SYSIBM.PACKAGE_VERSION') || ')'
FROM SYSIBM.SYSDUMMY;

2) there are other ways of running at a back level APPLCOMPAT

James Campbell


On 1 Apr 2022 at 0:02, Chris Muncan via International DB2 Users wrote:

> I have some old user batch stream which creates deprecated objects and that they are slowly converting but as always, it will take a little while. This is done either using DSNTEP2 or DSNTEP4. After reading some of the IBM documentation on the subject:
>
> DSNTEP2 and DSNTEP4 sample programs

100% right Roy.  We don't have that APAR PH41968 applied yet.  I've asked our sysprogs ever so politely to take a look and see if we can have it applied.  Would really save my bacon.

------------------------------
_________________________
Chris Muncan IDUG NA CPC
------------------------------

Original Message:
Sent: Apr 01, 2022 02:16 AM
From: Roy Boxwell
Subject: DSNTEP4 controlling package sets

Small typo, I think you meant UI79341 as the PTF. Which introduced the ability to change your collection:

PH41968: PRODUCTIVITY-AID SAMPLE PROGRAMS CURRENT PACKAGESET ENHANCEMENT

DSNTEP2 and DSNTEP4 are sample dynamic SQL programs that can be used to issue dynamic SQL statements. DSNTIAUL is a sample application that is used primarily to unload data from Db2 tables to an externalized format. DSNTIAD is a sample program that can issue any dynamic SQL statements, except for SELECT statements.
This APAR provides a new parameter PKGSET(collection) to the above 4 programs:
PKGSET(collection) specifies that the program implicitly executes a SET CURRENT PACKAGESET statement to assign a value to the CURRENT PACKAGESET special register before processing the dynamic SQL statements in SYSIN. 'collection' specifies the value to be assigned, it can be up to 40 characters. Db2 will use the value as collection ID of the package that will be used to execute SQL statements. For example:
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
PARMS('SQL,PKGSET(COL1)')
RUN PROGRAM(DSNTEP2) PLAN(DSNTEPC1) -
PARMS('/ALIGN(MID) PKGSET(COL2)')
Alternatively, for DSNTEP2 and DSNTEP4 only, you can use the
--#SET PKGSET collection
control statement in your SQL statement stream. For example:
--#SET PKGSET M503TEP2
CREATE TABLESPACE SEGTS1...
CREATE TABLE TB1 IN SEGTS1...
--#SET PKGSET M508TEP2
ALTER TABLESPACE SEGTS1 MOVE TABLE TB1 TO TABLESPACE PBGTS1..



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-



Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
Web http://www.seg.de
Link zur Datenschutzerklärung

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


Original Message:
Sent: 3/31/2022 10:11:00 PM
From: James Campbell
Subject: RE: DSNTEP4 controlling package sets

1) Are you sure that your DSNTEP4 load module has been recreated after applying
UI79342? If it has I would expect 'PH41968' to appear in the load module and package
version id. Otherwise I'd report this as a bug.

One way of seeing what package you are actually executing is
SELECT GETVARIABLE('SYSIBM.PACKAGE_SCHEMA') || '.' ||
GETVARIABLE('SYSIBM.PACKAGE_NAME') || '.(' ||
GETVARIABLE('SYSIBM.PACKAGE_VERSION') || ')'
FROM SYSIBM.SYSDUMMY;

2) there are other ways of running at a back level APPLCOMPAT

James Campbell


On 1 Apr 2022 at 0:02, Chris Muncan via International DB2 Users wrote:

> I have some old user batch stream which creates deprecated objects and that they are slowly converting but as always, it will take a little while. This is done either using DSNTEP2 or DSNTEP4. After reading some of the IBM documentation on the subject:
>
> DSNTEP2 and DSNTEP4 sample programs

Original Message:
Sent: 4/1/2022 4:18:00 PM
From: Chris Muncan
Subject: RE: DSNTEP4 controlling package sets

100% right Roy.  We don't have that APAR PH41968 applied yet.  I've asked our sysprogs ever so politely to take a look and see if we can have it applied.  Would really save my bacon.

------------------------------
_________________________
Chris Muncan IDUG NA CPC
------------------------------

Original Message:
Sent: Apr 01, 2022 02:16 AM
From: Roy Boxwell
Subject: DSNTEP4 controlling package sets

Small typo, I think you meant UI79341 as the PTF. Which introduced the ability to change your collection:

PH41968: PRODUCTIVITY-AID SAMPLE PROGRAMS CURRENT PACKAGESET ENHANCEMENT

DSNTEP2 and DSNTEP4 are sample dynamic SQL programs that can be used to issue dynamic SQL statements. DSNTIAUL is a sample application that is used primarily to unload data from Db2 tables to an externalized format. DSNTIAD is a sample program that can issue any dynamic SQL statements, except for SELECT statements.
This APAR provides a new parameter PKGSET(collection) to the above 4 programs:
PKGSET(collection) specifies that the program implicitly executes a SET CURRENT PACKAGESET statement to assign a value to the CURRENT PACKAGESET special register before processing the dynamic SQL statements in SYSIN. 'collection' specifies the value to be assigned, it can be up to 40 characters. Db2 will use the value as collection ID of the package that will be used to execute SQL statements. For example:
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
PARMS('SQL,PKGSET(COL1)')
RUN PROGRAM(DSNTEP2) PLAN(DSNTEPC1) -
PARMS('/ALIGN(MID) PKGSET(COL2)')
Alternatively, for DSNTEP2 and DSNTEP4 only, you can use the
--#SET PKGSET collection
control statement in your SQL statement stream. For example:
--#SET PKGSET M503TEP2
CREATE TABLESPACE SEGTS1...
CREATE TABLE TB1 IN SEGTS1...
--#SET PKGSET M508TEP2
ALTER TABLESPACE SEGTS1 MOVE TABLE TB1 TO TABLESPACE PBGTS1..



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-



Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
Web http://www.seg.de
Link zur Datenschutzerklärung

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


Original Message:
Sent: 3/31/2022 10:11:00 PM
From: James Campbell
Subject: RE: DSNTEP4 controlling package sets

1) Are you sure that your DSNTEP4 load module has been recreated after applying
UI79342? If it has I would expect 'PH41968' to appear in the load module and package
version id. Otherwise I'd report this as a bug.

One way of seeing what package you are actually executing is
SELECT GETVARIABLE('SYSIBM.PACKAGE_SCHEMA') || '.' ||
GETVARIABLE('SYSIBM.PACKAGE_NAME') || '.(' ||
GETVARIABLE('SYSIBM.PACKAGE_VERSION') || ')'
FROM SYSIBM.SYSDUMMY;

2) there are other ways of running at a back level APPLCOMPAT

James Campbell


On 1 Apr 2022 at 0:02, Chris Muncan via International DB2 Users wrote:

> I have some old user batch stream which creates deprecated objects and that they are slowly converting but as always, it will take a little while. This is done either using DSNTEP2 or DSNTEP4. After reading some of the IBM documentation on the subject:
>
> DSNTEP2 and DSNTEP4 sample programs