• No replies
2 Posts

Pinned topic IFCID patterns for SQL statements: 59-66 then 58; 58 only; or 53 only?

‏2013-10-08T06:26:18Z | ifcid trace

For each type of SQL statement, which of the following IFCID patterns applies?

  1. 0059 - 0066 ("start" record) followed by an 0058 ("end")
  2. 0058 only (no preceding "start" 0059 - 0066)
  3. 0053 only

A comprehensive A-Z list of SQL statements with (1), (2), or (3) next to each item would be nice ;-), but a concise set of rules might be even better.

Some background to this question...

The DB2-supplied "IFCID field descriptions" member SDSNIVPD(DSNWMSGS) states that IFCID 0058 records the end of an SQL statement whose start was recorded by an IFCID 0059 - 0066. (Perhaps I am being oversensitive about copyright, but I am deliberately paraphrasing DSNWMSGS here, not directly quoting an extract.)

In practice, however, I have found that an IFCID 0058 record is not always preceded by a matching IFCID 0059 - 0066 record. For example, IFCID 0058 records for SQL ASSOCIATE LOCATORS and SQL ALLOCATE CURSOR statements do not appear to be preceded by "start" IFCID 0059 - 0066 records. Furthermore, based on the IFCID descriptions in DSNWMSGS, I cannot see which of the IFCIDs 0059 - 0066 would record the start of an SQL ASSOCIATE LOCATORS or an SQL ALLOCATE CURSOR statement.

Then we have IFCID 0053, which records... well, see the description in DSNWMSGS. I have seen IFCID 0053 records for SQL CALL and SQL SET statements, which aren't included in the types of statement mentioned by DSNWMSGS for IFCID 0053. Furthermore, there are no matching values of the QW0053TOS field (introduced by the DB2 for z/OS V10 PTF for APAR PM62481) for SQL CALL and SQL SET statements. In the IFCID 0053 records for CALL and SET, the QW0053TOS field  is null (X'00').

(In case you're wondering: I determined that these IFCID 0053 records were for CALL and SET by correlating the statement number field QW0053SN with the corresponding line in the DB2 SQL precompiler listing.)

It irks me that the mapping macro for QW0053TOS refers to the same list of values as QW0058TOS, and that this list of values does not include some statement types that are recorded by IFCID 0053 (such as CALL and SET). Separate lists of values for QW0053TOS and QW0058TOS would help answer my question.

When I see an IFCID 0058 record, I would like to know whether or not there ought to be a preceding 0059 - 0066.

When I see an IFCID 0053 record, I would like to know the statement type by looking at the QW0053TOS field (rather than having to refer to the precompiler listing).

Added 9 October:

I've just looked at some new DB2 trace records that were cut after installing the PTF that introduced the TOS field to IFCIDs 0053 and 0058. The SQL ASSOCIATE LOCATORS and SQL ALLOCATE CURSOR statements that were previously represented by a DTR 0058 record with no preceding 0059 - 0066 are now represented by a DTR 0053 record! So, I can no longer point to any examples of DTR 0058 records without a preceding 0059 - 0066.

The TOS field values for those DTR 0053 records: X'00'.

Added 11 October:

I've seen another IFCID pattern, this time for an SQL FETCH statement: 0059 followed by an 0058 (as expected so far), and then (to my mind, unexpectedly), an 0053. (The 0059, 0058, and 0053 all have the same statement number.) I suspect the 0053 appears because of the nonzero SQLCODE from that FETCH. But, unless I'm misreading something (entirely possible!), this IFCID pattern does not appear to be consistent with the descriptions in DSNWMSGS. The QW0053TOS field value for this FETCH statement is X'00' (I expected X'01', QW005801).

I've seen APAR PM75595 ("The type of SQL request is not being set in IFCID 58 for DROP TABLESPACE statements."). One APAR per SQL statement type with a null QW0053TOS or QW0058TOS field, then? Really? The list of examples I've seen (as described here) is growing... and why no QW0058TOS equate for a plain old SELECT (only X'11' for, specifically, SELECT INTO)? Surely I'm missing something.

Updated on 2013-10-11T04:10:05Z at 2013-10-11T04:10:05Z by Graham_Hannington