out of physical log

Hi All, first time happened to me that an instance has 0 phydbslog. Is there anyway i can free up physical log without adding additional space? 7000000500b9af0 3 1 3 1 N informix physdbs01 700000050081048 3 3 125001 25000 0 PO- /dev/infx/phylogdbs Thank you.

Hi All, Is there any way i can free up the physical log? i have tried to increase it but i'm getting error: onparams -p -s 25500Physical log size specified is too large.

??? Your physical log isn't full, it's just taking up the entire dbspace which is how it was originally allocated (it cannot grow by itself). No worries, see onstat -l for how empty or full the physical log is. Also any checkpoint will empty the physical log (onmode -c). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 18, 2011 at 12:00 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi All, > > first time happened to me that an instance has 0 phydbslog. Is there anyway > i > can free up physical log without adding additional space? > > 7000000500b9af0 3 1 3 1 N informix physdbs01 > > 700000050081048 3 3 125001 25000 0 PO- /dev/infx/phylogdbs > > Thank you. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf30549e6ba1b2dd04a3865940

Hi Art, I've done "onmode -c" and "onmode -l" and the physical dbs has 0 remaining. All logs are backed up as per "onstat -l". Physical Logging Buffer bufused bufsize numpages numwrits pages/io P-1 0 32 10 1 10.00 phybegin physize phypos phyused %used 300035 24947 17422 0 0.00 Logical Logging Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io L-3 0 16 17 5 5 3.4 1.0 Subsystem numrecs Log Space used OLDRSAM 17 1428 address number flags uniqid begin size used %used 700000013d8b320 1 U-B---- 809 200035 50000 50000 100.00 700000013d8b340 2 U-B---- 810 20c385 50000 50000 100.00 700000013d8b360 3 U-B---- 811 2186d5 50000 50000 100.00 700000013d8b380 4 U-B---- 812 224a25 50000 50000 100.00 700000013d8b3a0 5 U-B---- 813 230d75 50000 2652 5.30 700000013d8b3c0 6 U-B---- 814 23d0c5 50000 12 0.02 700000013d8b3e0 7 U-B---L 815 249415 50000 6 0.01 700000013d8b400 8 U---C-- 816 255765 50000 0 0.00 700000013d8b420 9 U-B---- 807 261ab5 50000 50000 100.00 700000013d8b440 10 U-B---- 808 26de05 50000 50000 100.00 I have set the DB "onmode -s" and try to modify phydbs value but i can't. The "free space" column through onstat -d shows "0" on phylogdbs. I had the instance rebounce already but still the same.

No, Jack, you are wrong. Your physical log has zero bytes used, look at the onstat -l report's heading section again. It says: Physical Logging Buffer bufused bufsize numpages numwrits pages/io P-1 0 32 10 1 10.00 phybegin physize phypos phyused %used 300035 24947 17422 0 0.00 =========================^^^^^^^^^^ Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 18, 2011 at 1:52 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi Art, > > I've done "onmode -c" and "onmode -l" and the physical dbs has 0 remaining. > All logs are backed up as per "onstat -l". > > Physical Logging > Buffer bufused bufsize numpages numwrits pages/io > P-1 0 32 10 1 10.00 > > phybegin physize phypos phyused %used > > 300035 24947 17422 0 0.00 > > Logical Logging > Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io > L-3 0 16 17 5 5 3.4 1.0 > > Subsystem numrecs Log Space used > > OLDRSAM 17 1428 > > address number flags uniqid begin size used %used > 700000013d8b320 1 U-B---- 809 200035 50000 50000 100.00 > 700000013d8b340 2 U-B---- 810 20c385 50000 50000 100.00 > 700000013d8b360 3 U-B---- 811 2186d5 50000 50000 100.00 > 700000013d8b380 4 U-B---- 812 224a25 50000 50000 100.00 > 700000013d8b3a0 5 U-B---- 813 230d75 50000 2652 5.30 > 700000013d8b3c0 6 U-B---- 814 23d0c5 50000 12 0.02 > 700000013d8b3e0 7 U-B---L 815 249415 50000 6 0.01 > 700000013d8b400 8 U---C-- 816 255765 50000 0 0.00 > 700000013d8b420 9 U-B---- 807 261ab5 50000 50000 100.00 > 700000013d8b440 10 U-B---- 808 26de05 50000 50000 100.00 > > I have set the DB "onmode -s" and try to modify phydbs value but i can't. > The > "free space" column through onstat -d shows "0" on phylogdbs. I had the > instance rebounce already but still the same. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec5014c4760bc7904a38709c2

