Intricacies of Maximo ConfigDB

Few years ago, I had written a blog on errors encountered while applying the configuration changes to one or many database objects in Maximo. One of my acquaintances had asked me to write a blog on the intricacies of configdb. Let us first understand why do we need to configure the database.

When we make some change to an object, say adding a new attribute or changing the length of an attribute, creating or modifying indexes, etc. or,  may be we are adding a new object altogether in Database Configurations application, and when we click on the Save button, the changes are stored in temporary database configuration objects (secondary tables) and these changes do not take effect until you apply the configuration changes. It is important to back up your data before configuring the database. However, if we want to add a new relationship to an object or we want to modify a few relationships, once we save the record in Database Configurations application, they can be used and we do not need to apply the configuration changes. A relationship is a link between two MBOs, which is created by specifying a SQL Join statement.

There are three ways to configure the database: command-line mode, a full live configuration, or a partial live configuration with administration mode turned on. If you want to use the command-line mode, you must shut down the application server. It is easier to restore data from this configuration mode as there are no updates while the database is being configured. While restarting the application server, you need to ensure that you do not encounter internal 500 error. I had also written a blog on internal 500 error. You can refer to that if you like.

Full live configuration has least impact on users as active transactions are not interrupted or lost as administration mode need not be turned on in this case and this can be used only for non-structural changes to Maximo objects. Partial live configuration requires administration mode to be turned on which blocks users from the system applications, disables event listeners, suspends cron tasks, and does not allow remote connectivity. We should know when to use what configuration mode. For this, we need to understand the difference between a structural and a non-structural change. Before that let us also find out what are MBOs and its attributes.

Maximo Business Objects and Attributes

A business object is an object that  has attributes and values, operations, and relationship to other business objects. The business object contains the business data and models the business behavior around it. The Maximo Business Object (MBO) defines a set of fields and business rules and updates one or more Maximo database tables. These can be of two types - Mbo and MboSet. MboSet is a collection of Mbo objects, which has methods to manipulate, iterate and query through the data. This can be considered as a database table. Mbo represents a record in a table which implies that each MBO represents a single database record from a single database table.

Information about a business object, which is referred to as metadata, is stored in the database in database tables. The metadata that is associated with the business objects is used to manage the database objects. This metadata for a business object may include the following:

1. Definition of the Business Object - Name of the object, database entity, whether the object is persistent or non-persistent, the underlying java class
2. Attributes  - Name, data type, length or size, field validation class
3. Associated Relationships - associations between tables that are created using join statements to retrieve data.

The Attributes of business objects contain the data that is associated with a business object. When you create an object, you must specify the attributes that are assigned to the object. These attributes can either be persistent or non-persistent. A persistent attribute represents a database table column or a database view column. A non-persistent attribute exists in memory only, because the data that is associated with the attribute is not stored in the database. The additional metadata that is associated with business object attributes, can include a domain, a custom class, a default value, and to specify whether the attribute is required, etc.

Structural Vs Non-Structural Database Change

The changes made in the Database Configurations application has the ability to change not only the data schema but also the data dictionary tables which store information about the tables, fields, and views used by Maximo. You can check the list of data dictionary tables here.

There is also a set of shadow data dictionary tables which are known as the CFG tables, which store the necessary changes made in the Database Configurations application until you are ready to apply those changes to the database. So, the changes which are made in the Database Configuration application are actually written into the CFG tables and after the DBConfig process is successful, the changes are written into the data dictionary tables which may alter the schema of the database or reconfigure particular attributes. 

A non-structural change to Maximo object does not disrupt the live business object definition. For example, if we change the field validation class for an attribute or we add a domain to an attribute and perform a live update to apply the changes, the business objects that are already instantiated are not re-validated. Modifications to indexes do not use the CFG tables but use two other Maximo objects, MAXSYSINDEXES and MAXSYSKEYS. These changes are also considered as non-structural and hence, do not required admin mode to be turned on before configuring the database.

A change which may alter the data schema, like the ALTER, CREATE, DROP statements in SQL, is termed as as Structural change, like, creating or deleting a Maximo object, changing the data type or length of an attribute, etc. A structural change may alter the transaction table. For certain structural changes, Maximo will automatically rename the original table with an XX prefix during the configuration process and these tables will serve as backed up data. This would be for major modifications, like data type changes which cannot be made on a table with data in it. The configuration process reconstructs the table schema and restores the data from the backup. These backup tables will be retained so as to be used in case you need to do a roll back of the configuration process, and they can be deleted periodically by an administrator. Turning admin mode on allows the structural changes to be applied to the database without stopping the application server.

Changes to the structure should only be done through the Database Configuration application and not directly through the database. This will ensure that all Maximo rules are observed. After doing the changes in the database configuration, the system will then take care to do the changes to the underlying database, if any are needed.

