Howard�s ODBC Connection String FAQ

Everything you always wanted to know about ODBC connection strings and the iSeries.

When using ODBC to talk to the AS/400, you can use a data source name to connect or you can use a connection string with all of the relevant settings in the string you pass to the ODBC Driver. A data source name stores ODBC information in the Windows registry after you use the ODBC Data Source Administrator to configure your data source. The problem with data source names is that each time you attempt to connect; the program has to look up information about the connection options in the Windows registry. If you are programming a web application, you could be issuing hundreds of connections to the iSeries a minute, and the performance hit of reading the registry is not something you want to have in your application. Another problem with data source names, if you are deploying ODBC applications to end users, is that you must set up the data source on the end users machine as part of your program installation process. This can be problematic, and if the desktop is not properly configured you end users can futz with their ODBC settings and possibly make changes the break the application. All of the above can be avoided by using a connection string rather than using an ODBC data source name when connecting. In addition, you will have finite control over how you application interacts with the iSeries.

Problems with connection strings

The problem with using connection strings is that IBM likes to change some keywords from Client Access version to Client Access version. I have never understood this; I think sometimes it is change for the sake of change. In addition, the keywords used in connection strings are poorly documented and what documentation exists is difficult to find. Finally, the documentation that exists does not necessarily tell you how you might use these settings to enhance performance of an application or how some settings can help with the development process. This article attempts to rectify that situation by giving you an application to generate connection strings for V5R2 Client Access ODBC and by documenting most connection keywords. I recently spent about three weeks gathering this information from disparate sources and writing the program to generate these strings, I hope you find it useful.

Basics of a connection string

An ODBC connection string is a series of keywords and values separated by the semi-colon character in the form KEYWORD=<value>; where KEYWORD is an allowed ODBC or driver specific keyword and <value> is one of the permissible values for the keyword. Some values are passed as numbers, some a strings. Each value depends on the acceptable range of values for the appropriate keyword.

An ODBC connection string has some keywords that must be supplied in order to make a connection. The following string instructs ODBC to look for a Data Source in the registry called MY400, (the DSN keyword), and to connect to the data source using the user ID HARNER, (the UID keyword) and the password SECRET, (the PWD keyword):

Dim sConStr as String

sConStr = "DSN=MY400;UID=HARNER;PWD=SECRET;"

To connect without a data source name, the following connection string could be used:

Dim sConStr as String
sConStr = "Driver=Client Access ODBC Driver (32-bit);"
sConStr = sConStr & "System=192.168.1.25;UID=HARNER;PWD=SECRET;"

The Driver keyword tells ODBC which ODBC Driver to use in attempting to make a connection tot eh iSeries. Client Access ODBC Driver (32-bit) was the name of the Client Access ODBC Driver for the past several releases of Client Access. With V5R2, IBM renamed the driver iSeries Access ODBC Driver which would have broken backward compatibility if they had not also left a legacy entry for the previous driver name. I continue to use the previous driver name, so that my software is compatible with previous versions of Client Access.

The System keyword specifies the AS/400 that you want to connect to. This keyword can contain either the IP address of the AS/400 or it can contain a resolvable DNS name. UID and PWD are the user ID and password that you want to send to the iSeries in order to establish a connection, (Note that these can also be specified using the Password and UserID keywords). Connecting with the basic connection string will result in a connection with all ODBC settings defaulted. In the next sections of this article, I will cover other settings and their default values and attempt to clarify how the settings effect your connection to the iSeries.

Keywords That Affect Security

The following keywords affect the security of your iSeries and the connection from client to server.

The SIGNON keyword allows you to specify an integer value which decodes to what should happen if no User ID or Passwords are specified, or if the User ID and Password combination are invalid. A 0 value instructs the driver to use the Windows user ID and password every time a connection is attempted with the iSeries. Use this option if you use the same user id and passwords on your Windows and iSeries user profiles.  A 1 value instructs the driver to use the default user id, if one is available, that is associated with this iSeries in Operations Navigator. Note that if you use this setting and also use the UID or UserID parameter this setting will be ignored. A value of 2 specifies that no default user ID should be provided if the connection string does not provide one. A value of 3 instructs the program to look at the Operations Navigator settings for this iSeries and use those settings, (this is the default value, so if you do not specify the SIGNON keyword this is how the connection will behave). A value of 4 will cause the driver to use Kerberos principal for authentication. This option is only available to Windows 2000 and later operating systems that have Kerberos enabled and can only talk to V5R2 and above iSeries systems. I typically use value 2, (no default specified), for this connection option as I do not want the system searching the registry for the default behavior.

The SSL keyword allows you to specify whether to use Secured Sockets Layer in communicating with your iSeries. The iSeries supports SSL communications on systems with V4R4 or later. The value 0 is the default value and tells the driver to only use SLL in sending the password to the iSeries. A value of 1 will cause the driver to use SSL for all communications with the iSeries. I typically do not specify this value as SSL encryption will cause a tremendous overhead in processing power on the client and server systems. If I am on the internal network, I feel I do not need this security. However, if connecting to the iSeries over the internet, you may wish to enable SSL to encrypt the data being sent between client and server systems.

Settings that Affect the Database Server

The following settings control how your client connections interact with the iSeries database server. Several of these have implications beyond the obvious, so read on!

The NAM keyword, (can also be specified as Naming) specifies the naming convention to be used when issuing SQL statements against the iSeries. It defaults to value 0 which represents SQL Naming Convention. A value of 1 indicates *SYS Naming Convention should be used. Ninety nine percent of the time, I connect using the default value so I do not specify this keyword. However, there are important security, (and other!), differences between SQL and *SYS naming conventions. You should review these differences in the iSeries SQL Reference to understand when *SYS naming is appropriate and when *SQL is appropriate. The main ramification of this keyword is the catalogue separator character. In *SYS naming convention, the separator is the / character. In SQL naming convention the separator is period (.) character.

The CMT keyword, (can also be specified as CommitMode), identifies the isolation level that should be used when connecting to the iSeries. The isolation level refers to the level of locking and commitment control that you will have when executing SQL statements against the iSeries. A value of 0 specifies the isolation level of *NONE on the iSeries. This isolation level means that you will attach read locks when reading data, but these locks will disappear as soon as your cursor moves off the current record. This isolation level is appropriate for reporting programs or programs that will only read data. Any inserts, updates and deletes can not be rolled back in this isolation level and will be committed immediately. Note that this is the only isolation level that can be used when working with non-journaled tables. A value of 1 specifies read committed, (also referred to as cursor stability or *CS), isolation level. A value of 2, (the default value), specifies read uncommitted, (also referred to as *CHG), isolation level. A value of 3 specifies repeatable read, (also referred to as *ALL), isolation level. A value of 4 specifies serializable, (also referred to as *RR), isolation level. If you do not specify this keyword, the driver will default to *CHG isolation.

