Logical log for Temp table
Question: are operations on temporary tables written to the logical log, given temp tables aren't recovered? Answers: yes, by default they are, because rollback of a temp table requires logging; you can avoid it with CREATE TEMP TABLE ... WITH NO LOG (or SELECT ... INTO TEMP ... WITH NO LOG), and IDS 11 adds a server-wide TEMPTAB_NOLOG parameter (use with care). A side point that extent allocation is always logged was corrected: temp tables in dbspaces created with onspaces -t don't log extent allocation either, since such spaces are rebuilt at initialization — behaviour seen as far back as 7.31.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
\
On Jan 29, 5:03 am, falcons...@gmail.com wrote:
> From informix manual, I found that any logical database operation will
> be logged in logical log. However, will any logical operation on
> temporary table be logged too in logical log. I ask this question as
> it is non sense to log logical operation for temp table as it will not
> be recovered when recovery is required.
>
> Thanks for any reply.
It depends. If you create the temporary table with logging then it
will log if you create it without logging it won't
Here are the two syntax types:
create temp table(
....
) with no log;
create temp table(
) ;
select * from <table> into temp <temp table> with no log;
select * from <table> into temp <temp table> ;
falconsoon@gmail.com wrote: > From informix manual, I found that any logical database operation will > be logged in logical log. However, will any logical operation on > temporary table be logged too in logical log. I ask this question as > it is non sense to log logical operation for temp table as it will not > be recovered when recovery is required. If the temp table is not created "without logging", then it will be logged. The reason is that rollbacks on the temp table can not be performed unless the temp table is logged. > > Thanks for any reply.
falconsoon@gmail.com wrote: > From informix manual, I found that any logical database operation will > be logged in logical log. However, will any logical operation on > temporary table be logged too in logical log. I ask this question as > it is non sense to log logical operation for temp table as it will not > be recovered when recovery is required. > > Thanks for any reply. Besides the answers you already got, it's worth mentioning that IDS 11 (maybe late 10.x versions?) introduced a global parameter called TEMPTAB_NOLOG which can be used to disable temp table logging. This can have serious impacts on your applications, so be careful if you want to use it. Doc: http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.adref.doc/adref196.htm?resultof=%22%74%65%6d%70%74%61%62%5f%6e%6f%6c%6f%67%22%20 Regards, -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
It should be noted that extent allocation is always logged - even for non-logging tables. Pre-creation and sizing of a temp table can help performance quite a bit. cheers j. "He's right. No one can deny the results of No Child Left Behind, because thanks to the program, no one has any basic reasoning skills." -- The Onion -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of Madison Pruet Sent: Tuesday, January 29, 2008 10:25 AM To: informix-list@iiug.org Subject: Re: Logical log for Temp table falconsoon@gmail.com wrote: > From informix manual, I found that any logical database operation will > be logged in logical log. However, will any logical operation on > temporary table be logged too in logical log. I ask this question as > it is non sense to log logical operation for temp table as it will not > be recovered when recovery is required. If the temp table is not created "without logging", then it will be logged. The reason is that rollbacks on the temp table can not be performed unless the temp table is logged. > > Thanks for any reply. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
On Jan 29, 7:29 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> It should be noted that extent allocation is always logged - even for
> non-logging tables. Pre-creation and sizing of a temp table can help
> performance quite a bit.
>
> cheers
> j.
>
> "He's right. No one can deny the results of No Child Left Behind, because
> thanks to the program, no one has any basic reasoning skills." -- The Onion
>
>
Actually temp tables created in dbspaces flagged and created as
"temp" (used the -t flag in onspaces) don't even log extent allocation
because at initialization time the entire dbspaces structure (chunk
free list page, tblspace tblspace, etc...) is recreated as if the
dbspace had just been added.
Jacques
Finally got a chance to test this out (10.00.TC5I1). Indeed you are
correct. Is this new behaviour?
cheers
j.
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of jprenaut@yahoo.com
Sent: Wednesday, January 30, 2008 10:06 AM
To: informix-list@iiug.org
Subject: Re: Logical log for Temp table
On Jan 29, 7:29 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> It should be noted that extent allocation is always logged - even for
> non-logging tables. Pre-creation and sizing of a temp table can help
> performance quite a bit.
>
> cheers
> j.
>
> "He's right. No one can deny the results of No Child Left Behind, because
> thanks to the program, no one has any basic reasoning skills." -- The
Onion
>
>
Actually temp tables created in dbspaces flagged and created as
"temp" (used the -t flag in onspaces) don't even log extent allocation
because at initialization time the entire dbspaces structure (chunk
free list page, tblspace tblspace, etc...) is recreated as if the
dbspace had just been added.
Jacques
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
On Feb 3, 10:49 am, "Jack Parker" <jack.park...@verizon.net> wrote:
> Finally got a chance to test this out (10.00.TC5I1). Indeed you are
> correct. Is this new behaviour?
>
> cheers
> j.
>
> -----Original Message-----
> From: informix-list-boun...@iiug.org
>
> [mailto:informix-list-boun...@iiug.org]On Behalf Of jpren...@yahoo.com
> Sent: Wednesday, January 30, 2008 10:06 AM
> To: informix-l...@iiug.org
> Subject: Re: Logical log for Temp table
>
> On Jan 29, 7:29 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> > It should be noted that extent allocation is always logged - even for
> > non-logging tables. Pre-creation and sizing of a temp table can help
> > performance quite a bit.
>
> > cheers
> > j.
>
> > "He's right. No one can deny the results of No Child Left Behind, because
> > thanks to the program, no one has any basic reasoning skills." -- The
> Onion
>
> Actually temp tables created in dbspaces flagged and created as
> "temp" (used the -t flag in onspaces) don't even log extent allocation
> because at initialization time the entire dbspaces structure (chunk
> free list page, tblspace tblspace, etc...) is recreated as if the
> dbspace had just been added.
>
> Jacques
> _______________________________________________
> Informix-list mailing list
> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list
I don't recall when this was added actually but I believe it's been
this way awhile. I went back and tested a 7.31.UD7 and it also behaves
that way.
Jacques