IBM Support

PI44721: SAMPLE SQL PL PROCEDURES FOR TRACING

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as new function.

Error description

  • Sample SQL PL procedures for tracing.
    (DCR DN1854)
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All Db2 11 for z/OS and Db2 12 for z/OS      *
    *                 customers who develop or maintain native     *
    *                 SQL routines.                                *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR provides a set of sample      *
    *                      native SQL procedures that can be used  *
    *                      to debug native SQL routines using      *
    *                      tracing type code.                      *
    ****************************************************************
    * RECOMMENDATION: Apply corrective PTF when available.         *
    ****************************************************************
    This APAR provides a set of sample SQL procedures for putting
    messages (lines of text) in a message buffer and getting
    messages from the message buffer. These procedures are useful
    during application debugging.
    

Problem conclusion

Temporary fix

Comments

  • This APAR provides the following set of sample native SQL
    procedures that can be used to debug native SQL routines using
    tracing type code:
    - DSN8.CREATE_DGTT: Creates the DGTT for the session
    - DSN8.DISABLE    : Disables the message buffer
    - DSN8.ENABLE     : Enables the message buffer
    - DSN8.GET_LINE   : Gets a line from the message buffer
    - DSN8.GET_LINES  : Gets multiple lines from the message buffer
    - DSN8.NEW_LINE   : Puts an end-of-line character sequence in
                        the message buffer
    - DSN8.PUT        : Puts a string in the message buffer
    - DSN8.PUT_LINE   : Puts a complete line, including the end
                        of line character, in the message buffer
    
    The DISABLE and ENABLE procedures are not supported inside
    autonomous procedures.  An autonomous procedure is a
    procedure that, when called, executes inside a new transaction
    independent of the original transaction.
    
    In addition to the sample procedures, this APAR also introduces
    the following supporting sample items:
    - DSN8.DBMS_ENABLE global variable: This global variable is
      used to test if trace is enabled.
    - DSN8.GRPHICARR user-defined array data type. It is used for
      the first parameter to the DSN8.GET_LINES procedure.
    
    This APAR also provides a sample job, DSNTEJTR, that can be
    used to create the above SQL objects.  This job grants the
    EXECUTE privilege on each of these procedures to PUBLIC; it
    grants the READ privilege DSN8.DBMS_ENABLE global variable
    to PUBLIC; and it grants the USAGE privilege on the
    DSN8.GRPHICARR user-defined array data type to PUBLIC.
    
    The new functions can be installed and used beginning with Db2
    for z/OS Version 11 in New Function Mode.  If you plan to call
    these functions from a user-defined function invoked in
    a subselect then you must also apply the fixing PTF for APAR
    PI93887 and restart Db2.  In the Db2 data sharing, you must
    apply this action to all members that will be used to call
    the trace functions from a user-defined function invoked in
    a subselect.
    
    
    Example of using PUT_LINE and GET_LINE:
    --------------------------------------
    -- put 'foo' in the output buffer
    create procedure f ()
      begin
       CALL DSN8.ENABLE(NULL);
       CALL DSN8.PUT_LINE( 'foo' );
      end#
    
    -- read a line from the output buffer or the return status
    create function g ()
      returns varchar(100)
      begin
       declare r varchar(100);
       declare status integer;
       CALL DSN8.GET_LINE( r, status );
       if (status = 0) then
         return r;
       else
         return 'status is '||varchar(status);
       end if;
      end#
    
    call f ()#
    create variable gvvar varchar(100)#
    -- values(g ())#
    set gvvar = g()#
    select gvvar from sysibm.sysdummy1#
    -- gets 'foo'
    
    GVVAR
    -----------------------------------------
    foo
    
      1 record(s) selected.
    
    
    Detailed information about each function and the supporting
    objects follows:
    
    ---------------------------------------------------------------
    CREATE_DGTT procedure
    ---------------------------------------------------------------
    Creates a Declared Global Temporary Table called
    SESSION.DBMS_BUFFER
    
    Syntax:
    
    >>-DSN8.CREATE_DGTT-------------------------------------><
    
    Example: The following example creates the SESSION.DBMS_BUFFER
    DGTT:
    
    CALL DSN8.CREATE_DGTT;
    
    
    ---------------------------------------------------------------
    DISABLE procedure
    ---------------------------------------------------------------
    The DISABLE procedure disables the message buffer.
    
    After this procedure runs, any messages that are in the message
    buffer are discarded. Calls to the PUT, PUT_LINE, or NEW_LINE
    procedures are ignored, and no error is returned to the sender.
    
    Syntax:
    
    >>-DSN8.DISABLE-----------------------------------------><
    
    Example: The following example disables the message buffer for
    the current session:
    
    CALL DSN8.DISABLE;
    
    Usage notes: To send and receive messages after the message
    buffer has been disabled, use the ENABLE procedure.
    
    
    ---------------------------------------------------------------
    ENABLE procedure
    ---------------------------------------------------------------
    The ENABLE procedure enables the message buffer. During a
    single session, applications can put messages in the message
    buffer and get messages from the message buffer.
    
    Syntax:
    
    >>DSN8.ENABLE--(--buffer-size--)-----------------------><
    
    buffer-size:
    
      An input argument of type INTEGER that is currently ignored.
      The number of message lines is restricted only by the amount
      of temporary work file space available.
    
    Example: The following example enables the message buffer:
    
    CALL DSN8.ENABLE( CAST(NULL AS INTEGER) );
    
    
    ---------------------------------------------------------------
    GET_LINE procedure
    ---------------------------------------------------------------
    The GET_LINE procedure returns a single line from the message
    buffer, up to an end-of-line character which is not copied to
    the output buffer.
    
    Tip: To add an end-of-line character sequence to the message
    buffer, use the PUT_LINE procedure, or, after a series of
    calls to the PUT procedure, use the NEW_LINE procedure.
    
    Syntax:
    
    >>-DSN8.GET_LINE--(--line--,--status--)---------------><
    
    line
      An output argument of type VARGRAPHIC(16334) that
      returns a line from the message buffer.
    
    The CCSID of the result is 1200.
    
    status
      An output argument of type INTEGER that indicates whether
      a line was returned from the message buffer:
      - 0 indicates that a line was returned
      - 1 indicates that there was no line to return
    
    
    ---------------------------------------------------------------
    GET_LINES procedure
    ---------------------------------------------------------------
    The GET_LINES procedure gets one or more lines of text from
    the message buffer and stores the text in an array. Each line
    of text in the message buffer, up to an end-of-line character,
    is stored in an element of the target array. The end-of-line
    characters are not stored in the target array elements.
    
    Syntax:
    
    >>-DSN8.GET_LINES--(--lines--,--numlines--)-------------><
    
    lines
      An output argument of type DSN8.GRPHICARR that returns
      the lines of text from the message buffer. The type
      DSN8.GRPHICARR is an array data type that is defined as
      VARGRAPHIC(16334) ARRAY 2147483647 .
    
    The CCSID of the result is 1200.
    
    numlines
      An input and output argument of type INTEGER. When used as
      input, numlines specifies the number of lines to retrieve
      from the message buffer. When used as output, indicates
      the actual number of lines that were retrieved from the
      message buffer. If the output value of numlines is less
      than the input value, then there are no more lines
      remaining in the message buffer.
    
      If numlines is 0 or null, then null is returned for lines
      and 0 is returned for numlines.
    
    
    ---------------------------------------------------------------
    NEW_LINE procedure
    ---------------------------------------------------------------
    The NEW_LINE procedure writes an end-of-line character sequence
    to the message buffer.
    
    Syntax
    
    >>-DSN8.NEW_LINE----------------------------------------><
    
    
    ---------------------------------------------------------------
    PUT procedure
    ---------------------------------------------------------------
    The PUT procedure writes a line to the message buffer. An
    end-of-line character sequence is not written at the end of
    the line in the message buffer.
    
    Syntax:
    
    >>-DSN8.PUT--(--item--)---------------------------------><
    
    item
      An input argument of type VARGRAPHIC(16334) that specifies
      the line to write to the message buffer. If item is an
      empty string or null, the message buffer is unmodified.
    
    
    ---------------------------------------------------------------
    PUT_LINE procedure
    ---------------------------------------------------------------
    The PUT_LINE procedure writes a line, including an end-of-line
    character sequence, to the message buffer.
    
    Syntax:
    
    >>-DSN8.PUT_LINE--(--item--)---------------------------><
    
    
    item
      An input argument of type VARGRAPHIC(16334) that specifies
      the line to write to the message buffer. If item is an empty
      string or null, the message buffer is unmodified.
    
    Notes: The PUT_LINE procedure adds the end-of-line character
    to the end of the input string that is written to the message
    buffer. If you use the DSN8.PUT procedure instead, an
    end-of-line character is not added, and the NEW_LINE procedure
    can be used to add the end-of-line character to the end of
    the message buffer.
    
    
    ---------------------------------------------------------------
    Global variable DSN8.DBMS_ENABLE
    ---------------------------------------------------------------
    This global variable is used to test if trace is enabled.
    
    Example using SQL PL:
      IF DSN8.DBMS_ENABLE = 'Y' THEN
        CALL DSN8.PUT_LINE (line);
      END IF;
    
    
    ---------------------------------------------------------------
    Array Type TYPE DSN8.GRPHICARR
    ---------------------------------------------------------------
    This user-defined data type is used for the first parameter to
    the DSN8.GET_LINES procedure.
    
    The DSN8.GRPHICARR user-defined data type is defined as a
    VARGRAPHIC(16334) ARRAY 2147483647  array.
    
    
    ---------------------------------------------------------------
    Sample job DSNTEJTR
    ---------------------------------------------------------------
    This sample job creates each of the SQL procedures described
    above as well as the supporting global variables and array type.
    It also grants the EXECUTE privilege on each of the procedures
    to PUBLIC; it grants the READ privilege on DSN8.DBMS_ENABLE
    global variable to PUBLIC; and it grants the USAGE privilege
    on the DSN8.GRPHICARR user-defined array data type to PUBLIC.
    - Do not run the job prior to Db2 11 new-function mode
    - Follow the guidance in the job prolog to customize it for
      use at your site.
    - All CREATE statements are embedded directly in the job as
      in-stream data and processed by the DSNTEP2 sample dynamic
      SQL program.  If necessary, configure and run the DSNTEJ1L
      sample job to deploy DSNTEP2.
    - To avoid corruption of any variant EBCDIC characters in the
      CREATE statements, DSNTEJ6R binds and runs DSNTEP2 with a
      temporary package and plan bound EBCDIC CCSID 37.  This
      plan and package collection-ID are both named DSNTEP37
      and are freed by the final job step of DSNTEJ6R.
    ****************************************************************
    

APAR Information

  • APAR number

    PI44721

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2015-07-09

  • Closed date

    2018-10-15

  • Last modified date

    2018-11-01

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UI59129 UI59132

Modules/Macros

  •    DSNTEJTR
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI59132

       UP18/10/31 P F810

  • RC10 PSY UI59129

       UP18/10/31 P F810

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
01 November 2018