产品:IBM CM 8.4,DB2 9.5
环境是:windows server2003
在CM底层的Log日志中,删除数据ddo时,出现以下错误:
com.ibm.mm.sdk.common.DKUsageError: DGL3616A: 发生意外的 SQL 错误; ICM7015: 在库服务器的 SQL 操作期间,发生意外错误。有关错误的详细信息,请参阅数据库文档。 (STATE) : [LS RC = 7015, SQL RC = 100
解决过程:
1、进入系统管理客户机中,将库服务器的日志级别改为完全,系统运行一段时间,改回日志级别为错误。
2、查看ICMSERVER.LOG文件,查找有误的数据ID,并执行文件中报出异常的的SQL语句,如下面两条:
SELECT T.* FROM ( SELECT DISTINCT dt_plot_doc_LOB_1.ITEMID, dt_plot_doc_LOB_1.COMPONENTID, dt_plot_doc_LOB_1.VERSIONID, 1672 AS COMPONENTTYPEID, 1669 AS ITEMTYPEID FROM ICMUT01672001 dt_plot_doc_LOB_1 WHERE (dt_plot_doc_LOB_1.ATTR0000001488 = 'A1001001A12F22B70421F28020') AND (((( (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C , ICMSTITVIEWDEFS V WHERE 1669=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1669) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' ))))))) T OPTIMIZE FOR 500 ROWS FOR READ ONLY WITH UR
select * from icmstitems001001 where itemid='A1001001A12F22B71819G24455'
一般会发现,在上面的SQL语句中会找到下面itemId的记录,而下面的SQL语句一般是没有记录的,也就说明该数据库的记录呈现出不一致的状态。
3、一般情况下,可以直接调用sql语句,将这多余的记录删除掉,如执行指令:
delete from icmstitems001001 where itemid='A1001001A12F22B71819G24455'
但是为了确保准确性,我们还需要进行一些验证性的操作,执行以下10条sql语句,其中表名 icmut01672001中的数字1672 是通过以下的SQL语句
SELECT T.* FROM ( SELECT DISTINCT dt_plot_doc_LOB_1.ITEMID, dt_plot_doc_LOB_1.COMPONENTID, dt_plot_doc_LOB_1.VERSIONID, 1672 AS COMPONENTTYPEID, 1669 AS ITEMTYPEID FROM ICMUT01672001 dt_plot_doc_LOB_1 WHERE (dt_plot_doc_LOB_1.ATTR0000001488 = 'A1001001A12F22B70421F28020') AND (((( (EXISTS (SELECT 1 FROM ICMSTCOMPILEDACL C , ICMSTITVIEWDEFS V WHERE 1669=V.ITEMTYPEID AND V.ITEMTYPEVIEWID IN (1669) AND (C.ACL = V.ACLCODE OR C.ACL = -1) AND C.UNUM=2 AND C.RPRIV='1' ))))))) T OPTIMIZE FOR 500 ROWS FOR READ ONLY WITH UR
结果中的匹配itemId的那条记录中的COMPONENTTYPEID中的值获得,若值是1679,则表名是icmut01679001:
1) select * from icmut01672001 where itemid='A1001001A12F22B71819G24455'
2) select * from icmstitems001001 where itemid='A1001001A12F22B71819G24455'
3) select * from icmstitemver001001 where itemid='A1001001A12F22B71819G24455'
4) select * from icmstreplicas where itemid='A1001001A12F22B71819G24455'
5) select * from icmut00204001 where RTARGETITEMID='A1001001A12F22B71819G24455'
6) select * from icmstitemstodelete where itemid='A1001001A12F22B71819G24455'
7) select * from icmstlinks001001 where TARGETITEMID='A1001001A12F22B71819G24455'
or SOURCEITEMID='A1001001A12F22B71819G24455'
8) select * from ICMSTRI001001 where TARGETITEMID='A1001001A12F22B71819G24455'
or SOURCEITEMID='A1001001A12F22B71819G24455'
9) select * from icmstcheckedout where itemid='A1001001A12F22B71819G24455'
10) find component id of reference child first, then query the reference child component table
a) SELECT A.COMPONENTTYPEID
FROM ICMSTCOMPDEFS A, ICMSTCOMPVIEWDEFS B
WHERE B.COMPONENTVIEWID = 1672 AND
B.COMPONENTTYPEID = A.PARENTCOMPTYPEID AND
COMPTYPECLASS = 2;
b) select * from icmut0xxxx001 where itemid='A1001001A12F22B71819G24455'
-------------> xxxx is the componenttypeid obtained from 10 a)
如果以上结果只有第一条返回记录,其余的都为0的话,则可以直接删除该条记录,当然为了确保安全,在删除之前最好备份数据库。
4、查看该表是否只有一条数据处于不稳定的状态,通过以下SQL:
select * from ICMUT01672001 ut where not exists (select 1 from icmstitems001001 I
where I.itemid= ut.itemid);
若返回记录数过多的话,则得查看引起该问题的原因,若不多的话,则直接删除。