DB2 Z/OS Stored proc help

appunni

Hi,

I am trying to execute an DB2 SQL stored proc in mainframe.

my proc is

CREATE PROCEDURE db2t.abuproc
()
LANGUAGE SQL
external name abuproc
wlm environment dbatspas
BEGIN
UPDATE DBXR.PRTTAB SET NUMBER=10 WHERE NUMBER=0;
end?

But when am executing it thru coammnd line editor, am gtting SQLERROR -444...

it sauys its not able to locate the package...is it required to use a package for a SQL Stored proc and if so pls let me know where is the package is creatied by the system...

appreciate a help.

thanks
appunni

Aug 10 '07 #1

rinkum2007db2

Hi,

After creating the stored procedure, have you compiled the stored procedure?
Here are the steps in case you have not -
1. Write the code in Notepad & save it with the extension as .db2.
(make sure you DB2 is connected)
2. On command line editor give command tf-! vf- <path for your file>
(here ! is the terminating character in your stored procedure)

This will give you a message that the check completed & then you can run the procedure from command line editor with a call command.

Hope this helps you.

Regards,
rinku

Hi,

I am trying to execute an DB2 SQL stored proc in mainframe.

my proc is

CREATE PROCEDURE db2t.abuproc
()
LANGUAGE SQL
external name abuproc
wlm environment dbatspas
BEGIN
UPDATE DBXR.PRTTAB SET NUMBER=10 WHERE NUMBER=0;
end?

But when am executing it thru coammnd line editor, am gtting SQLERROR -444...

it sauys its not able to locate the package...is it required to use a package for a SQL Stored proc and if so pls let me know where is the package is creatied by the system...

appreciate a help.

thanks
appunni

Sep 7 '07 #2

Remi

Hello :

Try not to use the external proc and do it all in database, send you an example with select.

CREATE PROCEDURE RACE.SP_LISTARPAISES6(
IN OPCION VARCHAR(2) ,
IN CODPAIS VARCHAR(3) ,
IN DENPAIS VARCHAR(30),
IN TIP VARCHAR(1)
)
LANGUAGE SQL
RESULT SETS 1
BEGIN
DECLARE SQLSELE1 CHAR(512);
DECLARE C1 CURSOR WITH RETURN FOR S1;
-- DEBERAN PASAR EL VALOR DE CODPAIS INCLUYENDO EL PORCENTUAL '02%'--
IF TIP = '1' THEN
SET SQLSELE1 = 'SELECT A .CODPAI , A . DENPAI , A . PFTPAI FROM RACE . YYYPAI A WHERE
A.CODPAI LIKE ? FETCH FIRST 5000 ROWS ONLY ';
ELSE
SET SQLSELE1 = 'SELECT A .CODPAI , A . DENPAI , A . PFTPAI FROM RACE . YYYPAI A WHERE
A.CODPAI LIKE ?
FETCH FIRST 5000 ROWS ONLY';
END IF;
PREPARE S1 FROM SQLSELE1;
OPEN C1 USING CODPAIS;
END;

Sep 19 '07 #3