Steps for running Excel sample (Windows only)

  1. Configure Java security policy

    You will need to grant certain permissions for the Z and I Emulator for Web applet in order to run this sample. You can alter the .java.policy file by using policytool or you can create a new policy file and specify this file in the plug-in Java Runtime Parameters(-Djava.security.policy=PolicyFileName).

    Your new policy file should contain the following and should be located in your local home directory. 
    grant {
    permission java.lang.RuntimePermission
        "accessClassInPackage.sun.jdbc.odbc";
    permission java.util.PropertyPermission 
       "file.encoding", "read";   };

    If you want to change .java.policy (and not set the parameter above in your plug-in), launch the policytool executable in the bin directory of your Java plug-in install and set the permissions specified in the lines above.

  2. Set up the Excel spreadsheet as an ODBC data source
    1. On your Windows machine, go to Settings->Control Panel->Administrative Tools->Data Sources(ODBC).
    2. Click Add...
    3. Select Microsoft Excel Driver (*.xls).
    4. Hit Finish.
    5. Give a data source name of amnu and give any description you desire (or leave it blank).
    6. Use the Select Workbook button to find the spreadsheet provided in this example. Hit OK.
    7. Deselect the Read Only option for this source. You may need to hit an Options>> button to find this option.
    8. Hit OK. You now have the amnu.xls spread sheet available as the ODBC data source amnu.
    .
  3. Create a new Deployment Wizard page that gives the Z and I Emulator for Web client access to the EditDB class.
    1. Start Deployment Wizard.
    2. On the Additional Options page, click "Advanced Options..."
    3. On the Add HTML Parameters panel, add a parameter with Name "AdditionalArchives" and Value "amnu"
  4. Place amnu.jar in your Z and I Emulator for Web publish directory
  5. Open your newly created page in a web browser and start your CICS session.

    USING AMNU:

    The transaction amnu is a small sample database that is provided with CICS. To start amnu, follow these steps:
    1. Log onto CICS
    2. At the CICS prompt, type amnu and type enter.
    The amnu menu comes up in the upper left quadrant of the session window and displays operator instructions.
    To see if there are any records in the database, follow these steps:
    1. In the ENTER TRANSACTION: field, type abrw
    2. Leave the NUMBER field blank.
    3. Type enter.
    The amnu transaction displays the first 4 records. Follow the instructions on the screen to browse the database.
    If the database is empty, you need to add records to it before you run the macro. To add records to the database, follow these steps:
    1. In the ENTER TRANSACTION: field, type aadd
    2. In the NUMBER FIELD, enter a number for the record that you want to add, such as 40.
    3. Type enter.
    4. Follow the instructions on the screen to provide information for the new record.
  6. Load amnu.mac into your session.
    1. If it is not already visible, display the Macro Manager toolbar by selecting View->Macro Manager on the session toolbar.
    2. Click on the Edit current macro properties icon.
    3. Click on the Import... button on the Macro Editor.
    4. Browse to the location of amnu.mac and open it.
    5. Click Save and Exit to save the macro to your current session and close the Macro Editor.
  7. Navigate to the amnu menu screen and hit the Play macro icon.

    You will be prompted to enter a record number. Enter a number of a transaction that you have entered or that you saw when you browsed through the database in step 5. Hit OK. The application screen will display the contents of the record corresponding to the number that you entered. You will be prompted again to ask if you would like to save the record on the screen to your database. The default response is "Y." Hit OK. You will again be prompted to Enter a transaction number. You could continue to enter as many record numbers as you like (and you will be notified if you enter an invalid number), but this time hit Q to quit. Hit OK to close the prompt, and OK again to dismiss the message "Good Bye!" The macro will end, and amnu.xls will open. You should see the contents of the record you just saved inside the spreadsheet.

    Hopefully this sample will get you thinking about the powerful ways in which you can put macros to use for your business. Note that this sample can easily be modified, for example to write to a different kind of database (see below for directions on writing to DB2) or to read from the local database and write to the amnu database. Note that this sample was designed to be short and simple, not a lesson in best practices of Java or macro coding. For example, you may have noticed that we are connecting to and disconnecting from the local database every time we write a record out. This could be avoided by writing "connect" and "disconnect" macros that are linked to the amnu macro such that there is only one connect and one disconnect for each macro play.

If you look inside the Excel macro amnu.mac, you'll see that it is using the driver sun.jdbc.odbc.JdbcOdbcDriver to connect to the Excel spreadsheet. If this class is not in your classpath, the sample will not run properly. This class is included in the Sun Java 2 plug-ins but not in IBM plug-ins.

Parent topic: Copy CICS transaction records into Excel spreadsheet or DB2 database