SQL Statement Text from Native Stored Procedures
Posted by Clive Petley on 12/05/14 @ 5:47 PM

Depending on how your Native Stored Procedures have been created, it is possible that when tracing SQL issued by Native Stored Procedures, the final reports may not display the correct SQL text/statistics for the statements executed by the Native SP.

The problem occurs because all the SQL statements in the stored procedure have statement number set to 1 in SYSPACKSTMT and so EZ-Tracer cannot distinguish between them and thinks that there is only a single SQL. In this situation, the SQL statistics are correct for the entire stored procedure, but all SQL issued are reported under the text for the first SQL in the procedure.

The method used to generate the stored procedure affects how statement numbers are allocated:

  • If the Native SP is created via the IBM Data Studio/Workbench – the PC development front end, then the stored procedure text preserves the linefeeds/numbers, and STMTNO reflects the line number that the SQL statement occurs on. In this case the EZ-Tracer reports will be correct.
  • If the Native SP is created on the mainframe using SPUFI or batch JCL using DSNTEP2 or DSNTEP4, then by default, the linefeeds in the stored procedure source are NOT preserved and the entire source is considered as a single line – hence STMTNO is set to ‘1’ for all SQL statements in the SP and the EZ-Tracer reports will be incorrect.

You can correct the Native Stored Procedure:

  • You can force DSNTEP2 or DSNTEP4 to preserve the linefeeds and hence generate unique STMTNOs by adding the parm - PARMS('/SQLFORMAT(SQLPL)')
  • Alternatively for SPUFI or DSNTEP2/4 you can also add the following directive at the beginning of the input: --#SET SQLFORMAT SQLPL

To resolve future traces of the stored procedure, the SP must be regenerated using one of the two methods above to force unique statement numbers in the Catalog. EZ-Tracer will then function as normal for these SQL statements.

If it is not possible to regenerate the stored procedure, then you can use EZ-Cache (requires DB2 10 onwards, to trace static cache), which does not rely on SYSPACKSTMT STMTNO's for the reporting.