On Tue, 16 Jun 2009 14:13:03 -0700, Bill Pettit <[email protected]> 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
