[Squirrel-sql-users] DB2: How to set up SQuirreL to be able to create (and run) multiline and multicommand stored procedure?

[Stepan R. <xr...@se...>]

Hello, 

is anybody successful with running SQL code to create stored procedure on DB2 
(let say 9.1 running on AIX, but I guess, it is not important) and the stored 
procedure is multiline and multicommand? Am I missing some SQuirreL setting or
 configuration SQL command or JDBC driver property (like @ terminator)? I read
 many articles about thin on Internet but no recommendation lead to success. I
 am using "com.ibm.db2.jcc.DB2Driver" JDBC driver in SQuirreL in Win7 64bit 
(Java 64bit) and DB2 9.1 on AIX on server. 

Thank You in advance. Stepan 

Because 

create procedure dropTestIfExists(in myIndex varchar(128)) language SQL 
begin 
  declare sqlCmd varchar(139); 
  if exists(select 1 from syscat.indexes where indschema = current_schema and 
indname = myIndex) then 
    set sqlCmd = 'drop index ' || myIndex; 
    execute immediate sqlCmd; 
  end if; 
end 

end with: 

Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, 
SQLERRMC=END-OF-STATEMENT;sqlCmd varchar(139);<psm_semicolon>, DRIVER=4.7.85
SQLState:  42601
ErrorCode: -104
Error occured in:
create procedure dropTestIfExists(in myIndex varchar(128)) language SQL 
begin 
  declare sqlCmd varchar(139) 

Using DB2 CLP on AIX is OK with following command on my environment: 

create procedure dropIndexIfExists(in myIndex varchar(128)) language SQL \
begin \
  declare sqlCmd varchar(139); \
  if exists(select 1 from syscat.indexes where indschema = current_schema and 
indname = myIndex) then \
    set sqlCmd = 'drop index ' || myIndex; \
    execute immediate sqlCmd; \
  end if; \
end \