Drop column, and alter nullability is not allowed on any table with data capture on.

DBFinder · #1 (**permalink**) 06-07-09, 08:49

Hi everybody,

DB2 V 9.5 Fixpack 4 on Win 2K3

While ALTERing ACC_NUM on many tables in POS databbase, 7 tables were not Altered because of Following Error

Quote:

Alter Table ACC_DEPOSIT Alter Column ACC_NUM Set Data Type Varchar(20)
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0270N Function not supported (Reason code = "100"). SQLSTATE=42997

The reason code "100" expands as

Quote:

100
Drop column, and alter nullability is not allowed on any table with data capture on.

How can I work around, better if I can avoid using EXPORT-DROP-CREATE-IMPORT approach because these tables are huge and this approach will take too long time. (offline)

Please help with your ideas, I won't be able to perform this without testing it.

Thanks
DBFinder

Peter.Vanroose · #2 (**permalink**) 06-07-09, 10:36

Are you sure you didn't use

Code:

ALTER TABLE ... ALTER COLUMN ... SET NOT NULL

or

Code:

ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL

? Since these seem to be the only situations where you would receive -290 with reason code 100, at least in DB2 version 9.5 for LUW.
Just changing the datatype should not give this error.

DBFinder · #3 (**permalink**) 06-07-09, 11:05

Quote:

Alter Table ACC_DEPOSIT Alter Column ACC_NUM Set Data Type Varchar(20)

I used above statement as seed to build the script for 117 tables.
Only table name is substituted.

Please note that error is -270 not -290.

Also the tables where ALTER COLUMN failed had been created with DATA CAPTURE CHANGES clause.

Thanks
DBFinder

Peter.Vanroose · #4 (**permalink**) 06-07-09, 11:10

What was the old datatype? Was it wider than 20 bytes?

DBFinder · #5 (**permalink**) 06-07-09, 11:11

Older was CHARACTER[12]

DBFinder · #6 (**permalink**) 06-07-09, 11:14

Can we turn DATA CAPTURE off for this to work and turn it back on after

DBFinder · #7 (**permalink**) 06-07-09, 11:49

Peter Vanroose,

I have found a way.

Set data capture to NONE
ALTER TABLE to change data type to VARCHAR(20)
Set data capture to CHANGES.

This seems to work, will you please advise me if this will affect any thing else.
I mean side affect.

Thanks,

DBFinder

Peter.Vanroose · #8 (**permalink**) 06-07-09, 12:03

This would indeed be safe *unless* you plan to use that table for inter-database replication, and table changes are to be captured from the logs. And apparently you do, since otherwise DATA CAPTURE would not have been set to CHANGES on this table.
Actually, data changes are still captured in the logs, but just for this ALTER TABLE that info could be insufficient.
No idea whether this will cause problems for replication, though; I've never used it myself.

Internet Services

Web Development

Digital Marketing

Consumer Tech