Release-Informationen

40.6 Chapter 3. Using Advanced Features

40.6.1 Writing Multi-Threaded Applications

The following should be added to the end of the "Multi-Threaded Mixed Applications" section:

Anmerkung:: It is recommended that you do not use the default stack size, but instead increase the stack size to at least 256 000. DB2 requires a minimum stack size of 256 000 when calling a DB2 function. You must ensure therefore, that you allocate a total stack size that is large enough for both your application and the minimum requirements for a DB2 function call.

40.6.2 Writing a DB2 CLI Unicode Application

The following is a new section for this chapter.

There are two main areas of support for DB2 CLI Unicode Applications:

The addition of a set of functions that can accept Unicode string arguments in place of ANSI string arguments.

The addition of new C and SQL data types to describe data as ANSI or Unicode data. 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 CLI will connect as a Unicode client, and all Unicode data will be sent in either UTF-8 for CHAR data or UCS-2 for GRAPHIC data.

40.6.2.1 Unicode Functions

The following is a list of the ODBC API functions that support both Unicode (W) and ANSI (A) versions (the function name will have a W for Unicode):

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
SQLDrivers              SQLGetStmtAttr          SQLStatistics
SQLError                SQLNativeSQL            SQLTablePrivileges
SQLExecDirect           SQLPrepare              SQLTables

Unicode functions that always return, or take, string length arguments are passed as count-of-characters. For functions that return length information for server data, the display size and precision are described in number of characters. When the length (transfer size of the data) could refer to string or nonstring data, the length is described in octet lengths. For example, SQLGetInfoW will still take the length as count-of-bytes, but SQLExecDirectW will use count-of-characters. CLI will return 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. The driver manager maps SQL_C_WCHAR to SQL_C_CHAR for ANSI drivers but does no mapping for Unicode drivers.

40.6.2.2 New datatypes and Valid Conversions

There are two new CLI or ODBC defined data types, SQL_C_WCHAR and SQL_WCHAR. SQL_C_WCHAR indicates that the C buffer contains UCS-2 data. SQL_WCHAR indicates that a particular column or parameter marker contains Unicode data. For DB2 Unicode Servers, graphic columns will be described as SQL_WCHAR. Conversion will be allowed between SQL_C_WCHAR and SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR and SQL_CLOB, as well as between the graphic data 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

Anmerkung:

D: Conversion is supported. This is the default conversion for the SQL data type.
X: All IBM DBMSs support the conversion.
blank: No IBM DBMS supports the conversion.

Data is not converted to LOB Locator types, rather locators represent a data value, refer to Using Large Objects for more information.

SQL_C_NUMERIC is only available on 32-bit Windows operating systems.

40.6.2.3 Obsolete Keyword/Patch Value

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.

40.6.2.4 Literals in Unicode Databases

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.

40.6.2.5 New CLI Configuration Keywords

The following three keywords have been added to avoid any extra overhead when Unicode applications connect to a database.

DisableUnicode

Keyword Description:
Disables the underlying support for Unicode.
db2cli.ini Keyword Syntax:
DisableUnicode = 0 | 1
Default Setting:
0 (false)
DB2 CLI/ODBC Settings Tab:
This keyword cannot be set using the CLI/ODBC Settings notebook. The db2cli.ini file must be modified directly to make use of this keyword.
Usage Notes:

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 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.

ConnectCodepage

Keyword Description:
Specifies a specific code page to use when connecting to the data source to avoid extra connection overhead.
db2cli.ini Keyword Syntax:
ConnectCodepage = 0 | 1 <any valid db2 code page>
Default Setting:
0
DB2 CLI/ODBC Settings Tab:
This keyword cannot be set using the CLI/ODBC Settings notebook. The db2cli.ini file must be modified directly to make use of this keyword.
Usage Notes:

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, and will connect to non-Unicode databases using the database's code page. 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.
Specify a value of 1 to cause SQLDriverConnect() to return the correct value in the output connection string, so the value can be used on future SQLDriverConnect() calls.

UnicodeServer

Keyword Description:
Indicates that the data source is a unicode server. Equivalent to setting ConnectCodepage=1208.
db2cli.ini Keyword Syntax:
UnicodeServer = 0 | 1
Default Setting:
0
DB2 CLI/ODBC Settings Tab:
This keyword cannot be set using the CLI/ODBC Settings notebook. The db2cli.ini file must be modified directly to make use of this keyword.
Usage Notes:

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.

40.6.3 Microsoft Transaction Server (MTS) as Transaction Monitor

The following corrects the default value for the DISABLEMULTITHREAD configuration keyword in the "Installation and Configuration" sub-section:

DISABLEMULTITHREAD keyword (default 0)

40.6.4 Scrollable Cursors

The following information should be added to the "Scrollable Cursors" section:

40.6.4.1 Server-side Scrollable Cursor Support for OS/390

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.

Cursor Attributes

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-only^a	unspecified	non-updatable	read-only concurrency	non-scrollable
static	insensitive	non-updatable	read-only concurrency	scrollable
keyset-driven	sensitive	updatable	values concurrency	scrollable
a Forward-only is the default behavior for a scrollable cursor without the `FOR UPDATE` clause. Specifying `FOR UPDATE` on a forward-only cursor creates an updatable, lock concurrency, non-scrollable cursor.

Supported Fetch Orientations

All ODBC fetch orientations are supported via the SQLFetchScroll or SQLExtendedFetch interfaces.

Updating the Keyset-Driven Cursor

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:

Issue an UPDATE WHERE CURRENT OF "<cursor name>" or DELETE WHERE CURRENT OF "<cursor name>" using SQLPrepare() with SQLExecute() or SQLExecDirect().

Use SQLSetPos() or SQLBulkOperations() to update, delete, or add a row to the result set.
Anmerkung:
Rows added to a result set via SQLSetPos() or SQLBulkOperations() are inserted into the table on the server, but are not added to the server's result set. Therefore, these rows are not updatable nor are they sensitive to changes made by other transactions. The inserted rows will appear, however, to be part of the result set, since they are cached on the client. Any triggers that apply to the inserted rows will appear to the application as if they have not been applied. To make the inserted rows updatable, sensitive, and to see the result of applicable triggers, the application must issue the query again to regenerate the result set.

Troubleshooting for Applications Created Before Scrollable Cursor Support

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:

Tabelle 12. Configuration keyword values restoring application behavior before scrollable cursor support

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.

40.6.5 Using Compound SQL

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.

40.6.6 Using Stored Procedures

40.6.6.1 Writing a Stored Procedure in CLI

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.

40.6.6.2 CLI Stored Procedures and Autobinding

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:

UNIX

db2 bind <BNDPATH>/@db2cli.lst blocking all

Windows and OS/2

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.

[ Seitenanfang | Vorherige Seite | Nächste Seite | Inhaltsverzeichnis | Index ]