IT17148: ACCIDENTAL REMOVAL OF DAT FILE FROM SMS TABLESPACE COULD RESULT IN DUPLICATE TABLE IDS IN CATALOGS

DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• The following steps could reproduce this problem:
  
  db2 "create tablespace sms1 managed by system using
  ('/data2/bennyv/path1' ) "
  for i in 0 1 2 3 4 5 6 7 8 9
  do
  db2 "create table tab$i ( col1 int ) in sms1 "
  done
  db2stop force
  $ ls /data2/bennyv/path1
  SQL00002.DAT  SQL00003.DAT  SQL00004.DAT  SQL00005.DAT
  SQL00006.DAT
  SQL00007.DAT  SQL00008.DAT  SQL00009.DAT  SQL00010.DAT
  SQL00011.DAT
  SQLTAG.NAM
  $ rm /data2/bennyv/path1/SQL00004.DAT
  db2start
  db2 "create table tab11 ( col1 char, col2 int) in sms1 "
  
  $ db2 -tvf dup2.sql
  select ctime, substr(name,1,30) name, substr(creator,1,30)
  schema,fid,tid, colcount from sysibm.systables s1 where type =
  'T' and (select count(*) from sysibm.systables s2 where s1.fid =
  s2.fid and s1.tid = s2.tid ) > 1 order by fid,tid, ctime desc
  
  CTIME                      NAME                           SCHEMA
  FID    TID    COLCOUNT
  -------------------------- --------------------
  ---------------------- ------ ------ --------
  2015-03-05-06.34.09.907963 TAB11                          BENNYV
  4      5        2
  2015-03-05-06.32.49.984790 TAB2                           BENNYV
  4      5        1
  

Local fix

• Don't remove DAT file manually from SMS tablespace or move data
  to DMS tablespace.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 11.1 Mod 2 Fix Pack 2 or higher               *
  ****************************************************************
  

Problem conclusion

• First fixed in DB2 11.1 Mod 2 Fix Pack 2
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  IT17095

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels