Using jconsole to monitor Tomcat running on i5/OS

Shipped with JDK, there is an application monitoring tool called jconsole. Using jconsole, we can detect low memory, enable or disable GC and class loading verbose tracing, detect deadlocks, control the log level of any loggers in an application etc. This simple tutorial shows how we can start up Tomcat 6.x on i5/OS V5R4 and monitor it remotely through our desktop. In fact, this is not Tomcat's feature. It is JVM feature. We just use Tomcat as an example and see if JDK on i5/OS supports this feature or not.

1) Open the catalina.sh and add additional JVM properties after CATALINA_BASE env settings.

# Only set CATALINA_HOME if not already set
[ -z "$CATALINA_HOME" ] && CATALINA_HOME=`cd "$PRGDIR/.." >/dev/null; pwd`

# Copy CATALINA_BASE from CATALINA_HOME if not already set
[ -z "$CATALINA_BASE" ] && CATALINA_BASE="$CATALINA_HOME"

CATALINA_OPTS="-Dcom.sun.management.jmxremote 
-Dcom.sun.management.jmxremote.port=YourJMXPort 
-Dcom.sun.management.jmxremote.ssl=false 
-Dcom.sun.management.jmxremote.authenticate=true 
-Djava.rmi.server.hostname=YourTomcatHostname
-Dcom.sun.management.jmxremote.password.file=$CATALINA_BASE/conf/jmx.psd 
-Dcom.sun.management.jmxremote.access.file=$CATALINA_BASE/conf/jmx.acl"

2) creating jmx.psd under folder $CATALINA_BASE/conf/ and put text content in it as below. So, we create a user name "controlRole" with password as "tomcat"

controlRole tomcat

3) creating jmx.acl under folder $CATALINA_BASE/conf/ and put text content in it as below. So, we assign user controlRole privilege of Read and Write.

controlRole readwrite

4) Open qshell and modify the file attribute as below. This will make sure that your account has read and write privilege to these access control files. We change this according to different account that start Tomcat.

call qp2term
chmod 600 jmx.acl  
chmod 600 jmx.psd  

5) On a Windows PC, run jconsole as below.

c:\Program Files\Java\jdk1.6.0_21\bin>jconsole

6) After jconsole is running, we need to input host name, port number, user name, and password.

7)Now, we can see jconsole connects to remote tomcat running on i5/OS.

Enjoy it for monitoring and tuning your Tomcat and servlet application.

Mapping SQL terminology with IBM i terminology

As a IBMi developer coming with background of modern SQL RDBMS, I was always try to "translate" iSerience term like "physical file", "logical file" into SQL terms. Here, I found a table, which maps these terms.

Mapping SQL terminology with IBM i terminology

SQL term	IBM i term
TABLE	PHYSICAL FILE
ROW	RECORD
COLUMN	FIELD
INDEX	KEYED LOGICAL FILE
VIEW	NON-KEYED LOGICAL FILE
SCHEMA	LIBRARY
LOG	JOURNAL
ISOLATION LEVEL	COMMITMENT CONTROLE LEVEL
PARTITION	MEMBER

The table is cited from document IBM DB2 for i indexing methods and strategies

IBM DB2 extension has no limitation on number of opened persistent PHP DB2 connection.

To further study how to optimize PHP DB2 connection, I looked into PHP DB2 extension again recently. I want to find out the best way to use persist connection with considering about ODP reusing.

After reading the source code of PHP DB2 extension, I noticed that there is no limitation of maximum number of pconnection from PHP DB2 extension. In the C code, we can see only UserID, password, and Database name are used to compose hash code. You locate the place by search following code in the source code.

sprintf(hKey, "__db2_%s.%s.%s", uid, database, password);

This is interesting as it implies that I should avoid allowing huge number of different user profile to be used inside one Zend Server.

I am thinking that we will have difficult to reuse ODP when only userId, password, and database name are used to generate hash code for pconnect because ODP will be reused based on not only user name and password. For example, I think the ODP will be rebuilt if different requests use same User Name and password but different library list to get pconnection. PHP Db2 extension allows us to pass in library list. So, if developers, for some reason, happen to pass in different library list to run query on same library, the ODP will be rebuilt for sure.

Some other things I do not have answers are,

1) What happens to pconnection if its associated QSQSRVR job is terminated for some reason?

2) I saw something about ODBC version occur in C code. Does PHP DB2 extension works with ODBC in any way?

A simple example about creating DB2 SQL stored procedure to return multiple Result Set

There are plenty of examples showing how to get multiple result sets in languages like Java or php. But, there are not so many simple stored procedure examples, which return multiple result sets. Here is a very simple DB2 stored procedure, which return two result set. Just following the steps, you will have a multiple results DB2 stored procedure for testing.

Step 1: Create a table:

CREATE TABLE DEPT
     (DEPTNO   CHAR(3)     NOT NULL,
      DEPTNAME VARCHAR(36) NOT NULL,
      PRIMARY KEY(DEPTNO))

Step 2: Populate data into table:

INSERT INTO DEPT (DEPTNO, DEPTNAME)
     VALUES ('B11', 'PURCHASING'),
            ('E41', 'DATABASE ADMINISTRATION') ;

Step 3: Create SQL store procedure:

CREATE PROCEDURE TESTMULTIRS
--We do not use this input parameter.
(IN i_cmacct CHARACTER(5)) 
RESULT SETS 2
LANGUAGE SQL
BEGIN 

DECLARE csnum INTEGER;

