QPE Sample Table

The example code below uses the Query Process Engine (QPE) to create the DB2 sample table "DSN81110.EMP" used throughout the examples in this guide. If you change the table name ensure all occurrences of this table name are changed in your examples. See initQPE to learn how to use the Query Process Engine (QPE).

Example Explained

To run the example; select, copy, and paste the content into a new HTML file. Then change Lines 70 to 72 to suit your sites requirements using the explanations below to assist.

Lines 1 to 19 and Lines 110 to 112 contain the HTML code required to run the example as a standalone web page.
Lines 21 to 65 - Initializes the variable sqlText  with the QPE SQL rules to process.
Lines 23 to 38 - Contains the SQL rule to create the sample table.
Line 40 - Contains the SQL rule to grant the necessary table privileges. Authorization is given to PUBLIC in order to make the sample easier to run.
Lines 42 to 61 - Contains the SQL rules to populate the new DB2 table.
Line 63 - Contains the SQL rule to commit the table updates.
Line 69 - Creates the API object to work with. A new MFCAgentRequest object instance is created and assigned to variable obj1.
Line 70 - Sets your z/OS TSO login credentials. See setLogin.
Line 71 - Sets the web service URL. See setURL.
Line 72 - Sets the z/OS DB2 subsystem identifier you wish to interrogate. See setDB2SSID.

Initialize QPE
Line 74 - Executes the API initQPE method with arguments sqlText, and []. This loads to QPE; the SQL rules from the sqlText  variable, and NO SQL callback definitions.

Execute QPE
Line 77 - The create function:   (triggered by "Run" HTML button element in Line 10)
Lines 79 to 80 - Initializes the array variable tables1  with the table list criteria argument to pass to the executeQPE method in Line 84.
Line 82 - Initializes the variable arg1  with the criteria argument to pass to the executeQPE method in Line 84.
Line 84 - Executes the API executeQPE method with arguments tables1, [], and arg1. This performs the following:
   1. Steps through each QPE SQL rule and translates the <%TABLE1%> field to the value in the corresponding tables1  array element.
   2. Performs no <%TABLE2%> translation because the tables2  parameter array is empty.
   3. Translates all QPE SQL rule <%ARG1%> fields to the value in the arg1  variable.
   4. Executes the translated SQL statements in an SQL pipe.

Line 88 - The qpeSuccess function:   (executed after the final QPE API call is complete)
Lines 89 to 102 - Updates the web page "container" element with data extracted from the response array returned from QPE.

Line 106 - The qpeError function:   (executed if QPE ends due to an API call error)
Line 107 - Converts the response data returned from QPE into a raw string and populates the web page "container" element.

Example

<!DOCTYPE html>
<!--
    Mainframe Cloud Pty Ltd MAINSPACE WEB APP EXAMPLE
