By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,004 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,004 IT Pros & Developers. It's quick & easy.

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

P: n/a
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@

Feb 24 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Feb 24 '06 #2

P: n/a
thx for your help
i do a stupid thing before .. ha

Mar 2 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.