DB2 Version 9.7 for Linux, UNIX, and Windows

Best practices for calling built-in routines and views in applications

To ensure your successful use of the built-in routines and views, certain coding practices are recommended. These practices are especially important because at times the routines might change, as can happen from release to release as enhancements are made.

When you issue a query to retrieve information using a system-defined routine or view, do not use a statement of the form SELECT * .... For example, do not issue the following query:

SELECT * FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS t 
ORDER BY total_cpu_time DESC

Instead, name the result columns in the SELECT statement. This gives the application control over the number of result columns and the sequence in which they are returned. For example:

SELECT application_handle, 
       uow_id, 
       total_cpu_time, 
       app_rqsts_completed_total, 
       rqsts_completed_total 
FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS t 
ORDER BY total_cpu_time DESC

This prevents problems when the sequence and number of columns in the routines changes. It is possible the number of result columns that a routine returns might increase, and if, for example, you provide only five host variables when the routine returns six result columns, your application will break.

In addition, the type and size of output parameters or result columns of routines might change, for example a column might change from VARCHAR(8) to VARCHAR(128), or an INTEGER column might become a BIGINT column. If a variable you use is too small, the data you receive from the routine could be truncated.

To protect your application from such changes, for C applications, you can describe a prepared statement in order to determine which result columns are being returned and what are their types and sizes. For example, the following code snippet describes the query SELECT application_handle, uow_id,total_cpu_time FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS t ORDER BY total_cpu_time DESC:

strcpy(strStmt, "SELECT application_handle, uow_id,total_cpu_time 
   FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) 
   AS t ORDER BY total_cpu_time DESC");
EXEC SQL PREPARE stmt FROM :strStmt;
EXEC SQL DESCRIBE stmt into :*pSqlda;

See the RowDatamemoryAlloc function in samples/c/tbread.sqc for more details regarding how to use the information returned in the SQLDA.

For Java™ and .Net applications, if data type and size is an issue, you can use metadata to determine which result columns are being returned and what are their types and sizes. For example:

ResultSet rs = pstmt.executeQuery();
ResultSetMetaData rsms = rs.getMetaData();

See the execPreparedQueryWithUnknownOutputColumn() method in samples/java/jdbc/TbRead.java for details regarding how to use the metadata of the result set.