Maximo Open Forum

MAM 7.6.1.2:

Is there a way to use an escalation/action to truncate and reload a custom table? (on a nightly schedule)

I would populate the table with the rows from a SQL query (a query that rolls up tasks to WOs). The idea being, a static table that is refreshed nightly would be faster/less load on the system -- compared to constantly calculating rows on-the-fly in a db view (real-time data isn't needed). And the custom related table avoids the need to add 10+ fields to the WORKORDER table...which already has too many fields (250+ OOB fields).

I would prefer to do this from inside Maximo, rather than do it directly in the database via Oracle Scheduler.

I've tried creating a custom action. But I don't see any options that would let me batch delete and insert records.

Thanks.
#Administration

------------------------------
User 1972
NA
------------------------------

I can think of 2 options:
1. You can write a automation script to run sql queries.
2. Create automation script to use mbo delete and mbo add. Mbo add: Logic is to run select query to fetch data from the required table then use mboset loop to add new rows into the custom table.
Mbo delete: fetch all record of the custom table into an mbo then call mboset.deleteall() then call mboset.save()

---------------------------------
Biplab Choudhury
Tata Consultancy Services
---------------------------------



Original Message:
Sent: 10-27-2021
From: User 1972
Subject: Use escalation/action to truncate and reload a custom table?

MAM 7.6.1.2:

Is there a way to use an escalation/action to truncate and reload a custom table? (on a nightly schedule)

I would populate the table with the rows from a SQL query (a query that rolls up tasks to WOs). The idea being, a static table that is refreshed nightly would be faster/less load on the system -- compared to constantly calculating rows on-the-fly in a db view (real-time data isn't needed). And the custom related table avoids the need to add 10+ fields to the WORKORDER table...which already has too many fields (250+ OOB fields).

I would prefer to do this from inside Maximo, rather than do it directly in the database via Oracle Scheduler.

I've tried creating a custom action. But I don't see any options that would let me batch delete and insert records.

Thanks.
#Administration

------------------------------
User 1972
NA
------------------------------

As Biplab mentioned, you can use an automation script that executes direct SQL and that's the approach I would go since this is entirely custom and is reloaded every day. If we're talking a large number of records in the table, you can even use the "truncate table" command which is going to be faster than running a "delete from table" command. I would do this as a cron task automation script instead of calling from an escalation unless there's something else you're also trying to do. 

I would do this utilizing DBShortcut & SqlFormat libraries. I can type up a sample script if you need it.

------------------------------
Steven Shull
IBM
------------------------------

Original Message:
Sent: 10-27-2021 16:16
From: User 1972
Subject: Use escalation/action to truncate and reload a custom table?

MAM 7.6.1.2:

Is there a way to use an escalation/action to truncate and reload a custom table? (on a nightly schedule)

I would populate the table with the rows from a SQL query (a query that rolls up tasks to WOs). The idea being, a static table that is refreshed nightly would be faster/less load on the system -- compared to constantly calculating rows on-the-fly in a db view (real-time data isn't needed). And the custom related table avoids the need to add 10+ fields to the WORKORDER table...which already has too many fields (250+ OOB fields).

I would prefer to do this from inside Maximo, rather than do it directly in the database via Oracle Scheduler.

I've tried creating a custom action. But I don't see any options that would let me batch delete and insert records.

Thanks.
#Administration

------------------------------
User 1972
NA
------------------------------