Maximo Archiving with Optim 6.2.1 and 7.1: Quick start guide for extending Maximo archive definitions

Introduction

Maximo Archiving with Optim Data Growth includes definitions for archiving standard Maximo EAM tables related to

• Assets
• Invoices
• Purchase Orders
• Purchase Requisitions
• Service Requests (7.1 only)
• Work Orders
• Workflow Transactions

Most client implementations of the software will require that these definitions be extended to include custom tables, industry solutions and other installed products. Because Optim connects directly to the Maximo database and does not go through the Maximo business objects, the elements configured in the Maximo data dictionary, such as relationships, object and attribute definitions, cannot be 'discovered' by Optim. Optim is not compatible with XML at this point so the integration framework also cannot be leveraged for use - this data must be created in Optim. The standard archive definitions create this data for the default data models and when extension is required, additional data must be found in Maximo and entered into Optim.

This document provides details on the procedures that should be followed to extend the definitions using the extension of Maximo EAM to include tables related to Calibration for the Asset and Work Order definitions as an example.

Definitions

Archive Definition - A text (.txt) file that is imported into Optim so that it will 'understand' how to archive Maximo data. It is defined per application and includes an archive request, an access definition, several relationships, several primary keys, two restore requests, two insert requests and two table maps.

Archive Request - This process initiates an archive job. An associated Access Definition is required for the request. If records are to be deleted from the source tables, Primary Keys are also required for those tables.

Access Definition - The major element of archive configuration. This where tables to be archived are specified, what is deleted and what is not is determined, the Selection Criteria for the archive data is specified and the definitions of related tables is selected.

Selection Criteria - The definition of which records from a table are to be archived. Records can be selected based on attribute values or complex SQL.

Relationship - This is similar to a Maximo relationship but uses a different syntax. Optim relationships match records based on attributes in the parent/child tables. Complex SQL is not supported in relationships.

Primary Key - This is a list of attributes that constitute the table's key from Optim's perspective.

Restore Request - Restore requests move archive files back into databases. This could be back to the source database or to another database to be used for reporting or other access.

Insert Request - This is how Optim puts data into tables. Each Restore Request needs an Insert Request.

Table Map - Table maps allow for manipulation of data when it is inserted. They are used by Insert Requests and contain the qualifiers for the source and target databases being mapped.

Common Tasks

Tables

Determine Tables to be Added

Tables can be added to an existing access definition or you can create a whole new access definition with your tables for a new archive request. The first step is to determine the tables you need. In Maximo we archive tables that are persistent and are not views. To find the tables for your implementation, you can consult an ERD for your application or search the MAXOBJECT table. To find IBM extensions, for example, you would search like this:

select * from maxobject where objectname like '%PLUS%' and ISVIEW = 0 and PERSISTENT = 1

Once you have this list of tables, you can review it to determine which of these tables are relevant to be archived. You might want to count the records in the tables to see if they contain high volumes of data and should be archived as a new job or you may want to look at their relationships to see if they are related to other tables being archived and could result in incomplete data or orphaned records if they are not added.

For example, the following tables are returned with the Calibration solution installed:

• PLUSCASSETSTATUS
• PLUSCDSASSETLINK
• PLUSCDSINSTR
• PLUSCDSPOINT
• PLUSCDSSTATUS
• PLUSCJPDATASHEET
• PLUSCSPOTCHECK
• PLUSCWODS
• PLUSCWODSINSTR
• PLUSCWODSPOINT
• PLUSDSPLAN

The next step is to examine these tables to see which ones have relationships to objects being archived so should also be archived to avoid orphan records. In addition, see if any are high growth and should be archived in a new definition.

To determine if these tables are needed in the archive, you must determine how they are used. The first step is to query the MAXRELATIONSHIP table to see if there are relationships to the main objects of the standard archive definitions. Find relationships where ASSET, INVOICE, PO, PR or WORKORDER is the parent or child of a relationship containing one of the new tables. Relationships added for the purpose of lookups should not be considered.

For example, the following tables are appropriate to be archived with the Calibration solution installed:

• PLUSCASSETSTATUS
• PLUSCDSASSETLINK
• PLUSCDSPOINT
• PLUSCSPOTCHECK
• PLUSCWODS
• PLUSCWODSINSTR

After you whittle down the list of tables to what must be archived, determine if any of them use attached documents, bookmarks or are long description enabled.