Yes art. you are right. But looking into "onstat -d" output, it shows that my phydbs shows "0" free with a total size of 25000, which equal to 99788KB, while performing delete, im encountering this error, so I looked into the phydbs and it shows free=0, size=25000. I am performing "delete from table where date < "01/01/2008". 271: Could not insert new row into the table. 131: ISAM error: no free disk space Error in line 4Near character position 63 Database closed. i did check the table location and it still has 2GB free space. the data is just small.

On 18 May 2011 07:45, JACK PAPA <informix2009@gmail.com> wrote: > Yes art. you are right. But looking into "onstat -d" output, it shows that my > phydbs shows "0" free with a total size of 25000, which equal to 99788KB, > > while performing delete, im encountering this error, so I looked into the > phydbs and it shows free=0, size=25000. > > I am performing "delete from table where date < "01/01/2008". > > 271: Could not insert new row into the table.> > 131: ISAM error: no free disk space > Error in line 4> Near character position 63 > > Database closed. > > i did check the table location and it still has 2GB free space. the data is > just small. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Jack If the physlog fills then a checkpoint is initiated to clear it. Your physlog cannot grow by itself, only if you expand it. The error you are getting indicates an area of disk filling which cannot happen as part of a delete !! If there something else happening that is inserting records and filling another part of the disk ? What happens if you select an earlier date (say 01/07/2007) , to delete less records ?? Keith

We'll, I did try per rowid and i can delete a record (1 by 1) but also getting the same error. I have no idea why the 25000 x 4pages space are consumed and phydbslog should have freed up. How can i free up the phylogdbs as it is showing "free=0"?. I suspect, the record cannot be processed because of this. phylogdbs can't store a "before delete" record due to space issue. I did tried changing the onconfig PHYDBSSIZE into bigger value but the instance won't come up. Is there a way to increase the phylogdbs without affecting the data? i've done "onparams -p -s 100000" but getting error => onparams -p -s 99888 Physical log size specified is too large. Tried to lower 1000, higher 1024000, still the same error.

On 18 May 2011 08:40, JACK PAPA <informix2009@gmail.com> wrote: > We'll, > > I did try per rowid and i can delete a record (1 by 1) but also getting the > same error. I have no idea why the 25000 x 4pages space are consumed and > phydbslog should have freed up. > > How can i free up the phylogdbs as it is showing "free=0"?. I suspect, the > record cannot be processed because of this. phylogdbs can't store a "before > delete" record due to space issue. > > I did tried changing the onconfig PHYDBSSIZE into bigger value but the > instance won't come up. > > Is there a way to increase the phylogdbs without affecting the data? i've done > "onparams -p -s 100000" but getting error > > => onparams -p -s 99888 > Physical log size specified is too large. > > Tried to lower 1000, higher 1024000, still the same error. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Jack The physlog simply DOES NOT grow and running with zero free space in the physlogdbs is quite acceptable (and quite normal for some people). The engine will always take a checkpoint (and empty the physlog) when the physlog is 75% full. If you feel you need to expand the physlog, and I am by no means convinced this is a requirment, you need to move it first to another dbs (needs engine bounce), drop the existing physlogdbs, create a new, bigger physlogdbs, move the physlog back to this one (another bounce) and then take an archive. Data will be completely unaffected. Keith

