In the Database Configuration application Maximo provides the ability to import a view based on an already existing database view.
It is a simple process, but it does require you to extend an existing Maximo object. The new object will be the same as the view name.
In the example shown we have created a custom object called ZPZSYMNODE which has a single unique key field of NODEID. A database view was created to expose two runtime values in an underlying table which is part of another system, the table was called SYM_NODE. Each record in ZPZSYMNODE becomes one record in SYM_NODE, and so the relationship between the Extends Object and the view is one:one.
The definition of the view ZPZSYMNODEVIEW was:
CREATE VIEW zpzsymnodeview AS SELECT
no.node_id as nodeid,
no.batch_to_send_count as batchtosendcount,
no.batch_in_error_count as batchinerrorcount
FROM sym_node AS no;
In Database Configuration you use the New Object action and in the Object attribute (OBJECTNAME) type the name of the view. Maximo will now read the view definition from the native database and import it creating the necessary attributes. But a word of warning, do not be too anxious to save the new definition as you will find the definition is mostly read-only after it has been imported.
Before you can save you need to add an object to the Extends Object attribute (EXTENDSOBJECT). This object’s primary key field should exist in the definition of the view. In this particular case the Extends Object is ZPZSYMNODE and it has a single primary key column (PRIMARYKEYCOLSEQ) of NODEID. Once saved the Imported check box in the 2nd column of the details section will be set.
During the import process Maximo will make the primary key the same definition as that in the Extends Object and will set SAMEASOBJECT and SAMEASATTRIBUTE to the primary key column of that object.
Some attribute types may not get recognised on some databases. In the tests we performed on a SQLServer database we had to manually set attributes as follows:
- Timestamp fields had no type and we had to set them to DATETIME or DATE as required.
- Tinyint fields were first set as ALN 1 and we had to change them to YORN.
- Longvarchar fields were first set to ALN 16 and we had to change them to CLOB.
We also had to change the search type for some fields, particularly where they had been set to NONE and we wanted them as WILDCARD or EXACT.
The Database Configuration fields you can change after you have saved the record are the Description, Required, Title, Class, Domain, Default Value and E-signature Enabled. You cannot change the Type, Length or Scale, or Search Type after the definition has been saved.
In some cases, we extended an existing object that had multiple attributes for its primary key and this worked well, the view needed to have each of those primary key attributes defined.
In other cases, we had no host Maximo object to extend and so had to create a custom one. The particular system we were working with had Incoming and Outgoing Batches and to be able to see the data from their views we needed to create a stub object which we called ZPZSYMBATCH that only had a BATCHID attribute to provide the primary key field. This object didn’t need any records for us to be able to view the records through the Incoming and Outgoing Batch views.
The imported views were read-only views. We created several views and linked them to Maximo applications using relationships so that we could monitor the underlying system from within a set of Maximo applications.
None of the views were updateable as we did not want to extend the underlying system with additional attributes. Maximo applications require a single internal unique identifier and a rowstamp attribute for the application to support insert, update and delete actions. You may not have access or privilege to extend the underlying system and so read-only views may be all that you can achieve without creating an integration point with that system.