Print Email

Create an IBM DB2 for z/OS Mobile Application With SQL Statements

Part Two: Using regular SQL statements to retrieve a string ID from the SYSIBM.SYSXMLSTRINGS table

2/3/2016 12:00:56 AM | This is part two of a three-part series on building an IBM DB2 for z/OS mobile application using IBM MobileFirst. Read part one.

Create a simple mobile app that accesses DB2 for z/OS data by using regular SQL statements. Once you've learned these basic steps, you'll be able to design and build more complex apps that leverage your DB2 for z/OS data.

Before you begin, make sure that you've set up your IBM MobileFirst Platform environment by completing the steps in part one.

In this post, we'll retrieve a string from SYSIBM.SYSXMLSTRINGS. This is a catalog table that contains mappings between the string and string ID that is used inside XML storage. SYSIBM.SYSXMLSTRINGS contains a set of pre-defined mappings. The following example shows several rows of the SYSIBM.SYSXMLSTRINGS catalog table:

SELECT STRINGID, SUBSTR(STRING,1,60)as STRING, IBMREQD
FROM SYSIBM.SYSXMLSTRINGS
STRING      STRING                                                    IBMREQD
1001        product                                                   N
1002        description                                               N
1003        name                                                      N


To do this, complete the following steps.

1. Create an IBM MobileFirst project:
      a. Click File > New > MobileFirst Project
      b. Enter Hello2 as the project name
      c. Select Hybrid Application
      d. Enter Hello2 as the application name

Figure 1 shows the file structure for your new project.



Figure 1: Project File Structure


2. Edit index.html. Define a text field to enter a string ID value and associate that field with a Submit button:

   <body style="display: none;">
   <div data-role="page" id="page">
   <div data-role="content" style="padding: 15px">
 <img src="images/db2v11Image5.jpg"/>
 <br/>
    Find DB2 for z/OS XML String from String id
   </div>
 
   <label for="text">String id:</label>
<input type="text" name="text" id="stringid"/>
 
<input type="button" value="Submit" src="js/main.js"  
       onclick="loadFeeds(document.getElementById('stringid').value)"/>
<div id="wrapper">
    <ul id="itemsList"/>
    </div>
   </div>
 
   <script src="js/initOptions.js"/>
   <script src="js/main.js"/>
   <script src="js/messages.js"/>
</body>

3. When you click Submit, the loadFeeds function in main.js is called, as shown:

function loadFeeds(stringid){
     WL.Logger.debug("Inside loadFeeds");
     busyIndicator.show();
     stringidInput = stringid;
 
     var invocationData = {
          adapter : 'Hello2DB2Adapter',
          procedure : 'getStringFromStringId',
          parameters : [stringid]
     };
 
     WL.Client.invokeProcedure(invocationData,{
       onSuccess : loadFeedsSuccess,
       onFailure : loadFeedsFailure
     });
}

As shown inside loadFeeds, the procedure getStringFromStringId is called within Hello2DB2Adapter.

4. Create an SQL adapter in Eclipse:
      a. Click File > New > MobileFirst Adapter. The New MobileFirst Adapter window opens.
      b. Select Hello2 as the project name
      c. Select SQL Adapter
      d. Enter Hello2DB2Adapter as the adapter name
      e. Check Create procedure for offline JSONStore
      f. Click Finish

Two files are added under the newly created Hello2DB2Adapter: Hello2DB2Adapter.xml and Hello2Adapter-impl.js.

5. Edit Hello2DB2Adapter.xml:
      a. Add zserveros as dataSourceJNDIName. This should be the same as the one defined by the jndiName attribute in <dataSource> in server.xml.
      b. Add a procedure call getStringFromStringId.

<connectivity>
   <connectionPolicy xsi:type="sql:SQLConnectionPolicy">
 
     <dataSourceJNDIName>zserveros</dataSourceJNDIName>
   </connectionPolicy>
</connectivity>
 
<procedure name="procedure1"/>
<procedure name="procedure2"/>
<procedure name="getHello2DB2Adapters"> </procedure>
<procedure name="addHello2DB2Adapter"> </procedure>
<procedure name="updateHello2DB2Adapter"> </procedure>
<procedure name="deleteHello2DB2Adapter"> </procedure>
<procedure name="getStringFromStringId"></procedure>