--Declare serial cursors as serial cursor consume less resources
--and we do not need rollable cursor.
DECLARE getDeptNo CHAR(50); --Be careful about estimated length here.
DECLARE getDeptName CHAR(200);
DECLARE c1 CURSOR WITH RETURN FOR s1; 
DECLARE c2 CURSOR WITH RETURN FOR s2;

SET getDeptNo = 'SELECT DEPTNO FROM DEPT';
SET getDeptName = 'SELECT DEPTNAME FROM DEPT'; 

PREPARE s1 FROM getDeptNo;
OPEN c1;

PREPARE s2 FROM getDeptName;
OPEN c2;

END;

Step 4: Call stored procedure in iNavigator:

call testmultirs('jia');

Then we can see outpu as below in inavigator:

and

A simple tutorial about creating Jersey RESTful web service in Netbean 7.0

I believe that new technologies should make things simpler instead of more complex. RESTful Web service is one of them. But, it seems there is no very simple tutorial about how to create RESTful Web service in Netbean. So, here is a extremely simple tutorial about how to create a Servlet based only RESTful web service in Netbeans 7.0.

There are several different RESTful web service frameworks on the market. Jersey is one of them. Jersey is reference implementation of JSR 311: JAX-RS: The JavaTM API for RESTful Web Services . The follows steps will show how easy it is to create a RESTful Web Service in Netbeans 7.0.

1)Create a Web application

2) Create our first RESTful Web service in Netbeans 7.0 with Jersey.

3) Test it.

Once we successfully did above steps, we see a new Restful Web Service folder in project as shown below.

We modify and add hello world string into GenericResource.java as shown below,

/**
* Retrieves representation of an instance of jia.blog.rest.GenericResource
* @return an instance of java.lang.String
*/
@GET
@Produces("text/html")
public String getHtml() {
   //TODO return proper representation object
   return "
Hello World!
";
}

Then we compile and deploy the project. Once we successful deployed it. We can test our hello world example by using the following URL:

http://localhost:8080/jerseyRest/firstREST/generic

I purposely use different string "jerseyRest", "fristREST", "generic" in URL as this way can help you to map string in URL into the place where we input in netbeans 7.0.

Also, this simple hello world example has been successfully tested on JDK 1.5 and Tomcat 6.0 environment. So, it works on i5/OS V5R4 platform too.

Here is the link to source code jiaRest.zip

i5/OS job status help file

Below is job status help file exactly copied from i5/OS R5V4 help menu. I copy it here to study them in detail later. Especially for status of prestart job on i5/OS.

---

The status of the initial thread of the job. Only one status is
displayed per job. A blank status field represents an initial thread
that is in transition. Possible values are:

BSCA
The initial thread of the job is waiting for the completion of an
I/O operation to a binary synchronous device in the activity level.

BSCW
The initial thread of the job is waiting for the completion of an
I/O operation to a binary synchronous device.

CMNA
The initial thread of the job is waiting for the completion of an
I/O operation to a communications device in the activity level.

CMNW
The initial thread of the job is waiting for the completion of an
I/O operation to a communications device.

CMTW
The initial thread of the job is waiting for the completion of
save-while-active checkpoint processing in another job. This wait
is necessary to prevent a partial commitment control transaction
from being saved to the media.

CNDW
The initial thread of the job is waiting for the handle-based
condition.

CPCW
The initial thread of the job is waiting for the completion of a
CPI Communications call.

DEQA
The initial thread of the job is waiting for completion of a
dequeue operation in the pool activity level.

DEQW
The initial thread of the job is waiting for completion of a
dequeue operation. For example, QSYSARB and subsystem monitors
generally wait for work by waiting for a dequeue operation.

DKTA
The initial thread of the job is waiting for the completion of an
I/O operation to a diskette device in the activity level.

DKTW
The initial thread of the job is waiting for the completion of an
I/O operation to a diskette device.

DLYW
Due to the Delay Job (DLYJOB) command, the initial thread of the
job is delayed while it waits for a time interval to end, or for a
specific delay end time. The function field shows either the
number of seconds the job is to delay (999999), or the specific
time when the job is to resume running.

DSC
The job has been disconnected from a work station display.

DSPA
The initial thread of the job is waiting for input from a work
station display in the activity level.

DSPW
The initial thread of the job is waiting for input from a work
station display.

END
The job has been ended with the *IMMED option, or delay time has
ended with the *CNTRLD option.

EOFA
The initial thread of the job is waiting in the activity level to
try a read operation again on a database file after the end-of-file
has been reached.

EOFW
The initial thread of the job is waiting to try a read operation
again on a database file after the end-of-file has been reached.

EOJ
The job is ending for a reason other than End Job (ENDJOB) or End
Subsystem (ENDSBS). For example, SIGNOFF, End Group Job
(ENDGRPJOB), or an exception that is not being handled.

EVTW
The initial thread of the job is waiting for an event. For
example, QLUS and SCPF generally wait for work by waiting for an
event.

GRP
The job is suspended due to a Transfer to Group Job (TFRGRPJOB)
command.

HLD
The job is being held.

HLDT
The initial thread of the job is held.

ICFA
The initial thread of the job is waiting, in an activity level, for
the completion of an I/O operation to an intersystem communications
function file.

ICFW
The initial thread of the job is waiting for the completion of an
I/O operation to an intersystem communications function file.

INEL
The initial thread of the job is ineligible and not currently in
the pool activity level.

JVAA
The initial thread of the job is waiting for completion of a Java
program operation in the pool activity level.

JVAW
The initial thread of the job is waiting for completion of a Java
program operation.

LCKW
The initial thread of the job is waiting for a lock.

