IBM Support

Stored Procedures and Maximo Reporting

Technical Blog Post


Abstract

Stored Procedures and Maximo Reporting

Body

Stored procedures are either a loved or hated feature of enterprise database technologies on the market today.  They are usually loved by Database Administrators and hated by everyone else.  Creating even a simple stored procedure can be a daunting task to an experienced database analyst.  Yet, chances are if you are developing custom reports in Maximo, you may have access to a wealth of stored procedures and never realized you could use them to drive your business intelligence outputs until now!

For the purposes of this write-up, the focus will be on the scripted data source which is used universally throughout all out-of-the-box Maximo reports.  

In the example below, you will find two examples of a simple stored procedure developed for a DB2 database.   NOTE:  This is for DB2 Only.   The specific details for SQL Server and Oracle are not covered.

When called (executed), both will receive two input parameters (organization and site).  However, the first example will return back values stored in output parameters and the second example will return back a full result set.  Only Example 2 will work with the scripted data source.
Example 1:  
CREATE PROCEDURE MAXIMO.GETSITEORGJOBPLANCOUNT(IN org_id varchar(8), IN site_id varchar(8), OUT organization varchar(8), OUT site varchar(8), OUT jobresult integer)
RESULT SETS 1
LANGUAGE SQL
begin
DECLARE jobcount CURSOR WITH RETURN FOR
SELECT orgid, siteid, count(jpnum)  FROM jobplan  WHERE orgid = org_id AND siteid = site_id group by orgid, siteid order by orgid, siteid;  
OPEN jobcount;    
FETCH jobcount INTO organization, site, jobresult;
CLOSE jobcount;
End
 
Example 2:
CREATE PROCEDURE MAXIMO.GETSITEORGJOBPLANCOUNT(IN org_id varchar(8), IN site_id varchar(8))
RESULT SETS 1
LANGUAGE SQL
begin
DECLARE jobcount CURSOR WITH RETURN FOR
SELECT orgid, siteid, count(jpnum) as jobresult  FROM jobplan  WHERE orgid = org_id AND siteid = site_id group by orgid, siteid order by orgid, siteid;  
OPEN jobcount;    
RETURN;
end

In the scripted data source, the ability to retrieve and interpret output parameters is not available.  This is why Example 1 would not work.  By returning back a full data set in Example 2, the data source is able to interpret the tables, columns and data returned and successfully display the data on the report.

After the stored procedure is created on the database, rights must be granted to database users in order to use it.
GRANT EXECUTE ON PROCEDURE MAXIMO.GETSITEORGJOBPLANCOUNT TO DB2ADMIN

Finally, the scripted data source needs to be developed to execute the stored procedure.
 
image
  
Whether you love or hate stored procedures, they are a powerful tool available in today’s enterprise database technologies.  Since all of the work is done on the database server, if used correctly, they can significantly reduce the runtime of a report with considerable overhead.  For this reason, stored procedures can be considered when developing your Maximo BIRT reports.
 
 
 

[{"Business Unit":{"code":"BU005","label":"IoT"}, "Product":{"code":"SSLKT6","label":"Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11133739