Category: Technical

At some point in time every maximo developer or support engineer would have implemented data restrictions to hide data or make it un-editable for the users. Since they are fairly easy to implement and only require a sign out and sign in for them to take effect for a user, they are very widely used. However, if not implemented carefully, they can seriously impact performance.

I am going to explain how mbosets are impacted by data restrictions, the qualified type of data restriction in particular.

When a qualified type of data restriction is implemented on an object with a condition, the user will have access to only those records of that object which are fetched by the condition’s where clause i.e. those records which qualify the condition. This clause is applied over and above any exist clause.

When it comes to java customization, the impact of data restrictions on the mboset depend on the way the mboset has been fetched in the code. If the mboset has been fetched through MXServer then the data restrictions will apply (if you remember we pass the userinfo to fetch the set) but if the set is fetched through a relationship then the data restrictions don’t apply.

You may ask why this different behavior? That’s because when an mboset is fetched through relationship, it becomes a child mboset and the rule is – owner must have access to all it children. That’s why!

Let me explain this with an example. Let’s say there is a data restriction on the asset object to show only those assets that have their type as FACILITIES. When a user navigated to the asset application he would be shown only FACILITIES type assets. When he navigates to the subassembly section of any of these assets, he would be shown all child assets of that asset which may or may not be of the type FACILITIES. Seems justifying right!

Note: Subassembly section in assets is a relationship of asset with itself to show child assets.

Let see how and where does this impact –

Domains – Table domains are fetched using MXServer so data restrictions apply. But if you are creating a table domain through code and in the getList method you use relationship then data restriction wont apply.

Dialogs – Dialogs are mostly created using a relationship so data restrictions would not apply. In some cases, mboname is used instead of relationship, in that case data restrictions would apply.

Non-Persistent Fields – Non-persistent fields are populated through java code. If the code uses MXServer to fetch some data and populate the field, the non-persistent field might not get populated or might display incorrect data if data restriction exist on that fetched set.

Other Customizations – Certain logic may fail due to data restriction if these logics are not implemented keeping data restrictions in mind and specially if MXServer is used to implement these logics.

Developer must keep in mind the impact of data restrictions while implementing logics. I am not saying MXServer is a strict no-no, but if data restrictions are going to be placed the impact must be considered.

On the good side even if qualified data restrictions are applied, they are majorly applied on main objects isn’t so!

Cheers!

Whenever I travel, I either read or I write. These days I am travelling a lot and I am on a writing spree, so you would be seeing quite a few posts from me.

That brings me to this unique requirement to display different lookups on a field based on a condition. A simple example of this is displaying a location lookup on a field if, lets say the orgid is EAGLENA else display an asset lookup on the same field.

This can be achieved through conditional UI in application designer.

The images below show how to setup this through conditional UI on a field EQ1 on ASSET.

Have a great day!

There are several scenarios where you have a requirement to present data that cannot be queried through a simple select statement but requires looping through the table as the data resides in a parent child hierarchy in the table. Work order hierarchy is one such example where a work order record has its parent listed in one of its column, and the parent’s record would have its own parent listed in its record and so on. So to find all the child work orders to the leaf level under a work order, looping would be required.

Note: Although ANCESTOR tables maintain the complete ancestor hierarchy but at times they can have incorrect data.

This is typically achieved in a relational database through a recursive query and the traditional way is to use CTE or Common Table Expression which uses the ‘WITH’ statement to construct recursive queries. I will not go into details of how to construct or use CTE, their detailed documentation is readily available online.

Oracle has introduced a much simpler syntax to execute recursive queries – the START WITH, CONNECT BY statement. It has a lot of limitation compared to the traditional CTE method but for simple querying its gets the work done in a lot less effort. The START WITH, CONNECT BY statement can also be used in DB2 by enabling the oracle compatibility mode 08.

When it comes to table domains in Maximo, using the traditional CTE is a challenge since in table domains only the where clause can be specified and querying using CTE has an entirely different syntax so it cannot be specified in the listwhereclause. The START WITH, CONNECT BY on the contrary, has the syntax similar to the traditional select statement and can be used in the table domains to query data. Here is an example of the START WITH, CONNECT BY statement that lists all the child work orders of the work order WO-1990 till the leaf level.

SELECT * FROM WORKORDER
START WITH WONUM = ‘WO-1990’
CONNECT BY PRIOR WONUM = PARENT AND ISTAKS = 0;

A little bit about table domains, when you open a lookup in Maximo which displays data through table domain, it doesn’t fetch the entire result set in one go but only that no. of rows that are displayed on the first page of the lookup. When you click the next page on the lookup, the next set of records is fetched and so on. What that means is if the lookup is configured to display only 10 rows at a time, when the lookup is opened, only first 10 records are fetched from the database, now if the next button on the lookup is clicked, the next 10 records are fetched and so on.

This is where it gets interesting, what I have observed is if you use the START WITH, CONNECT BY statement in a domain, when the lookup for this domain is opened, it holds a lock on the table(row level lock) till the time that lookup is closed. This is not the case with non-recursive queries, they never hold a lock, even complex queries with joins and sub-selects.

This is where the problem lies, when the lookup with the START WITH, CONNECT BY statement is opened in one application and another user or the same user in another application tries to do a transaction (update or delete) on the same set of rows as queried by the lookup, the user doing the transaction is presented with a deadlock error. Overriding the isolation level for the recursive query also doesn’t help.

The image above shows an error presented to the user in a similar scenario but in this case the lookup and the transaction performed was on the LOCHIERARCHY object.

So it is better to avoid using START WITH, CONNECT BY statement in lookups and use alternate solutions like ANCESTOR tables or views to display data. Some of the lookups can be tricky and may require lot of effort to avoid using START WITH, CONNECT BY statement but if there are deadlock issues, it’s worth the effort.

