Explicitly starting a transaction?

Jonathan Gennick

Explicitly starting a transaction?
Does DB2 support a statement similar to Oracle's SET
TRANSACTION, that lets you explicitly start a transaction
and specify the isolation level for that transaction? I've
looked in the DB2 docs, but if such a statement exists, it's
name is not obvious enough for me to pick it out.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[login to unmask email]

Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article,
or send email to [login to unmask email] and
include the word "subscribe" in either the subject or body.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Simon George

Re: Explicitly starting a transaction?
(in response to Jonathan Gennick)
I think the simple answer is no. In DB2 (specifically z/OS) isolation levels are supported by DB2 either in its static SQL model via the BIND ISOLATION(CS/RR/UR/RS) parameter or in the dynamic SQL model by specifying the WITH CS/RR/UR/RS on the statement. Likewise read only is specified on the SELECT statement using the FOR READ ONLY clause at the end of the statement.

In general DB2 does not co-ordinate (start) transactions. This is usually done via another product such as CICS/WEBSphere or a PC Client that manages the transaction.


Cheers Simon
[login to unmask email]
(1931) 74408 (internal)
(020) 7567 4408 (external)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Jonathan Gennick
Sent: 29 December 2003 16:03
To: [login to unmask email]
Subject: Explicitly starting a transaction?


Does DB2 support a statement similar to Oracle's SET
TRANSACTION, that lets you explicitly start a transaction
and specify the isolation level for that transaction? I've
looked in the DB2 docs, but if such a statement exists, it's
name is not obvious enough for me to pick it out.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[login to unmask email]

Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article,
or send email to [login to unmask email] and
include the word "subscribe" in either the subject or body.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore
does not accept liability for any errors or omissions in the contents
of this message which arise as a result of e-mail transmission. If
verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Cathy Taddei

Re: Explicitly starting a transaction?
(in response to Simon George)
APAR PQ47595 introduced a "new stored procedure for calling a CICS
transactions". However, this may not be the kind of transaction you're
asking about, or the right platform. A CICS transaction does not have an
isolation level; that is a characteristic of SQL statements.
HTH,
Cathy Taddei

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of [login to unmask email]
Sent: Monday, December 29, 2003 8:27 AM
To: [login to unmask email]
Subject: Re: Explicitly starting a transaction?


I think the simple answer is no. In DB2 (specifically z/OS) isolation levels
are supported by DB2 either in its static SQL model via the BIND
ISOLATION(CS/RR/UR/RS) parameter or in the dynamic SQL model by specifying
the WITH CS/RR/UR/RS on the statement. Likewise read only is specified on
the SELECT statement using the FOR READ ONLY clause at the end of the
statement.

In general DB2 does not co-ordinate (start) transactions. This is usually
done via another product such as CICS/WEBSphere or a PC Client that manages
the transaction.


Cheers Simon
[login to unmask email]
(1931) 74408 (internal)
(020) 7567 4408 (external)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Jonathan Gennick
Sent: 29 December 2003 16:03
To: [login to unmask email]
Subject: Explicitly starting a transaction?


Does DB2 support a statement similar to Oracle's SET
TRANSACTION, that lets you explicitly start a transaction
and specify the isolation level for that transaction? I've
looked in the DB2 docs, but if such a statement exists, it's
name is not obvious enough for me to pick it out.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[login to unmask email]

------------------------------------------------------------------------------

This email is confidential and may be legally privileged.

It is intended solely for the addressee. Access to this email by anyone else,
unless expressly approved by the sender or an authorized addressee, is
unauthorized.

If you are not the intended recipient, any disclosure, copying, distribution
or any action omitted or taken in reliance on it, is prohibited and may be
unlawful. If you believe that you have received this email in error, please
contact the sender, delete this e-mail and destroy all 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

tim malamphy

Re: Explicitly starting a transaction?
(in response to Cathy Taddei)
I read the original question a bit differently...
From what I know about Oracle, it sounds to me like
the gentleman is looking for something along the lines
of START UOW command...and wants to do it explicitly.
As I understand it, Oracle uses transactions like DB2
uses UOW to process commits/rollbacks, etc.
DB2 "implicitly" starts a UOW when it encounters a
SQL statement, and stops it upon COMMIT. So you could
say that executing any SQL statement (and I'm sure
there are exceptions) is an explicit way of starting a
transaction. SQL statements allow you to set the
Isolation level.

