Db2 for z/OS and MobileFirst Part 3: Using a stored procedure to execute RUNSTATS

In this article, we'll show you how to use a stored procedure to call a standard Db2 for z/OS® utility, RUNSTATS, from within a mobile app. You can apply the steps in this post to call other Db2 for z/OS utilities as well.

1. Follow the instructions in Part 2 to create a new IBM MobileFirst project and a new hybrid application called Runstats.
2. Create an SQL adapter for the Runstats project called RunstatsSQLAdapter.
3. Edit RunstatsSQLAdapter.xml and add the following definitions:
   • zserveros as dataSource JNDIName (or whatever JNDI name you defined in server.xml).
   • Add <procedure name="runstatSP1"/>.
4. Edit RunstatsSQLAdapter-impl.js to implement runstatSP1.

   We need to call a procedure called SYSPROC.DSNUTILS using WL.Server.invokeSQLStoredProcedure() and pass in the parameters.

   The following code shows part of its implementation:

   function runstatSP1(database, tablespace) {
   
      var utilityStatement = "RUNSTATS TABLESPACE " + database + "." + tablespace + "    UPDATE(ALL)";
      utilityStatement = utilityStatement.toString();
      return WL.Server.invokeSQLStoredProcedure({
         procedure : "SYSPROC.DSNUTILS",
         parameters : [123456789,
                       "",
                       utilityStatement,
                       "",
                       "RUNSTATS TABLESPACE",
                       "", "", 0, "", "",
                       0, "", "", 0, "",
                       "", 0, "", "", 0,
                       "", "", 0, "", "",
                       0, "", "", 0, "",
                       "", 0, "", "", 0,
                       "", "", 0, "", "",
                       0
                       ]
      });
   }

5. Implement the front end (client side) like we did in Part 2. See steps 1-3.
   1. Edit index.html and add two inputs: one for the database and one for the table space.

      <div data-role="content" style="padding: 15px">
      <!--application UI goes here-->
            RUNSTATS
      </div>
      
         <label for="text">Database:</label>
         <input type="text" name="text" id="database">
         <label for="text">Tablespace:</label>
         <input type="text" name="text" id="tablespace">
         <input type="button" value="Submit" src="js/main.js"
          onclick="loadFeeds(document.getElementById('database').value, 
                             document.getElementById('tablespace').value)">
       
      
      <div id="wrapper">
            <ul id="itemsList"></ul>
      </div>

   2. Edit main.js to use the result after invoking runstatSP1.

      function loadFeeds(db, ts){
            WL.Logger.debug("InsideFeeds");
            WL.Logger.debug("InsideFeeds: db= " + db);
            WL.Logger.debug("InsideFeeds: ts= " + ts);
            busyIndicator.show();
      
            // cast database and tablespace to uppercase because DB2 objects are case          
      
            // insensitive; otherwise RUNSTATS will not find it
            db = db.toUpperCase();
            ts = ts.toUpperCase();
      
            var invocationData = {
                      adapter : 'RunstatSQLAdapter',
                      procedure : 'runstatSP1',
                      parameters : [db, ts]
            };
            WL.Client.invokeProcedure(invocationData,{
                   onSuccess : loadFeedsSuccess,
                   onFailure : loadFeedsFailure
            });
      }
      
      function loadFeedsSuccess(result){
            WL.Logger.debug("Feedieve success");
            busyIndicator.hide();
            if (result.invocationResult.resultSet.length>0)
                  displayFeeds(result.invocationResult.resultSet);
            else
                  loadFeedsFailure();
      }
      
      function displayFeeds(resultSet){
            WL.Logger.debug("displayFeeds....");
            var ul = $('#itemsList');
            for (var i = 0; i < resultSet.length; i++) {
                  var li = $('<li/>').text(resultSet[i].SEQNO);
                  var TEXT = $('<div/>', {
                       'class': 'TEXT'
                  }).text(resultSet[i].TEXT);
      
                  li.append(TEXT);
      
                  ul.append(li);
            }
      }

Results

The following figure shows what your application looks like in an Android emulator:

Now that you've learned how to use a stored procedure to call RUNSTATS, you can apply this knowledge to incorporate other Db2 for z/OS utilities into your mobile apps.

We hope that you've found this series of tutorials helpful and that they've given you a good glimpse at the capabilities of using IBM MobileFirst Platform to leverage the value of your Db2 for z/OS transactional data. Use the following resources to learn more about working with Db2 for z/OS data in mobile environments.