Import Data from DB into Maximo by using RMI with Jython

Body

Importing data is supported by standard Maximo Integration Framework as well as different tools from the business partner landscape.

In a past PoC I was forced to import massive data with bad quality, agile data structures (implies changing data structures from day to day) and last but not least pay regard on what was already imported into Maximo. Let me share my experience on how we did it.

Most of the mentioned tools using an approach of a 1:1 import of data, means you have to take care of data quality before you start import. By using a programming language you are more flexible to transfrom import data and even take care of semantics by focusing on the relation between records and interpreting their meaning.

In combination with a programming language as Jython and RMI (Java Remote Method Invocation) you can do your import job easily.

RMI is a protocol using stub/skeleton approach. A skeleton acts as gateway for (Maximo) server side objects and all incoming clients requests are routed through it. The skeleton wraps server object functionality and exposes it to the clients. The stub acts as a gateway for client side objects and all outgoing requests to server side objects that are routed through it.

Understanding the Maximo implementation of Java Remote Method Invocation is described at /support/pages/node/348041

Jython is an implementation of Python, written in the Java language and integrated with the Java platform. Jython is easy to learn, efficient to code and runs on any JVM.

Have a look on a simple Jython script to insert a new asset record into Maximo.

Later I will explain how to setup a Eclipse based environment and extend the script to fetch data from database table to import into Maximo.

In first stept we import class MXSession which coordinates a number of items that occur during an active session with the user. It looks after connect / disconnect from a MBO Server, holds connection to a MBO Server and maintains user and password information.

Second block returns a reference to the Maximo session object and connects to the Maximo server using the approprate credentials.

In the third block we retrieve an asset MBO set. Once the set has been retrieved, the addAtEnd - method adds a new MBO to this set at end, and sets the current MBO position to the MBO just added. Next we set string values to MBO attributes identified by name within the current object. And finally we send changes to the database for all objects in the collection (see save() - method).

Do not forget to disconnect from the server, this avoids orphaned user session on the server.

Before we can play with our first script, we do need to setup a scripting environment.

PyDev is an excellent and mature third-party plug-in for Eclipse, which adds support for the Python language and includes specialized support for Jython.

Install process is pretty straight forward :

• Jython and Java should be installed before installing Eclipse and PyDev plug-in
• minimum required JRE version depends on the Jython version used
  • Jython 2.5 requires Java 1.5 or newer
• install Jython
  • most current stable release of Jython is 2.5.3
  • install to c:\jython directory
• install Eclipse
  • best experience with Eclipse Standard SDK / Kepler Release v4.3 (Eclipse Luna Release v4.4 I did not tested so far)
  • use 32-bit version, even if your OS supports 64-bit
  • install to c:\eclipse
• install PyDev
  • detailed describtion found at http://www.jython.org/jythonbook/en/1.0/JythonIDE.html

Above documentation will also give you some short instructions on how to build projects and execute modules in PyDev environment.

There is also a section which shows you on how to integrate Java libraries into your project. In our example we need at least the Java libraries to communicate to Maximo server and the database JDBC libraries (in our case for Oracle) :

• businessobjects.jar           Maximo business logic to extract from your maximo.ear
• icu4j.jar                              provides globalization technology into Java , also extract from maximo.ear
• ojdbc6.jar                           contains JDBC driver classes, to be fetched from official Oracle download page

Now we are ready to import data from database table into Maximo. Basic structure of our script follows an easy logic :

•                import Maximo libraries
•                open Maximo session and connect as user
•                import database libraries
•                use java dynamic class to load driver and register
•                get database connection to import table data
•                create SQL  statement and execute query for import data
•                return MBO set for the specified type of business object   
•                loop / iterate SQL result set to fetch import data
  •                process import data from database into Maximo
•                end-loop
•                disconnect from Maximo and import database

How to import Maximo libraries, open Maximo session and connect as user was already explained earlier.

In the next step we import database libraries and use java dynamic class to load and register JDBC driver.

Afterwards we create SQL statement and execute query for import data.

The import table DATA4IMP does have two varchar - columns ( EQ_ID / EQ_DESC ).

Next we return MBO set for the specified type of business object - in our example ASSET.

In the while - loop we iterate over the records from SQL result set to fetch import data and process against Maximo. The where clause of the Maximo recordset alllows us to determine if a record already exist in Maximo. Depending on return value of the recordset.count() method, we do an insert or update against Maximo.

Do not forget to disconnect from Maximo and database.

Full code can be downloaded here :

http://maximo.w4f.eu/downloads/import_basic_script.py

http://maximo.w4f.eu/downloads/import_ora2mx_without_exception_handling.py

http://maximo.w4f.eu/downloads/import_ora2mx_with_exception_handling.py

There are 2 versions of <import_ora2mx> scripts. One is easier to read, the other one does include exception handling.