DB2 ZOS stored procedure

Please convert this stored procedure from DB2 for LUW to DB2 for ZOS.


CREATE
PROCEDURE CREATE_COMPONENT_EVENT(IN useTablespace_fl CHAR(1))
result sets 1
language sql
    BEGIN
        DECLARE l_sql VARCHAR(2000);
        DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' SET l_sql = NULL;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '42889' SET l_sql = NULL;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' SET l_sql = NULL;
        SET l_sql  = 'CREATE TABLE COMPONENT_EVENT (COMPONENT_EVENT_ID   BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY( START WITH 1,INCREMENT BY 1,CACHE 20),COMPONENT_CD         VARCHAR(50) NOT NULL,COMPONENT_DESC       VARCHAR(50) ,COMPONENT_EVENT      VARCHAR(30) ,COMPONENT_LEVEL      VARCHAR(30) ,EVENT_DT             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,CTRL_INSERT_DT  TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP,CTRL_UPDATE_DT  TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP )';
        IF useTablespace_fl = 'Y' THEN
            SET l_sql = l_sql ||' IN MYDATE INDEX IN MYINDX LONG IN MYBLOB';
        END IF;
        EXECUTE IMMEDIATE l_sql;
        SET l_sql = 'CREATE UNIQUE INDEX CMPNNTVNT_PKX ON COMPONENT_EVENT (COMPONENT_EVENT_ID)';
        EXECUTE IMMEDIATE l_sql;
        SET l_sql = 'ALTER TABLE COMPONENT_EVENT ADD CONSTRAINT CMPNNTVNT_PKX PRIMARY KEY (COMPONENT_EVENT_ID)';
        EXECUTE IMMEDIATE l_sql;
        SET l_sql = 'DROP VIEW INSTALLED_COMPONENT';
        EXECUTE IMMEDIATE l_sql;
        SET l_sql = 'CREATE VIEW INSTALLED_COMPONENT AS SELECT DISTINCT COMPONENT_CD, COMPONENT_LEVEL, COMPONENT_DESC FROM COMPONENT_EVENT  WHERE COMPONENT_EVENT NOT IN (''UNINSTALL'',''MIGRATE'')       AND COMPONENT_EVENT_ID NOT IN ( SELECT E1.COMPONENT_EVENT_ID     FROM COMPONENT_EVENT E1, COMPONENT_EVENT E2    WHERE E1.COMPONENT_EVENT     = ''INSTALL''    AND E1.COMPONENT_CD    = E2.COMPONENT_CD      AND E1.COMPONENT_EVENT_ID    < E2.COMPONENT_EVENT_ID )';
        EXECUTE IMMEDIATE l_sql;
    END

mbevilacquaAsked:
Who is Participating?
 
momi_sabagCommented:
code -449 indicates that there is some thing wrong with the external name parameter
in your case it's missing
by default db2 will pick the procedure name, but since yours is more than 8 chars long it can not be use as a load module name in zOS
0
 
momi_sabagCommented:
what is the error you are recieveing ?
which version of db2 for zos ?
0
 
mbevilacquaAuthor Commented:
The script needs to work on V8 and V9. I am working on obtaining the error again, but did it compile for you in its current state?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
momi_sabagCommented:
i did not try to comipile it since my zos db2 is on a different network than my internet computer so i can't copy and paste, but the syntax is supposed to be compatiable
0
 
mbevilacquaAuthor Commented:
DB2 SQL error: SQLCODE: -449, SQLSTATE: 42878, SQLERRMC: SKUMAR.CREATE_COMPONENT_EVENT
0
 
mbevilacquaAuthor Commented:
I do not understand. Is the solution to change the name to less than 8 characters? I tried this, but it reports the same error.
0
 
momi_sabagCommented:
if you change the name to something with less than 8 characters it should work
you can also try to add the parameter external name and give a name which is less than 8 chars long
0
 
vermakrishCommented:
Hi,
    No need of truncating the name of the procedure.DB2 V8 allows upto 128 chracter long procedure name.But since its  a External Procedure(non Cobol) hence it MUST have EXTERNAL clause.else DB2 will take its external name as the default name(system predefined) which is wat causing the problem in here.

hopefully this shld solve ur problem.atleast not this error,while compiling.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.