RE: How many logical log is needed
From: JasonYLPang@pg.SLR.com > >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 Well, I guess this temp table is bigger than 3GB * LTX_HWM -- can you make the developer either restrict the size or build it in several steps? >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 ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com