Building Reports in Excel using IBM PCOMM Automation API

Introduction
​IBM Personal Communications provides an emulator interface to communicate with IBM Mainframe/AS400/VT sessions. This is used for reading & updating host data and interfacing host with other applications. Performing manual tasks on emulator screen increases in transaction and becomes repetitive, this over a long term would have issues:
​

-          Inefficient
-          Redundant and hence error prone
-          Time consuming and thus expensive

Consider a business scenario where bank employees, on a regular basis, build reports on their customer’s financial information by querying their credit limit, balance due etc. This report building operation involves below set of activities:
a.     Execute macros on the emulator screen to perform certain tasks.
b.     Query database to read customers’ credit and balance amount.
c.     Generate the final report by exporting query results to a text or CSV file.

Performing manual operations to generate such reports is inefficient and prone to errors. This is where automation, in the form of API’s can help.

Exploring IBM PCOMM Automation API
In this post we will look at a sample application that uses PCOMM Automation API’s in a Microsoft Excel sheet. This application can start multiple PCOMM sessions or connect to the existing ones. On a selected session it can execute PCOMM macros (.mac) and extract information from the emulator screen to generate a user report.

This application is divided into two sections:
A.      Session Manager:
                a.       Use ‘Connect Session’ dropdown list to start a new emulator session or connect                             to an existing one.
                b.       Use ‘Stop Session’ to gracefully terminate any active session which was started                             using this application.
B.      Generate Report:
                a.       Use ‘Select Session’ to connect to a session started using above steps.
                b.       Select macro (.mac) from ‘Table Definition’ list which will log into the Host                                        session  and move to the table definition page.
                c.        Select macro (.mac) from ‘Query Window’ list to move to table query page.
                d.        Click on ‘Generate report’ to collect Customers information from the Emulator                                  page.
Session Manager
​A.      Starting PCOMM Session: The PCOMM session can be launched by couple of methods:
    a.         Using PCSAPI, ‘pcsStartSession’:

   b.         Using HACL API, ‘autECLConnMgr.StartConnection’:

Once the session is started, use PCOMM HACL API to connect to it:

B.     Stopping PCOMM Session: The session started above can be stopped using either of the              methods below:
         a.      Using PCSAPI, ‘pcsStopSession’:

         b.      Using HACL API, ‘autECLConnMgr.StopConnection’:

C.      PCOMM Session Management: In this application, user can select any session to generate          the report. To manage these sessions, we should associate each session with its PCOMM            connection objects. To do so, use Dictionary objects to store session name as a key and                session objects as its value.

In an array store the newly created autECLPS and autECLOIA objects. As shown below, add session name (A-Z, a-z) as a Key to the dictionary and the array as its value:

Once user selects a session to generate a report, get the PCOMM Session objects from the dictionary ‘SessionObjsDict’ by providing ‘sessionName’ as a key (described in section below).

​Generate Report
A.      In this section, user generates a report by running macros on the Host and performs                    query  on the host table using Excel User Form. Once user selects a session under ‘Select              Session’, application retrieves the session objects from the ‘SessionObjsDict’ dictionary:

B.      To execute any macro (.mac) file in the Host presentation space, use autECLPS API                      ‘StartMacro’:

C.      User Form ‘Generate Report’ displays the column list of a table which has been read from            a table inside the Emulator screen. In this User Form, user can select the column which                they want to view in the final report and can add filters on the columns ‘Balance Due’                  (BALDUE) or ‘Credit Due’ (CDTDUE).

By using PCOMM HACL API’s, the data is read from the Emulator presentation space and is stored in the Excel sheet.

For more information on PCOMM Automation API, please refer to PCOMM documentation.

Below is link to a video that shows a demo of this application.
https://www.youtube.com/watch?v=i30L2x-8Q5E&feature=youtu.be​

Contact:
Please email zServices@hcl.com  or HCL-HI-LabServices@hcl.com to get more information on Services offerings.

Badal Burathoki
Technical Architect, Lab Services, IBM HACP & HATS
badal.b@hcl.com