Usage of ROWSTAMP column in Maximo tables

Resolving The Problem

Maximo creates a column called rowstamp for Oracle, Microsoft SQL Server and DB2 databases. Historically, the SQLBase ROWID changed whenever a record was updated. Maximo creates the rowstamp column to be used to determine if the row has been updated by another user. The rowstamp column is created in every table, and is defined as not null.

In Oracle, the column is populated by a trigger on each table. The trigger selects values from an Oracle object called a sequence. Every insert and update of a record in any Maximo table causes the trigger to be executed. The sequence is named MAXSEQ. The triggers are named tablename_T.

Note: Oracle has an internal column called ROWID which contains the address of the record (file#, block#, record#). The ROWID is populated by Oracle and cannot be modified. The ROWID is the fastest access path to an Oracle record.

In SQL Server, the rowstamp column is defined as a timestamp column. Whenever a row is inserted or updated, the rowstamp column is modified internally by the database engine. Note that the timestamp value is a unique value, but it is not a true date/timestamp.

In DB2, rowstamp column is defined as a not null integer. It is populated by an insert and update trigger on each table. The trigger selects values from a DB2 object called a sequence. Every insert and update of a record in any Maximo table causes the trigger to be executed. The sequence is named MAXSEQ and the triggers are named tablename_I and tablename_U.

The rowstamp is used to ensure that a user does not write over a record which has been modified by another user. This can happen if two users query a record, one user changes and saves the record, and the second user attempts to save the record. It is important to stop the second user from writing over (losing) changes that the first user has made.

Maximo updates the table based on the rowstamp. For example, when updating the companies table, the following SQL statement is generated by Maximo to ensure that the rowstamp column has not changed since the time the record was first queried:

update companies
set company='test', name='newtest',
changeby='MAXIMO', changedate='1997-05-08-16.47.51.000000'
where company = 'test' and rowstamp= '000000010000C424'

If the rowstamp has changed, the user will be informed that the record has changed and the update does not complete.


POPULATING Maximo TABLES

When inserting into Maximo tables using outside interfaces such as Excel or Access, the rowstamp column will be populated automatically. The Excel or Access table should not include the rowstamp column. If you are inserting using a SQL interface, the insert should ignore the rowstamp column.

For example, the altitem table has three columns: itemnum, altitemnum, rowstamp. When performing the insert, the statement should be coded as follows:

insert into altitem (itemnum, altitemnum) values ('ITEM1', 'ALTITEM1');

The rowstamp column will be populated automatically by the server.


ORACLE

The following is a sample trigger (on the equipment table):

create or replace trigger equipment_T
before insert or update on equipment
for each row
declare
nextval integer;
begin
select maxseq.NEXTVAL into nextval from dual;
:new.rowstamp := nextval;
end;
/

The following is the sql statement which creates the sequence, MAXSEQ:

create sequence maximo.maxseq nocycle nomaxvalue;

Recreating MAXSEQ - Oracle

The following is a procedure to determine the maximum rowstamp in each table, and the maximum overall. The maximum rowstamp will be printed as the last line of the output from the procedure. Modify the "create sequence ..." command below so that maxrowstamp + 1 is set to the maximum value returned plus one.

Detach the file as, for example, c:\maxrow.sql

Run SQL*Plus:
connect MAXIMO/password
@c:\maxrow.sql
set serveroutput on size 4000
execute maxrowstamp;
drop sequence maxseq;
create sequence maxseq start with maxrowstamp+1 nocycle nomaxvalue;
drop procedure maxrow_proc;
drop procedure maxrowstamp;
exit;