Datastage extracts & UniVerse Transaction Logging problem

Post by **admin** » Tue Nov 18, 2003 1:56 pm

Does Datastage start a database transaction which remains open until the
extract job is complete?
Why? Is there any way to turn off this "feature"?

We use Datastage to extract data from our Universe(UV) database. My own
expertise, if any, is with Universe, not Datastage, so be gentle.

I have correlated
(a) UV transaction logs that fill, and get left in a "Need Sync" state
(with a corresponding message in the uvchkd.info file saying "...1
pending transaction(s)...",
with
(b) Datastage extracts that start when that log is "current" and have
not finished until well after that log has filled.

When the DS job finishes, the log(s) progress to their normal "Full" or
"Released" status.

We have one extract that runs for several hours and during that time the
UV db will fill 1-2GB worth of logs. They ALL remain in the "Need Sync"
state until the Datastage job finishes. If we actually crashed during
that time, we'd have to apply 1-2GB worth of logs during warmstart
recovery. Also, these logs cannot be archived to tape. More mischief
if the datastage job ever aborted for any reason.

Is there an for this datastage-users list that I could have searched for
this?

Thank-you in advance,

Charles Stevenson
DBA
BP/IS

Getronics
836 North Street
Tewksbury, MA 01876
Tel: 978-858-6872
Fax: 978-858-8631
charles.stevenson@getronics.com
www.getronics.com/us The information transmitted is intended only for
use by the addressee and may contain confidential and or or privileged
material. Any review, re-transmission, dissemination or other use of it,
or the taking of any action in reliance upon this information by persons
and or or entities other than the intended recipient is prohibited. If
you received this in error, please inform the sender and/or addressee
immediately and delete the material. Thank you.

Post by **admin** » Tue Nov 18, 2003 3:04 pm

Visit www.dsxchange.com for a full archive of Oliver
posts, plus Ray Wurlod is a resident expert on the
site. He's just about the best when it comes to
Universe.

--- "Stevenson, Charles"
wrote:
> Does Datastage start a database transaction which
> remains open until the
> extract job is complete?
> Why? Is there any way to turn off this "feature"?
>
> We use Datastage to extract data from our
> Universe(UV) database. My own
> expertise, if any, is with Universe, not Datastage,
> so be gentle.
>
> I have correlated
> (a) UV transaction logs that fill, and get left in
> a "Need Sync" state
> (with a corresponding message in the uvchkd.info
> file saying "...1
> pending transaction(s)...",
> with
> (b) Datastage extracts that start when that log is
> "current" and have
> not finished until well after that log has filled.
>
> When the DS job finishes, the log(s) progress to
> their normal "Full" or
> "Released" status.
>
> We have one extract that runs for several hours and
> during that time the
> UV db will fill 1-2GB worth of logs. They ALL
> remain in the "Need Sync"
> state until the Datastage job finishes. If we
> actually crashed during
> that time, we'd have to apply 1-2GB worth of logs
> during warmstart
> recovery. Also, these logs cannot be archived to
> tape. More mischief
> if the datastage job ever aborted for any reason.
>
> Is there an for this datastage-users list that I
> could have searched for
> this?
>
> Thank-you in advance,
>
> Charles Stevenson
> DBA
> BP/IS
>
> Getronics
> 836 North Street
> Tewksbury, MA 01876
> Tel: 978-858-6872
> Fax: 978-858-8631
> charles.stevenson@getronics.com
> www.getronics.com/us The information transmitted is
> intended only for
> use by the addressee and may contain confidential
> and or or privileged
> material. Any review, re-transmission, dissemination
> or other use of it,
> or the taking of any action in reliance upon this
> information by persons
> and or or entities other than the intended recipient
> is prohibited. If
> you received this in error, please inform the sender
> and/or addressee
> immediately and delete the material. Thank you.
>

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Post by **admin** » Tue Nov 18, 2003 3:24 pm

Chuck,

What version of UniVerse, what version of DataStage? Are you using a UV stage or a Hashed File stage to perform the extraction? (I'm guessing the former.) What is the setting of "transaction isolation level" in this stage?

One "solution" would be to reduce the transcation isolation level to the lowest possible (from memory "read uncommitted"). Another "solution" would be to use a Hashed File stage to extract the rows; this does not use a transaction.

I'm really astounded that so much transaction activity is being logged by what ought to be a simple SELECT, with no data being changed! Or is there something you didn't tell?

There are two main sites for DataStage, this one (datastage-users@oliver.com) and a Forum at www.dsxchange.com.

Regards,
Ray

----- Original Message -----
From: "Stevenson, Charles"
Date: Tue, 18 Nov 2003 14:56:49 -0500
To:
Subject: Datastage extracts & UniVerse Transaction Logging problem

> Does Datastage start a database transaction which remains open until the
> extract job is complete?
> Why? Is there any way to turn off this "feature"?
>
> We use Datastage to extract data from our Universe(UV) database. My own
> expertise, if any, is with Universe, not Datastage, so be gentle.
>
> I have correlated
> (a) UV transaction logs that fill, and get left in a "Need Sync" state
> (with a corresponding message in the uvchkd.info file saying "...1
> pending transaction(s)...",
> with
> (b) Datastage extracts that start when that log is "current" and have
> not finished until well after that log has filled.
>
> When the DS job finishes, the log(s) progress to their normal "Full" or
> "Released" status.
>
> We have one extract that runs for several hours and during that time the
> UV db will fill 1-2GB worth of logs. They ALL remain in the "Need Sync"
> state until the Datastage job finishes. If we actually crashed during
> that time, we'd have to apply 1-2GB worth of logs during warmstart
> recovery. Also, these logs cannot be archived to tape. More mischief
> if the datastage job ever aborted for any reason.
>
> Is there an for this datastage-users list that I could have searched for
> this?
>
> Thank-you in advance,
>
> Charles Stevenson
> DBA
> BP/IS
>
> Getronics
> 836 North Street
> Tewksbury, MA 01876
> Tel: 978-858-6872
> Fax: 978-858-8631
> charles.stevenson@getronics.com
> www.getronics.com/us The information transmitted is intended only for
> use by the addressee and may contain confidential and or or privileged
> material. Any review, re-transmission, dissemination or other use of it,
> or the taking of any action in reliance upon this information by persons
> and or or entities other than the intended recipient is prohibited. If
> you received this in error, please inform the sender and/or addressee
> immediately and delete the material. Thank you.
>

Post by **admin** » Tue Nov 18, 2003 4:39 pm

Ray,
My response interspersed with your post.
cds

> -----Original Message-----
> From: Ray Wurlod [mailto:rayw@mindless.com]
>
> Chuck,
>
> What version of UniVerse, what version of DataStage?

UV 10.0.16
DS 4.2.1

> Are you
> using a UV stage or a Hashed File stage to perform the
> extraction? (I'm guessing the former.)

Correct, UV Stage.

> What is the setting
> of "transaction isolation level" in this stage?

For UV: ISOMODE = 1
For DS: "transaction isolation level" not set = NONE

> One "solution" would be to reduce the transaction isolation
> level to the lowest possible (from memory "read
> uncommitted").

The DS guy here says "NONE" means "The default isolation level for the
database is used." But he says he can set it to "Read Uncommitted."

> Another "solution" would be to use a Hashed
> File stage to extract the rows; this does not use a transaction.

Meaning UV Stage DOES use a transaction??? Ascential support is
telling us it does not.
IBM is telling me that the message I see in uvchkd.info:
"Mon Nov 17 23:37:32 2003: (Info) Log file 3635 has 1 pending
transaction(s)."
Means that a "BEGIN TRANSACTION" has happened, but no COMMIT or ROLLBACK
yet.
All evidence points to the Datastage jobs. I can go into excruciating
detail if anyone wishes.

> I'm really astounded that so much transaction activity is
> being logged by what ought to be a simple SELECT, with no
> data being changed! Or is there something you didn't tell?

The UV transaction volume is not from Datastage, but from other
processing (especially batch nighttime updates) concurrent with the
Datastage extract. Does that make more sense?
Whatever txlog happens to be current when a long running datastage
extract starts, and all subsequent logs that fill up while it is
running, gets kept in "Need Sync" state until the long datastage job
finishes. At that point uvchkd daemon does its thing, and everyone is
happy again. No intervention needed. But I am uncomfortable leaving up
to 40 50MB (~2GB) txlogs in this "Need Sync" state for so long. I am
not positive what would happen if we crashed or if the DS job terminated
abnormally.

> There are two main sites for DataStage, this one
> (datastage-users@oliver.com) and a Forum at www.dsxchange.com.

So, should I post this same question at www.dsxchange.com or do the
oliver list posts end up there anyway?

>
> Regards,
> Ray

Many thanks as usual,
Chuck

> ----- Original Message -----
> From: "Stevenson, Charles"
> Date: Tue, 18 Nov 2003 14:56:49 -0500
> To:
> Subject: Datastage extracts & UniVerse Transaction Logging problem
>
> > Does Datastage start a database transaction which remains
> open until
> > the extract job is complete? Why? Is there any way to turn
> off this
> > "feature"?
> >
> > We use Datastage to extract data from our Universe(UV)
> database. My
> > own expertise, if any, is with Universe, not Datastage, so
> be gentle.
> >
> > I have correlated
> > (a) UV transaction logs that fill, and get left in a "Need Sync"
> > state (with a corresponding message in the uvchkd.info file saying
> > "...1 pending transaction(s)...", with
> > (b) Datastage extracts that start when that log is
> "current" and have
> > not finished until well after that log has filled.
> >
> > When the DS job finishes, the log(s) progress to their
> normal "Full"
> > or "Released" status.
> >
> > We have one extract that runs for several hours and during
> that time
> > the UV db will fill 1-2GB worth of logs. They ALL remain
> in the "Need Sync"
> > state until the Datastage job finishes. If we actually
> crashed during
> > that time, we'd have to apply 1-2GB worth of logs during warmstart
> > recovery. Also, these logs cannot be archived to tape.
> More mischief
> > if the datastage job ever aborted for any reason.
> >
> > Is there an for this datastage-users list that I could have
> searched
> > for this?
> >
> > Thank-you in advance,
> >
> > Charles Stevenson

Post by **admin** » Tue Nov 18, 2003 4:50 pm

I forgot to mention we have NO logical transactions in our system.
Our application code does not have any BEGIN TRANSACTION / COMMIT /
ROLLBACK / END TRANSACTION code in it (yet).

> IBM is telling me that the message I see in uvchkd.info:
> "Mon Nov 17 23:37:32 2003: (Info) Log file 3635 has 1 pending
transaction(s)."
> means that a "BEGIN TRANSACTION" has
> happened, but no COMMIT or ROLLBACK yet. All evidence points
> to the Datastage jobs. I can go into excruciating detail if
> anyone wishes.

Post by **admin** » Tue Nov 18, 2003 7:36 pm

OK, what's going on here is as follows (imho).

You are running DataStage and UniVerse on separate machines. I know this because DataStage 4.2 is actually UniVerse 9.5 which cannot be co-resident on a machine with UniVerse 10.x.

DataStage is using BCI functions, which connect to the UniVerse instance via UCI. This is almost certainly where the implicit transaction is being started. The "transaction" will be held open until the extraction operation completes.

It is surprising that a transaction is being started when transaction isolation level is set to NONE. This could be a bug but, with such an old release of DataStage, I seriously doubt that you'll get it addressed by Ascential.

Can you try using an NFS-mounted disk (or a shared drive if you're on Windows), set ALLOWNFS in the DataStage uvconfig (and do the uvregen thing, stop and start DataStage), then access the UniVerse file via that mechanism? I can't think of a workaround that would change the BCI/UCI behaviour.

Is there any way you can make the extraction from UniVerse run faster? For example, if there is a WHERE clause, would indexing those columns in UniVerse help?

To answer your other question, this list is archived daily to www.dsxchange.com
----- Original Message -----
From: "Stevenson, Charles"
Date: Tue, 18 Nov 2003 17:39:52 -0500
To: "Ray Wurlod" ,
Subject: RE: Datastage extracts & UniVerse Transaction Logging problem

> Ray,
> My response interspersed with your post.
> cds
>
> > -----Original Message-----
> > From: Ray Wurlod [mailto:rayw@mindless.com]
> >
> > Chuck,
> >
> > What version of UniVerse, what version of DataStage?
>
> UV 10.0.16
> DS 4.2.1
>
> > Are you
> > using a UV stage or a Hashed File stage to perform the
> > extraction? (I'm guessing the former.)
>
> Correct, UV Stage.
>
> > What is the setting
> > of "transaction isolation level" in this stage?
>
> For UV: ISOMODE = 1
> For DS: "transaction isolation level" not set = NONE
>
> > One "solution" would be to reduce the transaction isolation
> > level to the lowest possible (from memory "read
> > uncommitted").
>
> The DS guy here says "NONE" means "The default isolation level for the
> database is used." But he says he can set it to "Read Uncommitted."
>
> > Another "solution" would be to use a Hashed
> > File stage to extract the rows; this does not use a transaction.
>
> Meaning UV Stage DOES use a transaction??? Ascential support is
> telling us it does not.
> IBM is telling me that the message I see in uvchkd.info:
> "Mon Nov 17 23:37:32 2003: (Info) Log file 3635 has 1 pending
> transaction(s)."
> Means that a "BEGIN TRANSACTION" has happened, but no COMMIT or ROLLBACK
> yet.
> All evidence points to the Datastage jobs. I can go into excruciating
> detail if anyone wishes.
>
> > I'm really astounded that so much transaction activity is
> > being logged by what ought to be a simple SELECT, with no
> > data being changed! Or is there something you didn't tell?

>
> The UV transaction volume is not from Datastage, but from other
> processing (especially batch nighttime updates) concurrent with the
> Datastage extract. Does that make more sense?
> Whatever txlog happens to be current when a long running datastage
> extract starts, and all subsequent logs that fill up while it is
> running, gets kept in "Need Sync" state until the long datastage job
> finishes. At that point uvchkd daemon does its thing, and everyone is
> happy again. No intervention needed. But I am uncomfortable leaving up
> to 40 50MB (~2GB) txlogs in this "Need Sync" state for so long. I am
> not positive what would happen if we crashed or if the DS job terminated
> abnormally.
>
> > There are two main sites for DataStage, this one
> > (datastage-users@oliver.com) and a Forum at www.dsxchange.com.
>
> So, should I post this same question at www.dsxchange.com or do the
> oliver list posts end up there anyway?
>
> >
> > Regards,
> > Ray
>
> Many thanks as usual,
> Chuck
>
> > ----- Original Message -----
> > From: "Stevenson, Charles"
> > Date: Tue, 18 Nov 2003 14:56:49 -0500
> > To:
> > Subject: Datastage extracts & UniVerse Transaction Logging problem
> >
> > > Does Datastage start a database transaction which remains
> > open until
> > > the extract job is complete? Why? Is there any way to turn
> > off this
> > > "feature"?
> > >
> > > We use Datastage to extract data from our Universe(UV)
> > database. My
> > > own expertise, if any, is with Universe, not Datastage, so
> > be gentle.
> > >
> > > I have correlated
> > > (a) UV transaction logs that fill, and get left in a "Need Sync"
> > > state (with a corresponding message in the uvchkd.info file saying
> > > "...1 pending transaction(s)...", with
> > > (b) Datastage extracts that start when that log is
> > "current" and have
> > > not finished until well after that log has filled.
> > >
> > > When the DS job finishes, the log(s) progress to their
> > normal "Full"
> > > or "Released" status.
> > >
> > > We have one extract that runs for several hours and during
> > that time
> > > the UV db will fill 1-2GB worth of logs. They ALL remain
> > in the "Need Sync"
> > > state until the Datastage job finishes. If we actually
> > crashed during
> > > that time, we'd have to apply 1-2GB worth of logs during warmstart
> > > recovery. Also, these logs cannot be archived to tape.
> > More mischief
> > > if the datastage job ever aborted for any reason.
> > >
> > > Is there an for this datastage-users list that I could have
> > searched
> > > for this?
> > >
> > > Thank-you in advance,
> > >
> > > Charles Stevenson
>
> --
> ________________________________________________________
> Unsubscribe: mailto:u2-users-unsubscribe@oliver.com
> or mailto:moderator@oliver.com
> Moderator : mailto:moderator@oliver.com
> Web-site : http://www.oliver.com/lists/u2
>
>

Post by **admin** » Thu Nov 20, 2003 2:00 pm

Thanks, Ray Wurlod, for pushing me in the right direction.
My testing has convinced me that DS does start a transaction on the
target UV system and holds it open until the extract job ends, although
Ascential support assured us that is not true.

At least, the version we're running does so, for the way we are running
the extracts.

Options include:

- Upgrading DS. We're on the near-end-of-life version 4.2.1.
Maybe an upgrade fixes this. Ascential hasn't told us so, but then
again, they told us it never was a problem. They did say there was
once a similar problem extracting from Informix but it was fixed at
3.x.

- Trying "Hashed File stage" instead of "UV Stage" (Ray's idea).

- Tightening up the extracts by doing some logic on UV side ahead
of time, reducing the execution time of the longer DS extract jobs.

- (not Ray's NFS idea. I don't want to go there.)

- (Explicitly setting iso mode to "read uncommitted" didn't work.)

I'll post our final answer once we have it.

Sincerely,

Chuck Stevenson

> -----Original Message-----
> From: Ray Wurlod [mailto:rayw@mindless.com]
> Sent: Tuesday, November 18, 2003 8:37 PM
> To: datastage-users@oliver.com
> Cc: u2-users@oliver.com
> Subject: RE: Datastage extracts & UniVerse Transaction Logging problem
>
>
> OK, what's going on here is as follows (imho). . . .

Post by **admin** » Thu Nov 20, 2003 2:07 pm

If the file is not too large, for one client I ftp'd
the file over to the DS machine and then used the hash
file stage. It was basically a snapshot of the file.
Word of advice if you do this, copy the file locally
first, that way you have a near-instantaneous
snapshot, otherwise the ftp session may take too long
and you have issues with DATA and OVER files out of
sync.

Good luck!
-Ken

--- "Stevenson, Charles"
wrote:
> Thanks, Ray Wurlod, for pushing me in the right
> direction.
> My testing has convinced me that DS does start a
> transaction on the
> target UV system and holds it open until the extract
> job ends, although
> Ascential support assured us that is not true.
>
> At least, the version we're running does so, for the
> way we are running
> the extracts.
>
>
> Options include:
>
> - Upgrading DS. We're on the near-end-of-life
> version 4.2.1.
> Maybe an upgrade fixes this. Ascential hasn't
> told us so, but then
> again, they told us it never was a problem.
> They did say there was
> once a similar problem extracting from Informix
> but it was fixed at
> 3.x.
>
> - Trying "Hashed File stage" instead of "UV Stage"
> (Ray's idea).
>
> - Tightening up the extracts by doing some logic
> on UV side ahead
> of time, reducing the execution time of the
> longer DS extract jobs.
>
> - (not Ray's NFS idea. I don't want to go there.)
>
> - (Explicitly setting iso mode to "read
> uncommitted" didn't work.)
>
>
> I'll post our final answer once we have it.
>
> Sincerely,
>
> Chuck Stevenson
>
>
> > -----Original Message-----
> > From: Ray Wurlod [mailto:rayw@mindless.com]
> > Sent: Tuesday, November 18, 2003 8:37 PM
> > To: datastage-users@oliver.com
> > Cc: u2-users@oliver.com
> > Subject: RE: Datastage extracts & UniVerse
> Transaction Logging problem
> >
> >
> > OK, what's going on here is as follows (imho). . .
..

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree