OLEDB - OLE DB and ODBC Positioning

Positioning IBM i ODBC Driver and OLE DB Providers

The IBM i ODBC driver is one of the fastest and most efficient in the industry. It continues to be a viable option for accessing the operating system relational databases when the full power of ODBC and IBM Db2 for i SQL is required. ODBC is a C API. Therefore, it may be difficult (or impossible) to call ODBC directly from high level languages such as scripting languages or Visual Basic. Other vendors, including Microsoft, offer object models conforming to the Microsoft Component Object Model (COM) that sit on top of ODBC. These object models are typically not optimized for Db2 for i and may cause significant performance degradation.

Like ODBC, OLE DB is a Microsoft specification. OLEDB is an object interface that can be used to access any type of data store - not just relational data. One of the design goals of the OLE DB provider was to provide the OLEDB object model over other types of IBM i data that can not be efficiently accessed by ODBC. The OLE DB provider implemented four dialects: Record level access, data queue, program call, and SQL. SQL support is limited compared to the ODBC driver. The OLE DB provider also includes a Visual Basic toolkit. The toolkit contains Visual Basic add-in wizards that can be used to generate code and forms that use record level access, data queues, or program calls.

NOTE: When originally introduced there was one OLE DB provider, IBMDA400, that provided all functionality. Starting in V5R1 specific functionality was delivered in two new providers, IBMDASQL (SQL) and IBMDARLA (record-level access), as well as keeping the original fully functional IBMDA400 for legacy application support.

The OLE DB provider IBMDASQL support includes interfaces to do the following:

o Execute SQL statements
o Call SQL stored procedure (which are cataloged programs)
o Use multiple result sets
o Use SQL commitment control
o Use updatable cursors
o It does not include support for SELECT for UPDATE.

The OLE DB provider record-level access support provides the ability to do the following:

o Open logical or physical files
o Access records sequentially or by key
o Read, insert, update, and delete records
o Perform commitment control
o Work with multiple file members
o Work with multiple record formats

New applications should use the appropriate provider rather than the generic IBMDA400 provider. When reference is made to SQL it implies IBMDASQL and likewise reference to record-level access implies IBMDARLA.

In general, the OLE DB is the best choice for record level access and operating system remote program calls while ODBC is the best choice for SQL intensive applications. In cases where the development tool being used requires OLE DB, ODBC can be used indirectly by using MSDASQL, the Microsoft OLE DB provider for ODBC (see below).

For an overview of all the methods available to access various information on IBM i, refer to the Programmer's Toolkit that is included with the IBM i Access Client Solutions - Windows Application Package.

Use the following guideline to decide when to use OLE DB:

o	For record-level table access (including indexed file access, multi-member or multi-format file support, bookmark support, and cursor positioning by key or partial key values), OLE DB record level access is the best choice. ODBC can support most of this function (although it may require the overhead of the client cursor engine) but at reduced performance levels.
o	To run read-only SQL queries that do not run under commitment control or to import all data in a table, use OLE DB SQL dialect or ODBC.
o	To access advanced relational database functions using SQL (updatable cursors, commitment control, extended dynamic support, and so on), use ODBC .
o	For calling operating system programs or system APIs that: -- May use structures as parameters -- Do not require commitment control (at the job level) -- Do not return result sets use OLEDB program call dialect. ODBC and OLEDB stored procedures can be used but at reduced performance levels.
o	For calling operating system programs or SQL procedures that: -- Use SQL data types as parameters -- Do not require commitment control -- Return single result sets Use ODBC or OLEDB stored procedure calls.
o	For calling operating system programs or SQL procedures that: -- Use SQL data types as parameters -- Require commitment control -- Return multiple result sets Use ODBC stored procedure calls.
o	To expose a data queue as a record set, use OLE DB data queue dialect.

o	Applications that require updatable cursor support.
o	Applications that use SQL for data access and require commitment control.
o	Applications that require advanced catalog information support, extended dynamic support, and other SQL related performance options offered only with ODBC.