RE: How many logical log is needed

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BFEFB8.46F711E2 Content-Type: text/plain; charset="iso-8859-1" Belows is the transaction that captured during the LONGTX. Is it because of the SQL error?? Any idea on this? Informix Dynamic Server Version 7.30.UC5 -- On-Line (LONGTX) -- Up 9 days 13:38:59 -- 1718848 Kbytes Blocked:LONGTX session #RSAM total used id user tty pid hostname threads memory memory 75062 cronadm - 24760 infimacs 1 131072 124888 tid name rstcb flags curstk status 96077 sqlexec 606fcfdc --RPX-- 2232 606fcfdc sleeping(Forever) Memory pools count 1 name class addr totalsize freesize #allocfrag #freefrag 75062 V 5730e018 131072 6184 432 10 name free used name free used overhead 0 120 scb 0 128 opentable 0 6464 filetable 0 1376 ru 0 224 log 0 2152 temprec 0 1608 keys 0 448 ralloc 0 82568 gentcb 0 8496 ostcb 0 2008 sqscb 0 8896 rdahead 0 832 hashfiletab 0 280 osenv 0 1624 buft_buffer 0 4272 sqtcb 0 2512 fragman 0 688 shmblklist 0 192 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 75062 SELECT INTO infimacs DR Not Wait -213 0 7.20 Current SQL statement : select pos_orderno orderno , pos_itemno itemno , pos_dckdte dckdte , pos_ordqty ordqty , pos_accqty accqty , pos_projno projno , pos_lnno lnno , pos_ordsts ordsts , pol_price price , ( pol_price / fcu_curexrate ) * ? usprice , pos_ovrpriceyn opriceyn , pos_ovrprice ovrprice , ( pos_ovrprice / fcu_curexrate ) * ? uspriceyn , pol_vnditem vnditem , pol_vndesc vndesc , poa_vndno vndno , poa_stsdte stsdte , poa_maildte maildte , poa_revdte revdte , poa_buyercd buyercd , pvv_name name , fcu_currprt currprt , enb_commcode commcode from pos , pol , poa , pvv , pvp , fcu , enb where pos_div = pol_div and pos_orderno = pol_orderno and pos_itemno = pol_itemno and pol_div = poa_div and pol_orderno = poa_orderno and poa_cno = pvv_cno and poa_vndno = pvv_vndno and poa_site = pvv_site and pvv_cno = pvp_cno and pvv_vndno = pvp_vndno and pvv_site = pvp_site and pvp_cno = fcu_cmpno and pvp_currtyp = fcu_currtype and pos_div = enb_div and pos_itemno = enb_itemno and pos_div = "01" and enb_commcode <> "0800" and enb_commcode <> "IDM" and enb_commcode <> " " and pos_itemno <> "IDM%" and pos_itemno <> "NDM%" and pos_itemno <> "INT%" and poa_stsdte >= "01/01/1998" and enb_commcode [ 1 , 2 ] <> " " and enb_commcode [ 3 , 4 ] <> " " and enb_commcode [ 3 , 4 ] <> "**" into temp tmp_allpo Last parsed SQL statement : select pos_orderno orderno , pos_itemno itemno , pos_dckdte dckdte , pos_ordqty ordqty , pos_accqty accqty , pos_projno projno , pos_lnno lnno , pos_ordsts ordsts , pol_price price , ( pol_price / fcu_curexrate ) * ? usprice , pos_ovrpriceyn opriceyn , pos_ovrprice ovrprice , ( pos_ovrprice / fcu_curexrate ) * ? uspriceyn , pol_vnditem vnditem , pol_vndesc vndesc , poa_vndno vndno , poa_stsdte stsdte , poa_maildte maildte , poa_revdte revdte , poa_buyercd buyercd , pvv_name name , fcu_currprt currprt , enb_commcode commcode from pos , pol , poa , pvv , pvp , fcu , enb where pos_div = pol_div and pos_orderno = pol_orderno and pos_itemno = pol_itemno and pol_div = poa_div and pol_orderno = poa_orderno and poa_cno = pvv_cno and poa_vndno = pvv_vndno and poa_site = pvv_site and pvv_cno = pvp_cno and pvv_vndno = pvp_vndno and pvv_site = pvp_site and pvp_cno = fcu_cmpno and pvp_currtyp = fcu_currtype and pos_div = enb_div and pos_itemno = enb_itemno and pos_div = "01" and enb_commcode <> "0800" and enb_commcode <> "IDM" and enb_commcode <> " " and pos_itemno <> "IDM%" and pos_itemno <> "NDM%" and pos_itemno <> "INT%" and poa_stsdte >= "01/01/1998" and enb_commcode [ 1 , 2 ] <> " " and enb_commcode [ 3 , 4 ] <> " " and enb_commcode [ 3 , 4 ] <> "**" into temp tmp_allpo -----Original Message----- From: Obnoxio The Clown [mailto:obnoxio@hotmail.com] Sent: Sunday, July 16, 2000 7:41 PM To: Pang, JasonYL; informix-list@iiug.org Subject: RE: How many logical log is needed From: JasonYLPang@pg.SLR.com > >I have increased the log space from 2G to 3G. However, I'm still getting >the >long transaction rollback. why? Because you still don't have enough log space? What kind of transactions are you doing, anyway? >-----Original Message----- >From: Obnoxio The Clown [mailto:obnoxio@hotmail.com] >Sent: Saturday, July 15, 2000 8:30 PM >To: Pang, JasonYL; informix-list@iiug.org >Subject: Re: How many logical log is needed > > >From: JasonYLPang@pg.SLR.com > > > >The size of database is around 40G. Currently, I have 100 logical log >with > >each log 20MB (Total is 2G). Is my logical log size sufficient to support > >my > >database? Recently, I'm facing a lot of long transaction. Is it because >of > >insufficient logical log?? > >I couldn't tell based on the size of your database and the size of your >logs > >whether you have sufficient space. However, the fact that you're getting >lots of long transaction rollbacks hints that you don't have enough log >space. >________________________________________________________________________ >Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com ------_=_NextPart_001_01BFEFB8.46F711E2 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12"> <TITLE>RE: How many logical log is needed</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>Belows is the transaction that captured during the = LONGTX. Is it because of the SQL error?? Any idea on this?</FONT> <BR><FONT SIZE=3D2>&nbs

JasonYLPang@pg.SLR.com wrote: > Current SQL statement : > select ...> ] > <> " " and enb_commcode [ 3 , 4 ] <> "**" into temp tmp_allpo > > Add the "WITH NO LOG" clause to your temp table creation. The statement should now read : SELECT .... INTO TEMP tmp_allpo WITH NO LOG; Rudy