IBM Support

SQL0750 renaming table created before v10.1

Troubleshooting


Problem

Attempting to rename a table originally created before v10.1 returns SQL0750N even though there are no table dependencies.

Symptom

There will be no table dependencies in DB2 system catalog to prevent the rename.

db2 "CALL DBMS_UTILITY.GET_DEPENDENCY('TABLE', 'db2inst1','mytable')"

But db2trc will show SQL0901 caused by SQL0750 due to constraint violation
154373     ||||||||||||||||sqlnn_erds data [probe 0]
   pid 123456 tid 9048 cpid 63242366 node 0 probe 0
   bytes 70
   CompID= 60, FcnId= 553, sqlCode= -750, loc= 150, numTokens= 0
154388     |||||||||||||||||sqlzeSqlCode exit [rc = 0x801A006D = -2145779603 = SQLZ_CA_BUILT]
154389     ||||||||||||||||sqlnn_erds exit [rc = 0x801A006D = -2145779603 = SQLZ_CA_BUILT]
154390     |||||||||||||||sqlnq_rename exit [rc = 0x80310025 = -2144272347 = SQLNN_E_BAD]
154391     |||||||||||||||sqlogmblkEx entry [eduid 9048 eduname db2agent]

Cause

Dependency checking is done via internal packed desciptor (PD) associated with each table. Changes made as of v10.1 so there is additional flag to indicate there are no dependencies. However legacy tables created before v10.1 do not have this new flag, thus there is false positive and DB2 will think there is dependency but actually there are no dependencies.

Diagnosing The Problem

Check the creation date of the table and check corresponding database history upgrades from SYSIBM.SYSVERSIONS to determine if the table was created before v10.1.
select create_time from syscat.tables where tabschema='db2inst1' and tabname='mytable';
select * from SYSIBM.SYSVERSIONS;

Resolving The Problem

Drop and re-create the table. When new table is created, the packed descriptor will be in new format.

Document Location

Worldwide

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.1;10.5;11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
18 April 2019

UID

ibm10881770