Customizing select, insert, update and delete statements

The JDBC connector has the ability to expand a SQL template before executing any of its SQL operations. There are five operations where the templates can be used. You can use the information provided here for understanding these operations.

Table 1. SQL Operations
Operation	Connector Parameter name	Description	Mode(s)
SELECT	SQL Select	Used in Iterator mode (no search criteria).	Iterator
INSERT	SQL Insert	Used when adding an entry to the data source.	Update, AddOnly, Delta
UPDATE	SQL Update	Used when modifying an existing entry in the data source.	Update, Delta
DELETE	SQL Delete	Used when deleting an existing entry in the data source.	Delete, Delta
LOOKUP	SQL Lookup	A SELECT statement with a WHERE clause. Used when searching the data source.	Lookup, Delete, Update

If the template for a given operation is not defined (for example, null or empty), the JDBC connector will use its own internal template.

When there is a template defined for an operation, the template must generate a complete and valid SQL statement. The template can reference the standard parameter substitution objects (for example, mc, config, work, Connector), as well as the JDBC schema for the table configured for the connector and a few other convenience objects.

Note: The template for the LOOKUP operation can contain a WHERE clause filtering the elements that will be returned by the query. But when the connector is in Lookup, Update or Delete mode the Link Criteria parameter is mandatory, as it is used to assemble a WHERE clause for the executed query. If Link Criteria is omitted an exception will be thrown:

java.lang.Exception: CTGDIS143E No criteria can be built from input (no link criteria specified).
  		at com.ibm.di.server.SearchCriteria.buildCriteria(Unknown Source)

Therefore if a configuration is created and it uses a WHERE clause in the LOOKUP template the you must provide a Link criteria although one will not be needed. The connector will simply ignore it and the template query will be used. In order to save you from adding unneeded "dummy" conditions in the Link criteria,the solution is to check the option Build criteria from custom script and leave the displayed script area empty.