November 12, 2011

Crontask analysis and optimization

An interesting feature of Maximo crontask is the capability of storing the task execution times. This feature must be enabled in each crontask clicking on 'Keep history' checkbox. This is nice feature but Maximo does not provide a graphical tool to analyze crontask performances and execution times so i have prepared a small set of SQL queries to be able to quickly gather important statistics directly from Maximo's database.
The first one extracts the start time and duration (in seconds) of each crontask execution in the last week.

select
  crontaskname,
  to_char(starttime,'YYYY-MM-DD HH24:MI:SS') as starttime,
  (endtime-starttime) as duration
from CRONTASKHISTORY
where endtime>sysdate-7
  and activity='ACTION'
order by starttime;

I typically import the output of this query into an Excel spreadsheet and analyze it sorting by whatever column I need. Furthermore, you can create a pivot table or chart to analyze the data. This is an example of what can be achieved:

If you are not familiar with Excel, here is a query that generates the same output straight from an SQL query:
select
  crontaskname,
  sum(endtime-starttime) as totalduration
from CRONTASKHISTORY
where endtime>sysdate-7
  and activity='ACTION'
group by crontaskname;

October 12, 2011

October 10, 2011

Enable lookup in report parameters selection page

In this post I will show how to enable the lookup functionality for selecting one or multiple values in a report parameter.
Instead of creating a new lookup I will show all the necessary configurations looking at the built-in report 'Inventory Balance' (inventory_balance_tbl.rptdesign).

Enabling lookup in report

Open the 'Report Administration' application and search for 'Inventory Balance' report.
Open the report details page and look at the 'Parameters' section. The 'item' parameter is the right example that what we are searching for.



The three fields we intereded in are: Attribute Name, Lookup Name and Multi-Lookup Enabled.
The 'Attribute Name' specifies the database attribute on which we want to filter the results. The Inventory Balance report allows to specify one or more items.
The 'Lookup Name' specifies a Maximo system lookup that will be displayed to select the parameter's values. Just as a remainder, Maximo system lookups are available in the Application Designer through the action menu. In the chosen example the 'item' lookup will be displayed.
The 'Multi-Lookup Enabled' field specifies if the user can select multiple values in the lookup list that is displayed.
Clicking on 'Generate Request Page' and then on 'Preview' button. You will see the following request page.

BIRT report

To see how to handle the multiple values in the BIRT code open the report design file located under [SMP_INSTALL_FOLDER]\maximo\reports\birt\reports\INVENTOR.
In the 'open' method you will find the following lines:

if(params["item"].value) {
  params["where"]+= " and " + MXReportSqlFormat.createParamWhereClause("inventory.itemnum", params["item"].toUpperCase());
}


Using the createParamWhereClause function you can easily convert the items list passed by Maximo in the correct where clause.

September 19, 2011

Automatically display table results when application is displayed

Sometimes you may develop a custom Maximo application that shows a list of entries in a table. If the application is used frequently and the result set to show is not huge, it could be useful to automatically display the table content without the need to hit enter or click on the search icon.
Here is a little tip that may solve this little usability issue.
Open the application in the Application Designer and select main table, find the 'Start Empty' checkbox and uncheck it. If it is already unchecked you may need to first tick and then untick it.


That's all.

Application default search filters

I know that there is an official IBM Technote that already provides a solution to apply a default filter in a given Maximo application.
However this solution has some drawbacks. The main problem I see is that using this technique the field filters are not displayed in the application so it could be tricky for the user to understand what's happening.

I have found a better solution customizing the application's Java bean class (AppBean) specifying the QBE (Query By Example) parameters to the application DataBean in the initializeApp() method.
Here is a practical example about how to do this on the WOTRACK application.

To determine which Java bean handles the application, open Application Designer and select the application, from the Action menu choose 'Toggle Show All Controls' and display the properties of the 'presentation' section. The 'App Bean Class' defines the Java class that you need to customize. For the WOTRACK application it should be psdi.webclient.beans.workorder.WorkorderAppBean.
You should extend this class and override the initializeApp() method adding your filters as QBEs to the application's DataBean.
Here is how the custom class should look like.

public class CustomWorkorderAppBean extends WorkorderAppBean
{
  public void initializeApp() throws MXException, RemoteException
  {
    DataBean resultsBean = this.app.getResultsBean();
    resultsBean.setQbe("siteid", "XXX");
    resultsBean.setQbe("someotherfield", "1234");
    super.initializeApp();
  }
}

Put the custom AppBean class file in the Maximo tree, rebuild and redeploy the EAR file. Don't forget to change the App Bean Class property setting your own custom class.

September 1, 2011

How to create a custom message and display it in a Maximo application

This entry is part of the Maximo Java Development series.

A typical need during Maximo application customization is the ability to display a message on the GUI.