LSPA
The initial thread of the job is waiting for a lock space to be
attached in the pool activity level.

LSPW
The initial thread of the job is waiting for a lock space to be
attached.

MLTA
The initial thread of the job is waiting, in an activity level, for
the completion of an I/O operation to multiple files.

MLTW
The initial thread of the job is waiting for the completion of an
I/O operation to multiple files.

MSGW
The initial thread of the job is waiting for a message from a
message queue.

MTXW
The initial thread of the job is in a mutex wait. A mutex is a
synchronization function that is used to allow multiple jobs or
processes to serialize their access to shared data.

MXDW
The initial thread of the job is waiting for the completion of an
I/O operation to a mixed device file. Details are in the Remote
Work_Station_Support book.

OPTA
The initial thread of the job is waiting, in an activity level, for
the completion of an I/O operation to an optical device.

OPTW
The initial thread of the job is waiting for the completion of an
I/O operation to an optical device.

OSIW
The initial thread of the job is waiting for the completion of an
OSI Communications Subsystem OSLISN, OSRACS, OSRACA, OSRCV, or
OSRCVA operation.

PRTA
The initial thread of the job is waiting for output to a printer to
complete in the activity level.

PRTW
The initial thread of the job is waiting for output to a printer to
be completed.

PSRW
The initial thread of the job is a prestart job waiting for a
program start request.

RUN
The initial thread of the job is currently running in the activity
level.

SELW
The initial thread of the job is in a select wait. More
information on the select() function is in the Sockets APIs chapter
in the System API Reference information in the iSeries Information
Center at http://www.ibm.com/eserver/iseries/infocenter.

SEMW
The initial thread of the job is waiting for a semaphore. A
semaphore is a synchronization function that is used to allow
multiple jobs or threads to serialize their access to shared data.

SIGS
The initial thread of the job is stopped by a signal.

SIGW
The initial thread of the job is waiting for a signal.

SRQ
The initial thread of the job is the suspended half of a system
request job pair.

SVFA
The initial thread of the job is waiting for completion of a Save
File operation in the activity level.

SVFW
The initial thread of the job is waiting for completion of a Save
File operation.

TAPA
The initial thread of the job is waiting for completion of an I/O
operation to a tape device in the activity level.

TAPW
The initial thread of the job is waiting for completion of an I/O
operation to a tape device.

THDW
The initial thread is waiting for another thread to complete an
operation.

TIMA
The initial thread of the job is waiting, in the activity level,
for a time interval to end.

TIMW
The initial thread of the job is waiting for a time interval to
end.

IBM i5/OS V5R4 prestart job and database connection tuning.

Database connection tuning is an important part of application performance tuning, particularly, when applications uses connection pool. I noticed that some iSeries user does not care about this too much as I can not get answer when I ask them what is the limitation of database connection iSeries can support. The answer may not be simple. Or it can be as simple as no limitation as the default maximum number of QSQSRVR job and QZDASOINIT job is *NOMAX. This wont be good answer as the number of job should be optimized to a number, which fits your applications requirement.

On IBM i5/OS, there are two jobs used by database connection. They are SQL server mode prestarted job QSQSRVR and Database server prestart job QZDASOINIT job. QSQSRVR job is used when Database connection is coming through Command Level Interface (CLI). For example, PHP DB2 extension uses CLI to connect to DB2. Therefore, its corresponding job is QSQSRVR. Native iSeries JDBC driver (type 2) is using QSQSRVR job too. QZDASOINIT job is used when the connection is from ODBC or type 4 JDBC. Therefore, access from jt400.jar goes through QZDASOINIT job. When the type 4 JDBC is configured to use SSL, QZDASSINIT job is used instead of QZDASOINIT.

Both QSQSRVR job and QZDASOINIT job have their deamon job, which creates them. QSQSRVR job's deamon job is QYPSJSVR. Command WRKJOB JOB(QYPSJSVR/QYPSJSVR) can be used to do further investigate. For QSQSRVR job itself, we can use command WRKSBSD SBSD(QSYS/QSYSWRK) or command WRKJOB JOB(QSQSRVR) to do deeper investigation. For QZDASOINIT job, its deamon job is QZDASRVSD. We can use command WRKJOB JOB(QZDASRVSD) or WRKSBSD SBSD(QSYS/QSERVER) work with it further. Command CHGPJE can be used to change the configuration of prestart jobs. Also, don't forget that iNavigator is a convenience tool for us to investigate too: Work Management -> Server Jobs . The database server prestart jobs (QZDAINIT, QZDASOINIT, and QZDASSINIT) by default are shipped to run in subsystem QSERVER QZDAINIT) and QUSRWRK (QZDASOINIT and QZDASSINIT).

Also, there is another prestart job called QRWTSRVR, which is used by DB2 Connect driver. DB2 Connect uses DRDA protocol that is used to reduce the cost and complexity of accessing data in different DB2 supporting DRDA. In fact, in Work Management menu of iNavigator, you can see that iNavigator uses QRWTSRVR job. However, its license fee is expensive for enterprise edition. QRWTSRVR job typically run in QSYSWRK subsystem.

I made a diagram to present these three ways to connect to DB2 for iSeries. It is helpful for having a clear concept how the database connection is connect to DB2 and what is under those licensed and unlicensed program indeed. For example, people always ask why IBM gives type 4 JDBC driver for free and charge DB2 Connect. From this post, we can see jt400.jar, php db2 extension, and DB2 Connect use different protocol connect to DB2 for iSeries and different server jobs serve them. Furthermore, different protocols are designed for different usage. For example, DRDA is designed to support topology in complicate distributed network environment.

