Troubleshooting
Problem
Unable to access application due to error on DB2 while the server is added to the MAXSESSION object / table because of MAXSESSION_T trigger.
Symptom
Cannot log in to Maximo. After starting up system and trying to log in you will see in the logs the following error message (sample):
[17/03/13 03:40:43:625 EST] 00000018 SystemOut O 17 Mar 2013
03:40:43:625 [ERROR] BMXAA6424E - The server was not added to the
Maxsession table: MXServer
com.ibm.db2.jcc.b.SqlException: An error occurred in a triggered SQL
statement in trigger "MAXIMO.MAXSESSION_T". Information returned for
the error includes SQLCODE "-359", SQLSTATE "23522" and message tokens
"".. SQLCODE=-723, SQLSTATE=09000, DRIVER=3.53.70
at com.ibm.db2.jcc.b.bd.a(bd.java:679)
at com.ibm.db2.jcc.b.bd.a(bd.java:60)
at com.ibm.db2.jcc.b.bd.a(bd.java:127)
at com.ibm.db2.jcc.b.fm.b(fm.java:2132)
at com.ibm.db2.jcc.b.fm.c(fm.java:2115)
at com.ibm.db2.jcc.t4.db.k(db.java:353)
at com.ibm.db2.jcc.t4.db.a(db.java:59)
at com.ibm.db2.jcc.t4.t.a(t.java:50)
at com.ibm.db2.jcc.t4.tb.b(tb.java:200)
at com.ibm.db2.jcc.b.gm.Zb(gm.java:2445)
at com.ibm.db2.jcc.b.gm.e(gm.java:3287)
at com.ibm.db2.jcc.b.gm.Rb(gm.java:612)
at com.ibm.db2.jcc.b.gm.executeUpdate(gm.java:595)
at psdi.server.MaxSession.addServerToMaxSession(MaxSession.java:72)
at psdi.server.MXServer.start(MXServer.java:3095)
at psdi.servlet.MAXIMOStartupServlet.init(MAXIMOStartupServlet.java:63)
Cause
DB2 SQL error code -359 will tell us the following:
THE RANGE OF VALUES FOR THE IDENTITY COLUMN OR SEQUENCE IS EXHAUSTED
Explanation
DB2® attempted to generate a value for an identity column or a sequence object. However, all allowable values have already been assigned.
DB2 SQL error code -723 will tell us the following:
AN ERROR OCCURRED IN A TRIGGERED SQL STATEMENT IN trigger-name. INFORMATION RETURNED: SQLCODE: sql-code, SQLSTATE: sql-state, MESSAGE TOKENS token-list, SECTION NUMBER section-number.
Explanation
During execution of an UPDATE, MERGE, INSERT, or DELETE statement, a trigger was activated. One of the triggered SQL statements received an SQL error condition.
This means when trigger MAXIMO.MAXSESSION_T is fired when a record is created no MAXSESSION object / table the value of the sequence it refers to is exhausted and needs to be restarted.
The likely reason for MAXSEQ sequence to reach its maximum value can be the result of abnormal DB2 termination, DB2 or kernel upgrade. There is a IBM technote that describes how to restart db2 sequence from the required number ("Restarting Sequence with the specific value on DB2" – http://www.ibm.com/support/docview.wss?uid=swg21291596).
Environment
MAM 7.1 and MAM 7.5 using IBM DB2 database.
Diagnosing The Problem
See on the system's log the error message BMXAA6424E followed by: An error occurred in a triggered SQL statement in trigger "MAXIMO.MAXSESSION_T". Information returned for
the error includes SQLCODE "-359", SQLSTATE "23522".
Resolving The Problem
R1) Stop all JVMs;
R2) Make sure you have a good backup of your DB2 database;
R3) Execute against the DB2 Maximo database the following update:
alter sequence maxseq restart
R4) Make sure the following returns "1" as outcome:
select nextval for maxseq from dummy_table
R5) Delete the old system's logs (SystemOut, SystemErr, FFDC log folder, maximo log, etc) or move them out of Maximo folder (e.g. C:\temp folder);
R6) Clear up the records related to MAXSESSION object:
TRUNCATE TABLE MAXSESSION ;
TRUNCATE TABLE LOGINTRACKING;
***NOTE: This will bring no harm to your system once JVMs are stopped and both objects will contain dynamic login information. If no user is connected, this should be empty.
R7) Restart Maximo application (JVMs);
***NOTE: Restarting the MAXSEQ sequence from 1 will cause no issues because the ROWSTAMP column is not unique and has no constraints on it. There might be a really rare situation when system may get a record accessed by 2 transactions having the same key attribute values and ROWSTAMP (unlikely situation). Please, see more details on the technote: “Usage of ROWSTAMP column in Maximo tables” - http://www.ibm.com/support/docview.wss?uid=swg21262756
Related Information
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21628869