home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC67148 Status: Geschlossen

WHEN ROW CHANGE TIMESTAMP IS SPECIFIED FOR USER TEMPORARY TABLES AN ERROR
IS NOT RETURNED.

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
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-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* Users running DB2 for LUW version 9.7 GA through to Fix Pack * 
* 1, 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.
verfügbare FixPacks:
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
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 9a 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 10 for Linux, UNIX, and Windows

Lösung
Problem first fixed in DB2 Version 9.7 Fix Pack 2 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
keiner bekannt / siehe Local-Fix
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC67153 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
11.03.2010
08.04.2010
08.04.2010
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP2
Problem behoben lt. FixList in der Version
9.7.0.2 FixList