The first step is to define the custom message. Open the Database Configuration application and select Messages for the Action menu. Create a new entry filling the required info:
  • Message Group: Messages can be categorized in groups. I typically put here the name of the customer that I'm working on. This allows to quickly list all the custom entries.
  • Message Key: Unique ID of the message. This field together with the MSGGROUP uniquely identifies the message. I typically use a short descriptive name of the message like 'InfoMsg' or 'ErrorMsg'.
  • Display Method: Can be 'MSGBOX' or 'STATUS'. The 'MSGBOX' display a standard popup window displaying the message and a set of customizable buttons. The 'STATUS' displays the text above the tool bar and does not require user intervention.
  • Message ID: Unique ID of the message. The typical format of custom messages is BMX[CC][NNNN][T] where [CC] is a two letters code that identifies your customization, [NNNN] is a four digits number and [T] is the type of message and can be E (Error), W (warning) or I (Info). Maximo suggest to use [CC]='ZZ' for custom messages. For example I have started my custom enumeration with code BMXZZ1001I.
  • Value: Text that will be displayed. Use {N} tags for parameters to be replaced at runtime (see example hereafter). Use \n to insert a newline.
Here is a screenshot of the new message.


Now that you have defined your message in the database it's time to show it.
The technique to display the message is different whether you are in an AppBean or in a Mbo class.

AppBean
If you are in a custom application bean class (extending AppBean) those two Java lines will bring up the custom message box.

String params[] ={"Hi there!"};
clientSession.showMessageBox(
  clientSession.getCurrentEvent(), "MyGroup", "HelloMsg", params);

As you can see the {0} tag in the message will be replaced with the string 'Bruno' defined in the params array. Obviously it is possible to define multiple tags.

It is also possible to display this message box when an exception is thrown. For example the following two rows will display the same message but will also log a stacktrace in the system logs.

String params[] ={"Hi there!"};
throw new MXApplicationException("MyGroup", "HelloMsg", params);

Mbo/MboSet
If you are in an Mbo or MboSet (businessobject project) you can 'attach' the message to the MboSet with the MboSet.addWarning(MXException e) method.

String params[] ={"Hi there!"};
this.addWarning(new MXApplicationException("MyGroup", "HelloMsg", params));

Alternatively it is possible to throw an MXApplicationException like this.

String[] params = {"Hi there!"};
throw new MXApplicationException("MyGroup", "HelloMsg", params);

August 31, 2011

Query groups application security

The standard Maximo 'Security Groups' application provides an easy way to view and modify users access to applications. However it is not always easy to have an overview of what groups are granted to access a specific application or what applications can be accessed by a specific group.
With the following SQL query you can get a report of read/write permissions granted to all the groups.

SELECT
  maxapps.app,
  maxapps.description,
  maxgroup.groupname,
  maxgroup.description,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='READ') appread,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='SAVE') appsave,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='INSERT') appins,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='DELETE') appdel
FROM maxapps, maxgroup
ORDER BY maxapps.app, maxgroup.groupname;

If you are using the Oracle's than it is possible to list all sigoptions using CURSOR statement.

SELECT
  maxapps.app,
  maxapps.description,
  maxgroup.groupname,
  maxgroup.description,
  CURSOR (SELECT optionname FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app) AS auth
FROM maxapps, maxgroup
ORDER BY maxapps.app, maxgroup.groupname;

It is possible to narrow down the results of the query to understand what groups have access to a specific application adding a where clause in the previous query. For example here is how to list the permissions to ASSET application:

SELECT
  maxapps.app,
  maxapps.description,
  maxgroup.groupname,
  maxgroup.description,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='READ') appread,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='SAVE') appsave,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='INSERT') appins,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='DELETE') appdel
FROM maxapps, maxgroup
WHERE maxapps.app='ASSET'
ORDER BY maxapps.app, maxgroup.groupname;

August 8, 2011

Call Java method on action menu or toolbar button click

This entry is part of the Maximo Java Development series.

In this post I will describe all the steps needed to execute custom Java code upon the selection of an entry in the action menu or the click of a button in the toolbar.

Add a sigoption

First you need to create a new sigoption. Open the Application Designer and select the application you want to modify. Select 'Add/Modify Signature Options' and create a new signature RUNJAVA.

Add the menu entry

The second step is to create the custom entry in the action menu (or a new button).
Select 'Add/Modify Select Action Menu' and create the new entry RUNJAVA of type OPTION. Beware that the name must be the same as the sigoption.

Grant sigoption permissions and test the dummy action

Before going on I recommend the test that the new menu entry is visible.
Go in the Security Groups application, select the group you want to be able to see and execute the custom code. Go in the Applications tab and select your application. Now you should see the RUNJAVA sigoption on the bottom of the page. Click on the checkbox to grant access to the new menu entry.

