IF you are using IBM DB2 9.7 database, following error is thrown when browsing security audit logs:
2011.03.10 01:23:12 DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=63, DRIVER=3.61.65
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=63, DRIVER=3.61.65
at com.ibm.db2.jcc.am.ed.a(ed.java:676)
at com.ibm.db2.jcc.am.ed.a(ed.java:60)
at com.ibm.db2.jcc.am.ed.a(ed.java:127)
at com.ibm.db2.jcc.am.gn.c(gn.java:2554)
at com.ibm.db2.jcc.am.gn.d(gn.java:2542)
at com.ibm.db2.jcc.am.gn.a(gn.java:2034)
at com.ibm.db2.jcc.am.hn.a(hn.java:6500)
at com.ibm.db2.jcc.t4.cb.g(cb.java:140)
at com.ibm.db2.jcc.t4.cb.a(cb.java:40)
at com.ibm.db2.jcc.t4.q.a(q.java:32)
at com.ibm.db2.jcc.t4.rb.i(rb.java:135)
at com.ibm.db2.jcc.am.gn.gb(gn.java:2005)
at com.ibm.db2.jcc.am.hn.qc(hn.java:3053)
at com.ibm.db2.jcc.am.hn.b(hn.java:3838)
at com.ibm.db2.jcc.am.hn.dc(hn.java:683)
at com.ibm.db2.jcc.am.hn.executeQuery(hn.java:657)
at sun.reflect.GeneratedMethodAccessor7.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at com.ibm.db2.jcc.am.kn$$EnhancerByProxool$$5b7c2e02.executeQuery()
at org.jivesoftware.database.ProfiledConnection$TimedPreparedStatement.executeQuer y(ProfiledConnection.java:780)
at org.jivesoftware.openfire.security.DefaultSecurityAuditProvider.getEvents(Defau ltSecurityAuditProvider.java:141)
at org.jivesoftware.openfire.security.SecurityAuditManager.getEvents(SecurityAudit Manager.java:160)
at org.jivesoftware.openfire.admin.security_002daudit_002dviewer_jsp._jspService(s ecurity_002daudit_002dviewer_jsp.java:190)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:530)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1216)
at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:11 8)
at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:52)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)
at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:74)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)
at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingF ilter.java:50)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)
at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:78)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)
at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:164)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:425)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:494)
at org.eclipse.jetty.server.session.SessionHandler.handle(SessionHandler.java:182)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:93 3)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:362)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:867 )
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandler Collection.java:245)
at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.jav a:126)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:113)
at org.eclipse.jetty.server.Server.handle(Server.java:334)
at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:559)
at org.eclipse.jetty.server.HttpConnection$RequestHandler.headerComplete(HttpConne ction.java:992)
at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:541)
at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:203)
at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:406)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:4 62)
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:436)
at java.lang.Thread.run(Unknown Source)
SQL statement which caused this is:
SELECT msgID,username,entryStamp,summary,node,details FROM ofSecurityAuditLog ORDER BY entryStamp DESC
Problem is not in SELECT itself, it is in datatypes used and cursor type requested:
DB2 Error is:
SQL0270N Function not supported (Reason code = “63”)
Error desc:
63 A column with a LOB type, distinct type on a LOB type, or
structured type cannot be specified in the select-list of an
insensitive scrollable cursor.
USer response:
63 Modify the select-list of the scrollable cursor to not
include a column with these types.
Proposed fix:
Try sensitive scrollable cursor instead and if it fails too, go for non scrollable cursor which should be good enough for selecting rows too.