On Tue, 16 Jun 2009 14:13:03 -0700, Bill Pettit <bi...@ormutual.com> wrot e:
>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; <=== l ock 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 rec ords. 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 terminat ing the process that is adding records, not to loose records in the merge/purge process? > >Thanks > >Tom Duerbusch >THD Consulting >======================== ========================= ========== ============= You could add a FLAG, normally NULL, to mark the records you plan to ins ert and then delete. That would ignore the added records. UPDATE ASN.CDERS_HISTORY SET FLAG=1; INSERT INTO STL01.ERS_HISTORY_A SELECT * FROM ASN.ERSHIST_XXXXX WHERE FLAG=1; DELETE FROM ASN.CDERS_HISTORY WHERE FLAG=1; Alan Ackerman Alan (dot) Ackerman (at) Bank of America (dot) com