Hello everybody,
I would like to know if the update action "Truncate table then insert rows" used in the OCI stage, puts locks on the table before starting the insert (Oracle NO WAIT command). We have a customer whose job aborted with the Oracle message ORA-00054 (resource busy and acquire with NOWAIT specified). They use the transaction isolation Read-committed too. Does anybody know the differences between Read-committed and serializable options? I read the help and the two definitions are quiet identical!
Thanks a lot in advance
Regards Patrizia
DataStage Fatal error caused by ORA-00054
Hi, im no expert but, when you truncate a table you are deleting all the
records in that table, so the data base engine needs to lock the table in
order to clean the table, you must wait for the truncate instruction to
finish.
Please correct-me if im wrong.
Cheers:
Pedro
>From: "Patrizia Vitali"
>Reply-To:
>To:
>Subject: DataStage Fatal error caused by ORA-00054
>Date: Tue, 6 Nov 2001 12:45:48 +0100
>
>Hello everybody,
>I would like to know if the update action "Truncate table then insert
>rows" used in the OCI stage, puts locks on the table before starting
>the insert (Oracle NO WAIT command). We have a customer whose job
>aborted with the Oracle message ORA-00054 (resource busy and acquire
>with NOWAIT specified). They use the transaction isolation
>Read-committed too. Does anybody know the differences between
>Read-committed and serializable options? I read the help
>and the two definitions are quiet identical!
> Thanks a lot in advance
> Regards Patrizia
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
records in that table, so the data base engine needs to lock the table in
order to clean the table, you must wait for the truncate instruction to
finish.
Please correct-me if im wrong.
Cheers:
Pedro
>From: "Patrizia Vitali"
>Reply-To:
>To:
>Subject: DataStage Fatal error caused by ORA-00054
>Date: Tue, 6 Nov 2001 12:45:48 +0100
>
>Hello everybody,
>I would like to know if the update action "Truncate table then insert
>rows" used in the OCI stage, puts locks on the table before starting
>the insert (Oracle NO WAIT command). We have a customer whose job
>aborted with the Oracle message ORA-00054 (resource busy and acquire
>with NOWAIT specified). They use the transaction isolation
>Read-committed too. Does anybody know the differences between
>Read-committed and serializable options? I read the help
>and the two definitions are quiet identical!
> Thanks a lot in advance
> Regards Patrizia
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
Hi Pedro,
the problem is that our customer uses the OCI option truncate then insert rows. He doesnt use truncate and insert in two separated stages.
Thanks Patrizia
----- Original Message -----
From: "pedro santos"
To:
Sent: Tuesday, November 06, 2001 12:58 PM
Subject: Re: DataStage Fatal error caused by ORA-00054
> Hi, im no expert but, when you truncate a table you are deleting all
> the records in that table, so the data base engine needs to lock the
> table in order to clean the table, you must wait for the truncate
> instruction to finish. Please correct-me if im wrong.
> Cheers:
> Pedro
>
>
> >From: "Patrizia Vitali"
> >Reply-To:
> >To:
> >Subject: DataStage Fatal error caused by ORA-00054
> >Date: Tue, 6 Nov 2001 12:45:48 +0100
> >
> >Hello everybody,
> >I would like to know if the update action "Truncate table then insert
rows"
> >used in the OCI stage, puts locks on the table before starting the
> >insert (Oracle NO WAIT command). We have a customer whose job aborted
> >with the Oracle message ORA-00054 (resource busy and acquire with
> >NOWAIT
specified).
> >They use the transaction isolation Read-committed too. Does anybody
> >know the differences between Read-committed and serializable options?
> >I read the help
> >and the two definitions are quiet identical!
> > Thanks a lot in advance
> > Regards Patrizia
> >
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at
> http://explorer.msn.com/intl.asp
>
the problem is that our customer uses the OCI option truncate then insert rows. He doesnt use truncate and insert in two separated stages.
Thanks Patrizia
----- Original Message -----
From: "pedro santos"
To:
Sent: Tuesday, November 06, 2001 12:58 PM
Subject: Re: DataStage Fatal error caused by ORA-00054
> Hi, im no expert but, when you truncate a table you are deleting all
> the records in that table, so the data base engine needs to lock the
> table in order to clean the table, you must wait for the truncate
> instruction to finish. Please correct-me if im wrong.
> Cheers:
> Pedro
>
>
> >From: "Patrizia Vitali"
> >Reply-To:
> >To:
> >Subject: DataStage Fatal error caused by ORA-00054
> >Date: Tue, 6 Nov 2001 12:45:48 +0100
> >
> >Hello everybody,
> >I would like to know if the update action "Truncate table then insert
rows"
> >used in the OCI stage, puts locks on the table before starting the
> >insert (Oracle NO WAIT command). We have a customer whose job aborted
> >with the Oracle message ORA-00054 (resource busy and acquire with
> >NOWAIT
specified).
> >They use the transaction isolation Read-committed too. Does anybody
> >know the differences between Read-committed and serializable options?
> >I read the help
> >and the two definitions are quiet identical!
> > Thanks a lot in advance
> > Regards Patrizia
> >
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at
> http://explorer.msn.com/intl.asp
>
Do you use the truncate statement in an user defined sql query? If so you may try removing it from the statement and chose a new update
action thats under the general tab in the OCI stage, i guess the option you
must choose is replace existing rows completely.
Please correct-me if im wrong.
Best regards
Pedro Santos
>From: "Patrizia Vitali"
>Reply-To:
>To:
>Subject: Re: DataStage Fatal error caused by ORA-00054
>Date: Tue, 6 Nov 2001 13:12:28 +0100
>
>Hi Pedro,
>the problem is that our customer uses the OCI option truncate then
>insert rows. He doesnt use truncate and insert in two separated stages.
> Thanks Patrizia
>
>----- Original Message -----
>From: "pedro santos"
>To:
>Sent: Tuesday, November 06, 2001 12:58 PM
>Subject: Re: DataStage Fatal error caused by ORA-00054
>
>
> > Hi, im no expert but, when you truncate a table you are deleting all
> > the records in that table, so the data base engine needs to lock the
> > table
>in
> > order to clean the table, you must wait for the truncate instruction
> > to finish. Please correct-me if im wrong.
> > Cheers:
> > Pedro
> >
> >
> > >From: "Patrizia Vitali"
> > >Reply-To:
> > >To:
> > >Subject: DataStage Fatal error caused by ORA-00054
> > >Date: Tue, 6 Nov 2001 12:45:48 +0100
> > >
> > >Hello everybody,
> > >I would like to know if the update action "Truncate table then
> > >insert
>rows"
> > >used in the OCI stage, puts locks on the table before starting the
>insert
> > >(Oracle NO WAIT command). We have a customer whose job aborted with
> > >the Oracle message ORA-00054 (resource busy and acquire with NOWAIT
>specified).
> > >They use the transaction isolation Read-committed too. Does anybody
>know
> > >the
> > >differences between Read-committed and serializable options? I read
> > >the help and the two definitions are quiet identical!
> > > Thanks a lot in advance
> > > Regards Patrizia
> > >
> >
> >
> > _________________________________________________________________
> > Get your FREE download of MSN Explorer at
>http://explorer.msn.com/intl.asp
> >
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
action thats under the general tab in the OCI stage, i guess the option you
must choose is replace existing rows completely.
Please correct-me if im wrong.
Best regards
Pedro Santos
>From: "Patrizia Vitali"
>Reply-To:
>To:
>Subject: Re: DataStage Fatal error caused by ORA-00054
>Date: Tue, 6 Nov 2001 13:12:28 +0100
>
>Hi Pedro,
>the problem is that our customer uses the OCI option truncate then
>insert rows. He doesnt use truncate and insert in two separated stages.
> Thanks Patrizia
>
>----- Original Message -----
>From: "pedro santos"
>To:
>Sent: Tuesday, November 06, 2001 12:58 PM
>Subject: Re: DataStage Fatal error caused by ORA-00054
>
>
> > Hi, im no expert but, when you truncate a table you are deleting all
> > the records in that table, so the data base engine needs to lock the
> > table
>in
> > order to clean the table, you must wait for the truncate instruction
> > to finish. Please correct-me if im wrong.
> > Cheers:
> > Pedro
> >
> >
> > >From: "Patrizia Vitali"
> > >Reply-To:
> > >To:
> > >Subject: DataStage Fatal error caused by ORA-00054
> > >Date: Tue, 6 Nov 2001 12:45:48 +0100
> > >
> > >Hello everybody,
> > >I would like to know if the update action "Truncate table then
> > >insert
>rows"
> > >used in the OCI stage, puts locks on the table before starting the
>insert
> > >(Oracle NO WAIT command). We have a customer whose job aborted with
> > >the Oracle message ORA-00054 (resource busy and acquire with NOWAIT
>specified).
> > >They use the transaction isolation Read-committed too. Does anybody
>know
> > >the
> > >differences between Read-committed and serializable options? I read
> > >the help and the two definitions are quiet identical!
> > > Thanks a lot in advance
> > > Regards Patrizia
> > >
> >
> >
> > _________________________________________________________________
> > Get your FREE download of MSN Explorer at
>http://explorer.msn.com/intl.asp
> >
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
Pedro,
There is an option in the OCI stage "Truncate table then insert rows". It is quite different to "Replace rows completely".
Patrizia,
The truncate and insert option will cause a truncate (which is different to deleting all rows) to be executed before the first row is written. Im not sure of the timing, that is, whether the truncate happens when the transform stage (at the other end of the link) initialises or when the first row is actually presented to it.
If another process has a lock on the table (typically an uncommitted update, or a select ... for update, which I dont believe DataStage ever does) when you attempt the truncate, you will definitely get the ORA-00054 error. If the error is occurring on the link which does the truncate/insert, then you will have to find out what else has the table (or rows) locked.
As for the transaction isolation, yes the manual/help does not tell you much. I have ALWAYS used read committed. If someone (who actually knows, not just guessing) could give definite answer about what the serializable option does, I think we might all learn something.
Which version of DataStage are you using? The version (of the ORAOCI8
plugin) makes a difference to how transactions are handled.
-----Original Message-----
From: pedro santos [mailto:pedro_f_santos@hotmail.com]
Sent: Tuesday, 6 November 2001 10:40 PM
To: datastage-users@oliver.com
Subject: Re: DataStage Fatal error caused by ORA-00054
Do you use the truncate statement in an user defined sql query? If so you may try removing it from the statement and chose a new update action thats under the general tab in the OCI stage, i guess the option you must choose is replace existing rows completely. Please correct-me if im wrong. Best regards Pedro Santos
>From: "Patrizia Vitali"
>Reply-To:
>To:
>Subject: Re: DataStage Fatal error caused by ORA-00054
>Date: Tue, 6 Nov 2001 13:12:28 +0100
>
>Hi Pedro,
>the problem is that our customer uses the OCI option truncate then
>insert rows. He doesnt use truncate and insert in two separated stages.
> Thanks Patrizia
>
>----- Original Message -----
>From: "pedro santos"
>To:
>Sent: Tuesday, November 06, 2001 12:58 PM
>Subject: Re: DataStage Fatal error caused by ORA-00054
>
>
> > Hi, im no expert but, when you truncate a table you are deleting all
> > the records in that table, so the data base engine needs to lock the
> > table
>in
> > order to clean the table, you must wait for the truncate instruction
> > to finish. Please correct-me if im wrong.
> > Cheers:
> > Pedro
> >
> >
> > >From: "Patrizia Vitali"
> > >Reply-To:
> > >To:
> > >Subject: DataStage Fatal error caused by ORA-00054
> > >Date: Tue, 6 Nov 2001 12:45:48 +0100
> > >
> > >Hello everybody,
> > >I would like to know if the update action "Truncate table then
> > >insert
>rows"
> > >used in the OCI stage, puts locks on the table before starting the
>insert
> > >(Oracle NO WAIT command). We have a customer whose job aborted with
> > >the Oracle message ORA-00054 (resource busy and acquire with NOWAIT
>specified).
> > >They use the transaction isolation Read-committed too. Does anybody
>know
> > >the
> > >differences between Read-committed and serializable options? I read
> > >the help and the two definitions are quiet identical!
> > > Thanks a lot in advance
> > > Regards Patrizia
> > >
> >
> >
> > _________________________________________________________________
> > Get your FREE download of MSN Explorer at
>http://explorer.msn.com/intl.asp
> >
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
There is an option in the OCI stage "Truncate table then insert rows". It is quite different to "Replace rows completely".
Patrizia,
The truncate and insert option will cause a truncate (which is different to deleting all rows) to be executed before the first row is written. Im not sure of the timing, that is, whether the truncate happens when the transform stage (at the other end of the link) initialises or when the first row is actually presented to it.
If another process has a lock on the table (typically an uncommitted update, or a select ... for update, which I dont believe DataStage ever does) when you attempt the truncate, you will definitely get the ORA-00054 error. If the error is occurring on the link which does the truncate/insert, then you will have to find out what else has the table (or rows) locked.
As for the transaction isolation, yes the manual/help does not tell you much. I have ALWAYS used read committed. If someone (who actually knows, not just guessing) could give definite answer about what the serializable option does, I think we might all learn something.
Which version of DataStage are you using? The version (of the ORAOCI8
plugin) makes a difference to how transactions are handled.
-----Original Message-----
From: pedro santos [mailto:pedro_f_santos@hotmail.com]
Sent: Tuesday, 6 November 2001 10:40 PM
To: datastage-users@oliver.com
Subject: Re: DataStage Fatal error caused by ORA-00054
Do you use the truncate statement in an user defined sql query? If so you may try removing it from the statement and chose a new update action thats under the general tab in the OCI stage, i guess the option you must choose is replace existing rows completely. Please correct-me if im wrong. Best regards Pedro Santos
>From: "Patrizia Vitali"
>Reply-To:
>To:
>Subject: Re: DataStage Fatal error caused by ORA-00054
>Date: Tue, 6 Nov 2001 13:12:28 +0100
>
>Hi Pedro,
>the problem is that our customer uses the OCI option truncate then
>insert rows. He doesnt use truncate and insert in two separated stages.
> Thanks Patrizia
>
>----- Original Message -----
>From: "pedro santos"
>To:
>Sent: Tuesday, November 06, 2001 12:58 PM
>Subject: Re: DataStage Fatal error caused by ORA-00054
>
>
> > Hi, im no expert but, when you truncate a table you are deleting all
> > the records in that table, so the data base engine needs to lock the
> > table
>in
> > order to clean the table, you must wait for the truncate instruction
> > to finish. Please correct-me if im wrong.
> > Cheers:
> > Pedro
> >
> >
> > >From: "Patrizia Vitali"
> > >Reply-To:
> > >To:
> > >Subject: DataStage Fatal error caused by ORA-00054
> > >Date: Tue, 6 Nov 2001 12:45:48 +0100
> > >
> > >Hello everybody,
> > >I would like to know if the update action "Truncate table then
> > >insert
>rows"
> > >used in the OCI stage, puts locks on the table before starting the
>insert
> > >(Oracle NO WAIT command). We have a customer whose job aborted with
> > >the Oracle message ORA-00054 (resource busy and acquire with NOWAIT
>specified).
> > >They use the transaction isolation Read-committed too. Does anybody
>know
> > >the
> > >differences between Read-committed and serializable options? I read
> > >the help and the two definitions are quiet identical!
> > > Thanks a lot in advance
> > > Regards Patrizia
> > >
> >
> >
> > _________________________________________________________________
> > Get your FREE download of MSN Explorer at
>http://explorer.msn.com/intl.asp
> >
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
Hi Pedro,
no unfortunatly our customer uses the update action "Truncate table then insert rows" under the OCI stage. He doesnt use user defined SQL query.
Thanks Patrizia
----- Original Message -----
From: "pedro santos"
To:
Sent: Tuesday, November 06, 2001 1:40 PM
Subject: Re: DataStage Fatal error caused by ORA-00054
> Do you use the truncate statement in an user defined sql query? If so
> you may try removing it from the statement and chose a new update
> action thats under the general tab in the OCI stage, i guess the
> option
you
> must choose is replace existing rows completely.
> Please correct-me if im wrong.
> Best regards
> Pedro Santos
>
>
> >From: "Patrizia Vitali"
> >Reply-To:
> >To:
> >Subject: Re: DataStage Fatal error caused by ORA-00054
> >Date: Tue, 6 Nov 2001 13:12:28 +0100
> >
> >Hi Pedro,
> >the problem is that our customer uses the OCI option truncate then
> >insert rows. He doesnt use truncate and insert in two separated stages.
> > Thanks Patrizia
> >
> >----- Original Message -----
> >From: "pedro santos"
> >To:
> >Sent: Tuesday, November 06, 2001 12:58 PM
> >Subject: Re: DataStage Fatal error caused by ORA-00054
> >
> >
> > > Hi, im no expert but, when you truncate a table you are deleting
> > > all
the
> > > records in that table, so the data base engine needs to lock the
> > > table
> >in
> > > order to clean the table, you must wait for the truncate
> > > instruction
to
> > > finish.
> > > Please correct-me if im wrong.
> > > Cheers:
> > > Pedro
> > >
> > >
> > > >From: "Patrizia Vitali"
> > > >Reply-To:
> > > >To:
> > > >Subject: DataStage Fatal error caused by ORA-00054
> > > >Date: Tue, 6 Nov 2001 12:45:48 +0100
> > > >
> > > >Hello everybody,
> > > >I would like to know if the update action "Truncate table then
> > > >insert
> >rows"
> > > >used in the OCI stage, puts locks on the table before starting
> > > >the
> >insert
> > > >(Oracle NO WAIT command). We have a customer whose job aborted
> > > >with
the
> > > >Oracle message ORA-00054 (resource busy and acquire with NOWAIT
> >specified).
> > > >They use the transaction isolation Read-committed too. Does
> > > >anybody
> >know
> > > >the
> > > >differences between Read-committed and serializable options? I
> > > >read
the
> > > >help
> > > >and the two definitions are quiet identical!
> > > > Thanks a lot in advance
> > > > Regards Patrizia
> > > >
> > >
> > >
> > > _________________________________________________________________
> > > Get your FREE download of MSN Explorer at
> >http://explorer.msn.com/intl.asp
> > >
> >
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at
> http://explorer.msn.com/intl.asp
>
>
no unfortunatly our customer uses the update action "Truncate table then insert rows" under the OCI stage. He doesnt use user defined SQL query.
Thanks Patrizia
----- Original Message -----
From: "pedro santos"
To:
Sent: Tuesday, November 06, 2001 1:40 PM
Subject: Re: DataStage Fatal error caused by ORA-00054
> Do you use the truncate statement in an user defined sql query? If so
> you may try removing it from the statement and chose a new update
> action thats under the general tab in the OCI stage, i guess the
> option
you
> must choose is replace existing rows completely.
> Please correct-me if im wrong.
> Best regards
> Pedro Santos
>
>
> >From: "Patrizia Vitali"
> >Reply-To:
> >To:
> >Subject: Re: DataStage Fatal error caused by ORA-00054
> >Date: Tue, 6 Nov 2001 13:12:28 +0100
> >
> >Hi Pedro,
> >the problem is that our customer uses the OCI option truncate then
> >insert rows. He doesnt use truncate and insert in two separated stages.
> > Thanks Patrizia
> >
> >----- Original Message -----
> >From: "pedro santos"
> >To:
> >Sent: Tuesday, November 06, 2001 12:58 PM
> >Subject: Re: DataStage Fatal error caused by ORA-00054
> >
> >
> > > Hi, im no expert but, when you truncate a table you are deleting
> > > all
the
> > > records in that table, so the data base engine needs to lock the
> > > table
> >in
> > > order to clean the table, you must wait for the truncate
> > > instruction
to
> > > finish.
> > > Please correct-me if im wrong.
> > > Cheers:
> > > Pedro
> > >
> > >
> > > >From: "Patrizia Vitali"
> > > >Reply-To:
> > > >To:
> > > >Subject: DataStage Fatal error caused by ORA-00054
> > > >Date: Tue, 6 Nov 2001 12:45:48 +0100
> > > >
> > > >Hello everybody,
> > > >I would like to know if the update action "Truncate table then
> > > >insert
> >rows"
> > > >used in the OCI stage, puts locks on the table before starting
> > > >the
> >insert
> > > >(Oracle NO WAIT command). We have a customer whose job aborted
> > > >with
the
> > > >Oracle message ORA-00054 (resource busy and acquire with NOWAIT
> >specified).
> > > >They use the transaction isolation Read-committed too. Does
> > > >anybody
> >know
> > > >the
> > > >differences between Read-committed and serializable options? I
> > > >read
the
> > > >help
> > > >and the two definitions are quiet identical!
> > > > Thanks a lot in advance
> > > > Regards Patrizia
> > > >
> > >
> > >
> > > _________________________________________________________________
> > > Get your FREE download of MSN Explorer at
> >http://explorer.msn.com/intl.asp
> > >
> >
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at
> http://explorer.msn.com/intl.asp
>
>
Thanks a lot David,
well check if other processes occur to the Oracle error.
In any case the DataStage version is 4.1.1 and the OCI version is ORAOCI8i 2.3
Regards Patrizia
----- Original Message -----
From: "David Barham"
To:
Sent: Tuesday, November 06, 2001 1:57 PM
Subject: RE: DataStage Fatal error caused by ORA-00054
> Pedro,
>
> There is an option in the OCI stage "Truncate table then insert rows".
> It is quite different to "Replace rows completely".
>
> Patrizia,
>
> The truncate and insert option will cause a truncate (which is
> different
to
> deleting all rows) to be executed before the first row is written.
> Im
not
> sure of the timing, that is, whether the truncate happens when the
transform
> stage (at the other end of the link) initialises or when the first row
> is actually presented to it.
>
> If another process has a lock on the table (typically an uncommitted
update,
> or a select ... for update, which I dont believe DataStage ever does)
when
> you attempt the truncate, you will definitely get the ORA-00054 error.
> If the error is occurring on the link which does the truncate/insert,
> then
you
> will have to find out what else has the table (or rows) locked.
>
> As for the transaction isolation, yes the manual/help does not tell
> you much. I have ALWAYS used read committed. If someone (who
> actually knows, not just guessing) could give definite answer about
> what the serializable option does, I think we might all learn
> something.
>
> Which version of DataStage are you using? The version (of the ORAOCI8
> plugin) makes a difference to how transactions are handled.
>
> -----Original Message-----
> From: pedro santos [mailto:pedro_f_santos@hotmail.com]
> Sent: Tuesday, 6 November 2001 10:40 PM
> To: datastage-users@oliver.com
> Subject: Re: DataStage Fatal error caused by ORA-00054
>
> Do you use the truncate statement in an user defined sql query? If so
> you may try removing it from the statement and chose a new update
> action thats under the general tab in the OCI stage, i guess the
> option
you
> must choose is replace existing rows completely.
> Please correct-me if im wrong.
> Best regards
> Pedro Santos
>
>
> >From: "Patrizia Vitali"
> >Reply-To:
> >To:
> >Subject: Re: DataStage Fatal error caused by ORA-00054
> >Date: Tue, 6 Nov 2001 13:12:28 +0100
> >
> >Hi Pedro,
> >the problem is that our customer uses the OCI option truncate then
> >insert rows. He doesnt use truncate and insert in two separated stages.
> > Thanks Patrizia
> >
> >----- Original Message -----
> >From: "pedro santos"
> >To:
> >Sent: Tuesday, November 06, 2001 12:58 PM
> >Subject: Re: DataStage Fatal error caused by ORA-00054
> >
> >
> > > Hi, im no expert but, when you truncate a table you are deleting
> > > all
the
> > > records in that table, so the data base engine needs to lock the
> > > table
> >in
> > > order to clean the table, you must wait for the truncate
> > > instruction
to
> > > finish.
> > > Please correct-me if im wrong.
> > > Cheers:
> > > Pedro
> > >
> > >
> > > >From: "Patrizia Vitali"
> > > >Reply-To:
> > > >To:
> > > >Subject: DataStage Fatal error caused by ORA-00054
> > > >Date: Tue, 6 Nov 2001 12:45:48 +0100
> > > >
> > > >Hello everybody,
> > > >I would like to know if the update action "Truncate table then
> > > >insert
> >rows"
> > > >used in the OCI stage, puts locks on the table before starting
> > > >the
> >insert
> > > >(Oracle NO WAIT command). We have a customer whose job aborted
> > > >with
the
> > > >Oracle message ORA-00054 (resource busy and acquire with NOWAIT
> >specified).
> > > >They use the transaction isolation Read-committed too. Does
> > > >anybody
> >know
> > > >the
> > > >differences between Read-committed and serializable options? I
> > > >read
the
> > > >help
> > > >and the two definitions are quiet identical!
> > > > Thanks a lot in advance
> > > > Regards Patrizia
> > > >
> > >
> > >
> > > _________________________________________________________________
> > > Get your FREE download of MSN Explorer at
> >http://explorer.msn.com/intl.asp
> > >
> >
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at
> http://explorer.msn.com/intl.asp
>
well check if other processes occur to the Oracle error.
In any case the DataStage version is 4.1.1 and the OCI version is ORAOCI8i 2.3
Regards Patrizia
----- Original Message -----
From: "David Barham"
To:
Sent: Tuesday, November 06, 2001 1:57 PM
Subject: RE: DataStage Fatal error caused by ORA-00054
> Pedro,
>
> There is an option in the OCI stage "Truncate table then insert rows".
> It is quite different to "Replace rows completely".
>
> Patrizia,
>
> The truncate and insert option will cause a truncate (which is
> different
to
> deleting all rows) to be executed before the first row is written.
> Im
not
> sure of the timing, that is, whether the truncate happens when the
transform
> stage (at the other end of the link) initialises or when the first row
> is actually presented to it.
>
> If another process has a lock on the table (typically an uncommitted
update,
> or a select ... for update, which I dont believe DataStage ever does)
when
> you attempt the truncate, you will definitely get the ORA-00054 error.
> If the error is occurring on the link which does the truncate/insert,
> then
you
> will have to find out what else has the table (or rows) locked.
>
> As for the transaction isolation, yes the manual/help does not tell
> you much. I have ALWAYS used read committed. If someone (who
> actually knows, not just guessing) could give definite answer about
> what the serializable option does, I think we might all learn
> something.
>
> Which version of DataStage are you using? The version (of the ORAOCI8
> plugin) makes a difference to how transactions are handled.
>
> -----Original Message-----
> From: pedro santos [mailto:pedro_f_santos@hotmail.com]
> Sent: Tuesday, 6 November 2001 10:40 PM
> To: datastage-users@oliver.com
> Subject: Re: DataStage Fatal error caused by ORA-00054
>
> Do you use the truncate statement in an user defined sql query? If so
> you may try removing it from the statement and chose a new update
> action thats under the general tab in the OCI stage, i guess the
> option
you
> must choose is replace existing rows completely.
> Please correct-me if im wrong.
> Best regards
> Pedro Santos
>
>
> >From: "Patrizia Vitali"
> >Reply-To:
> >To:
> >Subject: Re: DataStage Fatal error caused by ORA-00054
> >Date: Tue, 6 Nov 2001 13:12:28 +0100
> >
> >Hi Pedro,
> >the problem is that our customer uses the OCI option truncate then
> >insert rows. He doesnt use truncate and insert in two separated stages.
> > Thanks Patrizia
> >
> >----- Original Message -----
> >From: "pedro santos"
> >To:
> >Sent: Tuesday, November 06, 2001 12:58 PM
> >Subject: Re: DataStage Fatal error caused by ORA-00054
> >
> >
> > > Hi, im no expert but, when you truncate a table you are deleting
> > > all
the
> > > records in that table, so the data base engine needs to lock the
> > > table
> >in
> > > order to clean the table, you must wait for the truncate
> > > instruction
to
> > > finish.
> > > Please correct-me if im wrong.
> > > Cheers:
> > > Pedro
> > >
> > >
> > > >From: "Patrizia Vitali"
> > > >Reply-To:
> > > >To:
> > > >Subject: DataStage Fatal error caused by ORA-00054
> > > >Date: Tue, 6 Nov 2001 12:45:48 +0100
> > > >
> > > >Hello everybody,
> > > >I would like to know if the update action "Truncate table then
> > > >insert
> >rows"
> > > >used in the OCI stage, puts locks on the table before starting
> > > >the
> >insert
> > > >(Oracle NO WAIT command). We have a customer whose job aborted
> > > >with
the
> > > >Oracle message ORA-00054 (resource busy and acquire with NOWAIT
> >specified).
> > > >They use the transaction isolation Read-committed too. Does
> > > >anybody
> >know
> > > >the
> > > >differences between Read-committed and serializable options? I
> > > >read
the
> > > >help
> > > >and the two definitions are quiet identical!
> > > > Thanks a lot in advance
> > > > Regards Patrizia
> > > >
> > >
> > >
> > > _________________________________________________________________
> > > Get your FREE download of MSN Explorer at
> >http://explorer.msn.com/intl.asp
> > >
> >
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at
> http://explorer.msn.com/intl.asp
>