- 论坛徽章:
- 0
|
How to improve the performance when doing update???
below are my stored procedure, log for snapshot, db cfg, and dbm cfg. Please advise
CREATE PROCEDURE DB2INST.update_yearly_position ( )
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
declare v_data_src varchar(2);
declare v_eal_typ varchar(2);
declare v_eal_cat varchar(5);
declare v_prof_id varchar(20);
declare v_bric varchar(20);
declare v_rpt_yr integer;
declare v_rpt_qtr integer;
declare v_yr integer;
declare v_qtr integer;
declare v_cur_cde varchar(3);
declare v_ass_liab_ind varchar(1);
declare v_exchg_rate decimal(19,6);
declare v_prev_cls_pos_fc decimal(19,2);
declare v_prev_cls_pos_rm decimal(19,2);
declare v_prev_conv_cls_rm decimal(19,2);
declare v_opn_pos_fc decimal(19,2);
declare v_opn_pos_rm decimal(19,2);
declare v_convert_opn_rm decimal(19,2);
declare v_res_status varchar(10);
declare v_res_status_desc varchar(50);
declare v_close_pos_fc decimal(19,2);
declare v_close_pos_rm decimal(19,2);
declare v_convert_close_rm decimal(19,2);
declare v_val_mthd varchar(1);
declare v_u_risk_t_flg varchar(1);
declare v_stat_flg varchar(1);
declare v_err_msg varchar(100);
declare v_del_flg varchar(1);
declare v_tran_flg varchar(1);
declare v_ver_no integer;
declare v_ope_cde varchar(1);
declare v_crt_tms varchar(19);
declare v_upd_tms varchar(19);
declare v_crt_uid varchar(50);
declare v_upd_uid varchar(50);
declare at_end int default 0;
declare not_found condition for sqlstate '02000';
declare continue handler for not_found set at_end = 1;
declare continue handler for sqlexception, sqlwarning
set at_end = 0;
begin
declare c0 cursor for
select distinct rpt_yr
from yearly_position
for fetch only;
open c0;
fetch c0 into v_yr;
while (at_end = 0) do
begin
declare c1 cursor for
select data_src, eal_typ, eal_cat, prof_id, bric, rpt_yr, rpt_qtr
from yearly_position
where rpt_yr = v_yr
for update;
open c1;
fetch c1 into v_data_src, v_eal_typ, v_eal_cat, v_prof_id, v_bric, v_rpt_yr, v_rpt_qtr;
while (at_end = 0) do
--get opn bal
begin
declare c2 cursor for
select rpt_qtr, opn_pos_fc, opn_pos_rm, convert_opn_rm
from quarterly_position
where prof_id = v_prof_id and bric = v_bric and rpt_yr = v_rpt_yr and
data_typ = 'EAL' and data_src = v_data_src and eal_typ = v_eal_typ and eal_cat = v_eal_cat and
not del_flg = 'Y'
order by rpt_qtr asc
fetch first 1 rows only
optimize for 1 rows
for fetch only;
/*
using index:
===========
CREATE INDEX WIZ287 ON "DB2INST "."quarterly_position" (" ROF_ID" DESC, "BRIC" DESC) ;
CREATE INDEX w999 ON "DB2INST "."quarterly_position" (DATA_TYP, DATA_SRC, EAL_TYP, EAL_CAT, PROF_ID, BRIC, RPT_YR, DEL_FLG) ;
*/
open c2;
fetch c2 into v_qtr, v_opn_pos_fc, v_opn_pos_rm, v_convert_opn_rm;
close c2;
set at_end = 0;
end;
--get prev closing bal
begin
declare c2 cursor for
select close_pos_fc, close_pos_rm, convert_close_rm
from quarterly_position
where prof_id = v_prof_id and bric = v_bric and rpt_yr = v_rpt_yr - 1 and
data_typ = 'EAL' and data_src = v_data_src and eal_typ = v_eal_typ and eal_cat = v_eal_cat and
not del_flg = 'Y'
order by rpt_qtr desc
fetch first 1 rows only
optimize for 1 rows
for fetch only;
/*
using index:
===========
CREATE INDEX WIZ287 ON "DB2INST "."quarterly_position" ("ROF_ID" DESC, "BRIC" DESC) ;
CREATE INDEX w999 ON "DB2INST "."quarterly_position" (DATA_TYP, DATA_SRC, EAL_TYP, EAL_CAT, PROF_ID, BRIC, RPT_YR, DEL_FLG) ;
*/
open c2;
fetch c2 into v_prev_cls_pos_fc, v_prev_cls_pos_rm, v_prev_conv_cls_rm;
close c2;
set at_end = 0;
end;
--get exchg_rate
begin
declare c2 cursor for
select a.cur_cde, a.ass_liab_ind, b.exchg_rate
from profile a
left outer join exchange_rate b on b.cur_cde = a.cur_cde and b.year = v_rpt_yr and
b.quarter = 4
where prof_id = v_prof_id and bric = v_bric and data_typ = 'EAL' and data_src = v_data_src and eal_typ = v_eal_typ and eal_cat = v_eal_cat and not a.del_flg = 'Y'
fetch first 1 rows only
optimize for 1 rows
for fetch only;
/*
using index:
===========
CREATE INDEX W2 ON "DB2INST "."profile" (PROF_ID, BRIC, DATA_TYP, DATA_SRC, EAL_TYP, EAL_CAT) ;
*/
open c2;
fetch c2 into v_cur_cde, v_ass_liab_ind, v_exchg_rate;
close c2;
set at_end = 0;
end;
--get res_status, res_status_desc
begin
declare c2 cursor for
select a.cat_id, b.desc
from resident_cde a
inner join resident_desc b on b.cat_id = a.cat_id
where a.bric = v_bric and a.year = v_rpt_yr and a.quarter = 4
fetch first 1 rows only
optimize for 1 rows
for fetch only;
/*
using index:
===========
CREATE INDEX WIZ1198 ON "DB2INST "."resident_cde" ("QUARTER" ASC, "YEAR" ASC, "BRIC" ASC, "CAT_ID" ASC) ;
CREATE INDEX WIZ1263 ON "DB2INST "."ITPSTDJPPACO" ("CAT_ID" ASC, "DESC" DESC) ;
CREATE INDEX w9765 ON "DB2INST "."resident_cde" (BRIC, YEAR, QUARTER) ;
CREATE INDEX WIZ1263 ON "DB2INST "."ITPSTDJPPACO" (CAT_ID) ;
CREATE INDEX WIZ1263 ON "DB2INST "."resident_cde" (CAT_ID) ;
*/
open c2;
fetch c2 into v_res_status, v_res_status_desc;
close c2;
set at_end = 0;
end;
--get closing detail + bal
begin
declare c2 cursor for
select close_pos_fc, close_pos_rm, val_mthd, u_risk_t_flg, stat_flg, err_msg, del_flg, tran_flg, ver_no, ope_cde, crt_tms, upd_tms, crt_uid, upd_uid
from quarterly_position
where prof_id = v_prof_id and bric = v_bric and rpt_yr = v_rpt_yr and rpt_qtr = v_rpt_qtr and
data_typ = 'EAL' and data_src = v_data_src and eal_typ = v_eal_typ and eal_cat = v_eal_cat and
not del_flg = 'Y'
fetch first 1 rows only
optimize for 1 rows
for fetch only;
/*
CREATE INDEX w999 ON "DB2INST "."quarterly_position" (DATA_TYP, DATA_SRC, EAL_TYP, EAL_CAT, PROF_ID, BRIC, RPT_YR, RPT_QTR, DEL_FLG) ;
*/
open c2;
fetch c2 into v_close_pos_fc, v_close_pos_rm, v_val_mthd, v_u_risk_t_flg, v_stat_flg, v_err_msg, v_del_flg, v_tran_flg, v_ver_no, v_ope_cde, v_crt_tms, v_upd_tms, v_crt_uid, v_upd_uid;
close c2;
set at_end = 0;
end;
if (v_eal_cat = 'CICAR') then
begin
set v_convert_close_rm = v_close_pos_rm;
end;
elseif (v_eal_cat = 'PO') then
begin
if (v_ass_liab_ind = 'A') then
begin
set v_convert_close_rm = v_close_pos_rm;
end;
else
begin
set v_convert_close_rm = round(v_close_pos_fc * case when v_cur_cde = 'MYR' then 1 else coalesce(v_exchg_rate,0.00) end,2);
end;
end if;
end;
else
begin
set v_convert_close_rm = round(v_close_pos_fc * case when v_cur_cde = 'MYR' then 1 else coalesce(v_exchg_rate,0.00) end,2);
end;
end if;
-- not yearly data (NB CUSTODIAN, NBEAL) and not QNBEAL and CICAR
if not (v_data_src = 'NB' and v_eal_typ in ('EI','CU') and v_eal_cat = '') and not v_eal_cat in ('ED','CICAR') then
begin
if (v_qtr = 1) then
begin
update yearly_position
set prev_cls_pos_fc = coalesce(v_prev_cls_pos_fc,0.00),
prev_cls_pos_rm = coalesce(v_prev_cls_pos_rm,0.00),
prev_conv_cls_rm = coalesce(v_prev_conv_cls_rm,0.00),
opn_pos_fc = coalesce(v_opn_pos_fc,0.00),
opn_pos_rm = coalesce(v_opn_pos_rm,0.00),
convert_opn_rm = coalesce(v_convert_opn_rm,0.00),
res_status = coalesce(v_res_status,''),
res_status_desc = coalesce(v_res_status_desc,''),
close_pos_fc = coalesce(v_close_pos_fc,0.00),
close_pos_rm = coalesce(v_close_pos_rm,0.00),
convert_close_rm = coalesce(v_convert_close_rm,0.00),
val_mthd = coalesce(v_val_mthd,''),
u_risk_t_flg = coalesce(v_u_risk_t_flg,''),
stat_flg = coalesce(v_stat_flg,''),
err_msg = coalesce(v_err_msg,''),
del_flg = coalesce(v_del_flg,''),
tran_flg = coalesce(v_tran_flg,''),
ver_no = coalesce(v_ver_no,0),
ope_cde = coalesce(v_ope_cde,''),
crt_tms = coalesce(v_crt_tms,''),
upd_tms = coalesce(v_upd_tms,''),
crt_uid = coalesce(v_crt_uid,''),
upd_uid = coalesce(v_upd_uid,'')
where data_typ = 'EAL' and data_src = v_data_src and eal_typ = v_eal_typ and eal_cat = v_eal_cat and
prof_id = v_prof_id and bric = v_bric and rpt_yr = v_rpt_yr and rpt_qtr = v_rpt_qtr;
end;
else
begin
update yearly_position
set prev_cls_pos_fc = coalesce(v_prev_cls_pos_fc,0.00),
prev_cls_pos_rm = coalesce(v_prev_cls_pos_rm,0.00),
prev_conv_cls_rm = coalesce(v_prev_conv_cls_rm,0.00),
opn_pos_fc = 0.00,
opn_pos_rm = 0.00,
convert_opn_rm = 0.00,
res_status = coalesce(v_res_status,''),
res_status_desc = coalesce(v_res_status_desc,''),
close_pos_fc = coalesce(v_close_pos_fc,0.00),
close_pos_rm = coalesce(v_close_pos_rm,0.00),
convert_close_rm = coalesce(v_convert_close_rm,0.00),
val_mthd = coalesce(v_val_mthd,''),
u_risk_t_flg = coalesce(v_u_risk_t_flg,''),
stat_flg = coalesce(v_stat_flg,''),
err_msg = coalesce(v_err_msg,''),
del_flg = coalesce(v_del_flg,''),
tran_flg = coalesce(v_tran_flg,''),
ver_no = coalesce(v_ver_no,0),
ope_cde = coalesce(v_ope_cde,''),
crt_tms = coalesce(v_crt_tms,''),
upd_tms = coalesce(v_upd_tms,''),
crt_uid = coalesce(v_crt_uid,''),
upd_uid = coalesce(v_upd_uid,'')
where data_typ = 'EAL' and data_src = v_data_src and eal_typ = v_eal_typ and eal_cat = v_eal_cat and
prof_id = v_prof_id and bric = v_bric and rpt_yr = v_rpt_yr and rpt_qtr = v_rpt_qtr;
end;
end if;
end;
--QNBEAL
elseif not (v_data_src = 'NB' and v_eal_typ in ('EI','CU') and v_eal_cat = '') and v_eal_cat = 'ED' and not v_eal_cat = 'CICAR' then
begin
if (v_qtr = 1) then
begin
update yearly_position
set prev_cls_pos_fc = coalesce(v_prev_cls_pos_fc,0.00),
prev_cls_pos_rm = coalesce(v_prev_cls_pos_rm,0.00),
prev_conv_cls_rm = coalesce(v_prev_conv_cls_rm,0.00),
opn_pos_fc = coalesce(v_opn_pos_fc,0.00),
opn_pos_rm = coalesce(v_opn_pos_rm,0.00),
convert_opn_rm = coalesce(v_convert_opn_rm,0.00),
res_status = coalesce(v_res_status,''),
res_status_desc = coalesce(v_res_status_desc,''),
close_pos_fc = coalesce(v_close_pos_fc,0.00),
close_pos_rm = coalesce(v_close_pos_rm,0.00),
convert_close_rm = coalesce(v_convert_close_rm,0.00),
val_mthd = coalesce(v_val_mthd,''),
u_risk_t_flg = coalesce(v_u_risk_t_flg,''),
stat_flg = coalesce(v_stat_flg,''),
err_msg = coalesce(v_err_msg,''),
del_flg = coalesce(v_del_flg,''),
tran_flg = coalesce(v_tran_flg,''),
ver_no = coalesce(v_ver_no,0),
ope_cde = coalesce(v_ope_cde,''),
crt_tms = coalesce(v_crt_tms,''),
upd_tms = coalesce(v_upd_tms,''),
crt_uid = coalesce(v_crt_uid,''),
upd_uid = coalesce(v_upd_uid,'')
where data_typ = 'EAL' and data_src = v_data_src and eal_typ = v_eal_typ and eal_cat = v_eal_cat and
prof_id = v_prof_id and bric = v_bric and rpt_yr = v_rpt_yr and rpt_qtr = v_rpt_qtr;
end;
else
begin
update yearly_position
set prev_cls_pos_fc = coalesce(v_prev_cls_pos_fc,0.00),
prev_cls_pos_rm = coalesce(v_prev_cls_pos_rm,0.00),
prev_conv_cls_rm = coalesce(v_prev_conv_cls_rm,0.00),
opn_pos_fc = 0.00,
opn_pos_rm = 0.00,
convert_opn_rm = 0.00,
trx_inc_fc = case when v_ass_liab_ind = 'A' then trx_inc_fc + coalesce(v_opn_pos_fc,0.00) else trx_inc_fc end,
trx_inc_rm = case when v_ass_liab_ind = 'A' then trx_inc_rm + coalesce(v_opn_pos_rm,0.00) else trx_inc_rm end,
convert_trx_inc_rm = case when v_ass_liab_ind = 'A' then convert_trx_inc_rm + coalesce(v_convert_opn_rm,0.00) else convert_trx_inc_rm end,
trx_dec_fc = case when v_ass_liab_ind = 'L' then trx_dec_fc + coalesce(v_opn_pos_fc,0.00) else trx_dec_fc end,
trx_dec_rm = case when v_ass_liab_ind = 'L' then trx_dec_rm + coalesce(v_opn_pos_rm,0.00) else trx_dec_rm end,
convert_trx_dec_rm = case when v_ass_liab_ind = 'L' then convert_trx_dec_rm + coalesce(v_convert_opn_rm,0.00) else convert_trx_dec_rm end,
res_status = coalesce(v_res_status,''),
res_status_desc = coalesce(v_res_status_desc,''),
close_pos_fc = coalesce(v_close_pos_fc,0.00),
close_pos_rm = coalesce(v_close_pos_rm,0.00),
convert_close_rm = coalesce(v_convert_close_rm,0.00),
val_mthd = coalesce(v_val_mthd,''),
u_risk_t_flg = coalesce(v_u_risk_t_flg,''),
stat_flg = coalesce(v_stat_flg,''),
err_msg = coalesce(v_err_msg,''),
del_flg = coalesce(v_del_flg,''),
tran_flg = coalesce(v_tran_flg,''),
ver_no = coalesce(v_ver_no,0),
ope_cde = coalesce(v_ope_cde,''),
crt_tms = coalesce(v_crt_tms,''),
upd_tms = coalesce(v_upd_tms,''),
crt_uid = coalesce(v_crt_uid,''),
upd_uid = coalesce(v_upd_uid,'')
where data_typ = 'EAL' and data_src = v_data_src and eal_typ = v_eal_typ and eal_cat = v_eal_cat and
prof_id = v_prof_id and bric = v_bric and rpt_yr = v_rpt_yr and rpt_qtr = v_rpt_qtr;
end;
end if;
end;
--CICAR
elseif not (v_data_src = 'NB' and v_eal_typ in ('EI','CU') and v_eal_cat = '') and not v_eal_cat = 'ED' and v_eal_cat = 'CICAR' then
begin
if (v_qtr = 1) then
begin
update yearly_position
set prev_cls_pos_fc = coalesce(v_prev_cls_pos_fc,0.00),
prev_cls_pos_rm = coalesce(v_prev_cls_pos_rm,0.00),
prev_conv_cls_rm = coalesce(v_prev_conv_cls_rm,0.00),
opn_pos_fc = coalesce(v_opn_pos_fc,0.00),
opn_pos_rm = coalesce(v_opn_pos_rm,0.00),
convert_opn_rm = coalesce(v_convert_opn_rm,0.00),
trx_dec_fc = coalesce(v_opn_pos_fc,0.00) - coalesce(v_close_pos_fc,0.00),
trx_dec_rm = coalesce(v_opn_pos_rm,0.00) - coalesce(v_close_pos_rm,0.00),
convert_trx_dec_rm = coalesce(v_convert_opn_rm,0.00) - coalesce(v_convert_close_rm,0.00),
res_status = coalesce(v_res_status,''),
res_status_desc = coalesce(v_res_status_desc,''),
close_pos_fc = coalesce(v_close_pos_fc,0.00),
close_pos_rm = coalesce(v_close_pos_rm,0.00),
convert_close_rm = coalesce(v_convert_close_rm,0.00),
val_mthd = coalesce(v_val_mthd,''),
u_risk_t_flg = coalesce(v_u_risk_t_flg,''),
stat_flg = coalesce(v_stat_flg,''),
err_msg = coalesce(v_err_msg,''),
del_flg = coalesce(v_del_flg,''),
tran_flg = coalesce(v_tran_flg,''),
ver_no = coalesce(v_ver_no,0),
ope_cde = coalesce(v_ope_cde,''),
crt_tms = coalesce(v_crt_tms,''),
upd_tms = coalesce(v_upd_tms,''),
crt_uid = coalesce(v_crt_uid,''),
upd_uid = coalesce(v_upd_uid,'')
where data_typ = 'EAL' and data_src = v_data_src and eal_typ = v_eal_typ and eal_cat = v_eal_cat and
prof_id = v_prof_id and bric = v_bric and rpt_yr = v_rpt_yr and rpt_qtr = v_rpt_qtr;
end;
else
begin
update yearly_position
set prev_cls_pos_fc = coalesce(v_prev_cls_pos_fc,0.00),
prev_cls_pos_rm = coalesce(v_prev_cls_pos_rm,0.00),
prev_conv_cls_rm = coalesce(v_prev_conv_cls_rm,0.00),
opn_pos_fc = 0.00,
opn_pos_rm = 0.00,
convert_opn_rm = 0.00,
trx_dec_fc = 0.00 - coalesce(v_close_pos_fc,0.00),
trx_dec_rm = 0.00 - coalesce(v_close_pos_rm,0.00),
convert_trx_dec_rm = 0.00 - coalesce(v_convert_close_rm,0.00),
res_status = coalesce(v_res_status,''),
res_status_desc = coalesce(v_res_status_desc,''),
close_pos_fc = coalesce(v_close_pos_fc,0.00),
close_pos_rm = coalesce(v_close_pos_rm,0.00),
convert_close_rm = coalesce(v_convert_close_rm,0.00),
val_mthd = coalesce(v_val_mthd,''),
u_risk_t_flg = coalesce(v_u_risk_t_flg,''),
stat_flg = coalesce(v_stat_flg,''),
err_msg = coalesce(v_err_msg,''),
del_flg = coalesce(v_del_flg,''),
tran_flg = coalesce(v_tran_flg,''),
ver_no = coalesce(v_ver_no,0),
ope_cde = coalesce(v_ope_cde,''),
crt_tms = coalesce(v_crt_tms,''),
upd_tms = coalesce(v_upd_tms,''),
crt_uid = coalesce(v_crt_uid,''),
upd_uid = coalesce(v_upd_uid,'')
where data_typ = 'EAL' and data_src = v_data_src and eal_typ = v_eal_typ and eal_cat = v_eal_cat and
prof_id = v_prof_id and bric = v_bric and rpt_yr = v_rpt_yr and rpt_qtr = v_rpt_qtr;
end;
end if;
end;
else
--YEARLY DATA
begin
update yearly_position
set prev_cls_pos_fc = coalesce(v_prev_cls_pos_fc,0.00),
prev_cls_pos_rm = coalesce(v_prev_cls_pos_rm,0.00),
prev_conv_cls_rm = coalesce(v_prev_conv_cls_rm,0.00),
res_status = coalesce(v_res_status,''),
res_status_desc = coalesce(v_res_status_desc,''),
close_pos_fc = coalesce(v_close_pos_fc,0.00),
close_pos_rm = coalesce(v_close_pos_rm,0.00),
convert_close_rm = coalesce(v_convert_close_rm,0.00),
val_mthd = coalesce(v_val_mthd,''),
u_risk_t_flg = coalesce(v_u_risk_t_flg,''),
stat_flg = coalesce(v_stat_flg,''),
err_msg = coalesce(v_err_msg,''),
del_flg = coalesce(v_del_flg,''),
tran_flg = coalesce(v_tran_flg,''),
ver_no = coalesce(v_ver_no,0),
ope_cde = coalesce(v_ope_cde,''),
crt_tms = coalesce(v_crt_tms,''),
upd_tms = coalesce(v_upd_tms,''),
crt_uid = coalesce(v_crt_uid,''),
upd_uid = coalesce(v_upd_uid,'')
where data_typ = 'EAL' and data_src = v_data_src and eal_typ = v_eal_typ and eal_cat = v_eal_cat and
prof_id = v_prof_id and bric = v_bric and rpt_yr = v_rpt_yr and rpt_qtr = v_rpt_qtr;
end;
end if;
--commit;
fetch c1 into v_data_src, v_eal_typ, v_eal_cat, v_prof_id, v_bric, v_rpt_yr, v_rpt_qtr;
end while;
close c1;
set at_end = 0;
fetch c0 into v_yr;
end;
end while;
close c0;
end;
END P1
Application Snapshot
Application handle = 41
Application status = UOW Executing
Status change time = 03-07-2005 12:46:43.567478
Application code page = 819
Application country code = 1
DUOW correlation token = *LOCAL.db2inst.050306235925
Application name = db2bp
Application ID = *LOCAL.db2inst.050306235947
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =
Sequence number = 0001
Connection request start timestamp = 03-07-2005 07:59:47.499063
Connect request completion timestamp = 03-07-2005 07:59:47.512672
Application idle time =
Authorization ID = DB2INST
Client login ID = db2inst
Configuration NNAME of client =
Client database manager product ID = SQL07029
Process ID of client application = 27196
Platform of client application = AIX
Communication protocol of client = Local Client
Inbound communication address = *LOCAL.db2inst
Database name = HRMS
Database path = /disc3/ITP/db2inst/NODE0000/SQL00001/
Client database alias = iteps
Input database alias =
Last reset timestamp =
Snapshot timestamp = 03-07-2005 12:46:43.703997
The highest authority level granted =
Direct DBADM authority
Direct CREATETAB authority
Direct BINDADD authority
Direct CONNECT authority
Direct CREATE_NOT_FENC authority
Direct LOAD authority
Direct IMPLICIT_SCHEMA authority
Indirect SYSADM authority
Indirect CREATETAB authority
Indirect BINDADD authority
Indirect CONNECT authority
Indirect IMPLICIT_SCHEMA authority
Coordinating node number = 0
Current node number = 0
Coordinator agent process or thread ID = 29690
Agents stolen = 0
Agents waiting on locks = 0
Maximum associated agents = 1
Priority at which application agents work = 0
Priority type = Dynamic
Locks held by application = 8
Lock waits since connect = 2
Time application waited on locks (ms) = 425530
Deadlocks detected = 0
Lock escalations = 1
Exclusive lock escalations = 1
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = 0
Total sorts = 2
Total sort time (ms) = 0
Total sort overflows = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Buffer pool data logical reads = 1113025
Buffer pool data physical reads = 86973
Buffer pool data writes = 1354
Buffer pool index logical reads = 9784122
Buffer pool index physical reads = 24494
Buffer pool index writes = 4325
Total buffer pool read time (ms) = 668405
Total buffer pool write time (ms) = 319956
Time waited for prefetch (ms) = 5130
Direct reads = 620
Direct writes = 0
Direct read requests = 51
Direct write requests = 0
Direct reads elapsed time (ms) = 486
Direct write elapsed time (ms) = 0
Number of SQL requests since last commit = 2691714
Commit statements = 0
Rollback statements = 0
Dynamic SQL statements attempted = 12
Static SQL statements attempted = 2691702
Failed statement operations = 0
Select SQL statements executed = 1726720
Update/Insert/Delete statements executed = 192960
DDL statements executed = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 0
Rows inserted = 0
Rows updated = 192960
Rows selected = 1823914
Rows read = 965128
Rows written = 192960
UOW log space used (Bytes) = 201367348
Previous UOW completion timestamp = 03-07-2005 07:59:47.512672
Elapsed time of last completed uow (sec.ms)= 0.000000
UOW start timestamp = 03-07-2005 07:59:47.541240
UOW stop timestamp =
UOW completion status =
Open remote cursors = 0
Open remote cursors with blocking = 0
Rejected Block Remote Cursor requests = 0
Accepted Block Remote Cursor requests = 771851
Open local cursors = 3
Open local cursors with blocking = 3
Total User CPU Time used by agent (s) = 6661.590000
Total System CPU Time used by agent (s) = 60.200000
Host execution elapsed time = 0.000019
Package cache lookups = 1919680
Package cache inserts = 49
Application section lookups = 2691714
Application section inserts = 27
Catalog cache lookups = 3
Catalog cache inserts = 3
Catalog cache overflows = 0
Catalog cache heap full = 0
Most recent operation = Fetch
Cursor name = CURS21
Most recent operation start timestamp = 03-07-2005 12:46:43.567480
Most recent operation stop timestamp =
Agents associated with the application = 1
Number of hash joins = 0
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
Statement type = Static SQL Statement
Statement = Fetch
Section number = 5
Application creator = DB2INST
Package name = P1521980
Cursor name = CURS21
Statement node number = 0
Statement start timestamp = 03-07-2005 12:46:43.567480
Statement stop timestamp =
Elapsed time of last completed stmt(sec.ms)= 0.000019
Total user CPU time = 0.020000
Total system CPU time = 0.000000
SQL compiler cost estimate in timerons = 157
SQL compiler cardinality estimate = 1
Degree of parallelism requested = 1
Number of agents working on statement = 1
Number of subagents created for statement = 1
Statement sorts = 0
Total sort time = 0
Sort overflows = 0
Rows read = 0
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Blocking cursor = NO
Agent process/thread ID = 29690
Statement type = Static SQL Statement
Statement = Fetch
Section number = 3
Application creator = DB2INST
Package name = P1521980
Cursor name = CURS10
Statement node number = 0
Statement start timestamp = 03-07-2005 12:20:52.179157
Statement stop timestamp = 03-07-2005 12:45:41.341607
Elapsed time of last completed stmt(sec.ms)= 0.053950
Total user CPU time = 0.210000
Total system CPU time = 0.070000
SQL compiler cost estimate in timerons = 13057
SQL compiler cardinality estimate = 45284
Degree of parallelism requested = 1
Number of agents working on statement = 0
Number of subagents created for statement = 1
Statement sorts = 0
Total sort time = 0
Sort overflows = 0
Rows read = 0
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 13070
Blocking cursor = NO
Statement type = Static SQL Statement
Statement = Fetch
Section number = 2
Application creator = DB2INST
Package name = P1521980
Cursor name = CURS5
Statement node number = 0
Statement start timestamp = 03-07-2005 07:59:47.785153
Statement stop timestamp = 03-07-2005 07:59:48.130788
Elapsed time of last completed stmt(sec.ms)= 0.297730
Total user CPU time = 0.250000
Total system CPU time = 0.000000
SQL compiler cost estimate in timerons = 1488
SQL compiler cardinality estimate = 4
Degree of parallelism requested = 1
Number of agents working on statement = 0
Number of subagents created for statement = 1
Statement sorts = 0
Total sort time = 0
Sort overflows = 0
Rows read = 2
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 4
Blocking cursor = NO
Agent process/thread ID = 29690
Database Configuration for Database HRMS
Database configuration release level = 0x0900
Database release level = 0x0900
Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country code = 1
Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
Directory object name (DIR_OBJ_NAME) =
Discovery support for this database (DISCOVER_DB) = ENABLE
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
Backup pending = NO
Database is consistent = NO
Rollforward pending = NO
Restore pending = NO
Multi-page file allocation enabled = NO
Log retain for recovery status = NO
User exit for logging status = NO
Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0
Database heap (4KB) (DBHEAP) = 20000
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 64
Log buffer size (4KB) (LOGBUFSZ) = 4096
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 10000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 6000
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 4096
Sort list heap (4KB) & |
|
免费注册
发表于 2005-03-06 16:08
发表于 2005-03-09 09:10