Re: sql question for purge routine
Mick
I assume that you require some sort of sql based solution? Is that correct?
If you are running into a space problem, you could use a stored procedure.
This might give you some flexibility to limit the space requirements by being
more creative in the way you retrieve and delete data.
Do you have a version of informix that supports stored procedures?
Your stored procedure could look something like
CREATE PROCEDURE delete_proc(commit_count INTEGER)
DEFINE deletes_so_far INTEGER;
LET deletes_so_far = 0;
BEGIN WORK;
FOREACH cDelereCur CURSOR WITH HOLD FOR
SELECT target_table_key_fields
INTO variable_list
FROM target, temp_tab
WHERE target.col1 = temp_tab.col1 AND target.col2 = temp_tab.col2
DELETE FROM target WHERE col1 = var1 AND col2 = var2 ....;
LET deletes_so_far = deletes_so_far + 1;
IF deletes_so_far = commit_count
THEN
COMMIT WORK;
BEGIN WORK;
LET deletes_so_far = 0;
END IF
END FOREACH
IF deletes_so_far > 0
THEN
COMMIT WORK;
END IF
END PROCEDURE
You can control the amount of information written to the logs
Mark
MarkDenham@aol.com (home)
Denham.M@amstr.com (work)