Technical Blog Post
Abstract
How to drop the table when APAR IC87147 is hit.
Body
If you hit the APAR IC87147 in v9.7fp7 and below, you should contact DB2 Support to fix this issue.
This issue can be reproduced easily like as follows but this could be quite annoying issue if the table is critical in business.
-------------------------------------
(woongc@hotel81) /home/woongc/07461.000.766
$ ./tst.ksh
+ db2 connect to tstv97
Database Connection Information
Database server = DB2/LINUXX8664 9.7.4
SQL authorization ID = WOONGC
Local database alias = TSTV97
+ db2 $'create table errtable (\naa int,\nbb varchar(100),\nadddate timestamp with default sysibm.timestamp(current timestamp)\n)'
DB20000I The SQL command completed successfully.
+ db2 $'alter table errtable alter column adddate set data type\nvarchar(30)'
DB20000I The SQL command completed successfully.
+ db2 'select * from errtable'
SQL0901N The SQL statement failed because of a non-severe system error.
Subsequent SQL statements can be processed. (Reason "bad Default".)
SQLSTATE=58004
+ db2 terminate
DB20000I The TERMINATE command completed successfully.
-------------------------------------
Even with db2dart /MT or db2cat, we cannot resolve this issue. We need an external tool from area expert team to access this table again.
For this, the following data should be collected first.
1. The List of Tables with the issue
2. Result of the following query.
: db2 "select varchar(NAME,10), varchar(TBNAME,10), varchar(TBCREATOR,10), varchar(COLTYPE,10) , varchar(DEFAULT,50)
from sysibm.syscolumns
where tbname = '<table_name>'"
-------------------------------------
e.g.
1 2 3 4 5
---------- ---------- ---------- ---------- --------------------------------------------------
AA ERRTABLE DB2I97 INTEGER -
ADDDATE ERRTABLE DB2I97 VARCHAR "SYSIBM"."TIMESTAMP"(CURRENT TIMESTAMP)
BB ERRTABLE DB2I97 VARCHAR -
-------------------------------------
3. ixf export of sysibm.syscolumns.
After providing this data to DB2 Support, you will get the external tool from DB2 Support with the following guide.
(In PMR 07461.000.766, the tool name was rtc61474)
1. Recommend to backup the database in case. This backups the database pd on ERRTABLE.
db2cat -d <dbname> -s DB2I97 -n ERRTABLE -f DB2I97.ERRTABLE.bkp -o DB2I97.ERRTABLE.bkp.out -l
2. Run the tool: ./rtc61474 <dbname>
3. Set service password on. (i.e. service password can be obtained via db2svcpw and set to the env variable $DB2SVCPW via export DB2SVCPW=xxxx).
4. Use db2cat -g and db2cat -r to regenerate a new table pd with null default column and then replace table pd in the catalog with the generated version
db2cat -d <dbname> -s DB2I97 -n ERRTABLE -g generatepd.bin -o db2cat.out1
db2cat -d <dbname> -s DB2I97 -n ERRTABLE -r generatepd.bin -o db2cat.out1
The table should be accessible now. Please export the table data if there is any, at this point. Drop and recreate the table with the required definitions.
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
UID
ibm13286509