IBM Maximo Application Suite - updatedb fails with dbchange ScriptFailure

 Reactivation of Manage application fails. Adding the IBM Maximo Health, Safety & Environment and Spatial add-on components and reactivating Manage application results in "dbchange#ScriptFailure" error. 

Logs show error messages as below log entries:

Example 1:

CREATE PROCEDURE PLUSG_AS_6 ( v_pOrgID VARGRAPHIC(8) ) LANGUAGE SQL BEGIN DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; 
// Failed.
ERROR: BMXAA7025E -- Script failed while running.;
BMXAA8313E -- A statement failed to execute inside a freeform statement. See the nested exception.;
DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=PLUSG_AS_7;PROCEDURE, DRIVER=4.26.14
at psdi.oilandgas.en.V7110_97(V7110_97.java: 55)
aused by: BMXAA8313E -- A statement failed to execute inside a freeform statement. See the nested exception.;
DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=PLUSG_AS_7;PROCEDURE, DRIVER=4.26.14
at psdi.oilandgas.en.V7110_97(V7110_97.java: 55)
Caused by: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=PLUSG_AS_7;PROCEDURE, DRIVER=4.26.14
       at com.ibm.tivoli.maximo.dbmanage.connection.LoggingStatement(LoggingStatement.java: 145)
        ... 32 more
rollback;

Example 2:

insert into crontaskdef (crontaskname, classname, description, crontaskdefid, accesslevel, langcode, hasld) select crontaskname, classname, description, crontaskdefid, accesslevel, langcode, hasld from zxcrontaskdef
;
// Failed.

Example 3:

- BMXAA9115I -- The script code inserted only unique records into table CRONTASKDEF.
insert into crontaskdef (crontaskname, classname, description, crontaskdefid, accesslevel, langcode, hasld) values (?, ?, ?, ?, ?, ?, ?)
;
ReportImport, psdi.app.report.ReportImportCronTask, Crontask to import report designs., 424, FULL, EN, 0
// Failed.

Example 4:

-- Product: IBM Maximo Oil and Gas, Last release database version:V7620-96, Next release dabatabase version:V8000-03, DBOILANDGAS:V8000-3
-- WARNING: Property file V8000_props does not exist. Continuing.......
-- WARNING: File classnames.dat for resetting maxobject/maxattribute table does not exist. Continuing.......
-- WARNING: File services.dat for resetting maxservice table does not exist. Continuing.......
-- Product: IBM Maximo Oil and Gas extension update starts...
-- Product: IBM Maximo Oil and Gas extension update complete: SUCCESSFUL
-- Report Version: Last report version for IBM Maximo Health, Safety and Environment not found in product xml, checking report version xml.
-- MESSAGES_PROC Product: DBHSE did not switch over
-- Product: IBM Maximo Health, Safety and Environment, Last release database version:V7620-02, Next release dabatabase version:V8000-01, DBHSE:V7620-1
-- WARNING: Property file V8000_props does not exist. Continuing.......
-- Product: IBM Maximo Health, Safety and Environment script updatedb process starts...
Running scripts for product: IBM Maximo Health, Safety and Environment from #1 to #2, fileVersion: V7620, varName: DBHSE, productDir: hse
             Calling the class file : psdi.hse.en.V7620_02 Mon Apr 04 11:47:03 GMT 2022
   -------------------------------------------------------------------
             Calling the dbchange file : V7620_02 Mon Apr 04 11:47:03 GMT 2022
   -------------------------------------------------------------------
/*
 * freeform: Update Apphelp Links for HSE.
 */
update maxpresentation set presentation = REPLACE(presentation, 'com.ibm.oil.doc', 'com.ibm.hse.doc') where app not in ('LIBRARY','REPLIBRARY')
;

// Failed.

ERROR: BMXAA7025E -- Script failed while running.;
BMXAA8313E -- A statement failed to execute inside a freeform statement. See the nested exception.;
com.ibm.tivoli.maximo.dbmanage.MXExceptionWithDefault: scriptrun#ColumnNameTooLong;
Attributename value from dbc file is longer than the allowed length for a database column name;
DB2 SQL Error: SQLCODE=-433, SQLSTATE=22001, SQLERRMC=<?xml version="1.0" encoding="UTF-8"?>

