Getting data off the iSeries part 2

Someone asked me to expand on the Data Access overview from .NET I gave previously.
If you want more information on this, go to the Programmer's Toolkit included with IBM's client access. It has excellent information with sample code.
Here's an overview from the Programmer's Toolkit :

.NET Framework Classes - The Managed Provider
If you are using the .NET framework to connect to the iseries use the IBM.Data.DB2.iSeries database provider for application development. This is in c:/program files/IBM/Client Access/ as a dll. This managed provider will provide better performance than using the System.Data.OleDb provider to bridge to the iSeries Access OLE DB provider, or using the Microsoft.Data.Odbc provider to bridge to the iSeries Access ODBC driver.

IBM.Data.DB2.iSeries database provider aka Managed Provider aka IBM DB2 UDB for iSeries .NET Provider. They all refer to the same integrated iSeries Access for processing DB2 using ADO.NET.
These are the interfaces supported
Provider Class Name Interface(s) supported
iDB2Connection DbConnection
iDB2Command DbCommand
iDB2DataReader DataReader; IDataRecord
iDB2DataAdapter DbDataAdapter (inherits from IDataAdapter)
iDB2Parameter DataParameter; IDbDataParameter
iDB2ParameterCollection IDataParameterCollection
iDB2Transaction DbTransaction
iDB2DbType iSeries-specific types
iDB2Error iSeries-specific errors
iDB2ErrorCollection Collection, IEnumerable
iDB2Exception Provider-specific exceptions
iDB2CommandBuilder iSeries-specific command builder
advantages

advantages
Easy to program
Allows easy integration of SQL commands and result data with other data sources
Application processes SQL commands directly into an ADO.NET DataSet object
Application places SQL result data into an ADO.NET DataSet object
Provides a minimal set of interfaces between the application and the iSeries database
Increases performance without sacrificing functionality

disadvantage
Record level access, program calls, and data queues are not supported.

ADO/OLE DB
The IBMDA400 and IBMDASQL providers allow you to send remote SQL statements to the iSeries server. There is support for calling most SQL statements on the iSeries server.
The OLE DB provider record-level access support provides the ability to do the following:

- open logical or physical files
- access records sequentially or by key
- read, insert, update, and delete records
- perform commitment control
- work with multiple file members
- work with multiple record formats

advantages
Easy to program to.
Provides SQL access and record-level access to database files.
Excellent record level access performance.

disadvantages
Limited application optimization potential due to standard interface.
Limited support for native DB2 UDB functions.



ActiveX automation objects
The iSeries Access for Windows ActiveX automation object library provides a set of automation objects to allow easy access to iSeries host systems, data queues, programs and commands and call RPG programs.
This is the CWBX.DLL library that you need to add to solution explorer.

- accessing iSeries data queues
- calling iSeries server APIs and user programs
- managing connections and validating security
- running CL commands on the iSeries server
- performing data type and code page conversions
- transferring database data to and from the iSeries server


advantages
Easy to program to.
Automatic conversion to and from many popular PC file formats.
Provides high-level object for easiest data transfers and low-level objects for customized data transfers.
Supports creating, modifying, saving, and executing data transfer request files.
Request files are interchangeable with the Data Transfer GUI and batch applications.

disadvantages
The database SQL support is limited to SELECTs and full file updates.
No support for record level access.
Slower performance in performing individual file/individual row updates than OLE DB record level access.
No future updates planned for this interface.


ODBCadvantagesApplications capable of accessing database files on servers other than iSeries (using non-iSeries Access for Windows ODBC driver) disadvantagesDifficult to program to, though easier than C API.Limited support for native DB2 UDB functions.
.
The above is summarized from IBM's Programmer's toolkit documentation.