DB2 - L

 View Only
  • 1.  Origin of the name of sample programs DSNTEP2/4?

    Posted Nov 28, 2022 03:11 PM
    Just a question of curiosity – Does anyone know where the names for the DB2 PLI sample programs DSNTEP2 and DSNTEP4 came from?  Is it as simple as (DB2 prefix DSN) + (TEst Program 2/4)?
    Also, if anyone is interested I have a modified copy of DSNTEP2 that implements full PARM/PARMDD processing (up to the PARMDD max of 32760) for all "functional comment" overrides and a new SYSDATA option that writes all SELECT output to a new file with just one record per returned row and a single instance of the column headings (suitable for post-processing into CSV or other formats).  Write to me **privately** please (pjfarley3 at earthlink dot net) and not to this list or to this work email address if you are interested.
    I would contribute the changes to CBTTAPE.ORG but the original DSNTEP2 code is copyrighted by IBM.
    This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and delete the message and any attachments from your system.

  • 2.  RE: Origin of the name of sample programs DSNTEP2/4?

    Posted Dec 02, 2022 10:26 AM
    HI Peter,

    I do not know why DSNTEP2/4 are named the way they are named.  Your suggestion for the origin of the name seems reasonable to me!  I will go with it. 
    > This now leads to the obvious question about what happened to DSNTEP1 and DSNTEP3???
    > my little pet peeve around here is to always clarify that DSNTEP2 (and DSNTIAUL) are sample PROGRAMS from IBM that do something useful so everyone in the world probably uses them.  But they should not be called "utilities".  They are not a UTILITY that comes with DB2.  They are a PROGRAM that do something useful, not UTILITIES.  
    > my other pet peeve is that DSNTEP4 should not be needed.  Sure, it does multi-row fetch which saves CPU for large result sets.  But one should never use DNSTEP2/4 for SQL SELECT that produce large result sets.  What is the point? The normal output is formatted to look like a simple report with headings and page numbers.  One normally looks at the (normal-sysprint) output -> one does not normally "processes" it.  Therefore, one would not look at a huge result set via DSNTEP2/4.  That would not be fun

    With regards to your changed source for DSNTEP2
    I really like the new SYSDATA option.  of a new wider output with one instance of column headings and one row per line.  That is a significant enhancement to DNSTEP2.  Sounds like an interesting batch alternative to other tools (like Data Studio or AQT where I manually ask the tool to save the result in CSV with headers)
    1) Is this SYSDATA option in addition to the regular output that goes to SYSPRINT?
    2) My main question is about the full PARM/PARMDD processing for all "functional comment" overrides. What do you mean by PARM/PARMDD and functional comments?

    All this has made me re-read the IBM documentation for DSNTEP2/4.

    If I read the documentation correctly, the V12 version of the source for DSNTEP2/4 has new (as of v12) parameters inside the provided source from IBM.  We can now specify parameters such as PAGEWIDTH to change page width from 133 up to something much wider.  This PAGEWIDTH, and the other new parameters, control how the output sent to SYSPRINT is formatted and how it looks.  interesting


    Brian Laube Manulife Financial

    Db2 Z DBA (mostly)

  • 3.  RE: Origin of the name of sample programs DSNTEP2/4?

    Posted Dec 02, 2022 01:15 PM

    I CC'ed my original message to the IBM-MAIN mailing list and got this response about the naming convention:

    "IMO, the P in the name DSNTEP2 is because it is written in PL/1.

    There are some other sample programs like DSNTIAD and DSNTIAUL, which are written in ASSEMBLER, that's why IMO the 6th letter depends on the programming language."

    Which makes another kind of sense as well.

    I saw the "new" parameter PKGSET on that document url you posted but in the two DB2 V12 SDSNSAMP libraries I have available to me I do not see that new parameter anywhere in the source of either DSNTEP2 or DSNTEP4.  Perhaps that is a "continuous delivery" upgrade that the sites I have access to have not yet applied.  It will be interesting to see what that parameter does when I see a copy of the code with that change in it.

    The comments in that doc url about changing the PAGEWIDTH, etc., sizing parameters has always been a possibility for a shop to change, so long as they had a PLI compiler of a sufficiently new level with which to recompile it.  That is definitely not a "new" capability.  Changing those parameters also just keeps all of the existing "paging" logic active with the same formatting and output to SYSPRINT as it is currently implemented.

    As for my PARM changes, I deleted the existing PARM analysis code (it wasn't even in a separate procedure, just part of the mainline code) which looked for (at most) three overrides in the PARM and limited PARM analysis to the first 60 bytes in the PARM value.  I added a new  separate PARM procedure that can analyze a PARM with a length of up to 32760 (the max allowable when using the PARMDD JCL parameter rather than PARM) and it allows ALL of the "functional comment" parameters (TOLWARN, SQLTERM, etc. - ALL of them and not just a smaller subset of options) to be specified in the PARM or PARMDD value in the format:

    optionname(optionvalue)[,optionname(optionvalue) . . .]

    The SYSDATA option I added is used INSTEAD of the usual "paged" print output for the data rows from a SELECT.  My goal was to allow me to use DSNTEP2 to retrieve large result sets from a SELECT to a VB output file separate from SYSPRINT in a format that could be easily converted to other formats (CSV, JSON, etc.).  When SYSDATA is active for a given SELECT request then all SELECT output goes to the SYSDATA DD name or to a new DD name that you enter as the value of the SYSDATA parameter, like SYSDATA(MYDDNAME).

    In support of the SYSDATA option I also made the MAXCOLWD parameter a variable instead of constant and I allow the default value of 120 to be changed in a "functional comment" or in the PARM value.  This allows large xCHAR-type or xLOB-type column widths to be limited to a smaller value or expanded to a larger value  in the output file.

    Messages *about* a SELECT process (SQL warnings, SQL errors, etc.) and the listing of the input SQL still goes to SYSPRINT in my version.


    PeterFarleyBroadridge Financial Systems

  • 4.  RE: Origin of the name of sample programs DSNTEP2/4?

    Posted Dec 02, 2022 03:31 PM
    Slight update:  I did find a current DB2 V12 copy of the DSNTEPx routines (I was looking in the wrong SDSNSAMP library for V12), and I do see the code for the new PKGSET parameter listed in that doc url you provided.

    Other than that they are pretty much the same as the V11 versions, except that they did increase the maximum length of the *value* for a "functional parameter" to 50 bytes because PKGSET needs to allow up to a 40 character name to be specified.  The prior limit was 10 bytes per value, which my new PARM analysis routine changed as well to 60 bytes. (72 - length of "--#SET " - shortest length of any parameter name (=5 for "MIXED")).

    My other comments still apply.


    PeterFarleyBroadridge Financial Systems

  • 5.  RE: Origin of the name of sample programs DSNTEP2/4?

    Posted 25 days ago
    Happy New Year to everybody????
    The start of 2023 is not so happy for me????
    I can't start my Db2 for Windows - "DB2 v11.5.8000.317"
    When I issue the db2start command I get the following,
    DB2-0 : The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.
    I look into services and I can see the all Db2 services are disabled.

    I don't know what has happened and how to fix it...

    The only thing which may or may not be important,
    I converted to Win 11 Pro and everything was seemingly OK.
    I didn't like the behavior of the taskbar, so I reverted back to Win 10 Pro.

    Any help is dearly appreciated????

    Best regards,


    --   +--------------------------------+---------------------------------+ | Peter G Backlund               | E-mail:  BacklundDB2@telia.com  | +--------------------------------+---------------------------------+ | Phone:   0046(0)70 - 764 7554  | Skype:   BacklundDB2            | +--------------------------------+---------------------------------+ | May - September                | October - April                 | +------------------------------------------------------------------+ | R??sundav??gen 94                | Calle Alella 55, Apt 2B         | | S-169 57  SOLNA   	         | 03185 Torrevieja                | | Sweden                         | Spain                           | +--------------------------------+---------------------------------+ |          Playing with DB2 since 1981 ... and forever!            | +------------------------------------------------------------------+