To check if an object is Long Description enabled, run the following query:

select * from maxattribute where objectname = '<TABLETOBEADDED>' and isldowner = 1

For example, the following tables from the Calibration solution are long description enabled:

• PLUSCDSPOINT
• PLUSCSPOTCHECK

To check if an object has associated attachments, run the following query:

select * from doclinks where ownertable = '<TABLETOBEADDED>'

There is no example from the Calibration solution that can be used as users create attachments to records differently in every implementation.

Bookmarks are at the application level, not the object level, so you must determine the applications for the data being archived to see of bookmark data should also be archived. Once you have the list of applications, run the following query:

select * from bookmark where app = '<APPNAME>'

There is no example from the Calibration solution that can be used as users create bookmarks to records differently in every implementation.

If you are creating new definitions, be sure to check to see if your objects or attributes have LONGDESCRIPTION, DOCLINKS or BOOKMARK records that need to be archived as well.

Determine Relationships Required to Archive Tables

Once you determine the tables you will add to an existing archive definition or what tables you want in a new archive definition you need to explore the relationships needed to get the right data.

To find the relationships for the tables you will be adding run the following queries:

select * from maxrelationship where parent = '<TABLETOBEADDED>'select * from maxrelationship where child = '<TABLETOBEADDED>'

Review the resulting list of relationships. Many times there are multiple representations of the same table relationships. Only one needs to be defined in Optim. For example, if there is a relationship for parent to child and then child to parent matching on the same attributes, it only needs to be defined in Optim once. In addition, Optim relationships can only match an attribute in one table to an attribute in another table or a constant value. No 'AND' or 'OR' statements can be used in Optim relationships - they can be used in table selection criteria.

For example, to recreate this relationship in Optim:

Parent: WORKORDER

Child: LOCATIONS Where Clause:

(location=:location or location in (select location from workorder where parent=:wonum and location is not null)) and siteid=:siteid

You create an Optim relationship where WORKORDER is the parent and LOCATIONS is the child and SITEID = SITEID. You would then use the remainder of the Where Clause in the selection criteria for the LOCATIONS table in the access definition.

Relationships for LONGDESCRIPTION, BOOKMARK and DOCLINK are usually not found in the MAXRELATIONSHIP table so a bit more research needs to be done to determine the attributes for these relationships.

For LONGDESCRIPTION, you need to find the following:

For BOOKMARK you need to find the following:

For DOCLINK you need to find the following:

Determine Primary Keys for New Tables

When a table is to be deleted you will need to specify an Optim Primary Key for it. To find the columns for the table you are adding that should be in the Primary Key run the following query in the database for each table that you are adding:

select attributename from maxattribute where objectname = <TABLETOBEADDED> and primarykeycolseq is not null

The resulting columns should be added as primary keys using the procedures detailed below.

Add Tables

Tables are added to the Access Definition used by an archive request. To add a table to an existing Access Definition, open it and type the table name in the first empty row at the bottom of the table window. The vales for the Type and DBMS columns will default if the table name is valid. If you are using a relationship to find the values in this table, no table specification is required. If a table specification is required, for example, to execute a complex SQL query to find the data, it is entered here by right-clicking in that column to open the dialog. If the tables are to be deleted after archiving, also select the 'Delete Rows After Archive' check box.

This is the Tables tab in the Optim Access Definition configuration:

For example, to archive Calibration data for Assets along with the standard Asset data, add the following tables with the following settings to the Asset access definition provided with Maximo Archiving with Optim Data Growth Solution.

If you also want to archive Calibration data for Work Orders along with the standard Work Order data, add the following tables with the following settings to the Work Order access definition provided with Maximo Archiving with Optim Data Growth Solution.

Adding or Modifying Selection Criteria

If you want to select only certain records from a table you can do it with a relationship or a selection criteria. If there is a selection criteria on a table an icon will display in the Table Specifications column in the access definition. There are two basic ways to create a selection criteria:

1. Select records based on the values of one or more attributes.

To use this method, right click in the Table Specification column for the row of the table you want to create a selection criteria for, then pick Table Specifications and Selection Criteria. You will then be presented with a list of attributes where you can specify attribute values or Optim supported variables. See the Optim documentation for more detail.

This is the Selection Criteria dialog box:

1. Select Records with SQL

