Previous Topic Next topic Print topic


Tutorial: DSNREXX

This tutorial shows how to execute DB2 statements from within a DSNREXX procedure. This is done by creating a JCL project and then associating it with an XA resource, enabling you to run a DSNREXX procedure using sample JCL provided with Enterprise Developer.
Note: You need to ensure that you consistently use 32-bit versions of the tools and utilities as you proceed through this tutorial.

Prerequisites

To complete this tutorial, you need the following software in addition to Enterprise Developer:

  • IBM's DB2 Database for Linux, UNIX, and Windows (LUW). See Additional Software Requirements on Windows for more information.

Creating a DB2 LUW sample database

If you do not already have a sample database you will need to create one. You can create a sample database by running the db2sampl utility. The utility is part of the DB2 installation:
  1. Start a command prompt as an administrator.
  2. Type db2sampl -sql
  3. To verify that the database can be connected to, start a DB2 Command Window.
  4. Type DB2 CONNECT TO SAMPLE USER user-id USING password
    You are presented with database connection information, for example:
       Database Connection Information
    
     Database server        = DB2/NT64 11.1.0
     SQL authorization ID   = user-id
     Local database alias   = SAMPLE
Note: See the DB2 LUW documentation for more information on how to create or catalog the SAMPLE database. Click Start, and type DB2 Information Center. Choose DB2 Information Center, and search for "Create Sample database" or "Catalog database" as required.

Catalog the sample database as an ODBC data source

If your sample database has not been cataloged as an ODBC data source, then perform the following steps:

  1. Click Start or use the shortcut keys Ctrl+Esc. In the search field, type ODBC.
  2. In the results list, select ODBC Data Source Administrator (32-bit) or ODBC Data Sources (32-bit), depending on your version of Windows.

    This opens the 32-bit version of the ODBC Data Source Administrator tool.

  3. Click System DSN tab.
  4. Click Add.

    This opens the Create New Data Source dialog box.

  5. Click IBM DB2 ODBC DRIVER.
  6. Click Finish.

    This opens the ODBC IBM DB2 Driver - Add dialog box.

  7. In the Data source name field, type SAMPLE
  8. In the Database alias list, select the SAMPLE database.
    • Optionally, in the Description field, type a description for your data source.
  9. Click OK, and then click OK.

Create an ODBC XA switch module

If you have not already created the ODBC XA switch module, perform the following steps:
Note: To build the ODBC XA switch module, you must have the Windows Software Development Kit (SDK) installed for your version of Windows. Before building the switch module, ensure that your LIB environment variable contains the path to your Windows SDK LIB directory.
  1. Start an Enterprise Developer command prompt (32-bit) as administrator.
  2. Change to the %ProgramFiles(x86)%\Micro Focus\Enterprise Developer\src\enterpriseserver\xa directory.
  3. Enter build odbc
    Note: Make a note of the switch module name created, it is used in subsequent steps.

Create a working directory for your project

  1. Create the working directory on your machine, for example C:\Users\Public\REXXSAMPLE
  2. Create a REXX subdirectory.

Create an MSS project

  1. Start Enterprise Developer.
  2. Click File > New > Project.
  3. Under Installed > Templates, expand COBOL, and then click Native.
  4. Click Mainframe Subsystem Application.
  5. In the Name field, type REXXJCL
  6. In the Location field, specify the working directory, for example C:\Users\Public\REXXSAMPLE
  7. Ensure that Create directory for solution is checked.
  8. Click OK.

Add a JCL file to run DSNREXX

You can use the IKJEFT01 or IRXJCL utility to run REXX exec statements. The following JCL jobstream runs the REXX script named Test1:

  1. In Solution Explorer, right-click your project.
  2. Click Add > New Item.

    This opens the Add New Item - REXXJCL dialog box.

  3. Expand Installed > COBOL > Native > Mainframe Subsystem.
  4. Click JCL File.
  5. In the Name field, type Test1.jcl.
  6. Click Add.
  7. In the Test1.jcl file, paste the following code:
    //REXXTEST JOB 'TEST DSNREXX SUPPORT',CLASS=A,MSGCLASS=A 
    //*
    //* Test the REXX engine using IKJEFT01
    //*
    //STEP10 EXEC PGM=IKJEFT01
    //SYSEXEC  DD DSN=USER.TEST.EXEC,DISP=SHR
    //SYSTSIN  DD *
    Test1
    //SYSPRINT DD SYSOUT=*
    //SYSTSPRT DD SYSOUT=*
    //SYSOUT   DD SYSOUT=*
    //*
    //* Test the REXX engine using IRXJCL
    //*
    //STEP20   EXEC PGM=IRXJCL,PARM='Test1'
    //SYSEXEC   DD DSN=USER.TEST.EXEC,DISP=SHR
    //SYSTSPRT  DD SYSOUT=*
    //SYSTSIN   DD DUMMY
    
  8. Click File > Save Test1.jcl.

