Problem:

A customer noticed that SQLWizard does not commit data until an actual COMMIT command is submitted (or SQLWizard is closed), and wanted to know why SQLWizard does not work like SPUFI.

Resolution:

SPUFI is not an interactive utility.  SPUFI is a batch program that paired with ISPF panels allows users to edit an SQL script and then submit it to DB2 for processing.  The ISPF SPUFI panel has an "AutoCommit" entry.  However, that ISPF panel clearly indicates that this is a "Commit after successful run".  If you look at the " CURRENT SPUFI DEFAULTS" panel, you will notice an entry there labeled "MAX SELECT LINES".  This entry controls the maximum number of tuples for SPUFI to fetch from a query before abandoning the query.  SPUFI runs the SQL script to completion and writes the results fetched back (not necessarily complete result sets) to a data set to be viewed in the ISPF browser.  If the SPUFI panel specifies AUTOCOMMIT NO, another panel is then invoked asking if the user to choose either COMMIT, ROLLBACK, or DEFER.  This allows the user to have SPUFI send a COMMIT, a ROLLBACK, or in the case of DEFER, return to the SPUFI panel to introduce changes to the script.  If DEFER is specified, you can not leave the SPUFI session (see IBM message DSNE396A).

SQLWizard is an interactive utility.  When users open an SQL script window within SQLWizard, they have the option to execute the first SQL statement, the current SQL statement, or all SQL statements.  SQLWizard, unlike SPUFI, does not place an upper limit on the number of tuples to be fetched from a result set.  Rather, it limits the initial set of rows fetched back into a result set window until the user requests to see rows it has not already fetched back (typically done by scrolling down through the result set).  You can see this behavior by executing a query with a large result set like the following:

SELECT *

FROM SYSIBM.SYSTABLES T, SYSIBM.SYSCOLUMNS C

WHERE T.NAME = C.TBNAME

AND T.CREATOR = C.TBCREATOR

FOR FETCH ONLY;

When executed against DB2 via the XDB Link, notice how the first rows of the result set are returned rapidly.  Once the result set window is displayed, scroll down to the bottom of the result set and note how long it takes to display the bottom of the result set.  This delay is the result of SQLWizard fetching the remaining rows back from the result set.

Consider the situation where a user has three or four queries in an SQLWizard SQL window and has selected the option to execute all of the queries in the script.  The result sets will all be displayed simultaneously.  If the user has a COMMIT at the end of the script, when any of the result set windows is scrolled to a point where SQLWizard will need to fetch additional tuples, the user will be presented with a -517 DB2 error stating that the CURSOR does identify a prepared statement.  This occurs because the COMMIT statement sent to DB2 has closed all of the open result sets.

Now that I have drawn the distinctions between SPUFI and SQLWizard, I would like to explain what XDB AUTOCOMMIT actually is.  It is not a setting that indicates that XDB client software like SQLWizard or Migrate should send a COMMIT after other operations.  Rather, it is a setting within the XDB SQL engine environment of the users.  When it is set, the XDB SQL engines perform a special operation internally whenever updates are made to the XDB location.  Since this operation is internal to the XDB SQL engine, the engine puts the commit operation into the XDB location log files and releases locks held but it does not perform the additional tasks of a standard COMMIT such as closing open cursors.  IBM does not have this functionality in DB2.  They have valid reasons for not including such a functionality.  However, without that functionality I have no way to address a situation described as "the AutoCommit is ignored for DB/2 tables accessed via XDB Link".

I believe your client has two options.  The first option is to run a batch utility similar to SPUFI.  This could be accomplished by using the DSNTEP2 FDS in MFE 3.1.  A similar approach would be to request an enhancement that a SPUFI analog be added to MFE 3.1.  The second option is to take care of the problem through peer pressure.  We experience these same problems internally at Micro Focus.  When z/OS DB2 times out a thread due to lock contention, a DSNT376I message is written to the logs.  Here's an example of one that occurred on Wednesday where a Micro Focus employee in the Newbury Supportline group was locking out an XDB developer:

DSNT376I  @D5 PLAN=DISTSERV WITH 526                             

        CORRELATION-ID=CROCKACA0045                              

        CONNECTION-ID=SERVER                                    

        LUW-ID=0A180B26.0A6F.07D803130C2D=1673                   

        THREAD-INFO=MFIATC:*:*:*                                 

        IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=DISTSERV

WITH                                                            

        CORRELATION-ID=CNWBJOHN0008                              

        CONNECTION-ID=SERVER                                    

        LUW-ID=0A780B19.064F.07D80313100C=1656                   

        THREAD-INFO=MFIJPT:*:*:*                                 

        ON MEMBER DB8R                                           

Please note that this message clearly indicates user MFIATC on XDB workstation CROCKACA0045 timed out because a lock held by user MFIJPT on workstation CNWBJOHN0008.  Because the XDB components have clearly registered the workstation, we can identify the XDB client machine in use by user MFIJPT.  That is not the machine running the XDB Link; that is the machine running SQLWizard.  You can find that workstation name in the XDB Client options "Connect" tab in the "Client Node Name" text box:

Since the XDB workstation name was based upon the Windows Machine name, and the Micro Focus policy is to include something indicative of location and employee in that machine name, I know that this is a user named John in the Newbury office:  NWBJOHN.  I could also contact our system programmer and find out who is user MFIJPT on the Micro Focus mainframe.  Fortunately, I knew exactly who this person was without having to do any further investigation.  I guarantee you that if I have any further lock conflicts holding back XDB developers caused by SupportLine, I will call user MFIJPT personally and inform them that I would like them to begin issuing COMMIT statements prior to leaving their desk or getting interrupted by a pressing client call.  After a phone call or two, I believe user MFIJPT will understand my view and no longer be an impediment to XDB development.  If your client implements a procedure where a DBA or z/OS systems programmer is notified of lock conflicts on the z/OS DB2 region, the same peer pressure will rapidly solve the problem for your client.