[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2223
  • Last Modified:

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

0
mbevilacqua
Asked:
mbevilacqua
  • 4
  • 3
2 Solutions
 
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
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
mbevilacquaAuthor Commented:
DB2 SQL error: SQLCODE: -449, SQLSTATE: 42878, SQLERRMC: SKUMAR.CREATE_COMPONENT_EVENT
0
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now