Create an MSS-enabled Enterprise Server

Now you want to create an enterprise server region with Mainframe Subsystem (MSS) for JCL use:

  1. In Solution Explorer, right-click your project.
  2. Click Enterprise Server > New Enterprise Server.
    This opens the Create New Enterprise Server dialog box.
    Note: If you are prompted with an Enterprise Server Sign On dialog box, click OK without specifying any sign-on details. This is a standard security dialog box.
  3. In the Name field, type REXX32
  4. Click Browse next to the Template field.
  5. Click JCLTemplate.
  6. Click Open.
  7. Click OK.

Add an XA resource for REXX procedures pointing to the DB2 LUW SAMPLE database

Now that the enterprise server region is created, you need to set up an XA resource:

  1. In Server Explorer, right-click Micro Focus Servers.
  2. Click Administration.
  3. Click Edit next to the REXX32 region name.
  4. Click Server > Properties > XA Resources.
  5. Click Add.
  6. In the ID field, type DB2T
  7. In the Name field, type REXXPROC
  8. In the Module field, type the location and name of the ODBC XA switch module created in the earlier steps, for example:

    C:\Program Files (x86)\Micro Focus\Enterprise Developer\src\enterpriseserver\xa\ESODBCXA.dll

    Note: When specifying the location and name of the ODBC XA switch module, do not enter an environment variable such as %ProgramFiles(x86)%. Ensure that you use an expanded path.
  9. In the Open string field, type DSN=SAMPLE

    Where SAMPLE is the Data source name specified in the ODBC Data Source Administrator.

    • You may need to add the ID and PASSWORD if connecting to a remote database. See Generic one-phase commit for ODBC xa_open string for more information.
  10. Click Add.

See To define an XA resource (XAR) for an enterprise server region for more information.

Start the enterprise server region

  1. In Server Explorer, expand Micro Focus Servers and then localhost.
  2. Right-click REXX32, and then click Start.
    Note: The Enterprise Server Sign On dialog box might prompt you to provide connection details for the server. This is a standard security dialog box. Click OK without specifying any sign-on details. Also, if prompted to disable password recovery, click No.
    • If you receive a Windows Security Alert for the MF Communications Process, click Allow access.

Catalog the directories that have the REXX procedures

You need to specify the catalog file that holds details of the data sets required by the JCL job that run in an enterprise server region. To do this you need to create a Dynamic PDS. See Dynamic PDSs for more information.

  1. In Server Explorer, right-click REXX32 under localhost.
  2. Click Show Catalog.
  3. Click List.
  4. Click New.
  5. In the DS Name field, type USER.TEST.EXEC
  6. In the Physical File field, type C:\Users\Public\REXXSAMPLE\REXX
  7. From the DS Org list, select PO.
  8. From the RECFM list, select LSEQ.
  9. Check Dynamic PDS.
  10. In the PDS Exts field, type rex
  11. Click Apply.

Copy the REXX script from the samples directory

  1. Copy the Test1.rex file, from the %PUBLIC%\Documents\Micro Focus\Enterprise Developer\Samples\Mainframe\SQL\OpenESQL\DSNREXX\Test1\Scripts sample directory to the C:\Users\Public\REXXSAMPLE\REXX directory.

To run the JCL job

  1. In Solution Explorer, expand the project to show the Test1.jcl file.
  2. Right-click the Test1.jcl file, and then click Submit JCL.

View the spool output

You can check the result of submitting the JCL job by viewing the spool:

  1. In Server Explorer, expand Micro Focus Servers > localhost.
  2. Right-click REXX32.
  3. Click Show Spool.
  4. Click REXXTEST.

    This provides more information on the job submitted.

  5. Click SYSTSPRT for either of the steps to see more information.
    The following message or similar is displayed for STEP10:
                                                                                                                               
    DROPPING REXTEST1 TABLE                                                                                                               
    INSERTING DATA                                                                                                                        
    PREPARING S1                                                                                                                          
    FETCHING DATA                                                                                                                         
    TEST PASSED  -33329.3333                                                                                                              
    INSERTING DATA USING INSQLDA                                                                                                          
    PREPARING S3                                                                                                                          
    FETCHING DATA USING OUTSQLDA                                                                                                          
    TEST PASSED  -33329.3333
Previous Topic Next topic Print topic