6. Edit Hello2DB2Adapter-impl.js.Enter your SQL statement and implement getStringFromStringId, as shown:

var select2Statement = WL.Server.createSQLStatement(
   "SELECT SUBSTR(STRING,1,60) as STRING from SYSIBM.SYSXMLSTRINGS WHERE STRINGID= ?");
 
function getStringFromStringId(param1)
{
   return WL.Server.invokeSQLStatement
   ({
      preparedStatement : select2Statement,
      parameters : [param1]
   });
}

7. Deploy and test the adapter:
      a. Right-click Hello2DB2Adapter > Run As > Deploy MobileFirst Adapter
      b. Right-click Hello2DB2Adapter > Run As > Call MobileFirst Adapter. The Call MobileFirst Procedure window opens (see Figure 2).


Figure 2: Call MobileFirst Procedure Window

      c. Enter the value of the parameter−in our example, it's 1006−then click Run. The result is returned in JSON format:

{
   "isSuccessful": true,
   "resultSet":
   [
      {
         "STRING": "instruction"
      }
 
8. To use the result after calling an adapter or external resource, edit main.js and implement loadFeedsSuccess, as shown in the following code sample. Note the resultSet from a SQL adapter is a result.invocationResult.resultSet object. First, we need to check the length of this object. If it isn't empty, we call displayFeeds() with the resultSet object as the parameter.

The following code shows the implementation of loadFeedsSuccess in main.js. Inside displayFeeds(), we call WL.SimpleDialog.show() to display the first resultSet object.

function loadFeedsSuccess(result){
   WL.Logger.debug("Feed retrieve success");
   busyIndicator.hide();
   if (result.invocationResult.resultSet.length>0)
      displayFeeds(result.invocationResult.resultSet);
   else if (result.invocationResult.resultSet.length==0)
      loadFeedsNotFound();
   else
      loadFeedsFailure();
}
function displayFeeds(resultSet){
   var ul = $('#itemsList');
 
   WL.SimpleDialog.show(resultSet[0].STRING, "String id for " + stringidInput + " is found.",
       [{
         text : 'Reload',
         handler : WL.Client.reloadApp
       }
     ]
   );
}

9. Build and deploy the application:
      a. Right-click Hello2\apps\Hello2 > Run As > Build All Environments
      b. Select iPhone, iPad, Android phones and tablets

If the application was built successfully, folders for Android, iPad and iPhone are created, as shown in the following figure. A project called Hello2Hello2Android is created as well.

The first Hello2 is the original project name, and the second is the original application name (see Figure 3).


Figure 3: Names

10. Test your application. To do this:
      a. Right-click Hello2 > Open MobileFirst Console. The IBM MobileFirst Platform Operations Console opens.
      b. Click Applications
      c. Click the Android icon for Hello2 (see Figure 4). The Android window for your application opens.


Figure 4: Android Icon

      d. Click Preview to display a mobile browser simulator, like the one in Figure 5. We can use this simulator to test our new Hello2 application.


Figure 5: Mobile Browser Simulator

If you installed the Android SDK ADT Eclipse plugin and defined an Android virtual device, you can run the Android emulator now.

11. Right-click Hello2Hello2Android > Run As > Android Application to open your application in an Android emulator (see Figure 6).


Figure 6: Various Types of Android Emulators

Congratulations. You've created a mobile application that can access DB2 for z/OS data through the use of a simple SQL statement. In the next article, we'll show you how create a mobile application that uses a stored procedure to run RUNTSTATS.

Jane Man is Senior Software Engineer in DB2 for z/OS development.
Eric G. Radzinski is a content developer in DB2 for QMF, IBM Analytics.

Please sign in to comment.

Sign In




Join Now!
Big Data Demands Big Iron Skills

Big Data Demands Big Iron Skills

The effects of a mass exodus from the mainframe ranks could affect a number of computing trends, not the least of which is big data.

Read more »

Best System Programmer Attributes

Best System Programmer Attributes

Mainframers give advice and lessons learned on what helps in the real world.

Read more »