And, in Zend server for IBMi, there is a package called i5 Toolkit. It can be used to connect to i5/OS and access data too as DB2 for i5/OS is part of OS. A daemon called i5_COMD server is shipped together with Zend Server. i5 toolkit client communicate with it-COMD server for accessing i5 Data object. However, I feel it emulates 5250 terminal protocol and uses Remot Command Server prestart job QZRCSRVS. I am not able to discuss it here. I will spend some time to dig it later.

Here are some links from IBM for prestarted job on i5/OS V5R4 and checking job log of the appropriate "Host Server" and a PTF for improved management of QSQSRVR jobs.

SQL naming must be used when calling user defined function in SQL Stored procedure on UDB DB2 for i5/OS

Today, I got a small tip, which let me aware that when SQL naming must be used when calling a Java User Defined Function in SQL stored procedure.

I wrote a UDF in Java for DB2 for i5/OS. A dummy code sample like below,

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
* @author Yiyu Jia
*/
public class DummyUDF {

 boolean debugtableOn = true;
 public static String helloJia(String name)throws SQLException, Exception {
  //Connection con = DriverManager.getConnection("jdbc:default:connection");
  return "hello" + name;
 }
}

Then, we register it as DB2 user defined function as below,

CREATE FUNCTION YOURSCHEMA.DummyUDF ( 
NAME VARCHAR(500) 
) 
RETURNS VARCHAR(1000)   
LANGUAGE JAVA 
SPECIFIC YOURSCHEMA.DummyUDF 
NOT DETERMINISTIC 
MODIFIES SQL DATA  
RETURNS NULL ON NULL INPUT 
EXTERNAL NAME 'DummyUDF.helloJia' 
PARAMETER STYLE JAVA ;

However, when I call it in either iNavigator or SQL stored procedure, I was reported error message, which says
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] DummyUDF in *LIBL type *N not found. ...

The statement used to call DummyUDF is as below,

values(YOURSCHEMA/DummyUDF('Yiyu'));

Since my iNavigator is configured to use system naming. I use "/" to link the schema name and function name. However, this wont work!After research and trying, I found it is extremely simply to solve this problem. That is, using SQL naming instead of System naming to call UDF though iNavigator is set to use system naming already. Below code works,

values(YOURSCHEMA.DummyUDF('Yiyu'));

Zend Server for IBM iSeries platform does not support multiple persistent connection to DB2

_
_

Edit: This post's content may only apply to i5/OS V5R4 without latest PTF. Please refer to comments for detailed info.

Persistent connection in PHP has its advantage and disadvantage. According to my observation about creating DB2 connection on i5/OS, time used to set up connection via normal connection and persistent connection can be 50 ~ 100 times different. However, persistent connection has its disadvantage as well. Some php programmer even call it evil. Also, it might not be worthy to take risk from persistent connection if the access traffic is not so high.

In a brief way to describe, the causer for persistent connection has disadvantage is that PHP itself is a stateless script language and database connection is usually stateful. That is, php script's life time only exists from the request coming and request end. Meanwhile, database connection will hold some state like table lock, user defined variables etc. So, the problem will occur when a php script use a persistent connection as a fresh new connection and it is not. PHP persistent does not support a kind of "private" connection, which will be used by one user session only. This kind private connection implementation can be found in easyComm i5 toolkit.

Back to what I observed recently. I find that Zend Server (ver 5.0.4) for IBM i5/OS V5R4 does not supports having multiple persistent connection in one request to php scripts. Below is a simple testing code. When you run it, you can see that the output Current Schema is not ALWAYS as what you specified in db2 connection string. I guess the hash function used Zend Server for resource manager may has some problem. Or, DB2 extension has a bug if it does not intend to support only one persistent connection in one PHP script request.

Change the db2_pconnect to db2_connect to see different output. You need to run this script many times to see that you can not ALWAYS get the right schema as your current schema. Most likely, you need to take 5 minutes to 10 minutes break during the testing and see the wrong result to come out.

<?php 

$sysStr = 'select CURRENT SCHEMA from SYSIBM.SYSDUMMY1 ';

$params = array(
 'username' => 'accountNameOne', 
 'password' => 'accountPwdOne',
 'dbname' => '*LOCAL', 
 'driver_options' => array('i5_lib' => 'libraryOne')
);
$connOne = db2_pconnect($params['dbname'], $params['username'], 
$params['password'], $params['driver_options']);
$sysStmt = db2_prepare($connOne, $sysStr);
db2_execute($sysStmt);
$returnObj = db2_fetch_object($sysStmt);
var_dump($returnObj);

$params2 = array(
 'username' => 'accountNameTwo', 
 'password' => 'accountPwdTwo', 
 'dbname' => '*LOCAL', 
 'driver_options' => array('i5_lib' => 'libraryTwo')
);
$connTwo = db2_pconnect($params2['dbname'], $params2['username'], 
$params2['password'], $params2['driver_options']);
$sysStmt2 = db2_prepare($connTwo, $sysStr);
db2_execute($sysStmt2);
$returnObj2 = db2_fetch_object($sysStmt2);
var_dump($returnObj2);

buit-in datatype supported by DB2 for i5/OS and MySQL

To complete one of my work, I need to know list of data type supported by RDBMS. So, I wrote a simple Java application to fetch it. Below is java code and output.

Db2MetaData.java using jt400.jar. (This is just a quick work)

package jia.blog.db2;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Yiyu Jia
 */
