索引设计工具 db2advis使用

还是没有执行成功，谢谢大家了。以后再研究研究！收起

放到一个文件里面比如sql.sql db2advis -d dbname -i sql.sql收起

回复 15# eeenjoy

语法是正确的，SQL都发到一行，试试收起

回复 14# tianshizuoyi


   这样会报非法字符的错误，执行不成功:'(:'(:'(收起

试下在数据库服务器上直接运行     db2advis -d sino -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 "收起

回复 12# shadowflare


使用的数据库是8.2版本的，改成-q db2inst1，会报错。Using user id as default schema name. Use -n option to specify schema
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]
版本问题，也会有影响吧？收起

-[schema|n]      schema name that is used to define new MQTs
   [ -[qualifier|q] ]

用错了吧。收起

回复 10# eeenjoy


   -n db2inst1 改成 -q db2inst1收起

回复 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]

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

回复 8# eeenjoy

你是不是用客户端工具访问过这个数据库？先drop掉所有的ADVISE和EXPLAIN开头的表，再执行explain.ddl收起