Space allocation

Posted in 2008

Summary

A user on IDS 10 filled a dedicated dbspace, deleted ~2.5M rows, and found oncheck/sysptnhdr still showed 0 free pages. Respondents explained that extents allocated to a tblspace are never released back to the dbspace (only DROP or ALTER FRAGMENT reorganises), and that a page only counts as free if all its rows are deleted; partially emptied pages still accept new rows. For free space within pages, 'oncheck -pT' was suggested, but it needed a lock the running delete held; Art Kagel's printfreeB/printfreeP from utils2_ak was offered as a lock-free alternative, though the user hit a -400 fetch error and core dumps (only a suggestion to try a TCP instead of shared-memory connection, with no confirmed fix).

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: High Availability & Replication, Storage & Space Management

mohitanchlia@gmail.com — Thu, 24 Jan 2008 13:06:34 -0800 (PST) — source: Usenet: comp.databases.informix

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 before Informix will release the space and show it as free space. Why don't I see "Number of pages used" in oncheck going down. I know I have already deleted lot of data.

↪ replying to mohitanchlia@gmail.com

Murray Wood — Fri, 25 Jan 2008 10:22:51 +1300 — source: Usenet: comp.databases.informix

Once space is allocated to a table it is not deallocated from that table, unless you drop or reorganise that table (with Alter Fragment). Deleting rows dos not release space to the chunk or dbspace (if the table required that space once it will probably require it again). MW -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of mohitanchlia@gmail.com Sent: Friday, 25 January 2008 10:07 a.m. To: informix-list@iiug.org Subject: Space allocation 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 before Informix will release the space and show it as free space. Why don't I see "Number of pages used" in oncheck going down. I know I have already deleted lot of data. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

↪ replying to mohitanchlia@gmail.com

scottishpoet — Thu, 24 Jan 2008 15:09:33 -0800 (PST) — source: Usenet: comp.databases.informix

On Jan 24, 9:06 pm, 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 before Informix will release the space and show > it as free space. Why don't I see "Number of pages used" in oncheck > going down. I know I have already deleted lot of data. You need to understand how EXTENTS work within a tblspace within a dbspace. I really would recommend if you haven;t doone so already that you attend an IDS admin course. If you can't get on a course then please review the section of the superb IDS admin manuals about EXTENTS. The section refering to this specific problem is "Reclaiming Unused Space Within an Extent" Once the database server allocates disk space to a tblspace as part of an extent, that space remains dedicated to the tblspace. Even if all extent pages become empty after you delete data, the disk space remains unavailable for use by other tables. Fully explained : http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.perf.doc/perf180.htm?resultof=%22%45%58%54%45%4e%54%53%22%20%22%65%78%74%65%6e%74%22%20

↪ replying to scottishpoet

mohitanchlia@gmail.com — Thu, 24 Jan 2008 16:50:37 -0800 (PST) — source: Usenet: comp.databases.informix

On Jan 24, 3:09 pm, scottishpoet <drybur...@yahoo.com> wrote: > On Jan 24, 9:06 pm, 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 the space and show > > it as free space. Why don't I see "Number of pages used" in oncheck > > going down. I know I have already deleted lot of data. > > You need to understand how EXTENTS work within a tblspace within a > dbspace. I really would recommend if you haven;t doone so already that > you attend an IDS admin course. > > If you can't get on a course then please review the section of the > superb IDS admin manuals about EXTENTS. > > The section refering to this specific problem is "Reclaiming Unused > Space Within an Extent" > > Once the database server allocates disk space to a tblspace as part of > an extent, that space remains dedicated to the tblspace. Even if all > extent pages become empty after you delete data, the disk space > remains unavailable for use by other tables. > > Fully explained : > > http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.p... I understand that piece. But my understanding was that sysptnhdr or oncheck would still be able to tell me how much space has been freed after deletes. I am not looking at free space of chunks but I am looking at free space within the partition. There should be some way to tell how much space is available, how does informix know how much space is available or if really there is any space within the page ? There should be some table in sysmaster database, correct. sysptnhdr is supposed to give nptotal-npused pages that would tell how much free pages are available even though entire space is allocated to the extents, but in this case it's not helping.

↪ replying to mohitanchlia@gmail.com

Art S. Kagel (Oninit LLC) — Thu, 24 Jan 2008 20:35:30 -0500 — source: Usenet: comp.databases.informix

mohitanchlia@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 before Informix will release the space and show > it as free space. 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 particular page are deleted the page itself is not 'free' but if you've deleted 3 of say 9 rows that fit on the page, then that page will 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 ===========================================================================================

↪ replying to Art S. Kagel (Oninit LLC)

