DB2 FOR LUW / DB2FORLUW / 910 - DB2

When you reproduce database objects from one to another with a 
v9.1 db2look output, you will have an unexpected SQL0440N error 
and can not reproduce. 
 
Problem Reproducible steps: 
1.  logon as root 
2.  su - db2inst1  # instance owner 
3.  db2start 
4.  db2sampl 
5.  db2 terminate 
 
6.  Open anoter terminal for a regular user 
7.  su - hidehy # regular user 
8.  db2 connect to sample 
9.  db2 -td% -vf create_func.sql 
      ==> This will create DEPTEMPLOYEES() 
      ==> Please modify create_func.sql if instance name is not 
db2inst1 
10. db2 -td% -vf create_proc.sql 
      ==> This will create TESTPROC() 
11. db2look -d sample -a -e -l -x -i db2inst1 -w db2inst1 -o 
db2look.txt 
12. db2 terminate 
 
13. go back to db2inst1 terminal 
14. db2 drop db sample 
15. db2 create db sample 
 
16. go back to the hidehy regular user terminal 
17. db2 -tvf db2look.txt 
    ==> Can not make the TESTPROC due to SQL0440N.  This is the 
problem. 
    ==> The same above steps can be run on V9.7FP4 without any 
error. 
 
 
--- create_func.sql --- 
--DROP FUNCTION DEPTEMPLOYEES 
--% 
  CREATE FUNCTION DEPTEMPLOYEES (DEPTNO VARCHAR(3)) 
    RETURNS TABLE (EMPNO CHAR(6), 
                   LASTNAME VARCHAR(15), 
                   FIRSTNAME VARCHAR(12)) 
    LANGUAGE SQL 
    READS SQL DATA 
    NO EXTERNAL ACTION 
    DETERMINISTIC 
    RETURN 
      SELECT EMPNO, LASTNAME, FIRSTNME FROM db2inst1.EMPLOYEE 
        WHERE db2inst1.EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO 
% 
--- 
 
--- create_proc.sql --- 
--DROP PROCEDURE TESTPROC 
--% 
 
   CREATE PROCEDURE TESTPROC() 
     LANGUAGE SQL 
     RESULT SETS 1 
     BEGIN 
       DECLARE C1 CURSOR WITH RETURN FOR 
          SELECT EMPNO, LASTNAME, FIRSTNAME FROM 
TABLE(DEPTEMPLOYEES('A00')) AS D; 
       OPEN C1; 
   END 
% 
---

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 UDB Version 9.1.                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error description field for more information.            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.1 FixPack 11.                           * 
****************************************************************

N/A

DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
 DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows
 

Problem was first fixed in DB2 UDB Version 9.1 FixPack 11.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC78985 IC78986 
follow-up : 

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

27.09.2011
16.12.2011
16.12.2011

9.1.FP11