Recovering lost UTIL G 2 counters using SQL flashback query
- Product: Aleph
- Product Version: 20, 21, 22, 23
- Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care
Description:
This morning at around 10:50 we accidentially called up UTIL G 3, causing all UTIL G 2 counters to be reset to 0. How can we get a list of previous counter values?
Resolution:
A SQL flashback query can be used to retrieve the Z52 settings per 10:50 this morning:
abc50@ALEPH1> select * from z52
2 as of timestamp
3 to_timestamp ('11-06-2012 10:50:00','dd-mm-yyyy hh24:mi:ss');
**** Hit return to continue ****
Z52_REC_KEY Z52_PREFIX Z52_SEQUENCE Z Z52_FORMAT Z
-------------------- -------------------- ------------ - ------------------------------ -
call-number CN- 0 n S
change-file-name 0 y S
last-acc-number 0 Y S
last-barcode-number 221294 Y S
last-bor-id ID 159010 y S
last-bor-id-1 B 0 y S
last-bor-verify-1 P 1 y S
last-bor-verify-2 P1 0 y S
last-bor-verify-3 P2 0 y S
last-cash-receipt-no No. 286233 y S
last-doc-number 10351526 S
**** Hit return to continue ****
Z52_REC_KEY Z52_PREFIX Z52_SEQUENCE Z Z52_FORMAT Z
-------------------- -------------------- ------------ - ------------------------------ -
last-edi-log-no 1831 Y S
last-edi-message 2409 y S
last-edi-order 2588 y S
last-file-number usm50 2674650 y S
last-hold-group 35780 y S
last-loan-number 3559920 y S
last-order-no-ser SER- 1114 y U
last-order-number 113173 y S
last-photo-number 0 y S
last-req-no DS 43650 y S
last-slip-number usm50 141636 y S
**** Hit return to continue ****
Z52_REC_KEY Z52_PREFIX Z52_SEQUENCE Z Z52_FORMAT Z
-------------------- -------------------- ------------ - ------------------------------ -
last-z34-sequence 0 y S
library-lock-status 0 n S
test-shelf 0 Y S
last-request-number 153606 Y S
last-order-number-no no. 0 Y S
location-usig USIG 0 Y S
last-title-req-num 2 Y S
last-order-number-tx TXT- 120000 Y U
last-frs-batch-id 0 n S
last-shelf-rpt-num no. 3 Y S
32 rows selected.
abc50@ALEPH1>
You can also use another timestamps for your retrieval.
For example to retrieve values per 10:52 use:
select * from z52
2 as of timestamp
3 to_timestamp ('11-06-2012 10:52:00','dd-mm-yyyy hh24:mi:ss');
You may also use a where-statement to restrict the query to a specific line in z52:
select * from z52
2 as of timestamp
3 to_timestamp ('11-06-2012 10:52:00','dd-mm-yyyy hh24:mi:ss')
4 where Z52_REC_KEY = 'last-bor-id-1';
Then UTIL G 2 can be used to manually set the counters to the correct value.
Please note that flashback query is only possible for a restricted period of time depending on the setup of the rollback segment and the number of transactions that has happened in your database.
- Article last edited: 26-Jun-2016