genesys.chunk_log table

[Pelly]

Hi everyone. I seem to have cracked the main purging part of my problem now, however there is still one table on the datamart database that is not getting purged. This is the genesys.chunk_log table. I am assuming that these records are signifying the chunks passed to the datamart from the ODS db? If this is the case, once the chunks have been dealt with by datamart, can these records in the chunk_log be deleted? Does anyone know if the records in this table are referenced any where else in the database or used for the reports in brio and would hence stop me from deleting them?

Thanks in advance again for any assistance you may be able to give.
Pelly

[Vic]

Pelly, PLEASE READ THIS ONE CAREFULLY BEFORE EVEN THINKING ABOUT DOING ANYTHING:

CHUNK_LOG is very important for troubleshooting CCA problems. You will need to corellate your work with OL_CHUNK_LOG table to make sure that you do not delete something you shouldn't.


Look in OL_CHUNK_LOG in ODS:

1. Don't ever delete the last record. Data Sourcer needs it.
2. Don't touch TRANSFERRED_TIME=null records.
3. Don't touch DELETE_TIME=null record. (be careful, Genesys sometimes gives wrong reply on this one, so better double check it!)

Ok, when you decide to delete it, you should first find your stoppoint. Use this select for this:

SELECT MAX(log_id) FROM ol_chunk_log WHERE begin_time < '2004/04/01'
AND transferred_time is not Null AND delete_time is not Null

then you could plug the output of that into:
DELETE FROM OL_CHUNK_LOG WHERE log_id < add_that_value_here AND transferred_time is not Null AND delete_time is not Null

AFTER THAT you can dump your DataMart CHUNK_LOG BUT....

READ READ READ DO NOT SKIP DO NOT SKIP DO NOT SKIP
(YOU ARE NOT SKIPPING, RIGHT?)

Only drop from CHUNK_LOG the chunks that you have already deleted from OL_CHUNK_LOG:
LOG_IDs from OL_CHUNK_LOG are the same as SRC_CHUNK_ID in the CHUNK_LOG table.


DELETE FROM chunk_log WHERE SRC_CHUNK_ID < that_number_you_got_before

NOTE THAT: THIS IS FOR ONE ODS ONLY. IF YOU HAVE MORE THAN ONE THEN YOU NEED TO TAKE CARE of defining the source in the previos DELETE statement by adding "AND source_id = source_id_here".

As you have probably already noticed, these select and delete statements could be combined into one. If you do that, post it here, ok?

[Pelly]

Vic, thanks for the info, I will pass on to our Database guys for them to look at and will let you know how things go.
Pelly