To use this method, you also right click in the Table Specification column for the table's row but you then navigate to the SQL tab. You can then specify your SQL or use the provided builder. If you want to use complex syntax such as like, and/or and other SQL conventions this is the method that must be used.

This is the SQL dialog box:

Add Relationships

To add the relationships you need into Optim, click File > New > Relationship from the main Optim menu. Select the parent table for the relationship, click Next then select the child table for the relationship and click Select. In the new dialog, enter the attributes or values that consist of the relationship.

For example, for the MAXRELATIONSHIP from WORKORDER to LOCATIONS with the where clause:

location = :location and siteid=:siteid

This is the Optim Relationship configuration:

With Calibration installed, add the following relationships for the Asset archive definition provided with Maximo Archiving with Optim Data Growth Solution.

With Calibration installed, add the following relationships for the Work Order archive definition provided with Maximo Archiving with Optim Data Growth Solution.

Associating Relationships with Access Definitions

In an Access Definition there is a tab for Relationships. Relationships can be viewed in this tab but not created or modified here. When you add a table to an access definition, all relationships for that table will be displayed on the Relationships tab. From this tab, select the relationships to use. Not all relationships for a table may be needed for an archive job. For example, the Work Order definition uses the relationship from Work Order to Asset but does not use all of the other relationships defined for Asset. Most of these relationships are used in the Asset definition. They will be displayed but not selected.

This is the Relationship tab in the Optim Access Definition configuration:

In addition, there are options for relationships when they are selected. By default, relationships are traversed from parent to child. With Option 1 enabled, the relationship will be re-traversed child to parent to find additional parents for the child - like an asset that could be listed on multiple work orders. Option 2 is selected to re-traverse the relationship again to find additional children of additional parents - like additional assets listed on the other work orders found. In most cases, these options are not required for our processing needs.

When a new relationship is added, it will be selected and have Option 1 enabled and will display with a 'New' status on an access definition until the definition is saved again. If you run an archive job with an access definition that has 'New' relationships it will present a warning but the archive job will run. Just save the changed access definition to get rid of these warnings once you have looked at the relationships and made the appropriate selections.

Add Primary Keys

For Oracle and SQL Server databases, primary keys are required to add or delete tables from databases. The RDBMS primary keys are used in DB2 so the Primary Key Editor does not need to be used to add primary keys for DB2 databases.

This is the Optim Primary Key configuration:

With Calibration installed, add the following primary keys to the Work Order archive definition provided with Maximo Archiving with Optim Data Growth Solution.

With Calibration installed, add the following primary keys to the Asset archive definition provided with Maximo Archiving with Optim Data Growth Solution.

Restore Requests, Table Maps and Insert Requests

No additions are required for these entities used in restoring data. These use the Access Definitions that you have updated for performing the archive job itself.

Appendix A

Research Worksheet

Tables to Add

For each table that you want to archive, enter the name, determine if there will be selection criteria for the table and decide if you will delete the rows from the source database after archiving records. If the record is only used for one thing (like a transaction specific to a work order) you probably want to delete them. If the record is used for more than one thing (like an asset that can be related to more than one work order) you probably do not want to delete it.

Selection Criteria

You can select records automatically with relationships or specifically using selection criteria. If you want to select specific records, enter the table, the attribute and the value you want to archive. For example, archive from the WORKORDER table where the STATUSDATE attribute is more than three years ago. If you want to archive based on more than one attribute, like STATUS and STATUSDATE you can enter multiple attributes on the same table. In addition, if you need to use complex SQL to select records when re-creating a MAXRELATIONSHIP, you specify it as selection criteria to extend the basic attributes in a relationship.

Relationships

When you define a new relationship, you need to decide what table is the parent and what table is the child. You then need to determine what attributes to match. Maximo relationship names are not a unique key so you probably won't be able to use the MAXRELATOINSHIP name for the new relationship. By default, relationships are traversed parent to child. If you also want them to be re-traversed child to parent, select 'Option 1'. If you also want to re-traverse back to find additional children select 'Option 2'. For example, a location has a child location which can also have another parent location and that second location can also have other children. Selection 'Option 1' and 'Option 2' will ensure that all these parents and children get found. If there is not a many to many relationship like this then these options are not necessary.

Primary Keys

If you will be deleting records from the database you will need to add Optim Primary Keys for those tables. A table's primary key will consist of one or more columns and can be found by looking in the MAXATTRIBUTE table for the table and seeing what columns are defined as primary keys.