Db2 for z/OS and MobileFirst Part 2: Using regular SQL statements to retrieve a string from a string ID from the SYSIBM.SYSXMLSTRINGS table

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.

About this task

In this part 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 a query and several resulting rows of the SYSIBM.SYSXMLSTRINGS catalog table:

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

A version of this tutorial was originally published on developerWorks® on 21 September, 2015. The following author contributed to the original version of this tutorial:

Jane Man, Senior Software Engineer, IBM®

Procedure

Create a simple mobile app that accesses Db2 for z/OS data by using regular SQL statements, complete the following steps:

Create an IBM MobileFirst project:
1. Click File > New > MobileFirst Project.
2. Enter Hello2 as the project name.
3. Select Hybrid Application.
4. Enter Hello2 as the application name.
The following figure shows the file structure of your new project:

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.gif"/> 
 <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>

When you click Submit, the loadFeeds function is run in main.js, 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 the loadFeeds function, the getStringFromStringId procedure is called within Hello2DB2Adapter.

Create an SQL adapter in Eclipse:
1. Click File > New > MobileFirst Adapter.
  The New MobileFirst Adapter window opens.
2. Select Hello2 as the project name.
3. Select SQL Adapter.
4. Enter HelloDB2Adapter as the adapter name.
5. Select Create procedure for offline JSONStore.
6. Click Finish.
Two files are added under the newly created Hello2DB2Adapter: Hello2DB2Adapter.xml and Hello2Adapter-impl.js.

Edit Hello2DB2Adapter.xml.

Add zserveros as dataSourceJNDIName. This should be the same as the one defined by the jndiName attribute in <dataSource> in server.xml.

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>

Edit Hello2DB2Adapter-impl.js.

Enter your SQL statement and implement getStringFromStringId, as shown in the following figure:

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]
   });
}

Deploy and test the adapter.
1. Right-click Hello2DB2Adapter and select Run As > Deploy MobileFirst Adapter > .
2. Right-click Hello2DB2Adapter and select Run As > Call MobileFirst Adapter > .
  
  The Call MobileFirst Procedure window opens:
3. 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"
      }
   ]
}
```

To use the result after calling an adapter or external resource, edit main.js and implement loadFeedsSuccess, as shown in the following figure.

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("Feedieve 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
       }
      ]
   );
}

Build and deploy the application.
1. . Right-click Hello2\apps\Hello2 and select Run As > Build All Environments.
2. 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 names Hello2Hello2Android is also created.
Test your application.
1. Right-click Hello2 and select Open MobileFirst Console.
  The IBM MobileFirst Platform Operations Console opens.
2. Click Applications.
3. Click the Android icon for Hello2, as shown in the following figure.
4. Click Preview to display a mobile browser emulator, as shown in the the following figure.
  We can use this simulator to test our new Hello2 application.
If you installed the Android SDK ADT Eclipse plug-in and defined an Android virtual device, you can run the Android emulator now. Right-click Hello2Hello2Android and select Run As > Android Application.
Your application opens in the Android emulator.

Results

Congratulations. You've created a mobile application that can access Db2 for z/OS data through the use of a simple SQL statement.

What to do next

We'll show you how create a mobile application that uses a stored procedure to run RUNTSTATS in Part Three.