Using Database Configuration Application - WHY?

An object is a self-contained software entity that consists of both data and functions to manipulate data. User-defined objects are always created in the Database Configuration application. All changes should be performed from here, as this will ensure the integrity and upgrade-ability of your configuration. 

When adding a new MBO, there are several settings which one should configure. Just to mention a few, you can set a “Custom class” which will tell Maximo which Java class has the logic to be used with this MBO. You can also set the MBO to be a View or enable Auditing for this MBO. So, it is not as simple as creating a new table in the database using CREATE TABLE statement.

When we are creating a new object in Maximo database, it is not as simple as creating a new table in the database using CREATE TABLE statement in the native database. The changes should be saved in the Data Dictionary tables else we cannot use it from Maximo instance. The object details will be saved in MAXOBJECT table which may be based on MAXTABLE or MAXVIEW. Details of the attributes of this object will be saved into MAXATTRIBUTE table, details of indexes will be saved in MAXSYSINDEXES and MAXSYSKEYS tables, and details of relationships will be saved in MAXRELATIONSHIP table, details of the columns for the view will be saved in MAXVIEWCOLUMN, and MAXSEQUENCE table will store all the sequences used in the system.

When we create the new object in Database Configurations application and save the record, details are saved in the shadow tables, MAXOBJECTCFG, MAXATTRIBUTECFG, MAXTABLECFG, MAXVIEWCFG, etc. During the partial live configuration process, the system runs the CREATE TABLE statement in the native database and also inserts the changes into the data dictionary tables as mentioned above. Once the configuration process is successful we will be able to access this object in the Maximo instance.

We can create a native view directly in Maximo database using CREATE VIEW statement, then we can come into Database Configurations application and click on New Object icon, provide the same  name of the view created above in the Object field, "Imported?"  and "View?" checkboxes will get automatically checked, and "View Select", "View From", and "View Where" fields will be populated with the appropriate portions of the SQL statement.  When you save these changes in the Database Confugurations, the details are written to MAXOBJECTCFG, MAXATTRIBUTECFG, MAXVIEWCFG tables and after the configuration changes are applied with admin mode turned on, these changes are written into MAXOBJECT, MAXVIEW, MAXATTRIBUTE, etc. tables and you can access the view in Maximo UI. You can refer the youtube video for the same.

References:

Reference 1
Reference 2

Reference 3

Reference 4

Reference 5

Reference 6
Reference 7

Maximo Applications - Exploring Conditional UI to Using Data/Attribute Restrictions

I have come across such questions many a time as why do we use data/attribute restrictions instead of using conditional UI with our application designer restrictions. There are pros and cons of using both in our practice, and there is no wrong or right away of doing things, also different people will have different thoughts based on their experience. And, I am  also writing this blog as per my experience or understanding.

As you all are aware that there are separate ways that can be used to set up condition UI in Maximo. Condition UI gives flexibility to change properties, give access or restrict data based on meeting or not meeting some condition. A developer also has the option to add and associate security options to tabs, sections, textboxes, tables etc., with a condition on top of the application security.

Let us start our explanation with a simple example. Let us suppose that my application has a text box, Status, which should be visible only to Management Group, Contractor Group, Labor group and HR group. I would go to application designer, create and associate a signature option to this text box. I would next go to Security Groups application, search for required groups, would navigate to Application tab and for my application I would give grant access to that signature option. I hope everybody would agree with me till this point. 

Next, let us add some restriction(s) to the Status field, gradually. For HR group, the field should always be read-only. What could be a better approach to achieve this? I can go to application designer to access the application, open the text box properties dialog for that field where the signature option, created above, is already associated and click on Configure Conditional Properties. Select the HR group in Security Groups section, associate a condition which is always true (like 1=1) and for true condition enter a property=inputmode and value=readonly. As per my understanding, better way would have been to use a attribute restriction in the security groups application. In the Security Groups application, search for HR group, navigate to Data Restrictions tab, open Attribute Restrictions sub-tab. Select the object, attribute, application and keep the Type=READONLY.

Since this data restriction is enforced at the data level, this restriction will work for all instances of the object/attribute regardless of being instantiated through presentation (application UI), or via background process (such as action, or via integration). We need to remember that data restrictions always supersede application configurations in the Application Designer application. The flow of restrictions on an attribute starts with database configuration, then data restriction which is enforced at a security group level, and then Application Designer configurations which is only at the application's UI presentation level.

We also need to remember that configurations that are created through data restrictions are applicable wherever that object-attribute pair is used, while application designer restrictions are not. If you want to apply a restriction to a field in all the tabs of an application, it is advisable to use data restrictions as all the fields in all the tabs will inherit the restriction. However, if you configure restriction in application designer, it will have to be configured in each field of each tab. Similarly, for a table column, for header and detail section you will need to configure the same restriction twice in application designer.

