Running SQL PL stored procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PJackson@txfb-ins.com - 23 Apr 2007 15:01 GMT

I have a number of SQL PL stored procedures (DB2 UDB 8.2 on Windows)
that were created in the DB2 Development Center. From there I can run
them just fine.

When I use another tool (WinSQL) and attempt to run while connected to
the same UDB database:
CALL TFBUDB.SMACF_DRIVER_INFO(21264682);
I get the following message:
SQL0444N Routine "*VER_INFO" (specific name "SQL070423085539400") is
implemented with code in
library or path "...DRIVER_INFO", function "TFBUDB.SMACF_DRIVER_INFO"
which cannot be accessed.
Reason code: "4". SQLSTATE=42724

The SP is defined as:
CREATE PROCEDURE TFBUDB.SMACF_Driver_Info (IN Pol_Nbr Char(10)
,OUT ErrNo INTEGER
,OUT ErrMsg CHAR(80) )
RESULT SETS 1
NOT DETERMINISTIC
LANGUAGE SQL

so perhaps there are some additional steps needed to make these
actually runnable from outside the development environment?

thanks,

Phil Jackson

Reply to this Message

Brian Tkatch - 23 Apr 2007 15:32 GMT

>I have a number of SQL PL stored procedures (DB2 UDB 8.2 on Windows)
>that were created in the DB2 Development Center. From there I can run
[quoted text clipped - 9 lines]
>which cannot be accessed.
> Reason code: "4". SQLSTATE=42724

The error given _Routine "*VER_INFO"_ is created temporarily by the
tool to execute the procedure for you. The SPECIFIC NAME is (most
likely) because the tool did not supply one, so DB2 uses the default,
which is a timestamp.

>The SP is defined as:
>CREATE PROCEDURE TFBUDB.SMACF_Driver_Info (IN Pol_Nbr Char(10)
[quoted text clipped - 6 lines]
>so perhaps there are some additional steps needed to make these
>actually runnable from outside the development environment?

IIRC, you need to execute "SET PATH TFBUDB, CURRENT PATH" and try
again.
B.

>thanks,
>
>Phil Jackson

Reply to this Message

PJackson@txfb-ins.com - 23 Apr 2007 19:56 GMT

When I look in the properties in DB2 Dev Center it shows Shcema as
TFBUDB, which looks correct.

When I look at the DDL in Quest, :
SET SCHEMA UTPSJ01 ;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","UTPSJ01";

It varies, depending who I am logged on as....I see otehr SPs remain
consant in this respect. Schema is always TFBUDB....
When I try to run the SP in Quest via its Script Runner tool it gets
a different error, SQL 0440N, SQLSTATE=42884.

Not sure that anyone has developed any DB2 SQL PL procedures since
they upgraded to 8.2, so perhaps something is not pointing to the
right place? Seems to run fine within the context of the DB2 Dev
Center though...

thanks,

Phil

On Apr 23, 9:32 am, Brian Tkatch <N/A> wrote:>

> The error given _Routine "*VER_INFO"_ is created temporarily by the
> tool to execute the procedure for you. The SPECIFIC NAME is (most
[quoted text clipped - 10 lines]
>
> - Show quoted text -

Reply to this Message

PJackson@txfb-ins.com - 23 Apr 2007 20:26 GMT

> >I have a number of SQL PL stored procedures (DB2 UDB 8.2 on Windows)
> >that were created in the DB2 Development Center. From there I can run
[quoted text clipped - 25 lines]
> >so perhaps there are some additional steps needed to make these
> >actually runnable from outside the development environment?

The DB2 Dev center shows the schema for this SP to be TFBUDB.

In Quest, when I look at the DDL , it depends upon who I am logged in
as, as to what I see in these two lines:
SET SCHEMA UTPSJ01 ;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","UTPSJ01";

Other stored procedures consistantly show :SET SCHEMA TFBUDB;
and
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","TFBUDB";

When I try to run the SP from the script runner tool, I get a
different error,
SQL0440n, SQLSTATE=42884

Strange that it runs fine in the context of the DB2 Dev Center!

> IIRC, you need to execute "SET PATH TFBUDB, CURRENT PATH" and try
> again.
[quoted text clipped - 5 lines]
>
> - Show quoted text -

Reply to this Message

Lew - 24 Apr 2007 21:47 GMT

I don't know if this may be having an affect but when I execute a
stored procedure from the unix command prompt I have to provide for
both inputs and outputs in the call statement. In your case the call
statement would be:

CALL TFBUDB.SMACF_DRIVER_INFO(21264682,?,?);

Reply to this Message

PJackson@txfb-ins.com - 26 Apr 2007 15:15 GMT

Interesting, as using those parameter markers works fine from Quest.
WinSWL, on the other hand does not like the parameter markers:
CLI0100E Wrong number of parameters. SQLSTATE=07001
CLI0125E Function sequence error. SQLSTATE=S1010
so that error i supose is actually bogus.

thanks!

> I don't know if this may be having an affect but when I execute a
> stored procedure from the unix command prompt I have to provide for
> both inputs and outputs in the call statement. In your case the call
> statement would be:
>
> CALL TFBUDB.SMACF_DRIVER_INFO(21264682,?,?);

Reply to this Message