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 ;