Troubleshooting
Problem
In IBM Rational Build Forge, filtering for System Admin and then sorting the results causes SQL Error com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -206, SQLSTATE:42703, SQLERRMC: BF_STRING.BF_MESSAGE
Symptom
The catalina log shows:
...
WARNING: Filtered query row count, compiled query : SELECT * FROM (SELECT bf_sysconfig.bf_param, bf_sysconfig.bf_title, bf_sysconfig.bf_value, bf_sysconfig.bf_datatype, rownumber() OVER ( ORDER BY bf_sysconfig.bf_value) bf_db2_idx FROM bf_sysconfig WHERE (bf_sysconfig.bf_public = 'Y' AND bf_strings.bf_message LIKE '%lic%' ESCAPE '\') ORDER BY bf_sysconfig.bf_value) bf_db2_rows WHERE bf_db2_rows.bf_db2_idx BETWEEN 1 AND 1000 FOR READ ONLY
com.buildforge.services.common.db.DBException: Failed SQL query: [org.apache.commons.dbcp.DelegatingPreparedStatement@16a416a4] => [com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: BF_STRINGS.BF_MESSAGE]
at com.buildforge.services.server.db.DBConnection.errorQueryFailed(DBConnection.java:1262)
at com.buildforge.services.server.db.DBConnection.query(DBConnection.java:1104)
at com.buildforge.services.server.db.DBConnection.getFilterMatchCount(DBConnection.java:685)
at com.buildforge.services.server.db.DBConnection.getQueryContextForFilteredQuery(DBConnection.java:828)
at com.buildforge.services.server.db.DBConnection.doFilteredQuery(DBConnection.java:718)
at com.buildforge.services.server.manager.SysConfigManager.findFiltered(SysConfigManager.java:466)
at com.buildforge.services.server.api.commands.SysConfigCommands.findFiltered(SysConfigCommands.java:91)
at com.buildforge.services.server.api.commands.SysConfigCommands.invoke(SysConfigCommands.java:79)
at com.buildforge.services.server.api.APICommandProcessor.process(APICommandProcessor.java:261)
at com.buildforge.services.server.api.APIServerConnection.handleRequest(APIServerConnection.java:206)
at com.buildforge.services.server.api.APIServerConnection.process(APIServerConnection.java:153)
at com.buildforge.services.server.dispatch.handler.BufferedConnection.run(BufferedConnection.java:243)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:665)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:690)
at java.lang.Thread.run(Thread.java:810)
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: BF_STRINGS.BF_MESSAGE
at com.ibm.db2.jcc.a.lg.e(lg.java:1600)
at com.ibm.db2.jcc.a.lg.a(lg.java:1210)
at com.ibm.db2.jcc.b.gb.g(gb.java:140)
at com.ibm.db2.jcc.b.gb.a(gb.java:39)
at com.ibm.db2.jcc.b.w.a(w.java:34)
at com.ibm.db2.jcc.b.vb.f(vb.java:139)
at com.ibm.db2.jcc.a.lg.n(lg.java:1181)
at com.ibm.db2.jcc.a.mg.eb(mg.java:1877)
at com.ibm.db2.jcc.a.mg.d(mg.java:2313)
at com.ibm.db2.jcc.a.mg.V(mg.java:430)
at com.ibm.db2.jcc.a.mg.executeQuery(mg.java:413)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at com.buildforge.services.server.db.DBConnection.query(DBConnection.java:1102)
... 13 more
...
WARNING: !!!
com.buildforge.services.common.db.DBException: Failed SQL query: [org.apache.commons.dbcp.DelegatingPreparedStatement@16a416a4] => [com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: BF_STRINGS.BF_MESSAGE]
at com.buildforge.services.server.db.DBConnection.errorQueryFailed(DBConnection.java:1262)
at com.buildforge.services.server.db.DBConnection.query(DBConnection.java:1104)
at com.buildforge.services.server.db.DBConnection.getFilterMatchCount(DBConnection.java:685)
at com.buildforge.services.server.db.DBConnection.getQueryContextForFilteredQuery(DBConnection.java:828)
at com.buildforge.services.server.db.DBConnection.doFilteredQuery(DBConnection.java:718)
at com.buildforge.services.server.manager.SysConfigManager.findFiltered(SysConfigManager.java:466)
at com.buildforge.services.server.api.commands.SysConfigCommands.findFiltered(SysConfigCommands.java:91)
at com.buildforge.services.server.api.commands.SysConfigCommands.invoke(SysConfigCommands.java:79)
at com.buildforge.services.server.api.APICommandProcessor.process(APICommandProcessor.java:261)
at com.buildforge.services.server.api.APIServerConnection.handleRequest(APIServerConnection.java:206)
at com.buildforge.services.server.api.APIServerConnection.process(APIServerConnection.java:153)
at com.buildforge.services.server.dispatch.handler.BufferedConnection.run(BufferedConnection.java:243)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:665)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:690)
at java.lang.Thread.run(Thread.java:810)
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: BF_STRINGS.BF_MESSAGE
at com.ibm.db2.jcc.a.lg.e(lg.java:1600)
at com.ibm.db2.jcc.a.lg.a(lg.java:1210)
at com.ibm.db2.jcc.b.gb.g(gb.java:140)
at com.ibm.db2.jcc.b.gb.a(gb.java:39)
at com.ibm.db2.jcc.b.w.a(w.java:34)
at com.ibm.db2.jcc.b.vb.f(vb.java:139)
at com.ibm.db2.jcc.a.lg.n(lg.java:1181)
at com.ibm.db2.jcc.a.mg.eb(mg.java:1877)
at com.ibm.db2.jcc.a.mg.d(mg.java:2313)
at com.ibm.db2.jcc.a.mg.V(mg.java:430)
at com.ibm.db2.jcc.a.mg.executeQuery(mg.java:413)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at com.buildforge.services.server.db.DBConnection.query(DBConnection.java:1102)
... 13 more
The query is missing a join. Specifically this:
SELECT * FROM (SELECT bf_sysconfig.bf_param, bf_sysconfig.bf_title, bf_sysconfig.bf_value, bf_sysconfig.bf_datatype, rownumber() OVER ( ORDER BY bf_sysconfig.bf_value) bf_db2_idx FROM bf_sysconfig WHERE (bf_sysconfig.bf_public = 'Y' AND bf_strings.bf_message LIKE '%lic%' ESCAPE '\') ORDER BY bf_sysconfig.bf_value) bf_db2_rows WHERE bf_db2_rows.bf_db2_idx BETWEEN 1 AND 1000 FOR READ ONLY
should be this:
SELECT * FROM (SELECT bf_sysconfig.bf_param, bf_sysconfig.bf_title, bf_sysconfig.bf_value, bf_sysconfig.bf_datatype, rownumber() OVER ( ORDER BY bf_strings.bf_message) bf_db2_idx FROM bf_sysconfig LEFT JOIN bf_strings ON (bf_sysconfig.bf_title = bf_strings.bf_key AND bf_strings.bf_lang = 'en_US') WHERE (bf_sysconfig.bf_public = 'Y' AND (bf_strings.bf_message LIKE '%SMTP%' ESCAPE '\' OR bf_sysconfig.bf_value LIKE '%SMTP%' ESCAPE '\')) ORDER BY bf_strings.bf_message) bf_db2_rows WHERE bf_db2_rows.bf_db2_idx BETWEEN 1 AND 1000 FOR READ ONLY
NOTE: This happens with all DBs. It only happens if you sort by the Value column - sorting by the Name column does not cause this issue.
Once you browse away from Admin > System you will be unable to bring the Admin > System page back.
Cause
This issue has been identified as a product defect and has been logged under APAR PM11486
Diagnosing The Problem
To reproduce:
1) Log into Build Forge
2) Browse to Admin > System
3) Sort by the value column
4) Apply a UI filter
Expected result:
The filter would be applied and the result set would be sorted based on value
Actual Result:
The UI emits the error:
Failed SQL query: [org.apache.commons.dbcp.DelegatingPreparedStatement@16a416a4] => [com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: BF_STRINGS.BF_MESSAGE]
Resolving The Problem
This issue is fixed in Rational Build Forge fix pack 7.1.1.4.
Workaround:
Remove the offending rows from bf_preferences using:
DELETE FROM bf_preferences WHERE bf_name LIKE 'page:administration.system.view%';
COMMIT;
This will remove two rows which cause the issue - one or the column to sort with, and one which determines the sort order.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21426826