Cheers!

Did you know that maximo support multi XML import export?

If you want to export a bunch of application xmls from one environment to another or may be you want to edit more than one xml and then import them back into maximo, this functionality can come in handy.

To use this functionality, filter the list of applications to be exported on the list tab of the application designer and click the export button on the toolbar. Maximo will export a single xml containing all the applications filtered on the list tab. This xml can be imported back in the same environment or other environments as per your need.

The screenshot below shows 3 applications being exported. The exported xml shows 1 expanded application presentation and other 2 collapsed.

When it comes to adding a custom attribute to asset template and getting it copied to the asset, one might think that this may require extending the asset template class and adding the code to get the field copied or probably configuring a cross over domain. But its way easier than that!

Just creating the custom attribute in asset template and in asset with the same name gets the job done. When the asset is created from the asset template, the data from the custom field in asset template gets copied over to the field with the same name in asset. I did a quick check on this and it works as expected. Have a look –

While looking at the debug logs of Maximo I noticed queries fired against the dummy_table. I wondered what is it that Maximo needs to query against the dummy_table. After looking at the sql and some digging in, I found that Maximo uses the dummy_table for evaluating the conditions.

The condition of a condition node in a workflow is evaluated in 2 ways –

1. The condition is validated using the Parser (standard way, on the object).
2. If the parser fails, the condition is validated using SqlFormat on dummy_table.

To explain it further, consider a workflow on the PO having one of the following conditions-

1. Condition: poid=:poid -this will pass the Parser, so validation would be successful.
2. Condition: exists (select * from ADDRESS where orgid = :orgid and addresscode = :shipto) – this will fail the parser, but will pass the SqlFormat checking on dummy_table using this sql: Select count(*) from dummy_table where (exists (select * from address where orgid =  ‘org01’ and addresscode =  ‘add01’));

So validation would be successful.

3. Condition: poid = :poid and exists (select * from ADDRESS where orgid = :orgid and addresscode = :shipto) will fail the parser, will also fail the SqlFormat check because dummy_table doesn’t have a column named poid :

sql: Select count(*) from dummy_table  where  poid =  32767 and exists (select * from address where orgid =  ‘org01’  and addresscode =  ‘add01’ );

So validation would fail.

In this case the poid = :poid doesn’t really make any sense in the sql.

So while writing a condition make sure that you are not writing a sql that would fail.

The “Independent of Other Groups” checkbox in the security group application is one of the most misunderstood concepts in Maximo. What is the significance of this checkbox? How does it affect the security authorization of the group? Let’s have a look.

As the name suggests, the “Independent of Other Groups” checkbox on the main tab of the security group application specifies weather the group is independent of other groups or not. What that means is whether the authorization of this group can be merged with the authorizations of other groups. This is only applies to multisite implementations, for single site implementation this checkbox doesn’t have any significance. Lets understand how this works –

Scenario 1: Consider a multisite implementation; the user belongs to the 2 security groups with the following privileges:

Group 1: Site A and Work Order Tracking application

Group 2: Site B and Purchase Orders application

If these security groups are both independent then the user ends up with rights for Work Order Tracking on Site A and Purchase Orders on Site B. If however the security groups are non-independent then the privileges combine and the user ends up with both Work Order Tracking and Purchase Orders on Sites A and B. Basically you sum up the privileges and if any of them overlap you take the highest level.

Scenario 2: Consider a single site implementation; the user belongs to the 2 security groups with the following privileges:

Group 1: Site A (or all sites authorization) and Work Order Tracking application

Group 2: Site A (or all sites authorization) and Purchase Orders application

Since there is only one site, irrespective of whether these groups are independent or non-independent the user will have the rights to both Work Order Tracking and Purchase Orders on Sites A.

Scenario 3: Consider a single site implementation; the user belongs to the 2 security groups with the following privileges:

Group 1: NO SITE and Work Order Tracking application

Group 2: Site A (or all sites authorization) and Purchase Orders application

If these security groups are both independent then the user ends up with rights for only Purchase Orders on Site A. This is because the two groups are independent and group 1 doesn’t have authorization to any site hence user doesn’t have rights to Work Order Tracking in Site A. If however the security groups are non-independent then the privileges combine and the user ends up with both Work Order Tracking and Purchase Orders on Sites A.

NOTE: This is why the independent checkbox should not be checked for single site implementations.

Scenario 4: Consider a multisite implementation; the user belongs to the 2 security groups with the following privileges:

Group 1: Site A and Work Order Tracking application – Read, Route workflow Access

Group 2: Site B and Work Order Tracking application – Read, Change Status Access

If these security groups are both independent then the user ends up with rights for Work Order Tracking – Read, Route workflow Access on Site A and Work Order Tracking application – Read, Change Status Access on Site B. If however the security groups are non-independent then the privileges combine and the user ends up with Work Order Tracking – Read, Route workflow & Change Status Access on Sites A and B.

Scenario 5: Consider a multisite implementation; the user belongs to the 2 security groups with the following privileges:

Group 1: Site A and PO Limit of 10,000

Group 2: Site B and PO Limit of 50,000

If these security groups are both independent then the user ends up with PO Limit of 10,000 for Site A and PO Limit of 50,000 for Site B. If however the security groups are non-independent then the privileges combine and the user ends up PO Limit of 50,000 for both Sites A and B.

Note: Never change the MAXEVERYONE group to independent group as this group has a lot of conditional grants which will stop working if this group is made independent without granting access to all sites (similar to Scenario 3).

IBM Tech Note on why MAXEVERYONE shouldn’t be set as independent