Maximo Open Forum

 View Only
  • 1.  Location Selection Returns LOCATIONSID, not LOCATION Value

    Posted 10-12-2020 20:38
    Upgraded to 7.5.0.11, then 7.6.1 on another system.  From WO Tracking and from Assets, when attempting to type in or select a location value, the system always returns the LOCATIONSID, not the LOCATION value.  Sometimes this works, if it happens to find an ID that matches a value, because some locations are just numbers, like 1234, which is incorrect.  Most of the time there is an error stating something like, "BMXAA2661E - Location 23,069 is not a valid location" when selecting a value.  When typing in a value the error is "The entered value Z20-PRV1 is not a valid integer value" but it's configured as UPPER, not integer.

    I can see the SQL command when it queries the database, and it is asking for the LOCATIONSID, not LOCATION.  I believe this is something in the database, since it is happening on a new 7.6 system, which is newer code, but where the database was upgraded.  I keep checking in the Application Designer, and in the Database Configuration, but have not found anything yet.  I have a working 7.5.0.0 system, where this issue does not occur, for comparison.  

    #EverythingMaximo

    ------------------------------
    Tim

    ------------------------------


  • 2.  RE: Location Selection Returns LOCATIONSID, not LOCATION Value

    Posted 10-13-2020 08:29
    I can't think of a situation where this would occur out of the box and it's even more strange to me that it's happening on both WORKORDER & ASSET. Off the top of my head, in order to control the mapping when a user types the value the mapping would have to be done via java classes, automation script (retrieve list launch point), crossover domain, or the maxlookupmap. It's also possible that it's not related to the mapping, and just invoking some logic on the attribute changing. If so you'd need to validate Action & Validation attribute launch points as well from an automation script perspective.

    I can't think of why someone would change the maxlookupmap but you can see that in Database Configuration. If you open the WORKORDER object and filter for the location attribute and use the pencil icon next to the location attribute you'll see a dialog popup with the lookup map for that attribute. This out of box should be empty for this attribute.

    Secondly would be to look at the domain associated on the field in database configuration. Out of box on WORKORDER it should be LOCATIONCROSSCI which just crosses the CINUM from locations. If you see something referencing locationsid that could be the issue. 

    Third is I would look at the java class referenced on that attribute in database configuration. Assuming you don't have industry solutions/add-ons, it should be psdi.app.workorder.FldWOLoc. If this is something custom I would review that code.

    Fourth is the automation script retrieve list. This wasn't available in 7.5, so it would have to have been something added after you upgraded which would be odd. Look for all launch points that reference the WORKORDER object & LOCATION attribute (since it could also be caused by an action/validate launch point).

    ------------------------------
    Steven Shull
    Projetech
    ------------------------------



  • 3.  RE: Location Selection Returns LOCATIONSID, not LOCATION Value

    Posted 10-13-2020 13:09
    Steven,

    Thanks for your reply and list of things to check.  We have made some configuration changes, like adding fields, but have no customizations that change any Java classes.  Early on we did have a consultant help us with installation, configuration, data import, etc.  I was involved in most of the process and don't recall changes that would have caused this.  We had an add-on from the consultant, but were not using it, and the new EARs were built without it.  I don't see anything related in Actions or automation scripts. 

    In Database Configuration, WORKORDER object, LOCATION attribute, the lookup map has no rows, as expected. 
    The domain for this attribute is LOC2WO, a crossover domain LOCATION=:LOCATION AND SITEID=:SITEID.
    I changed it to LOCATIONCROSSCI and applied the configuration changes.  It's still querying and returning the locationsid.

    ASSET is using LOC2ASSET, which is a crossover, location=:location and siteid=:siteid. I changed it to LOCATIONCROSSCI here too.

    The class for the LOCATION attribute from WORKORDER is psdi.app.workorder.FldWOLoc, and from ASSET it's psdi.app.asset.FldAssetLocation.  We do have the Transportation add-on, and I have seen it referenced on other attribute classes.

    I have also exported WOTRACK, ASSET, and System XML and searched these for locationsid, with nothing found.

    In 7.5.0.0 there were no errors.
    In 7.5.0.11 the error started to appear (same server and database)
    In 7.6.1 the error also appears, it's a newer 2016 server with WAS 9, only the database was upgraded.  I may be able to get help from IBM on this version.  All of the things I tried above were on 7.6.1.

    In the past we attempted to install either FP 7.5.0.3 or .5, and now I am certain it was the same issue, although at that time it looked like some kind of corrupted index.

    We are using an integration table for work orders taken to the field, but not for assets.

    I think my next step is to dig through the fix pack logs on the 7.5.0.11 server and see if I find any clues.​  Because the issue exists in the new 7.6.1 server, it must be something in the database causing the error.

    ------------------------------
    Tim Bratcher
    Sweetwater Authority
    ------------------------------



  • 4.  RE: Location Selection Returns LOCATIONSID, not LOCATION Value

    Posted 10-13-2020 17:23
    In the MAXATTRIBUTE table, objectname LOCATIONS, I found some differences in the primarykeycolseq. 
    All values are NULL, except as below:

    7.5.0.0  (working)
    attributename SITEID is 1
    attributename LOCATION is 2

    7.5.0.11 (error)
    attributename LOCATIONSID is 1

    7.6.1 (error)
    attributename LOCATIONSID is 1

    I believe this may be causing the issue.  I did not change them, it happened during the upgrade from 7.5 to FP11.  I'm wondering if it could be as simple as just stopping the server, updating these values from SSMS\SQL, and restarting the server. The values do not appear available for editing from the Database Configuration front end.



    ------------------------------
    Tim Bratcher
    Sweetwater Authority
    ------------------------------



  • 5.  RE: Location Selection Returns LOCATIONSID, not LOCATION Value

    Posted 10-13-2020 18:01
    That's a good find. The primary key column sequence is extremely important so I wouldn't be surprised if that was the issue. These definitely shouldn't change, so I'm not sure why they were, especially in an upgrade. I don't think we've had any environments that went to 7.5.0.11 so I'm not sure if this happens all the time or some of the time. Most of our customers stopped at 7.5.0.7, some went up to 7.5.0.8, before we took them to 7.6.0.X of Maximo. I would definitely recommend correcting this and see what happens.

    You mentioned that you had an index issue, and I wonder if someone potentially executed repair mode with the integrity checker tool. Everything that has a primary key column sequence should have a corresponding unique index. The ID column has a unique index, so there may be some flawed logic in the integrity checker process that caused it to change the primary key column sequence to match the unique index that existed instead. We never use the repair mode of integrity checker as it's not always right (we prefer to make fixes) but I could see that somehow leading to this.

    ------------------------------
    Steven Shull
    Projetech
    ------------------------------



  • 6.  RE: Location Selection Returns LOCATIONSID, not LOCATION Value

    Posted 10-13-2020 20:02
    Edited by Tim Bratcher 10-13-2020 20:07
    The version of 7.6.1 I have to install stated that I must be on 7.5.0.11 FP (or higher) prior to installation.  I haven't tried it with any 7.5 version prior to FP11.  Maybe I could have updated to 7.6.0 from an earlier FP, but we had previous issues with getting them to install correctly.

    I agree that the primarykeycolseq values should not change, and I can't imagine what the installer scripts are doing that caused it.  I ran across some IBM support documents where this had happened to the COMPANIES table and caused creation of new companies instead of updating existing ones.  Finding this by searching Google for "primarykeycolseq" is what gave me the idea.
    Maximo primarykeycolseq updated wrongly

    In the past when we tried FP3, and FP5, we experienced the same issue, and initially thought it was a corrupted index. Now that I have dug into it further, I can see that it was the changed primarykeycolseq values.  Before we didn't notice that the values being returned were locationsid for the selected records.  I have never used repair mode with the integrity checker.   I also check the boxes to manually run configdb and manually build the EAR files after FP installation.

    We had other issues with the FPs, like failing SQL commands due to a special character in the password field.  Another time we had updated the sqljdbc.jar driver for security reasons, but the FP installer extracted it's own copy of that file and caused errors.  There were "database" errors caused by cached BIRT report XML files.  Because of these and other issues we stayed on the working version of 7.5.0.0. 

    I stopped the servers and ran the below SQL, which fixed the issue.  Thanks for your initial response, it helped eliminate some possibilities.

    select objectname, attributename, primarykeycolseq from maxattribute where objectname = 'LOCATIONS' and attributename ='LOCATIONSID'
    -- was previously 1
    -- update maxattribute set primarykeycolseq = NULL where objectname = 'LOCATIONS' and attributename ='LOCATIONSID'

    select objectname, attributename, primarykeycolseq from maxattribute where objectname = 'LOCATIONS' and attributename ='SITEID'
    -- was previously NULL
    -- update maxattribute set primarykeycolseq = 1 where objectname = 'LOCATIONS' and attributename ='SITEID'

    select objectname, attributename, primarykeycolseq from maxattribute where objectname = 'LOCATIONS' and attributename ='LOCATION'
    -- was previously NULL
    -- update maxattribute set primarykeycolseq = 2 where objectname = 'LOCATIONS' and attributename ='LOCATION'

    select objectname, attributename, primarykeycolseq from maxattribute where objectname = 'LOCATIONS' and primarykeycolseq is not null

    ------------------------------
    Tim Bratcher
    Sweetwater Authority
    ------------------------------



  • 7.  RE: Location Selection Returns LOCATIONSID, not LOCATION Value

    Posted 10-14-2020 07:46
    Glad you were able to find and get it fixed.

    As for the 7.5.0.11 upgrade paths, you are correct. Every time IBM released a new fix pack they recommend going to that before upgrading to the next version and support going straight from 7.5.0.11 to 7.6.1.2. We're a bit unique in that we do upgrades hundreds of times and value consistency more importantly than speed. We've been burned by supported upgrade paths working differently and causing issues so we try and force all upgrades through the same path as initially intended and tested. This gives us the highest degree of confidence in the outcome of the upgrade for the customer.

    So a 7.5.0.X we ideally get to 7.5.0.7 (paths slightly change after as you can't upgrade 7.5.0.8 to 7.6.0.0 which makes it a deviation for us)
    Then 7.5.0.7 we take to 7.6.0.0
    Then 7.6.0.0 we take to 7.6.0.9
    Then 7.6.0.9 we take to 7.6.1.0
    Then 7.6.1.0 we take to 7.6.1.2

    It's more updates but we know these paths were well tested by our existing customer base & other customers as these were the initial upgrade paths. On paper the outcome should be identical, but because IBM makes tweaks to the existing update files (typically to address bugs they've found) this can cause things to come out differently. For example, 7.5.0.6 to 7.6.0.0 was a supported upgrade path but some attributes weren't created for job plans if you took that supported path. If you came from 7.5.0.7 to 7.6.0.0 those attributes were created. That's a relatively simple example but it's why we go for a more complicated path that we've used.

    ------------------------------
    Steven Shull
    Projetech
    ------------------------------



  • 8.  RE: Location Selection Returns LOCATIONSID, not LOCATION Value

    Posted 10-14-2020 17:21
    Sounds like a very good practice, to stay with experienced, predicable upgrades.  I've only done a few, but have heard from others about differences in version upgrades. 

    Since we had issues with some of the earlier FPs (3 and 5), we stayed on 7.5.0.0.  In order to upgrade we installed FP11, and the first test system is now on 7.6.1.0.  Once the last production system is done, I anticipate doing the same as your last upgrade path, 7.6.1.0 to 7.6.1.2, then stay current and be ready for Maximo 8.  The rest of the stack is Windows 2016, SQL Server 2016, and WAS 9.0.0.7.  I'll be checking WAS for updates too.

    ------------------------------
    Tim Bratcher
    Sweetwater Authority
    ------------------------------