A fix is available
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
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