tim

As an aside, we use OTG from a Solaris server as well
as datajoiner. While I wouldn't recommend OTG for
transactional usage, or on-going replication, I must
admit it's very fast for quick for pulling in
refreshes of entire OS/390 tables over the network.

--- "Taddei, Cathy" <[login to unmask email]>
wrote:
> APAR PQ47595 introduced a "new stored procedure for
> calling a CICS
> transactions". However, this may not be the kind of
> transaction you're
> asking about, or the right platform. A CICS
> transaction does not have an
> isolation level; that is a characteristic of SQL
> statements.
> HTH,
> Cathy Taddei
>
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email]On
> Behalf Of [login to unmask email]
> Sent: Monday, December 29, 2003 8:27 AM
> To: [login to unmask email]
> Subject: Re: Explicitly starting a transaction?
>
>
> I think the simple answer is no. In DB2
> (specifically z/OS) isolation levels
> are supported by DB2 either in its static SQL model
> via the BIND
> ISOLATION(CS/RR/UR/RS) parameter or in the dynamic
> SQL model by specifying
> the WITH CS/RR/UR/RS on the statement. Likewise read
> only is specified on
> the SELECT statement using the FOR READ ONLY clause
> at the end of the
> statement.
>
> In general DB2 does not co-ordinate (start)
> transactions. This is usually
> done via another product such as CICS/WEBSphere or a
> PC Client that manages
> the transaction.
>
>
> Cheers Simon
> [login to unmask email]
> (1931) 74408 (internal)
> (020) 7567 4408 (external)
>
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email]On
> Behalf Of Jonathan Gennick
> Sent: 29 December 2003 16:03
> To: [login to unmask email]
> Subject: Explicitly starting a transaction?
>
>
> Does DB2 support a statement similar to Oracle's SET
> TRANSACTION, that lets you explicitly start a
> transaction
> and specify the isolation level for that
> transaction? I've
> looked in the DB2 docs, but if such a statement
> exists, it's
> name is not obvious enough for me to pick it out.
>
> Best regards,
>
> Jonathan Gennick --- Brighten the corner where you
> are
> http://Gennick.com * 906.387.1698 *
> mailto:[login to unmask email]
>
>
------------------------------------------------------------------------------
>
> This email is confidential and may be legally
> privileged.
>
> It is intended solely for the addressee. Access to
> this email by anyone else,
> unless expressly approved by the sender or an
> authorized addressee, is
> unauthorized.
>
> If you are not the intended recipient, any
> disclosure, copying, distribution
> or any action omitted or taken in reliance on it, is
> prohibited and may be
> unlawful. If you believe that you have received this
> email in error, please
> contact the sender, delete this e-mail and destroy
> all 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". If you
> will be out of the office, send the SET DB2-L NO
> MAIL command to [login to unmask email] 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
>


__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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: Explicitly starting a transaction?
(in response to tim malamphy)
Here are a couple more thoughts to add. There is no explicit SET
TRANSACTION statement, but there are a number of alternatives, depending
upon what you need. The isolation level in DB2 can be set on the plan, on
the package or statement by statement on the WITH clause. If you want to
finish this transaction and start another, then COMMIT does the job. If
you want some form of nesting in transactions then SAVEPOINT and ROLLBACK
to savepoint may do the job. RRS attach (recommended) and Call Attach
have techniques to start a new transaction, as do IMS & CICS. As you
designate the plan and collection of packages, the package or plan has an
ISOLATION level, which can be overridden by an explicit statement
isolation level. In some cases, stored procedures provide enough
separation, rather than a separate transaction.

Roger Miller

On Mon, 29 Dec 2003 11:03:08 -0500, Jonathan Gennick
<[login to unmask email]> wrote:

>Does DB2 support a statement similar to Oracle's SET
>TRANSACTION, that lets you explicitly start a transaction
>and specify the isolation level for that transaction? I've
>looked in the DB2 docs, but if such a statement exists, it's
>name is not obvious enough for me to pick it out.
>
>Best regards,
>
>Jonathan Gennick --- Brighten the corner where you are
>http://Gennick.com * 906.387.1698 * mailto:[login to unmask email]
>

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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