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

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'));

an opened cursor can not survive between two different database connection session

I was shown a DB2 stored procedure design, which has a input parameter called "FIRST". I feel it is odd. After further enquiry, I confirm it is a wrong design.

What they expected is as below, 1) if the "FIRST" parameter is past in, it means it is the first time for calling application to call this stored procedure. 2) Therefore, the stored procedure will run and open the cursor. 3) if the "FIRST" parameter is false, it means it is not the first time for calling application to call this stored procedure. Therefore the stored procedure will "reuse" the cursor opened in the first calling.

This sounds wonderful as they told me they can avoid opening cursor frequently. Then, they can gain performance improve from it. However, I will say this is a bad design if it is not wrong because,

1) an opened cursor obviously consumes resource from DB2 server. DB2 memory cache size is limited. Furthermore, the chance for concurrency access to DB2 will be increased if each opened cursor last too long time.

2) an opened cursor should not be able to survive between two different connection session. This is actually the most risk part of this "FIRST" design because their existing Java Web app use connection pool, which can not promise always giving back same connection to servlet serving same longin customer.

So, as we can foresee, if the Web app use a different database connection to call the stored procedure without "FIRST" parameter, the stored procedure has to deal the calling as the first time call although the developer of Web app think they are "reuse" the opened cursor. In fact, the last opened cursor does not exist now.

The interesting thing is that they do not notice this problem as they configure the initialized available JDBC connection as small as one. So, the Web app is actually always using same JDBC connection although the connection is fetched from the connection pool.

So, this "FIRST" parameter for stored procedure is incorrect. The stored procedure only needs the parameters for telling stored procedure about start row and total number of rows it wants. However, in AS400 DB2 UDB, we only have function "FETCH FIRST". It does not have function like "limit", which MySQL has. However, we can use the DB2's row_num() to implement this as below,

select * from (select col1,  row_number() over() as rownum  from schemaname.tablename)end  where rownum < 10 and rownum > 6 ;

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.