Vivek Agarwal’s Portal/Java Blog

An IBM Gold Consultant’s weblog about IBM, Lotus, WebSphere, J2EE, IT Processes, and other IT technologies

Resolved an exception stacktrace related to Quickr/WebSphere Portal search tables

Posted by Vivek Agarwal on July 14, 2008


Recently we wanted to duplicate an existing Quickr test server from one machine to another without going through the entire process of installing Quickr, configuring security, configuring an external web server, installing a fixpack and efixes, installing our custom applications, and preserving all the test Quickr teamplaces and content. We got this to work pretty cleanly by restoring a file system backup of Quickr and a backup of the Quickr DB2 database. We did this for one of our test servers, but ran into an exception when we tried to duplicate another test server.

[7/14/08 10:29:36:953 CDT] 0000003b JCRCFLLoggerI E com.ibm.icm.ts.tss.JCRCFLLoggerImpl com.ibm.icm.ts.tss.ls.LibraryServerImpl.removeExpiredTableClaims [java.lang.ThreadGroup[name=icmciWorkManager: icmjcrear,maxpri=10]]: Error creating new search result table: {0}. com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -601, SQLSTATE: 42710, SQLERRMC: JCR.TSSTBL_1;TABLE

            at com.ibm.db2.jcc.a.lg.e(lg.java:1600)

            at com.ibm.db2.jcc.a.lg.b(lg.java:1164)

            at com.ibm.db2.jcc.b.gb.h(gb.java:217)

            at com.ibm.db2.jcc.b.gb.b(gb.java:46)

            at com.ibm.db2.jcc.b.w.b(w.java:40)

            at com.ibm.db2.jcc.b.vb.e(vb.java:118)

            at com.ibm.db2.jcc.a.lg.m(lg.java:1159)

            at com.ibm.db2.jcc.a.lg.a(lg.java:1874)

            at com.ibm.db2.jcc.a.lg.c(lg.java:520)

            at com.ibm.db2.jcc.a.lg.executeUpdate(lg.java:504)

            at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.pmiExecuteUpdate(WSJdbcStatement.java:1235)

            at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeUpdate(WSJdbcStatement.java:738)

            at com.ibm.icm.ts.tss.ls.BaseDBImpl.executeUpdate(BaseDBImpl.java:248)

            at com.ibm.icm.ts.tss.ls.BaseDBImpl.createTable(BaseDBImpl.java:306)

            at com.ibm.icm.ts.tss.ls.LibraryServerImpl.removeExpiredTableClaims(LibraryServerImpl.java:450)

            at com.ibm.icm.ts.tss.ls.LibraryServerImpl.state(LibraryServerImpl.java:53)

            at com.ibm.icm.ts.tss.app.Janitor.cleanupLibraryServer(Janitor.java:43)

            at com.ibm.icm.ts.tss.app.JanitorService.cleanupLibraryServer(JanitorService.java:66)

            at com.ibm.icm.ts.tss.app.JanitorService.access$000(JanitorService.java:22)

            at com.ibm.icm.ts.tss.app.JanitorService$Notifiee.onEvent(JanitorService.java:110)

            at com.ibm.icm.ts.act.ActivityImpl$ManagerImpl$ActivityRunner.runActivity(ActivityImpl.java:333)

            at com.ibm.icm.ts.act.ActivityImpl$ManagerImpl$ActivityRunner.run(ActivityImpl.java:303)

            at com.ibm.hrl.workmanager.RunnableWrapper.run(RunnableWrapper.java:44)

            at com.ibm.hrl.workmanager.impl.was.WasWork.run(WasWork.java:75)

            at com.ibm.ws.asynchbeans.J2EEContext$RunProxy.run(J2EEContext.java:258)

            at java.security.AccessController.doPrivileged1(Native Method)

            at java.security.AccessController.doPrivileged(AccessController.java(Compiled Code))

            at javax.security.auth.Subject.doAs(Subject.java:477)

            at com.ibm.websphere.security.auth.WSSubject.doAs(WSSubject.java:118)

            at com.ibm.ws.asynchbeans.J2EEContext$DoAsProxy.run(J2EEContext.java:325)

            at java.security.AccessController.doPrivileged1(Native Method)

            at java.security.AccessController.doPrivileged(AccessController.java:351)

            at com.ibm.ws.asynchbeans.J2EEContext.run(J2EEContext.java:709)

            at com.ibm.ws.asynchbeans.WorkWithExecutionContextImpl.go(WorkWithExecutionContextImpl.java:218)

            at com.ibm.ws.asynchbeans.ABWorkItemImpl.run(ABWorkItemImpl.java:154)

            at java.lang.Thread.run(Thread.java:570)