You should note that connections via ADO using the CMT keyword are re-set by ADO to use the Read Uncommitted isolation level and also ADO automatically turns on support for auto commit. To place ADO connections into another isolation level, you should use the connection objects isolation level property. To use commitment control with ADO, you should use the BeginTrans, CommitTrans and RollbackTrans methods of the connection object. Connections using the ODBC API respect this command line setting. Also, bear in mind that you can not use insert, update or delete statements against non-journaled tables unless you are at isolation level *NONE with auto-commit turned on; (this is the default for an ADO connection).

I typically use *NONE isolation and only switch to higher isolation levels when I need commitment control for transactions. Also, remember that once you are connected your program can change the isolation level value during its connection; this keyword only covers the initial isolation level. In addition, changing the isolation level usually implies a commit operation, so you should keep this in mind when programming with ODBC. For more information on how isolation levels effect locking and transactions, refer to my book, iSeries and AS/400 SQL at Work or the iSeries SQL Reference.

The CONNTYPE keyword, (can also be specified as ConnectionType), can be very useful as it controls the type things a user can do when connected to the AS/400 system. If the value is 0, (the default value), the user can execute any SQL statement that their authority on the AS/400 allows, (i.e. Insert, Update, Delete, Create, etc). If the value is 1, the user can only execute SELECT and CALL statements. If the value is 2, the user can only issue SELECT statements. I use this value when coding user query applications to ensure that the user can only issue SQL Select or stored procedure calls. You should bear in mind that this values does not override OS/400 security, if the user does not have authority to an object, they can not touch the object.

Here is some example code to illustrate the use of the CONNTYPE keyword. First, issue the following SQL statements to create a table called SEQCTRL in your SQLBOOK library and insert a record into the table:

CREATE TABLE SQLBOOK.SEQCTRL
       (SEQID CHAR(10) NOT NULL PRIMARY KEY,
        VLU INTEGER NOT NULL WITH DEFAULT);

	INSERT INTO SQLBOOK.SEQCTRL VALUES ('WO',0);

Next, execute the following in SQLThing�s Procedure Editor or using Client Access Express to create a stored procedure:

CREATE PROCEDURE SQLBOOK.GETSEQ            
	(REQKEY IN CHAR(10),                       
	 RETVAL INOUT INTEGER)                     
LANGUAGE SQL NOT DETERMINISTIC                 
BEGIN                                      
	DECLARE C1 CURSOR FOR                      
	 SELECT VLU+1 FROM SQLBOOK.SEQCTRL         
		 WHERE SEQID = REQKEY FOR UPDATE OF VLU;   
	OPEN C1;                                   
	FETCH C1 INTO RETVAL;                      
	UPDATE SQLBOOK.SEQCTRL 
	 SET VLU=RETVAL       
	 WHERE CURRENT OF C1;                      
END                                        

Next, execute the following VB code; you will not be able to call the procedure unless you do this.

Dim C As New ADODB.Connection
C.Open "Driver=Client Access ODBC Driver (32-bit);" & _

    "CONNTYPE=2;SYSTEM=192.168.1.2;UID=X;PWD=SECRET;"
Dim Cm As New ADODB.Command
Cm.ActiveConnection = C
Cm.CommandText = "CALL SQLBOOK.GETSEQ(?,?)"
Cm.Parameters.Append Cm.CreateParameter("A", adChar, adParamInput, 10, "WO")
Cm.Parameters.Append Cm.CreateParameter("B", adInteger, adParamOutput, , 0)
Cm.Execute
MsgBox Cm.Parameters(1).Value
C.Close

Now, change the value of the CONNTYPE keyword to 1 and you will be able to execute the call to the stored procedure, but not perform any SQL statements other than SELECT statements.

The ALLOWPROCCALLS keyword allows you to specify if the user should be able to call stored procedures when the ODBC connection attribute is set to read only mode. The default value is 0, which specifies that the user should only be able to call stored procedures when not in read only mode. A setting of 1 specifies that the user can call procedures even when the connection attribute is set to read only. This value can be overridden by the CONNTYPE keyword, (i.e. setting the CONNTYPE=2 and ALLOWPROCCALLS=1 will not allow calls to stored procedures). This keyword is only useful if you are programming directly to the ODBC API and are setting the connection attribute to read only in your code and would like to call procedures.

The DBQ keyword, (can also be specified as DefaultLibraries), allows you to specify the library list that the connection should have. You can specify the libraries as a list separated by commas or spaces. You can also place the reserved word *USRLIBL in this list which will cause the server library list to be appended to the list of libraries at that point in the list. If you do not specify *USRLIBL, the server library list will not be appended. If you do not use this keyword in connecting, the system will use the server library list as the connected jobs library list.  In addition, it is important to know that the first library specified in this list is used as the default library. The default library is where any objects you create, (which are not qualified with a library name), will be created.  If you do not want to have a default library, begin this list with a comma.

A few other considerations about the library list are important to understand. Let us say you have a library on your iSeries called X123 but you place X12 into your library list and there is no corresponding library X12 on your iSeries; your library list will not be replaced because of the invalid library. In addition, if you specify a library twice in the list your library list may not be replaced, (this depends a lot on the version of Client Access and the PTF level on your iSeries).

Finally, the way the library list is used bears some consideration and changes based on the naming convention you are using, (see the NAM keyword). If you are using *SQL naming convention, the following special considerations are in effect.

1)      If you do not specify a library list, (exclude this parameter), your default library will be your user profile name that you used to connect to the iSeries. I.e. If I connect as HARNER and do not specify a library list, my default library is HARNER. This happens even if you set the Default Library for the user profile.

 

2)      If you specify a list but use a comma as the first library, (to not specify a default), your default library is your user profile name.

 

3)      The AS/400 will search for procedures, functions and user-defined types in the library list. The AS/400 will NOT search for tables, files or views using the library list. Any un-qualified table names must exist in the default library, (either first in the list or your user name, (see rules 1 and 2)), or they will not be found.
 

The following special considerations are in effect for *SYS naming convention.

 

