Db2 SQL PL的动态SQL

环境

• 操作系统：Ubuntu 20.04
• Db2：11.5.0.0

背景

systools.admin_task_list 是一个administrative view，记录了Db2任务调度的信息。但是 systools.admin_task_list 默认并不存在，只有在创建第一个任务的时候，才会被创建。现在想要删除命名为 abc 开头的任务，该怎么做呢？

这个问题折腾了我好久，后来我意识到，使用静态SQL无法解决问题，最终使用动态SQL找到了解决办法。

我们先来了解一下静态SQL和动态SQL。注意，本文内容是我个人的经验总结，有些术语或者概念可能有理解不到位甚至错误的地方，还需继续深入研究。

复合语句无法运行静态DDL

创建文件 test1.sql 如下：

begin 
	create table db2inst1.t1 (c1 int, c2 int);
end@

无论表 t1 是否已存在，运行脚本 test1.sql ，如下：

➜  temp0531 db2 -td@ -f test1.sql
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "<ddl-statement>" was found following "".  
Expected tokens may include:  "".  LINE NUMBER=2.  SQLSTATE=42601

这说明在复合语句里无法直接运行DDL语句。

注：

• select * from t1 不行，必须使用它的变种，比如 select count(*) into n from t1 ，或者 set n = (select count(*) from t1) ；
• delete from t1 可以，但如果 t1 为空，则会报一个警告信息；

复合语句运行动态DDL

要解决这个问题，一个办法是改用动态SQL。创建文件 test2.sql 如下：

begin
	declare n int default 0;
	declare stmt varchar(1000);

	set n = (select count(*) from syscat.tables 
			where tabschema = 'DB2INST1' and tabname = 'T1');

	if (n = 0) then
		set stmt = 'create table db2inst1.t1 (c1 int, c2 int)';
		execute immediate stmt;
	end if;
end@

无论表 t1 是否已存在，运行脚本 test2.sql ，如下：

➜  temp0531 db2 -td@ -f test2.sql
DB20000I  The SQL command completed successfully.

该脚本使用了动态SQL，同时加上了判断，只有在表 t1 不存在的时候，才会创建它。

注： execute immediate 语句无法运行 select 语句。

存储过程运行静态DDL

创建文件 test3.sql 如下：

create or replace procedure myproc()
begin
	declare n int default 0;

	set n = (select count(*) from syscat.tables 
			where tabschema = 'DB2INST1' and tabname = 'T1');

	if (n = 0) then
		create table db2inst1.t1 (c1 int, c2 int);
	end if;
end@

注意，该脚本只是创建存储过程，并没有真正运行其中的逻辑。

运行脚本 test3.sql ，如果表 t1 不存在，没有问题，但是如果表 t1 已存在，则会报错：

➜  temp0531 db2 -td@ -f test3.sql
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0601N  The name of the object to be created is identical to the existing 
name "DB2INST1.T1" of type "TABLE".  LINE NUMBER=9.  SQLSTATE=42710

这就奇怪了，明明前面是做了判断，只有这个表不存在的时候才会创建它，但为什么还是会报错说表已存在呢？更何况，这个脚本只是创建存储过程，并没有真正运行其中的逻辑呢。

其中的大致原因，是因为这样的SQL是“静态SQL”，也就是说在创建存储过程的时候，就要先编译好其中create语句，所以即使有 if 语句做了判断，也无济于事。

存储过程运行动态DDL

创建文件 test4.sql 如下：

create or replace procedure myproc()
begin
	declare n int default 0;
	declare stmt varchar(1000);

	set n = (select count(*) from syscat.tables 
			where tabschema = 'DB2INST1' and tabname = 'T1');

	if (n = 0) then
		set stmt = 'create table db2inst1.t1 (c1 int, c2 int)';
		execute immediate stmt;
	end if;
end@

无论表 t1 是否已存在，运行脚本 test2.sql ，如下：

➜  temp0531 db2 -td@ -f test4.sql
DB20000I  The SQL command completed successfully.

因为加了判断，所以即使多次调用该存储过程也不会有问题：

