2

I'm trying to determine if the asset table in Maximo 7.6.1.1 has a primary key (query source):

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'ASSET'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

[0 records returned]

I'm surprised that the query did not find a primary key in this table.

Does the ASSET table in Maximo have a primary key? Or is there a problem with the query?

Edit:

Now that I look at the table properties in Toad, I see that it clearly states that there is no primary key on the asset table:

enter image description here

User1974
  • 276
  • 1
  • 17
  • 63
  • 1
    Based on your other questions you have been asking recently I think you are trying to build the same system I have been implementing for several cities in the US. Heavy lifting connecting all the GIS data to Maximo via the MIF. – Milton Oct 02 '19 at 17:52
  • @Milton That's interesting to hear. And it's nice to know that I'm not alone! Here's a list of ideas: [Things I wish I could do in Spatial](https://www.ibm.com/developerworks/community/forums/html/topic?id=3b493c0b-286a-4218-80b9-9b7af26c2083&ps=25) – User1974 Oct 02 '19 at 18:41
  • 1
    Yes. I read that. We are doing all those with an exception on routes I think. Also, not in Spatial. I was surprised that Joao didn't respond to you. I have implemented Spatial 4 times now and every time we end up where you are with customers. You have identified your workflows and it is not set up in a way to be part of your identified workflows. – Milton Oct 02 '19 at 22:37
  • @Milton What do you mean by, *"exception on routes"*? – User1974 Oct 03 '19 at 00:05
  • 1
    Routes mean different things to different people. I have Maximo routes running in a GIS integration. They are basically lists of stops. They are not automatically ordered like a traveling salesman problem. We also have integrated turn by turn text but not navigation. It seems when someone says routes they always understand it differently than I do. – Milton Oct 04 '19 at 17:59

2 Answers2

4

Maximo doesn't use database PK feature but instead will use different unique indices and an applicative composite PK that you can find by looking at the MAXATTRIBUTE.PRIMARYKEYCOLSEQ field.

Example SQL for ASSET, the applicative primary key would be SITEID, ASSETNUM:

select ATTRIBUTENAME,PRIMARYKEYCOLSEQ from maxattribute where primarykeycolseq is not null and objectname='ASSET' ORDER BY PRIMARYKEYCOLSEQ;

You'll also find for every persistent object a unique index bound to a single BIGINT attribute that is populated by a sequence object in Oracle. The corresponding attribute is usually the table name followed by "ID".

Example: With WORKORDER, that attribute is WORKORDERID. You can find the index with such a query:

select * from maxsysindexes where tbname='WORKORDER' and required=1;

And you can identify the attribute by looking at the index found with a tool such as Oracle SQL Dev or with a query like this:

SELECT COLNAME FROM MAXSYSKEYS WHERE IXNAME=(select NAME from maxsysindexes where tbname='WORKORDER' and required=1);
JPTremblay
  • 900
  • 5
  • 8
  • Thanks! When you say *"applicative"* do you simply mean that the constraint is enforced in the application, not in the database? – User1974 Oct 03 '19 at 13:52
  • 1
    The constraints will be applied by the database because the key will be bound to a unique index. I meant by applicative that the software uses it internally for PK purpose. Here's the PRIMARYKEYCOLSEQ field description: _"If a value exists in this field, it indicates that the column is used by business object components when constructing a dynamic where clause to update or delete a row in the database. Values in the Primary column must be sequential, unique, and greater than zero."_ – JPTremblay Oct 04 '19 at 13:36
3

Assetnum is the key field.

Maximo uses a very little of the database extended functionality and instead uses the MBOs (maximo business objects / java backend) to do all the processing and control of key fields and relationships. In maximo you can find the key field by looking at System configuration> Platform configuration> Database configuration and opening the desired table. There are also relationships defined in there.

Milton
  • 121
  • 4
  • It's pretty funny that Maximo doesn't have primary keys. Neither do versioned tables in ESRI geodatabases. I realized just now that I've never actually worked in an enterprise system that had real primary keys. – User1974 Oct 05 '19 at 17:24