volking
asked on
What is P1:BEGIN and P2:BEGIN do?
Can someone explain P1: & P2: to me? (or maybe point me at a BONEHEAD doc?)
I found a StoredProc sample using a GLOBAL TEMPORARY TABLE and a CURSOR ... It uses P1: and P2: ..... My StoredProc (using P1: & P2:) compiles and creates the StoredProc in the library, but attempts to CALL the SP returns an error.
-------------------------- ---------- ---------- ---------- --------
CREATE PROCEDURE CSIDEV.DVLCHRT ( )
DYNAMIC RESULT SETS 3
LANGUAGE SQL
SPECIFIC CSIDEV.DVLCHRT
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
P1 : BEGIN
DECLARE iYYYY INTEGER DEFAULT 0 ;
DECLARE GLOBAL TEMPORARY TABLE ITEMS (
LOC VARCHAR(4),
DIVAMT INTEGER,
MTHYR VARCHAR(50),
YYYY INTEGER,
MM INETGER
) WITH REPLACE NOT LOGGED ;
SELECT FISCALYYYY INTO iYYYY FROM CSIDEV . YMD WHERE DAYDATE = CURRENT DATE;
INSERT INTO SESSION . ITEMS ( LOC, DIVAMT, MTHYR, YYYY, MM)
SELECT DISTINCT
'',
0,
CSIDEV.YMD.MONNAME || ' ' || CAST(YYYY AS varchar(4)) AS mthyr,
CSIDEV.YMD.YYYY,
CSIDEV.YMD.MM
FROM CSIDEV.YMD
WHERE FiscalYYYY = iYYYY;
INSERT INTO SESSION . ITEMS ( LOC, DIVAMT, MTHYR, YYYY, MM)
SELECT
CSIDEV.DVL.Loc,
SUM(CSIDEV.DVL.EXTENDED_TO TAL_ALLOC) AS DivAmt,
CSIDEV.DVL.MONNAME || ' ' || CAST(CSIDEV.DVL.YYYY AS varchar(4)) AS mthyr,
CSIDEV.DVL.YYYY,
CSIDEV.DVL.MM
FROM
CSIDEV.DVL
GROUP BY
CSIDEV.DVL.Loc,
CSIDEV.DVL.FiscalYYYY,
CSIDEV.DVL.MONNAME || ' ' || CAST(CSIDEV.DVL.YYYY AS varchar(4)),
YYYY,
MM
HAVING (CSIDEV.DVL.FiscalYYYY = iYYYY);
P2: BEGIN
DECLARE DB2_SP_SQL1 CURSOR FOR
select distinct LOC as ColName
from SESSION . ITEMS
where LOC>''
order by ColName;
DECLARE DB2_SP_SQL2 CURSOR FOR
select distinct mthyr as RowName, YYYY, MM
from SESSION . ITEMS
order by YYYY, MM;
DECLARE DB2_SP_SQL3 CURSOR FOR
Select Loc as ColName, mthyr as RowName, DivAmt as Valu
from SESSION . ITEMS;
OPEN DB2_SP_SQL1 ;
OPEN DB2_SP_SQL2 ;
OPEN DB2_SP_SQL3 ;
END P2 ;
END P1 ;
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ------
Above script creates SP in my Library ...
BUT attempt to call .... results in error
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- -------
call csidev.dvlchrt
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] INETGER in *LIBL type *SQLUDT not found. Cause . . . . . : INETGER in *LIBL type *SQLUDT was not found. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, or trigger was not found. If a function was not found, INETGER is the service program that contains the function. The function will not be found unless the external name and usage name do not match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.
I found a StoredProc sample using a GLOBAL TEMPORARY TABLE and a CURSOR ... It uses P1: and P2: ..... My StoredProc (using P1: & P2:) compiles and creates the StoredProc in the library, but attempts to CALL the SP returns an error.
--------------------------
CREATE PROCEDURE CSIDEV.DVLCHRT ( )
DYNAMIC RESULT SETS 3
LANGUAGE SQL
SPECIFIC CSIDEV.DVLCHRT
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
P1 : BEGIN
DECLARE iYYYY INTEGER DEFAULT 0 ;
DECLARE GLOBAL TEMPORARY TABLE ITEMS (
LOC VARCHAR(4),
DIVAMT INTEGER,
MTHYR VARCHAR(50),
YYYY INTEGER,
MM INETGER
) WITH REPLACE NOT LOGGED ;
SELECT FISCALYYYY INTO iYYYY FROM CSIDEV . YMD WHERE DAYDATE = CURRENT DATE;
INSERT INTO SESSION . ITEMS ( LOC, DIVAMT, MTHYR, YYYY, MM)
SELECT DISTINCT
'',
0,
CSIDEV.YMD.MONNAME || ' ' || CAST(YYYY AS varchar(4)) AS mthyr,
CSIDEV.YMD.YYYY,
CSIDEV.YMD.MM
FROM CSIDEV.YMD
WHERE FiscalYYYY = iYYYY;
INSERT INTO SESSION . ITEMS ( LOC, DIVAMT, MTHYR, YYYY, MM)
SELECT
CSIDEV.DVL.Loc,
SUM(CSIDEV.DVL.EXTENDED_TO
CSIDEV.DVL.MONNAME || ' ' || CAST(CSIDEV.DVL.YYYY AS varchar(4)) AS mthyr,
CSIDEV.DVL.YYYY,
CSIDEV.DVL.MM
FROM
CSIDEV.DVL
GROUP BY
CSIDEV.DVL.Loc,
CSIDEV.DVL.FiscalYYYY,
CSIDEV.DVL.MONNAME || ' ' || CAST(CSIDEV.DVL.YYYY AS varchar(4)),
YYYY,
MM
HAVING (CSIDEV.DVL.FiscalYYYY = iYYYY);
P2: BEGIN
DECLARE DB2_SP_SQL1 CURSOR FOR
select distinct LOC as ColName
from SESSION . ITEMS
where LOC>''
order by ColName;
DECLARE DB2_SP_SQL2 CURSOR FOR
select distinct mthyr as RowName, YYYY, MM
from SESSION . ITEMS
order by YYYY, MM;
DECLARE DB2_SP_SQL3 CURSOR FOR
Select Loc as ColName, mthyr as RowName, DivAmt as Valu
from SESSION . ITEMS;
OPEN DB2_SP_SQL1 ;
OPEN DB2_SP_SQL2 ;
OPEN DB2_SP_SQL3 ;
END P2 ;
END P1 ;
--------------------------
Above script creates SP in my Library ...
BUT attempt to call .... results in error
--------------------------
call csidev.dvlchrt
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] INETGER in *LIBL type *SQLUDT not found. Cause . . . . . : INETGER in *LIBL type *SQLUDT was not found. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, or trigger was not found. If a function was not found, INETGER is the service program that contains the function. The function will not be found unless the external name and usage name do not match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh my ... do I feel stupid ...
HEY! VOLKING ..... READ THE SILLY ERROR MESSAGE!
Until someone is comfortable with a particular syntax, a DWEEB like me always assumes the problem is lack-of-knowledge ... not that my fingers made a mistake ... grin ...
Thanks Dave!
HEY! VOLKING ..... READ THE SILLY ERROR MESSAGE!
Until someone is comfortable with a particular syntax, a DWEEB like me always assumes the problem is lack-of-knowledge ... not that my fingers made a mistake ... grin ...
Thanks Dave!
ASKER