Maximo Open Forum

Hi Deepika,

Thanks for asking this question. It's taken time to think through the solution's design. Your success with this Maximo Integration Framework (MIF) architectural hurdle could set it up as an IDEA candidate.

By default, no standard Maximo table or out-of-the-box MBO permanently stores the raw S3 filename because the LOADFLATOBJECT cron task processes files in memory and disintegrates them into independent transactional record payloads before they are written to the database.

However, a custom solution to completely satisfy your audit requirements without creating a custom table (DATALOADAUDIT) or managing complex custom counters can be implemented by resolving the requirement by deploying a Maximo native dual-layer architecture solution:

1. The Writer (Automation Script): Captures the volatile S3 filename in-flight from the MIF context (with a fallback for multi-chunk streams) and stamps it into the native tracking log (MAXINTMSGTRK.MEMO).
2. The Reader (Db2 SQL Query): Groups the processed records by the captured filename and uses pure Db2 string functions to calculate your totals and durations on demand dynamically.

Here is the complete step-by-step blueprint to configure this in MAS 9:

This lightweight script catches the S3 filename in real-time and securely records it into the integration logging framework.

1. Go to the Automation Scripts application in MAS 9.
2. Select Actions > Create > Script for Integration.
3. Set the integration type to Enterprise Service.
4. Select the custom Enterprise Service handling your AWS S3 flat-file data ingestion.
5. Set the Processing Point to: Inbound User Exit (Before External Mapping).
6. Select Python (Jython) as the engine, ensure the Active box is checked, and paste the code below:

# Launch Point: Enterprise Service Inbound User Exit (Before External Mapping)
# Purpose: Capture volatile S3 filename from memory and stamp it into the transaction context log memo.

from psdi.iface.util import MICUtil

fileName = None

# 1. Primary Attempt: Extract the filename from the immediate thread context
if ctx.get("FILENAME") is not None:
    fileName = ctx.get("FILENAME")

# 2. Fallback Attempt: If a multi-chunk stream shifted context, pull from the MIF header properties
if not fileName and micHeader is not None:
    if micHeader.getProperty("FILENAME") is not None:
        fileName = micHeader.getProperty("FILENAME")

# 3. Process and securely record the file name metadata
if fileName:
    fileNameStr = str(fileName)
    
    # Isolate the file name from potential bucket directory paths
    if "/" in fileNameStr:
        fileNameStr = fileNameStr.split("/")[-1]
    if "\\" in fileNameStr:
        fileNameStr = fileNameStr.split("\\")[-1]
        
    # Strip the file format extension (e.g., .csv, .txt) if present
    if "." in fileNameStr:
        fileNameStr = fileNameStr.split(".")[0]
        
    # Inject the cleaned filename directly into the MAXINTMSGTRK.MEMO database column
    ctx.put("memo", fileNameStr)

Once the script saves the filename to the MEMO column, you can query it seamlessly. This query handles your exact RELEASEID-RUNNAME-OBJECT hyphenated naming layout and provides an automatic text narrative for auditors.

Run the following script in your database administrator client to create a virtual audit table:

SET SCHEMA = 'MAXIMO';

CREATE VIEW v_mif_data_load_audit AS
SELECT 
    -- 1. Parse File Naming Conventions using Pure Db2 String Expressions
    SUBSTR(msgtr.memo, 1, LOCATE('-', msgtr.memo) - 1) AS "Release Name",
    SUBSTR(msgtr.memo, LOCATE('-', msgtr.memo) + 1, LOCATE('-', msgtr.memo, LOCATE('-', msgtr.memo) + 1) - LOCATE('-', msgtr.memo) - 1) AS "Run Name",
    SUBSTR(msgtr.memo, LOCATE('-', msgtr.memo, LOCATE('-', msgtr.memo) + 1) + 1) AS "Object",

    -- 2. Audit Record Metric Tallies
    COUNT(msgtr.meamsgid) AS "Total Records",
    SUM(CASE WHEN msgtr.status = 'PROCESSED' THEN 1 ELSE 0 END) AS "Imported Records",
    SUM(CASE WHEN msgtr.status = 'SKIPPED'   THEN 1 ELSE 0 END) AS "Skipped Records",
    SUM(CASE WHEN msgtr.status = 'ERROR'     THEN 1 ELSE 0 END) AS "Error Records",

    -- 3. Execution Chronology & Duration Calculations
    MIN(hist.starttime) AS "Start Time",
    MAX(hist.endtime)   AS "End Time",
    ROUND(AVG((HOUR(hist.endtime - hist.starttime) * 60) + MINUTE(hist.endtime - hist.starttime)), 2) AS "Total Duration (Minutes)",

    -- 4. Custom Compliance Audit Narrative String
    'A total of ' || COUNT(msgtr.meamsgid) || ' record(s) were processed for Release ' || 
    SUBSTR(msgtr.memo, 1, LOCATE('-', msgtr.memo) - 1) || ' (Run: ' || 
    SUBSTR(msgtr.memo, LOCATE('-', msgtr.memo) + 1, LOCATE('-', msgtr.memo, LOCATE('-', msgtr.memo) + 1) - LOCATE('-', msgtr.memo) - 1) || 
    '). Success: ' || SUM(CASE WHEN msgtr.status = 'PROCESSED' THEN 1 ELSE 0 END) || 
    ', Errors: ' || SUM(CASE WHEN msgtr.status = 'ERROR' THEN 1 ELSE 0 END) || '.' AS "Audit Narrative"

FROM maxintmsgtrk msgtr
INNER JOIN crontaskhistory hist 
    ON msgtr.correlationid = hist.correlationid
WHERE hist.crontaskname = 'LOADFLATOBJECT'
  AND msgtr.memo IS NOT NULL 
  AND msgtr.memo LIKE '%-%-%' -- Guarantees file matches the target format
GROUP BY 
    msgtr.memo;

Once published, your compliance or audit team can pull the exact reporting layout cleanly by executing simple SELECT commands on the view without touching raw integration logs:

SELECT * FROM v_mif_data_load_audit WHERE "Release Name" = 'YOUR-RELEASE-ID';

• Zero Overhead: Eliminates the need to build, sync, and index a custom DATALOADAUDIT tracking table inside Maximo Database Configuration.
• Full Transactional Integrity: By utilizing the native MAXINTMSGTRK tables, Maximo safely handles database rollbacks if the crontask fails or errors mid-stream, preserving fully accurate metrics.

Hope this helps! Let me know if you have any follow-up questions. 

Best regards,