➜  temp0531 db2 "call myproc"

  Return Status = 0

问题

但是有一个问题我还是没有解决。

创建文件 test5.sql 如下：

set serveroutput on@

begin
	declare n, m int default 0;

	set n = (select count(*) from syscat.tables 
			where tabschema = 'DB2INST1' and tabname = 'T1');

	if (n = 1) then
		call dbms_output.put_line('OK');
		set m = (select count(*) from db2inst1.t1);
	else
		call dbms_output.put_line('oh no');
		set m = 0;
	end if;

	call dbms_output.put_line('m = ' || m);
end@

set serveroutput off@

代码逻辑很简单，如果表 db2inst1.t1 存在，就输出其行数，否则就输出0。

运行脚本 test5.sql ，假如表 db2inst1.t1 存在，则没有问题，结果如下：

➜  temp0531 db2 -td@ -f test5.sql
DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

OK
m = 2

DB20000I  The SET SERVEROUTPUT command completed successfully.

但是如果表 db2inst1.t1 不存在，则报错如下：

➜  temp0531 db2 -td@ -f test5.sql
DB20000I  The SET SERVEROUTPUT command completed successfully.

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "DB2INST1.T1" is an undefined name.  LINE NUMBER=9.  SQLSTATE=42704

DB20000I  The SET SERVEROUTPUT command completed successfully.

如前面所说，因为这样的SQL是“静态SQL”，也就是说在运行前就要先编译好package，所以即使有 if 语句做了判断，也无济于事。

解决办法是改用“动态SQL”，但是 execute immediate 语句无法运行 select 语句。这个问题该如何解决呢？

后来我找到了一个曲线救国的方法，就是 execute immediate + 复合语句。

修改 test5.sql ，创建文件 test6.sql 如下：

set serveroutput on@

begin
	declare n, m int default 0;
	declare stmt varchar(1000);

	set n = (select count(*) from syscat.tables 
			where tabschema = 'DB2INST1' and tabname = 'T1');

	if (n = 1) then
		call dbms_output.put_line('OK');
		set stmt = 'begin declare m int;'
			|| ' set m = (select count(*) from db2inst1.t1);'
			|| ' call dbms_output.put_line(''m = '' || m);'
			|| ' end';
		execute immediate stmt;
	else
		call dbms_output.put_line('oh no');
		set m = 0;
		call dbms_output.put_line('m = ' || m);
	end if;
end@

set serveroutput off@

运行脚本 test6.sql ，假如表 t1 存在，如下：

➜  temp0531 db2 -td@ -f test6.sql
DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

OK
m = 2

DB20000I  The SET SERVEROUTPUT command completed successfully.

假如表 t1 不存在，如下：

➜  temp0531 db2 -td@ -f test6.sql
DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

oh no
m = 0

DB20000I  The SET SERVEROUTPUT command completed successfully.

真不容易！终于有个办法了，虽然丑了点。

示例

现在来看本文开头所提出的实际问题。

systools.admin_task_list 是一个administrative view，记录了Db2任务调度的信息。但是 systools.admin_task_list 默认并不存在，只有在创建第一个任务的时候，才会被创建。现在想要删除命名为 abc 开头的任务，该怎么做呢？

要想知道哪些任务以 abc 开头，就要查询 systools.admin_task_list ，但是它不一定存在。所以，显然先要查询它是否存在，如果存在，再以动态select的方式来访问它。

创建文件 test7.sql 如下：

set serveroutput on@

create or replace type strarray as varchar(255) array[]@

