SQL Statements › CREATE PROCEDURE › CREATE PROCEDURE Syntax and Description › Example

Example

If you add a tracesOn input parameter or SQL variable to your procedure, you can use it to turn DATACOM DUMP statements on and off, preventing your having to recode the statements if procedure modifications require debugging. For example:

 IF tracesOn = 'TRUE' THEN
   DATACOM DUMP dumpable-expression-list TO PXXSQL;
 END IF;

DATACOM DUMP produces output that goes to the PXXSQL and is printed in the detailed format shown in the following sample. In this context, the dumping of constants, for example VALUES ON EXIT in the sample, can help find the output on your test or development MUFs. We reserve the right, however, to change the format of dumps without notice. For example, a less detailed, summary style dump that produces simpler output could be provided at a later time, a summary dump style that could even, at that time, become the default style.

Following is the DATACOM DUMP statement that produces the sample output that follows:

 DATACOM DUMP 'VALUES ON EXIT:', result, sqlstateLocal,
                                       errorMessage TO PXXSQL;

 000017 S0001 -- DCM DUMP PLAN SYSADM.SUPPLYHOSTVARSFORC STMT 5 LUW 0000000000000001 REQ 305 TASK 1 8/3/2007 17:39:02 --
 000018 S0001   LVL 2 NODE 3684D16C OP=00000000 NXT=3684D1A8  QCB=00000000 VAL=3684D15C NAM=00000000
 000019 S0001   ... . PROCTYPE=0x0000  STEP=-01  OPER=000    FLAGS=0x0,0x0
 000020 S0001   ... . LITERAL (#0021:00000.00015): CHAR (15) NOT NULL
 000021 S0001   DCM . CONTENTS= VALUES ON EXIT:.
 000022 S0001   LVL 2 NODE 3684D1A8 OP=00000000 NXT=3684D266 QCB=3684D068 VAL=3684254F NAM=00000000
 000023 S0001   ... . PROCTYPE=0x0000  STEP=01  OPER=000    FLAGS=0x0,0x0
 000024 S0001   ... . LCLVARREF (#1027:00000.00081): CHAR (80)
 000025 S0001   DCM . NAME: ..RESULT
 000026 S0001   DCM . CONTENTS= CREDIT LIMIT EXCEEDED
 000027 S0001   LVL 2 NODE 3684D266 OP=00000000 NXT=3684D324  QCB=3684D068 VAL=36842722 NAM=00000000
 000028 S0001   ... . PROCTYPE=0x0000  STEP=01  OPER=000    FLAGS=0x0,0x0
 000029 S0001   ... . LCLVARREF (#1025:00000.00006): CHAR (5)
 000030 S0001   DCM . NAME: ..SQLSTATELOCAL
 000031 S0001   DCM . CONTENTS= NULL
 000032 S0001   LVL 2 NODE 3684D324 OP=00000000 NXT=3684D446L QCB=3684D068 VAL=3684244B NAM=00000000
 000033 S0001   ... . PROCTYPE=0x8000  STEP=01  OPER=000    FLAGS=0x0,0x0
 000034 S0001   ... . LCLVARREF (#1026:00000.00081): CHAR (80)
 000035 S0001   DCM . NAME: ..ERRORMESSAGE
 000036 S0001   DCM . CONTENTS= N/A
 000037 S0001 -- END DCM DUMP PLAN SYSADM.SUPPLYHOSTVARSFORC STMT 5 LUW 0000000000000001 REQ 305 TASK 1 8/3/2007 17:39:02 --

See SIMULATE DATACOM PROCEDURE Statement to see how a DATACOM DUMP statement that produced the previously shown sample report appears within a SIMULATE PROCEDURE statement.