Are you locking the table exclusively before deleting? How many rows are you trying to delete? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JACK PAPA Sent: 18 May 2011 08:41 To: ids@iiug.org Subject: Re: out of physical log [23744] We'll, I did try per rowid and i can delete a record (1 by 1) but also getting the same error. I have no idea why the 25000 x 4pages space are consumed and phydbslog should have freed up. How can i free up the phylogdbs as it is showing "free=0"?. I suspect, the record cannot be processed because of this. phylogdbs can't store a "before delete" record due to space issue. I did tried changing the onconfig PHYDBSSIZE into bigger value but the instance won't come up. Is there a way to increase the phylogdbs without affecting the data? i've done "onparams -p -s 100000" but getting error => onparams -p -s 99888 Physical log size specified is too large. Tried to lower 1000, higher 1024000, still the same error. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System before entering the GFM Network. If you have any queries please contact Systems Administration or IT. ________________________________________________________________________ ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________

Jack As Art said before you cannot monitor the physical log with onstat -d. onstat -d in ypour case only shows that you used the whole space ofyour phylogdbs to create the physical log file. It does _not_ show the fill factor of the physical log file. The fill factor is shown by onstat -l and you can monitor the physical log with onstat -l -r 1 while deleting the records. As Keith said before another dbspace may cause your problem. You may use the following script to find dbspaces that are filled up: #!/usr/bin/ksh # freespace # WORKFILE=/tmp/dbsize$$ PROTFILE=${WORKFILE}.prot dbaccess sysmaster <<EOF 2>/dev/null set isolation to dirty read; unload to "$WORKFILE" delimiter ":" select a.dbsnum dbsnum, a.name name, count(b.chknum) num_chunks, sum(b.chksize / 512 ) space_size, sum(b.nfree / 512 ) space_free from sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum group by 1,2 order by 1; EOF #-----------------------awk-------------------------------------------- sort -t: -k 2.4n $WORKFILE | nawk -v MYDBNAME=$MYDBNAME 'BEGIN{ FS=":"; print "Dbsnum DB-space nChunks Groesse frei in MB "; print "----------------------------------------------------------------------" ; } { # printf ("%-18s%-12s%10d%10d%10d%10d%2s\\ ",$1,$2,$3,$4,$5,$6,$7); printf ("%-18s%-12s%10d%10d%10d\\ ",$1,$2,$3,$4,$5); }' (Maybe you find something similar at the IIUG Homepage). Please consider that this script is written for 2K pages. Change the output to your own convenience. Reinhard. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JACK > PAPA > Sent: Wednesday, May 18, 2011 9:41 AM > To: ids@iiug.org > Subject: Re: out of physical log [23744] > > We'll, > > I did try per rowid and i can delete a record (1 by 1) but also getting the > same error. I have no idea why the 25000 x 4pages space are consumed and > phydbslog should have freed up. > > How can i free up the phylogdbs as it is showing "free=0"?. I suspect, the > record cannot be processed because of this. phylogdbs can't store a "before > delete" record due to space issue. > > I did tried changing the onconfig PHYDBSSIZE into bigger value but the > instance won't come up. > > Is there a way to increase the phylogdbs without affecting the data? i've done > "onparams -p -s 100000" but getting error > > => onparams -p -s 99888 > Physical log size specified is too large. > > Tried to lower 1000, higher 1024000, still the same error. > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum.

I'd bet that the table you're deleting from has a delete trigger that inser= ts into another table... Nothing to do with the physical log. -----Original Message----- From: JACK PAPA <informix2009@gmail.com> Sent: quarta-feira, 18 de Maio de 2011 8:40 To: ids@iiug.org Subject: Re: out of physical log [23744] We'll,=20 I did try per rowid and i can delete a record (1 by 1) but also getting the= =20 same error. I have no idea why the 25000 x 4pages space are consumed and=20 phydbslog should have freed up.=20 How can i free up the phylogdbs as it is showing "free=3D0"?. I suspect, th= e=20 record cannot be processed because of this. phylogdbs can't store a "before= =20 delete" record due to space issue.=20 I did tried changing the onconfig PHYDBSSIZE into bigger value but the=20 instance won't come up.=20 Is there a way to increase the phylogdbs without affecting the data? i've d= one=20 "onparams -p -s 100000" but getting error=20 =3D> onparams -p -s 99888=20 Physical log size specified is too large.=20 Tried to lower 1000, higher 1024000, still the same error.=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

Hi All, It Works... there's a trigger inserting into another dbspace. Also, i was able to transfer the phylogdbs into another dbspace. Thanks a lot guys for all your help!!!!!! Kudos!