1)      If you do not specify a library list, (exclude this parameter), you will not have a default library. However, if your user profile is set to have a default library, that will be your default library. Setting the user profile to have a default library will override ANY setting you try to make with ODBC.

 

2)      Placing a comma as the first library in the list will cause no default library to be set. However, the user profile you use to connect will override this if the profile has a default library set.

 

3)      If you set a default library, the iSeries will not search the library list for unqualified files, tables or views. If a default library is not set, any unqualified SQL statements WILL cause the database server to search the library list for that table.

The UNICODESQL keyword allows you to specify whether SQL statements are sent to the iSeries in Unicode or EBCDIC format. A value of 0, (the default), will cause the driver to send statements in EBCDIC. A value of 1 will cause the driver to send statements as Unicode. This option only works with V5R1 or above.

The DATABASE keyword is new for V5R2 and allows you to specify the iSeries relational database that you wish to connect to. If you pass an empty string or *SYSBAS you will be connected to the local database on the iSeries that you are connecting to. If you pass a string, you will connect to the local iSeries, but queries will go against the RDB, (Remote Database Entry), that is specified in the Work with Relational Database Directory Entries, (WRKRBDIRE). Assume that you have an AS/400 system defined as BLUE and you are connecting to system GREEN that has an RDB entry for BLUE. If you pass BLUE as the database parameter when connecting to GREEN, you will be accessing data from the system BLUE.  I have not yet tested this option, but that is the theory, er documented, behavior.

The TRUEAUTOCOMMIT keyword allows you to turn on support for ODBC auto-commit. Auto-commit causes a commitment operation to be executed after each SQL statement is executed. Use value 1 to turn on support for true ODBC auto-commit. The default is 0; do not use true auto-commit. Using this setting, you are able to work on non-journaled tables in isolation levels other that *NONE. However, ADO does not respect this keyword as it issues connection attribute settings when connection is made to the iSeries rendering this keyword ineffective. If you are programming directly to the ODBC API, this keyword can be quite useful.

Settings that Affect Data Formatting

The data formatting keywords allow you to specify the way date, time and decimal values are handled by the iSeries when returning data. It is important to understand how these settings can effect the execution of your programs on the client and server and how some options affect the way stored procedures and user defined functions are created.

The DFT keyword, (can also be specified as DateFormat), sets the format that is used with date literals that are passed to the iSeries.  You can specify the following options for the date format, of which *ISO is the default:

Now, the setting of these values is very important if you are using the connection to create user defined functions or procedures that have date arguments or use dates internally. See, if you are using *MDY as your format and then you create a function that uses dates, the function is created with *MDY as the date format and will only be able to handle dates between 1940 and 2039. If fact, *JUL, *MDY, *DMY and *YMD also carry the 1940 to 2039 restriction. You can override, (starting with V5R1), the date format that a procedure or function will be created with by using the SET OPTION statement, but it is important to understand the ramifications of the date format setting.

Another ramification of the date setting is that if you are using one of the restricted date formats and attempt to read a date before 1940 or after 2039 the iSeries will simply stop sending data to the client application and issue a data mapping error message CPF5035 into the server job log. This is tragic, as to the client application it seems as if you have reached the end of file, but alas, you have just encountered a data hiccup. If there is any chance that you are going to read data outside the 1940 to 2039 range, I suggest that you do not use the restricted formats. Finally, even though you might be using a restrictive setting, the iSeries will accept string literal dates in the *ISO or the *USA formats that are outside the ranges allowed by the selected format.

The DSP keyword, (can also be specified as DateSeperator), is only allowed when using the *JUL, *MDY, *DMY or *YMD formats and allows you to change the date separator character. The allowable values are; 0 for forward slash, 1 for dash, 2 for period, 3 for comma and 4 to specify a blank.

The DEC keyword, (can also be specified as Decimal), allows you to specify the character to use as a decimal separator. A 0, the default, specifies the period character as the decimal separator whereas a 1 specifies to use the comma character.

The TFT keyword, (cal also be specified as TimeFormat), allows you to change the format used in time literals in SQL statements. The following table outlines the settings, of which value 0 is the default.

The TSP keyword, (can also be specified as TimeSeparator), allows you to change the separator character used in time literals. This setting only has an effect is the TFT is set to 0, (the default). The valid settings are 0, (the default), for the colon character, 1 for the period character, 2 for the comma character and 3 for the blank character.

Performance Enhancing, and Degrading, Settings

The following settings can be used to tune the performance of an ODBC application.

The BLOCKFETCH keyword allows you to turn on or off the block fetching of data. Block fetching will cause the iSeries to send multiple results, (if a function returns more than one result), to the client application even if the client application only requests one record in a single fetch. This can really improve the processing of a reporting or web application that is listing data.

By default, block fetching is enabled but passing value 0 to this keyword will allow you to control block fetching at the ODBC level. Controlling block fetching at the ODBC level is fine if you are programming directly to the ODBC API, but since most programmers using ODBC are using ADO or a third party application like Crystal Reports, I recommend that you leave this setting at the default value so that Client Access will control blocking. I have done a lot of experiments using record blocking and here are some performance tidbits; when using blocking on a select statement against a 100,000 record table I am able to achieve over 5,000 records per second with blocked ODBC fetches, whereas I can only retrieve 190 records per second when not using blocking techniques.

The BLOCKSIZE keyword, (can also be specified as BlockSizeKB), works hand in hand with the BLOCKFETCH keyword and lets you set the size in kilobytes of the fetch buffer. The default size is 32 but can be adjusted in the following increments: 1, 2, 4, 8, 16, 32, 64, 128, 256 and 512. In my web applications, I go so far as to adjust block size based on what the individual web page is doing, using larger sizes if the expected result set is large and smaller sizes if it will be small. Large block sizes can really impact reporting and exporting applications performance.

Here is a small VB/ADO program that demonstrates the effectiveness of using the BLOCKFETCH and BLOCKSIZE keywords.

Dim c As New ADODB.Connection
Dim cm As New ADODB.Command
c.Open "Driver=Client Access ODBC Driver (32-bit);" & _
    "SYSTEM=216.199.26.5;UID=HARNER;PWD=harner;" & _
    "BLOCKFETCH=0;"
cm.ActiveConnection = c
cm.CommandText = "select table_name from QSYS2.systables"
Dim rs As ADODB.Recordset
Set rs = cm.Execute()
Dim a As Single, b As Single, cn As Long
cn = 0
a = Timer()
While Not rs.EOF
    rs.MoveNext
    cn = cn + 1
