Thanks all. Interesting that everyone seems to suggest writing code to get around this problem.
I would have thought that there would have been a DB2 method, which I didn't know about, that would, in the same LUW, with a locked DBSPACE, given me the same record selection set for the delete, as was obtained for the insert. Between Repeatable Read and locking the DBSPACE, I thought I should have been given the same selection set within the same LUW. Not a happy camper at this point <G>. Tom Duerbusch THD Consulting >>> "Kevin Corkery" <kcork...@live.com> 6/16/2009 1:03 PM >>> You need to create a delta table. Into an empty delta, select all records of interest from the source table. Insert all records from the delta table to the target table. Use the delta table as a basis for deletion of records from the source table. Not really a DB2 type but I have a similar scenerio with SQLServer and VSAM using ViaSQL; good ol' batch processing mentality at work here :-) -----Original Message----- From: owner-vs...@lehigh.edu [mailto:owner-vs...@lehigh.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 1:38 PM To: VSE Discussion List Subject: DB2 Problem I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem <G>. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ ------> CONNECT "SYSA " IDENTIFIED BY ********; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0 ------> ------> COMMENT 'PAYROLL SYSTEM' ------> ------> LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE; <=== lock the dbspace of ershist_xxxxxx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0 ------> INSERT INTO STL01.ERS_HISTORY_A ------> SELECT * FROM ASN.ERSHIST_XXXXX; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 30000 <=== I've inserted 30000 records ------> ------> DELETE FROM ASN.CDERS_HISTORY ------> ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 <=== I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE ------> COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0 ------> SET ERRORMODE OFF; ARI0899I ...Command ignored. ------> ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK, and wait (perhaps till -911, in which case it will delay and restart), but the lock didn't seem to do the trick. Between Repeatable Read and Locking the DBSPACE didn't do what I needed. Is there another option, without taking down the database to Single User Mode, or terminating the process that is adding records, not to loose records in the merge/purge process? Thanks Tom Duerbusch THD Consulting
