Application development: Call Level Interface (CLI)
|
|
|Diagnostic table update for SQLDescribeParam function (CLI)
|The SQLDescribeParam() function returns the description of a
|parameter marker associated with a prepared SQL statement.
|The diagnostics table has been updated with SQLSTATE HYC00.
|
|Diagnostics
|
|
|
|Table 24. SQLDescribeParam SQLSTATEs
|
|
|SQLSTATE |
|Description |
|Explanation |
|
|
|
|
|HYC00 |
|Driver not capable |
|The schema function stored procedures are not accessible on the server.
|Install the schema function stored procedures on the server and ensure they
|are accessible. |
|
|
|
|
|
|Asynchronous execution of Call Level Interface
|The DB2 Call Level Interface (DB2 CLI) can run a subset of functions asynchronously.
|The DB2 CLI driver returns control to the application after calling the function
|but before that function has finished executing. The function returns SQL_STILL_EXECUTING
|each time it is called until it is finished running, at which point it returns
|a different value (for example, SQL_SUCCESS).
|Asynchronous execution is beneficial only on single-threaded operating
|systems. Applications that run on multithreaded operating systems should
|execute functions on separate threads. Asynchronous execution is possible
|for those functions that normally send a request to the server and then wait
|for a response. Rather than waiting, a function executing asynchronously
|returns control to the application. The application can then perform other
|tasks, or return control to the operating system, and use an interrupt to
|repeatedly poll the function until a return code other than SQL_STILL_EXECUTING
|is returned.
|Support for asynchronous execution of CLI is included in DB2 Universal
|Database (UDB), starting with Version 8.2 FixPak 1 (equivalent to Version
|8.1 FixPak 8). For documentation of this feature, refer to the DB2 UDB Version
|7 Information Center at http://publib.boulder.ibm.com/infocenter/db2v7luw/index.jsp. All of the information in the Version 7 documentation applies to Version
|8.2 FixPak 1 (equivalent to Version 8.1 FixPak 8) and later. The DB2 Version
|8 Information Center does not contain any documentation of this feature.
|
|
|SQL_ATTR_PING_DB connection attribute
|
|SQL_ATTR_PING_DB (DB2 CLI v8.2)
|SQL_ATTR_PING_DB is a 32-bit integer that is used with the SQLGetConnectAttr() function to get the network response time of the existing connection
|between the DB2 UDB client and the DB2 UDB server. Response time is reported
|in microseconds.
|If a connection has previously been established and has been dropped by
|the database, a value of 0 is reported. If the connection has been closed
|by the application, then an SQLSTATE of 08003 is reported. This connection
|attribute can be returned by SQLGetConnectAttr(), but it cannot
|be set by SQLSetConnectAttr(). Any attempt to set this attribute
|will result in an SQLSTATE of HYC00 (Driver not capable).
|
|
|SQLBindParameter function (CLI)
|In the documentation for the SQLBindParameter function, the description
|in the Input parameter section is incorrect. The correct description is as
|follows:
|
|- Input parameter
|- A 0 ColumnSize means that DB2 CLI will
|use the maximum length for the SQL type provided as the size of the column
|or stored procedure parameter. DB2 CLI will perform any necessary conversions
|using this size.
|
|
|
|
|SQLMoreResults function (CLI)
|In the documentation for the SQLMoreResults function, the SQL_ATTR_ROW_ARRAY_SIZE
|statement attribute is referred to incorrectly. The correct statement attribute
|is SQL_ATTR_PARAMSET_SIZE. The Usage section should read as follows:
|This function is used to return multiple results set in a sequential manner
|upon the execution of:
|
|- A parameterized query with an array of input parameter values specified
|with the SQL_ATTR_PARAMSET_SIZE statement attribute and SQLBindParameter()
Additional environment attributes
In addition to being CLI connection attributes, the following attributes
are also supported as CLI environment attributes:
- SQL_ATTR_INFO_ACCTSTR
- SQL_ATTR_INFO_APPLNAME
- SQL_ATTR_INFO_USERID
- SQL_ATTR_INFO_WRKSTNNAME
For information on these attributes, refer to the CLI connection attributes
documentation in the DB2 Information Center or in the CLI Guide and Reference Volume 2 .
Dynamic scrollable cursors requirement
To perform updates and deletions on rows in a dynamic scrollable cursor's
result set, the UPDATE or DELETE statement must include all the columns of
at least one unique key in the base table. This can be the primary key or
any other unique key.
|
|
|RetCatalogAsCurrServer CLI/ODBC configuration keyword
|
|- Keyword description:
|- Catalog functions return the CURRENT SERVER value instead of the null
|value for the catalog columns.
|
|- db2cli.ini keyword syntax:
|- RetCatalogAsCurrServer= 0 | 1
|
|- Default setting:
|- If the target DBMS returns null for the catalog columns, the CURRENT
|SERVER value will not be substituted.
|
|- Usage notes:
|- If the catalog functions for the target DBMS return a null value for
|the catalog columns, setting RetCatalogAsCurrServer to 1 causes the DBMS to
|return the CURRENT SERVER value instead.
|
|- 0 = Catalog functions return the null value for the catalog columns (default).
|- 1 = Catalog functions return the CURRENT SERVER value, instead of the
|null value, for the catalog columns.
|For example, assume the catalog function SQLTables() returns
|a result set where the values in the TABLE_CAT column are null values. Setting
|RetCatalogAsCurrServer to 1 causes the DBMS to return the CURRENT SERVER value
|in the TABLE_CAT column.
|
|
|Note:
|This keyword is supported by DB2
|UDB for Linux, UNIX(R) and Windows(R) Version 8.2 FixPak 3 (equivalent
|to Version 8.1 FixPak 10) and later.
|
|
ReceiveTimeout CLI/ODBC configuration keyword
- Keyword description:
- Specify the time in seconds to wait for a reply from the server on an
established connection before terminating the attempt and generating a communication
timeout error.
- db2cli.ini keyword syntax:
- ReceiveTimeout = 0 | 1 | 2 |
... | 32767
- Default setting:
- The client waits indefinitely for a reply from the server on an established
connection.
- Equivalent statement attribute:
- SQL_ATTR_RECEIVE_TIMEOUT
- Usage notes:
- The default value of 0 indicates that the client waits indefinitely
for a reply. The receive timeout has no effect during connection establishment;
it is only supported for TCP/IP and is ignored for any other protocol.
SQL_ATTR_RECEIVE_TIMEOUT statement attribute
- SQL_ATTR_RECEIVE_TIMEOUT (DB2 CLI v8)
- A 32-bit integer value that is the number of seconds a client
waits for a reply from a server on an established connection before terminating
the attempt and generating a communication timeout error. The default value
of 0 indicates the client waits indefinitely for a reply. The receive timeout
has no effect during connection establishment; it is only supported for TCP/IP,
and is ignored for any other protocol. Supported values are integers from
0 to 32767.
Reopt CLI/ODBC configuration keyword
- Keyword description:
- Enable query optimization or reoptimization of SQL statements that have
special registers or parameter markers.
- db2cli.ini keyword syntax:
- Reopt = 2 | 3 | 4
- Default setting:
- No query optimization occurs at query execution time. The default estimates
chosen by the compiler are used for special registers or parameter markers.
- Equivalent statement attribute:
- SQL_ATTR_REOPT
- Usage notes:
- Optimization occurs by using the values available at query execution
time for the special registers or parameter markers instead of the default
estimates that are chosen by the compiler. The valid values of the keyword
are:
- 2 = SQL_REOPT_NONE. This is the default. No query optimization occurs
at query execution time. The default estimates chosen by the compiler are
used for the special registers or parameter markers. The default "NULLID
" package set is used to execute dynamic SQL statements.
- 3 = SQL_REOPT_ONCE. Query optimization occurs once at query execution
time, when the query is executed for the first time. The "NULLIDR1"
package set, which is bound with the REOPT ONCE bind option, is used.
- 4 = SQL_REOPT_ALWAYS. Query optimization or reoptimization occurs at
query execution time every time the query is executed. The "NULLIDRA"
package set, which is bound with the REOPT ALWAYS bind option, is used.
The "NULLIDR1" and "NULLIDRA" are reserved package
set names, and when used, REOPT ONCE and REOPT ALWAYS are implied respectively.
These package sets have to be explicitly created with the following commands:
db2 bind db2clipk.bnd collection NULLIDR1
db2 bind db2clipk.bnd collection NULLIDRA
If both the Reopt and CurrentPackageSet
keywords are specified, CurrentPackageSet takes precedence.
SQL_ATTR_REOPT statement attribute
- SQL_ATTR_REOPT (DB2 CLI v8)
- A 32-bit integer value that enables query optimization for SQL
statements that contain special registers or parameter markers. Optimization
occurs by using the values available at query execution time for special registers
or parameter markers, instead of the default estimates that are chosen by
the compiler. The valid values of the attribute are:
- 2 = SQL_REOPT_NONE. This is the default. No query optimization occurs
at query execution time. The default estimates chosen by the compiler are
used for the special registers or parameter markers. The default "NULLID
" package set is used to execute dynamic SQL statements.
- 3 = SQL_REOPT_ONCE. Query optimization occurs once at query execution
time, when the query is executed for the first time. The "NULLIDR1"
package set, which is bound with the REOPT ONCE bind option, is used.
- 4 = SQL_REOPT_ALWAYS. Query optimization or reoptimization occurs at
query execution time every time the query is executed. The "NULLIDRA"
package set, which is bound with the REOPT ALWAYS bind option, is used.
The "NULLIDR1" and "NULLIDRA" are reserved package
set names, and when used, REOPT ONCE and REOPT ALWAYS are implied respectively.
These package sets have to be explicitly created with these commands:
db2 bind db2clipk.bnd collection NULLIDR1
db2 bind db2clipk.bnd collection NULLIDRA
SQL_ATTR_REOPT and SQL_ATTR_CURRENT_PACKAGE_SET
are mutually exclusive, therefore, if one is set, the other is not allowed.
CurrentPackageSet CLI/ODBC configuration keyword
- Keyword description:
- Issues the SET CURRENT PACKAGESET statement after every connection.
- db2cli.ini keyword syntax:
- CurrentPackageSet = schema name
- Default setting:
- The clause is not appended.
- Equivalent connection attribute:
- SQL_ATTR_CURRENT_PACKAGE_SET
- Usage notes:
-
This option issues the SET CURRENT PACKAGESET SQL statement with
the CurrentPackageSet value after every connection to a database. By default
this clause is not appended.
The SET CURRENT PACKAGESET SQL statement
sets the schema name (collection identifier) that is used to select the package
to use for subsequent SQL statements.
CLI/ODBC applications issue dynamic
SQL statements. Using this option you can control the privileges used to run
these statements:
- Choose a schema to use when running SQL statements from CLI/ODBC applications.
- Ensure the objects in the schema have the desired privileges and then
rebind accordingly.
- Set the CurrentPackageSet option to this schema.
The SQL statements from the CLI/ODBC applications will now run under
the specified schema and use the privileges defined there.
The following
package set names are reserved: "NULLID ", "NULLIDR1", "NULLIDRA".
If both the Reopt and CurrentPackageSet keywords are specified, CurrentPackageSet
takes precedence.
SQL_ATTR_CURRENT_PACKAGE_SET connection attribute
- SQL_ATTR_CURRENT_PACKAGE_SET (DB2 CLI v5)
- A null-terminated character string that indicates the schema name (collection
identifier) that is used to select the package for subsequent SQL statements.
Setting this attribute causes the SET CURRENT PACKAGESET SQL statement to
be issued. If this attribute is set before a connection, the SET CURRENT PACKAGESET
SQL statement will be issued at connection time.
CLI/ODBC applications
issue dynamic SQL statements. Using this connection attribute, you can control
the privileges used to run these statements:
- Choose a schema to use when running SQL statements from CLI/ODBC applications.
- Ensure the objects in the schema have the desired privileges and then
rebind accordingly. This typically means binding the CLI packages (sqllib/bnd/db2cli.lst)
using the COLLECTION <collid> option. Refer to the BIND command for
further details.
- Set the CURRENTPACKAGESET option to this schema.
The SQL statements from the CLI/ODBC applications will now run under
the specified schema and use the privileges defined there.
Setting
the CLI/ODBC configuration keyword CURRENTPACKAGESET is an alternative method
of specifying the schema name.
The following package set names are
reserved: "NULLID ", "NULLIDR1", "NULLIDRA".
SQL_ATTR_REOPT and SQL_ATTR_CURRENT_PACKAGE_SET are mutually exclusive, therefore,
if one is set, the other is not allowed.
MapBigintCDefault CLI/ODBC configuration keyword
- Keyword description:
- Specify the default C type of BIGINT columns and parameter markers.
- db2cli.ini keyword syntax:
- MapBigintCDefault = 0 | 1 | 2
- Default setting:
- The default C type representation for BIGINT data is SQL_C_BIGINT.
- Usage notes:
-
MapBigintCDefault controls the C type that is used when SQL_C_DEFAULT is
specified for BIGINT columns and parameter markers. This keyword should be
used primarily with Microsoft(R) applications, such as Microsoft Access,
which cannot handle 8-byte integers. Set MapBigintCDefault as follows:
- 0 - for the default SQL_C_BIGINT C type representation
- 1 - for an SQL_C_CHAR C type representation
- 2 - for an SQL_C_WCHAR C type representation
This keyword affects the behavior of CLI functions where SQL_C_DEFAULT
might be specified as a C type, such as SQLBindParameter(), SQLBindCol(),
and SQLGetData().
DescribeOutputLevel CLI/ODBC configuration keyword
- Keyword description:
- Set the level of output column describe information that is requested
by the CLI driver during prepare or describe requests.
- db2cli.ini keyword syntax:
- DescribeOutputLevel = 0 | 1 | 2 | 3
- Default setting:
- Request the describe information listed in level 2 of Table 25.
- Usage notes:
-
This keyword controls the amount of information the CLI driver requests
on a prepare or describe request. By default, when the server receives a
describe request, it returns the information contained in level 2 of Table 25 for the result set columns. An application, however, might
not need all of this information or might need additional information.
Setting the DescribeOutputLevel keyword to a level that suits the needs
of the client application might improve performance because the describe data
transferred between the client and server is limited to the minimum amount
that the application requires. If the DescribeOutputLevel setting is set
too low, it might impact the functionality of the application (depending on
the application's requirements). The CLI functions to retrieve the describe
information might not fail in this case, but the information returned might
be incomplete.
Supported settings for DescribeOutputLevel are:
- 0 - no describe information is returned to the client application
- 1 - describe information categorized in level 1 (see Table 25)
is returned to the client application
- 2 - (default) describe information categorized in level 2 (see Table 25)
is returned to the client application
- 3 - describe information categorized in level 3 (see Table 25)
is returned to the client application
The following table lists the fields that form the describe information
that the server returns when it receives a prepare or describe request. These
fields are grouped into levels, and the DescribeOutputLevel CLI/ODBC configuration
keyword controls which levels of describe information the CLI driver requests.
Note:
Not all levels of describe information are supported by all DB2(R) servers.
All levels of describe information are supported on the following DB2 servers: DB2 Universal Database(TM) (UDB) for Linux(TM) , UNIX(R), and Windows(R) Version 8 and later, DB2 UDB for z/OS(R) Version 8 and later, and DB2 UDB for iSeries(TM) Version 5 Release 3 and later. All
other DB2 servers support only the 2 or 0 setting for DescribeOutputLevel.
Table 25. Levels of describe information
Level 1 |
Level 2 |
Level 3 |
SQL_DESC_COUNT
SQL_COLUMN_COUNT
SQL_DESC_TYPE
SQL_DESC_CONCISE_TYPE
SQL_COLUMN_LENGTH
SQL_DESC_OCTET_LENGTH
SQL_DESC_LENGTH
SQL_DESC_PRECISION
SQL_COLUMN_PRECISION
SQL_DESC_SCALE
SQL_COLUMN_SCALE
SQL_DESC_DISPLAY_SIZE
SQL_DESC_NULLABLE
SQL_COLUMN_NULLABLE
SQL_DESC_UNSIGNED
SQL_DESC_SEARCHABLE
SQL_DESC_LITERAL_SUFFIX
SQL_DESC_LITERAL_PREFIX
SQL_DESC_CASE_SENSITIVE
SQL_DESC_FIXED_PREC_SCALE |
all fields of level 1 and:
SQL_DESC_NAME
SQL_DESC_LABEL
SQL_COLUMN_NAME
SQL_DESC_UNNAMED
SQL_DESC_TYPE_NAME
SQL_DESC_DISTINCT_TYPE
SQL_DESC_REFERENCE_TYPE
SQL_DESC_STRUCTURED_TYPE
SQL_DESC_USER_TYPE
SQL_DESC_LOCAL_TYPE_NAME
SQL_DESC_USER_DEFINED_
TYPE_CODE |
all fields of levels 1
and 2 and:
SQL_DESC_BASE_COLUMN_NAME
SQL_DESC_UPDATABLE
SQL_DESC_AUTO_UNIQUE_VALUE
SQL_DESC_SCHEMA_NAME
SQL_DESC_CATALOG_NAME
SQL_DESC_TABLE_NAME
SQL_DESC_BASE_TABLE_NAME |
[ Top of Page |Previous Page | Next Page | Contents ]