public class Db2MetaData {
   Connection conn = null;
   DatabaseMetaData dmd = null;

    public Db2MetaData() {
     
    }

  public void openConnection () {

      try {                  
          String url = "jdbc:as400://172.0.0.1"; //change ip address
          DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
          conn = DriverManager.getConnection(url, "accountName", "password");
      }
    catch (Exception e) {
         Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, e);
       }
  }  

 
  public void closeConnection () {

    if (conn != null) {
       try {
           conn.close();           
           System.out.println("Connection closed");
           }
       catch(SQLException e) {
          Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, e);
          }
       }
  }  


    public void displayMaxConnection() {
        try {
            DatabaseMetaData metadata = conn.getMetaData();
            int maxConnection = metadata.getMaxConnections();
            System.out.println("Maximum Connection = " + maxConnection);
        } catch (SQLException ex) {
            Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void displayTypeInfo() {
        try {
            DatabaseMetaData metadata = conn.getMetaData();
            ResultSet resultSet = metadata.getTypeInfo();
            while (resultSet.next()) {
                String typeName = resultSet.getString("TYPE_NAME");
                System.out.println(typeName);
            }
            resultSet.close();
        } catch (SQLException ex) {
            Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

  public static void main(String args[])  {
      Db2MetaData db2Meta = new Db2MetaData();
      db2Meta.openConnection();
      db2Meta.displayMaxConnection();
      db2Meta.displayTypeInfo();
      db2Meta.closeConnection();
  }

}

Supported data type printed by JDBC

DB2 i5 R6V1	MySQL 5.5.8
CHAR CHAR() FOR BIT DATA GRAPHIC LONG VARGRAPHIC LONG VARCHAR FOR BIT DATA VARCHAR VARCHAR() FOR BIT DATA VARGRAPHIC DATE TIMESTAMP TIME DECIMAL DOUBLE FLOAT INTEGER NUMERIC REAL SMALLINT BIGINT ROWID DECFLOAT DATALINK BLOB CLOB DBCLOB	BIT BOOL TINYINT TINYINT UNSIGNED BIGINT BIGINT UNSIGNED LONG VARBINARY MEDIUMBLOB LONGBLOB BLOB TINYBLOB VARBINARY BINARY LONG VARCHAR MEDIUMTEXT LONGTEXT TEXT TINYTEXT CHAR NUMERIC DECIMAL INTEGER INTEGER UNSIGNED INT INT UNSIGNED MEDIUMINT MEDIUMINT UNSIGNED SMALLINT SMALLINT UNSIGNED FLOAT DOUBLE DOUBLE PRECISION REAL VARCHAR ENUM SET DATE TIME DATETIME TIMESTAMP

Below is a picture linked from IBM web site. It listed out built-in data type supported by DB2 for i5/OS RvV4.source

Here are links for DB2 data type to jdbc data type mapping and MySQL data type to JDBC data type mapping

PHP DB2 extension should support features like timeout checking.

I ran into very troublesome problem when I use DB2 connection from PHP to call RPG stored procedure. What happened is described in the following diagram.

  

As you can see, if calling a bad configured RPG stored procedure from PHP through DB2 extension on the same machine where Zend server and DB2 are installed, Zend Server may hang and it can not even be restarted from Zend Menu in green screen of i5/OS.

The appeared phenomenon is that QSQSRVR jobs is under MSGW status and seems the waiting status will never be broken. I am not sure which part exactly causes this. But, I think adding a time-out check mechanism in PHP DB2 extension on IBM i could be considered. The detailed discussion can be found from Zend Forums.

naming scheme of i5/OS commands

This post is a citation from http://www.mcpressonline.com. I like it because this tip helps me to remember command set from i5/OS. I cite it to my blog here as I think this place is better than my Web browser bookmark.

1. First, a three-letter abbreviation for a verb, such as create, remove, print, or send. See the table below.
2. Last, a series of abbreviations for modifiers, such as file, program, job queue, or user profile. These abbreviations are usually three letters long, but there are many exceptions.

Example Abbreviations
Verbs		Modifiers
CRT	Create	F	File
CHG	Change	PF	Physical File
DLT	Delete	LF	Logical File
ADD	Add	SRCF	Source File
RMV	Remove	CLPGM	CL Program
DSP	Display	RPGPGM	RPG Program
WRK	Work with	MSGQ	Message Queue
STR	Start	OUTQ	Output Queue
END	End	JOBQ	Job Queue
		DTAQ	Data Queue
		DTAARA	Data Area
		SBS	Subsystem

For example,

DSPSBSD SBSD(QUSRWRK) => DSP+SBS+D(display)
STRSQL  =>  STR + SQL 
WRKJVMJOB  =>  WRK + JVM  + JOB
CRTDTAQ  => CRT  +  DTAQ
DSPMSGD CPEnnnn (where NNNN is 4-digit error number)
CHGATR OBJ(Sth) ATR(*READONLY) VALUE(*NO)
WRKLNK 'your/file'

The full article is here

Did MySQL for IBM i5/OS platform die?

On Zend Forum, there is a hot discussion about MySQL announce EOL support for MySQL on IBM i. This is the link to Zend forum post. This is MySQL EOL announce. However, after reading those discussion, I feel some of them are worrying something which they do not need to worry about.

I think the EOL support only means that there will be newer version of MySQL for IBM i platform. It does not mean developer can not use existing latest version of MySQL on IBM i. The only thing missed  is that you can not BUY support from MySQL because they do not supply this service. However, will this be big issue? I do not think it is going to big problem because many people want to use MySQL because it is FREE. I think I will be happy with MySQL as long as its features are enough for my application and it is in a product quality.

So, I would like to ask people, who worry about the EOL support for MySQL on IBM i, to think about one thing. What do they want, always upgrading to latest version of MySQL or using one stable version to build their own product? Obviously, most of people's target is the second one: build their own application on top of free MySQL RDBMS. Therefore, I would like to say MySQL does not have its EOL on IBM i. Developers can still use it as long as they are satisfied with its quality and features.

I am interesting in MySQL on IBM i is because I am looking for a way to walk around the DB2 Connect license fee issue. To remotely connect to a IBM i DB2, developer need to buy DB2 Connect license from IBM if they are not using free type 4 JDBC library like jt400. Therefore, I am thinking that let PHP script to remotely connect to MySQL server with DB2i storage engine might be a good way to walk around this license issue. With this solution, PHP can write and read data which is stored in DB2. And the data can be subsequently accessed from DB2 side like RPG programm. But, the limitation will be that developer can only write stored procedure in MySQL and this stored procedure can be remotely accessed by PHP through MySQL interface.

Some notes about QSQSRVR job on IBM i5 OS V5R4

QSQSRVR runs in subsystem QSYSWRK. But, with "V5R4 PTF—SI33298" or "V6R1 PTF—SI33949", it is possible to configure QSQSRVR to run in different subsystem. I copied some commands here for my convenience.

There are two ways to request to use SQL Server Mode in programm. One is, in SQL CLI API, call SQLSetEnvAttr() via the SQL_ATTR_SERVER_MODE attribute. The other is using QWTCHGJB() Work Management API, via the "Server mode for Structured Query Language" key 1922 within the JOBC0200 format.

By default, five QSQSRVR jobs are active. Two more QSQSRVR jobs will be created if fewer than jobs are unused. The following command is to increase the initial number of jobs, threshold, and job number increasing interval.
CHGPJE SBSD(QSYS/QSYSWRK) PGM(QSYS/QSQSRVR)

Management Central starts 18 SQL Server Mode connections. Each of the two servers (QYPSSRV and QYPSJSVR) currently establishes nine connections with a QSQSRVR job to process its SQL requests to these databases. The following commands are used to find the controlling job:

1)Using command line: WRKJOB JOB(QYPSJSVR/QYPSJSVR)
2)Or using iNavigator: Work Management --> Subsystem --> Active Subsystems --> Qsyswrk
3)Using SQL monitor: STRDBMON OUTFILE(MYLIB/QSQMON1) JOB(*ALL/*ALL/QSQSRVR)

In order to run QSQSRVR job in same subsystem as application runs, we need to do following steps,

1)install PTF—SI33298 for i5 OS V5R4 or PTF—SI33949 for i5 OS V6R1. Use command WRKPTFGRP to list all installed PTF.

2) set environment variable QIBM_SRVRMODE_SBS to be "*SAME" or "". Here is the command to add this environment variable, ADDENVVAR ENVVAR(QIBM_SRVRMODE_SBS) VALUE('*SAME') LEVEL(*SYS). To remove environment variable, command RMVENVVAR is used.

Finally, can I use this to optimize Web application running inside Zend server? It seems I can not because the QIBM_SRVRMODE_SBS environment variable is ignored whenever the name of the application subsystem is 'QHTTPSVR' or 'ZEND'. However, this environment variable should be seen in subsystem where application use native JDBC driver to connect to DB2. PHP db2 extension and native JDBC driver (in IBM Java toolkit) connect to DB2 through DB2 SQL server mode. Using DB2 SQL Server Mode, each connection has a QSQSRVR job. QSQSRVR job inherits attributes user profile and running priority from connection request initializing.

references
1) DB2 for i5/OS: SQL Server Mode Primer
2) TechTip: Grab Control of the DB2 QSQSRVR Jobs

Installing activeMQ on IBM i5 V5R4

The project I am working on is written in PHP. Since it is a B2B web service, I want my application to write down both system level log and application level log as much as possible. Then, somebody can analyze the log when they want to.

However, as well known, an I/O operation is a time consuming operation. Obviously, I do not want this logging function will significantly slow down my application. Therefore, I design a asynchronous model to write logs. In order to be asynchronous, I have two choices. The first is multi-threaded. The second is messaging queue. Since PHP does not have built-in support for multi-thread and PHP is stateless, I choose Messaging Queue. I used to lead on migrating from Sonic MQ to ActiveMQ for a ETL engine project. So, I decide to give it a try on IBM i i5 V5R4 platform.

On IBM i i5 V5R4 platform, I have JDK 1.5 32bit, which is IBM J9 VM. Below is the steps I install activeMQ on IBM i5/OS V5R4,

1) run command call qp2term to open a i5/OS PASE terminal session.

2) run java -version to check the JDK version.

3) run export JAVA_HOME=/QOpenSys/QIBM/ProdData/JavaVM/jdk50/32bit to set this session's JAVA_HOME to point to JDK1.5 . or, call command CHGENVVAR ENVVAR(JAVA_HOME) VALUE('/QOpenSys/QIBM/ProdData/JavaVM/jdk50/32bit') to modify the JAVA_HOME environment if i have not in PASE environment. or use ADDENVVAR to add the environment.

4) run java -version to check the JDK version again to make sure your using JDK 1.5 now.

5) Go to http://activemq.apache.org/ and download activeMQ distribution for Unix/Linux/Cygwin platform. I downloaded version 5.4.2 .

6) using 7zip to unzip the file to be a TAR file as I can not use -z option with tar command in PASE environment on i5/OS.

7) copying the tar file under my home directory on i5/OS via IFS.

8) running command tar -xvf apache-activemq-5.4.2.tar

9) cd into directory "apache-activemq-5.4.2" and run command ./bin/activemq to start up activemq with default configuration.

10) Since I can not control i5/OS and the PASE terminal emulator might has problem with character schema used by JDK in default. I choose to test this activemq instance from my PC workstation.

11) Download and install activemq zip format distribution.

12) set the JAVA_HOME environment properly from either Windows system environment variable setting or adding set JAVA_HOME=%your jdk home directory% into BATCH files.

13) Download and install apache ANT and add ANT bin directory into Windows PATH environment.

14) In windows command prompt window, go to activemq example directory and run command ant producer -Durl=tcp://your_i5_host:11616 to send sample msg into activemq testing channel.

15) In windows command prompt window, go to activemq example directory and run command ant consumer -Durl=tcp://your_i5_host:11616 to consume sample msg inside activemq testing channel.

After the last two steps is successful, I know I can use activeMQ on i5/OS. Although this is not a installation for product environment, it shows me how to install activeMQ under my home directory as developing environment.

creating RESTful service with Zend_Rest_Route and Zend_Rest_Controller

After trying different ways to make RESTful WS in Zend Framework, I am aware that I can simply use Zend_Rest_Route and ZendRest_Controller to make "standard" RESTful WS in PHP. Especially, Zend_Rest_Route supports RESTful URI pattern like /parameterName/parameterValue/.... This is different from what I read from forums. I am using Zend Framework 1.10 in Zend Server 5.0.1 running on IBM i. Zend_Rest_Server is really not good choice to make RESTful service. Zend_Rest_Server needs to be further improved to meet the requirement of "standard" RESTful.

I tried Zend_Controller_Router_Route to implemented RESTful URI pattern. But, it is easier to use Zend_Rest_Route and Zend_Rest_Controller. Actually, Zend_Rest_Controller does nothing but defines several abstract methods, which map to relevant HTTP methods. But, our RESTful controller must extend from it. Below is the sample codes. In my environment, I can access it with this URL: http://localhost/jiaRESTfulWS/public/index/product/fish/number/100/

BootStrap class

class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
{
 //adding this function in Bootstrap class to initilize Zend_Rest_Route.
 protected function _initRestRoute() {
  //getting an instance of zend front controller.
  $frontController = Zend_Controller_Front::getInstance ();
  //initializing a Zend_Rest_Route
  $restRoute = new Zend_Rest_Route ( $frontController );
  //let all actions to use Zend_Rest_Route.
  $frontController->getRouter ()->addRoute ( 'default', $restRoute );
 }
 
}

IndexController.php

class IndexController extends Zend_Rest_Controller
{
 
 public function init() {
  $this->getHelper ( 'viewRenderer' )->setNoRender ( true ); 
 }
 
 /**
  * The index action handles index/list requests; it should respond with a
  * list of the requested resources.
  */
 public function indexAction() {
  //HTTP code 500 might not good choice here.
  $this->getResponse ()->setHttpResponseCode ( 500 );
  $this->getResponse ()->appendBody ( "no list/index allowed" );
  
 } 
 
 /**
  * The get action handles GET requests and receives an 'id' parameter; it
  * should respond with the server resource state of the resource identified
  * by the 'id' value.
  */
 public function getAction() {  
  
  //I will return result in XML format.
  $this->getResponse ()->setHeader ( 'Content-Type', 'text/xml' );
  
  //Note: the Request object here is not HttpRequest. It is Zend controller request. This is the key!
  if ($this->getRequest ()->getParam ( "product" ) != NULL and $this->getRequest ()->getParam ( "number") != NULL ) {   
   //Initializing a dummy object for return. 
   $return = new Jia_Return ();
   $return->setProducts ( $this->getRequest ()->getParam ( "product" ) );
   $return->setQuantity ( $this->getRequest ()->getParam ( "number" ) );
   //We prevent the product has been found.
   //So, we set HTTP code 200 here.  
   $this->getResponse ()->setHttpResponseCode ( 200 );
  }    
   else {
    $return= new Jia_ErrorCode('no parameters!');
    //prevent the product is not found.
    $this->getResponse ()->setHttpResponseCode ( 200 );
   }
  
  print $this->_handleStruct( $return );
 
 }
 
 /**
  * The post action handles POST requests; it should accept and digest a
  * POSTed resource representation and persist the resource state.
  */
 public function postAction() {
  
 }
 
 /**
  * The put action handles PUT requests and receives an 'id' parameter; it
  * should update the server resource state of the resource identified by
  * the 'id' value.
  */
 public function putAction() {
 
 }
 
 /**
  * The delete action handles DELETE requests and receives an 'id'
  * parameter; it should update the server resource state of the resource
  * identified by the 'id' value.
  */
 public function deleteAction() {
 
 }
 
  
 /**
  * Handle an array or object result
  *
  * @param array|object $struct Result Value
  * @return string XML Response
  */
 protected function _handleStruct($struct) {

  $dom = new DOMDocument ( '1.0', 'UTF-8' );
  
  $root = $dom->createElement ( "Jia" );
  $method = $root;
  
  $root->setAttribute ( 'generator', 'Yiyu Blog' );
  $root->setAttribute ( 'version', '1.0' );
  $dom->appendChild ( $root );
  
  $this->_structValue ( $struct, $dom, $method );
  
  $struct = ( array ) $struct;
  if (! isset ( $struct ['status'] )) {
   $status = $dom->createElement ( 'status', 'success' );
   $method->appendChild ( $status );
  }
  return $dom->saveXML ();
 }
 
 /**
  * Recursively iterate through a struct
  *
  * Recursively iterates through an associative array or object's properties
  * to build XML response.
  *
  * @param mixed $struct
  * @param DOMDocument $dom
  * @param DOMElement $parent
  * @return void
  */
 protected function _structValue($struct, DOMDocument $dom, DOMElement $parent) {
  $struct = ( array ) $struct;
  
  foreach ( $struct as $key => $value ) {
   if ($value === false) {
    $value = 0;
   } elseif ($value === true) {
    $value = 1;
   }
   
   if (ctype_digit ( ( string ) $key )) {
    $key = 'key_' . $key;
   }
   
   if (is_array ( $value ) || is_object ( $value )) {
    $element = $dom->createElement ( $key );
    $this->_structValue ( $value, $dom, $element );
   } else {
    $element = $dom->createElement ( $key );
    $element->appendChild ( $dom->createTextNode ( $value ) );
   }
   
   $parent->appendChild ( $element );
  }
 }
 
}

