When to use cursors?

[Dima Kuchin]

Hello,

I was trying to find the information about when and where should I use
cursors in DB2, no luck. Maybe you can point me to some article that
describes just that (or tell me which page is it in DB2 SQL reference
book, if it's there)? Or maybe you can post a short answer here, if
there's no dedicated article?

Another question would be about scrollable cursors (just found out
about their existence) - what performance gains will I get by using
them over the technique I'm using right now:

<code>SELECT T.* FROM
(SELECT FOO.*, ROWNUM() as ROWNUM
FROM (some query) AS FOO
) AS T
WHERE T.ROWNUM RMIN AND T.ROWNUM < RMAX</code>

I'm using PHP via ibm_db2 PECL module, but also plan to use Python
soon (most probably via PyDB2). Considering to use stored procedures.

[Doug Foulds]

On Jun 28, 12:19 am, Dima Kuchin <[e-mail]:

Hello,

I was trying to find the information about when and where should I use
cursors in DB2, no luck. Maybe you can point me to some article that
describes just that (or tell me which page is it in DB2 SQL reference
book, if it's there)? Or maybe you can post a short answer here, if
there's no dedicated article?

Another question would be about scrollable cursors (just found out
about their existence) - what performance gains will I get by using
them over the technique I'm using right now:

<code>SELECT T.* FROM
(SELECT FOO.*, ROWNUM() as ROWNUM
FROM (some query) AS FOO
) AS T
WHERE T.ROWNUM RMIN AND T.ROWNUM < RMAX</code>

I'm using PHP via ibm_db2 PECL module, but also plan to use Python
soon (most probably via PyDB2). Considering to use stored procedures.

Dima

I'd suggest you start with the following information in the DB2 9
Information Center:

When to use cursors (particularly scollable cursors):
http://publib.boulder.ibm.com/infoce...c/t0005656.htm

Cursors and SQL procedures:
http://publib.boulder.ibm.com/infoce...c/c0024361.htm

Cursors and CLI applications:
http://publib.boulder.ibm.com/infoce...c/c0007645.htm

Differences between scrollable vs. "forward-only" cursors:
http://publib.boulder.ibm.com/infoce...c/c0007649.htm

Cursors and performance considerations (specifically CS isolation
level):
http://publib.boulder.ibm.com/infoce...c/t0005656.htm

Hope this helps.