Example 5:

-- BEGINUPDATEDBERROR
-- Product: IBM Maximo Spatial
-- Error running script: V7603_02
-- UPDATEDBFILE: V7603_02.dbc
-- Script: Error
rollback
;

Example 6:

/*
 * freeform: Create automation scripts Spatial Auto Create
 */
insert into autoscript (autoscript, status, scheduledstatus, comments, ownerid, ownername, ownerphone, owneremail, createdbyid, description, orgid, siteid, "ACTION", "SOURCE", createddate, version, category, statusdate, changedate, createdbyphone, createdbyname, createdbyemail, owner, createdby, changeby, autoscriptid, hasld, langcode, binaryscriptsource, scriptlanguage, userdefined, loglevel, rowstamp, interface, active) values('AUTO_CREATE_SPATIAL_SR_SAVE_ADD', 'Draft', NULL, NULL, NULL, NULL, '(617) 555-9017', 'm.wilson@helwig.com', NULL,'Auto Create Spatial when the Service Request is created.', NULL, NULL, NULL, '/*Sample script '||CHR(13) || CHR(10) || 'Automatically run Auto Create Spatial when the Service Request is created. */'||CHR(13) || CHR(10) || ''||CHR(13) || CHR(10) || 'ticketid = mbo.getString("ticketid");'||CHR(13) || CHR(10) || 'println( "Auto Creating SR Spatial");'||CHR(13) || CHR(10) || 'println("TICKETID: "+ticketid );'||CHR(13) || CHR(10) || 'println("SiteID: "+ticketid);'||CHR(13) || CHR(10) || 'var success = mbo.autoCreateSpatial(true);'||CHR(13) || CHR(10) || 'if (success) {'||CHR(13) || CHR(10) || '    println("SR Spatial successfully created");'||CHR(13) || CHR(10) || '} else {'||CHR(13) || CHR(10) || '    println("SR Spatial creation failed");'||CHR(13) || CHR(10) || '    mbo.getThisMboSet().save();'||CHR(13) || CHR(10) || '    warngroup = "plussgis";'||CHR(13) || CHR(10) || '    warnkey = "autocreate_nofeats_placed_workorderid";'||CHR(13) || CHR(10) || '    warnparams = [ticketid]'||CHR(13) || CHR(10) || '}', current timestamp, NULL, NULL, current timestamp, current timestamp, '(617) 555-9017', NULL, 'm.wilson@helwig.com', 'WILSON', 'WILSON', 'WILSON', NEXTVAL FOR AUTOSCRIPTSEQ, 0, 'EN', HEXTORAW(''), 'javascript', 1, 'INFO', '', 0, 0)
;

// Failed.

ERROR: BMXAA7025E -- Script failed while running.;
BMXAA8313E -- A statement failed to execute inside a freeform statement. See the nested exception.;
DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=CHR;FUNCTION, DRIVER=4.26.14

Resolving The Problem

In the JDBC configuration resource for Manage application, over IBM Maximo Application Suite Administration UI you need to add the below parameters:

currentSchema=<schema name>;currentFunctionPath=<schema name>,SYSIBM,SYSFUN,SYSPROC,SYSIBMADM;

Final JDBC connection URL is:

jdbc:db2://<host>:<port>/<db name>:currentSchema=<schema name>;currentFunctionPath=<schema name>,SYSIBM,SYSFUN,SYS

Reference Link :https://www.ibm.com/support/pages/node/6573217

How to use the Tools API with Maximo Manage

 The tools API was created to facilitate a simple way for clients to access CLI tools from outside of the Red Hat OpenShift cluster.

