Topic
  • 1 reply
  • Latest Post - ‏2013-11-09T12:13:12Z by bsteeg_screen
Graham_Hannington
Graham_Hannington
2 Posts

Pinned topic IFCIDs 59 and 58 for FETCH refer to the package that opens the cursor: bug or by design?

‏2013-10-28T07:27:29Z |

In IFCIDs 59 and 58 for SQL FETCH statements, the package name fields (QW0059PN and QW0058PN) refer to the package that contains the SQL OPEN statement for the cursor used by the FETCH statement. This is not necessarily the package that contains the FETCH statement.

For example, a stored procedure might open a cursor that is subsequently - in the thread that called the stored procedure - used by a FETCH statement. The stored procedure and its calling thread might use different packages. In this case, the 59 and 58 IFCID records for the FETCH statement refer to the package that is used by the stored procedure.

I understand that there is a close relationship between a FETCH statement and its cursor, but this IFCID behavior means that, in this particular situation, you cannot use the following SQL SELECT statement to get the SQL statement text, which (so far, in my experience) works for all other statements:

SELECT STATEMENT FROM SYSIBM.SYSPACKSTMT WHERE NAME='package' AND STMTNO=statement-number

Is this a bug or deliberate (working as designed)?

Updated on 2013-10-28T07:29:16Z at 2013-10-28T07:29:16Z by Graham_Hannington
  • bsteeg_screen
    bsteeg_screen
    1 Post

    Re: IFCIDs 59 and 58 for FETCH refer to the package that opens the cursor: bug or by design?

    ‏2013-11-09T12:13:12Z  

    Hello,

    I don't monitor this forum for questions and just happen to see it. Maybe you already received a reply via other channels. If not, here are my observations and comments.

    When you are fetching from the calling program from a result set that was opened by a SP (WITH RETURN), the program name that shows up in IFCID 59 is indeed the program name of the SP that contains the cursor, and not the program name of the program that actually contains the FETCH statement (whereas the statement number is the one that belongs to the calling program that issues the actual FETCH call).

    The reason for that is that even though the FETCH is issued by the calling program, the code that is executed to do the actual work is part of the SP package (that contains the actual cursor). So when you execute the FETCH you are actually running under the SP package, and the program name in IFCID 59 is the name of the 'current package being executed'. (If you were to look at the accounting class 10 information of the SP package, you would actually see the FETCH statements there, and not in the calling program's package info.)

    So it is not bug that things are this way, although I agree it is somewhat confusing. In V10 we introduced a unique statement_id. This can be found in SYSPACKSTMT in the STMT_ID column. I have requested to add this statement_id information to more DB2 trace records (including IFCID 59) but as it is quite a bit of work, I expect that this will be done on a release/version boundary. If you think that adding the stmt_id info to trace records would help you please raise an RFE via http://www.ibm.com/developerworks/rfe/?BRAND_ID=184

    Hope this helps.

    Kind regards,

    Bart.