Microsoft's .NET Framework is gaining acceptance among those iSeries solution developers who traditionally rely on Microsoft development tools. This trend has been recently exemplified by formation of the Midrange Alliance, which is aimed at integrating iSeries and AS/400 back-end servers with .NET. It seems that the combination of the .NET Framework and a powerful database server, such as DB2 UDB for iSeries, can be very appealing to the development community.

Typically, .NET applications use ADO.NET classes to access and manipulate data stored in databases. In this article, we'll use DB2 Development Add-in for Visual Studio .NET to visually manipulate DB2 for iSeries objects such as tables and stored procedures. We'll also take advantage of the Add-in wizards to significantly speed up the ADO.NET development process.

The .NET applications rely on services of .NET providers for communication with back-end database servers. A .NET data provider is used for connecting to a database, executing commands, and retrieving results. Currently, four providers can be used to access DB2 UDB for iSeries from .NET applications:

• V5R3 iSeries Access .NET provider implemented by IBM Rochester
• DB2 for Linux, Unix, and Windows (LUW) V8.2 (formerly "Stinger") .NET provider implemented by IBM software group. (This provider is required by the DB2 Development Add-in discussed in this article.)
• ODBC DB .NET Data Provider, the Microsoft-supplied ODBC bridge provider using the iSeries Access for Windows ODBC driver for underlying database connectivity
• OLE DB .NET Data Provider, the Microsoft-supplied OLE DB bridge provider using the iSeries Access OLE DB driver for underlying database connectivity

The native IBM DB2 UDB for iSeries .NET provider should be your choice, if performance of the data access is the most important consideration in your environment. The native iSeries .NET provider uses a highly optimized communication protocol to communicate with the back-end database server jobs. (See ".NET Integration with DB2 UDB for iSeries.") On the other hand, the recently announced IBM DB2 UDB for Linux, Unix, Windows (LUW) V8.2 (formerly Stinger) provides a high level of integration with Visual Studio .NET, improved performance for .NET applications, and significant enhancements for stored procedure and user-defined function developers. This new version also introduced a set of wizards to simplify the development process of applications that work with DB2 UDB for iSeries servers. The iSeries support provided by the DB2 Add-in relies on services provided by DB2 Connect.

DB2 Connect in a Nutshell

DB2 Connect provides functionality for accessing data stored in DB2 UDB for iSeries as well as DB2 for z/OS and OS/390. Many applications supporting the DB2 family of products leverage DB2 Connect as a key middleware component. DB2 Connect is offered as separate Windows licensed products:

• Enterprise Edition
• Personal Edition
• Unlimited Edition
• Application Server Edition

The DB2 Connect Enterprise Edition functionality is also contained in DB2 Enterprise Server Edition for Windows.

DB2 Connect implements the Distributed Relational Database Architecture (DRDA) to access data stored in DB2 UDB for iSeries and other DRDA-compliant database servers. A .NET client that uses the DB2 .NET provider connects to a DRDA server job on iSeries. The iSeries DRDA server jobs are called QRWTSRVR, and they run in the QUSRWRK subsystem. This is shown in Figure 1.

Figure 1: This illustration shows the DB2 Connect DRDA connection to the iSeries database.

You can use the DB2 Configuration Assistant (db2ca) on the Windows client to configure the required DRDA connection to the iSeries. The process is well-explained in chapter 5.11, "DB2 Connect access to an iSeries server via TCP/IP," in the ITSO Redbook Advanced Functions and Administration on DB2 Universal Database for iSeries.

DB2 Development Add-in for .NET

Once the DB2 Connect connection has been configured, you can take advantage of the DB2 Development Add-in. The Add-in provides a rich collection of functions that support DB2 servers directly in Visual Studio's IDE. Here's the list of the most important features:

• DB2 .NET Managed Provider
• Solution Explorer, which allows you to create and manage DB2 projects
• IBM Explorer, which allows you to view DB2 server catalog information and supports client-side ADO.NET code generation
• SQL Editor, which facilitates editing of DB2 scripts, including syntax colorization and statement auto-completion
• DB2 Tools Toolbar

In addition, the DB2 Add-in provides a set of wizards that can be used to visually configure DB2 commands, adapters, etc. For example, the DB2 Data Adapter Configuration wizard allows you to define both SQL statements and stored procedure calls. It also allows you to define the structure of the result set and map SQL command parameters to columns in the result set.

Software Prerequisites