mohitanchlia@gmail.com — Thu, 24 Jan 2008 18:54:25 -0800 (PST) — source: Usenet: comp.databases.informix

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 the space and show > > it as free space. 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 particular page are deleted the page itself > is not 'free' but if you've deleted 3 of say 9 rows that fit on the > page, then that page will 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 has blob pages 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 the free space within the page. I already use sysptnhdr to report free pages, but how do I get free space within the pages.

↪ replying to mohitanchlia@gmail.com

Art S. Kagel (Oninit LLC) — Thu, 24 Jan 2008 22:17:53 -0500 — source: Usenet: comp.databases.informix

mohitanchlia@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 the space and show >>> it as free space. 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 particular page are deleted the page itself >> is not 'free' but if you've deleted 3 of say 9 rows that fit on the >> page, then that page will 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 has blob pages 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 the free space within the page. I already use > sysptnhdr to report free pages, but how do I get free space within 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 ===========================================================================================

↪ replying to Art S. Kagel (Oninit LLC)

mohitanchlia@gmail.com — Thu, 24 Jan 2008 20:05:15 -0800 (PST) — source: Usenet: comp.databases.informix

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 the space and show > >>> it as free space. 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 particular page are deleted the page itself > >> is not 'free' but if you've deleted 3 of say 9 rows that fit on the > >> page, then that page will 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 has blob pages 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 the free space within the page. I already use > > sysptnhdr to report free pages, but how do I get free space within 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 ?

↪ replying to mohitanchlia@gmail.com

Art S. Kagel (Oninit LLC) — Fri, 25 Jan 2008 01:31:31 -0500 — source: Usenet: comp.databases.informix

mohitanchlia@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 the space and show >>>>> it as free space. 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 particular page are deleted the page itself >>>> is not 'free' but if you've deleted 3 of say 9 rows that fit on the >>>> page, then that page will 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 has blob pages 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 the free space within the page. I already use >>> sysptnhdr to report free pages, but how do I get free space within 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 ===========================================================================================

↪ replying to Art S. Kagel (Oninit LLC)

mohitanchlia@gmail.com — Fri, 25 Jan 2008 16:05:05 -0800 (PST) — source: Usenet: comp.databases.informix

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 the space and show > >>>>> it as free space. 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 particular page are deleted the page itself > >>>> is not 'free' but if you've deleted 3 of say 9 rows that fit on the > >>>> page, then that page will 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 has blob pages 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 the free space within the page. I already use > >>> sysptnhdr to report free pages, but how do I get free space within 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.

↪ replying to mohitanchlia@gmail.com

mohitanchlia@gmail.com — Fri, 25 Jan 2008 16:06:23 -0800 (PST) — source: Usenet: comp.databases.informix

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 the space and show > > >>>>> it as free space. 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 particular page are deleted the page itself > > >>>> is not 'free' but if you've deleted 3 of say 9 rows that fit on the > > >>>> page, then that page will 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 has blob pages 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 the free space within the page. I already use > > >>> sysptnhdr to report free pages, but how do I get free space within 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.

↪ replying to mohitanchlia@gmail.com

mohitanchlia@gmail.com — Sat, 26 Jan 2008 13:26:52 -0800 (PST) — source: Usenet: comp.databases.informix

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 the space and show > > > >>>>> it as free space. 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 particular page are deleted the page itself > > > >>>> is not 'free' but if you've deleted 3 of say 9 rows that fit on the > > > >>>> page, then that page will 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 has blob pages 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 the free space within the page. I already use > > > >>> sysptnhdr to report free pages, but how do I get free space within 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 the free pages, 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.

↪ replying to mohitanchlia@gmail.com

Art S. Kagel (Oninit LLC) — Sat, 26 Jan 2008 19:55:55 -0500 — source: Usenet: comp.databases.informix

mohitanchlia@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 the space and show >>>>>>>>> it as free space. 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 particular page are deleted the page itself >>>>>>>> is not 'free' but if you've deleted 3 of say 9 rows that fit on the >>>>>>>> page, then that page will 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 has blob pages 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 the free space within the page. I already use >>>>>>> sysptnhdr to report free pages, but how do I get free space within 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 the free pages, 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 the free space: 1 - read the bitmap pages and determine how many pages are a-full, b-1/2 full, c-empty 2 - read every data page of the table and look at the page header, there is a free bytes field in the header that records the number of free bytes on the page. Divide that by the size of a data row and you'll know how many rows can still fit on the page. If you want bytes you should re-multiply by the rowsize rather than use the raw free byte 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 ===========================================================================================

↪ replying to Art S. Kagel (Oninit LLC)

mohitanchlia@gmail.com — Sat, 26 Jan 2008 18:36:44 -0800 (PST) — source: Usenet: comp.databases.informix

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 much space in the pages is left for data and blob pages because blob is part of the tablespace, below is the output: Bitmap scan reports: Unused pages: 3181752. Bitmap pages: 1663. Unused blob pages: 0. Partial data pages: 21548. Partial blob pages: 152199. Small data pages: 0. Half full blob pages: 496605. Full data pages: 1219. Full index pages: 1663. Full blob pages: 2845890. ----------- Total pages reported: 9885954.