Return.php

/**
 * A dummy class as return objec.
 * 
 * @author Yiyu Jia
 *
 */
class Jia_Return {
 
 /**
  * 
  * @var unknown_type
  */
 public $products;
 public $quantity;
 /**
  * @return the $products
  */
 public function getProducts() {
  return $this->products;
 }

 /**
  * @param $products the $products to set
  */
 public function setProducts($products) {
  $this->products = $products;
 }
 /**
  * @return the $quantity
  */
 public function getQuantity() {
  return $this->quantity;
 }

 /**
  * @param $quantity the $quantity to set
  */
 public function setQuantity($quantity) {
  $this->quantity = $quantity;
 }
 
}

ErrorCode.php

/**
 * An dummy error code class.
 * 
 * @author Yiyu Jia
 *
 */
class Jia_ErrorCode { 
 
 public $errorCode;
 
 function __construct($errMsg){
  $this->errorCode = $errMsg;
 }  

}

ZendStudio 7.2 project file can be downloaded from here.

failure on passing SQL statement as parameters into stored procedure and the way to walk around

Recently, I got code 3 error message when I tried to call a stored procedure on IBM i DB2 through System i Navigator. The purpose of this stored procedure is to perform a series queries that are passed in as the input parameter under a predefined schema.  Below is the way to call the stored procedure,

