Use System Naming Convention and Library List with .NET Managed Provider

October 13, 2004 Michael Sansoterra

The iSeries .NET managed provider, first released in iSeries Access V5R3, lacked the ability to specify a library list and the SQL naming convention in connection strings. We were stuck with the defaults. However, with the introduction of service pack SI15176, in early September, IBM has enhanced the provider to set the library list and naming convention in the connection string.

For those unfamiliar with the concept of a managed data provider, it is .NET’s plug-in equivalent to an ODBC driver in the Windows world or a JDBC driver in the Java world. Each database manufacturer creates a .NET provider to let .NET programs access data from their respective database. While .NET programs can use ODBC and OLE DB data sources, a database provider written specifically for .NET will perform better because special calls don’t need to be made to execute code written outside of the .NET framework (which is the case when using ODBC and OLE DB data access.)

Below is a C# example of establishing a connection using the two new properties:

private void ReadData()
{ 
      //
      // Naming Convention Values:  System 
      //                            SQL
      //
      iDB2Connection connDB2=new iDB2Connection( 
              "DataSource=your400.yourcompany.com;" + 
              "userid=MIKE;password=MYPASSWORD;" +
              "LibraryList=*USRLIBL;Naming=System");

      iDB2Command cmdDB2=new iDB2Command(
              "Select * From DataFile",
               CommandType.Text, connDB2);

      iDB2DataReader drDB2;

      connDB2.Open();
      drDB2=cmdDB2.ExecuteReader();
	  while(drDB2.Read())
      {
                   // Do something here
      }
      drDB2.Close();
      connDB2.Close();
}

The two new property names in the connection string are LibraryList and Naming. Unfortunately, the Programmer’s Toolkit help file was not updated with these new properties, so here is a brief explanation of each.

LibraryList accepts a comma delimited list of libraries. Additionally, the *USRLIBL keyword can be specified to indicate the user library list should be used (the user library list will be inherited from the job description of the user ID used to establish the connection). Further, *USRLIBL can be used as a place holder for adding additional libraries. For example, LibraryList=*USRLIBL,TESTDATA will add the library TESTDATA after the user library list. Likewise, specifying LibraryList=TESTDATA,*USRLIBL will place TESTDATA first in the library list. As far as I can tell, this functionality is identical to the iSeries Access ODBC driver’s ability to set the library list.

Naming accepts a value of System to indicate the system naming convention should be used, and SQL to indicate that the SQL naming convention should be used (SQL is the default). The basic difference between the two is the separator character and the system convention’s ability to use the library list for unqualified table names.

For those of us getting to speed on .NET, IBM has made life easier because we don’t have to worry about some of the complexities of SQL programming using the SQL naming convention.

For more information on these new properties, see the “ReadMeSP.txt” file that comes with SI15176.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.

Editor’s Note: Lorie DuBois, the developer responsible for the DB2 UDB .NET Data provider for iSeries, at IBM Rochester, had a clarification of the following sentence: “As far as I can tell, this functionality is identical to the iSeries Access ODBC driver’s ability to set the library list.” According to DuBois: “Our functionality is almost the same as the ODBC driver’s. The difference is with how we handle the Default Collection property. With ODBC, they add the Default Collection property to the library list. The .NET provider chose not to do this. With the .NET provider, if you want the Default Collection library in the library list, you must specifically include it.”