Performance issues with VB ADO and the AS/400

We are running a Visual Basic 6.0 program that uses ActiveX Data Objects (ADOs) to access an AS/400 running V4R4. We first pass some information to a legacy RPG program on the AS/400 that populates 12 files with information about the selected user. Back at the Visual Basic program, we then select records from the 12 files with SQL statements. With some SQL statements, we are creating joins over two different files.

Whenever we create a join, a lock remains in the QZDASOINIT job on the AS/400 as long as the connection from the PC is active. Whenever the program is run again, it creates another lock. It retains these locks until the connection is stopped. We are not ending the connection, because of the extra time it takes to make the first connection in a QZDASOINIT job. Is there some statement we can put in on the VB side that will release the locks?

We would also appreciate it if the Wizard could recommend a good source of information on performance issues with VB ADO and the AS/400. --Much Ado about ADO

Dear Ado, There is a setting on the ODBC provider called Enable Lazy Close. If this is checked, files will remain open even after the SQL statement is finished. This helps with files that are reused very frequently. However, it also means that you have shared locks lying around in a puddle. In addition, if Enable Lazy Close is checked, the locks will not disappear even after a COMMIT or ROLLBACK operation is performed against the database.

Another thing that can lead to locks is the isolation level in which your program is running. This can be controlled from the ODBC Data Sources program available from the control panel (if you are using an ODBC connection), but it can also be overridden with additional arguments that you can provide when you open your connection to DB2/400 (whether you are using an OLE DB Provider or an ODBC Connection via the MSDASQL OLE DB Provider).

In addition, an individual SQL statement can assume another isolation level due based on additional SQL grammar. For example, suppose your QZDASOINIT job is in *NONE isolation level. Executing the following statement will cause locks to be thrown in the *RS isolation level even though your job is in *NONE:

SELECT * FROM MYFILE JOIN MYOTHERFILE WITH RS

The WITH RS clause indicates that this statement should run in read stability isolation, whether or not that is the current isolation level of the job.

To further complicate matters, there is an additional clause that can be appended to the SQL statement:

SELECT * FROM MYFILE WITH RS KEEP LOCKS

The KEEP LOCKS informs SQL to keep any record locks thrown beyond commitment boundaries. Therefore, even if you issue a COMMIT or ROLLBACK operation, any records locked by this statement will continue to be locked. You should peruse the ODBC and OLE DB documentation for the different connection properties that can affect locking, as by changing the properties at the connection level, you can achieve very fine granular control of locking within your application. Using the properties available on the connection object in addition to using the ability to control isolation level at the statement, you can design swift and robust client/server applications that do not suffer from lock contention.

The Wizard recommends Howard F. Arner, Jr. as a good source of ADO programming knowledge. Howard consults with companies all over the country on ADO/SQL performance issues. Be sure to read his article "My Own Private QAQQINI" in the June 2001 issue of Midrange Computing. It deals with how to reduce the number of ODPs that the iSeries and AS/400 leave strewn about.

You may also want to read Chapter 5: Cursors, Transactions, Journals and Locking in Howard's book, iSeries and AS/400 SQL at Work. It contains a lot of information on locks and such, and has a section on performance.

You can email Howard at harner@sqlthing.com. He is working on a new set of Web pages for his site, www.sqlthing.com, addressing common performance problems.

[report a broken link by clicking here]