All of the catalog functions have CatalogName and SchemaName (and their
associated lengths) on their input argument list. Other input arguments may also include TableName, ProcedureName, or ColumnName (and their associated lengths).
These input arguments are used to either identify or constrain the amount of information to be returned.
All of the catalog functions have CatalogName and SchemaName (and their
associated lengths) on their input argument list. Other input arguments may also include TableName, ProcedureName, or ColumnName (and their associated lengths).
These input arguments are used to either identify or constrain the amount of information to be returned.
Input arguments to catalog functions may be treated as ordinary arguments or pattern value arguments. An ordinary argument is treated as a literal, and the case of letters is significant. These arguments limit the scope of the query by identifying the object of interest. An error results if the application passes a null pointer for the argument.
Some catalog functions accept pattern values on some of their input arguments.
For example, SQLColumnPrivileges() treats SchemaName and TableName as ordinary arguments and ColumnName as a pattern value. Refer to the "Function Arguments"
section of the specific catalog function to see if a particular input argument accepts pattern values.
Inputs treated as pattern values are used to constrain the size of the result set by including only matching rows as though the underlying query's WHERE clause contained a LIKE predicate. If the application passes a null pointer for a pattern value input, the argument is not used to restrict the result set (that is, there is no corresponding LIKE in the WHERE clause). If a catalog function has more than one pattern value input argument, they are treated as though the LIKE predicates of the WHERE clauses in the underlying query were joined by AND; a row appears in this result set only if it meets all the conditions of the LIKE predicates.
Each pattern value argument can contain:
v The underscore (_) character which stands for any single character.
v The percent (%) character which stands for any sequence of zero or more characters. Note that providing a pattern value containing a single % is equivalent to passing a null pointer for that argument.
v Characters with no special meaning which stand for themselves. The case of a letter is significant.
These argument values are used on conceptual LIKE predicate(s) in the WHERE clause. To treat the metadata characters (_, %) as themselves, an escape character must immediately precede the _ or %. The escape character itself can be specified as part of the pattern by including it twice in succession. An application can determine the escape character by calling SQLGetInfo() with
SQL_SEARCH_PATTERN_ESCAPE.
For example, the following calls would retrieve all the tables that start with 'ST':
/* tbinfo.c */
/* ... */
struct
{ SQLINTEGER ind ; SQLCHAR val[129] ;
} tbQualifier, tbSchema, tbName, tbType;
struct
{ SQLINTEGER ind ; SQLCHAR val[255] ; } tbRemarks;
SQLCHAR tbSchemaPattern[] = "
SQLCHAR tbNamePattern[] = "ST /* all the tables starting with ST */
/* ... */
sqlrc = SQLTables( hstmt, NULL, 0,
tbSchemaPattern, SQL_NTS, tbNamePattern, SQL_NTS, NULL, 0);
/* ... */
/* bind columns to variables */
sqlrc = SQLBindCol( hstmt, 1, SQL_C_CHAR, tbQualifier.val, 129,
&tbQualifier.ind ) ; STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 2, SQL_C_CHAR, tbSchema.val, 129,
&tbSchema.ind ) ; STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 3, SQL_C_CHAR, tbName.val, 129,
&tbName.ind ) ; STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 4, SQL_C_CHAR, tbType.val, 129,
&tbType.ind ) ; STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLBindCol( hstmt, 5, SQL_C_CHAR, tbRemarks.val, 255,
&tbRemarks.ind ) ; STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ... */
sqlrc = SQLFetch( hstmt );
/* ... */
while (sqlrc != SQL_NO_DATA_FOUND) { /* ... */
sqlrc = SQLFetch( hstmt );
/* ... */
}
Chapter 11. Programming hints and tips for CLI applications
These hints and tips can help you tune and improve the logic of your CLI applications.
v KEEPDYNAMIC support v Common connection attributes v Common statement attributes v Reusing statement handles v Binding and SQLGetData() v Limiting use of catalog functions
v Column names of function generated result sets v CLI-specific functions loaded from ODBC applications v Global dynamic statement caching
v Data insertion and retrieval optimization v Large object data optimization
v Case sensitivity of object identifiers v SQLDriverConnect() versus SQLConnect() v Turning off statement scanning
v Holding cursors across rollbacks
v Preparing compound SQL sub-statements v User-defined types casting
v Deferred prepare to reduce network flow
KEEPDYNAMIC behavior refers to the server's ability to keep a dynamic statement in a prepared state, even after a commit has been performed. This behavior
eliminates the need for the client to prepare the statement again, the next time the statement is executed. Some CLI/ODBC applications on the client might improve their performance by taking advantage of the KEEPDYNAMIC behavior on servers that are DB2 for z/OS and OS/390 Version 7 and later. Complete the listed steps to enable KEEPDYNAMIC behavior:
1. Enable the dynamic statement cache on the DB2 for z/OS and OS/390 server (see the DB2 for z/OS and OS/390 server documentation).
2. Bind the db2clipk.bnd file on your DB2 for Linux, UNIX, and Windows client with the KEEPDYNAMIC and COLLECTION options. The example shows how to bind db2clipk.bnd, creating a collection named KEEPDYNC:
v db2 connect to database_name user userid using password
v db2 bind db2clipk.bnd SQLERROR CONTINUE BLOCKING ALL KEEPDYNAMIC YES COLLECTION KEEPDYNC GRANT PUBLIC v db2 connect reset
3. Inform the client that the KEEPDYNAMIC bind option is enabled for your collection by performing either of the listed examples:
v Set the CLI/ODBC configuration keywords in the db2cli.ini file:
KeepDynamic = 1, CurrentPackageSet = collection name created in Step 2.
For example:
[dbname]
KeepDynamic=1
CurrentPackageSet=KEEPDYNC
v Set the SQL_ATTR_KEEPDYNAMIC and
SQL_ATTR_CURRENT_PACKAGE_SET connection attributes in the CLI/ODBC application. For example:
SQLSetConnectAttr(hDbc,
SQL_ATTR_KEEP_DYNAMIC, (SQLPOINTER) 1,
SQL_IS_UINTEGER );
SQLSetConnectAttr(hDbc,
SQL_ATTR_CURRENT_PACKAGE_SET, (SQLPOINTER) "KEEPDYNC", SQL_NTS);
See the DB2 for z/OS and OS/390 server documentation for further information about KEEPDYNAMIC behavior and configuration.