DECLARE CURSOR defaults

Donna Domovic

DECLARE CURSOR defaults
Hello,

We ran in to a problem over the weekend where we were using a read-only
cursor with a join and then using that data to update one of the tables
involved in the join. The problem we had was that the newly updated row
was then read in by the cursor and processed a second time.

We were able to get around the problem by adding an ORDER BY on a non-
indexed column. Through further investigation, I believe we would also be
able to solve the problem by using INSENSITIVE on the DECLARE CURSOR
statement. (We're going to try this.)

The question we have is what is the default for the DECLARE CURSOR
statement? Is it SENSITIVE or INSENSITIVE?

Thanks in advance for your help.

Donna Domovic

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: DECLARE CURSOR defaults
(in response to Donna Domovic)
Donna

It is ALWAYS dangerous to update data OUTSIDE a cursor controlled by
what you find INSIDE a cursor. In IBM-speak "the results are
unpredictable" which translates as "although it might look like it works
now, but don't come crying to us if it suddenly stops working in the
future"

If you are processing data in a cursor then ALL the processing should be
done there.

As you say though, you are a read-only cursor so the BEST solution might
be

1. Read through the cursor a make a list of the keys you need to process
2. Open a new cursor AFTER YOU HAVE FINISHED THIS STEP based on the list
of keys you need to process further

You MIGHT be able to get it to work, but please remember what "the
results are unpredictable" means!


Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125752
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Donna Domovic
Sent: 11 December 2006 16:26
To: [login to unmask email]
Subject: [DB2-L] DECLARE CURSOR defaults

Hello,

We ran in to a problem over the weekend where we were using a read-only
cursor with a join and then using that data to update one of the tables
involved in the join. The problem we had was that the newly updated row
was then read in by the cursor and processed a second time.

We were able to get around the problem by adding an ORDER BY on a non-
indexed column. Through further investigation, I believe we would also
be able to solve the problem by using INSENSITIVE on the DECLARE CURSOR
statement. (We're going to try this.)

The question we have is what is the default for the DECLARE CURSOR
statement? Is it SENSITIVE or INSENSITIVE?

Thanks in advance for your help.

Donna Domovic

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Agus Kwee

Re: DECLARE CURSOR defaults
(in response to Phil Grainger)
Donna,

SENSITIVE and INENSITIVE are option of SCROLLABLE cursor.
Are you using scrollable cursor?

Regards,
Agus Kwee
Themis Training
http://www.themisinc.com

----- Original Message -----
From: Donna Domovic
Date: Monday, December 11, 2006 11:36 am
Subject: [DB2-L] DECLARE CURSOR defaults
To: [login to unmask email]

> Hello,
>
> We ran in to a problem over the weekend where we were using a
> read-only
> cursor with a join and then using that data to update one of the
> tablesinvolved in the join. The problem we had was that the
> newly updated row
> was then read in by the cursor and processed a second time.
>
> We were able to get around the problem by adding an ORDER BY on
> a non-
> indexed column. Through further investigation, I believe we
> would also be
> able to solve the problem by using INSENSITIVE on the DECLARE CURSOR
> statement. (We're going to try this.)
>
> The question we have is what is the default for the DECLARE CURSOR
> statement? Is it SENSITIVE or INSENSITIVE?
>
> Thanks in advance for your help.
>
> Donna Domovic
>
> -----------------------------------------------------------------
> ----------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the
> archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG
> DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins
> can be reached at [login to unmask email] Find out the
> latest on IDUG conferences at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Donna Domovic

Re: DECLARE CURSOR defaults
(in response to Agus Kwee)
Agus,

We weren't originally using scrollable cursors but I think we will need
to. We know the process works correctly and the cursor is materialized if
we use the ORDER BY on a non-indexed column but unfortunately we won't be
able to guarantee that the column we choose won't become part of an index.
By using INSENSITIVE, we're hoping we can avoid this problem reoccurring in
the future.

Is my thinking incorrect? Is INSENSITIVE not the way to go?

Thanks,
Donna Domovic

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John Miller

Re: DECLARE CURSOR defaults
(in response to Donna Domovic)
You may want to consider using a GTT rather than a Scrollable cursor
(scrollable cursor uses a GTT behind the scenes anyways).
You can do a batch insert into the GTT
i.e.
INSERT INTO SESSION.MYGTT
( COL1,
COL2,
COL3,
...
)
(
SELECT COL1,
COL2,
COL3,
...
);

Then open a cursor on the GTT. In this way your SELECT is
"materialized" (in a matter of speaking). You then don't need an order
by and you can index the column if you need to.

John

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Donna Domovic
Sent: Monday, December 11, 2006 11:55 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DECLARE CURSOR defaults

Agus,

We weren't originally using scrollable cursors but I think we will need
to. We know the process works correctly and the cursor is materialized
if
we use the ORDER BY on a non-indexed column but unfortunately we won't
be
able to guarantee that the column we choose won't become part of an
index.
By using INSENSITIVE, we're hoping we can avoid this problem reoccurring
in
the future.

Is my thinking incorrect? Is INSENSITIVE not the way to go?

Thanks,
Donna Domovic

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Agus Kwee

Re: DECLARE CURSOR defaults
(in response to John Miller)
Donna,



Since your application doesn't want  to see updates that  occur  after the cursor

is opened, and using ORDER BY for the non scrollable cursor might not invoke

the sort in case there is index on the column(s)  specified in the ORDER BY,

INSENSITIVE scrollable cursor seems to be the option that will accomplish

what you want.

I know no reason why insensitive scrollable cursor should not be used.

For DB2 z/OS Version 7 : INSENSITIVE SCROLL must be specified, there is

no default.

For DB2 z/OS Version 8 the default is ASENSITIVE SCROLL which is the same with

INSENSITIVE SCROLL if the cursor is declared as READ ONLY.



Regards,

Agus Kwee

Themis Training

http://www.themisinc.com


----- Original Message -----From: Donna Domovic Date: Monday, December 11, 2006 2:06 pmSubject: Re: [DB2-L] DECLARE CURSOR defaultsTo: [login to unmask email]> Agus,> > We weren't originally using scrollable cursors but I think we > will need> to. We know the process works correctly and the cursor is > materialized if> we use the ORDER BY on a non-indexed column but unfortunately we > won't be> able to guarantee that the column we choose won't become part of > an index.> By using INSENSITIVE, we're hoping we can avoid this problem > reoccurring in> the future.> > Is my thinking incorrect? Is INSENSITIVE not the way to go?> > Thanks,> Donna Domovic> > -----------------------------------------------------------------> ----------------> Welcome to the IDUG DB2-L list. To unsubscribe, go to the > archives and home page at http://www.idugdb2-l.org/archives/db2-> l.html. From that page select "Join or Leave the list". The IDUG > DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins > can be reached at [login to unmask email] Find out the > latest on IDUG conferences at http://conferences.idug.org/index.cfm>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm