DB2 存储过程 - 利用 dbms_output.put_line 输出至屏幕

优点: 可以调用 oracle 的 put_line 方法来执行,并将结果打印在屏幕上, 无需再调用 shell 脚本.
缺点: 在存储过程正在执行的过程中, 无法实时监控到追踪的结果.


## 设置注册变量 打开兼容性开关 DB2_COMPATIBILITY_VECTOR 设置其值为 ORA
db2set  DB2_COMPATIBILITY_VECTOR=ORA


## 打开输出开关
db2 "set serveroutput on"


## 创建测试存储过程
drop procedure acrm.put_in_00
;
create procedure acrm.put_in_00()
language sql
not deterministic
no external action
modifies sql data
called on null input
inherit special registers
begin
declare v_chr1 varchar(256);
declare v_chr2 varchar(256);


set v_chr1 = varchar(current timestamp);
call dbms_alert.sleep(5);
set v_chr2 = 'step 1 elapse time : '||varchar(timestampdiff(2,char(current timestamp - timestamp(v_chr1))))||' seconds.';
call dbms_output.put_line('exec step 1 : call dbms_alert.sleep(5)');
call dbms_output.put_line(v_chr2);
call dbms_output.put_line('');


set v_chr1 = varchar(current timestamp);
call dbms_alert.sleep(10);
set v_chr2 = 'step 1 elapse time : '||varchar(timestampdiff(2,char(current timestamp - timestamp(v_chr1))))||' seconds.';
call dbms_output.put_line('exec step 2 : call dbms_alert.sleep(10)');
call dbms_output.put_line(v_chr2);
call dbms_output.put_line('');


set v_chr1 = varchar(current timestamp);
call dbms_alert.sleep(15);
set v_chr2 = 'step 3 elapse time : '||varchar(timestampdiff(2,char(current timestamp - timestamp(v_chr1))))||' seconds.';
call dbms_output.put_line('exec step 1 : call dbms_alert.sleep(15)');
call dbms_output.put_line(v_chr2);
call dbms_output.put_line('');


set v_chr1 = varchar(current timestamp);
call dbms_alert.sleep(20);
set v_chr2 = 'step 4 elapse time : '||varchar(timestampdiff(2,char(current timestamp - timestamp(v_chr1))))||' seconds.';
call dbms_output.put_line('exec step 1 : call dbms_alert.sleep(20)');
call dbms_output.put_line(v_chr2);
call dbms_output.put_line('');


set v_chr1 = varchar(current timestamp);
call dbms_alert.sleep(25);
set v_chr2 = 'step 5 elapse time : '||varchar(timestampdiff(2,char(current timestamp - timestamp(v_chr1))))||' seconds.';
call dbms_output.put_line('exec step 1 : call dbms_alert.sleep(25)');
call dbms_output.put_line(v_chr2);
call dbms_output.put_line('');


set v_chr1 = varchar(current timestamp);
call dbms_alert.sleep(30);
set v_chr2 = 'step 6 elapse time : '||varchar(timestampdiff(2,char(current timestamp - timestamp(v_chr1))))||' seconds.';
call dbms_output.put_line('exec step 1 : call dbms_alert.sleep(30)');
call dbms_output.put_line(v_chr2);
call dbms_output.put_line('');


set v_chr1 = varchar(current timestamp);
call dbms_alert.sleep(35);
set v_chr2 = 'step 7 elapse time : '||varchar(timestampdiff(2,char(current timestamp - timestamp(v_chr1))))||' seconds.';
call dbms_output.put_line('exec step 1 : call dbms_alert.sleep(35)');
call dbms_output.put_line(v_chr2);
call dbms_output.put_line('');


set v_chr1 = varchar(current timestamp);
call dbms_alert.sleep(40);
set v_chr2 = 'step 8 elapse time : '||varchar(timestampdiff(2,char(current timestamp - timestamp(v_chr1))))||' seconds.';
call dbms_output.put_line('exec step 1 : call dbms_alert.sleep(40)');
call dbms_output.put_line(v_chr2);
call dbms_output.put_line('');


set v_chr1 = varchar(current timestamp);
call dbms_alert.sleep(45);
set v_chr2 = 'step 9 elapse time : '||varchar(timestampdiff(2,char(current timestamp - timestamp(v_chr1))))||' seconds.';
call dbms_output.put_line('exec step 1 : call dbms_alert.sleep(45)');
call dbms_output.put_line(v_chr2);
call dbms_output.put_line('');


set v_chr1 = varchar(current timestamp);
call dbms_alert.sleep(50);
set v_chr2 = 'step 10 elapse time : '||varchar(timestampdiff(2,char(current timestamp - timestamp(v_chr1))))||' seconds.';
call dbms_output.put_line('exec step 1 : call dbms_alert.sleep(50)');
call dbms_output.put_line(v_chr2);
call dbms_output.put_line('');


end
;


# 调用示例
[db2inst1@script ~]$ db2 "call acrm.put_in_00()"
  Return Status = 0


exec step 1 : call dbms_alert.sleep(5)
step 1 elapse time : 5 seconds.


exec step 2 : call dbms_alert.sleep(10)
step 1 elapse time : 10 seconds.


exec step 1 : call dbms_alert.sleep(15)
step 3 elapse time : 15 seconds.


exec step 1 : call dbms_alert.sleep(20)
step 4 elapse time : 20 seconds.


exec step 1 : call dbms_alert.sleep(25)
step 5 elapse time : 25 seconds.


exec step 1 : call dbms_alert.sleep(30)
step 6 elapse time : 30 seconds.


exec step 1 : call dbms_alert.sleep(35)
step 7 elapse time : 35 seconds.


exec step 1 : call dbms_alert.sleep(40)
step 8 elapse time : 40 seconds.


exec step 1 : call dbms_alert.sleep(45)
step 9 elapse time : 45 seconds.


exec step 1 : call dbms_alert.sleep(50)
step 10 elapse time : 50 seconds.


## 如何在视图中查看到当前的进度呢?
select 
coord_member,
application_handle,
application_name,
session_auth_id,
client_applname,
elapsed_time_sec,
activity_state,
activity_type,
stmt_text
from sysibmadm.mon_current_sql
where application_handle in (
select t.application_handle
from
(
select 
actmetrics.application_handle,
varchar(actmetrics.stmt_text,256) as stmt_text
FROM TABLE(MON_GET_ACTIVITY_DETAILS(for each row of (select application_handle,
  uow_id,
  activity_id from table(wlm_get_workload_occurrence_activities_v97(NULL, -1)) 
  WHERE activity_id > 0), -1)) AS ACTDETAILS, 
XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),'$actmetrics/db2_activity_details' 
  PASSING XMLPARSE(DOCUMENT ACTDETAILS.DETAILS) as "actmetrics" 
  COLUMNS "APPLICATION_HANDLE" INTEGER PATH 'application_handle', 
  "STMT_TEXT" VARCHAR(1024) PATH 'stmt_text' ) AS ACTMETRICS
) t
where lower(t.stmt_text) like '%call acrm.put_in()%'
)