Problem:

When trying  to debug a program I get the sqlcode -805 when the db2 table is on the mainframe.

This worked on mfe 3.0 all11.

After opening the cursor the sqlcode is -805.  

Resolution:

The customer has forgotten the bind of the XDB Link default DBRM; I am sure it has been a very long time since they initially set up their XDB Link.  He is not binding the SQL from COBOL programs, however, some packages must be bound to execute dynamic SQL on z/OS DB2 with the XDB Link.  

In order to see the existing XDB Link default packages which have already bound on a z/OS DB2 region, the following query will list the pertinent details:

SELECT P.COLLID,

       P.NAME,

       P.OWNER,

       P.QUALIFIER,

       P.ISOLATION,

       P.QUOTE,

       P.COMMA,

       P.DYNAMICRULES,

       S.STMTNO

  FROM SYSIBM.SYSPACKAGE P,

      (SELECT COLLID, NAME, CONTOKEN, MAX(STMTNO) AS STMTNO

         FROM SYSIBM.SYSPACKSTMT

        WHERE CONTOKEN = X'5844424452444131'

        GROUP BY COLLID, NAME, CONTOKEN) AS S

WHERE P.CONTOKEN = X'5844424452444131'

   AND P.COLLID   = S.COLLID

   AND P.NAME     = S.NAME

   AND P.CONTOKEN = S.CONTOKEN

ORDER BY P.DYNAMICRULES,

          P.COLLID,

          P.NAME;

This query targets binds which were performed to support dynamic SQL via the XDB Link.  The query selects only rows with a particular consistency token, X'5844424452444131', which is always used for dynamic SQL.  For a single collection ID/package name pair bound with the XDB Bind utility, between 3 and 11 packages are generated.  If only three packages exist, the bind was done to only support normal cursors and WITH HOLD cursors.  If all 11 packages are present, the bind was done to support SCROLLABLE cursors and ROWSET operations.  The package names are made unique by appending a single character.  Packages suffixed with the letters H, I, J, or K are the ROWSET cursor packages.  Packages suffixed with the letter D, E, F, or G are SCROLLABLE CURSOR packages.  Packages suffixed with A or B are for normal cursors and WITH HOLD cursors.  Packages suffixed with the letter C are special XDB Link operation statements.

Customers can use the query to identify how previous XDB Link default DBRMs have been bound on their DB2 region.  They may have bound multiple version of the default DBRM.  If so, they will see multiple combinations of the collection ID/package name combination.  When I say collection ID/package name combination, I am referring to the package name minus the character that has been appended to the package name to make the individual XDB Link package names unique.

The last column the query produces, S.STMTNO, will tell if they changed the default for the number of "Dynamic Sections" when the Default DBRM was last created.  The default value is 32 and that should show up for all packages except the packages that are suffixed with the letter 'C'.  If the query returns a value higher than 32, they will need to use that value when generating the new Default DBRM prior to binding.

The most important option of the bind process will be the DYNAMICRULES column.  If the value on that column is 'B', the that means the packages have been bound with DYNAMICRULES(BIND).  This means that many features of the SQL (for instance, the default qualifier) are established during the bind operation.  When DYNAMICRULES(BIND) is in effect, the Bind options will have to be specified during the bind of the default qualifier.  Those bind options are established using the "Bind" button of the "Modify Options" group in the "Bind Package" dialog.

That button brings up the "Bind Options" dialog.  This dialog is where the Dynamic Rules settings is established as well as the "Package Owner" (authority to access tables) and "Default Qualifier" (used to qualify one part table names).  If the packages were bound with DYNAMICRULES(RUN), then values for "Package Owner" and "Default Qualifier" will have no effect on the SQL since these attributes (authority and qualification) will be determined at run time.

There is also a "Format" button in the "Modify Options" group in the "Bind Package" dialog.  The "Format Options" dialog that pops up is where "String Delimiter", "Decimal Delimiter", and Date/Time Formats are established.  Unfortunately, the values used during the bind for the Date/Time formats are not shown in the metadata so they will have to supply the correct values.

More information on the values returned by the query I have provided may be found in the DB2 V9.1 for z/OS SQL Reference in the chapter "Appendix G. DB2 catalog tables".  The section for the " SYSIBM.SYSPACKAGE table" contains the values used by z/OS DB2 for the attributes of the bind.