|
Problem IC67153
|
Status: Closed |
WHEN ROW CHANGE TIMESTAMP IS SPECIFIED FOR USER TEMPORARY TABLES AN ERROR IS NOT RETURNED. |
| product: |
DB2 FOR LUW / DB2FORLUW / 980 - DB2 |
| Problem description: |
ROW CHANGE TIMESTAMP columns and ROW CHANGE TOKEN expressions
are not supported on user temporary tables.
When created global temporary tables are created with a row
change timestamp column it should return SQLSTATE 429BV but
instead it incorrectly completes successfully. Declared global
temporary tables do not have this issue and return the expected
error.
For example, the following DDL statement should fail, yet it
does not.
db2 "create global temporary table cgtt(c1 int, rowchangets
TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS
ROW CHANGE TIMESTAMP)"
DB20000I The SQL command completed successfully.
Once the created global temporary table is created successfully
and an INSERT statement inserts a row into this table, INSERT
will return SQL0901N as ROW CHANGE TIMESTAMP columns are not
supported on created global temporary tables.
db2 "insert into cgtt(c1) values 1, DEFAULT"
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 "Timestamp
requested but
not a perm table or partition".) SQLSTATE=58004
The ROW CHANGE TOKEN expression is also not supported on user
temporary tables. When ROW CHANGE TOKEN is used on the user
temporary tables it returns 0. It does not return SQLSTATE
42703.
db2 "declare global temporary table session.dgtt(c1 int, ts
TIMESTAMP)"
DB20000I The SQL command completed successfully.
db2 "select row change token for session.dgtt from session.dgtt"
1
--------------------
0
1 record(s) selected. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users running DB2 for LUW version 9.8 GA through to Fix Pack *
* 2, using ROW CHANGE TIMESTAMP column on user temporary *
* tables. *
****************************************************************
* PROBLEM DESCRIPTION: *
* The SQL0901N error while inserting into created global *
* temporary tables results from not blocking the usage of ROW *
* CHANGE TIMESTAMP column when defining the table. The *
* database manager is missing logic to block the creation of *
* created global temporary tables with these column types. *
* *
* *
* ROW CHANGE TIMESTAMP column and ROW CHANGE TOKEN expression *
* are not supported on user temporary tables. *
* *
* When a created global temporary table is created with row *
* change timestamp column it should return an SQLSTATE 429BV *
* but instead it completes successfully. *
* *
* db2 "create global temporary table cgtt(c1 int, rowchagets *
* TIMESTAMP NOT NU11 GENERATED ALWAYS FOR EACH ROW ON UPDATE *
* AS ROW CHANGE TIMESTAMP)" *
* DB20000I The SQL command completed successfully. *
* *
* Once the created global temporary table is created *
* successfully and INSERT statement inserts a row into this *
* table, INSERT will return SQL0901N as we do not support ROW *
* CHANGE TIMESTAMP column on created global temporary table. *
* *
* db2 "insert into cgtt(c1) values 1, DEFAULT" *
* 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 *
* "Timestamp requested but *
* not a perm table or partition".) SQLSTATE=58004 *
* *
* *
* The ROW CHANGE TOKEN expression is also not supported on the *
* user temporary table. When ROW CHANGE TOKEN is used on the *
* user temporary tables it returns 0. It does not return *
* SQLSTATE 42703. *
* *
* *
* db2 "declare global temporary table session.dgtt(c1 int, ts *
* TIMESTAMP)" *
* DB20000I The SQL command completed successfully. *
* *
* db2 "select row change token for session.dgtt from *
* session.dgtt" *
* *
* 1 *
* -------------------- *
* 0 *
* *
* 1 record(s) selected. *
****************************************************************
* RECOMMENDATION: *
* Do not use ROW CHANGE TIMESTAMP column or ROW CHANGE *
* TIMESTAMP (TOKEN) expression on the user temporary tables. *
**************************************************************** |
| Local Fix: |
Do not use row change timestamp columns or expressions with user
temporary tables. |
| available fix packs: |
DB2 Version 9.8 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.8 Fix Pack 4 for AIX and Linux
DB2 Version 9.8 Fix Pack 5 for AIX and Linux
|
| Solution |
Problem first fixed in DB2 Version 9.8 Fix Pack 3 and all
subsequent Fix Packs.
When a user temporary table is created with a ROW CHANGE
TIMESTAMP column an sqlcode SQL20354N with sqlstate 429BV is
returned.
When a ROW CHANGE TOKEN expression is used for user temporary
tables an sqlcode SQL1389N with sqlstate 42703 is returned. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 11.03.2010
20.12.2010
20.12.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.8.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.8.0.3
|
 |
