DB2 10.5 for Linux, UNIX, and Windows

Input arguments on catalog functions in CLI applications

All of the catalog functions have CatalogName and SchemaName components and their associated lengths on the input argument list. Other input arguments can also include TableName, ProcedureName, or ColumnName, and their associated lengths. You can use these input arguments 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:

The underscore (_) character which stands for any single character.
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.
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 );
        /* ... */
    }