Using set transaction in Before SQL

[admin]

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

[admin]

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.

[admin]

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

[admin]

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