Skip to Main Content
Digital Business Automation Ideas

Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:

Post your ideas

Start by posting ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea

Help IBM prioritize your ideas and requests

The IBM team may need your help to refine the ideas so they may ask for more information or feedback. The offering manager team will then decide if they can begin working on your idea. If they can start during the next development cycle, they will put the idea on the priority list. Each team at IBM works on a different schedule, where some ideas can be implemented right away, others may be placed on a different schedule.

Receive notifications on the decision

Some ideas can be implemented at IBM, while others may not fit within the development plans for the product. In either case, the team will let you know as soon as possible. In some cases, we may be able to find alternatives for ideas that cannot be implemented in a reasonable time.

Please use the following category to raise ideas for these offerings for all environments (traditional on premises, containers, on cloud):

  • Cloud Pak for Automation - incl Business Automation Studio, Business Automation Insights

  • Business Automation Workflow (BAW) - incl BAW, Business Process Manager, Workstream Services, Business Performance Center, Advanced Case Management

  • Content and Capture Services - incl Filenet, Automation Document Processing, Daeja, Navigator, Content Collector, Enterprise Records, Business Automation Content Analyzer, Datacap, Automation Mobile Capture, Content Manager OnDemand, IBM Content Manager

  • Automation Decision Services (ADS) - incl ADS, Operational Decision Manager

  • Robotic Process Automation

  • Robotic Process Automation with Automation Anywhere

  • IBM Blueworks Live

If you encounter any issues accessing the Ideas portals, please send email describing the issue to ideasibm@us.ibm.com for resolution. For more information about IBM's Ideas program visit ibm.com/ideas.

Status Future consideration
Created by Guest
Created on Apr 3, 2019

lower() DB function renders idex on LSW_BPD_INSTANCE ineffective

Description of the issue; we started seeing “SQLCODE=-905, SQLSTATE=57014”. At that time, process instance creation was successful but all fetch operation failed. See ticket number - TS002094734; this ticket number failed the PMR validation above.

[3/28/19 15:57:10:482 EDT] 0000dedf ProgrammaticT W CWLLG2103W: The retry limit of 5 was exceeded in ProgrammaticTransactionSupport.
[3/28/19 15:57:10:483 EDT] 0000dedf wle E CWLLG2229E: An exception occurred in an EJB call. Error: PreparedStatementCallback; SQL [
SELECT i.EXECUTION_STATUS, COUNT(*) AS INSTANCE_COUNT
FROM LSW_BPD_INSTANCE i
WHERE LAST_MODIFIED_DATETIME<=? AND
(lower(i.INSTANCE_NAME) LIKE ? ESCAPE '\')
GROUP BY i.EXECUTION_STATUS
WITH UR
]; DB2 SQL Error: SQLCODE=-905, SQLSTATE=57014, SQLERRMC=ASUTIME;000000000007;000000500000;SYSTEM PARAMETER, DRIVER=4.21.29; nested exception is com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-905, SQLSTATE=57014, SQLERRMC=ASUTIME;000000000007;000000500000;SYSTEM PARAMETER, DRIVER=4.21.29
org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [
SELECT i.EXECUTION_STATUS, COUNT(*) AS INSTANCE_COUNT
FROM LSW_BPD_INSTANCE i
WHERE LAST_MODIFIED_DATETIME<=? AND
(lower(i.INSTANCE_NAME) LIKE ? ESCAPE '\')

GROUP BY i.EXECUTION_STATUS
WITH UR
]; DB2 SQL Error: SQLCODE=-905, SQLSTATE=57014, SQLERRMC=ASUTIME;000000000007;000000500000;SYSTEM PARAMETER, DRIVER=4.21.29; nested exception is com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-905, SQLSTATE=57014, SQLERRMC=ASUTIME;000000000007;000000500000;SYSTEM PARAMETER, DRIVER=4.21.29
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:105)
.
.
.
[3/28/19 15:57:10:485 EDT] 0000dedf RestHelper W Unexpected database exception
com.lombardisoftware.core.TeamWorksException: Unexpected database exception
at com.ibm.bpm.rest.util.RestHelper.getExceptionResponse(RestHelper.java:473)
at com.ibm.bpm.rest.util.RestHelper.getExceptionResponse(RestHelper.java:444)
at com.ibm.bpm.rest.impl.process.ProcessesResource.getProcessInstances(ProcessesResource.java:1579)
at sun.reflect.GeneratedMethodAccessor935.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)
at java.lang.reflect.Method.invoke(Method.java:508)

Workaround:
Up DB2 SQL CPU allotment from 7 seconds to 32 seconds. The above query now runs within 32 seconds but that is not acceptable.

Recommendation By DB2 DBA:

I would get them to fix the app so the lower() function wasn't necessary.
Barring that, add column function to the table and place that column in an index. Expression on index became available in db2 v10.
Create index xxxxx on LSW_BPD_INSTANCE (LAST_MODIFIED_DATETIME, lower(i.INSTANCE_NAME, execution_status)
So this would be my recommendation for ibm to add to the product.
SELECT i.EXECUTION_STATUS, COUNT(*) AS INSTANCE_COUNT
FROM LSW_BPD_INSTANCE i
WHERE LAST_MODIFIED_DATETIME<=? AND
(lower(i.INSTANCE_NAME) LIKE ? ESCAPE '\')
GROUP BY i.EXECUTION_STATUS

Idea priority High
RFE ID 131706
RFE URL
RFE Product IBM Business Process Manager