DB2 - L

 View Only
  • 1.  DSNTEP4 controlling package sets

    Posted Mar 31, 2022 08:03 PM
    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.

    BIND PACKAGE (M503TEP2) MEMBER(DSN@EP2L) APPLCOMPAT(V12R1M503) + 
       CURRENTDATA(NO) ACT(REP) ISO(CS) ENCODING(EBCDIC) 
    BIND PACKAGE (M508TEP2) MEMBER(DSN@EP2L) APPLCOMPAT(V12R1M508) + 
       CURRENTDATA(NO) ACT(REP) ISO(CS) ENCODING(EBCDIC) 
    BIND PLAN(DSNTEP2N) PKLIST(*.M503TEP2.*,*.M508TEP2.*) + 
       ACTION(REPLACE) RETAIN + 
       CURRENTDATA(NO) ISO(CS) ENCODING(EBCDIC) SQLRULES(DB2) 
    Now to call DSNTEP2 I can do so to create deprecated objects
    RUN PROGRAM(DSNTEP2) PARMS('/PKGSET(M503TEP2)')
    or allow for further SQL functionality
    RUN PROGRAM(DSNTEP2) PARMS('/PKGSET(M508TEP2)')

    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:

    BIND PACKAGE (M503TEP4) MEMBER(DSN@EP4L) APPLCOMPAT(V12R1M503) CURRENTDATA(NO) ACT(REP) ISO(CS) ENCODING(EBCDIC)
    BIND PACKAGE (M508TEP4) MEMBER(DSN@EP4L) APPLCOMPAT(V12R1M508) CURRENTDATA(NO) ACT(REP) ISO(CS) ENCODING(EBCDIC)

    BIND PLAN(DSNTEP4N) PKLIST(*.M503TEP4.*,*.M508TEP4.*) ACTION(REPLACE) RETAIN CURRENTDATA(NO) ISO(CS) ENCODING(EBCDIC) SQLRULES(DB2)

    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
    ------------------------------


  • 2.  RE: DSNTEP4 controlling package sets

    Posted Mar 31, 2022 10:11 PM
    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


  • 3.  RE: DSNTEP4 controlling package sets

    Posted Apr 01, 2022 02:17 AM
    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




  • 4.  RE: DSNTEP4 controlling package sets

    Posted Apr 01, 2022 04:18 PM
    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
    ------------------------------



  • 5.  RE: DSNTEP4 controlling package sets

    Posted Apr 01, 2022 06:18 PM
    And we ALL love bacon!!!!

    Roy Boxwell 


    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-
     


    IBM Champion 2022


    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