Convert Oracle Stored procedure to Db2

Jisha George

Convert Oracle Stored procedure to Db2

Hi,

I need help to convert the below stored proc which has been written for Oracle to DB2. 

CREATE OR replace PROCEDURE "employee" 

(   empid IN VARCHAR2
    rollondate IN DATE
    rolloffdate IN DATE
    drsidentifier IN VARCHAR2
    ref_cursor_getdept OUT SYS_REFCURSOR 
)   IS 
v_cursor SYS_REFCURSOR
BEGIN 
OPEN v_cursor FOR 
'SELECT DISTINCT NK.ADMIN_DEPT_ID ADMINDEPTID,CC.Prod_tp_cd AS LOB FROM WCC.DEPTCOMPONENT CC 

INNER JOIN WCC.NATIVEKEY NK ON  CC.DEP_COMPONENT_ID = NK.DEPT_ID  AND CC.EXPIRY_DT >=  SYSDATE and CC.Prod_tp_cd in (' || drsidentifier || ')

INNER JOIN  WCC.DEPTROLE CR ON  CR.DEP_COMPONENT_ID=CC.DEP_COMPONENT_ID and (CR.END_DT IS NULL OR CR.END_DT >= SYSDATE)

WHERE CR.CONT_ID = ' || empid || ' and  ((CC.ISSUE_DT>=TO_DATE(''' || rollondate || ''') AND CC.ISSUE_DT<=TO_DATE(''' || rolloffdate || ''')) OR (CC.EXPIRY_DT>=TO_DATE(''' || rollondate || ''') AND CC.EXPIRY_DT<=TO_DATE(''' || rolloffdate || ''')) OR (CC.ISSUE_DT<=TO_DATE(''' || rollondate || ''') AND CC.EXPIRY_DT>=TO_DATE(''' || rolloffdate || '''))) 

    and NK.ADMIN_DEPT_ID is not null and CC.DEPT_ST_TP_CD=10050'; 
ref_cursor_getdept := v_cursor; 
END;

Below is how i converted:-

CREATE OR replace PROCEDURE "employee" 

(   IN empid  VARCHAR
    IN rollondate TIMESTAMP
    IN rolloffdate TIMESTAMP
    IN drsidentifier VARCHAR
   OUT ref_cursor_getdept SYS_REFCURSOR 
)   IS 
v_cursor SYS_REFCURSOR
BEGIN 
OPEN v_cursor FOR 
'SELECT DISTINCT NK.ADMIN_DEPT_ID ADMINDEPTID,CC.Prod_tp_cd AS LOB FROM WCC.DEPTCOMPONENT CC 

INNER JOIN WCC.NATIVEKEY NK ON  CC.DEP_COMPONENT_ID = NK.DEPT_ID  AND CC.EXPIRY_DT >=  SYSDATE and CC.Prod_tp_cd in (' || drsidentifier || ')

INNER JOIN  WCC.DEPTROLE CR ON  CR.DEP_COMPONENT_ID=CC.DEP_COMPONENT_ID and (CR.END_DT IS NULL OR CR.END_DT >= SYSDATE)

WHERE CR.CONT_ID = ' || empid || ' and  ((CC.ISSUE_DT>=TO_DATE(''' || rollondate || ''') AND CC.ISSUE_DT<=TO_DATE(''' || rolloffdate || ''')) OR (CC.EXPIRY_DT>=TO_DATE(''' || rollondate || ''') AND CC.EXPIRY_DT<=TO_DATE(''' || rolloffdate || ''')) OR (CC.ISSUE_DT<=TO_DATE(''' || rollondate || ''') AND CC.EXPIRY_DT>=TO_DATE(''' || rolloffdate || '''))) 

    and NK.ADMIN_DEPT_ID is not null and CC.DEPT_ST_TP_CD=10050'; 
ref_cursor_getdept := v_cursor; 
END;

Should i change the dynamically passed values in the query to "?"

Are there any helpful links with some sample stored procs using cursor, since i am a beginner?

Daniel Luksetich

Convert Oracle Stored procedure to Db2
(in response to Jisha George)
You cannot use an alias for you name on this forum, please update your IDUG profile with your full name or you will be removed from the forum.

