The following should be added to the end of the "Multi-Threaded Mixed Applications" section:
The following is a new section for this chapter.
There are two main areas of support for DB2 CLI Unicode Applications:
The following sections provide more information for both of these areas. To be considered a Unicode application, the application must set the SQL_ATTR_ANSI_APP connection attribute to SQL_AA_FALSE, before a connection is made. This will ensure that the CLI will use Unicode as the preferred method of communication between itself and the database.
ODBC API functions have suffixes to indicate the format of their string arguments: those that accept unicode end in W; those that accept ANSI have no suffix.
SQLBrowseConnect SQLForeignKeys SQLPrimaryKeys SQLColAttribute SQLGetConnectAttr SQLProcedureColumns SQLColAttributes SQLGetConnectOption SQLProcedures SQLColumnPrivileges SQLGetCursorName SQLSetConnectAttr SQLColumns SQLGetDescField SQLSetConnectOption SQLConnect SQLGetDescRec SQLSetCursorName SQLDataSources SQLGetDiagField SQLSetDescField SQLDescribeCol SQLGetDiagRec SQLSetStmtAttr SQLDriverConnect SQLGetInfo SQLSpecialColumns SQLGetStmtAttr SQLStatistics SQLError SQLNativeSQL SQLTablePrivileges SQLExecDirect SQLPrepare SQLTables
Unicode functions whose arguments are always the length of strings interpret these arguments as count-of-characters. Functions that return length information for server data also describe the display size and precision in terms of characters. When the length (transfer size of the data) could refer to string or nonstring data, the length is interpreted as a count of bytes. For example, SQLGetInfoW will still take the length as count-of-bytes, but SQLExecDirectW will use count-of-characters. CLI will return data from result sets in either Unicode or ANSI, depending on the application's binding. If an application binds to SQL_C_CHAR, the driver will convert SQL_WCHAR data to SQL_CHAR. An ODBC driver manager, if used, maps SQL_C_WCHAR to SQL_C_CHAR for ANSI drivers but does no mapping for Unicode drivers.
Additional ODBC and CLI defined data types have been added to accommodate Unicode databases. These types supplement the set of C and SQL types that already exist. The new C type, SQL_C_WCHAR, indicates that the C buffer contains UCS-2 data in native endian format. The new SQL types, SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR, indicate that a particular column or parameter marker contains Unicode data. For DB2 Unicode databases, graphic columns will be described using the new types.
Tabelle 10. Supported Data Conversions
SQL Data Type |
S Q L _ C _ C H A R |
S Q L _ C _ W C H A R |
S Q L _ C _ L O N G |
S Q L _ C _ S H O R T |
S Q L _ C _ T I N Y I N T |
S Q L _ C _ F L O A T |
S Q L _ C _ D O U B L E |
S Q L _ C _ T Y P E _ D A T E |
S Q L _ C _ T Y P E _ T I M E |
S Q L _ C _ T Y P E _ T I M E S T A M P |
S Q L _ C _ B I N A R Y |
S Q L _ C _ B I T |
S Q L _ C _ D B C H A R |
S Q L _ C _ C L O B _ L O C A T O R |
S Q L _ C _ B L O B _ L O C A T O R |
S Q L _ C _ D B C L O B _ L O C A T O R |
S Q L _ C _ B I G I N T |
S Q L _ C _ N U M E R I C |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
BLOB |
X |
X |
|
|
|
|
|
|
|
|
D |
|
|
|
X | |||
CHAR |
D |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
|
|
|
X |
X | |
CLOB |
D |
X |
|
|
|
|
|
|
|
|
X |
|
|
X |
| |||
DATE |
X |
X |
|
|
|
|
|
D |
|
X |
|
|
|
|
| |||
DBCLOB |
|
X |
|
|
|
|
|
|
|
|
X |
|
D |
|
|
X | ||
DECIMAL |
D |
X |
X |
X |
X |
X |
X |
|
|
|
X |
X |
|
|
|
X |
X | |
DOUBLE |
X |
X |
X |
X |
X |
X |
D |
|
|
|
|
X |
|
|
|
X |
X | |
FLOAT |
X |
X |
X |
X |
X |
X |
D |
|
|
|
|
X |
|
|
|
X |
X | |
GRAPHIC (Non-Unicode) |
X |
X |
|
|
|
|
|
|
|
|
|
|
D |
|
| |||
GRAPHIC (Unicode) |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
D |
|
|
|
X |
|
INTEGER |
X |
X |
D |
X |
X |
X |
X |
|
|
|
|
X |
|
|
|
X |
X | |
LONG VARCHAR |
D |
X |
|
|
|
|
|
|
|
|
X |
|
|
|
| |||
LONG VARGRAPHIC (Non-Unicode) |
X |
X |
|
|
|
|
|
|
|
|
X |
|
D |
|
| |||
LONG VARGRAPHIC (Unicode) |
X |
X |
|
|
|
|
|
|
|
|
X |
|
D |
|
|
|
|
|
NUMERIC |
D |
X |
X |
X |
X |
X |
X |
|
|
|
|
X |
|
|
|
X | ||
REAL |
X |
X |
X |
X |
X |
D |
X |
|
|
|
|
X |
|
|
|
X | ||
SMALLINT |
X |
X |
X |
D |
X |
X |
X |
|
|
|
|
X |
|
|
|
X |
X | |
BIGINT |
X |
X |
X |
X |
X |
X |
X |
|
|
|
X |
X |
|
|
|
D |
X | |
TIME |
X |
X |
|
|
|
|
|
|
D |
X |
|
|
|
|
| |||
TIMESTAMP |
X |
X |
|
|
|
|
|
X |
X |
D |
|
|
|
|
| |||
VARCHAR |
D |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
|
|
|
X |
X | |
VARGRAPHIC (Non-Unicode) |
X |
X |
|
|
|
|
|
|
|
|
|
|
D |
|
| |||
VARGRAPHIC (Unicode) |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
D |
|
|
|
X |
|
Before Unicode applications were supported, applications that were written to work with single-byte character data could be made to work with double-byte graphic data by a series of cli ini file keywords, such as GRAPHIC=1,2 or 3, Patch2=7. These workarounds presented graphic data as character data, and also affected the reported length of the data.
These keywords are no longer required for Unicode applications, and should not be used due to the risk of potential side effects. If it is not known if a particular application is a Unicode application, we suggest you try without any of the keywords that affect the handling of graphic data.
In non-unicode databases, data in LONG VARGRAPHIC and LONG VARCHAR columns cannot be compared. Data in GRAPHIC/VARGRAPHIC and CHAR/VARCHAR columns can only be compared, or assigned to each other, using explicit cast functions since no implicit code page conversion is supported. This includes GRAPHIC/VARGRAPHIC and CHAR/VARCHAR literals where a GRAPHIC/VARGRAPHIC literal is differentiated from a CHAR/VARCHAR literal by a G prefix.
For Unicode databases, casting between GRAPHIC/VARGRAPHIC and CHAR/VARCHAR literals is not required. Also, a G prefix is not required in front of a GRAPHIC/VARGRAPHIC literal. Provided at least one of the arguments is a literal, implicit conversions occur. This allows literals with or without the G prefix to be used within statements that use either SQLPrepareW() or SQLExecDirect().Literals for LONG VARGRAPHICs still must have a G prefix.
For more information, see "Casting Between Data Types" in "Chapter 3. Language Elements" of the SQL Reference.
The following three keywords have been added to avoid any extra overhead when Unicode applications connect to a database.
With Unicode support enabled, and when called by a Unicode application, CLI will attempt to connect to the database using the best client code page possible to ensure there is no unnecessary data loss due to code page conversion. This may increase the connection time as code pages are exchanged, or may cause code page conversions on the client that did not occur before this support was added.
Setting this keyword to True (1) will cause all Unicode data to be converted to the application's local code page first, before the data is sent to the server. This can cause data loss for any data that cannot be represented in the local code page.
Non-Unicode applications always connect to the database using the application's local code page, or the DB2Codepage environment setting. By default, CLI will ensure that Unicode applications will connect to Unicode databases using UTF-8 and UCS-2 code pages. The default for connecting to non-unicode databases is to use the databases's code page is the database server is running DB2 for Windows, DB2 for Unix or DB2 for OS/2. This ensures that there is no unnecessary data loss due to code page conversion.
This keyword allows the user to specify the database's code page when connecting to a non-Unicode database in order to avoid any extra overhead on the connection.
Specifying a value of 1 causes SQLDriverConnect() to return the correct value in the output connection string, so the value can be used on future SQLDriverConnect() calls.
This keyword is equivalent to ConnectCodepage=1208, and is added only for convenience. Set this keyword to avoid extra connect overhead when connecting to DB2 for OS/390 Version 7 or higher. There is no need to set this keyword for DB2 for Windows, DB2 for Unix or DB2 for OS/2 databases, since there is no extra processing required.
The following corrects the default value for the DISABLEMULTITHREAD configuration keyword in the "Installation and Configuration" sub-section:
The following information should be added to the "Scrollable Cursors" section:
The UDB client for the Unix, Windows, and OS/2 platforms supports updatable server-side scrollable cursors when run against OS/390 Version 7 databases. To access an OS/390 scrollable cursor on a three-tier environment, the client and the gateway must be running DB2 UDB Version 7.1, FixPak 3 or later.
There are two application enablement interfaces that can access scrollable cursors: ODBC and JDBC. The JDBC interface can only access static scrollable cursors, while the ODBC interface can access static and keyset-driven server-side scrollable cursors.
The table below lists the default attributes for OS/390 Version 7 cursors
in ODBC.
Tabelle 11. Default attributes for OS/390 cursors in ODBC
Cursor Type | Cursor Sensitivity | Cursor Updatable | Cursor Concurrency | Cursor Scrollable |
---|---|---|---|---|
forward-onlya | unspecified | non-updatable | read-only concurrency | non-scrollable |
static | insensitive | non-updatable | read-only concurrency | scrollable |
keyset-driven | sensitive | updatable | values concurrency | scrollable |
|
All ODBC fetch orientations are supported via the SQLFetchScroll or SQLExtendedFetch interfaces.
A keyset-driven cursor is an updatable cursor. The CLI driver appends the FOR UPDATE clause to the query, except when the query is issued as a SELECT ... FOR READ ONLY query, or if the FOR UPDATE clause already exists. The keyset-driven cursor implemented in DB2 for OS/390 is a values concurrency cursor. A values concurrency cursor results in optimistic locking, where locks are not held until an update or delete is attempted. When an update or delete is attempted, the database server compares the previous values the application retrieved to the current values in the underlying table. If the values match, then the update or delete succeeds. If the values do not match, then the operation fails. If failure occurs, the application should query the values again and re-issue the update or delete if it is still applicable.
An application can update a keyset-driven cursor in two ways:
Since scrollable cursor support is new, some ODBC applications that were
working with previous releases of UDB for OS/390 or UDB for Unix, Windows, and
OS/2 may encounter behavioral or performance changes. This occurs
because before scrollable cursors were supported, applications that requested
a scrollable cursor would receive a forward-only cursor. To restore an
application's previous behavior before scrollable cursor support, set the
following configuration keywords in the db2cli.ini
file:
Configuration Keyword Setting | Description |
---|---|
PATCH2=6 | Returns a message that scrollable cursors (both keyset-driven and static) are not supported. CLI automatically downgrades any request for a scrollable cursor to a forward-only cursor. |
DisableKeysetCursor=1 | Disables both the server-side and client-side keyset-driven scrollable cursors. This can be used to force the CLI driver to give the application a static cursor when a keyset-driven cursor is requested. |
UseServerKeysetCursor=0 | Disables the server-side keyset-driven cursor for applications that are using the client-side keyset-driven cursor library to simulate a keyset-driven cursor. Only use this option when problems are encountered with the server-side keyset-driven cursor, since the client-side cursor incurs a large amount of overhead and will generally have poorer performance than a server-side cursor. |
The following note is missing from the book:
Any SQL statement that can be prepared dynamically, other than a query, can be executed as a statement inside a compound statement. Note: Inside Atomic Compound SQL, savepoint, release savepoint, and rollback to savepoint SQL statements are also disallowed. Conversely, Atomic Compound SQL is disallowed in savepoint.
The following privileges must be granted to users who want to build, debug, and run SQL stored procedures:
The following privileges must be granted to users who want to build, debug, and run Java stored procedures:
To create the DB2DBG.ROUTINE_DEBUG table, issue the following command:
db2 -tf sqllib/misc/db2debug.ddl
For more information about debugging Java stored procedures, see the Application Development Guide.
Following is an undocumented limitation on CLI stored procedures:
If you are making calls to multiple CLI stored procedures, the application must close the open cursors from one stored procedure before calling the next stored procedure. More specifically, the first set of open cursors must be closed before the next stored procedure tries to open a cursor.
The following supplements information in the book:
The CLI/ODBC driver will normally autobind the CLI packages the first time a CLI/ODBC application executes SQL against the database, provided the user has the appropriate privilege or authorization. Autobinding of the CLI packages cannot be performed from within a stored procedure, and therefore will not take place if the very first thing an application does is call a CLI stored procedure. Before running a CLI application that calls a CLI stored procedure against a new DB2 database, you must bind the CLI packages once with this command:
db2 bind <BNDPATH>/@db2cli.lst blocking all
db2bind "%DB2PATH%\bnd\@db2cli.lst" blocking
The recommended approach is to always bind these packages at the time the database is created to avoid autobind at runtime. Autobind can fail if the user does not have privilege, or if another application tries to autobind at the same time.