• icheckerreport – Run Integrity Checker in “report” mode
• icheckerrepair – Run Integrity Checker in “repair” mode
• toolslog - Get a specified tools log or get a list of all tools logs
• managestart - Start all Maximo Manage pods
• managestop – Stop all Maximo Manage pods
• submitUploadLogRequest - Upload logs from Maximo Manage UI, Cron, MEA, or Report pods to S3 Cloud Object Storage
• (Note: this tool runs against the Manage Liberty pods, where the Manage application runs, to collect and upload logs to Cloud Object Storage, unlike CLI tools that run against the MAXINST pod)

Setting up the Environment

There are three things to do before executing commands. 

Set up S3 Cloud Object Storage and configure Maximo properties (used to submit upload log requests for Manage Liberty pod logs).  See the link for more information:

https://www.ibm.com/docs/en/mas-cd/maximo-manage/continuous-delivery?topic=documents-sending-log-output-s3-cloud-object-storage

Configure access to the signature option:

1. In the Security Groups application, search for your security group
2. On the applications tab, search for the Logging application
3. Grant access to the "Load log files to object store" signature option
4. Save the changes and log out and in again to apply the changes

Generate an API key

In Manage 8.4 and newer versions:

1. Navigate to Integration -> API Keys. 
2. Click "Add API Key"
3. Search for the user, and click "Create"

For Manage 8.3 and earlier versions:

1. Navigate to the Administration -> Administration work center
2. Click the link "API Keys"
3. Click "Add API Key"
4. Search for the user, and click "Add"
   
5. Copy the API key
   

This key will be used in the next steps.

Setting up a rest client:

1. In the example shown, Postman was used as the REST client.  
2. Create new HTTP request
3. Add a header called "apikey".  Past the copied key into the value field

Executing CLI tools sending requests to the maxinst pod:

POST https://host:port/toolsapi/toolservice/icheckerreport - Generate an integrity checker log.

GET https://host:port/toolsapi/toolservice/toolslog?logfile=<name of report from icheckerreport request>

GET https://host:port/toolsapi/toolservice/toolslog - Get a list of all tools logs.

POST https://host:port/toolsapi/toolservice/icheckerrepair - Execute the integrity checker utility.

POST https://host:port/toolsapi/toolservice/managestop - Stop the Maximo Manage pods.

POST https://host:port/toolsapi/toolservice/managestart - Start the Maximo Manage pods.

Executing Logging service commands sending request to the Manage pod:

POST https://host:port/maximo/api/service/logging?action=wsmethod:submitUploadLogRequest - Upload logs from Maximo Manage pods to S3 Cloud Object Storage.
 

GET https://host:port/maximo/api/service/logging?action=wsmethod:streamLog - Stream the application log to the browser

Executing the integrity checker CLI

Executing Integrity Checker to write a report to logs

The URL for any of the toolsapi requests requires the url to your maxinst pod.  For this report, use the POST method. 

POST https://host:port/toolsapi/toolservice/icheckerreport

The body of the request is blank.

The response is shown in this screen capture:

Getting lists of tools logs

GET https://host:port/toolsapi/toolservice/toolslog Get a list of all tools logs.

A list of available logs is shown in the screen capture:

     

Streaming tools logs

To stream a particular log file output, add the logfile parameter along with the file name as shown:

GET https://host:port/toolsapi/toolservice/toolslog?logfile=ichecker_report1637953395961.log

Stopping and starting Manage

Executing managestop

You stop the manage pods by using the Tools API against the MAXINST pod URL:

POST https://host:port/toolsapi/toolservice/managestop

Executing managestart

POST https://host:port/toolsapi/toolservice/managestart

Submit Upload Log Request to Cloud Object Storage

Submitting upload log requests is executed on Manage pods.  Upon executing this command, all logs from all Manage Liberty pods (cron, UI, MIF, RPT) are collected, compressed, and automatically uploaded to Cloud Object Storage.  This allows administrators to access the logs without the need for access to the Red Hat OpenShift console.

POST https://host:port/maximo/api/service/logging?action=wsmethod:submitUploadLogRequest

Navigate to your Cloud Object Storage bucket to see the log returned in the output:

Reference Link : https://www.ibm.com/support/pages/how-use-tools-api-maximo-manage