begin
	declare n int default 0;
	declare stmt varchar(1000);

	set n = (select count(*) from syscat.tables 
			where tabschema = 'SYSTOOLS' and tabname = 'ADMIN_TASK_LIST');

	if (n = 1) then
		call dbms_output.put_line('OK');

		set stmt = 'begin declare m, i int;'
			|| ' declare arr1 strarray;'
			|| ' declare stmt2 varchar(1000);'
			|| ' set arr1 = array[select name from systools.admin_task_list where name like ''abc%''];'
			|| ' set m = cardinality(arr1);'
			|| ' set i = 1;'
			|| ' while (i <= m) do'
			|| ' 	call dbms_output.put_line(''haha'' || arr1[i]);'
			|| ' 	set stmt2 = ''call sysproc.admin_task_remove('''''' || arr1[i] || '''''', null)'';'
			|| ' 	execute immediate stmt2;'
			|| ' 	set i = i + 1;'
			|| ' end while;'
			|| ' call dbms_output.put_line(''m = '' || m);'
			|| ' end';
		call dbms_output.put_line('stmt = ' || stmt);

		execute immediate stmt;
	else
		call dbms_output.put_line('SYSTOOLS.ADMIN_TASK_LIST does not exist.');
	end if;
end@

set serveroutput off@

注意代码中的六个单引号！这个问题又折腾了我好长时间，这是因为在 execute immediate 里又嵌套了一层 execute immediate ，也就是说一共有3层。

• 在内层，只需1个引号 （ call sysproc.admin_task_remove('xxx', null) ）；
• 在中间层，因为文本本身是用引号引起来的，所以需要3个引号（ set stmt2 = 'call sysproc.admin_task_remove(''' || xxx || ''', null)' ）；
• 在外层，要把中间层代码变成文本，所以每个引号都变成两个引号（ set stmt = 'set stmt2 = ''call sysproc.admin_task_remove('''''' || xxx || '''''', null)''' ）；

运行脚本 test7.sql ，假如 systools.admin_task_list 不存在，如下：

➜  temp0531 db2 -td@ -f test7.sql
DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

SYSTOOLS.ADMIN_TASK_LIST does not exist.

DB20000I  The SET SERVEROUTPUT command completed successfully.

创建task：

CALL SYSPROC.ADMIN_TASK_ADD 
	('abcaaaaa',
  	NULL,
   	NULL,
  	NULL,
  	'0 0-23 * * *',
  	'PROD',
  	'FLUSH_EVENT_MONITOR',
  	NULL,
  	NULL,
  	NULL )

类似的，再建两个task，命名为 abcbbbbb 和 kkkkk 。

现在， systools.admin_task_list 就被Db2自动创建出来了：

➜  temp0531 db2 "select name from systools.admin_task_list" 
NAME                                                                                                                      ---------
abcaaaaa
abcbbbbb
kkkkk

  3 record(s) selected.

再次运行脚本 test7.sql ，如下：

➜  temp0531 db2 -td@ -f test7.sql                           
DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

OK
stmt = begin declare m, i int; declare arr1 strarray; declare stmt2 varchar(1000); set arr1 = array[select name from systools.admin_task_list where name like 'abc%']; set m = cardinality(arr1); set i = 1; while (i <= m) do 	call dbms_output.put_line('haha' || arr1[i]); 	set stmt2 = 'call sysproc.admin_task_remove(''' || arr1[i] || ''', null)'; 	execute immediate stmt2; 	set i = i + 1; end while; call dbms_output.put_line('m = ' || m); end
hahaabcaaaaa
hahaabcbbbbb
m = 2

DB20000I  The SET SERVEROUTPUT command completed successfully.

再次查看 systools.admin_task_list ：

➜  temp0531 db2 "select name from systools.admin_task_list"                               
NAME                                                                                                                          -------
kkkkk
  1 record(s) selected.

可见， abc 开头的task都被删除了。

总结

大致总结如下（可能会有疏漏或者错误，待查）：

• 复合语句无法运行静态DDL；
• 复合语句可以运行静态DML，但是要确保表存在（无论是否有if判断）；
• 复合语句可以运行动态DDL；
• 复合语句可以运行动态DML，但select语句不行；
• 存储过程可以运行静态DDL/DML，但是在创建存储过程时：
  • 若有 create 语句，要确保表不存在；
  • 若有 select 语句，要确保表存在；
  • 若有 drop 语句，不受影响，表存在不存在都OK；
• 存储过程可以运行动态DDL；
• 存储过程可以运行动态DML，但select语句不行；

要想动态运行select语句，可以采用 execute immediate 加复合语句的方法，曲线救国 。