[7/14/08 10:29:36:969 CDT] 0000003b JCRCFLLoggerI E com.ibm.icm.ts.tss.JCRCFLLoggerImpl com.ibm.icm.ts.tss.app.Janitor.cleanupLibraryServer [java.lang.ThreadGroup[name=icmciWorkManager: icmjcrear,maxpri=10]]: Error getting the library server. com.ibm.icm.ts.tss.ls.DatabaseException: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -601, SQLSTATE: 42710, SQLERRMC: JCR.TSSTBL_1;TABLE

            at com.ibm.icm.ts.tss.ls.LibraryServerImpl.removeExpiredTableClaims(LibraryServerImpl.java:455)

            at com.ibm.icm.ts.tss.ls.LibraryServerImpl.state(LibraryServerImpl.java:53)

            at com.ibm.icm.ts.tss.app.Janitor.cleanupLibraryServer(Janitor.java:43)

            at com.ibm.icm.ts.tss.app.JanitorService.cleanupLibraryServer(JanitorService.java:66)

            at com.ibm.icm.ts.tss.app.JanitorService.access$000(JanitorService.java:22)

            at com.ibm.icm.ts.tss.app.JanitorService$Notifiee.onEvent(JanitorService.java:110)

            at com.ibm.icm.ts.act.ActivityImpl$ManagerImpl$ActivityRunner.runActivity(ActivityImpl.java:333)

            at com.ibm.icm.ts.act.ActivityImpl$ManagerImpl$ActivityRunner.run(ActivityImpl.java:303)

            at com.ibm.hrl.workmanager.RunnableWrapper.run(RunnableWrapper.java:44)

            at com.ibm.hrl.workmanager.impl.was.WasWork.run(WasWork.java:75)

            at com.ibm.ws.asynchbeans.J2EEContext$RunProxy.run(J2EEContext.java:258)

            at java.security.AccessController.doPrivileged1(Native Method)

            at java.security.AccessController.doPrivileged(AccessController.java(Compiled Code))

            at javax.security.auth.Subject.doAs(Subject.java:477)

            at com.ibm.websphere.security.auth.WSSubject.doAs(WSSubject.java:118)

            at com.ibm.ws.asynchbeans.J2EEContext$DoAsProxy.run(J2EEContext.java:325)

            at java.security.AccessController.doPrivileged1(Native Method)

            at java.security.AccessController.doPrivileged(AccessController.java:351)

            at com.ibm.ws.asynchbeans.J2EEContext.run(J2EEContext.java:709)

            at com.ibm.ws.asynchbeans.WorkWithExecutionContextImpl.go(WorkWithExecutionContextImpl.java:218)

            at com.ibm.ws.asynchbeans.ABWorkItemImpl.run(ABWorkItemImpl.java:154)

            at java.lang.Thread.run(Thread.java:570)

Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -601, SQLSTATE: 42710, SQLERRMC: JCR.TSSTBL_1;TABLE

            at com.ibm.db2.jcc.a.lg.e(lg.java:1600)

            at com.ibm.db2.jcc.a.lg.b(lg.java:1164)

            at com.ibm.db2.jcc.b.gb.h(gb.java:217)

            at com.ibm.db2.jcc.b.gb.b(gb.java:46)

            at com.ibm.db2.jcc.b.w.b(w.java:40)

            at com.ibm.db2.jcc.b.vb.e(vb.java:118)

            at com.ibm.db2.jcc.a.lg.m(lg.java:1159)

            at com.ibm.db2.jcc.a.lg.a(lg.java:1874)

            at com.ibm.db2.jcc.a.lg.c(lg.java:520)

            at com.ibm.db2.jcc.a.lg.executeUpdate(lg.java:504)

            at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.pmiExecuteUpdate(WSJdbcStatement.java:1235)

            at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeUpdate(WSJdbcStatement.java:738)

            at com.ibm.icm.ts.tss.ls.BaseDBImpl.executeUpdate(BaseDBImpl.java:248)

            at com.ibm.icm.ts.tss.ls.BaseDBImpl.createTable(BaseDBImpl.java:306)

            at com.ibm.icm.ts.tss.ls.LibraryServerImpl.removeExpiredTableClaims(LibraryServerImpl.java:450)

            ... 21 more