↪ replying to mohitanchlia@gmail.com

Art S. Kagel (Oninit LLC) — Sat, 26 Jan 2008 22:18:31 -0500 — source: Usenet: comp.databases.informix

mohitanchlia@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 > much space in the pages is left for data and blob pages because blob > is part of the tablespace, below is the output: > Mohitan: Here's what it says: > Bitmap scan reports: > Unused pages: 3181752. > 3 Million free/unused pages. These can be used for row data or blob pages. > Bitmap pages: 1663. > Unused blob pages: 0. > No free bitmap pages. > Partial data pages: 21548. > These data pages are about 1/2 full or less. > Partial blob pages: 152199. > These are blob pages containing blobs smaller than a page. > Small data pages: 0. > These pages would be less than 1/3 full. > Half full blob pages: 496605. > Full data pages: 1219. > Full index pages: 1663. > Full blob pages: 2845890. > Deleted blob pages are not actually freed until the next level 0 archive. > ----------- > Total pages reported: 9885954. Art S. Kagel Oninit ===================================================================================

↪ replying to Art S. Kagel (Oninit LLC)

mohitanchlia@gmail.com — Sat, 26 Jan 2008 19:43:11 -0800 (PST) — source: Usenet: comp.databases.informix

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: 3181752. > > 3 Millionfree/unused pages. These can be used for row data orblobpages. > > > Bitmap pages: 1663. > > Unusedblobpages: 0. > > Nofreebitmap pages.> Partial data pages: 21548. > > These data pages are about 1/2 full or less. > > > Partialblobpages: 152199. > > These areblobpages containing blobs smaller than apage. > > > Small data pages: 0. > > These pages would be less than 1/3 full. > > > Half fullblobpages: 496605. > > Full data pages: 1219. > > Full index pages: 1663. > > Fullblobpages: 2845890. > > Deletedblobpages are not actually freed until the next level 0 archive. > > > ----------- > > Total pages reported: 9885954. > > Art S. Kagel > Oninit > > ========

↪ replying to mohitanchlia@gmail.com

Art S. Kagel (Oninit LLC) — Sat, 26 Jan 2008 23:37:30 -0500 — source: Usenet: comp.databases.informix

mohitanchlia@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: 3181752. >>> >> 3 Millionfree/unused pages. These can be used for row data orblobpages. >> >> >>> Bitmap pages: 1663. >>> Unusedblobpages: 0. >>> >> Nofreebitmap pages.> Partial data pages: 21548. >> >> These data pages are about 1/2 full or less. >> >> >>> Partialblobpages: 152199. >>> >> These areblobpages containing blobs smaller than apage. >> >> >>> Small data pages: 0. >>> >> These pages would be less than 1/3 full. >> >> >>> Half fullbl

↪ replying to Art S. Kagel (Oninit LLC)

mohitanchlia@gmail.com — Sat, 26 Jan 2008 23:19:35 -0800 (PST) — source: Usenet: comp.databases.informix

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: 3181752. > > >> 3 Millionfree/unused pages. These can be used for row data orblobpages. > > >>> Bitmap pages: 1663. > >>> Unusedblobpages: 0. > > >> Nofreebitmap pages.> Partial data pages: 21548. > > >> These data pages are about 1/2 full or less. > > >>> Partialblobpages: 152199. > > >> These areblobpages containing blobs smaller than apage. > > >>> Small data pages: 0. > > >> These pages would be less than 1/3 full. > > >>> Half fullblobpages: 496605. > >>> Full data pages:

↪ replying to mohitanchlia@gmail.com

mohitanchlia@gmail.com — Mon, 28 Jan 2008 17:13:29 -0800 (PST) — source: Usenet: comp.databases.informix

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: 3181752. > > > >> 3 Millionfree/unused pages. These can be used for row data orblobpages. > > > >>> Bitmap pages: 1663. > > >>> Unusedblobpages: 0. > > > >> Nofreebitmap pages.> Partial data pages: 21548. > > > >> These data pages are about 1/2 full or less. > > > >>> Partialblobpages: 152199.

↪ replying to mohitanchlia@gmail.com

mohitanchlia@gmail.com — Mon, 28 Jan 2008 17:34:20 -0800 (PST) — source: Usenet: comp.databases.informix

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: 3181752. > > > > >> 3 Millionfree/unused pages. These can be used for row data orblobpages. > > > > >>> Bitmap pages:

↪ replying to mohitanchlia@gmail.com

Art S. Kagel (Oninit LLC) — Mon, 04 Feb 2008 09:04:07 -0500 — source: Usenet: comp.databases.informix

mohitanchlia@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 because

Space allocation

Glossary

Your privacy choices