Well, I'm hoping you were updating the table names for security purposes, and that's the error below. In this example, the table you're using for the SELECT (ASN.ERSHIST_XXXXX) is not the same table that you specify in the DELETE (ASN.CDERS_HISTORY).
Nora -----Original Message----- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 1:38 PM To: IBMVM@LISTSERV.UARK.EDU 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