You must have one of the DB2 Connect products or DB2 Enterprise Server Edition for Windows V8.2. At the minimum, you should have DB2 LUW FixPak 7a installed on the Windows machine.

For maximum database server stability, you should install the latest database group PTF (SF99502 for V5R2 or SF99503 for V5R3) on the iSeries server. The Informational APAR ii13348 is keeping track of any fixes relevant to DRDA above and beyond the current database group PTF.

Sample Application Walkthrough

To illustrate how the DB2 Add-in simplifies the developer's work, let's walk through the process of creating a simple .NET application called StingerAccess2iSeries.
The application reads and modifies data stored in DB2 for iSeries using the DB2 LUW .NET provider. We'll use a DB2DataAdapter class to manipulate rows, but rather than coding direct SQL statements (SELECT, INSERT, DELETE, and UPDATE), we'll provide appropriate stored procedures. Stored procedures offer many benefits, such as precompiled execution, reduced communications traffic, efficient code reuse, and enhanced security through adopted authority.

Once DB2 Connect has been successfully installed and registered, a new icon representing DB2 projects will appear on the Visual Studio .NET welcome window. This indicates that DB2 Add-in functionality is available through Visual Studio's IDE.

Let's start by creating a new DB2 for iSeries connection that will allow us to work with DB2 for iSeries objects. It is assumed that the DRDA connection to the iSeries server called myi5 has been previously configured and tested. In the Visual Studio IDE, select
View > IBM Explorer to open the IBM Explorer panel. You may want to anchor the Explorer panel in the IDE so that the DB2 objects are easily accessible. Right-click the Data Connections icon to display the Add DB2 Data Connection dialog. The example configuration is shown in Figure 2.

Figure 2: Configure the iSeries database connection.

Note that we've specified localhost as the server to connect to rather than myi5. This is because you connect to the DB2 instance on the local Windows system that reroutes the database requests to the iSeries using the DB2 Connect services (see Figure 1). I also recommend that you use the schema filters on the Filter tab to scope the connection to the schemas (libraries) you intend to access. The iSeries server usually contains a large number of schemas, so accessing a system with no filtering would result in transmitting a potentially large amount of unwanted meta data information.

Once the connection to DB2 has been established, we can use the Explorer to view, create, and modify database objects. For instance, by right-clicking a table icon, we have access to the context menu that allows us to retrieve rows and display table details, such as column attributes, triggers associated with the table, and indexes built over the table. It is also possible to delete (drop) the table altogether. Figure 3 shows the IBM Explorer panel on the left and the column properties for a sample table ACT in the main Visual Studio panel on the right.

Figure 3: View table details in IBM Explorer.

The sample application manipulates data in a table called COFFEES. We create this table using the DB2 Create Table Wizard. The wizard can be activated by right-clicking the Tables icon in the IBM Explorer and selecting New > Table. The Provide Information About the Table dialog allows us to specify the table schema name and table name. Since DB2 for iSeries, in contrast to DB2 LUW, provides built-in storage management, we can leave the Table space field empty. On the next dialog, Create Columns for the Table, we define the table's columns and their attributes. This is shown in Figure 4.

Figure 4: Create columns for the table.

NULLs are disallowed for the first column (ID). We'll also use the Value Options function to define the Identity attribute for this column. The identity column is used to auto-generate surrogate key values. The identity functionality is implemented by the database, so it is faster and it results in less lock contention than the alternatives typically implemented in the application code, such as temporary tables used to draw consecutive numbers.

The next dialog, the Define primary key, is used to select the ID column as the primary key for the table. Note that only the columns that were defined with the NOT NULL attribute are available as primary key candidates.

In addition to the described functionality, the Create Table wizard also allows us to specify unique keys and foreign keys, insert script snippets into the table definition script, and save the generated script in a Visual Studio project folder. At the end of the process, the wizard shows the table definition summary. We can also display the generated DDL script, as shown in Figure 5.

Figure 5: View the generated script.

Once the script is successfully executed on DB2 for iSeries, the newly created table COFFEES shows in the IBM Explorer panel.

Let's move on now to creating the required stored procedures. To create a stored procedure that selects all rows from COFFEES, right-click the Procedures icon in the IBM Explorer panel and select New > Procedure. This activates the DB2 SQL Procedures Wizard. The Provide Information About the Procedure dialog allows us to specify the procedure schema name, procedure name, and specific name. We'll create the COF_SELECT procedure in DB2USER schema to retrieve all the rows from COFFEES. On the next dialog, Create SQL Statements, we provide the appropriate SELECT statement. This is shown in Figure 6.

