DBD::DB2 and REBIND PACKAGE

------_=_NextPart_001_01C76D7E.603D8810
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Greetings!
=20
I am using DBD::DB2 as part of an automation system for the deployment
of stored procedures. The deployment of the stored procedures is
successful, but in the course of reloading the procs, parent procs (ie,
that CALL to a child proc we just reloaded) get set to invalid. This is
not normally an issue, as invalid procs get rebound at runtime. However,
as this is an automated process, we'd like to force the rebind during
our transaction, so that if somebody breaks something, we catch it and
roll back the transaction, rather than propagating errors into the
runtime environment.
=20
However, when I execute a statement like:
=20
REBIND PACKAGE LK.P0343993=20
=20
it fails with the following error:
=20
[IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "BIND" was
found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"EXPAND". SQLSTATE=3D42601
=20
=20
I suspect, although I haven't been able to confirm it yet, that REBIND
isn't actually a valid SQL statement, and is rather a db2 specific
command, which is why the statement fails with that error. I can, in a
command line, run the statement:
=20
db2 REBIND PACKAGE LK.P0343993
=20
and it works successfully.
=20
My question is twofold:
=20
1) Is the REBIND (or just BIND) a valid statement, which I believe it is
not=20
2) Is there a way to call the REBIND directly, without preparing a
statement, in such a way that I can continue to use the DBD::DB2 module
to complete this task.
=20
Please let me know if there are additional details that would help in
answering these questions.
=20
Thanks!
=20
Dave Sorenson
Senior Web Administrator
Jackson National Life Insurance
david.sorenson@jnli.com

------_=_NextPart_001_01C76D7E.603D8810--

Re: DBD::DB2 and REBIND PACKAGE

Sorenson, David wrote:
> Greetings!
>
> I am using DBD::DB2 as part of an automation system for the deployment
> of stored procedures. The deployment of the stored procedures is
> successful, but in the course of reloading the procs, parent procs (ie,
> that CALL to a child proc we just reloaded) get set to invalid. This is
> not normally an issue, as invalid procs get rebound at runtime. However,
> as this is an automated process, we'd like to force the rebind during
> our transaction, so that if somebody breaks something, we catch it and
> roll back the transaction, rather than propagating errors into the
> runtime environment.
>
> However, when I execute a statement like:
>
> REBIND PACKAGE LK.P0343993
>
> it fails with the following error:
>
> [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "BIND" was
> found following "BEGIN-OF-STATEMENT". Expected tokens may include:
> "EXPAND". SQLSTATE=42601
>
>
> I suspect, although I haven't been able to confirm it yet, that REBIND
> isn't actually a valid SQL statement, and is rather a db2 specific
> command, which is why the statement fails with that error. I can, in a
> command line, run the statement:
>
> db2 REBIND PACKAGE LK.P0343993
>
> and it works successfully.
>
> My question is twofold:
>
> 1) Is the REBIND (or just BIND) a valid statement, which I believe it is
> not
> 2) Is there a way to call the REBIND directly, without preparing a
> statement, in such a way that I can continue to use the DBD::DB2 module
> to complete this task.
>
> Please let me know if there are additional details that would help in
> answering these questions.

You're hitting the wrong list for these types of questions - you really
should contact the email address listed in the DBD::DB2 docs.

Anyway, since I *do* know the answer, I might as well help you...

The DB2 command-line processor (the "db2" command) supports two types of
commands:
- SQL statements
- Built-in commands that map to the administrative API

The administrative API is everything that DB2 supports but isn't a SQL
statement and isn't supported in the CLP. That includes the various
catalog/uncatalog commands, the snapshot commands, the get/set
database/instance configuration commands, the load/export/import
commands, plus commands like reorg, runstats and rebind.

IBM has been doing a good job exposing more and more of this
functionality through SQL, allowing you to retrieve information through
the snapshot table functions and admin views; and allowing you to change
things through the SYSPROC.ADMIN_CMD stored procedure. The rebind
command isn't exposed in V8.2 and V9.1, though.

Your next option is to call the administrative API directly. This is a
C API, so you'll have to write XS code. See the "Administrative API
Reference" for details. If you don't like that idea, you'll have to
invoke the DB2 command-line processor.

Hildo