IBM Support

Db2 v9.7: SQL0750N encountered when performing a table rename with existing index

Troubleshooting


Problem

After upgrade database from v9.5 to v9.7, it's not possible to rename table where index exists while table created in v9.7 could be renamed. A rename fails with SQL0750N.

Symptom



This is a scenario that causes the issue:

create database testdb;
                                                   
connect to testdb;    
                                                   
create table t1 (ID INTEGER, DESC VARGRAPHIC(100) NOT NULL, DESCU VARGRAPHIC(100) NOT NULL GENERATED ALWAYS AS (UCASE(DESC)) PRIMARY KEY);

rename t1 to t2;
--command above will generate SQL0750N under v9.5 and will not generate error under v9.7

connect reset;    
             
--under v9.5
backup database testdb;

--under v9.7
restore database testdb;  
                                         
rename t1 to t2;
--command above will generate SQL0750N for v9.5 upgraded to v9.7

Cause


It works as designed.
This is an enhancement in v9.7 to narrow down the blocking condition when executing RENAME statement. Specifically, in v9.7 and after, when creating a check constraint or generated column, if there is no table name reference in the expression, the flag NO TABLE REFERENCE will be recorded in the constraint descriptor. Later, when a table is renamed, if NO TABLE REFERENCE has been recorded, the renaming is allowed. Otherwise, SQL750N will be returned.
Since there is no such enhancement in v9.5, the check constraint or generated column created in v9.5 will not have this flag in the constraint descriptor. As a result, the database migrated from v9.5 to v9.7 will also not have this flag, and hence, SQL750N is returned.

Resolving The Problem


Since it works as designed as a workaround, after migrating to v9.7, you need to recreate the generated columns using ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION. For check constraint, the columns can be recreated using ALTER TABLE ... DROP CHECK / ALTER TABLE ... ADD CHECK... . The resulting constraint descriptor will have the NO TABLE REFERENCE flag. You will need to first put the table in check pending state, and then bring the table out of check pending after the ALTER.

Here are the commands to do that for generated columns:

set integrity for t1 off;
alter table t1 alter column DESCU set expression as (ucase(desc));  
set integrity for t1 immediate checked;                                                                                        
For check constraint:

set integrity for t1 off;
alter table t1 drop check <check_constraint_name>;
alter table t1 add constraint <check_constraint_name> check <check_text>;
set integrity for t1 immediate checked;

To see the list of check constraints with their corresponding text defined on a table, you can use the system catalog as follows (first 20 characters will be shown):

select substr(constname, 1, 20), substr(text, 1, 20) from syscat.checks where tabschema = upper('<schema_name>') and tabname = upper('<table_name>');

Related Information

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Tables","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21445314