Figure 6: The Create SQL Statements dialog allows us to create SQL statements.

We use the Validate SQL function to make sure that the statement will execute on the iSeries server.

Additionally, the DB2 SQL Procedure Wizard allows us to specify the input/output parameters, insert code fragments, and save the generated script in a Visual Studio project. At the end of the process, the wizard submits a request to compile the stored procedure on iSeries. The SQL code is compiled into program object (*PGM) COF_SELECT in the specified schema DB2USER, and the procedure is registered in the iSeries database catalogs. The newly created procedure also appears in the IBM Explorer panel. We can view the generated SQL script by right-clicking the COF_SELECT icon under Procedures in IBM Explorer and selecting View Create Script. The body of the created SQL stored procedure is listed below:

At [1] in the code, the stored procedure opens a result set to be returned to the client. This result set can then be bound to a visual control, such as a DataGrid in a client application.

Next, to illustrate the process of using input/output parameters, we'll create the COF_UPDATE procedure. We start the DB2 SQL Procedure Wizard and specify DB2USER as the schema and COF_UPDATE as the procedure name. On the second dialog, we provide the following SQL statement:

Note that I intentionally omitted the ID column on the list of the columns to be updated. By definition, the identity columns cannot be updated with user-specified values. I also recommend that you validate the SQL statement before moving to the next dialog. This ensures that the wizard can properly derive the attributes of the parameters specified in the UPDATE statement (parameter names are denoted by a leading colon).

The next dialog, Specify Parameters for the Procedure, allows us to define the input/output parameters required by the stored procedure. In this case, since the SQL statement provided in the previous step was valid, the wizard automatically detected and inserted the necessary parameters. This is shown in Figure 7.

Figure 7: Use this wizard to define the input/output parameters required by the stored procedure.

The next dialog, Specify Script Options for the Procedure, allows us to specify files that contain script fragments to be inserted in the generated procedure script. Also, we can specify additional SQL statements that we want to generate. We use this functionality to add the LANGUAGE SQL clause to the stored procedure header. This clause, required by the DB2 for iSeries parser, is not automatically generated by the wizard. On the dialog, we click the Insert Code Fragments button to open the Code Options dialog. In the Header field, we provide the path to the file containing the required clause. This is illustrated in Figure 8.

Figure 8: Insert code fragments.

Now, we can continue to the Summary dialog and inspect the generated code by clicking the Show SQL button. The final stored procedure code generated by the wizard is shown below:

At [1], the necessary clause has been inserted from the external script file. With the LANGUAGE SQL added to the CREATE PROCEDURE statement, the procedure compiles without any problems on the iSeries server.

The DB2 SQL Procedure wizard is also used to create the two remaining procedures: COF_DELETE and COF_INSERT.

As mentioned, the database objects (the table COFFEES and the stored procedures) created in previous steps are used by a sample .NET application called StingerAccess2iSeries. We created the application in the Visual Studio IDE as a Visual Basic project using the Windows Application template. The sample application uses DB2 LUW .NET provider to access DB2 for iSeries in the disconnected mode. In the disconnected mode, a DataAdapter object is used to retrieve data from the database and to store it in a DataSet object that serves as a local data cache. A .NET application can use the data cached in a DataSet without a need for multiple round-trips to the database. A DataAdapter can also implicitly update the central database with changes made to the DataSet.

In our case, we define a DB2DataAdapter that will perform the four fundamental operations (SELECT, INSERT, UPDATE, and DELETE) on the table COFFEES. The DB2DataAdapter will use the stored procedures that were coded in the previous section. The DB2 LUW for .NET provider-specific classes are easily accessible through the Visual Studio Toolbox. To visually configure the necessary DataAdapter, drag a DB2DataAdapter object from the Data tab in the Toolbox and drop it on the Form1.vb designer. This activates the DB2 Data Adapter Configuration Wizard. The wizard allows us to specify DB2 commands that are either SQL statements or stored procedure calls. It also helps us define the structure of the result set and facilitates mapping of DB2 command parameters to the result set columns.

On the wizard's first dialog, DB2 Data Connection, we provide the iSeries connection information. We'll use the previously defined MYI5:db2user(localhost) connection (see Figure 2). On the second dialog, SQL Statement Options, we choose to include the INSERT, UPDATE, and DELETE commands by selecting the corresponding checkboxes. On the next dialog, SQL SELECT Statement, we change the Command Type to Procedure and also provide the schema and stored procedure names. Note that the Procedure signature is filled by the wizard as soon as we select the procedure name. Figure 9 illustrates this step.

