| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC62112 | Status: Closed |
ADMIN_COPY_SCHEMA() MAY FAIL TO COPY SOME FUNCTIONS THAT DEPEND ON PROCEDURES | |
| product: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
| Problem description: | |
ADMIN_COPY_SCHEMA() may fail to copy some functions that depend
on procedures:
Customer's repro script before calling ADMIN_COPY_SCHEMA():
set current schema example@
set current path example@
CREATE TABLE TRADE_ENTITY_PROPERTY ("TRADE_ENTITY_ID" BIGINT
NOTNULL, "NAME" VARCHAR(100) NOT NULL,
"VALUE" VARCHAR(200) NOT NULL )
@
CREATE PROCEDURE GET_PROP (IN ID BIGINT, IN PNAME VARCHAR(100)
,OUT out_val VARCHAR(100))LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGINDECLARE c1 CURSOR FOR select tep.value from
trade_entity_property tep where tep.TRADE_ENTITY_ID=ID and
tep.name=PNAME;DECLARE EXIT HANDLER FOR NOT FOUND SET out_val =
NULL;OPEN c1;
set out_val = null;
FETCH c1 INTO out_val;
CLOSE c1;
RETURN;
END
@
CREATE FUNCTION PROP (ID BIGINT, PNAME VARCHAR(100)) RETURNS
VARCHAR(100)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTIONBEGIN ATOMICDECLARE out_val VARCHAR(100);CALL
GET_PROP(ID, PNAME, out_val);
RETURN out_val;
END
@
CREATE FUNCTION PROPATTR (ID BIGINT, PNAME VARCHAR(100))
RETURNSVARCHAR(100)
LANGUAGE SQL
READS SQL DATANO EXTERNAL ACTIONBEGIN ATOMICDECLARE out_val
VARCHAR(100);
CALL GET_PROP(ID, PNAME, out_val);
IF out_val IS NOT NULL THEN
set out_val = PNAME || '="' || out_val || '" ';ELSE set
out_val = '';END IF;RETURN out_val;
END
@
========
Both of the functions PROP and PROPATTR depend on the procedure
GET_PROP. When customer run ADMIN_COPY_SCHEMA(), the
functions PROP and PROPATTR are not copied to the new schema,
but they should be.
Command:
$ db2 "call SYSPROC.ADMIN_COPY_SCHEMA('EXAMPLE', 'CLONE',
'DDL','MSDB2', NULL, NULL, 'MSDB2', 'CLONE_SCHEMA_ERRORS')"
Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : MSDB2
Parameter Name : ERRORTABNAME
Parameter Value : CLONE_SCHEMA_ERRORS
Return Status = 0
The
table has the following error message logged in it:
[IBM][CLI Driver][DB2/LINUX] SQL0440N No authorized routine
named "GET_PROP" of type "PROCEDURE" having compatible
argumentswas foun
d. LINE NUMBER=3. SQLSTATE=42884
The TRADE_ENTITY_PROPERTY table was copied
successfully (but not the functions)
:
Table/View Schema Type
Creation time
------------------------------- --------------- -----
--------------------------
TRADE_ENTITY_PROPERTY CLONE T
2009-06-10-08.43.39.934874
TRADE_ENTITY_PROPERTY EXAMPLE T
2009-06-10-08.41.24.979527
CLONE_SCHEMA_ERRORS MSDB2 T
2009-06-10-08.43.36.378247 | |
| Problem Summary: | |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* ADMIN_COPY_SCHEMA() may fail to copy some functions that *
* depend *
* on procedures: *
* *
* *
* *
* Customer's repro script before calling ADMIN_COPY_SCHEMA(): *
* *
* *
* *
* set current schema example@ *
* *
* set current path example@ *
* *
* *
* *
* CREATE TABLE TRADE_ENTITY_PROPERTY ("TRADE_ENTITY_ID" BIGINT *
* *
* NOTNULL, "NAME" VARCHAR(100) NOT NULL, *
* *
* "VALUE" VARCHAR(200) NOT NULL ) *
* *
* @ *
* *
* *
* *
* CREATE PROCEDURE GET_PROP (IN ID BIGINT, IN PNAME *
* VARCHAR(100) *
* ,OUT out_val VARCHAR(100))LANGUAGE SQL *
* *
* READS SQL DATA *
* *
* NO EXTERNAL ACTION *
* *
* BEGINDECLARE c1 CURSOR FOR select tep.value from *
* *
* trade_entity_property tep where tep.TRADE_ENTITY_ID=ID and *
* *
* tep.name=PNAME;DECLARE EXIT HANDLER FOR NOT FOUND SET *
* out_val = *
* NULL;OPEN c1; *
* *
* set out_val = null; *
* *
* FETCH c1 INTO out_val; *
* *
* CLOSE c1; *
* *
* RETURN; *
* *
* END *
* *
* @ *
* *
* *
* *
* CREATE FUNCTION PROP (ID BIGINT, PNAME VARCHAR(100)) RETURNS *
* *
* VARCHAR(100) *
* *
* LANGUAGE SQL *
* *
* READS SQL DATA *
* *
* NO EXTERNAL ACTIONBEGIN ATOMICDECLARE out_val *
* VARCHAR(100);CALL *
* GET_PROP(ID, PNAME, out_val); *
* *
* RETURN out_val; *
* *
* END *
* *
* @ *
* *
* *
* *
* CREATE FUNCTION PROPATTR (ID BIGINT, PNAME VARCHAR(100)) *
* *
* RETURNSVARCHAR(100) *
* *
* LANGUAGE SQL *
* *
* READS SQL DATANO EXTERNAL ACTIONBEGIN ATOMICDECLARE *
* out_val *
* VARCHAR(100); *
* *
* CALL GET_PROP(ID, PNAME, out_val); *
* *
* IF out_val IS NOT NULL THEN *
* *
* set out_val = PNAME || '="' || out_val || '" ';ELSE *
* set *
* out_val = '';END IF;RETURN out_val; *
* *
* END *
* *
* @ *
* *
* *
* *
* ======== *
* *
* *
* *
* Both of the functions PROP and PROPATTR depend on the *
* procedure *
* GET_PROP. When customer run ADMIN_COPY_SCHEMA(), the *
* *
* functions PROP and PROPATTR are not copied to the new *
* schema, *
* but they should be. *
* *
* *
* *
* Command: *
* *
* $ db2 "call SYSPROC.ADMIN_COPY_SCHEMA('EXAMPLE', 'CLONE', *
* *
* 'DDL','MSDB2', NULL, NULL, 'MSDB2', 'CLONE_SCHEMA_ERRORS')" *
* *
* *
* *
* Value of output parameters *
* *
* -------------------------- *
* *
* Parameter Name : ERRORTABSCHEMA *
* *
* Parameter Value : MSDB2 *
* *
* *
* *
* Parameter Name : ERRORTABNAME *
* *
* Parameter Value : CLONE_SCHEMA_ERRORS *
* *
* *
* *
* Return Status = 0 *
* *
* The *
* *
* table has the following error message logged in it: *
* *
* [IBM][CLI Driver][DB2/LINUX] SQL0440N No authorized routine *
* *
* named "GET_PROP" of type "PROCEDURE" having compatible *
* *
* argumentswas foun *
* *
* d. LINE NUMBER=3. SQLSTATE=42884 *
* *
* *
* *
* The TRADE_ENTITY_PROPERTY table was copied *
* *
* successfully (but not the functions) *
* *
* : *
* *
* Table/View Schema Type *
* *
* Creation time *
* *
* ------------------------------- --------------- ----- *
* *
* -------------------------- *
* *
* TRADE_ENTITY_PROPERTY CLONE T *
* *
* 2009-06-10-08.43.39.934874 *
* *
* TRADE_ENTITY_PROPERTY EXAMPLE T *
* *
* 2009-06-10-08.41.24.979527 *
* *
* CLONE_SCHEMA_ERRORS MSDB2 T *
* *
* 2009-06-10-08.43.36.378247 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 1 *
**************************************************************** | |
| Local Fix: | |
use db2look to generate the DDL for the source schema, and manually change the schema to target schema and apply using CLP. | |
| available fix packs: | |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows | |
| Solution | |
Problem was first fixed in Version 9.7 Fix Pack 1 | |
| Workaround | |
see Local Fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 20.07.2009 19.02.2010 19.02.2010 |
| Problem solved at the following versions (IBM BugInfos) | |
9.7.FP1 | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 9.7.0.1 |
|