We currently have DB2 scripts that execute from a DB2 command line (DB2 -vtf <jobname.db2>) and in which all statements (UPDATEs, DELETEs, INSERTs, CREATEs, etc.) execute one after another without caring whether the previous one completed successfully or failed.

We want do be able to direct the execution a little bit more and I have read on IBM's website about IF's, FOR's, REPEAT's, WHILE's, throwing and catching exceptions, etc. How exactly do these work? What do I need to create in order to execute such scripts? Is it stored procedures or is there something else like SQL Server's Transac SQL? Keep in mind we may want to connect to more than one database as we do with our current scripts (exporting data from database 1 to a file and then loading into database 2).

Thank you all in advance!

1. the various commands are control statements and they are used in the sp and triggers.the sp runs under the connectivity of the client.

2. You can use type 2 connections and move back and forth among them.

3. You can write scripts that connect to db and did some work , reset the
connection and connected to another db and so on.

4. you can use the s options like db2 -stvf to stop on error.


regards,

mujeeb

mujeeb,

Are we talking a Java or SQL stored procedure?

Also, as far as stopping on error, we don't necessarily want to do that, but say, we want to skip certain statements if there is an exception, but still execute others or something of the sort. We don't want exiting the procedure to be the only way to handle exceptions. For example:

if
<statement to update rows> is unsuccessful
then
<statement to insert rows>
<statement to update rows>
end if

<statements to export rows>

Thank you for your response.

I read somewhere this procedure,may be this could help you

CREATE PROCEDURE simple_error_token ( IN p_midinit CHAR
,IN p_empno CHAR(6)
,OUT p_sqlcode_out int
,OUT p_sqlstate_out char(5)
,OUT p_token_string VARCHAR(100) )
SPECIFIC simple_error_token
LANGUAGE SQL
setk: BEGIN
-- Declare variables
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
-- Declare condition handlers
DECLARE EXIT HANDLER FOR SQLEXCEPTION -- (1)
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -- (4)
GET DIAGNOSTICS EXCEPTION 1 p_token_string = DB2_TOKEN_STRING;
SELECT SQLCODE -- (2)
,SQLSTATE
INTO p_sqlcode_out
,p_sqlstate_out
FROM sysibm.sysdummy1;
RESIGNAL; -- (3)
END;
-- Procedure logic
SET p_token_string = '';
VALUES (SQLCODE, SQLSTATE)
INTO p_sqlcode_out
,p_sqlstate_out;

if p_sql_code_out // use your logic
UPDATE employee
SET midinit = p_midinit
WHERE empno = p_empno;
END setk

regards,

mujeeb

mujeeb,

I am trying to run such sp, but I am getting an error. I've posted a new thread, as the issue at hand is different from this one. Feel free to check it out and respond.

Thanks for all your help!

Personally,
I'd rewrite SQL*Server TransactSQL and/or Oracle PL/SQL scripts in Java rather than SQL/PL. The reason is:-

1. You don't have to fiddle around setting up your compiler (Stinger interprets SQL/PL, however, so it'll soon not be a problem)

2. You don't have to learn a whole new language

3. Java is functionally much richer than SQL/PL and will continue to enhance

4. SQL/PL is mainly for stored procedures whereas you can write scripts and stored procedures in Java for DB2.

Check out <DB2 installation directory>/samples/java/jdbc