|
View previous topic :: View next topic
|
| Author |
Message |
beepravin
New User
Joined: 29 Sep 2008
Posts: 4
Location: Chennai
|
|
|
|
Hi ,
As per my requirment, I have to read the records from the table and after some process, I have to update the values in the same table. So, in this case, how I have to declare the cursor?
Ex - The table "CONTRACT" will have 2 fields 'Policy' and the 'premium' fileds. The program will read the record and after some process it will update the 'premium' filed with new values for particular 'policy'.
So Could anybody provide any help??
Regards,
Praveen |
|
| Back to top |
|
 |
murugan_mf
Active User
.jpg)
Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India
|
|
|
|
Praveen,
The cursor declaration should be as
I am assuming policy field as a primary key.
| Code: |
Declare contract_csr cursor for
Select
policy
,premium
From
contract
For update of premium |
After fetching each row from the cursor and processing, do an update on
premium field based on policy. |
|
| Back to top |
|
|
beepravin
New User
Joined: 29 Sep 2008
Posts: 4
Location: Chennai
|
|
|
|
Many Thanks Murugan :-)
Regards,
Praveen. |
|
| Back to top |
|
|
rajesh_mbt
New User
Joined: 27 Mar 2006
Posts: 97
Location: India
|
|
|
|
Hi Murugan,
I am not clear with the
| Code: |
For Update Of premimum...
|
Can you bit explain the difference between with and without using For update in the cursor? |
|
| Back to top |
|
|
srikanthkailasam
New User
Joined: 13 Nov 2006
Posts: 24
Location: Bengalooru
|
|
|
|
Hi Rajesh,
FOR UPDATE lets you to update only the field that is mentioned. This helps in preventing any unwanted/unintentional updates.
Please correct me if I am wrong. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
From the DB2 Vsn8 Appl Prog & Sql Guide,
2.2.1.1 Step 1: Declare the cursor
| Quote: |
DB2 must do more processing when you use the FOR UPDATE clause without a column list than when you use the FOR UPDATE clause with a column list.
Therefore, if you intend to update only a few columns of a table, your program can run more efficiently if you include a column list.
The precompiler options NOFOR and STDSQL affect the use of the FOR UPDATE clause in static SQL statements. For information about these options, see Table 59 in topic 5.1.1.1.6.
If you do not specify the FOR UPDATE clause in a DECLARE CURSOR statement, and you do not specify the STDSQL(YES) option or the NOFOR precompiler options, you receive an error if you execute a positioned UPDATE statement.
|
in short,
you need the FOR UPDATE clause in a cursor definition to differentiate
between a READ-ONLY cursor
and one that is to be used to UPDATE a column(s).
If you are not going to update all columns selected,
it is more efficient to list the columns to be updated. |
|
| Back to top |
|
|
rajesh_mbt
New User
Joined: 27 Mar 2006
Posts: 97
Location: India
|
|
|
|
Great!!! I leant one of the way of DB2 fine tunning 
Thanks a lot Dick and Srikanth |
|
| Back to top |
|
|
|
|
