REG: updates/Inserts

bhaskar kalita

REG: updates/Inserts
Hi List
I have an update statement which is supposed to update 10 rows. If it gets an error while updating the 7th row, what will happen to the previous six rows considering that no ROLLBACK/COMMIT statements have been explicitly used?
Will the result differ for batch spufi and COBOL programs ? Also, will it be different for Inserts?

regards

Bhaskar


---------------------------------
Yahoo! DSL Something to write home about. Just $16.99/mo. or less

---------------------------------------------------------------------------------
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

Manas (Cognizant) Biswal

Re: REG: updates/Inserts
(in response to bhaskar kalita)

Everything will be rolled back in all cases.

Thanks,
Manas

"It is not the strongest of the species that survives, nor the most intelligent; it is the one that is most adaptable to change". - Charles Darwin


________________________________

From: DB2 Data Base Discussion List on behalf of bhaskar kalita
Sent: Tue 12/27/2005 3:56 AM
To: [login to unmask email]
Subject: [DB2-L] REG: updates/Inserts


Hi List
I have an update statement which is supposed to update 10 rows. If it gets an error while updating the 7th row, what will happen to the previous six rows considering that no ROLLBACK/COMMIT statements have been explicitly used?
Will the result differ for batch spufi and COBOL programs ? Also, will it be different for Inserts?

regards

Bhaskar

________________________________