Now, let us add some more interesting things to our Status field. Let us assume there is also a lookup for Status field with values, Inspected, Routed, Checked, Routed to Manager, In Progress, Approved, Completed. For Contractor and Labor group, the Status field should be required based on the value of some other field. Let us suppose that if the description field is not null and contains 'Test', the Status field should become required for Contractor and Labor group(s). But the Status field lookup should display only 'Inspected' and 'Routed' values to a contractor and only 'Checked' and 'Routed to Manager' values to a labor group member. Also, a labor group member should see all other values in the lookup if  description field does not contain 'Test' but for a contractor group member no only two values should appear in the lookup at any time. We need not go explore conditional domains yet, let us save it for another day :)

Step 1: Create a condition TESTCOND in Conditional Expression Manager to check for value in description field of the same object.
Go to Security Groups application, search for Contractor group, navigate to Data Restrictions tab, open Attribute Restrictions sub-tab. Select the object, attribute, application, select the Type=REQUIRED and select Condition=TESTCOND.  Repeat the same step for Labor group.

Step 2: Create two new lookups (CONTLOOKUP and LABLOOKUP) in Lookups.xml to display Inspected and Routed values and Checked and Routed to Manager values, respectively.
Go to Application Designer application, search for the application. Open text properties dialog for Status field where the signature option, created above, is already associated and click on Configure Conditional Properties. Select the Contractor group in Security Groups section, associate a condition which is always true (like 1=1) and for true condition enter a property=lookup and value=CONTLOOKUP. Similarly, add Labor group in Security Groups section, associate TESTCOND in Conditions section, for true condition enter a property=lookup and value=LABLOOKUP and for false condition enter a property=lookup and value=VALUELIST (if there is ALN/SYNONYM domain associated with STATUS attribute in database configurations).

I am not sure whether everyone would agree with me for the above approach I have considered. Let us consider another aspect of granting access to signature options. We had configured conditional UI for a section to become read-only on meeting some condition for say, Labor Group. But this section needs to be visible to Management Group and HR Group but this section need not be available to Contractor group at all. As you all have figured out already, we have associated a signature option to this section and configured conditional properties for Labor group with a condition and have set property=inputmode and value=readonly for when condition is true. And, in Security Groups application, for this application and this signature option, we granted access to Management Group and HR Group but did not grant access to Contractor Group.

Here I usually need to answer very often why do we need to go to each security group to grant access? We can configure conditional UI for rest of the groups as well. For Management and HR Group(s) who already have this application access, we can also configure conditional properties for the section mentioned above by associating a always true condition with property=display and value=true. For contractor group, we can configure conditional properties for this section by associating a always true condition with property=display and value=false.

We do get into some more confusions with independence or dependence of security groups. There is very useful (or rather sometimes confusing) check box provided in Security Groups application as 'Independent of Other Groups'. We will keep multi-site implementations for some other time, we will focus on single-site implementations for today. The purpose of this particular checkbox is to decide whether access rights of this group can be merged with the access rights of other groups. This is very useful and applicable to multi-site implementations and does not hold much significance for single site implementations.

Let us consider a single site (SITE-A) implementation where a user belongs to two security groups, say Management and HR. Management group has access to Purchase Orders application and HR group has access to People application. Let us also consider that Management Group is set up with No Site information and HR Group is set up with SITE-A in a single site implementation. If we make both the sites independent then this user will have access only to People application. If we do not check 'Independent of Other Groups' checkbox for these two groups, the user will have access to both the applications. As a best practice, we should not check this checkbox for single site implementations.

If we have multiple independent security groups then the resulting security profile of the user gets difficult to determine if the user belongs to more than one security group. Let us extend our example further. Say user USER1 belongs to both HR and Contractor group(s). HR and Contractor groups are both independent and are implemented with SITE-A (or authorized for all sites). Both groups are implemented with access to Purchase Contracts application but have been configured with specific conditional access to change status. Say, contractor should be able to change the status of a purchase contract record on meeting condition X and HR should be able to change the status of a purchase contract record on meeting condition Y. If either of the condition X or Y is true, USER1 user will be able to change status of the purchase contract. If there is some user USER2 who belongs to Contractor group alone and user USER3 who belongs to HR group alone, then USER 2 will be able to change the status of a purchase contract record when condition X is true and USER3 will be able to change the status when condition Y is met.

If we clear the checkbox and make the contractor group dependent, what will happen to the privileges for USER1? In this case, USER1 will be able to change the status of Purchase Contract record when condition X is true.

Please feel free to post your views and comments on this blog. I would love to hear from you.