DB2 - L

 View Only

  • 1.  Comments in procedure body

    Posted 14 days ago
    Edited by Shay Miller 14 days ago

    Hello Everyone.

    We are in the process of implementing stored procedures with dynamic result sets on a wide scale.

    We treat them like we treat cobol programs - they are to be maintained and written by the apllication teams.

    The life cycle of the defintions is:

    1. The programmer creates the procedure in a special schema in the test system.
    2. The DA/DBA teams generates the definition from the catalog and create the procedure in QA/PROD/training systems.

    One of our programmers said that the comments he wrote in his procedure (legitimate action) disappear in higher environments.

    I've checked and indeed the comments are stripped and not stored in the catalog (TEXT field in SYSIBM.SYSROUTINES).

    I think that his request  to save the comments is logical and reasonable.

    Is there a way to not strip the comments and have them saved in the catalog?

    We are on DB2 12 LVL 100 on z (not LUW).

    Thank you,

    Shay.



    ------------------------------
    ShayMillerMataf
    ------------------------------



  • 2.  RE: Comments in procedure body

    Posted 14 days ago

    "--" comments are stripped by the client, Db2 never even sees them, so they cannot end up in the catalog.

    If you embed comments in /*   comment here  */   they will appear in the catalog.



    ------------------------------
    Peter Vanroose
    ABIS Training & Consulting
    https://www.abis.be/html/enDB2Calendar.html
    ------------------------------

    Original Message


  • 3.  RE: Comments in procedure body

    Posted 14 days ago
    If you are using DSNTEP2/4 with SQLFORMAT = SQL, are filtered out.  You need to use either SQLPL or SQLCOMNT - either via a PARM or --#SET. 
    DSNTIAD passes through to Db2.  The same for DSNTIAUL - but who would use that to create a stored procedure?  This is usefull commenting other pieces of SQL that's are run using them - when -- comments are rejected.
    Or, you could have localised version of any of these to do whatever you want - their source code is in SDSNSAMP.
    James Campbell
    On 20 Nov 2023 at 6:45, Peter Vanroose via Internatio wrote:
    > "--" comments are stripped by the client, Db2 never even sees them, so they cannot end up in the catalog.
    >
    >
    > If you embed comments in    they will appear in the catalog.
    >
    >
    > ------------------------------
    > Peter Vanroose
    > ABIS Training & Consulting
    > ------------------------------
    > -------------------------------------------
    > Original Message:
    > Sent: Nov 19, 2023 07:35 AM
    > From: Shay Miller
    > Subject: Comments in procedure body
    >
    >
    > Hello Everyone.
    >
    > We are in the process of implementing stored procedures with dynamic result sets on a wide scale.
    >
    > We treat them like we treat cobol programs - they are to be maintained and written by the apllication teams.
    >
    > The life cycle of the defintions is:
    >
    > The programmer creates the procedure in a special schema in the test system.
    > The DA/DBA teams generates the definition from the catalog and create the procedure in QA/PROD/training systems.
    >
    >
    > One of our programmers said that the comments he wrote in his procedure (legitimate action) disappear in higher environments.
    >
    > I've checked and indeed the comments are stripped and not stored in the catalog (TEXT field in SYSIBM.SYSROUTINES).
    >
    > I think that his request  to save the comments is logical and reasonable.
    >
    >
    >
    > Is there a way to not strip the comments and have them saved in the catalog?
    >
    > We are on DB2 12 LVL 100 on z (not LUW).
    >
    >
    >
    > Thank you,
    >
    > Shay.
    >
    >
    >
    > ------------------------------
    > ShayMillerMataf
    > ------------------------------
    >
    >



    Original Message


  • 4.  RE: Comments in procedure body

    Posted 14 days ago

    I've just looked at this thread in https://idug.com .  It isn't only DSNTEP2/4 that filters out slash-asterisk .... asterisk-slash comments - the web interface also thinks they are comments and filters them from the display.  Making the parts of the responses non-sensical.



    ------------------------------
    James Campbell
    ------------------------------

    Original Message


  • 5.  RE: Comments in procedure body

    Posted 14 days ago

    Indeed -- my slash star comment in the reply from James got even commented out ;-)

    So, the first question for the O.P. is: what SQL tool are your developers using, and with what settings?



    ------------------------------
    Peter Vanroose
    ABIS Training & Consulting
    https://www.abis.be/html/enDB2Calendar.html
    ------------------------------

    Original Message


  • 6.  RE: Comments in procedure body

    Posted 13 days ago

    If I remember correctly, the embedded comments are depending on a parameter controlled by SQL FORMAT.

    Steen



    ------------------------------
    Steen Rasmussen, Broadcom Mainframe Division
    ------------------------------

    Original Message


  • 7.  RE: Comments in procedure body

    Posted 13 days ago
    Edited by Shay Miller 13 days ago

    I used SQLFORMAL SQLPL and now the comments are kept.

    Thank you all for your input and help.

    Shay



    ------------------------------
    ShayMillerMataf
    ------------------------------

    Original Message


  • 8.  RE: Comments in procedure body

    Posted 12 days ago

    We do the same thing

    Our process for maintaining db2 native stored procedures uses DSNTEP2 with SQLPL.  It will not touch the input SQL and pass it on Db2 with all the pretty spacing and comments (dash-dash AND /* .. */)

    I always thought the default behaviour of DSNTEP2 is to remove comments and extra blanks in order to make the SQL shorter before giving it to DB2.  I think that back in the day... the max length of a SQL was 32K bytes and DSNTEP2 was trying to keep your SQL short so it fit in the limit and it would be processed.  Sure... it would not look pretty in the DSC or the catalog... but at least the SQL might fit and execute. 

    Of course, today, the limit is now 2M.

    cheers

    Brian



    ------------------------------
    Brian Laube Manulife Financial

    Db2 Z DBA (mostly)
    ------------------------------

    Original Message


  • 9.  RE: Comments in procedure body

    Posted 12 days ago

    Maybe time to start looking at some non-3270 tools?

    Start with the IBM Db2 for z/OS Developer Extension of VS Code.    Create your stored procedures in files ending .plsql (makes them compatible with the older Data Studio).   The tool gives you lots of benefits, like syntax highlighting and full debugging.

    Save you code in source control (preferably git).

    Deploy using scripts - we use Linux shell scripts calling clpplus and executed from Jenkins.   The clpplus command line has many benefits over the older clp command line, including being able to deploy SPs without having to make any changes (such as adding an alternative terminator to a semicolon.

    These will probably be familiar tools to many of your developers.

    We go way beyond this in our pipelines, mixing in DDL and ZOWE CLI commands as well.   Our database developers have a much better and productive experience overall.



    ------------------------------
    PhilipNelsonLloyds Banking Group/ScotDB Limited
    ------------------------------

    Original Message