Figure 9: Use this wizard to configure the SELECT command.

The COF_SELECT stored procedure returns a result set. The result set is locally cached in the .NET application in a DataTable object. A DataTable is, in turn, contained in a DataSet object. The necessary mapping of the returned result set columns to the DataTable columns can be automatically performed by the wizard. To do so, we click the Mapping button on the SQL SELECT Statement dialog. This launches the Data Table Mapping dialog. Here, we click the Discover button, which generates the mapping of the source result set columns to the Dataset Table columns. This is shown in Figure 10.

Figure 10: There's even a wizard for dataset table mapping.

Now, we change the ID column to not Nullable to reflect the source column attributes.

In the next step, we configure the INSERT command. On the SQL INSERT Statement dialog, we select Procedure as the statement type and provide the schema and procedure names (DB2USER, COF_INSERT). We also need to map the stored procedure parameters to the corresponding columns in the Dataset Table object. By clicking the Parameters button, we open the DB2 Parameter Specification dialog. Here, we map the P_COF_NAME parameter to the COF_NAME source column. We'll leave source version attribute at Current. In a similar manner, we map the rest of the COF_INSERT parameters. As a reminder, the value for the ID column is generated by the database, so there is no mapping for this column. See Figure 11 for details.

Figure 11: Map parameters for the COF_INSERT procedure.

In the next step, the SQL UPDATE Statement dialog allows us to provide the definition and mapping for the COF_UPDATE procedure. The process is analogical to the INSERT command, with one exception. The COF_UPDATE procedure accepts one additional parameter, OLD_ID, which is mapped to the primary key column ID. The database uses the parameter value to locate the record to be updated. Therefore, we map the original version of the ID column to the OLD_ID parameter. This is illustrated in Figure 12.

Figure 12: Map parameters for the COF_UPDATE procedure.

We follow the same process to specify the DELETE command on the SQL DELETE Statement dialog.

At the end of the configuration process, the wizard creates two objects associated with the VB Form class: db2Connection1 and DB2DataAdapter1. The wizard also generates the necessary code in the "Windows Form Designer generated code" region. Developers should not modify this code; the wizard should be used to make any changes in the DB2DataAdapter configuration.

As previously stated, a local copy of the data is cached in a DataSet object. Once the DB2DataAdapter1 is created, we can use it to generate a DataSet object that represents the result set metadata and holds the data rows for the sample application. To do so, we right-click the DB2DataAdapter1 control and select Generate Data Set from the context menu. On the Generate Data Set dialog, add the COF_SELECT_RS1 Data Table to the new DB2DataSet1 object.

Then, to make the source code more readable, we change the DB2DataAdapter1 name to daCoffees, db2Connection1 to cniSeries, and DB2DataSet1 to dsCoffees. Finally, we bind the dsCoffees to a DataGrid in the VB Form and add code for the Populate, Select, and Modify buttons that are used to manipulate the data through the DataGrid. The finished application is shown in Figure 13.

Figure 13: Our sample application is finished.

The DDL sources for the sample table and stored procedures and the source code of the finished StingerAccess2iSeries application are available for download.

I encourage you to review the source code because it contains the recommended error handling as well as the identity column handling. Due to the limited space, these topics are not covered in this article.

The Final Tip

If the best possible performance is critical for your application, you might consider using the DB2 Add-ins to build your application. Then, you could migrate the application to use the "native" .NET provider included with iSeries Access for Windows. You should be able to get an almost working application by simply changing IBM.Data.DB2 to IBM.Data.DB2.iSeries and DB2% to iDB2% . For example, IBM.Data.DB2.DB2DataAdapter becomes IBM.Data.DB2.iSeries.iDB2DataAdapter, IBM.Data.DB2.DB2Type.Integer becomes IBM.Data.DB2.iSeries.iDB2DbType.iDB2Integer, and so forth.

One thing to remember is that you must have a valid DB2 Connect license in order to take advantage of the DB2 Add-ins.

Go Forth

Hopefully, this article has given you a taste of the truly unique capabilities of the DB2 Add-in. The toolset targets .NET application developers, database administrators, and database architects who wish to take advantage of the DB2 functionality and scalability.

Jarek Miszczyk is the Senior Software Engineer, PartnerWorld for Developers, IBM Rochester. He can be reached by email at This e-mail address is being protected from spam bots, you need JavaScript enabled to view it .