删除大量记录 [英] deleting large numbers of records
问题描述
我们有一个批处理过程，每天将大量（100,000 - 1,000,000）的
记录插入数据库。 （DL / I数据库。）我们正在考虑将
转换为DB2表。目前我们有逻辑，在插入任何数据之前，先读取第一个输入记录，然后检查表格中是否已存在
。如果记录已经存在，则有两个
选项：
1）不要继续，因为你今天已经完成了这份工作！
2）这是重新运行 - 继续。
如果选择了数字2，首先发生的事情就是删除今天插入的每条记录
做常规插入
过程。 （你可能会问，为什么不跳过那些已经在那里的b $ b。那是因为我们可能正在重新运行一个更新的输入文件，其中
输入记录可能与第一次运行时不同。）
无论如何，我认为DB2会很快。我将需要做的是：
EXEC SQL
从FILM.FILM_TRANSACTIONS删除
WHERE UPDATE_DATE = FB_FUNC.TO_DATE （：CURR-DATE-JUL-PACKED）
END-EXEC
唯一的问题是我的日志文件最终会耗尽空间。所以
现在我想出了以下内容：
DELETE-TODAY SECTION。
DISPLAY''DELETE PROCESS BEGINS''UPON CONSOLE
在测试后进行测试
UNTIL SQLCODE = 100
显示''委托......''控制台
执行COMMIT-UOW
DISPLAY''删除10000''在控制台上
执行DB2-DELETE-TODAY
END -PERFORM
PERFORM COMMIT-UOW
DISPLAY''删除进程结束''在控制台上
We have a batch process that inserts large numbers (100,000 - 1,000,000) of
records into a database each day. (DL/I database.) We''re considering
converting it to a DB2 table. Currently we have logic in place that, prior
to inserting any data, reads the first input record and checks to see if it
already exists in the table. If the record already exists there are two
options:
1) Don''t continue, because you already ran this job today!
2) This is a rerun - continue.
If number 2 is selected the first thing that happens is that it deletes
every record that was inserted today prior to doing the regular insert
process. (You may ask, why not just skip over the ones that are already
there. It''s because we may be rerunning with an updated input file, where
the input records may be different than during the first run.)
Anyway, I figured with DB2 this would be a snap. I''ll I''d need to do is:
EXEC SQL
DELETE FROM FILM.FILM_TRANSACTIONS
WHERE UPDATE_DATE = FB_FUNC.TO_DATE(:CURR-DATE-JUL-PACKED)
END-EXEC
The only problem is that my log file would end up running out of room. So
now I''ve come up with the following:
DELETE-TODAY SECTION.
DISPLAY ''DELETE PROCESS BEGINS'' UPON CONSOLE
PERFORM WITH TEST AFTER
UNTIL SQLCODE = 100
DISPLAY ''COMMITTING...'' UPON CONSOLE
PERFORM COMMIT-UOW
DISPLAY ''DELETING 10000'' UPON CONSOLE
PERFORM DB2-DELETE-TODAY
END-PERFORM
PERFORM COMMIT-UOW
DISPLAY ''DELETE PROCESS ENDS'' UPON CONSOLE
推荐答案
" Frank Swarbrick" < Fr ************* @ efirstbank.com写信息
新闻：51 ************* @ mid.individual .net ...
"Frank Swarbrick" <Fr*************@efirstbank.comwrote in message
news:51*************@mid.individual.net...
我们有一个批量处理，插入大数（100,000 - 1,000,000）
每天记录到数据库中。 （DL / I数据库。）我们正在考虑将
转换为DB2表。目前我们有逻辑，
之前
插入任何数据，读取第一个输入记录并检查是否
it
已存在于表中。如果记录已经存在，则有两个
选项：
1）不要继续，因为你今天已经完成了这份工作！
2）这是重新运行 - 继续。
如果选择了数字2，首先发生的事情就是删除今天插入的每条记录
做常规插入
过程。 （你可能会问，为什么不跳过那些已经在那里的b $ b。那是因为我们可能正在重新运行一个更新的输入文件，其中
输入记录可能与第一次运行时不同。）
无论如何，我认为DB2会很快。我将需要做的是：
EXEC SQL
从FILM.FILM_TRANSACTIONS删除
WHERE UPDATE_DATE = FB_FUNC.TO_DATE （：CURR-DATE-JUL-PACKED）
END-EXEC
唯一的问题是我的日志文件最终会耗尽空间。所以
现在我想出了以下内容：
DELETE-TODAY SECTION。
DISPLAY''DELETE PROCESS BEGINS''UPON CONSOLE
在测试后进行测试
UNTIL SQLCODE = 100
显示''委托......''控制台
执行COMMIT-UOW
DISPLAY''删除10000''在控制台上
执行DB2-DELETE-TODAY
END -PERFORM
PERFORM COMMIT-UOW
DISPLAY''删除流程结束''在控制台上
。
DB2-DELETE-TODAY SECTION。
EXEC SQL
DELETE FROM（
SELECT UPDATE_DATE
FROM FILM.FILM_TRANSACTIONS
WHERE UPDATE_DATE = FB_FUNC.TO_DATE（：CURR-DATE-JUL-PACKED）
FETCH FIRST 10000行只有
）
WHERE 1 = 1
END-EXEC
使用SQLCA ERRLOC打电话给CHECKERR
。
我的问题是，这是通往去还是有更好的方法吗？
我试过制作10000一个主变量，但是没有用。任何方式
围绕这个？
你可能想知道为什么我把WHERE 1 = 1 DELETE
语句中的条款。这是因为如果您在没有WHERE子句的情况下预编译
DELETE
或UPDATE语句，DB2会发出警告。仍然有效，但我喜欢
避免警告。
谢谢！
Frank
---
Frank Swarbrick
高级开发人员/分析师 - 大型机应用程序
FirstBank Data Corporation - 美国科罗拉多州莱克伍德
We have a batch process that inserts large numbers (100,000 - 1,000,000)
of
records into a database each day. (DL/I database.) We''re considering
converting it to a DB2 table. Currently we have logic in place that,
prior
to inserting any data, reads the first input record and checks to see if
it
already exists in the table. If the record already exists there are two
options:
1) Don''t continue, because you already ran this job today!
2) This is a rerun - continue.
If number 2 is selected the first thing that happens is that it deletes
every record that was inserted today prior to doing the regular insert
process. (You may ask, why not just skip over the ones that are already
there. It''s because we may be rerunning with an updated input file, where
the input records may be different than during the first run.)
Anyway, I figured with DB2 this would be a snap. I''ll I''d need to do is:
EXEC SQL
DELETE FROM FILM.FILM_TRANSACTIONS
WHERE UPDATE_DATE = FB_FUNC.TO_DATE(:CURR-DATE-JUL-PACKED)
END-EXEC
The only problem is that my log file would end up running out of room. So
now I''ve come up with the following:
DELETE-TODAY SECTION.
DISPLAY ''DELETE PROCESS BEGINS'' UPON CONSOLE
PERFORM WITH TEST AFTER
UNTIL SQLCODE = 100
DISPLAY ''COMMITTING...'' UPON CONSOLE
PERFORM COMMIT-UOW
DISPLAY ''DELETING 10000'' UPON CONSOLE
PERFORM DB2-DELETE-TODAY
END-PERFORM
PERFORM COMMIT-UOW
DISPLAY ''DELETE PROCESS ENDS'' UPON CONSOLE
.
DB2-DELETE-TODAY SECTION.
EXEC SQL
DELETE FROM (
SELECT UPDATE_DATE
FROM FILM.FILM_TRANSACTIONS
WHERE UPDATE_DATE = FB_FUNC.TO_DATE(:CURR-DATE-JUL-PACKED)
FETCH FIRST 10000 ROWS ONLY
)
WHERE 1 = 1
END-EXEC
CALL CHECKERR USING SQLCA ERRLOC
.
My question is, is this the way to go or is there some better way?
I tried making the "10000" a host variable, but that didn''t work. Any way
around this?
You may wondering why I put the "WHERE 1 = 1" clause on the DELETE
statement. This is because DB2 gives a warning if you pre-compile a
DELETE
or UPDATE statement without a WHERE clause. Still works, but I like to
avoid warnings.
Thanks!
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
是的，有一种更好的方法可以避免填写DB2 z / OS日志。
使用以下内容声明游标：
SELECT UPDATE_DATE来自FILM.FILM_TRANSACTIONS
WHERE UPDATE_DATE = FB_FUNC.TO_DATE（：CURR-DATE-JUL-PACKED）FOR
更新持有
OPEN游标名称。
然后每次FETCH一行（直到RC = 100）。
删除C1的当前位置（光标名称）。
COMMIT。如果你不想每行支付
行，你可以提交100 - 10000.
关闭光标名。
请注意，即使你已经提交了
（通常会关闭光标），WITH HOLD也会保留光标位置。
请请参阅SQL参考了解详细信息，因为我从（我的）内存中发布了所有
语法。
另外，你发现它很少比每100次更新更频繁地提供更多的性能。更频繁地提交
可以提高与其他应用程序的并发性。
Yes there is a better way that will avoid filling up your DB2 z/OS logs.
Declare a cursor with something like:
SELECT UPDATE_DATE FROM FILM.FILM_TRANSACTIONS
WHERE UPDATE_DATE = FB_FUNC.TO_DATE(:CURR-DATE-JUL-PACKED) FOR
UPDATE WITH HOLD
OPEN cursor-name.
Then FETCH one row at a time (until RC = 100).
DELETE WHERE CURRENT OF C1 (cursor name).
COMMIT. You could commit every 100 - 10000 if you don''t want to commit each
row.
CLOSE cursor-name.
Note that the WITH HOLD will retain the cursor position even though you have
committed (which usually closes a cursor).
Please consult the SQL Reference for details, because I am posting all the
above syntax from (my) memory.
Also, you find that there is very little increase in performance in
committing more often than every 100 updates. Committing more often will
provide increased concurrency with other applications.
Mark A < no **** @ nowhere.com写了留言
新闻：pO ************************** ****@comcast.com。 ..
"Mark A" <no****@nowhere.comwrote in message
news:pO******************************@comcast.com. ..
此外，您发现
的性能几乎没有每100次更新提交。更频繁地提交将使b / b $ b与其他应用程序提供更高的并发性。
Also, you find that there is very little increase in performance in
committing more often than every 100 updates. Committing more often will
provide increased concurrency with other applications.
更正，你会发现，每隔100次更新，经常提交LESS的性能几乎没有增加。
Correction, you find that there is very little increase in performance in
committing LESS often than every 100 updates.
我认为这两种解决方案都有其优点。弗兰克，你的版本非常像
，就像Serge的SQL on Fire中概述的方法一样。研讨会，虽然在
COBOL而不是SQL / PL。 Mark的解决方案将比你的使用更少的日志空间
，但我恭敬地想知道当他们正在删除所有目标行时所需的时间
获取和删除
一次一个。
FWIW，这是'我是怎么做的（在存储过程中） ：
CREATE PROCEDURE CSE.PURGE_LRD（）
BEGIN
DECLARE V_NO_DATA SMALLINT DEFAULT 0; -
DECLARE V_DEADLOCK_OR_LTO SMALLINT DEFAULT 0; -
DECLARE C_DEADLOCK_OR_LTO SQLSTATE''40001''的条件; -
DECLARE CONTINUE HANDLER没有找到
SET V_NO_DATA = 1; -
- V_DEADLOCK_OR_LTO属性是扔掉的，
- 但是一个继续处理程序需要做一些事情，
- 也就是说，仅仅声明一个处理程序是不够的，
- 它有在它的身体里有一个动作。
DECLARE CONTINUE HANDLER for C_DEADLOCK_OR_LTO
SET V_ DEADLOCK_OR_LTO = 1; -
WHILE（V_NO_DATA = 0）DO
DELETE FROM
（
选择
1
来自
LRD
FETCH FIRST仅200000行
）AS LRD_D; -
COMMIT; -
END WHILE; -
END;
- 杰夫
1月24日下午3:44，马克A < nob ... @ nowhere.comwrote：
I think both solutions have merit. Frank, your version is very much
like the approach outlined in Serge''s "SQL on Fire" seminars, albeit in
COBOL as opposed to SQL/PL. Mark''s solution will use less log space
than yours, but I respectfully wonder about the time required to delete
all of the target rows when they''re being fetched and deleted
one-at-a-time.
FWIW, here''s how I''ve done it (in a stored procedure):
CREATE PROCEDURE CSE.PURGE_LRD()
BEGIN
DECLARE V_NO_DATA SMALLINT DEFAULT 0;--
DECLARE V_DEADLOCK_OR_LTO SMALLINT DEFAULT 0;--
DECLARE C_DEADLOCK_OR_LTO CONDITION FOR SQLSTATE ''40001'';--
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET V_NO_DATA = 1;--
-- The V_DEADLOCK_OR_LTO attribute is throw-away,
-- but a continue handler needs to do something,
-- i.e., it''s not enough to just declare a handler,
-- it has to have an action in its body.
DECLARE CONTINUE HANDLER FOR C_DEADLOCK_OR_LTO
SET V_DEADLOCK_OR_LTO = 1;--
WHILE (V_NO_DATA = 0) DO
DELETE FROM
(
SELECT
1
FROM
LRD
FETCH FIRST 200000 ROWS ONLY
) AS LRD_D;--
COMMIT;--
END WHILE;--
END;
--Jeff
On Jan 24, 3:44 pm, "Mark A" <nob...@nowhere.comwrote:
" Mark A" < nob ... @newhere.comwrote in messagenews：pO ****************************** @ comca st.com。 ..
"Mark A" <nob...@nowhere.comwrote in messagenews:pO******************************@comca st.com...
此外，您发现
的性能几乎没有每100次更新提交。更经常地提交
可以提高与其他应用程序的并发性。修正，你发现
中的性能几乎没有增加
Also, you find that there is very little increase in performance in
committing more often than every 100 updates. Committing more often will
provide increased concurrency with other applications.Correction, you find that there is very little increase in performance in
承诺LESS通常比每100次更新。
committing LESS often than every 100 updates.
这篇关于删除大量记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