Disconnect and login with a user belonging to the chosen group, go in the application and verify that you see the new menu entry.
Now it's time to attach your Java code to it.

Write your Java code

The most suitable place to put your Java code is in the 'App Bean Class' which is the custom Java class that manages the application.
To determine which Java bean handles the application, open Application Designer and select the application, from the Action menu choose 'Toggle Show All Controls' and display the properties of the 'presentation' section. The 'App Bean Class' defines the Java class that you need to customize. Here is the WOTRACK application App Bean Class definition.

You should extend this class and create a new method with the same name of the sig option in the Java bean class (RUNJAVA in our example).
Don't forget to change the App Bean Class property setting your own custom class.
Here is an example of how the custom class should look like.

package cust.psdi.webclient.beans.workorder;

public class CustWorkorderAppBean extends psdi.webclient.beans.workorder.WorkorderAppBean 
{
  public int RUNJAVA() throws MXException, RemoteException
  {
    // put your custom code here
    return EVENT_HANDLED;
  }    
}

Implement your custom logic in the RUNJAVA method and copy the class file under [SMPDIR]\maximo\applications\maximo\maximouiweb\webmodule\WEB-INF\classes folder. Rebuild and redeploy Maximo EAR file.

August 4, 2011

How to execute an Oracle stored procedure from Java code

This is a working Java method that can be used to execute an Oracle stored procedure. It can be linked to standard Mbo methods, application beans, actions, toolbar buttons, etc.

public int callOracleProcedure(String procName, int arg1) throws MXException, RemoteException
{
  int ret;

  logger.info("Calling " + procName + "(" + arg1 + ")");

  MXServer mxServer = MXServer.getMXServer();
  UserInfo userInfo = mxServer.getSystemUserInfo();
  Connection dbConnection = mxServer.getDBManager().getConnection(userInfo.getConnectionKey());

  try
  {
    CallableStatement cs = dbConnection.prepareCall("{call " + procName + "(?)}");
    cs.setInt(1, arg1);
    ret = cs.executeUpdate();
  }
  catch (SQLException e)
  {
    logger.error("SQL Error", e);
    throw new MXApplicationException("Error in procedure " + procName, e.getMessage());
  }
  finally
  {
    mxServer.getDBManager().freeConnection(userInfo.getConnectionKey());
  }

  return ret;
}

April 5, 2011

How to enable doclinks in a custom Maximo application

Here is an example of how to add the attachment capability to the organizations (MULTISITE) application.
Add the ‘attachment’ control to the MULTISITE application using the Application Designer.


Then add the DOCLINKS relationship to the ORGANIZATIONS object as shown in the following picture.
Please check the 'Where Clause' statement and modify it to match your application. The where clause must be: ownertable='[MAIN_APP_OBJECT]' and ownerid=:[OBJECT_ID]
The
[MAIN_APP_OBJECT] can be retrieved in the Application Designer and the [OBJECT_ID] can be determined through the 'Indexes' tab in the Database Configuration looking for the unique/internal index.
Note that this database change does not require to run dbconfig.

The last step is to insert a row in the APPDOCTYPE table to tell Maximo that the application MULTISITE supports attachments.

For DB2 use this INSERT statement:
INSERT INTO MAXIMO.APPDOCTYPE (APP, DOCTYPE, APPDOCTYPEID) VALUES ('MULTISITE', 'Attachments', (NEXT VALUE FOR MAXIMO.APPDOCTYPESEQ));
COMMIT;

For Oracle use this INSERT statement:
INSERT INTO MAXIMO.APPDOCTYPE (APP, DOCTYPE, APPDOCTYPEID) VALUES ('MULTISITE', 'Attachments', (MAXIMO.APPDOCTYPESEQ.NEXTVAL));
COMMIT;

You can check the table content with the following query:
SELECT * FROM MAXIMO.APPDOCTYPE WHERE APP='MULTISITE';

Now you should be able to attach documents and pictures to your organization.

April 4, 2011

How to schedule Preventive Maintenance at the first working day of each month

During one of my consultancies I was asked how it is possible to remind one person to produce a report each month. They want to generate the PM application to generate a work order for the first working day of each month.
This can be achieved with a tricky use of a 'Time Based Frequency' and 'Seasonal Dates' settings.
First you need to create a PM entry and set a frequency that is more than one week and less than one month. 20 days is a good choice. Leave the 'Use Last WO's Start Information to Calculate Next Due Frequency' option checked.


Then you need to configure the 'Seasonal Dates' unchecking the Sunday and Saturday options. Finally add twelve rows in the 'Active Dates' section specifying the first three days of each month. See the following screenshot.



Set the 'Estimated Next Due Date', activate the PM and generate the work orders for 360 days using the action menu. Go in the Work Order Tracking application and you will see your workorders.