调用DB2存储过程出现错误

错误为：
irp@P570R:/irp/etl>db2 -tvf fb.sql
CREATE PROCEDURE “DB2”.”FENBIAO” ()
language sql
begin
declare v_xbm varchar(50)
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 “END-OF-STATEMENT” was found following “v_xbm
varchar(50)”. Expected tokens may include: “”. LINE
NUMBER=4. SQLSTATE=42601

declare v_jbm varchar(50)
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 “declare v_jbm varchar” was found following
“BEGIN-OF-STATEMENT”. Expected tokens may include: “”. LINE
NUMBER=1. SQLSTATE=42601

declare v_a1 integer default 0
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 “0” was found following “1 integer default”.
Expected tokens may include: “END-OF-STATEMENT”. LINE NUMBER=1.
SQLSTATE=42601

declare v_b1 integer default 0
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 “0” was found following “1 integer default”.
Expected tokens may include: “END-OF-STATEMENT”. LINE NUMBER=1.
SQLSTATE=42601

declare v_bmhz varchar(20) default ‘2016’
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 “declare v_bmhz varchar” was found following
“BEGIN-OF-STATEMENT”. Expected tokens may include: “”. LINE
NUMBER=1. SQLSTATE=42601

declare v_sql varchar(1000)
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 “declare v_sql varchar” was found following
“BEGIN-OF-STATEMENT”. Expected tokens may include: “”. LINE
NUMBER=1. SQLSTATE=42601

declare exit handler for sqlexception begin rollback
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 “” was found following “EXIT”. Expected
tokens may include: “handler”. LINE NUMBER=1. SQLSTATE=42601

end
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 “END-OF-STATEMENT” was found following “end”.
Expected tokens may include: “JOIN ”. SQLSTATE=42601

a1:begin declare s_cur cursor with hold for select a. tabname from syscat.tables a,syscat.columns b where a.tabname = b.tabname and b.colname = ‘BATCHDATE’ and type = ‘T’ and card > ‘1000000’
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 “a1:begin” was found following
“BEGIN-OF-STATEMENT”. Expected tokens may include: “”.
SQLSTATE=42601

declare continue handler for not found set v_a1 = 1
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 “” was found following “CONTINUE”.
Expected tokens may include: “handler”. LINE NUMBER=1. SQLSTATE=42601

declare global temporary table session.tmp( name varchar(50) ) on commit preserve rows with replace not logged
DB20000I The SQL command completed successfully.

open s_cur
DB21028E The cursor “S_CUR” has not been declared.

fetch_loop1:loop fetch s_cur into v_jbm
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 “fetch_loop1” was found following
“BEGIN-OF-STATEMENT”. Expected tokens may include: “”.
SQLSTATE=42601

if v_a1 = 0 then set v_sql = ‘insert into session.tmp select ”’||v_jbm||”’ from ‘||v_jbm||’ where substr(trim(batchdate),1,4) = ‘||v_bmhz||’ having count(*) > 1000’
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 “if” was found following “BEGIN-OF-STATEMENT”.
Expected tokens may include: “”. SQLSTATE=42601

prepare cre from v_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 “prepare” was found following
“BEGIN-OF-STATEMENT”. Expected tokens may include: “SELECT”. SQLSTATE=42601

execute cre
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 “END-OF-STATEMENT” was found following “execute
cre”. Expected tokens may include: “JOIN ”. SQLSTATE=42601

commit
DB20000I The SQL command completed successfully.

set v_a1 = 0
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N “V_A1” is not valid in the context where it is used. SQLSTATE=42703

else leave fetch_loop1
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 “leave” was found following “else “. Expected
tokens may include: “JOIN ”. SQLSTATE=42601

end if
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 “END-OF-STATEMENT” was found following “end if”.
Expected tokens may include: “JOIN ”. SQLSTATE=42601

end loop fetch_loop1
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 “loop” was found following “end “. Expected
tokens may include: “JOIN ”. SQLSTATE=42601

close s_cur
DB21028E The cursor “S_CUR” has not been declared.

commit
DB20000I The SQL command completed successfully.

b1:begin declare c_cur cursor with hold for select name from session.tmp
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 “b1:begin” was found following
“BEGIN-OF-STATEMENT”. Expected tokens may include: “”.
SQLSTATE=42601

declare continue handler for not found set v_b1 = 1
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 “” was found following “CONTINUE”.
Expected tokens may include: “handler”. LINE NUMBER=1. SQLSTATE=42601

open c_cur
DB21028E The cursor “C_CUR” has not been declared.

fetch_loop2:loop fetch c_cur into v_jbm
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 “fetch_loop2” was found following
“BEGIN-OF-STATEMENT”. Expected tokens may include: “”.
SQLSTATE=42601

if v_b1 = 0 then set v_xbm = v_jbm||’_’||v_bmhz
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 “if” was found following “BEGIN-OF-STATEMENT”.
Expected tokens may include: “”. SQLSTATE=42601

set v_sql = ‘create table ‘||v_xbm||’ as (select * from ‘||v_jbm||’) definition only’
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N “V_SQL” is not valid in the context where it is used.
SQLSTATE=42703

prepare cre from v_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 “prepare” was found following
“BEGIN-OF-STATEMENT”. Expected tokens may include: “SELECT”. SQLSTATE=42601

execute cre
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 “END-OF-STATEMENT” was found following “execute
cre”. Expected tokens may include: “JOIN ”. SQLSTATE=42601

set v_sql = ‘insert into ‘||v_xbm||’ select * from ‘||v_jbm||’ where substr(trim(batchdate),1,4) = ‘||v_bmhz
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N “V_SQL” is not valid in the context where it is used.
SQLSTATE=42703

prepare cre from v_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 “prepare” was found following
“BEGIN-OF-STATEMENT”. Expected tokens may include: “SELECT”. SQLSTATE=42601

execute cre
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 “END-OF-STATEMENT” was found following “execute
cre”. Expected tokens may include: “JOIN ”. SQLSTATE=42601

set v_sql = ‘delete from ‘||v_jbm||’ where substr(trim(batchdate),1,4) = ‘||v_bmhz
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N “V_SQL” is not valid in the context where it is used.
SQLSTATE=42703

prepare cre from v_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 “prepare” was found following
“BEGIN-OF-STATEMENT”. Expected tokens may include: “SELECT”. SQLSTATE=42601

execute cre
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 “END-OF-STATEMENT” was found following “execute
cre”. Expected tokens may include: “JOIN ”. SQLSTATE=42601

commit
DB20000I The SQL command completed successfully.

else leave fetch_loop2
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 “leave” was found following “else “. Expected
tokens may include: “JOIN ”. SQLSTATE=42601

end if
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 “END-OF-STATEMENT” was found following “end if”.
Expected tokens may include: “JOIN ”. SQLSTATE=42601

end loop fetch_loop2
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 “loop” was found following “end “. Expected
tokens may include: “JOIN ”. SQLSTATE=42601

close c_cur
DB21028E The cursor “C_CUR” has not been declared.

end b1
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 “END-OF-STATEMENT” was found following “end b1”.
Expected tokens may include: “JOIN ”. SQLSTATE=42601

end a1
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 “END-OF-STATEMENT” was found following “end a1”.
Expected tokens may include: “JOIN ”. SQLSTATE=42601

DB21007E End of file reached while reading the command.

---

原因：
用xshell调用存储过程时，由于shell默认结束符为分号（；），所以在存储的结束后加@，然后在调用时：
irp@P570R:/irp/etl>db2 -tvf fb.sql -td@