Re: Space allocation

On Feb 4, 6:04 am, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> wrote: > mohitanch...@gmail.com wrote: > > On Jan 28, 5:13 pm, mohitanch...@gmail.com wrote: > > >> On Jan 26, 11:19 pm, mohitanch...@gmail.com wrote: > > >>> On Jan 26, 8:37 pm, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> > >>> wrote: > > >>>> mohitanch...@gmail.com wrote: > > >>>>> On Jan 26, 7:18 pm, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> > >>>>> wrote: > > >>>>>> mohitanch...@gmail.com wrote: > > >>>>>>> On Jan 26, 4:55 pm, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> > >>>>>>> wrote: > > >>>>>>>> mohitanch...@gmail.com wrote: > > >>>>>>>>> On Jan 25, 4:06 pm, mohitanch...@gmail.com wrote: > > >>>>>>>>>> On Jan 25, 4:05 pm, mohitanch...@gmail.com wrote: > > >>>>>>>>>>> On Jan 24, 10:31 pm, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> > >>>>>>>>>>> wrote: > > >>>>>>>>>>>> mohitanch...@gmail.com wrote: > > >>>>>>>>>>>>> On Jan 24, 7:17 pm, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> > >>>>>>>>>>>>> wrote: > > >>>>>>>>>>>>>> mohitanch...@gmail.com wrote: > > >>>>>>>>>>>>>>> On Jan 24, 5:35 pm, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> > >>>>>>>>>>>>>>> wrote: > > >>>>>>>>>>>>>>>> mohitanch...@gmail.com wrote: > > >>>>>>>>>>>>>>>>> Version IDS 10: > > >>>>>>>>>>>>>>>>> Table that has a dedicated dbspace got 100% full. Then I started the > >>>>>>>>>>>>>>>>> delete process and since than it has deleted around 2.5 M rows, still > >>>>>>>>>>>>>>>>> when I run oncheck or query against sysptnhdr it keeps showing 0 pages > >>>>>>>>>>>>>>>>> free. Is there a delay beforeInformixwill release thespaceand show > >>>>>>>>>>>>>>>>> it asfreespace. Why don't I see "Number of pages used" in oncheck > >>>>>>>>>>>>>>>>> going down. I know I have already deleted lot of data. > > >>>>>>>>>>>>>>>> Unless all of the rows on a particularpageare deleted thepageitself > >>>>>>>>>>>>>>>> is not 'free' but if you've deleted 3 of say 9 rows that fit on the > >>>>>>>>>>>>>>>> page, then thatpagewill hold another 3 rows, even though the npused > >>>>>>>>>>>>>>>> was not reduced. > > >>>>>>>>>>>>>>>> But I have a more fundamental question:  Why do you think that the table > >>>>>>>>>>>>>>>> is 100% full?  Or was the dbspace 100% used? > > >>>>>>>>>>>>>>>> Art S. Kagel > >>>>>>>>>>>>>>>> Oninit > > >>>>>>>>>>>>>>>> ===========================================================================­­­­­­­­================ > >>>>>>>>>>>>>>>> Please access the attached hyperlink for an important electronic communications disclaimer: > > >>>>>>>>>>>>>>>>http://www.oninit.com/home/disclaimer.php > > >>>>>>>>>>>>>>>> ===========================================================================­­­­­­­­================ > > >>>>>>>>>>>>>>> This table I am talking about also hasblobpages in the dbspace > >>>>>>>>>>>>>>> itself. And the reason why it's full is because it's a separate > >>>>>>>>>>>>>>> instance that just got installed and as part of testing it got full. > >>>>>>>>>>>>>>> Is there a way to get thefreespacewithin thepage. I already use > >>>>>>>>>>>>>>> sysptnhdr to reportfreepages, but how do I getfreespacewithin the > >>>>>>>>>>>>>>> pages. > > >>>>>>>>>>>>>> Run oncheck -pT <table> > > >>>>>>>>>>>>>> Art S. Kagel > >>>>>>>>>>>>>> Oninit > > >>>>>>>>>>>>>> ===========================================================================­­­­­­­­================ > >>>>>>>>>>>>>> Please access the attached hyperlink for an important electronic communications disclaimer: > > >>>>>>>>>>>>>>http://www.oninit.com/home/disclaimer.php > > >>>>>>>>>>>>>> ===========================================================================­­­­­­­­================ > > >>>>>>>>>>>>> The table is currently locked because delete process is running and > >>>>>>>>>>>>> oncheck can't get the lock. Is there any way to tell using sysmaster > >>>>>>>>>>>>> tables ? > > >>>>>>>>>>>> Too complex for simple queries.  The printfreeP.ec utility in my > >>>>>>>>>>>> utils2_ak package prints a very similar report without locking.  Haven't > >>>>>>>>>>>> tried it with v10.00 yet, but it should work. > > >>>>>>>>>>>> Art S. Kagel > > >>>>>>>>>>>> ===========================================================================­­­­­­­­================ > >>>>>>>>>>>> Please access the attached hyperlink for an important electronic communications disclaimer: > > >>>>>>>>>>>>http://www.oninit.com/home/disclaimer.php > > >>>>>>>>>>>> ===========================================================================­­­­­­­­================- Hide quoted text - > > >>>>>>>>>>>> - Show quoted text - > > >>>>>>>>>>> I tried running the program printfreeB.ec from utils2_ak but it throws > >>>>>>>>>>> following error: > > >>>>>>>>>>> Cannot FETCH fragment cursor. Err: -400, ISAM: 0 > > >>>>>>>>>>> Other times when I give some other table it dump cores too.- Hide quoted text - > > >>>>>>>>>>> - Show quoted text - > > >>>>>>>>>> Forgot to mention, I checked the code and it looks good, it opens the > >>>>>>>>>> cursor and the SQL looks good too, but it still throws this message as > >>>>>>>>>> it never opened the cursor. > > >>>>>>>>> Is there any other way to find out thefreepages, something like add > >>>>>>>>> number of npdata + sum(length(byte column)) and subtract the result > >>>>>>>>> with nptotal ? But the other problem is that this table is fragmented > >>>>>>>>> accross dbspaces. > >>>>>>>>> ____________________ > > >>>>>>>> There are two ways to calculate thefreespace: > > >>>>>>>> 1 - read the bitmap pages and determine how many pages are a-full, b-1/2 > >>>>>>>> full, c-empty > >>>>>>>> 2 - read every datapageof the table and look at thepageheader, there > >>>>>>>> is afreebytes field in the header that records the number offree > >>>>>>>> bytes on thepage.  Divide that by the size of a data row and you'll > >>>>>>>> know how many rows can still fit on thepage.  If you want bytes you > >>>>>>>> should re-multiply by the rowsize rather than use the rawfreebyte > >>>>>>>> count as it's not all usable. > > >>>>>>>> This is what printfreeB does.  I don't know why it's getting the error > >>>>>>>> that it's getting, try using a TCP connection rather than a shared > >>>>>>>> memory connection, that may help. > > >>>>>>>> Art S. Kagel > >>>>>>>> Oninit > > >>>>>>>> ===========================================================================­­­­­­================ > >>>>>>>> Please access the attached hyperlink for an important electronic communications disclaimer: > > >>>>>>>>http://www.oninit.com/home/disclaimer.php > > >>>>>>>> ===========================================================================­­­­­­================- Hide quoted text - > > >>>>>>>> - Show quoted text - > > >>>>>>> I got printfreeB working. I am trying to understand how to deduce how > >>>>>>> muchspacein the pages is left for data andblobpages becauseblob > >>>>>>> is part of the tablespace, below is the output: > > >>>>>> Mohitan: > > >>>>>> Here's what it says: > > >>>>>>>    Bitmap scan reports: > >>>>>>>         Unused pages: