DB2 Version 10.1 for Linux, UNIX, and Windows

Error-checking utilities

The DB2® Client provides several utility files. The utility files contain functions that you can use for error checking and printing out error information. Utility files are provided for each language in the samples directory.

When used with an application program, the error-checking utility files provide helpful error information, and make debugging a DB2 program much easier. Most of the error-checking utilities use the DB2 APIs GET SQLSTATE MESSAGE (sqlogstt) and GETERROR MESSAGE (sqlaintp) to obtain pertinent SQLSTATE and SQLCA information related to problems encountered in program execution. The CLI utility file, utilcli.c, does not use these DB2 APIs; instead it uses equivalent CLI statements. With all the error-checking utilities, descriptive error messages are printed out to allow the developer to quickly understand the problem. Some DB2 programs, such as routines (stored procedures and user-defined functions), do not need to use the utilities.

Here are the error-checking utility files used by DB2 supported compilers for the different programming languages:

Table 1. Error-checking utility files by language
Language	Non-embedded SQL source file	Non-embedded SQL header file	Embedded SQL source file	Embedded SQL header file
C samples/c	`utilapi.c`	`utilapi.h`	`utilemb.sqc`	`utilemb.h`
C++ samples/cpp	`utilapi.C`	`utilapi.h`	`utilemb.sqC`	`utilemb.h`
IBM® COBOL samples/cobol	`checkerr.cbl`	`n/a`	`n/a`	`n/a`
Micro Focus COBOL samples/cobol_mf	`checkerr.cbl`	`n/a`	`n/a`	`n/a`

In order to use the utility functions, the utility file must first be compiled, and then its object file linked in during the creation of the target program's executable file. Both the makefile and build files in the samples directories do this for the programs that require the error-checking utilities.

The example demonstrates how the error-checking utilities are used in DB2 programs. The utilemb.h header file defines the EMB_SQL_CHECK macro for the functions SqlInfoPrint() and TransRollback():

/* macro for embedded SQL checking */
#define EMB_SQL_CHECK(MSG_STR)                         \
SqlInfoPrint(MSG_STR, &sqlca, __LINE__, __FILE__);     \
if (sqlca.sqlcode < 0)                                 \
{                                                      \
  TransRollback();                                     \
  return 1;                                            \
}

SqlInfoPrint() checks the SQLCODE and prints out any available information related to the specific error encountered. It also points to where the error occurred in the source code. TransRollback() allows the utility file to safely rollback a transaction where an error has occurred. It uses the embedded SQL statement EXEC SQL ROLLBACK. The example demonstrates how the C program dbuse calls the utility functions by using the macro, supplying the value "Delete with host variables -- Execute" for the MSG_STR parameter of the SqlInfoPrint() function:

   EXEC SQL DELETE FROM org
     WHERE deptnumb = :hostVar1 AND
           division = :hostVar2;
   EMB_SQL_CHECK("Delete with host variables -- Execute");

The EMB_SQL_CHECK macro ensures that if the DELETE statement fails, the transaction will be safely rolled back, and an appropriate error message printed out.

Developers are encouraged to use and expand upon these error-checking utilities when creating their own DB2 programs.