ESQL Database Access

Note: Avoid repeatedly computing SQL statements that can be computed once and cached.

In the sample application and core code, there are many examples of the following coding style:

CREATE DATABASE MODULE A_MyAction_Database
-- declare constant string at module scope (or wider scope)
DECLARE SQL_STMT SHARED CONSTANT CHAR
PreProcessSQL_WithUR('SELECT OBJ1_ID FROM $DBSchema.OBJ_OBJ_REL
WHERE OBJ1_ID=? AND OBJ2_ID=? AND TYPE=?');

CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
    SET rEnv.ID[] = PASSTHRU(SQL_STMT , nTxnId, nBatchId, 'ACK_TO');
END;
END MODULE;

which is more efficient than:

CREATE DATABASE MODULE A_MyAction_Database
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
    SET rEnv.ID[] = PASSTHRU(PreProcessSQL_WithUR('SELECT OBJ1_ID FROM $DBSchema.OBJ_OBJ_REL
WHERE OBJ1_ID=? AND OBJ2_ID=? AND TYPE=?'), nTxnId, nBatchId, 'ACK_TO');
END;
END MODULE;

IBM® App Connect Enterprise is not optimized for string manipulation. This is a simple optimization technique that avoids repeated string computation. It may only yield small gains in terms of CPU usage; but, if you adopt this technique during development, the gain is zero cost and the sum of lots of small efficiencies that can, over the duration of a long complex business process, make a difference.

Use of PASSTHRU and parameter markers (?s) also allows optimization at the database. The database can compile the SQL statement once and cache it, making subsequent calls faster. Use PASSTHRU and parameter markers whenever possible.