asked on

JAVA and Cursors in DB2 Stored Procedures

Hi All,

I am a SQL Server DBA by trade - but have been drafted to help our DB2 team on a very time constrained project.

I have noticed that all the stored procedures in DB2 that return data to a Java client use cursors - this is something that I have not come across in SQLServer (cursors are rarely used).

Is is absolutely necessary to have the cursor in order to return data from the DB to the client in this fashion?

This is iSeries DB2 by the way.

Thanks for your help.

momi_sabag

Hi

no, this it not necessary
a db2 stored procedure can pass parameters, and it does not have to use cursors in order to return data,
but, you are limited with the number of parameters you can pass / return (it depends on a couple of factors)

so, if you need to write a stored procedure that takes couple and arguemnts and return a couple back, it's possible without the use of cursors
you can read about it here
http://publib.boulder.ibm.com/iseries/v5r1/ic2924/info/sqlp/rbafymst177.htm#HDRSPROEG

momi

PUMASOFT

ASKER

Hi,

Does this mean you do not need a cursor to return the results sets from a select statement in the stored producedure to the client then?

Kent Olsen

Hi pumasoft,

Cursors are an often over-used method in DB2. So many of the DB2 people are "old timers" that began the database life with DB2 on the mainframe. Programs were written in COBOL and the COBOL programs chased cursors. These people write what they know so cursors get overused.

You should be able to handle data returned from a DB2 stored procedure in a manner very similar to what you're accustomed to in SQL Server.

Kent

PUMASOFT

ASKER

Any examples of DB2 stored procedure code which do return selected records to a Java front end without using a cursor in the DB2 stored procedure would be greatly appreciated...

At this moment in time we seem to be unable to even get the sproc to run without the cursor...

I am beginning to believe that you must always define a cursor in DB2 - which seriously contradicts my SQL experience...

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_2484401

Hi Pumasoft,

You can go ehead and forget those arbitrary "rules" you learned in SQL Server. By and large, they don't apply in the iSeries world.

Cursors are nothing to be afraid of. If you want to return an actual result-set with multiple rows, then cursors are the way to do it. They are no problems with them at all.

Again, while SQL syntax may be very similar (but not identical) across different databases, the "unwritten rules" are completely different.

For instance, I once heard a SQL Server DBA tell me that you should never join more than four talbles in a SELECT. In his world, that almost guaranteed slow performance. Of course, that's a completely ridiculous rule in iSeries SQL. I write queries with over 20 tables (with millions of rows in each table), and I don't experience any slow-down at all.

Therefore, the "unwritten rule" in this DBA's head did not apply in a different database.

Now, there definitely ARE a bunch of unwritten rules in iSeries SQL (just as there is in ANY dbms), but they're not the same as the Oracle ones ... or the SQL Server ones ... etc.

HTH,
DaveSlash