|
Problem IC77423
|
Status: Closed |
UNEXPECTED SQLCODE MIGHT BE REPORTED AFTER REPLACING A DATA TYPE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
An unexpected sqlcode might be reported after replacing a user
defined data type. When the data type is replaced,
authorization
records are not recreated for the internal data type cast
functions. As a result, only users with DBADM authority will
have execute privilege on the cast functions. For users without
DBADM authority, subsequent explicit or implicit references to
the user defined type cast functions can result in an unexpected
error.
This is known to cause the following sqlcodes:
- SQL0901N with the reason message "no cast function"
- SQL0551N with the name of the cast function as the third token
in the message
Here is an example of both sqlcodes being reported.
create type arr1 as int array[10]
DB20000I The SQL command completed successfully.
create or replace type arr1 as int array[10]
DB20000I The SQL command completed successfully.
begin
declare v1 arr1;
set v1 = null;
end
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0901N The SQL statement failed because of a non-severe
system error.
Subsequent SQL statements can be processed. (Reason "no cast
function".)
LINE NUMBER=3. SQLSTATE=58004
begin
declare v1 arr1;
set v1 = arr1(array[1,2,3]);
end
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0551N "NEWTON" does not have the required authorization or
privilege to
perform operation "EXECUTE" on object "NEWTON.ARR1". LINE
NUMBER=3.
SQLSTATE=42501 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users of DB2 LUW version 9.7, starting in fix pack 1 *
* when the ability to replace a data type first appeared. *
****************************************************************
* PROBLEM DESCRIPTION: *
* An unexpected sqlcode might be reported after replacing a *
* user *
* defined data type. When the data type is replaced, *
* authorization *
* records are not recreated for the internal data type cast *
* *
* functions. As a result, only users with DBADM authority *
* will *
* have execute privilege on the cast functions. For users *
* without *
* DBADM authority, subsequent explicit or implicit references *
* to *
* the user defined type cast functions can result in an *
* unexpected *
* error. *
* *
* *
* *
* This is known to cause the following sqlcodes: *
* *
* - SQL0901N with the reason message "no cast function" *
* *
* - SQL0551N with the name of the cast function as the third *
* token in the message *
* *
* *
* *
* Here is an example of both sqlcodes being reported. *
* *
* *
* *
* create type arr1 as int array[10] *
* DB20000I The SQL command completed successfully. *
* *
* create or replace type arr1 as int array[10] *
* DB20000I The SQL command completed successfully. *
* *
* begin *
* declare v1 arr1; *
* set v1 = null; *
* end *
* DB21034E The command was processed as an SQL statement *
* because it was not a *
* valid Command Line Processor command. During SQL processing *
* it returned: *
* SQL0901N The SQL statement failed because of a non-severe *
* system error. *
* Subsequent SQL statements can be processed. (Reason "no *
* cast function".) *
* LINE NUMBER=3. SQLSTATE=58004 *
* *
* begin *
* declare v1 arr1; *
* set v1 = arr1(array[1,2,3]); *
* end *
* DB21034E The command was processed as an SQL statement *
* because it was not a *
* valid Command Line Processor command. During SQL processing *
* it returned: *
* SQL0551N "NEWTON" does not have the required authorization *
* or privilege to *
* perform operation "EXECUTE" on object "NEWTON.ARR1". LINE *
* NUMBER=3. *
* SQLSTATE=42501 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 LUW version 9.7 fix pack 5. *
**************************************************************** |
| Local Fix: |
Manually drop the data type and reissue the CREATE TYPE
statement. |
| available fix packs: |
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
|
| Solution |
|
| Workaround |
See LOCAL FIX |
| Comment |
Fix release: DB2 LUW version 9.7 fix pack 5
Fix commitment level: 999 |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.07.2011
08.12.2011
08.12.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
