By Andy Garratt

When you write SQL statements directly in IBM® Business Process Manager, such as from server-side JavaScript in a service, ensure that you use typical SQL good practices for performance and resiliency.

Avoid using ‘SELECT * from ‘

When you use ‘SELECT * ‘ all the fields from the table or view are returned. If the table changes, the list of fields also changes, which might not be what you want, especially when you are mapping directly to a business object where the new names might not exist.

In addition, the asterisk (*) might return many columns in the results that are not needed but take up memory.

Instead, explicitly name the columns that you want returned as a result name, for example “SELECT <COLUMN1> AS <RESULT1NAME>, <COLUMN2> AS <RESULT2NAME>”. The use of AS means that your business object names do not need to match the database column names and can be more meaningful. For example, CST1NAM could map to CustomerName.

Use parameter markers

Do not construct an SQL statement by concatenating variable names, such as

“SELECT CUST_NAME FROM CUST_MAST WHERE CUST_NUM=”+tw.local.customer_number

If you concatenate variable names, the database cannot precompile and cache the select statement because it changes every time.

Instead, use parameter markers, such as in “SELECT CUST_NAME FROM CUST_MAST WHERE CUST_NUM=?”, and then set the parameter value.

Better yet, wrap database access in an SOA service that is invoked remotely or by using an Advanced Integration service.

Applicable editions: Express, Standard, and Advanced

Applicable releases: All

Source: IBM