Re : Contents of package - local vs. remote bind

Sanjeev (CTS) S

Re : Contents of package - local vs. remote bind
> James,
> It looks that it has something to do with Deferred Embedded Sqls. I
> read it sometime back, So some guesses :
> All the remote access embedded sqls through the DRDA is called as the
> deferred embedded sqls which are neither fully static nor fully dynamic.
> It is ofcourse embedded in the application like static sqls but it is
> prepared at the execution time. Many of its processings are done at the
> bind time also, like authorization id and qualifier determination with the
> owner.
> It looks that SYSIBM.SYSPACKSTMT keeps only (already) prepared
> statements.
> Let's see if there is something right in this guess or some other
> reason for making it like this by IBMer's.
>
> HTH
> Regards,
> Sanjeev
> -----Original Message-----
> From: James Szabo [SMTP:[login to unmask email]
> Sent: Thursday, December 21, 2000 12:15 AM
> To: [login to unmask email]
> Subject: 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?
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Steven Mallett

Re: Contents of package - local vs. remote bind
(in response to Sanjeev (CTS) S)
James,

Our Local and Remote SYSPACKSTMTS appear to be the same.

We do our Remote Bind Copy as a 2 phase process, mainly due to RACF
restrictions ( not having an appropriate secondary authid for the DB on both
systems we use a primary authid instead ). The remote job is really just a
normal REBIND so maybe you could try rebinding on your remote subsystem, if
you haven't already.

The jobs we run are something like those below - would your equivalent job/s
be similar to these ?

Local Subsystem :
BIND PACKAGE (<remote subsystem>.<remote collid>) QUALIFIER(<remote
qualifier>) -
OWNER(<primary authid>) COPY(<local collid>.NAME) -
VALIDATE(RUN)

Remote Subsystem :
DSN SYSTEM(<remote subsystem>)
REBIND PACKAGE -
((<remote collid>.*) -
QUALIFIER(<remote qualifier>)-
VALIDATE(BIND)-
ISOLATION(CS)-
OWNER(<remote owner>)-
EXPLAIN(YES)




regards,
Steve

I 2nd Floor 484 St Kilda Rd, Melbourne

' (03) 9865 8557 7 (03) 9804 5368
* <mailto: [login to unmask email]>



> -----Original Message-----
> From: James Szabo [SMTP:[login to unmask email]
> Sent: Thursday, December 21, 2000 5:45 AM
> To: [login to unmask email]
> Subject: 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?
>
>
>