db2相关语句

--创建过程并执行
CREATE or replace PROCEDURE test_hzjkqsyss (
in v_start smallint default 0,
in v_end smallint default 0
)
LANGUAGE SQL
p1:begin

DECLARE idx smallint;
DECLARE sql varchar(1000);
set idx = v_start;

fetch_loop:
loop
if idx >= v_end then
leave fetch_loop;
end if;

set sql = 'insert into hzjkqsyss
(fdate, findate, fzqdm, fszsh, fgddm, fjyxwh, fbje, fsje, fbsl, fssl, fbyj, fsyj, fbjsf, fsjsf, fbyhs, fsyhs, fbzgf, fszgf, fbghf, fsghf, fbgzlx, fsgzlx, fhggain, fbfxj, fsfxj, fbsfje, fsssje, ffxj, fzqbz, fywbz, fqsbz, fsetcode, ftzbz, fbfy1, fsfy1, fbfy2, fsfy2, fbfy3, fsfy3, fxgr, fxgr1, zqdm, datasource, fsqbh, fcfbz, ffromset)
select fdate, findate, fzqdm, fszsh, fgddm, fjyxwh, fbje, fsje, fbsl, fssl, fbyj, fsyj, fbjsf, fsjsf, fbyhs, fsyhs, fbzgf, fszgf, fbghf, fsghf, fbgzlx, fsgzlx, fhggain, fbfxj, fsfxj, fbsfje, fsssje, ffxj, fzqbz, fywbz, fqsbz, ' || char(idx) || ', ftzbz, fbfy1, fsfy1, fbfy2, fsfy2, fbfy3, fsfy3, fxgr, fxgr1, zqdm, datasource, fsqbh, fcfbz, ffromset
from hzjkqsyss
where fdate = date''2018-11-28'' and fsetcode=-1';
prepare ps from sql;
execute ps;

set idx = idx + 1;
end loop;
end p1;

call test_hzjkqsyss(1001,1100);

--sqlstate=57016错误原因：修改表字段权限，此时该表处于不可用状态
CALL SYSPROC.ADMIN_CMD('REORG TABLE lsetlist');

--当前数据库表及数据统计
select tabname,card from syscat.tables
where tabschema = 'DB2INST1' and tabname like '%LACCOUNT'
order by card desc;

--查询所有锁表信息：
SELECT * FROM TABLE (SNAP_GET_LOCK ('', -1)) AS T WHERE lock_object_type = 'TABLE_LOCK'

--根据agent_id进行解锁操作：
FORCE application (162);

/*
单个事务10240*4KB=40MB 40MB*20=800 800MB*12%=96MB，直接影响事务提交数据量，批处理控制在此范围内，否则无法执行，数据库会强制断开该链接
修改日志文件大小(4KB)：update db cfg for zsyhjj using LOGFILSIZ 10240
修改主日志文件个数：update db cfg for zsyhjj using LOGPRIMARY 20
修改辅助日志文件个数：update db cfg for zsyhjj using LOGSECOND 0
事务使用的最大主日志空间的百分比： update db cfg for zsyhjj using MAX_LOG 12

单个应用表锁升级：做大储存量*百分比 40MB*30%=12MB
单个进程操作单标超过12MB时，表行锁升级为表锁，待进程执行完后表锁状态打开后下个进程才可对该表进行操作，直接影响，业务高峰期导致程序慢，严重是表死锁页面报错，或数据库链接超时页面超时报错
锁定列表的最大存储量 (4KB)： update db cfg for zsyhjj using LOCKLIST 10240
每个应用程序的锁定百分比列表：update db cfg for zsyhjj using MAXLOCKS 30
*/