OCI - Array Size Vs Transaction Size

Archive of postings to DataStageUsers@Oliver.com. This forum intended only as a reference and cannot be posted to.

Moderators: chulett, rschirm

Locked
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

OCI - Array Size Vs Transaction Size

Post by admin »

Hi All

Im a bit confused with the above. The help implies, to me at least, that "Array size" indicates the transaction size in Oracle ie the number of rows to commited by Oracle at a time. The "Transaction size" is the number of rows DataStage process before sending it to Oracle. Can someone confirm this is true? I think it should be the opposite.

Cheers
Xiong

DISCLAIMER: This electronic message together with any attachments is
confidential. If you are not the intended recipient, do not copy, disclose or
use the contents in any way. Please also advise us by return e-mail that you
have received the message and then please destroy. Carter Holt Harvey is not
responsible for any changes made to this message and / or any attachments after
sending by Carter Holt Harvey. We use virus scanning software but exclude all
liability for viruses or anything similar in this email or any attachment.
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

It is the opposite of what you have described.

Array size is the number of rows transferred between DataStage an Oracle at a time. It applies to both input and output. For reading, Oracle will build up the required number of transactions before sending them to DataStage. On output, DataStage will buffer these up before sending them to Oracle. In our situation, DataStage is on the same server as Oracle, so we generally have array size set to 1. However, for Oracle databases on other servers, we generally use a larger value for array size to improve efficiency over the network.

Transaction size is the Oracle transaction size, that is the number rows DataStage will process before it issues a commit to Oracle. Note that this is controlled by DataStage, not Oracle. Be aware that multiple links to the same OCI stage are treated by Oracle as a single session, so that a commit for any one of them is a commit for all of them. Unless there is a compelling reason and a very large number of rows being processed by a job, I prefer to leave transaction size set to 0 so that it commits on completion. That way, if the job fails, the updates will be rolled back. Our rollback tablespace is very large, so this is not a problem. Having said that, our jobs are designed such that they could be rerun repeatedly whether or not a previous failed run was rolled back or not.

-----Original Message-----
From: Li, Xiong (Corporate Manukau) [SMTP:Xiong.Li@chh.co.nz]
Sent: Friday, August 03, 2001 9:26 AM
To: datastage-users@oliver.com
Subject: OCI - Array Size Vs Transaction Size

Hi All

Im a bit confused with the above. The help implies, to me at least, that "Array size" indicates the transaction size in Oracle ie the number of rows to commited by Oracle at a time. The "Transaction size" is the number of rows DataStage process before sending it to Oracle. Can someone confirm this is true? I think it should be the opposite.

Cheers
Xiong

DISCLAIMER: This electronic message together with any attachments is
confidential. If you are not the intended recipient, do not copy, disclose or
use the contents in any way. Please also advise us by return e-mail that you
have received the message and then please destroy. Carter Holt Harvey is not
responsible for any changes made to this message and / or any attachments after
sending by Carter Holt Harvey. We use virus scanning software but exclude all
liability for viruses or anything similar in this email or any attachment.


*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in
error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else

*************************************************************************
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Thanks David. Thats exactly what I thought it is! But the DataStage Help confused me! Have you read it?

> -----Original Message-----
> From: David Barham [SMTP:David.Barham@Anglocoal.com.au]
> Sent: Friday, 3 August 2001 11:38
> To: datastage-users@oliver.com
> Subject: RE: OCI - Array Size Vs Transaction Size
>
> It is the opposite of what you have described.
>
> Array size is the number of rows transferred between DataStage an
> Oracle at a time. It applies to both input and output. For reading,
> Oracle will build up the required number of transactions before
> sending them to DataStage. On output, DataStage will buffer these up
> before sending them to
> Oracle. In our situation, DataStage is on the same server as Oracle, so
> we
> generally have array size set to 1. However, for Oracle databases on
> other
> servers, we generally use a larger value for array size to improve
> efficiency over the network.
>
> Transaction size is the Oracle transaction size, that is the number
> rows DataStage will process before it issues a commit to Oracle. Note
> that this is controlled by DataStage, not Oracle. Be aware that
> multiple links to the
> same OCI stage are treated by Oracle as a single session, so that a commit
> for any one of them is a commit for all of them. Unless there is a
> compelling reason and a very large number of rows being processed by a
> job,
> I prefer to leave transaction size set to 0 so that it commits on
> completion. That way, if the job fails, the updates will be rolled back.
> Our rollback tablespace is very large, so this is not a problem. Having
> said that, our jobs are designed such that they could be rerun repeatedly
> whether or not a previous failed run was rolled back or not.
>
> -----Original Message-----
> From: Li, Xiong (Corporate Manukau) [SMTP:Xiong.Li@chh.co.nz]
> Sent: Friday, August 03, 2001 9:26 AM
> To: datastage-users@oliver.com
> Subject: OCI - Array Size Vs Transaction Size
>
> Hi All
>
> Im a bit confused with the above. The help implies, to me at least,
> that "Array size" indicates the transaction size in Oracle ie the
> number of rows to commited by Oracle at a time. The "Transaction size"
> is the number of rows DataStage process before sending it to Oracle.
> Can someone confirm this
> is true? I think it should be the opposite.
>
> Cheers
> Xiong
>
> DISCLAIMER: This electronic message together with any attachments is
> confidential. If you are not the intended recipient, do not copy,
> disclose
> or
> use the contents in any way. Please also advise us by return e-mail that
> you
> have received the message and then please destroy. Carter Holt Harvey is
> not
> responsible for any changes made to this message and / or any attachments
> after
> sending by Carter Holt Harvey. We use virus scanning software but exclude
> all
> liability for viruses or anything similar in this email or any attachment.
>
>
> **********************************************************************
> ***
> This e-mail and any files transmitted with it may be confidential
> and are intended solely for the use of the individual or entity to
> whom they are addressed. If you have received this e-mail in
> error, please notify the sender by return e-mail, and delete this
> e-mail from your in-box. Do not copy it to anybody else
>
> **********************************************************************
> ***

DISCLAIMER: This electronic message together with any attachments is
confidential. If you are not the intended recipient, do not copy, disclose or
use the contents in any way. Please also advise us by return e-mail that you
have received the message and then please destroy. Carter Holt Harvey is not
responsible for any changes made to this message and / or any attachments after
sending by Carter Holt Harvey. We use virus scanning software but exclude all
liability for viruses or anything similar in this email or any attachment.
Locked