格式化了一下 刚才那个实在是太难看了
-- 导入导出 过程
[root@localhost ~]# date
2011年 07月 05日 星期二 08:56:18 CST
[root@localhost ~]# db2look -d suzhou -i l_sz_v15 -w afcapp -e -z l_sz_v15 -o /home/db2inst1/dbbackup/l_sz_v15_test.sql
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: ROOT
-- Specified SCHEMA is: L_SZ_V15
-- Creating DDL for table(s)
-- Schema name is ignored for the Federated Section
-- Output is sent to file: /home/db2inst1/dbbackup/l_sz_v15_test.sql
[root@localhost ~]# date
2011年 07月 05日 星期二 08:57:52 CST
--导出前
------------------------------ 输入的命令 ------------------------------
select current_timestamp from sysibm.dual;
select
num_executions as "总的执行次数",
LAST_METRICS_UPDATE,
STMT_TEXT,
(POOL_DATA_L_READS+POOL_TEMP_DATA_L_READS+POOL_XDA_L_READS+POOL_TEMP_XDA_L_READS+POOL_INDEX_L_READS+POOL_TEMP_INDEX_L_READS)/num_executions as "逻辑读",
(POOL_DATA_P_READS+POOL_TEMP_DATA_P_READS+POOL_XDA_P_READS+POOL_TEMP_XDA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_INDEX_P_READS)/num_executions as "物理读"
from TABLE (MON_GET_PKG_CACHE_STMT(null, null, null, -1)) AS tf
where num_executions>0
and stmt_text LIKE '%DECLARE THIS_TABLE_ALIAS CURSOR FOR%'
and stmt_text not like '%MON_GET_PKG_CACHE_STMT%'
order by QUERY_COST_ESTIMATE/num_executions desc;
select current_timestamp from sysibm.dual;
------------------------------------------------------------------------------
select current_timestamp from sysibm.dual
1
--------------------------
2011-07-05-08.56.10.231731
1 条记录已选择。
select num_executions as "总的执行次数", LAST_METRICS_UPDATE, STMT_TEXT, (POOL_DATA_L_READS+POOL_TEMP_DATA_L_READS+POOL_XDA_L_READS+POOL_TEMP_XDA_L_READS+POOL_INDEX_L_READS+POOL_TEMP_INDEX_L_READS)/num_executions as "逻辑读", (POOL_DATA_P_READS+POOL_TEMP_DATA_P_READS+POOL_XDA_P_READS+POOL_TEMP_XDA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_INDEX_P_READS)/num_executions as "物理读" from TABLE (MON_GET_PKG_CACHE_STMT(null, null, null, -1)) AS tf where num_executions>0 and stmt_text LIKE '%DECLARE THIS_TABLE_ALIAS CURSOR FOR%' and stmt_text not like '%MON_GET_PKG_CACHE_STMT%' order by QUERY_COST_ESTIMATE/num_executions desc
总的执行次数 LAST_METRICS_UPDATE
13020 2011-07-05-08.54.32.638919
STMT_TEXT
DECLARE THIS_TABLE_ALIAS CURSOR FOR
with iv (tabname, tabschema, base_tabname, base_tabschema, remarks,
create_time, definer) as
((SELECT tabname, tabschema, base_tabname, base_tabschema, remarks,
create_time, definer
FROM SYSCAT.TABLES WHERE type = 'A' and
base_tabname = :H00005 :H00004
and base_tabschema = :H00007 :H00006 )
union all
(select st.tabname, st.tabschema, st.base_tabname, st.base_tabschema, st.remarks,
st.create_time, st.definer from iv, SYSCAT.TABLES st
where iv.tabname = st.base_tabname and iv.tabschema = st.base_tabschema and
st.type = 'A')) select tabname, tabschema, base_tabname, base_tabschema, remarks, create_time, definer
from iv ORDER BY tabschema, tabname FOR READ ONLY
逻辑读 物理读
10103 0
1 条记录已选择。
select current_timestamp from sysibm.dual
1
--------------------------
2011-07-05-08.56.10.571164
1 条记录已选择。
--导出后
------------------------------ 输入的命令 ------------------------------
select current_timestamp from sysibm.dual;
select
num_executions as "总的执行次数",
LAST_METRICS_UPDATE,
STMT_TEXT,
(POOL_DATA_L_READS+POOL_TEMP_DATA_L_READS+POOL_XDA_L_READS+POOL_TEMP_XDA_L_READS+POOL_INDEX_L_READS+POOL_TEMP_INDEX_L_READS)/num_executions as "逻辑读",
(POOL_DATA_P_READS+POOL_TEMP_DATA_P_READS+POOL_XDA_P_READS+POOL_TEMP_XDA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_INDEX_P_READS)/num_executions as "物理读"
from TABLE (MON_GET_PKG_CACHE_STMT(null, null, null, -1)) AS tf
where num_executions>0
and stmt_text LIKE '%DECLARE THIS_TABLE_ALIAS CURSOR FOR%'
and stmt_text not like '%MON_GET_PKG_CACHE_STMT%'
order by QUERY_COST_ESTIMATE/num_executions desc;
select current_timestamp from sysibm.dual;
------------------------------------------------------------------------------
select current_timestamp from sysibm.dual
1
--------------------------
2011-07-05-08.57.59.949177
1 条记录已选择。
select num_executions as "总的执行次数", LAST_METRICS_UPDATE, STMT_TEXT, (POOL_DATA_L_READS+POOL_TEMP_DATA_L_READS+POOL_XDA_L_READS+POOL_TEMP_XDA_L_READS+POOL_INDEX_L_READS+POOL_TEMP_INDEX_L_READS)/num_executions as "逻辑读", (POOL_DATA_P_READS+POOL_TEMP_DATA_P_READS+POOL_XDA_P_READS+POOL_TEMP_XDA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_INDEX_P_READS)/num_executions as "物理读" from TABLE (MON_GET_PKG_CACHE_STMT(null, null, null, -1)) AS tf where num_executions>0 and stmt_text LIKE '%DECLARE THIS_TABLE_ALIAS CURSOR FOR%' and stmt_text not like '%MON_GET_PKG_CACHE_STMT%' order by QUERY_COST_ESTIMATE/num_executions desc
总的执行次数 LAST_METRICS_UPDATE
14667 2011-07-05-08.57.47.128307
STMT_TEXT
DECLARE THIS_TABLE_ALIAS CURSOR FOR
with iv (tabname, tabschema, base_tabname, base_tabschema, remarks, c
reate_time, definer) as ((SELECT tabname, tabschema, base_tabname, base_tabschema, remarks,
create_time, definer FROM SYSCAT.TABLES WHERE type = 'A' and
base_tabname = :H00005 :H00004 and
base_tabschema = :H00007 :H00006 )
union all (select st.tabname, st.tabschema, st.base_tabname, st.base_tabschema, st.remarks,
st.create_time, st.definer from iv, SYSCAT.TABLES st
where iv.tabname = st.base_tabname and
iv.tabschema = st.base_tabschema and
st.type = 'A'))
select tabname, tabschema, base_tabname, base_tabschema, remarks, create_time, definer
from iv ORDER BY tabschema, tabname FOR READ ONLY
逻辑读 物理读
10103 0
1 条记录已选择。
select current_timestamp from sysibm.dual
1
--------------------------
2011-07-05-08.58.00.533372
1 条记录已选择。
14667-13020=1647 次
select count(*) from syscat.tables where tabschema='L_SZ_V15' and type='T'
1
-----------
1645
1 条记录已选择。
1645 张表 执行了 1647次 ,所有 我怀疑 导出一张表,就执行一次这个sql
收起