Thanks,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: W W [mailto:[login to unmask email]
Sent: Friday, April 7, 2017 8:25 AM
To: [login to unmask email]
Subject: [DB2-L] - Convert Oracle Stored procedure to Db2



Hi,

I need help to convert the below stored proc which has been written for Oracle to DB2.

CREATE OR replace PROCEDURE "employee"

( empid IN VARCHAR2,
rollondate IN DATE,
rolloffdate IN DATE,
drsidentifier IN VARCHAR2,
ref_cursor_getdept OUT SYS_REFCURSOR
) IS
v_cursor SYS_REFCURSOR;
BEGIN
OPEN v_cursor FOR
'SELECT DISTINCT NK.ADMIN_DEPT_ID ADMINDEPTID,CC.Prod_tp_cd AS LOB FROM WCC.DEPTCOMPONENT CC

INNER JOIN WCC.NATIVEKEY NK ON CC.DEP_COMPONENT_ID = NK.DEPT_ID AND CC.EXPIRY_DT >= SYSDATE and CC.Prod_tp_cd in (' || drsidentifier || ')

INNER JOIN WCC.DEPTROLE CR ON CR.DEP_COMPONENT_ID=CC.DEP_COMPONENT_ID and (CR.END_DT IS NULL OR CR.END_DT >= SYSDATE)

WHERE CR.CONT_ID = ' || empid || ' and ((CC.ISSUE_DT>=TO_DATE(''' || rollondate || ''') AND CC.ISSUE_DT<=TO_DATE(''' || rolloffdate || ''')) OR (CC.EXPIRY_DT>=TO_DATE(''' || rollondate || ''') AND CC.EXPIRY_DT<=TO_DATE(''' || rolloffdate || ''')) OR (CC.ISSUE_DT<=TO_DATE(''' || rollondate || ''') AND CC.EXPIRY_DT>=TO_DATE(''' || rolloffdate || ''')))

and NK.ADMIN_DEPT_ID is not null and CC.DEPT_ST_TP_CD=10050';
ref_cursor_getdept := v_cursor;
END;

Below is how i converted:-

CREATE OR replace PROCEDURE "employee"

( IN empid VARCHAR,
IN rollondate TIMESTAMP,
IN rolloffdate TIMESTAMP,
IN drsidentifier VARCHAR,
OUT ref_cursor_getdept SYS_REFCURSOR
) IS
v_cursor SYS_REFCURSOR;
BEGIN
OPEN v_cursor FOR
'SELECT DISTINCT NK.ADMIN_DEPT_ID ADMINDEPTID,CC.Prod_tp_cd AS LOB FROM WCC.DEPTCOMPONENT CC

INNER JOIN WCC.NATIVEKEY NK ON CC.DEP_COMPONENT_ID = NK.DEPT_ID AND CC.EXPIRY_DT >= SYSDATE and CC.Prod_tp_cd in (' || drsidentifier || ')

INNER JOIN WCC.DEPTROLE CR ON CR.DEP_COMPONENT_ID=CC.DEP_COMPONENT_ID and (CR.END_DT IS NULL OR CR.END_DT >= SYSDATE)

WHERE CR.CONT_ID = ' || empid || ' and ((CC.ISSUE_DT>=TO_DATE(''' || rollondate || ''') AND CC.ISSUE_DT<=TO_DATE(''' || rolloffdate || ''')) OR (CC.EXPIRY_DT>=TO_DATE(''' || rollondate || ''') AND CC.EXPIRY_DT<=TO_DATE(''' || rolloffdate || ''')) OR (CC.ISSUE_DT<=TO_DATE(''' || rollondate || ''') AND CC.EXPIRY_DT>=TO_DATE(''' || rolloffdate || ''')))

and NK.ADMIN_DEPT_ID is not null and CC.DEPT_ST_TP_CD=10050';
ref_cursor_getdept := v_cursor;
END;

Should i change the dynamically passed values in the query to "?"

Are there any helpful links with some sample stored procs using cursor, since i am a beginner?



-----End Original Message-----