Contents of package - local vs. remote bind

James Szabo

Contents of package - local vs. remote bind
Environment: multiple DB2 UDB for OS/390 V6 systems, application programs written in COBOL.

We are trying to understand why the contents of SYSIBM.SYSPACKSTMT is different when you do a local bind vs. a remote bind.

If you bind a package into the same subsystem that you are running the bind command on, SYSIBM.SYSPACKSTMT contains all statements that need to be optimized (e.g. DEFINE CURSOR and singleton SELECT/INSERT/UPDATE/DELETE), as well as other executable statements that appear in the program (OPEN, FETCH, CLOSE, SET).

If that same package is bound to a remote location, SYSIBM.SYSPACKSTMT only contains the optimizable statements.

This makes things tough to match performance information from our DB2 monitor with the statement text, as the statement number shown in the monitor is that of the OPEN statement, not the DEFINE CURSOR. And that's not in the remotely-bound package.

How do other shops deal with this? Would BIND with the COPY parameter give me a "full" package at a remote location? Is there any other way to get this?



James Szabo

Contents of package - local vs. remote bind
(in response to James Szabo)
Environment: multiple DB2 UDB for OS/390 V6 systems, application programs written in COBOL.

We are trying to understand why the contents of SYSIBM.SYSPACKSTMT is different when you do a local bind vs. a remote bind.

If you bind a package into the same subsystem that you are running the bind command on, SYSIBM.SYSPACKSTMT contains all statements that need to be optimized (e.g. DEFINE CURSOR and singleton SELECT/INSERT/UPDATE/DELETE), as well as other executable statements that appear in the program (OPEN, FETCH, CLOSE, SET).

If that same package is bound to a remote location, SYSIBM.SYSPACKSTMT only contains the optimizable statements.

This makes things tough to match performance information from our DB2 monitor with the statement text, as the statement number shown in the monitor is that of the OPEN statement, not the DEFINE CURSOR. And that's not in the remotely-bound package.

How do other shops deal with this? Would BIND with the COPY parameter give me a "full" package at a remote location? Is there any other way to get this?