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