-->
<html>
  <body>
    <!-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -->
    <!-- Web page elements                                               -->
    <!-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -->
    <INPUT type=button value="Run" onclick="create();" title="Create Sample Table.">
    <div id="container"></div>
    <!-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -->
    <!-- MainSpace API JS library                                        -->
    <!-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -->
    <script type="text/javascript" src="https://www.mainframecloudplatform.com/applibs/mainspace-webmf.js"></script>
    <!-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -->
    <!-- JavaScript control code section                                 -->
    <!-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -->
    <script>
     
      var sqlText = `
     
        CREATE TABLE <%TABLE1%> 
            (EMPNO      CHAR(6)     NOT NULL, 
             FIRSTNME   VARCHAR(12) NOT NULL, 
             MIDINIT    CHAR(1)     NOT NULL, 
             LASTNAME   VARCHAR(15) NOT NULL, 
             WORKDEPT   CHAR(3), 
             PHONENO    CHAR(4), 
             HIREDATE   DATE, 
             JOB        CHAR(8), 
             EDLEVEL    SMALLINT, 
             SEX        CHAR(1), 
             BIRTHDATE  DATE, 
             SALARY     DECIMAL(9,2), 
             BONUS      DECIMAL(9,2), 
             COMM       DECIMAL(9,2), 
             PRIMARY KEY (EMPNO));
     
        GRANT ALL ON <%TABLE1%> TO PUBLIC;
     
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000010', 'CHRISTINE', 'I', 'HAAS'     , 'A00', '3978', '1965-01-01', 'ANALYST' , 18, 'F', '1933-08-14', 52750, 1000, 4220);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000020', 'MICHAEL'  , 'D', 'THOMPSON' , 'B01', '3476', '1973-10-10', 'MANAGER' , 18, 'M', '1948-02-02', 41520, 800 , 3300);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000030', 'SALLY'    , 'A', 'KWAN'     , 'C01', '4738', '1975-04-05', 'MANAGER' , 20, 'F', '1941-05-11', 38250, 800 , 3060);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000050', 'JOHN'     , 'B', 'GEYER'    , 'E01', '6789', '1949-08-17', 'MANAGER' , 16, 'M', '1925-09-15', 40175, 800 , 3214);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000060', 'IRVING'   , 'F', 'STERN'    , 'D11', '6423', '1973-09-14', 'MANAGER' , 16, 'M', '1945-07-07', 32250, 600 , 2580);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000070', 'EVA'      , 'D', 'PULASKI'  , 'D21', '7831', '1980-09-30', 'MANAGER' , 16, 'F', '1953-05-26', 36170, 700 , 2893);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000090', 'EILEEN'   , 'W', 'HENDERSON', 'E11', '5498', '1970-08-15', 'MANAGER' , 16, 'F', '1941-05-15', 29750, 600 , 2380);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000100', 'THEODORE' , 'Q', 'SPENSER'  , 'E21', '0972', '1980-06-19', 'MANAGER' , 14, 'M', '1956-12-18', 26150, 500 , 2092);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000110', 'VINCENZO' , 'G', 'LUCCHESI' , 'A00', '3490', '1958-05-16', 'SALESREP', 19, 'M', '1929-11-05', 46500, 900 , 3720);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000130', 'DOLORES'  , 'M', 'QUINTANA' , 'C01', '4578', '1971-07-28', 'ANALYST' , 16, 'F', '1925-09-15', 23800, 500 , 1904);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000140', 'HEATHER'  , 'A', 'NICHOLLS' , 'C01', '1793', '1976-12-15', 'ANALYST' , 18, 'F', '1946-01-19', 28420, 600 , 2274);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000150', 'BRUCE'    , '' , 'ADAMSON'  , 'D11', '4510', '1972-02-12', 'DESIGNER', 16, 'M', '1947-05-17', 25280, 500 , 2022);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000160', 'ELIZABETH', 'R', 'PIANKA'   , 'D11', '3782', '1977-10-11', 'DESIGNER', 17, 'F', '1955-04-12', 22250, 400 , 1780);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000170', 'MASATOSHI', 'J', 'YOSHIMURA', 'D11', '2890', '1978-09-15', 'DESIGNER', 16, 'M', '1951-01-05', 24680, 500 , 1974);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000180', 'MARILYN'  , 'S', 'SCOUTTEN' , 'D11', '1682', '1973-07-07', 'DESIGNER', 17, 'F', '1949-02-21', 21340, 500 , 1707);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000190', 'JAMES'    , 'H', 'WALKER'   , 'D11', '2986', '1974-07-26', 'DESIGNER', 16, 'M', '1952-06-25', 20450, 400 , 1636);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000200', 'DAVID'    , '' , 'BROWN'    , 'D11', '4501', '1966-03-03', 'DESIGNER', 16, 'M', '1941-05-29', 27740, 600 , 2217);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000210', 'WILLIAM'  , 'T', 'JONES'    , 'D11', '0942', '1979-04-11', 'DESIGNER', 17, 'M', '1953-02-23', 18270, 400 , 1462);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000220', 'JENNIFER' , 'K', 'LUTZ'     , 'D11', '0672', '1968-08-29', 'DESIGNER', 18, 'F', '1948-03-19', 29840, 600 , 2387);
        INSERT INTO <%TABLE1%> <%ARG1%> VALUES('000230', 'JAMES'    , 'J', 'JEFFERSON', 'D21', '4265', '1966-11-21', 'CLERK'   , 14, 'M', '1935-05-30', 22180, 400 , 1774);
     
        COMMIT;
     
      `;
     
     
      // Initialize API object.
      var obj1 = MFCAgentRequest.newObject();
      obj1.setLogin( "TSOUSER", "TSOPASS" );
      obj1.setURL( "http://hostname:port/" );
      obj1.setDB2SSID( "DBBG" );
      // Initialize QPE.
      obj1.initQPE( sqlText, [] );
     
      // Execute QPE.
      function create() {
          // Resolve table names used for input to SQL rules.
          var tables1 = [];
          for ( var i = 0 ; i < obj1.qpeSQLs.length ; i++ ) tables1[i] = 'DSN81110.EMP';
          // Convenience criteria argument.
          var arg1    = '(EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM)';
          // Execute SQL Rules.
          obj1.executeQPE( tables1, [], arg1 );
      }
     
      // QPE success function. Display SQL results.
      function qpeSuccess( response ) {
          for ( var i = 0 ; i < response.length ; i++ ) {
              var sqlresp = response[i].sqlresp;
              // Construct message.
              var sqlmsg = '';
              if ( sqlresp.message != null ) {
                  for ( var j = 0 ; j < sqlresp.message.length ; j++ ) {
                      sqlmsg += '<BR>' + sqlresp.message[j];
                  }
              }
              // Display results.
              document.getElementById( "container" ).innerHTML += '<BR><BR>SQL: ' + sqlresp.sql     +
                                                                  '<BR>SQLCODE: ' + sqlresp.sqlcode +
                                                                  sqlmsg;
          }
      }
     
      // QPE error function. Display JSON response in raw format.
      function qpeError( response ) {
          document.getElementById("container").innerHTML += '<br><br>' + JSON.stringify( response );
      }
     
    </script>
  </body>
</html>