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

How to DELETE with COMMIT COUNT

P: n/a
I figure someone else may have use for this toy, so here goes:
Prereq: DB2 V8.1 FP4

CREATE PROCEDURE DELETE_MANY_ROWS
(tabschema VARCHAR(128),
tabname VARCHAR(128),
predicate VARCHAR(1000),
commitcount INTEGER)
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE txt VARCHAR(10000);
DECLARE stmt STATEMENT;
SET txt = 'DELETE FROM (SELECT 1 FROM "'
|| tabschema || '"."' || tabname || '" WHERE '
|| predicate || ' FETCH FIRST ' ||
RTRIM(CHAR(commitcount)) || ' ROWS ONLY) AS D';
PREPARE stmt FROM txt;
l: LOOP
EXECUTE stmt;
IF SQLCODE = 100 THEN LEAVE l; END IF;
COMMIT;
END LOOP;
END
%

CALL DELETE_MANY_ROWS('SRIELAU', 'T1', 'c1 > 0', 1000)
%

Quiz!
How can performance be improved if the table has e.g. a known primary key?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Serge Rielau wrote:
I figure someone else may have use for this toy, so here goes:
Prereq: DB2 V8.1 FP4

CREATE PROCEDURE DELETE_MANY_ROWS
(tabschema VARCHAR(128),
tabname VARCHAR(128),
predicate VARCHAR(1000),
commitcount INTEGER)
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE txt VARCHAR(10000);
DECLARE stmt STATEMENT;
SET txt = 'DELETE FROM (SELECT 1 FROM "'
|| tabschema || '"."' || tabname || '" WHERE '
|| predicate || ' FETCH FIRST ' ||
RTRIM(CHAR(commitcount)) || ' ROWS ONLY) AS D';
PREPARE stmt FROM txt;
l: LOOP
EXECUTE stmt;
IF SQLCODE = 100 THEN LEAVE l; END IF;
COMMIT;
END LOOP;
I think you need an extra "COMMIT;" here, in case you're not in
auto-commit mode. Otherwise, the last few rows may not get committed.
END
%

CALL DELETE_MANY_ROWS('SRIELAU', 'T1', 'c1 > 0', 1000)
%
Thanks for posting this. I've been looking for a way to do this for
awhile. I have a question, though: why the "AS D" syntax?
Quiz!
How can performance be improved if the table has e.g. a known primary key?


The primary key name could be an argument to the procedure?

Thanks,
Dave
Nov 12 '05 #2

P: n/a
Dave Benjamin wrote:
Thanks for posting this. I've been looking for a way to do this for
awhile. I have a question, though: why the "AS D" syntax?

You got me. I forgot we made the correlation clause optional.
It is mandatory for derived selects in the from clause....
Quiz!
How can performance be improved if the table has e.g. a known primary
key?

The primary key name could be an argument to the procedure?

Well, looking up the key in the catalog would have yielded bonus points,
but that wasn't the point, so yes, assume a the column name for the PK
is given.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
I've been trying to modify this to use a date field rather than a numeric
as my predicate by adding a second predicate and more concats. However,
it always returns 'SQL0180N The syntax of the string representation of a
datetime value is incorrect. SQLSTATE=22007'. Is there a way to see how
the procedure parses the txt to find out how this date is being
interpreted?

create procedure DTF.DELETE_BY_DATE
(tabschema VARCHAR(128),
tabname VARCHAR(128),
predicate VARCHAR(1000),
deldate VARCHAR(1000),
commitcount INTEGER)
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE txt VARCHAR(10000);
DECLARE stmt STATEMENT;
SET txt='DELETE FROM (SELECT 1 FROM
' || tabschema || '.' || tabname || ' WHERE
' || predicate ||'''||deldate||''' ||
' FETCH FIRST ' || RTRIM(CHAR(commitcount))
|| ' ROWS ONLY) AS D' ;
PREPARE stmt FROM txt;
I:LOOP
EXECUTE stmt;
IF SQLCODE = 100 THEN LEAVE I; END IF;
COMMIT;
END LOOP;
COMMIT;
END
%

Apr 19 '07 #4

P: n/a
Froth wrote:
I've been trying to modify this to use a date field rather than a numeric
as my predicate by adding a second predicate and more concats. However,
it always returns 'SQL0180N The syntax of the string representation of a
datetime value is incorrect. SQLSTATE=22007'. Is there a way to see how
the procedure parses the txt to find out how this date is being
interpreted?
I would add an exit exception handler that SIGNALs the TXT as MESSAGE_TXT
SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt;
something like it anyway...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 19 '07 #5

P: n/a
Thanks.

Apr 19 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.