Wend
b = Timer()
MsgBox "Time = " & Format(b - a, "###,###.0000") & _
    " Records = " & Format(cn, "###,###") & _
    " RPS = " & Format(cn / (b - a), "###,###.0000")

c.Close

Note that the example above sets the BLOCKFETCH keyword to 0, turning off block fetching. My iSeries was unavailable, so I used a 384KB/256KBADSL connection to another iSeries and tested this program. The target iSeries had 2,577 tables in the SYSTABLES view. I then changed the connection string to BLOCKFETCH=1, and finally added the keyword BLOCKSIZE=512. The results of my simple test are in the following table which clearly shows the effectiveness of using blocking and large block sizes for large result sets:

Note that overriding the default block size of 32KB to 412JKB resulted in a 41% increase in records per second. Also, I applied the COMPRESSION=0 keyword, (turns off compression), in a further test and was only able to achieve 190 records per second. Obviously compression is very important when you are connected via internet rather than onsite.

The COMPRESSION keyword, (can also be specified as AllowDataCompression), toggles whether the iSeries should compress data that it is sending to the client. The default setting is 1, which will cause compression to be enabled, and I always use data compression as even when I am on the same switch as the iSeries it improves my data performance. A value of 0 disables data compression. This does incur a slight processing overhead on the client and server, but it seems to be worth the rewarded and noticeable improvement in speed. The only time to turn this off, IMHO, is when accessing large binary objects that are already compressed or do not lend themselves to compression as you are not gaining the benefit of compression an are incurring needless overhead.

The CONCURRENCY keyword lets you override ODBC concurrency settings and force the iSeries to always open cursors as updateable. The default value, 0, will cause the driver to use the settings within the application whereas the value 1 will cause all statements to be generated as updateable cursors. I do not recommend that you set this value, as updateable cursors cause overhead on the server side of you application. If you need updateable cursors, program them using the appropriate API calls. If you do set this one, be aware that statements using the FOR FETCH ONLY clause will not open as an updateable cursor. Also, system catalogue function in ODBC and ADO never return updateable result sets. Finally, you should not use this option because it can cause the iSeries to issue locks against records when opening the cursors as updatable.

The EXTCOLINFO keyword, (can also be specified as ExtendedColInfo), allows you to get more information about columns returned from a statement. This option will only effect programmers that are programming directly to the ODBC API as it will cause the get descriptor field and get column attributes API�s to retrieve additional information from the iSeries during a call to the SQLPrepare API. This will cause a little more processing on the iSeries and a little more data to be returned during SQLPrepare. The default for this value is 0, off, and it can be turned on by passing value 1. If you use this, you can do some neat things with the ODBC SQLColAttribute API like retrieve the base column name for a column in a result set or retrieve the iSeries header or description field for a column in a result set. Also, if you are using ADO and have this option checked ADO will return the iSeries column text description or column text header for the underlying field name in a select as the ADO field name. This can be useful for data display applications, but can cause problems as setting this option will cause multi-step OLEDB errors when accessing field objects properties in the ADO object model. See the REMARKS keyword for additional information that affects this keyword. This option only works with V5R2 systems.

The LAZYCLOSE keyword allows you to turn on support for lazy close which can add speed to your application by delaying the closing of cursors on the iSeries. This can be a very bad thing, because even if in your application you have specified to close a recordset object or an ODBC statement handle the cursor will stay open on the iSeries and hold any locks associated with the cursor. However, if you are using commitment control properly, and thereby flushing locks by committing or rolling back your transactions, you can use this option to keep cursors open on the iSeries. This helps when your application is using prepared SQL statements as the open cursors can be reused. By default, lazy close is disabled, option 0. To enable, pass option 1.

The MAXFIELDLEN keyword, (can also be specified as MaxFieldLength), controls how much LOB (large object) data is sent in a result set. The value indicates the size threshold in kilobytes and the default value is 15360 and in V5R2 the maximum value allowed is 2097152, (2MB). If a LOB is larger than this value, you will have to use subsequent calls to retrieve the rest of the LOB data in your application. Now, this brings up some interesting things about performance and large objects. First, I do not recommend that you store large objects in primary data tables. For example, if you have an employee file that contains employee ID, name, address, and other assorted information you should not have a BLOB field in that file that contains the employee picture. Separate large objects into tables dedicated to large object storage. In the employee example, you might have a field called Employee_Picture which contains an integer. You would then use the integer to look up the employee picture large object from the large objects table. In this way, you increase your performance by a) keeping the records in the employee file smaller and therefore easier to cache on the iSeries, and b) separating large objects to possibly a different ASP and keeping programs from accessing the large objects accidentally when opening the employee file.

The PREFETCH  keyword turns on or off the pre-fetching of data. Pre-fetching means that the iSeries will go ahead and send data to the client application when you call the SQLExecute API. Most ODBC applications can not handle using pre-fetching so I recommend that you leave this option off as you may find you are missing records from the beginning of your result set. To turn on pre-fetch, use value 1. By default, this value is off, 0.

The QUERYTIMEOUT keyword lets you turn on or off support for the AS/400 query governor. When the AS/400 optimizes a query, it generates both an execution plan which governs how the query will be performed and it also generates an estimate of how long it will take to perform the query. By setting the query timeout value, you can cause the AS/400 not to execute queries that it thinks will take longer than X seconds where X is the timeout value. By default, this option has the value 1 which indicates that the AS/400 should not execute queries longer than the query timeout value. Setting this option to 0 will cause the AS/400 to execute ALL queries regardless of whether they violate the query timeout value. There are two things to note about this option. 1) Some of my legacy ODBC applications start to have timeout problems when I activate this option. I�m trying to track down why and will write a Guru tip when I figure it out. 2) This is really useful for ADO applications as you no longer have to set the CommandTimeOut property of the connection or command objects before executing SQL statements that the optimizer thinks will run for a long time. ADO defaults the command time out property to 30 seconds, and I find myself always having to set it to 0 which tells ADO to let the command run forever. 3) The higher the release level the more �pessimistic� the iSeries gets about how long it thinks a query will take. I have queries that in V4R1 the optimizer thinks will take 3 seconds, (actual runtime <.1 second), and when we run on V5R2, (same data and indexes), the optimizer thinks they will take 180 seconds, (actual runtime again <.1 second). So, with that in mind take the estimated runtime with a grain of salt.

Keywords that affect SQL Packages

