Accessing Mainframe Tables with Stored Procedures under SQL Option

You can set up a special location to access mainframe tables from a stored procedure. Since the stored procedure must run under the XDB engine, the current location must be an SQL Option location. Access to DB2 tables and views is provided by using aliases.

To activate this functionality, the STProcRemoteAccess variable must be set to one in the server section of the xdb.ini file which, in Windows environments, is in the %ProgramData%\Micro Focus\Enterprise Developer\mfsql\cfg directory. The following is an example of setting the STProcRemoteAccess variable (applies to Windows environments only):

[CLIENT]
XDBCFG=c:\mfuser\config\
[SERVER]
XDBSERVE=myserver
XSRVCFG=c:\mfuser\config
StProcLoadLib=c:\mfuser\projects\MyProject\LOADLIB;c:\mfuser\projects\MyProject2\LOADLIB
StProcAnimate=yes
STProcRemoteAccess=1
.
.
. 

You can easily create aliases for mainframe tables using the CREATE ALIAS SQL command. If you can use a three-part name to select from a table on the mainframe, the CREATE ALIAS command can be used to alias that table.

For instance, if the following command returns rows:

select * from MFLOCATION.MYAUTHID.MYTABLE;

an alias can be created in the MYLOCAL location using the following command:

create alias MYAUTHID.MYTABLE for MFLOCATION.MYAUTHID.MYTABLE; 

CREATE ALIAS statements can be generated using SQL SELECT statements and the remote locations metadata. For instance, the following query will generate CREATE ALIAS syntax for all tables in a DB2 location that don't have a creator of SYSIBM:

SELECT 'CREATE ALIAS '
                  CONCAT
                  STRIP(CREATOR, TRAILING, ' ') CONCAT '.' CONCAT NAME
                  CONCAT
                  ' FOR ' 
                  CONCAT 
                  STRIP(CURRENT SERVER, TRAILING, ' ') CONCAT '.' CONCAT 
                  STRIP(CREATOR, TRAILING, ' ') CONCAT '.' CONCAT NAME
                  CONCAT
                  ';'
FROM SYSIBM.SYSTABLES
WHERE CREATOR NOT IN ('SYSIBM')
ORDER BY 1
FOR FETCH ONLY;

If the above query is run under SPUFI, you must take care to set up the SPUFI defaults appropriately - you need to ensure that settings such as MAX SELECT LINES in SPUFI are set to enable all the rows to be returned. Using SPUFI, the output data set will have output that looks as follows:

---------+---------+---------+---------+---------+---------+---------+---------
CREATE ALIAS MYAUTHID.PROC_TEST_TAB_1 FOR CSIMVSDB2.MYAUTHID.PROC_TEST_TAB_1;
CREATE ALIAS MYAUTHID.PROC_TEST_TAB_2 FOR CSIMVSDB2.MYAUTHID.PROC_TEST_TAB_2;
CREATE ALIAS MYAUTHID.PROC_TEST_TAB_3 FOR CSIMVSDB2.MYAUTHID.PROC_TEST_TAB_3;

The query can be adjusted to retrieve the tables necessary by using sub-queries or joins to limit the data retrieved. The following is an example of generating CREATE ALIAS commands for all tables and views within a particular DB2 database. Note that this query will not retrieve tables in other databases that are referenced by the views.

SELECT 'CREATE ALIAS ' 
       CONCAT
       STRIP(CREATOR, TRAILING, ' ') CONCAT '.' CONCAT NAME
       CONCAT
       ' FOR ' 
       CONCAT 
       STRIP(CURRENT SERVER, TRAILING, ' ') CONCAT '.' CONCAT 
       STRIP(CREATOR, TRAILING, ' ') CONCAT '.' CONCAT NAME
       CONCAT
       '; -- ALIAS OF TABLE'
  FROM SYSIBM.SYSTABLES
 WHERE DBNAME = 'MYDB2DB'
   AND TYPE = 'T'
 UNION
SELECT 'CREATE ALIAS ' 
       CONCAT
       STRIP(SVDP.DCREATOR, TRAILING, ' ') CONCAT '.' CONCAT SVDP.DNAME
       CONCAT
       ' FOR ' 
       CONCAT 
       STRIP(CURRENT SERVER, TRAILING, ' ') CONCAT '.' CONCAT 
       STRIP(SVDP.DCREATOR, TRAILING, ' ') CONCAT '.' CONCAT SVDP.DNAME
       CONCAT
       '; -- ALIAS OF VIEW'
  FROM SYSIBM.SYSTABLES  STBL,
       SYSIBM.SYSVIEWDEP SVDP
 WHERE STBL.DBNAME = 'MYDB2DB'
   AND STBL.TYPE = 'T'
   AND STBL.CREATOR = SVDP.BCREATOR
   AND STBL.NAME  = SVDP.BNAME
 ORDER BY 1 
   FOR FETCH ONLY
;

Because the stored procedure is running under the SQL Option engine and DB2 is being used only for the retrieval of data, certain operations must still be set up under the SQL Option engine. In particular, SET CURRENT PACKAGE SET commands must be set up under SQL Option. SET CURRENT PACKAGE SET commands will not be passed to the mainframe DB2 region since the identification of data entities must take place under the SQL Option engine. Additionally, SYNONYMS must be created on the SQL Option location if your DB2 processing uses SYNONYMS.