my db2 routine deleting records with commit count ,any advice ?

[heming_g]

drop procedure sp_DelTable_CommitCount@
CREATE PROCEDURE sp_DelTable_CommitCount(IN as_TableName VARCHAR(128) ,

IN as_Condition VARCHAR(1024) ,

IN abi_CommitCount BIGINT)

LANGUAGE SQL
BEGIN
DECLARE sm_End SMALLINT DEFAULT 0;
DECLARE retcode INTEGER DEFAULT 0;
DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE vc_DelStmt VARCHAR(1024);
DECLARE vc_IntactStmt VARCHAR(4096);
DECLARE vc_CreatViewStmt VARCHAR(4096);
DECLARE vc_tmpviewname VARCHAR(128);
DECLARE vc_DropViewStmt VARCHAR(256);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
EXECUTE IMMEDIATE vc_DropViewStmt ;
RESIGNAL; --给调用者�出原错误信�

END;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET sm_End = 1;

SET vc_IntactStmt = '( del_tmp_ID ) as select row_number()over()
from '||as_TableName||' where '||as_Condition;
SET vc_tmpviewname = 'tmp_view'||Replace(Replace(Rtrim(char(current
timestamp)),'-',''),'.','');
SET vc_DropViewStmt = 'DROP VIEW '||vc_tmpviewname;
SET vc_CreatViewStmt = ' create view '||vc_tmpviewname||vc_IntactStmt
;

EXECUTE IMMEDIATE vc_CreatViewStmt ;
Commit_Loop:
Loop

SET vc_DelStmt = 'DELETE FROM '||vc_tmpviewname||'
where del_tmp_ID BETWEEN 1 AND '||RTRIM(CHAR(abi_CommitCount));
INSERT INTO TMP4 VALUES (vc_DelStmt);
EXECUTE IMMEDIATE vc_DelStmt;
COMMIT;
IF (sm_End=1) THEN
LEAVE Commit_Loop;
End If ;

End Loop Commit_Loop;

EXECUTE IMMEDIATE vc_DropViewStmt ;

return 0;

END@

[Serge Rielau]

he******@hotmail.com wrote:

drop procedure sp_DelTable_CommitCount@
CREATE PROCEDURE sp_DelTable_CommitCount(IN as_TableName VARCHAR(128) ,

IN as_Condition VARCHAR(1024) ,

IN abi_CommitCount BIGINT)

LANGUAGE SQL
BEGIN
DECLARE sm_End SMALLINT DEFAULT 0;
DECLARE retcode INTEGER DEFAULT 0;
DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE vc_DelStmt VARCHAR(1024);
DECLARE vc_IntactStmt VARCHAR(4096);
DECLARE vc_CreatViewStmt VARCHAR(4096);
DECLARE vc_tmpviewname VARCHAR(128);
DECLARE vc_DropViewStmt VARCHAR(256);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
EXECUTE IMMEDIATE vc_DropViewStmt ;
RESIGNAL; --给调用者�出原错误信�

END;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET sm_End = 1;

SET vc_IntactStmt = '( del_tmp_ID ) as select row_number()over()
from '||as_TableName||' where '||as_Condition;
SET vc_tmpviewname = 'tmp_view'||Replace(Replace(Rtrim(char(current
timestamp)),'-',''),'.','');
SET vc_DropViewStmt = 'DROP VIEW '||vc_tmpviewname;
SET vc_CreatViewStmt = ' create view '||vc_tmpviewname||vc_IntactStmt
;

EXECUTE IMMEDIATE vc_CreatViewStmt ;
Commit_Loop:
Loop

SET vc_DelStmt = 'DELETE FROM '||vc_tmpviewname||'
where del_tmp_ID BETWEEN 1 AND '||RTRIM(CHAR(abi_CommitCount));
INSERT INTO TMP4 VALUES (vc_DelStmt);
EXECUTE IMMEDIATE vc_DelStmt;
COMMIT;
IF (sm_End=1) THEN
LEAVE Commit_Loop;
End If ;

End Loop Commit_Loop;

EXECUTE IMMEDIATE vc_DropViewStmt ;

return 0;

END@

Which version of DB2 are you on?
Starting in DB2 V8.1.4 you can do thsi a lot simpler (and without the view):
DELETE FROM (SELECT 1 FROM <name> WHERE <condition> FETCH FIRST <n> ROWS
ONLY)
Prepare this one ONCE then EXECUTE in the loop.
If you are prior to V8.1.4 at least move the PREPARE out of the loop.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[heming_g]

thx for your help
i do a stupid thing before .. ha