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.
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.
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":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
UID
ibm11133739