Joined: 10 Oct 2003 Posts: 315 Topics: 49 Location: Germany
Posted: Tue Jan 28, 2014 6:00 am Post subject: SQL -20523 / ADD VERSIONING to table
Hallo *,
I'm trying to add a versioning to a table.
Code:
--
ALTER TABLE My.BaseTable
ADD VERSIONING USE HISTORY TABLE My.HistoryTable
;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -20523, ERROR: TABLE My.HistoryTable WAS SPECIFIED AS A
HISTORY TABLE, BUT THE TABLE DEFINITION IS NOT VALID FOR A HISTORY
TABLE. REASON CODE = 10
DSNT418I SQLSTATE = 428HX SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXISB5 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 745 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000002E9' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Looking at the reason code and the DB2 catolog I can see:
My.BaseTable is in My.BaseTableTableSpace with SBCS_CCSID = 0 (SYSIBM.SYSTABLESPACE) and My.HistoryTableTableSpace has SBCS_CCSID=273.
My.BaseTable and My.BaseTableTableSpace was created with version 6, the history table space / table was created with version 10.
I tried to alter the CCSID of the BaseTableTableSpace:
Code:
ALTER TABLESPACE MyDataBase.BaseTableTableSpace CCSID 273 ;
--------+---------+---------+---------+---------+---------+---------+---------+
SNT408I SQLCODE = -20106, ERROR: THE CCSID FOR THE TABLE SPACE OR DATABASE
CANNOT BE CHANGED BECAUSE THE TABLE SPACE OR DATABASE ALREADY
CONTAINS A TABLE THAT IS REFERENCED IN EXISTING VIEW OR
MATERIALIZED QUERY TABLE DEFINITIONS OR AN EXTENDED INDEX
SNT418I SQLSTATE = 42945 SQLSTATE RETURN CODE
SNT415I SQLERRP = DSNXIATS SQL PROCEDURE DETECTING ERROR
SNT416I SQLERRD = 190 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
SNT416I SQLERRD = X'000000BE' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
--------+---------+---------+---------+---------+---------+---------+---------+
Joined: 10 Oct 2003 Posts: 315 Topics: 49 Location: Germany
Posted: Tue Jan 28, 2014 12:06 pm Post subject:
kolusu,
thank you for your replay. I tried this of course before my post. The result of the direct attempt to change the ccsid at TableSpace level is:
Code:
ALTER TABLESPACE MyDataBase.MyTableSpace CCSID 273 ;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -20106, ERROR: THE CCSID FOR THE TABLE SPACE OR DATABASE
CANNOT BE CHANGED BECAUSE THE TABLE SPACE OR DATABASE ALREADY
CONTAINS A TABLE THAT IS REFERENCED IN EXISTING VIEW OR
MATERIALIZED QUERY TABLE DEFINITIONS OR AN EXTENDED INDEX
DSNT418I SQLSTATE = 42945 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXIATS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 190 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000000BE' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE614I AUTOCOMMIT IS NO; NO CHANGES COMMITTED
My TableSpace contains one table, the table has some views, which are used in some packages.
The TableSpace and the Table was created about 10 years ago (at minimum), the CreateDate in SYSIBM.SYSTABLESPACE is not set, so should be something like DB2 version 6 during the creation process. The current version is 10.1.
And we are talking about of cource DB2 zOS.
The desired CCSID 273 is our system default. So not really anything to convert for DB2. Just in SYSIBM.SYSTABLESPACE the field SBCS_CCSID is not set.
Tha manual GC19-2971-05 "DB2 10 for z/OSCodes" is talking about this steps in the section for the SQLCODE 20106.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
