Problem:

Customer question:

We have DYNAMICRULES BIND to get implicit qualification of unqualified object references within dynamic SQL statements.

What should I do to be able to use the unqualified function on the mainframe DB2?

What should I do to set the schema name?

Resolution:

What should I do to be able to use the unqualified function on the mainframe DB2?

If you are running your COBOL app dynamic using default Link packages and you want to use a certain schema name to resolve unqualified user-defined functions, you can manipulate the SQL path to accomplish this (see 2.5 SQL path in DB2 v8 doc).  Basically, SQL path is an ordered list of schema names DB2 searches through to resolve user-defined types, functions and stored procedures; the first schema name in this list that contains the unqualified object is used to resolve it.

What should I do to set the schema name?

There are two ways you can alter the SQL path.  You can either do it dynamically by including a SET PATH SQL command in the COBOL program before the function is used (see 5.115 SET PATH in DB2V8 doc); or alternatively, you can rebind the default Link packages with option PATH set to the desired schema name or list of schema names.  The second alternative is a better alternative since it requires no changes to the COBOL source code.  To see a description of PATH option, go to 3.7 BIND and REBIND options.

The following DSN REBIND command rebinds default Link packages named JHTP606 (A through K), setting PATH option to a schema called 'MFIXDB'.

DSN SYSTEM(DSN)                                          

REBIND PACKAGE (NAQACOLL.JHTP606A.(*)) PATH(MFIXDB)      

REBIND PACKAGE (NAQACOLL.JHTP606B.(*)) PATH(MFIXDB)      

REBIND PACKAGE (NAQACOLL.JHTP606C.(*)) PATH(MFIXDB)      

REBIND PACKAGE (NAQACOLL.JHTP606D.(*)) PATH(MFIXDB)      

REBIND PACKAGE (NAQACOLL.JHTP606E.(*)) PATH(MFIXDB)      

REBIND PACKAGE (NAQACOLL.JHTP606F.(*)) PATH(MFIXDB)      

REBIND PACKAGE (NAQACOLL.JHTP606G.(*)) PATH(MFIXDB)      

REBIND PACKAGE (NAQACOLL.JHTP606H.(*)) PATH(MFIXDB)      

REBIND PACKAGE (NAQACOLL.JHTP606I.(*)) PATH(MFIXDB)      

REBIND PACKAGE (NAQACOLL.JHTP606J.(*)) PATH(MFIXDB)      

REBIND PACKAGE (NAQACOLL.JHTP606K.(*)) PATH(MFIXDB)      

END

A couple things to note about the syntax as specified above.  The (*) after the package name means rebind all versions of JHTP606 in collection NAQACOLL; the asterisk can be replaced with any version identifier for the package as listed in column VERSION of catalog table SYSPACKAGE.  Also note that "NAQACOLL" is a Micro Focus collection ID, "JHTP606" is a set of Micro Focus XDB Link default packages.  These values will need to be replaced with appropriate values from the Gateway Profile utility (GPRF40N) configuration for the XDB Link.

Additionally, if using MFE 3.0 you will get the additional packages when you rebind with MFE 3.1.  You probably won't need them immediately so you may use MFE 3.1 with the existing packages when you upgrade.