Using SQL Packages can really improve the performance of your iSeries applications. I have written many Midrange Guru Tips about packages, dynamic versus static SQL and the use of parameter markers so reference those articles or the resources section on my web site if you need to know how packages can help your application. I also cover this extensively in by book, iSeries and AS/400 SQL at Work, which can be purchased here.

In a nutshell, an SQL Package file contains copies of all SQL statements eligible for packaging that your application has used. Besides the SQL statement, the package file contains the execution plan for the statement. When you use packages, the iSeries database server first looks at statements in the package file to see if this statement has been used before. If it is found, the database server does not optimize the SQL statement to determine an execution plan, it uses the plan found in the package file. This saves a lot of processing power and execution time as optimization of SQL statements, (especially against legacy files with lots of logical files), takes a lot of power. Packages are a good thing, so you should use them in your applications where they are appropriate.

The XDYNAMIC keyword, (can also be specified as ExtendedDynamic), is used to turn on or off Extended Dynamic, (SQL Package File), support. The default value is 1 which enables package support. To turn off package support, supply a 0 value to this keyword. Note that the IBM web documentation of this keyword spells it as XDTDAMIC which is incorrect.

The DFTPKGLIB keyword, (can also be specified as DefaultPkgLibrary), is used to set the library to look for SQL packages in. If not specified, the library will be QGPL and please be aware this option only works if extended dynamic is turned on. You set the value of this option to a string representing the library where you want to look for the applications package file. Here is an example: DFTPKGLIB=SQLBOOK;

The PKG keyword, (can also be specified as DefaultPackage), allows you to specify where the package file should be stored and how it should be used. This keyword is kind of screwy and does not give you as much control as when using a DSN, but can be a little useful. It is interesting to note that when using the JDBC toolbox driver, you have a lot more control over the package name and use. Since the JDBC and ODBC use the same infrastructure you would think IBM would give us ODBC users the same level of control; alas we are the red headed stepchildren. The keyword takes the following arguments, LIBRARY/DEFAULT(IBM),x,0,y,z,0  each of which will be detailed below:

The LIBRARY portion can be replaced with any library name and this seems to have absolutely no effect on the application or package file used. I usually pass the value of my default package library for the library argument since it does not appear to do anything. Again, the DEFAULT(IBM) portion seems to have absolutely no effect either, so I suggest that you put it in as shown.  If you want to control the package used, the only way to do it seems to be to use the SQLSetConnectAttr API with and set attribute 2101 to the name of the package you want to use. You must do this before preparing any statement against the connection. BTW, I have not tested this API/Attribute yet but will update you if I find it does not work.

The x represents whether the application is allowed to add statements to the package file. A value of 1 allows the application only to use statements in the package, whereas a value of 2 allows the application to both use and add statements to the package.

The y setting controls what should happen if a package is not usable, (or in some versions of OS/400 the package does not exist). A value of 0 indicates to return an SQL Error. This error will not be returned until you prepare your first SQL statement. In V5R2, the iSeries will create a package if none exists for the application and not return an error, as long as the x setting is 2. If the x setting is 1, (use but don�t add), the application will receive an error that the package could note be created. Setting the value to 1 will cause the iSeries to return a warning if the package file is corrupted or not usable. Setting the value to 2 will cause the iSeries to not issue an error if the package file is corrupted or missing.

The z setting allows you to cache the package locally on the PC. Pass a 0 to not cache the package locally, pass a 1 to cache the package on the PC. Sometimes, if the package file is small, this can improve the performance of the ODBC application.

Finally, some of you will notice that if you use a UDL to create a connection string and set package properties, the iSeries will place the 512 at the end of the PKG keyword argument rather than a 0. I have no idea why is does this, as the documentation says the last argument must be a 0. However, if you look at the ODBC driver for Linux documentation you find that the documentation states this argument must be a 0, but the example string shows a 512 being passed. I have no idea what is going on with that argument, and setting it to 512 or 0 seems to have no discernable effect. IBM needs to fix the documentation of this keyword to let us know what these other options are, and they need to allow us to set the package name with this keyword instead of having to call the set connection attributes API.

Keywords That Effect Sorting

The following keywords can be passed in the connection string to effect how sorting is performed on the iSeries.

The SORTTYPE keyword, (can also be specified as SortSequence), tells the iSeries how to sort records before they are returned to the client application. The default is 0, which will cause a hexadecimal sort to be performed. A 1 will cause the sort to be based on the server job settings, which are controlled by the user profile used in connecting to the iSeries. Passing a 2 will cause the driver to use the setting specified in the LANGUAGEID keyword and optionally weighted with the SORTWEIGHT keyword. A value of 3 can be used in conjunction with the SORTTABLE keyword to specify a custom sort table.

The SORTTABLE keyword, (can also be specified as SortSequence), allows you to specify a custom sort sequence table on the iSeries. This is only used if the SORTTYPE keyword is set to 3. You must specify the library and filename of the sort table in the form LIB/SORTFILE.

The SORTWEIGHT keyword allows you to specify how the iSeries should treat upper and lowercase character sorting. This keyword is only used if the SORTTYPE keyword is set to 2. A value of 0, the default, will cause uppercase and lowercase character to be treated equally in sorting. A value of 1 cause�s uppercase and lowercase characters to have unique weights, thus sorting them differently. Here is an example assuming you have a table called TEST with a single character column called REQGEO and you then perform SELECT * FROM TEST ORDER BY 1 with the shared weight option:

Now, connect to the iSeries specifying SORTTYPE=2;SORTWEIGHT=1; and the same query will return the following data:

The LANGUAGEID keyword allows you to set the 3 character language identifier to use for sorting. You can only use this keyword if you specify option 2 in the SORTTYPE keyword. You can pass any valid language identifier. The default for this value is ENG. Any of the following values are permissible. AFR, ARA, BEL, BGR, CAT, CHS, CHT, CSY, DAN, DES, DEU, ELL, ENA, ENB, ENG, ENP, ENU, ESP, EST, FAR, FIN, FRA, FRB, FRC, FRS, GAE, HEB, HRV, HUN, ISL, ITA, ITS, JPN, KOR, LAO, LVA, LTU, MKD, NLB, NLD, NON, NOR, PLK, PTB, PTG, RMS, ROM, RUS, SKY, SLO, SQI, SRB, SRL, SVE, THA, TRK, UKR, URD, VIE.

Keywords That Affect Catalogue Operations

