Dear all,
I would like to use SQL's log file to extract information from it.
This file can include four different types of instruction with the number of lines involved for each of them:
-> (1) "INSERT" instruction with the number of lines inserted
-> (2) "UPDATE" instruction with the number of lines updated
-> (3) "DELETE" instruction with the number of lines deleted
-> (4) "COLLECT STATISTICS" instruction with the number of lines involved.
The number of lines involved for "UPDATE" and "COLLECT STATISTICS" has exactly the same appearance in the log file:
-> for "COLLECT STATISTICS" instruction (check "Update completed" below)
COLLECT STATISTICS COLUMN (STD_CDH_ID)
, COLUMN (PARTITION)
, COLUMN (METDVERS_OID_ID)
-- AXE ITEMGROUP
, COLUMN (ITEMGROUP_OID_CD)
, COLUMN (STD_CDH_ID, METDVERS_OID_ID, ITEMGROUP_OID_CD)
-- AXE VISIT
, COLUMN (VISIT_OID_CD)
, COLUMN (STD_CDH_ID, METDVERS_OID_ID, VISIT_OID_CD)
-- AXE FORM
, COLUMN (FORM_OID_CD)
, COLUMN (STD_CDH_ID, METDVERS_OID_ID, FORM_OID_CD)
-- AXE SITE
, COLUMN (SITE_OID_CD)
, COLUMN (STD_CDH_ID, METDVERS_OID_ID, SITE_OID_CD)
-- AXE SUBJCT
, COLUMN (SUBJCT_KEY_ID)
, COLUMN (STD_CDH_ID, METDVERS_OID_ID, SUBJCT_KEY_ID)
ON CDH_STGN_DEV1.RECRD_SHREDDED
;
*** Update completed. 14 rows changed.
*** Total elapsed time was 4 seconds.
-> for "UPDATE" instruction (check "Update completed" below)
UPDATE
TGT
FROM
CDH_REJT_DEV1.RAVE_LNKITEMGROUPITEM TGT
, CDH_STGN_DEV1.LNKITEMGROUPITEM SRC
SET
LNKITEMGROUPITEM_REJTDELTD_TS = CURRENT_TIMESTAMP(0)
WHERE
TGT.STD_CDH_ID = SRC.STD_CDH_ID
AND TGT.METDVERS_OID_ID = SRC.METDVERS_OID_ID
AND TGT.ITEM_OID_CD = SRC.ITEM_OID_CD
AND TGT.LNKITEMGROUPITEM_REJTDELTD_TS IS NULL
;
*** Update completed. No rows changed.
*** Total elapsed time was 1 second.
The problem I face is the following: I am not interested by the number of lines involved in the "COLLECT STATISTICS" instruction and I am not able to recognize from the log file what kind of expression - "COLLECT STATISTICS" or "UPDATE" - produced "Update completed" in the log file !
So, I thought to cut off from the log file the multi-line record starting with "COLLECT STATISTICS" and ending with "changed." ... but I failed, I failed, I failed !
Thanks in advance for the attention you pay to my request and obviously, any suggestion over welcomed,
Didier.