How to DELETE with COMMIT COUNT

[Serge Rielau]

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

[Dave Benjamin]

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

[Serge Rielau]

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

[Froth]

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
%

[Serge Rielau]

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

[Froth]

Thanks.