Using set transaction in Before SQL

[admin]

Yusufi,

David Barham is correct below.

Using "SET TRANSACTION USE ROLLBACK SEGMENT" will address the problem. This
statement should be set on the UPDATE, DELETE or INSERTS which is running
concurrent to your select. Oracle uses System Control Numbers (SCN) to
manage read consistency. When A SELECT is issued the most current SCN s
used. This SCN is used to compare against the SCN in each data block within
the data files for the tables selected against, those with smaller or equal
SCNs are read, those with larger blocks are not read (since the data
contained within them has been modified since the select began), instead
data in the log files are used. Each log file has both a low SCN and high
SCN, which allows Oracle to determine which log files are needed to create a
snapshot of the data as it existed at the time the select began. When the
lowest SCN of the log files is greater (due to log switches which overwrite
the data in the logs) than the SCN acquired at the beginning of the select,
Oracle issues the ORA-01555: snapshot to old.

Adding SET TRANSACTION USE ROLLBACK SEGMENT" to precede the DML statements
which are adding, deleting or modifying the data your DataStage job is
trying to read would be optimal. Other options are rescheduling your
DataStage job to a time where the adds, deletes or modifications are mot
occurring. or increasing either the number or size of the rollback segments.

Don


Using set transaction in Before SQL
6696 by: Nagdi, Yusufi, ALABS
6697 by: gxganes.regence.com
6698 by: David Barham
6699 by: J. Schatz JMS Data Management Inc
------------------------------

Date: Wed, 1 Oct 2003 15:22:05 -0400
To:
From: "Nagdi, Yusufi, ALABS"
Subject: Using set transaction in Before SQL
Message-ID:


I am using ORAOCI9 stage for loading data into a table. I was getting =
snapshot too old: rollback segment number 11 with name "SYSRBS" too =
small. DBA team asked me to use a speific rollback segment for this. I =
used the SET TRANSACTION USE ROLLBACK SEGMENT XXX statement in the =
Before SQL but it has no effect - looks like it is taking the Before =
SQL as a diferent session and I am still getting the same error.

Any ideas on how to overcome this. D/S ver is 6 on Sun Solaris.

Thanks,
Yusufi
PS. The transaction size in the OCI stage properties is set to 1000 !!

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

Date: Wed, 1 Oct 2003 12:27:26 -0700
To: datastage-users@oliver.com
From: gxganes@regence.com
Subject: Re: Using set transaction in Before SQL
Message-ID:

This would normally happen if you are reading the table on which you are
inserting or updating from within datastage. See if you can avoid that by
creating a hash lookup or something like that.



"Nagdi, Yusufi,

ALABS" To:
datastage-users@oliver.com
cc:

Subject: Using set
transaction in Before SQL
10/01/2003 12:22 PM

Please respond to

datastage-users



|-------------------|

| [ ] Secure E-mail |

|-------------------|




I am using ORAOCI9 stage for loading data into a table. I was getting
snapshot too old: rollback segment number 11 with name "SYSRBS" too
small. DBA team asked me to use a speific rollback segment for this. I
used the SET TRANSACTION USE ROLLBACK SEGMENT XXX statement in the
Before SQL but it has no effect - looks like it is taking the Before
SQL as a diferent session and I am still getting the same error.

Any ideas on how to overcome this. D/S ver is 6 on Sun Solaris.

Thanks,
Yusufi
PS. The transaction size in the OCI stage properties is set to 1000 !!

===========================================================================
IMPORTANT NOTICE: This communication, including any attachment, contains
information that may be confidential or privileged, and is intended solely
for the entity or individual to whom it is addressed. If you are not the
intended recipient, you should delete this message and are hereby notified
that any disclosure, copying, or distribution of this message is strictly
prohibited. Nothing in this email, including any attachment, is intended
to be a legally binding signature.

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

Date: Thu, 02 Oct 2003 06:43:27 +1000
To: datastage-users@oliver.com
From: David Barham
Subject: RE: Using set transaction in Before SQL
Message-id:

If you are getting this error, you are the one doing the reading. It is the
transaction that is doing the updating that needs to set a rollback segment.
You need to find out what updates are happening at the same time as your
read process and either avoid them or get them to set a rollback segment.

Maybe you could consider undo tablespaces instead of rollback segments -
talk to your DBA. He may not be happy about that though.

-----Original Message-----
From: Nagdi, Yusufi, ALABS [mailto:nagdi@att.com]
Sent: Thursday, 2 October 2003 5:22 AM
To: datastage-users@oliver.com
Subject: Using set transaction in Before SQL

I am using ORAOCI9 stage for loading data into a table. I was getting
snapshot too old: rollback segment number 11 with name "SYSRBS" too small.
DBA team asked me to use a speific rollback segment for this. I used the
SET TRANSACTION USE ROLLBACK SEGMENT XXX statement in the Before SQL but
it has no effect - looks like it is taking the Before SQL as a diferent
session and I am still getting the same error.

Any ideas on how to overcome this. D/S ver is 6 on Sun Solaris.

Thanks,
Yusufi
PS. The transaction size in the OCI stage properties is set to 1000 !!

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

Date: Wed, 01 Oct 2003 17:29:41 -0700
To: datastage-users@oliver.com
From: "J. Schatz JMS Data Management Inc"
Subject: Re: Using set transaction in Before SQL
Message-ID:

Your Undo (rollback segment) is too small - have your dba increase the size.

Nagdi, Yusufi, ALABS wrote:

>I am using ORAOCI9 stage for loading data into a table. I was getting
snapshot too old: rollback segment number 11 with name "SYSRBS" too small.
DBA team asked me to use a speific rollback segment for this. I used the
SET TRANSACTION USE ROLLBACK SEGMENT XXX statement in the Before SQL but
it has no effect - looks like it is taking the Before SQL as a diferent
session and I am still getting the same error.
>
>Any ideas on how to overcome this. D/S ver is 6 on Sun Solaris.
>
>Thanks,
>Yusufi
>PS. The transaction size in the OCI stage properties is set to 1000 !!
>
>
>

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

End of datastage-users Digest
***********************************


This electronic message contains information from BT Americas, Inc., which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us immediately by telephone 703-707-4500.