索引设计工具 db2advis使用

回复 9# shadowflare

drop掉所有的ADVISE和EXPLAIN开头的表，再执行explain.ddl，然后执行 【db2advis -d sino -n db2inst1  -m I -s "SELECT cckdnm , qj  , zzlx  , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm   FROM xt_wlxx    WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN '210000' ELSE wlnm END AS wlnm , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm FROM xt_wlxx WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN '...' ELSE wlmc END AS wlmc  , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm FROM xt_wlxx  WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN wlnm ELSE xpznm END AS xpznm  , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm  FROM xt_wlxx  WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN wlmc ELSE xpzmc END AS xpzmc  , nx  , sum (qmye) AS zycblkc , 0.0 AS jkzckc  , 0.0 AS splkc FROM db2inst1.ZF_TJ_SJKC JOIN (SELECT JHKDNM AS AJHKDNM  , MAX (ND || QJ) AS NDQJ  FROM ZF_TJ_SJKC  WHERE ND || QJ >= '201503' AND ND || QJ <= '201505'  GROUP BY JHKDNM) Y ON ND || QJ = NDQJ AND JHKDNM = AJHKDNM WHERE KCXZNM LIKE '0170102%' AND ND || QJ >= '201503' AND ND || QJ <= '201505' GROUP BY cckdnm, zzlx, wlnm, wlmc, xpznm, xpzmc, nx, nd, qj" -a sino_zgs/123456】，还是有错误，如下：

db2inst1@linux-b49v:/opt/IBM/db2/V8.1/misc> db2advis -d sino -n db2inst1  -m I -s "SELECT cckdnm , qj  , zzlx  , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm   FROM xt_wlxx    WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN '210000' ELSE wlnm END AS wlnm , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm FROM xt_wlxx WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN '...' ELSE wlmc END AS wlmc  , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm FROM xt_wlxx  WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN wlnm ELSE xpznm END AS xpznm  , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm  FROM xt_wlxx  WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN wlmc ELSE xpzmc END AS xpzmc  , nx  , sum (qmye) AS zycblkc , 0.0 AS jkzckc  , 0.0 AS splkc FROM db2inst1.ZF_TJ_SJKC JOIN (SELECT JHKDNM AS AJHKDNM  , MAX (ND || QJ) AS NDQJ  FROM ZF_TJ_SJKC  WHERE ND || QJ >= '201503' AND ND || QJ <= '201505'  GROUP BY JHKDNM) Y ON ND || QJ = NDQJ AND JHKDNM = AJHKDNM WHERE KCXZNM LIKE '0170102%' AND ND || QJ >= '201503' AND ND || QJ <= '201505' GROUP BY cckdnm, zzlx, wlnm, wlmc, xpznm, xpzmc, nx, nd, qj" -a sino_zgs/123456
Explain tables not set up properly for schema DB2INST1
The insert into the ADVISE_INSTANCE table has failed.


0 solutions were evaluated by the advisor


exiting with error code [-219]
db2inst1@linux-b49v:/opt/IBM/db2/V8.1/misc> db2advis -d sino -n sino_zgs  -m I -s "SELECT cckdnm , qj  , zzlx  , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm   FROM xt_wlxx    WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN '210000' ELSE wlnm END AS wlnm , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm FROM xt_wlxx WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN '...' ELSE wlmc END AS wlmc  , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm FROM xt_wlxx  WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN wlnm ELSE xpznm END AS xpznm  , CASE WHEN wlnm IN (SELECT xt_wlxx_wlnm  FROM xt_wlxx  WHERE xt_wlxx_wllbnm = '3000001' AND xt_wlxx_wlnm NOT IN ('200001', '200002', '200003', '200004')) THEN wlmc ELSE xpzmc END AS xpzmc  , nx  , sum (qmye) AS zycblkc , 0.0 AS jkzckc  , 0.0 AS splkc FROM db2inst1.ZF_TJ_SJKC JOIN (SELECT JHKDNM AS AJHKDNM  , MAX (ND || QJ) AS NDQJ  FROM ZF_TJ_SJKC  WHERE ND || QJ >= '201503' AND ND || QJ <= '201505'  GROUP BY JHKDNM) Y ON ND || QJ = NDQJ AND JHKDNM = AJHKDNM WHERE KCXZNM LIKE '0170102%' AND ND || QJ >= '201503' AND ND || QJ <= '201505' GROUP BY cckdnm, zzlx, wlnm, wlmc, xpznm, xpzmc, nx, nd, qj" -a sino_zgs/123456
Explain tables not set up properly for schema DB2INST1
The insert into the ADVISE_INSTANCE table has failed.


0 solutions were evaluated by the advisor


exiting with error code [-219]

请问这种情况，应该如何处理，谢谢！