Stored procedures and libraries

[CENTRINO]

When we write SQL stored procedures in a library,(using iseries navigator),
they seem to be somehow, attached to such a library or schema, so we cannot
move the SQL stored procedure to another library

We are calling them from CL programs using QZDFMDB2 utility, that allows us
to call SQL stored procedures. We use that undocumented utility becouse it
uses the same QTEMP library as the caller program. versus calling it via
QSHELL wich seems to be the standar method that creates its own QTEMP
library (a new job is created).

Well ... using QZDFMDB2 you *must* sepecify the library where stored
procedure is and was created.

My question: is there any way to generate or modify a stored procedure in
order to be able to move it onto anoder library-schema so that it could be
found via Library lLsts like any other OPM or ILE program ?

Thanks in advance.

PS
We are on V5R4M0
--
.

[CRPence]

On 15-Mar-2012 06:14 , CENTRINO wrote:
> When we write SQL stored procedures in a library, (using iSeries

> navigator), they seem to be somehow, attached to such a library or
> schema, so we cannot move the SQL stored procedure to another
> library

There is no "MOVE" statement in SQL; nor the equivalent effect via
the rename of a library\schema. The SQL allows DROP, CREATE, and ALTER
for a PROCEDURE.

If the external object to which an External Stored Procedure was
directed via the External Name was moved, then the the procedure must be
re-created or altered to point to the new location; i.e. to rebind to
the new External Name.

If the stored procedure name itself must since be associated with a
different library, then DROP the ROUTINE from the prior library name,
and create the PROCEDURE again, but into the other library name.

> We are calling them from CL programs using QZDFMDB2 utility, that
> allows us to call SQL stored procedures. We use that undocumented
> utility becouse it uses the same QTEMP library as the caller program.

> versus calling it via QSHELL wich seems to be the standard method

> that creates its own QTEMP library (a new job is created).

The supported interface is the DB2 command line, the db2 executable,
via QSH. While convenient, there is little good reason to use an
/unsupported/ interface if the effects should be predictable. The
utility is implemented using the SQL CLI, thus a program using the
SQLCLI could enable calling the the stored procedures from the CL. From
a CLLE, the SQLCLI could be called directly. I prefer to use REXX
instead of CL, and then code my [IN parms only] SQL CALL directly from
the REXX SQL; *EXECSQL environment.

> Well ... using QZDFMDB2 you *must* specify the library where stored

> procedure is and was created.

That utility performs the dynamic SQL requests with the SET OPTION
NAMING=*SQL so the unqualified routine name would refer to the
_authorization identifier_ under which the statement runs. If the
routine is in library FRED, then the user named FRED can run that
without any modifications to the PATH; i.e. for user FRED, CALL X()
becomes CALL FRED.X().

If the PATH can be changed within that utility in a prior SQL
statement string, e.g. perhaps separated by a semicolon, then precede
the SQL CALL with an appropriate SET PATH statement [according to the
rules for *SQL naming] to enable CALL X() to be the effective equivalent
of CALL *LIBL/X(). Note: This does not help for a "moved" routine, for
which the actual location of the procedure name as defined to the SQL
must be "moved" as well.

> My question: is there any way to generate or modify a stored

> procedure in order to be able to move it onto another library-schema
> so that it could be found via Library lists like any other OPM or ILE
> program ?

If the issue is for the External Name, then understand that the
qualification of the referenced program is bound at create-time, so the
routine would need to be re-created; or since v6r1 where the "-EXTERNAL
NAME--external-program-name-" is supported, ALTER PROCEDURE:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzaproceduree.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstaproceduree.htm

If the issue is the qualified name of the procedure, then create the
procedure into the correct\final intended location and use NAMING=*SYS
and the default path of *LIBL in the environment from which the CALL
statement is made to an unqualified ROUTINE name.

> PS We are on V5R4M0

Thanks. So few others divulge the most basic information.

Regards, Chuck

[CENTRINO]

Thank you for your extensive unswer.


"CENTRINO" <no...@nonelandia.com> escribió en el mensaje
news:jjsq07$it8$1...@dont-email.me...

[CENTRINO]

Thank you for your extensive unswer.

"CRPence" <CRP...@vnet.ibm.com> escribió en el mensaje
news:jk89d4$6ba$1...@speranza.aioe.org...