InfoSphere Optim

 View Only
  • 1.  Archive Process Report

    Posted Mon June 03, 2019 10:17 AM
    ​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


  • 2.  RE: Archive Process Report

    Posted Mon June 03, 2019 10:42 AM
    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
    ------------------------------



  • 3.  RE: Archive Process Report

    Posted Tue June 04, 2019 03:05 PM
    ​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
    ------------------------------