From the stacktrace, it was clear that the issue was related to Quickr trying to create a table named TSSTBL_1 that already existed. From one of the IBM performance tuning notes, I knew that on the very first search, the system creates twenty database tables named TSSTBL_1, TSSTBL_2 … TSSTBL_20 in the JCR database for search information. That note also talked about the fact that the system can dynamically generate more TSSTBLs. From our earlier investigations of JCR replication, I knew about the table named ICMSTJCRTSTABLES that tracks what TSSTBLs exist. On looking into the Quickr DB2 database I could see that there were the 20 TSSTBLs and there were 20 rows in ICMSTJCRTSTABLES – one for each of them. Given that Quickr (or WebSphere Portal) automatically creates the TSSTBLs on the first search, I figured that if I deleted the TSSTBLs and the 20 rows in ICMSTJCRTSTABLES, I could get Portal to recreate the tables and things ought to work right without this extraneous exception cluttering up the log. Well, that is what I did (drop the TSSTBL tables and deleting all the rows from ICMSTJCRTSTABLES using the DB2 command editor – first time that I ever used that), restarted Portal (actually Quickr), performed a search, and sure enough the TSSTBL tables got recreated and the exception was gone. I was pretty pleased with myself for figuring out this solution in about a hour – now did I ever claim to be modest?! 🙂

Advertisements

5 Responses to “Resolved an exception stacktrace related to Quickr/WebSphere Portal search tables”

  1. Lakshminarasimhan said

    Thanks a lot for this information! We had similar problem on our WPS Servers (6.0.1.3).One of the tables got corrupted (TSSTBL_1). I followed the steps mentioned by you and the problem is solved now.

  2. Raj said

    Hi Guys,

    I’ve got into the same issues TSSTBL_1 tables in portal, WCM environment. Do you know the contents of the TSSTBL and risks of dropping and re-creating the tables.

    Thanks very much.

    Appreciate your feedback.

  3. Vivek Agarwal said

    Raj,

    The TSSTBL_* tables contain search information and it is safe to drop/recreate them. If only one search is running at a time – only one table gets used; as more concurrent searches are executed, more tables are used. These tables only contain transient information related to searches and IMO it is safe to drop/recreate them.

    All the best!

    -Vivek.

  4. Emil said

    Hi Vivek,

    very useful and talent finding you give us about the TSSTBL_* tables. Could you tell if these transient JCR search indexes are used by the WCM syndication process?

    Regards,

    Emil

  5. performance…

    […]Resolved an exception stacktrace related to Quickr/WebSphere Portal search tables « Vivek Agarwal’s Portal/Java Blog[…]…

Sorry, the comment form is closed at this time.

 
%d bloggers like this: