Hello,

My goal is to run a build script for our ASP.NET front-end DB2/iSeries back-end system. To accomplish this I need to find a way to run a Create Procedure script from a command-prompt. Obviously I can fire up the DB2 Command Center and run scripts in the UI, but that is no way to execute a build script!

At the DB2 CLP (command-line processor) I am able to invoke the script by the standard syntax:

>db2 - f test.db2

where test.db2 is the file that contains my Create Procedure statement. Unfortunately, the DB2 CLP does not seem to understand the script, and throws all manner of DB21034E errors (invalid syntax).

Is there a way to accomplish my goal, and if so, where is it documented?

My build machine is configured with Windows XP Professional and DB2 Connect 7.2 FP5. On the iSeries we are running OS/400 v5R1.

Thanks!

are you creating sql procedure?
if so,you should define the statement termination character, not use the default semicolon (. like following:
create procedure test()
begin
......
end
@

db2 -td@ -vf test.db2

Hi,

1. Can you show me the SP?
2. You run the script in the Windows command prompt or the db2 CPL?

Abel.

Yao -

Your diagnosis was on the mark. The CLP was trying to execute individual statements, rather than passing the entire set of statements to DB2. Using your advice, I have made the scenario work. Thanks for your help!

Abel -

Here's how it works:

from the DOS command-line - c:\sqllib\bin>db2cmd mytest

mytest.cmd file contains:
db2 connect to mydb user myuser using mypassword > test.out 2>&1
db2 -td@ -v -f test.db2 -l log.out > test.out 2>&1
exit

test.db2 contains:
-- PROCEDURE SPRTESTCJF --
drop procedure SPRTESTCJF@
create procedure SPRTESTCJF(
IN prmAgencyId Char(10),
IN prmUsername Char(25),
OUT retCode INTEGER
)

language SQL
result sets 2
not deterministic
set option datfmt=*iso

BEGIN
-- declare section
DECLARE usercur CURSOR WITH RETURN FOR
SELECT FIELD1, FIELD2, FIELD3
FROM MYTABLE
WHERE AGENTNBR = prmAgencyId
AND USERNAME = prmUsername;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET retCode = -10;
DECLARE EXIT HANDLER FOR NOT FOUND
SET retCode = -1;

-- execute logic
SET retCode = 0;
OPEN usercur;
END
@

ok,
first, you have a connect with the db? else, connect to... (before create sp)

second, no break line in the command.
correct example:
---
connect to db user usuario using password
select * from tabla
connect reset
-----
INCORRECT example:
---
connect to db user usuario using password
select *
from tabla
connect reset
-----
Each line is processed with a commad.

Saludos, Abel.

Abel -

Thanks for your reply. Both of your points, which are quite valid, are addressed in my code. First, the connection is established in line 1 of mytest.cmd:

db2 connect to mydb user myuser using mypassword > test.out 2>&1

Second, when the CLP is processing the file test.db2, it uses a token to separate statements, rather than end-of-line character. In this case, the -td@ option sets the statement separator token to '@'. Thus test.db2 gets processed as 2 statements: a "DROP PROCEDURE" statement and a "CREATE PROCEDURE" statement.