call myStoredProcedure('update dummyTbl set columnA = 1 where columnB = ''Jia''  ')

The error message says that

Message: [SQL7008] dummyTbl in schemaA not valid for 
operation. Cause . . . . . :   The reason code is 3.  
Reason codes are: 
1 -- dummyTbl has no members. 
2 -- dummyTbl has been saved with storage free. 
3 -- dummyTbl not journaled, no authority to the 
journal, or the journal state is *STANDBY.  Files 
with an RI constraint action of CASCADE, SET NULL, 
or SET DEFAULT must be journaled to the same journal. 

The interesting thing is that I get error free when I run the same SQL query in System i Navigator. 

After doing research, I found that, on IBM i DB2, a table is journaled by default if it is created with SQL statement in i Navigator. On the other hand, the table is not journaled by default when it is created under green screen. And the walking around is adding the following statement in the stored procedure before executing the input string that is actually sql statement,

SET TRANSACTION ISOLATION LEVEL NO COMMIT

This could only be temporary walking around solution. To be serious, it is better to review tables setting about journaling and isolation level. However, for an existing complex database system, this walking around might be a good choice too.

Zend_Soap_Client and PHP http client do not share same native code

I was told by a senior PHP consultant that he can not successfully make Zend_Soap_Client call over the SSL. He runs his code on Zend Server running on i Series. Then, I told him that, if I were him, I will try to write a normal HTTP client to access the WSDL file over SSL first. The reason for me to approach in this way because,

