DB2 10.5 for Linux, UNIX, and Windows

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

To help ensure your successful use of the built-in routines and views, certain coding practices are recommended. These practices are especially important because routines might change from release to release and also within releases, such as through fix packs, as enhancements are made.

When you issue a query to retrieve information by using a built-in routine or view, select specific columns instead of selecting all columns with a wildcard. 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 technique gives the application control over the number of result columns and the sequence in which they are returned. In the following rewrite of the previous query, the columns are named:

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

Naming columns prevents problems if the sequence and number of columns in the routines change. The number of result columns that a routine returns might increase. 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 that you use is too small, the data that you receive from the routine might be truncated.

To protect your C application from such changes, you can describe a prepared statement to determine which result columns are returned and what their types and sizes are. The following example shows how to describe a prepared statement:

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;

For an example of how to use the information that is returned in the SQL description area (SQLDA), see the RowDatamemoryAlloc function in the samples/c/tbread.sqc file.

For Java™ and .NET applications, you need to know the data type and size for a program, you can use metadata to determine which result columns are returned and what their types and sizes are, as shown in the following example:

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

For an example of how to use the metadata of the result set, see the execPreparedQueryWithUnknownOutputColumn( ) method in the samples/java/jdbc/TbRead.java file.