ODBC has several API functions for querying the connected systems catalogues to find information on tables, indexes, columns, primary and foreign keys and permissions. If you are an ADO user, these API�s are being called when you use the connection object�s OpenSchema method. The following keywords affect how these different API�s work.

The LIBVIEW keyword, (can also be specified as LibraryView), tells the iSeries how to search libraries for information on objects when you perform a catalogue API. The default setting is 0, which will cause the iSeries to only search the library list of the connection for an object. Setting this value to 1 will cause the iSeries to search ALL libraries on the iSeries. This might not be advisable, as if you have a lot of files in a lot of libraries this can take quite a bit of time. Setting the LIBVIEW keyword to 2 will cause the iSeries to only search the current default library. Note that this setting will affect products like Crystal Reports, SQLThing and programs that use ADO to connect to the AS/400 as these products and programs use the catalogue APIs to determine what files and indexes reside on your iSeries.

The REMARKS keyword, (can also be specified as ODBCRemarks), determines where the AS/400 should retrieve the description of tables and columns when this information is requested. Let�s look at an example to see how this keyword works.

First, I create a sample table on my iSeries and then use the Label and Comment SQL commands to add descriptions. Note that SQL comments can be up to 2,000 characters, whereas and SQL label is a string of up to 50 characters. The label command for columns is special, in that a regular label is 60 characters, 20 characters for heading line 1, 20 for line 2 and 20 for line 3. If the TEXT keyword is used in a column label, the limit is 50 characters and the label is treaded as a column description, not a header.

CREATE TABLE SQLBOOK.LAT
	(A CHAR(1) NOT NULL);

LABEL ON TABLE SQLBOOK.LAT IS 'Table Label';

COMMENT ON TABLE SQLBOOK.LAT IS 'Table Comment';

LABEL ON COLUMN SQLBOOK.LAT.A IS 'Column Label';

COMMENT ON COLUMN SQLBOOK.LAT.A IS 'Column Comment';

LABEL ON COLUMN SQLBOOK.LAT.A TEXT IS 'Column Text';

Note that the last label statement uses the TEXT keyword, which will cause the label to become an iSeries column description whereas the label statement without the TEXT keyword specifies a column heading. Now, we are going to use ADO to connect to the iSeries and then use the OpenSchema method to grab information about the column A in the LAT table. Here is the snippet of code we will be working with:

Dim C As New ADODB.Connection

C.Open "Driver=Client Access ODBC Driver (32-bit);" & _
	"REMARKS=1;SYSTEM=x.x.x.x;UID=H;PWD=Secret;"

cm.ActiveConnection = C
Dim rs As ADODB.Recordset
Set rs = C.OpenSchema(adSchemaColumns, Array("JOKER", "SQLBOOK", "LAT", Empty))
MsgBox rs.Fields("DESCRIPTION").Value 

Note that the above snippet passes REMARKS=1 which will cause the iSeries to return the SQL Object Comment, (the results of the above COMMENT ON COLUMN statement), which is the string �Column Comment�.  If you change the REMARKS setting to 0, (the default), the program will retrieve the string �Column Text�, which is the iSeries description of the column. Now, let us modify our little program and look at the EXTCOLINFO effects:

Dim C As New ADODB.Connection
C.Open "Driver=Client Access ODBC Driver (32-bit);" & _
	"EXTCOLINFO=1;SYSTEM=x.x.x.x;UID=H;PWD=Secret;"
cm.ActiveConnection = C
Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM SQLBOOK.LAT", C
MsgBox rs.Fields(0).Name 

Running the above code snippet causes the iSeries to return the string �Column Label�, which is the column header of the field A, as the column name of the first column in the result set. Changing the EXTCOLINFO value to 0 will cause the iSeries to return the actual column name, �A�, instead of the header information.

The SEARCHPATTERN keyword allows you to turn on or off the ODBC wildcard characters. This can be useful when searching for fields or tables that have the underscore character in them. By default, (setting 1), ODBC treats the % and _ characters as wildcards. The % character means space or more characters whereas the _ character means space or any one character. If you wanted to search for tables that start with ABC, you could specify the search pattern ABC% which would find tables named ABC, ABCD and ABCDEF1. If you wanted to find tables that start with ABC, have any character and then a 1 you could specify the pattern ABC_1 which would find names like ABCD1 and ABCK1.  However, suppose you want to get column information for a table named ABC_1, you should specify to option 0 to cause the iSeries to treat the _ character as a literal value. Here is an example:

Dim C As New ADODB.Connection
C.Open "Driver=Client Access ODBC Driver (32-bit);" & _
	"SEARCHPATTERN=0;SYSTEM=x.x.x.x;UID=H;PWD=Secret;"
cm.ActiveConnection = C
Dim rs As ADODB.Recordset
Set rs = C.OpenSchema(adSchemaColumns, Array("JOKER", "SQLBOOK", "ABC_1", Empty))

The above code would search in the SQLBOOK library for a table named ABC_1, and the _ character will not treated as a wildcard character. This setting has no effect on the LIKE predicate used in SQL where clauses.

The CATALOGOPTIONS keyword effects how the iSeries returns alias information. In V5R2, you now have additional options, which allow the iSeries to return Table Privileges and Column Privileges result sets. If you supply a 1 to this keyword, you are telling the iSeries to also provide information on column aliases when returning SQLColumns result set information. A setting of 2 instructs the iSeries to return information for the SQLTablePrivilidges and SQLColumnPrivilidges API calls. A setting of 3 indicates to do both of these operations. I have played with the column privileges under ADO and noticed that I get no result set and it takes a long time to process with setting 2. Setting 1 seems to have no effect on the information returned by the ADO adSchemaColumns result set. However, I have not used the ODBC API calls to inspect any new information; once I do, I will report my findings in the Guru. 

The GRAPHIC keyword effects how the ODBC driver reports on and deals with the GRAPHIC data type. The GRAPHIC data type is used to handle double byte character set languages, (like Kanji), and to handle Unicode strings. The default setting for this parameter is 0, which indicates that the ODBC driver should not indicate that it supports the GRAPHIC data type. A setting of 1 indicates that the driver should report that the graphic data type is supported and if you inquire about the length of a graphic field to report the length in characters, not in byte length. Here is an example using ADO connection and recordset objects and the adSchemaProviderTypes schema to inquire about support of the graphic data type. Note that the example is passing the keyword GRAPHIC=1, if you change this to GRAPHIC=0 the program will responds that graphic is not supported.

Dim C as ADODB.Connection
C.Open "Driver=Client Access ODBC Driver (32-bit);" & _ 
	"GRAPHIC=1;SYSTEM=192.168.1.2;UID=X;PWD=SECRET;"
