RE: How many logical log is needed
En JasonYLPang@pg.SLR.com va escriure el dia 17 Jul 2000, a les 14:28: > 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 > To solve this try: a) before execute the sql statment create the temp table with the WITH NO LOG option. b) modify the sql statment select ..... insert into temp_table_created_at point_a With this the log at insert tinto temp table will be deactivated. I hope this helps --------------------------------------- Isidre PONS ROCA BASE - Gesti' d'Ingressos Locals (Diputacio de Tarragona) Servei de Sistemes d'Informacio Av President Lluis Companys 12-C 43005 - Tarragona SPAIN Tel # +34 977 236731 Fax # +34 977 227302 http://w