DB2 FOR LUW / DB2FORLUW / 970 - DB2

Here is the test case : 
 
 
$cat proc_udf.sql 
 
set sqlcompat plsql@ 
 
CREATE OR REPLACE PROCEDURE PROC_GOTO_TEST ( 
  i_cond in number 
) IS 
 
   v_result varchar2(50); 
 
BEGIN 
 
    IF i_cond > 10 THEN 
        GOTO OK_CONDITION; 
    ELSE 
        dbms_output.put_line('Input should be greater than 10. 
(cond=' || i_cond || ')'); 
        RETURN; 
    END IF; 
 
<< OK_CONDITION >> 
    NULL; 
dbms_output.put_line('--'); 
 
    BEGIN 
        select 'Successful !!' RESULT into v_result from dual; 
        dbms_output.put_line(i_cond || ' => ' || v_result); 
 
    EXCEPTION 
        WHEN OTHERS THEN 
            dbms_output.put_line('ErrMsg => ' || SQLERRM); 
    END; 
 
END@ 
 
CREATE OR REPLACE FUNCTION FUNC_TEST 
( 
  p_arg1 in VARCHAR2 
, p_arg2 in VARCHAR2 
, p_arg3 in VARCHAR2) 
 
RETURN  VARCHAR2 
IS 
 
BEGIN 
    IF p_arg1 is null THEN 
        return p_arg3; 
    ELSE 
        return p_arg2; 
    END IF; 
 
END FUNC_TEST@ 
 
 
set sqlcompat db2@ 
 
 
- DB2_COMPATIBILITY_VECTOR=ORA 
- Create TEST database 
- Connect to TEST 
- db2 -td@ -vf proc_udf.sql 
- db2look -d test -e -l -mod -cor -nofed -o db2look.out 
 
This problem only occur in the DB2 V9.7 Fixpack 4. 
In prior fixpacks db2look can successfully generate the CREATE 
OR REPLACE FUNCTION and CREATE OR REPLACE PROCEDURE statements.

Here is the test case : 
 
 
$cat proc_udf.sql 
 
set sqlcompat plsql@ 
 
CREATE OR REPLACE PROCEDURE PROC_GOTO_TEST ( 
  i_cond in number 
) IS 
 
  v_result varchar2(50); 
 
BEGIN 
 
    IF i_cond > 10 THEN 
        GOTO OK_CONDITION; 
    ELSE 
        dbms_output.put_line('Input should be greater than 10. 
(cond=' || i_cond || ')'); 
        RETURN; 
    END IF; 
 
<< OK_CONDITION >> 
    NULL; 
dbms_output.put_line('--'); 
 
    BEGIN 
        select 'Successful !!' RESULT into v_result from dual; 
        dbms_output.put_line(i_cond || ' => ' || v_result); 
 
    EXCEPTION 
        WHEN OTHERS THEN 
            dbms_output.put_line('ErrMsg => ' || SQLERRM); 
    END; 
 
END@ 
 
CREATE OR REPLACE FUNCTION FUNC_TEST 
( 
  p_arg1 in VARCHAR2 
, p_arg2 in VARCHAR2 
, p_arg3 in VARCHAR2) 
 
RETURN  VARCHAR2 
IS 
 
BEGIN 
    IF p_arg1 is null THEN 
        return p_arg3; 
    ELSE 
        return p_arg2; 
    END IF; 
 
END FUNC_TEST@ 
 
 
set sqlcompat db2@ 
 
 
- DB2_COMPATIBILITY_VECTOR=ORA 
- Create TEST database 
- Connect to TEST 
- db2 -td@ -vf proc_udf.sql 
- db2look -d test -e -l -mod -cor -nofed -o db2look.out 
 
This problem only occur in the DB2 V9.7 Fixpack 4. 
In prior fixpacks db2look can successfully generate the CREATE 
OR REPLACE FUNCTION and CREATE OR REPLACE PROCEDURE statements.

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in DB2 Version 9.7 Fixpack 5.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

08.06.2011
07.12.2011
07.12.2011

9.7.FP5