• SOAP is built on the top HTTP. So, SSL should not be business of SOAP. In other words, SOAP client shall rely on the HTTP client implement.
• I THOUGHT that Zend_Soap_Client uses same native code as PHP http client.

However, I was wrong. He tested and found that he was able to do file_get_contents and retrieve https:// content from the server. Then, I started to trace the source code of Zend_Soap_Client. I found,

1. Zend_Soap_Client is a wrapper around PHP SOAP extension.
2. PHP SOAP is a thin layer. It quickly goes to native code at file called soap.php.
3. Native c code of soap.php has its own logical to deal with proxy and SSL. Click Here to see C source code.

So, I was wrong. I can not regard that Zend_Soap_Client can support SSL just because PHP Http client can support SSL. They probably uses different version of native http client code. From this discussion, I get deeper understanding about architecture of PHP. I will probably write a article to compare Java and PHP.

BTW, PHP 5.3 version of Zend Server 5.0.2 on iSeries does not support Zend_Soap_Client to access web service over the SSL. However, Zend_Soap_Client work with SSL when the Zend Server was rolled back to version 5.0.1 . Zend_Soap_Client coming with Zend server 5.0.4 can be used to access Web service over one-way SSL. I have not tried mutual SSL with Zend_Soap_Client yet. But, it sounds not easy.

Also, to test Zend_Soap_Client access over one-way SSL can be as simple as below,

try {
 $soap_url = 'https://your.host/webservice?wsdl'; 
 
 $soap_client = new SoapClient ( $soap_url);
 
 var_dump ( $soap_client -> __getFunctions () );
 
} catch ( Exception $e ) {
 print_r ( $e );
 exit ();
}