Upgrade to Maximo 7.5 fails in UpdateDB script V7500_343.

Problem(Abstract)

When upgrading the Maximo database to 7.5.0.x, UpdateDB can fail in script V7500_343.dbc:


BMXAA7025E -- Script failed while running.;

A statement failed to execute inside a freeform statement;

sql#1407;
ORA-01407: cannot update ("INVENTORY"."COSTTYPE") to NULL

-- BEGINUPDATEDBERROR

-- Product: Tivoli's process automation engine
-- Error running file: V7500_343
-- UPDATEDBFILE:V7500_343.dbc

Symptom

The V7500_343 freeform statement that fails is:

update inventory set inventory.costtype = case
when ( select maxvars.varvalue from maxvars where maxvars.varname = 'COSTFROMASSET' and maxvars.siteid = inventory.siteid ) = '1'
and ( select item.rotating from item where item.itemnum = inventory.itemnum and item.itemsetid = inventory.itemsetid ) = 1
then ( select value from synonymdomain where synonymdomain.domainid = 'COSTTYPE' and synonymdomain.maxvalue = 'ASSET' and synonymdomain.defaults = 1 )
when ( select maxvars.varvalue from maxvars where maxvars.varname = 'DEFISSUECOST' and maxvars.siteid = inventory.siteid ) = 'AVGCOST'
then ( select value from synonymdomain where synonymdomain.domainid = 'COSTTYPE' and synonymdomain.maxvalue = 'AVERAGE' and synonymdomain.defaults = 1 )
when ( select maxvars.varvalue from maxvars where maxvars.varname = 'DEFISSUECOST' and maxvars.siteid = inventory.siteid ) = 'STDCOST'
then ( select value from synonymdomain where synonymdomain.domainid = 'COSTTYPE' and synonymdomain.maxvalue = 'STANDARD' and synonymdomain.defaults = 1 )
else ( select maxvars.varvalue from maxvars where maxvars.varname = 'DEFISSUECOST' and maxvars.siteid = inventory.siteid ) end

If the Maximo database was originally built using MAXDEMO data, rows can exist in the Inventory table that do not have a valid SiteID. As UpdateDB populates new column Inventory.CostType, the invalid SiteID values prevent the script from determining values to populate CostType for these old MAXDEMO records.

Upgrade 7.1 to 7.5.

Diagnosing the problem

If this query returns any values, then your database contains Inventory records with invalid SiteIDs:

Select siteid From inventory Where siteid Not In (Select siteid From maxvars Where varname In
('COSTFROMASSET', 'DEFISSUECOST') ) ;

Resolving the problem

Since these Inventory records are old MAXDEMO data, they serve no purpose and should be removed from your database. Replace XX in the following statement with any invalid SiteID values returned from the query above:

Delete From inventory Where siteid='XX'

Restore your database to pre-upgrade 7.1, remove the bad rows from the Inventory table, and run UpdateDB over again.