Yahoo! DSL <http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=37474/* http://promo.yahoo.com/broadband/ > Something to write home about. Just $16.99/mo. or less --------------------------------------------------------------------------------- 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


This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly
prohibited and may be unlawful.

Visit us at http://www.cognizant.com

---------------------------------------------------------------------------------
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

David Seibert

Re: REG: updates/Inserts
(in response to Manas (Cognizant) Biswal)
Hello Bhaskar,

What happens depends upon the type of error you encounter in the update
of the 7th row.

Some negative SQLcodes will generate rollback on your behalf. But not all.
For example, -803, violating a unique index or constraint, does NOT generate
a rollback. However, -911 does.

So it is your program's responsibility to do adequate error checking and
generate rollback if necessary.
It seems to me most people casually refer to batch SPUFI and mean the sample
program DSNTEP2.
DSNTEP2 behaves as I describe above (for insert and update).

However the actual SPUFI component of DB2 will generate a rollback for
negative SQLcodes encountered.

In a your example, if I insert 6 rows successfully, and then fail on an
insert or update with -803, your COBOL program and DSNTEP2 will NOT
rollback, SPUFI will rollback the first 6 inserts.

Dave


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of bhaskar kalita
Sent: Tuesday, December 27, 2005 3:56 AM
To: [login to unmask email]
Subject: [DB2-L] REG: updates/Inserts


Hi List
I have an update statement which is supposed to update 10 rows. If it gets
an error while updating the 7th row, what will happen to the previous six
rows considering that no ROLLBACK/COMMIT statements have been explicitly
used?
Will the result differ for batch spufi and COBOL programs ? Also, will it be
different for Inserts?

regards

Bhaskar


_____

Yahoo!
<http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=37474/*http://promo.yahoo.c
om/broadband/> DSL Something to write home about. Just $16.99/mo. or less
----------------------------------------------------------------------------
----- 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 contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.


---------------------------------------------------------------------------------
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

Troy Coleman

Re: REG: updates/Inserts
(in response to David Seibert)
This is one of the reasons you need to design checkpoint/restart (commit)
into your program design so that you can have a clean commit or rollback
when you are in the middle of a logical unit of work. If you do not issue
the ROLLBACK in your program before it ends then you will have saved some of
the work but may have not completed a logical unit of work. Usually your
error handling section of the program will issue the ROLLBACK for you so
that you do not commit incomplete work. DB2 is consistent on how it handles
commit processing it is the different applications that are designed to
handle it different. The -911 and -913 are the two sqlcodes related to
DEADLOCK. The -911 will always rollback on the deadlock but the -913 will
pass control back to your application and let you retry without a rollback
or allow you to issue the rollback. The -913 will only be returned in a
CICS transaction. You can configure CICS using the DSNCRCT parameter
ROLBI=YES (-911) or NO (-913). The default is YES.



Troy Coleman, Support Engineer
IBM Certified Solutions Expert

SoftBase Systems, Inc.
847-776-0618
828-670-9900 ext. 334
[login to unmask email]

Compliance Challenged with Test Data Privacy? White Papers and More at
http://www.softbase.com/

The information contained in this message may be CONFIDENTIAL and is for the
intended addressee only. Any unauthorized use, dissemination of the
information, or copying of this message is prohibited. If you are not the
intended addressee, please notify the sender immediately and delete this
message.




_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of bhaskar kalita
Sent: Tuesday, December 27, 2005 2:56 AM
To: [login to unmask email]
Subject: [DB2-L] REG: updates/Inserts


Hi List
I have an update statement which is supposed to update 10 rows. If it gets
an error while updating the 7th row, what will happen to the previous six
rows considering that no ROLLBACK/COMMIT statements have been explicitly
used?
Will the result differ for batch spufi and COBOL programs ? Also, will it be
different for Inserts?

regards

Bhaskar


_____

Yahoo!
<http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=37474/*http://promo.yahoo.c
om/broadband/> DSL Something to write home about. Just $16.99/mo. or less
----------------------------------------------------------------------------
----- 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

bhaskar kalita

Re: REG: updates/Inserts
(in response to Troy Coleman)
Thanks dave for the update

Regards
Bhaskar

"Seibert, Dave" <[login to unmask email]> wrote:
Hello Bhaskar,

What happens depends upon the type of error you encounter in the update of the 7th row.

Some negative SQLcodes will generate rollback on your behalf. But not all. For example, -803, violating a unique index or constraint, does NOT generate a rollback. However, -911 does.

So it is your program's responsibility to do adequate error checking and generate rollback if necessary.
It seems to me most people casually refer to batch SPUFI and mean the sample program DSNTEP2.
DSNTEP2 behaves as I describe above (for insert and update).

However the actual SPUFI component of DB2 will generate a rollback for negative SQLcodes encountered.

In a your example, if I insert 6 rows successfully, and then fail on an insert or update with -803, your COBOL program and DSNTEP2 will NOT rollback, SPUFI will rollback the first 6 inserts.
Dave


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of bhaskar kalita
Sent: Tuesday, December 27, 2005 3:56 AM
To: [login to unmask email]
Subject: [DB2-L] REG: updates/Inserts


Hi List
I have an update statement which is supposed to update 10 rows. If it gets an error while updating the 7th row, what will happen to the previous six rows considering that no ROLLBACK/COMMIT statements have been explicitly used?
Will the result differ for batch spufi and COBOL programs ? Also, will it be different for Inserts?

regards

Bhaskar

---------------------------------
Yahoo! DSL Something to write home about. Just $16.99/mo. or less --------------------------------------------------------------------------------- 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 contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

--------------------------------------------------------------------------------- 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



---------------------------------
Yahoo! DSL Something to write home about. Just $16.99/mo. or less

---------------------------------------------------------------------------------
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

Roger Miller

Re: REG: updates/Inserts
(in response to bhaskar kalita)
This is called atomicity in database terminology, and it works at several
levels statement and transaction. The idea is that the statement or the
transaction is either completely made or nothing is changed. If an
individual insert, update or delete encounters an error, the results of
that statement are removed, with few exceptions (example V8 multirow
insert NOT ATOMIC option). ROLLBACK is for the entire transaction - all
change statements prior to COMMIT. A very few errors cause rollback, but
almost all have statement atomicity. For a step further, you can look at
SAVEPOINT and ROLLBACK to SAVEPOINT. The more general concept is called
nested transactions.

Roger Miller

On Tue, 27 Dec 2005 00:56:27 -0800, bhaskar kalita
<[login to unmask email]> wrote:

>Hi List
> I have an update statement which is supposed to update 10 rows. If it
gets an error while updating the 7th row, what will happen to the previous
six rows considering that no ROLLBACK/COMMIT statements have been
explicitly used?
> Will the result differ for batch spufi and COBOL programs ? Also, will
it be different for Inserts?
>
> regards
>
> Bhaskar
>
>

---------------------------------------------------------------------------------
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