Dim rs As ADODB.Recordset
Set rs = C.OpenSchema(adSchemaProviderTypes, Array(Empty, Empty))
rs.Find "TYPE_NAME='GRAPHIC'"
If rs.EOF Then
    MsgBox "Graphic not supported"
Else
    MsgBox "Graphic supported"
End If

In addition to options 0 and 1, the GRAPHIC keyword also responds to options 2 and 3. Option 2 tells the driver to list GRAPHIC as not supported and to report the length of a graphic data type as its length in bytes. Option 3 tells the driver to report the graphic type as supported and to return the column length data as a byte count. Here is an example that shows how this keyword is effective.

First, use SQLThing to create the following test table:

CREATE TABLE SQLBOOK.AGTEST
       (A GRAPHIC(10)); 

Now, use VBScript to execute the following program:

Dim C As New ADODB.Connection
C.Open "Driver=Client Access ODBC Driver (32-bit); " & _
	"GRAPHIC=2;SYSTEM=192.168.1.2;UID=X;PWD=SECRET;"
Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM SQLBOOK.AGTEST", C
MsgBox rs.Fields(0).DefinedSize

Note that the above program opens a recordset against the AGTEST table in the SQLBOOK library, which has only one column A defined as a GRAPHIC(10). In the incarnation of the program above, the defined size of the field is returned as 20, (the length in bytes). If you change the GRAPHIC option in the connection string to 1, the defined size of the field will be returned as 10, (the length in characters).

Keywords That Affect Translation Options

The following connection keywords is used to change how the AS/400 handles translation of characters and how it responds to ODBC queries about character support.

The ALLOWUNSCHAR keyword, (can also be specified as AllowUnsupportedChar), tells the ODBC driver how to react when characters that are not supported or can not be translated by the iSeries are placed into a character data type. The default setting, 0, instructs the driver to return an error message when unsupported characters are used. A value of 1 tells the driver to suppress any error messages when unsupported characters are used.

The CCSID keyword tells the driver to override the default client code page with another CCSID. The default setting, 0, instructs the driver to use the default client code page. To specify an alternate code page use a valid numeric CCSID for the value.

The TRANSLATE keyword, (can also be specified as ForceTranslation), tells the ODBC driver whether to force the translation of CCSID 65535 fields to text. The default setting is 0, which causes the driver to not translate 65535 columns. Setting this value to 1 allows the driver to translate this binary data to text data.

The XLATEDLL keyword, (can also be specified as TranslationDLL), allows you to specify a DLL to be used for translation of data between the client and server. This DLL will be loaded when the connection is established.  I have never used this option, but it appears that you must specify the full path name of the DLL as in the following example.

XLATEDLL=C:\Windows\System32\MyTrans.DLL;. 

This keyword also works in conjunction with the XLATEOPT, (can also be specified as TranslationOption), which allows you to pass a 32-bit integer value to the specified translation DLL when it is loaded.

Keywords That Can Help in Debugging

The following section deals with keywords that can be helpful when attempting to diagnose performance problems or application errors between the client and the server.

The TRACE keyword allows to you to turn on several of the different facilities for tracing what is happening in a client server job. To select several options, you simply add the values together. The following paragraphs are a description of the valid values for the trace options

The 0 value is the default and indicates that no tracing should occur. A value of 1 enables the internal driver trace.  This option can work in conjunction with the MAXTRACESIZE, MULTTRACEFILES and the TRACEFILENAME keywords to allow you some control over where trace files are placed. You need to be very careful when using this option, because it can cause your application program to hang if you get the options incorrect. See the info on the other keywords for more information. This is not the same as doing an ODBC Driver trace, (which is enabled using the ODBC control panel Tracing tab), but does yield some useful information on communications activity.

Using option 2, you cause an iSeries database monitor job to be started for your connection. For this to work you must have *JOBCTL authority on the user profile used to connect to the iSeries. The database monitor will track all of the activity that you perform into a database monitor file which can be really useful in determining performance problems or spotting potential problems with application scalability. I do not recommend that you use this option, in that if you do not have control of the filename or library where the database monitor information is created. I suggest that you use the QCMDEXC API to issue the STRDBMON command against your ODBC connection. Examples of issuing CL commands using the QCMDEXC API can be found in the resources section of my web site, www.SQLThing.com

Using option 4 will cause you job to be run in DEBUG mode. It appears that the iSeries server job is placed into debug with the option UPDPROD(*YES) which allows your application to modify/access data in production libraries. If you need finer control over the debug option, try issuing the STRDBG command using the QCMDEXC API.

Option 8 causes the iSeries to print your job log once the job ends. Option 16 causes the iSeries enable job tracing. To use the iSeries job tracing your user profile must have *SERVICE authority. Job traces can be really helpful when diagnosing obscure problems with the SQL system.

Here is a sample program which has database monitor, debug and will print it�s job log  by passing option 2 + option 4 + option 8 = 14;

Dim c As New ADODB.Connection
Dim cm As New ADODB.Command
c.Open "Driver=Client Access ODBC Driver (32-bit);" & _
    "SYSTEM=192.168.1.2;UID=X;PWD=SECRET;" & _
    "TRACE=14;"
cm.ActiveConnection = c
cm.CommandText = "insert into imptest.a values ('A')"
cm.Execute
c.Close

When using the TRACE=1 option, you have other keywords which can control where the files are placed, how big they can get, etc. The MAXTRACESIZE keyword sets a maximum size for a trace file. The default value is 0 which means unlimited size. Setting this value to an integer indicates a size in megabytes. The largest value allowed is 1000. In conjunction, you can use the MULTTRACEFILES, (can also be specified as MultipleTraceFiles), to tell the driver whether you want a single trace file, (option 0), or have the driver split the traces into multiple files for each application thread, (option 1 which is the default setting).

The TRACEFILENAME keyword allows you to set the path to where the trace file should be stored. The documentation states that if the MULTTRACEFILES is set to 0 you should specify a full path and file name for the trace file and if MULTTRACEFILES is set to 1 you should only specify a path to a directory. This seems to be incorrect, as if MULLTTRACEFILES is 0 my programs hang if I set TRACEFILENAME to an actually filename. I recommend that you only specify a directory in this setting and leave the MULTTRACEFILES setting at its default value, (1). The following code shows how to generate a communications trace to a directory called TR in the C root. The driver will generate a funky file name that is the concatenation of the driver name along with the application name and other funky text. On my system, the generated file name was cwbodbc-VB6-1600-fd0.log

