Database Coding Notes

It is important to consider the following when developing database code for FTM:

• For some databases, the IBM® Integration Bus ODBC driver returns INTEGER fields as DECIMAL, which triggers type errors in the application flow. To avoid this, explicitly cast the affected fields to integer types in the ESQL code. For example:

  CAST(refObj.ID TO INTEGER)

• Using AS when specifying a table alias is not universally supported by all database vendors. While DB2® supports specifying a table alias with or without AS, it is better to code without AS if portability is a concern.
• There are some field names that are reserved words in DB2 and Oracle. To avoid confusing the database with them and creating errors, it is good practice to enclose table names and field names between double quotes. For example, use "OBJ"."ID" instead of OBJ.ID.
• Use READ ONLY queries to minimize locks and resources using:

  DB2

  Use the With UR clause.

  The FTM Core project detects the type of database automatically. FTM Core includes a function in SQLHelper.esql, called GetReadOnlyQuery(), that will format an SQL select statement to the correct read-only syntax for DB2.