Application development: Call Level Interface (CLI)
|
|
|Trusted_Connection CLI/ODBC configuration keyword
|
|
|- Keyword description:
|- Allow a connection to be made with the current authenticated user.
|
|- Syntax:
|- Trusted_Connection=Yes
|
|
Note:
|This keyword has no effect if
|set in the db2cli.ini file. It should instead
|be provided in the connection string to SQLDriverConnect().
|
|- Default setting:
|- DB2 CLI uses the user ID and password information provided in the connection
|string to SQLDriverConnect(), not the current authenticated user.
|
|- Usage notes:
|-
|
DB2 CLI applications that connect to a database typically connect using
|the function SQLDriverConnect(). One of the input arguments for this function
|is the DriverCompletion value, which determines when a window will
|be opened. The following are the values of DriverCompletion :
|
|- SQL_DRIVER_PROMPT: A dialog is always initiated.
|- SQL_DRIVER_COMPLETE: A dialog is only initiated if there is insufficient
|information in the connection string.
|- SQL_DRIVER_COMPLETE_REQUIRED: A dialog is only initiated if there is
|insufficient information in the connection string. Only mandatory information
|is requested. The user is prompted for required information only.
|- SQL_DRIVER_NOPROMPT: The user is not prompted for any information. A
|connection is attempted with the information contained in the connection string.
|If there is not enough information, SQL_ERROR is returned.
|
|Note:
|More details on DriverCompletion can be found
|in the documentation for SQLDriverConnect().
|Some applications,
|for example, those in a Kerberos environment, might require that a user be
|able to connect to a DB2 UDB server without providing a user ID or password.
|If the application uses the SQL_DRIVER_NO_PROMPT option on the SQLDriverConnect() call,
|the connection is attempted without the user authentication. This keyword
|is then not required.
|In the case where a third party application is
|involved and the prompt level used by the application is something other than
|SQL_DRIVER_NO_PROMPT, DB2 CLI opens a window to request the missing information.
|Setting Trusted_Connection to Yes, by providing it to the input connection
|string for SQLDriverConnect() ("Trusted_Connection=Yes"), causes DB2 CLI to
|ignore any user ID or password string (including blank strings) from the connection
|string and ignore the prompt level of the connection function. DB2 CLI uses
|the current authenticated user to attempt the connection to the database.
|If the connection attempt fails, the user is prompted for the user ID and
|password.
|This keyword is used only in the connection string for SQLDriverConnect();
|setting it in the db2cli.ini file has no effect.
|
|
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 connection 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 connection 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 and connection 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 and connection 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 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 servers.
All levels of describe information are supported on the following DB2 servers: DB2 Universal Database (UDB) for Linux , UNIX, and Windows Version 8 and later, DB2 UDB for z/OS Version 8 and later, and DB2 UDB for iSeries 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 ]