Dim c As New ADODB.Connection
Dim cm As New ADODB.Command
c.Open "Driver=Client Access ODBC Driver (32-bit);" & _
    "SYSTEM=192.168.1.2;UID=X;PWD=SECRET;" & _
    "TRACE=1;TRACEFILENAME=C:\TR\"
cm.ActiveConnection = c
cm.CommandText = "insert into imptest.a values ('A')"
cm.Execute
c.Close

The QAQQINILIB keyword, (can also be specified as QAQQINILibrary), lets you tell the driver where to look for the QAQQINI file on the iSeries. The QAQQINI file can be used to set a variety of attributes on the iSeries about how the SQL Query optimizer should behave. This can be an awesome capability, in that it can give you very granular control of how the query optimizer works. See the resources section of my web site for more information on QAQQINI and how it can effect your programs execution. IBM says not to use this option, my opinion is that you can have multiple QAQQINI files on your system tuned to give different performance and capabilities based on what your SQL statements need to do. Also, instead of this keyword, I recommend that you specify the QAQQINI file by issuing a CHGQRYA command via the QCMDEXC API. In this manner, you can connect to the iSeries, copy the QAQQINI from the QSYS library to QTEMP and then modify the default settings. After the settings are modified to your needs, you can then issue CHGQRYA and set the QRYOPTLIB parameter to specify QTEMP as you QAQQINI library.

Miscellaneous and Weird Keywords

This section documents a few weird settings that you probably do not want to use.

There is a keyword called DB2SQLSTATES which tells the driver to return DB2 SQL State information rather than the standard ODBC states. Only use this if you are an expert ODBC programmer as you are changing the default behavior for ODBC. This can be useful if you are attempting to write cross DB2 applications, but if you want to do that you should be using DRDA. A value of 0, the default, causes ODBC states to be returned whereas value 1 causes DB2 states to be returned.

The SQDIAGCODE lets you tell the driver to use DB2 Diagnostic Options. Only IBM seems to have information on this keyword. In fact, the documentation says, �use only as directed by your technical support provider�. That sentence reminds me of an advertisement for a prescription drug.

The DEBUG keyword allows you to set one or more options by adding the values together. Some of these options look quite interesting. The first option is 0, which indicates for the driver to do nothing. Option 2 instructs the driver to return SQL_IC_MIXED when you query the SQL_IDENTIFIER_CASE option of SQLGetInfo API.

Option 4 tells the driver to store ALL select statements in the SQL Package file. This is a really cool option, because by default the iSeries will only package SQL statements that contain parameter markers. I had a case where a statement had no markers but was taking a while to optimize because it was joining several legacy files together that had many logical files over each physical. By using this option, I was able to get the statement into my package file so that repeated optimization was avoided.

Option 8 tells the driver to return a zero for SQL_MAX_QUALIFIER_NAME_LEN inquiry to SQLGetInfo.

Option 16 tells the driver to add positioned updates and deletes to the package files. If you are using named cursors or the WHERE CURRENT OF CURSOR clause, this might be of interest to you as the iSeries does not normally package these statements. If you are using ADO, you can not use named cursors so this option will have no effect.

Option 32 causes the driver to convert static cursors to dynamic cursors. I recommend that you stay away from this option as dynamic cursors can lead to more locks and overhead on the iSeries.

Howard�s Connection String Generator

The Connection String Generator is a simple Visual Basic application that is current as of V5R2 connection string options. It uses Microsoft ActiveX Data Objects version 2.7, (MDAC), in order to communicate with the iSeries and test your ODBC connection string. If you do not have MDAC 2.7, you can download it from www.Microsoft.com/data .

The Connection String Generator is delivered as a Windows setup program in a compressed ZIP file. To install the program, simply unzip the program to a temporary directory and then click the Setup.EXE program. Answer each setup dialog and the program will be installed to folder called Connection String Maker in your Program Files directory and will place shortcut called Howards iSeries ODBC CSGEN in your Programs list. To start the program, simply click on the shortcut.

Figure 1 depicts the screen interface of the connection string generator when the program is running.

To use the program, you must first input an IP address or host name of the iSeries you want to connect to and then a User ID and Password to use in the connection. That is the minimum information required to generate a connection string.

Next, set any options that you desire your connection to have. Once you are satisfied with your options, press the Logon button to test the connection with your iSeries.  If the login is successful, you will see a dialog box reporting that you have successfully connected to the iSeries. If the login does not succeed, you will be notified of any error that occurs. If the connection succeeds, the logoff button will be enabled. Press the logoff button to disconnect from your iSeries.

To get a copy of the connection string, press the Put Connection String in Clipboard button to place a copy of the connection string in the Windows clipboard. Once the string is in the clipboard you will see a message box that tells you the connection string is in the Windows Clipboard. Once in the clipboard, you can paste the connection string using CTRL+Insert into any application.

The Make Pretty check box controls whether the connection string is formatted for readability when it is placed into the Windows clipboard. Here is an example connection string that has been formatted with Make Pretty:

Dim ConStr as String
Constr = ""
ConStr = ConStr & "Driver=Client Access ODBC Driver (32-bit);System=192.168.1.1;UID=Z"
ConStr = ConStr & "PWD=X;NAM=0;CONNTYPE=0;DBQ=*USRLIBL;CMT=0;UNICODESQL=0;"
ConStr = ConStr & "XDYDAMIC=0;BLOCKFETCH=1;BLOCKSIZE=32;LAZYCLOSE=1;TRACE=2;"

Note that the string generator will not usually put options into the connection string if you choose the default value for the option.

I hope that you have found this article and the attached utility useful. I spent a lot of time researching these options and testing them on my iSeries and other iSeries computers that I have connections to in order to divine the secrets of the connection string and its varied options.  If you find any other information not covered in this series, or God forbid find something incorrect,    please feel free to contact me with the information so that we can make a complete ODBC connection FAQ. Thanks for reading my stuff.

Bio: Howard F. Arner, Jr. is author of the book iSeries and AS/400 SQL at Work. He also designed SQLThing Enterprise Edition, a query program and stored procedure editor specifically for the AS/400�s unique capabilities and Import Expert 400, a program for quickly importing data from almost any data source to the iSeries. You can purchase a copy of Howard�s book or learn more about his products at www.sqlthing.com. Please send your SQL and iSeries questions to hfarner@sqlthing.com.