Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

What is P1:BEGIN and P2:BEGIN do?

Posted on 2008-09-29
4
Medium Priority
?
1,329 Views
Last Modified: 2012-05-05
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_TOTAL_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.






0
Comment
Question by:volking
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Author Comment

by:volking
ID: 22598515
Am I , ONCE AGAIN, missing some foundational concept?
0
 
LVL 18

Accepted Solution

by:
Dave Ford earned 1600 total points
ID: 22598557

According to the error message:

"INETGER" <> "INTEGER"

0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 400 total points
ID: 22598627
Hi volking,

P1: and P2: are just labels within the routine.  

"P1: BEGIN" is the label "P1:" (or "P1"), followed by a BEGIN statement the defines a logical block and must be accompanied by an END statement.

In the function (Stored Procedure) you declare a block called P1.  Within P1, you declare another block called P2.


As for the error, follow Dave's advise and correct the spelling in the DECLARE GLOBAL TABLE command.   :)


Kent
0
 
LVL 5

Author Closing Comment

by:volking
ID: 31501265
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!

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question