Thanks Danny, that was helpful.
I created my own views on the XMLDATA column of the OPTIM_TRANSACTION_CONTENT table, it is a lot easier to write custom archive process reports now.
SET CURRENT SCHEMA = SCHEMANAME;
---------------- OTC_BASE_VIEW
CREATE OR REPLACE VIEW OTC_BASE_VIEW
AS
SELECT
OAR.ID,
OAR.OBJECT_REF,
OTCX.DIRECTORY_NAME,
OTCX.DIRECTORY_DESCRIPTION,
OTCX.SERVER_NAME,
OTCX.TIME_STARTED,
OTCX.TIME_FINISHED,
OTCX.TIME_ELAPSE,
OTCX.REQUEST,
OTCX.REQUEST_DESCRIPTION,
OTCX.REQUEST_MODIFY_DATE,
OTCX.ACCESS_DEFINITION,
OTCX.ACCESS_DEFINITION_DESCRIPTION,
OTCX.ACCESS_DEFINITION_MODIFY_DATE,
OTCX.ARCHIVE_FILE_GUID,
OTCX.ARCHIVE_FILE_DATA_BYTE_COUNT,
OTCX.ARCHIVE_FILE_COMPRESSION_RATIO,
OTCX.ARCHIVE_FILE_AUTO_DELETE,
OTCX.ARCHIVE_FILE_NAME,
OTCX.ARCHIVE_FILE_HOSTNAME,
OTCX.ARCHIVE_FILE_TOTAL_SIZE_BYTES,
OTCX.SUMMARY_TOTAL_TABLES,
OTCX.STORAGE_PROFILE_STORAGE_TYPE,
OTCX.STORAGE_PROFILE_NAME,
OTCX.STORAGE_PROFILE_MODIFY_DATE,
OTCX.STORAGE_PROFILE_RETENTION_START,
OTCX.STORAGE_PROFILE_RETENTION_EXPIRATION
FROM
OPTIM_AUDIT_RECORDS OAR,
OPTIM_TRANSACTION_CONTENT OTC,
XMLTABLE('$n/*:OptimReport' PASSING OTC.XMLDATA AS "n"
COLUMNS
DIRECTORY_NAME VARCHAR(11) PATH '*:CommonContent/*:directory/@name',
DIRECTORY_DESCRIPTION VARCHAR(256) PATH '*:CommonContent/*:directory/@description',
SERVER_NAME VARCHAR(12) PATH '*:CommonContent/*:overview/@serverName',
TIME_STARTED TIMESTAMP PATH '*:CommonContent/*:overview/@timeStarted',
TIME_FINISHED TIMESTAMP PATH '*:CommonContent/*:overview/@timeFinished',
TIME_ELAPSE INTEGER PATH '*:CommonContent/*:overview/@timeElapse',
REQUEST VARCHAR(20) PATH '*:ArchiveReport/*:request/@name',
REQUEST_DESCRIPTION VARCHAR(20) PATH '*:ArchiveReport/*:request/@description',
REQUEST_MODIFY_DATE TIMESTAMP PATH '*:ArchiveReport/*:request/@modifyDate',
ACCESS_DEFINITION VARCHAR(20) PATH '*:ArchiveReport/*:accessDefinition/@name',
ACCESS_DEFINITION_DESCRIPTION VARCHAR(20) PATH '*:ArchiveReport/*:accessDefinition/@description',
ACCESS_DEFINITION_MODIFY_DATE TIMESTAMP PATH '*:ArchiveReport/*:accessDefinition/@modifyDate',
ARCHIVE_FILE_GUID VARCHAR(40) PATH '*:ArchiveReport/*:archiveFile/@fileGUID',
ARCHIVE_FILE_DATA_BYTE_COUNT BIGINT PATH '*:ArchiveReport/*:archiveFile/@dataByteCount',
ARCHIVE_FILE_COMPRESSION_RATIO SMALLINT PATH '*:ArchiveReport/*:archiveFile/@compressionRatio',
ARCHIVE_FILE_AUTO_DELETE VARCHAR(5) PATH '*:ArchiveReport/*:archiveFile/@autoDelete',
ARCHIVE_FILE_NAME VARCHAR(1024) PATH '*:ArchiveReport/*:archiveFile/@name',
ARCHIVE_FILE_HOSTNAME VARCHAR(12) PATH '*:ArchiveReport/*:archiveFile/@hostname',
ARCHIVE_FILE_TOTAL_SIZE_BYTES BIGINT PATH '*:ArchiveReport/*:archiveFile/@totalSizeInBytes',
SUMMARY_TOTAL_TABLES INTEGER PATH '*:ArchiveReport/*:summary/@totalTables',
STORAGE_PROFILE_STORAGE_TYPE VARCHAR(10) PATH '*:ArchiveReport/*:storageProfile/@storageType',
STORAGE_PROFILE_NAME VARCHAR(12) PATH '*:ArchiveReport/*:storageProfile/@name',
STORAGE_PROFILE_MODIFY_DATE TIMESTAMP PATH '*:ArchiveReport/*:storageProfile/@modifyDate',
STORAGE_PROFILE_RETENTION_START DATE PATH '*:ArchiveReport/*:storageProfile/*:RelativeToArchiveDate/@retentionStart',
STORAGE_PROFILE_RETENTION_EXPIRATION DATE PATH '*:ArchiveReport/*:storageProfile/*:RelativeToArchiveDate/@retentionExpiration'
) AS OTCX
WHERE
OAR.AUDIT_TYPE='PROCESS_REPORT_XML' AND
OAR.ID=OTC.ID;
---------------- OTC_ENTITY_VIEW
CREATE OR REPLACE VIEW OTC_ENTITY_VIEW
AS
SELECT
OAR.ID,
OAR.OBJECT_REF,
OTCX.TYPE,
OTCX.NAME,
OTCX.RECORD_COUNT,
OTCX.DATA_BYTE_COUNT
FROM
OPTIM_AUDIT_RECORDS OAR,
OPTIM_TRANSACTION_CONTENT OTC,
XMLTABLE('$n/*:OptimReport/*:ArchiveReport/*:entities/*:entity' PASSING OTC.XMLDATA AS "n"
COLUMNS
TYPE VARCHAR(5) PATH '@type',
NAME VARCHAR(256) PATH '@name',
RECORD_COUNT INTEGER PATH '@recordCount',
DATA_BYTE_COUNT INTEGER PATH '@dataByteCount'
) AS OTCX
WHERE
OAR.AUDIT_TYPE='PROCESS_REPORT_XML' AND
OAR.ID=OTC.ID;
---------------- OTC_STATISTICS_VIEW
CREATE OR REPLACE VIEW OTC_STATISTICS_VIEW
AS
SELECT
OAR.ID,
OAR.OBJECT_REF,
OTCX.WHERE_CLAUSE,
OTCX.NAME,
OTCX.COLUMN_COUNT,
OTCX.LOB_COLUMNS,
OTCX.ROW_LENGTH,
OTCX.ROWS_FETCHED,
OTCX.ROWS_WRITTEN,
OTCX.PROCESS_TIME,
OTCX.ROWS_PER_SECOND
FROM
OPTIM_AUDIT_RECORDS OAR,
OPTIM_TRANSACTION_CONTENT OTC,
XMLTABLE('$n/*:OptimReport/*:ArchiveReport/*:statistics/*:statistic' PASSING OTC.XMLDATA AS "n"
COLUMNS
WHERE_CLAUSE VARCHAR(256) PATH '@whereClause',
NAME VARCHAR(256) PATH '@name',
COLUMN_COUNT SMALLINT PATH '@columnCount',
LOB_COLUMNS VARCHAR(5) PATH '@lobColumns',
ROW_LENGTH INTEGER PATH '@rowLength',
ROWS_FETCHED INTEGER PATH '*:dbmsAccess/@rowsFetched',
ROWS_WRITTEN INTEGER PATH '*:dbmsAccess/@rowsWritten',
PROCESS_TIME VARCHAR(20) PATH '*:dbmsAccess/@processTime',
ROWS_PER_SECOND INTEGER PATH '*:dbmsAccess/@rowsPerSecond'
) AS OTCX
WHERE
OAR.AUDIT_TYPE='PROCESS_REPORT_XML' AND
OAR.ID=OTC.ID;
---------------- OTC_ERRORS_VIEW
CREATE OR REPLACE VIEW OTC_ERRORS_VIEW
AS
SELECT
OAR.ID,
OAR.OBJECT_REF,
OTCX.MESSAGE
FROM
OPTIM_AUDIT_RECORDS OAR,
OPTIM_TRANSACTION_CONTENT OTC,
XMLTABLE('$n/*:OptimReport/*:ArchiveReport/*:errorMessages' PASSING OTC.XMLDATA AS "n"
COLUMNS
MESSAGE VARCHAR(1024) PATH '*:message/@message'
) AS OTCX
WHERE
OAR.AUDIT_TYPE='PROCESS_REPORT_XML' AND
OAR.ID=OTC.ID;
---------------- OTC USER QUERY EXAMPLE
SELECT
OBV.ID,
OBV.SERVER_NAME,
OBV.OBJECT_REF,
OBV.TIME_STARTED,
OBV.TIME_FINISHED,
OBV.TIME_ELAPSE,
OBV.ARCHIVE_FILE_DATA_BYTE_COUNT,
OBV.SUMMARY_TOTAL_TABLES,
OBV.STORAGE_PROFILE_RETENTION_START,
OBV.STORAGE_PROFILE_RETENTION_EXPIRATION,
OEV.NAME,
OEV.RECORD_COUNT,
OEV.DATA_BYTE_COUNT,
OSV.WHERE_CLAUSE,
OSV.COLUMN_COUNT,
OSV.LOB_COLUMNS,
OSV.ROW_LENGTH,
OSV.PROCESS_TIME,
OSV.ROWS_PER_SECOND,
ORV.MESSAGE
FROM
OTC_BASE_VIEW OBV JOIN OTC_ENTITY_VIEW OEV ON OBV.ID=OEV.ID
JOIN OTC_STATISTICS_VIEW OSV ON OBV.ID=OSV.ID
LEFT OUTER JOIN OTC_ERRORS_VIEW ORV ON OBV.ID=ORV.ID
WHERE
OEV.NAME=OSV.NAME
ORDER BY
OBV.TIME_STARTED DESC;
------------------------------
Elder Lira
------------------------------
Original Message:
Sent: Mon June 03, 2019 10:41 AM
From: Danny Lankford
Subject: Archive Process Report
Elder,
The process reports are stored in .xml data on the OPTIM_TRANSACTION_CONTENT table.
The OPTIM_DATA_CONSUMPTION_VIEW calls this table. You can steal the SQL from that view.
------------------------------
Danny Lankford
Advanced IT Specialist
3M
St Paul MN
Original Message:
Sent: Mon June 03, 2019 10:16 AM
From: Elder Lira
Subject: Archive Process Report
Hi,
I have been trying to write a SQL against the Optim Directory to get the amount of rows archived for each table of a given archive request.
Does anyone know what columns and tables from the Optim Directory would contain the data needed to build this query?
Thanks,
Elder
------------------------------
Elder Lira
------------------------------
#InfoSphereOptim
#Optim