MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

SQL -20523 / ADD VERSIONING to table

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany

PostPosted: Tue Jan 28, 2014 6:00 am    Post subject: SQL -20523 / ADD VERSIONING to table Reply with quote

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
--------+---------+---------+---------+---------+---------+---------+---------+




Any comments how to solve this topic ?


kind regards,
bauer
Back to top
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany

PostPosted: Tue Jan 28, 2014 8:08 am    Post subject: Reply with quote

Ok, after some additional manual reading und some tests:

Follow this steps:

1) DROP VIEWs (and materialized querys, Indexes with expressions)
2) ALTER TABLESPACE xxxx CCSID .....
3) CREATE VIEW, INDEXES ...... (see step (1))
4) GRANT ...... for VIEWs
5) Rebind packages (or automatic rebind)

finally ADD VERSIONING ......

I hope, this information is for anybody helpful in the future.

So, topic is closed (or any better solution available ?????).

kind regards
bauer
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Tue Jan 28, 2014 11:35 am    Post subject: Reply with quote

bauer wrote:
Ok, after some additional manual reading und some tests:

Follow this steps:

1) DROP VIEWs (and materialized querys, Indexes with expressions)
2) ALTER TABLESPACE xxxx CCSID .....
3) CREATE VIEW, INDEXES ...... (see step (1))
4) GRANT ...... for VIEWs
5) Rebind packages (or automatic rebind)

finally ADD VERSIONING ......

I hope, this information is for anybody helpful in the future.

So, topic is closed (or any better solution available ?????).

kind regards
bauer


I am not sure if you need steps 1,3,4,5. Can't you just change the CCSID on the table space and then add versioning?

Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany

PostPosted: Tue Jan 28, 2014 12:06 pm    Post subject: Reply with quote

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.

Have a nice day,

kind regards,
bauer
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Tue Jan 28, 2014 12:56 pm    Post subject: Reply with quote

bauer,

Thank you for the detailed explanation.

Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group