TBLSpace tblspace....

Posted in 2010

Summary

Question: what is the "TBLSpace" entry seen in sysmaster's sysextents, and can it hit the max-extents limit? Answers: it's the TBLspace tblspace, one per dbspace (not per chunk), with an entry per dbspace; sysextents/systabnames treat the whole TBLspace tblspace as a pseudo-table fragmented across dbspaces, so a query returns rows for each dbspace's extents. Its extents can spread across chunks, which can prevent dropping a chunk and lead to "no more extents"; avoid this by sizing it with TBLTBLFIRST/TBLTBLNEXT in ONCONFIG for rootdbs and the corresponding onspaces options for other dbspaces.

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

Topics: Storage & Space Management

WILL LANDSTROM — Wed, 31 Mar 2010 14:41:55 — source: IIUG Forums & Mailing Lists

Hi everyone. I have a question regarding the "TBLSpace tblspace". I know that each individual chunk as a portion that is know as the "TBLSpace" that tracks the individual tables within a particular chunk..but..if you were to run the following example sql against the sysmaster database select tabname from sysextents;you will see a table called TBLSpace... Could someone tell me what this table is really referring to? I would assume it is NOT the TBLSpace in each individual chunk. Also...when the TBLSpace gets highly fragmented is there a potential risk that it could reach the max extents limit in Informix? Thanks, Will

↪ replying to WILL LANDSTROM

Fernando Nunes — Wed, 31 Mar 2010 14:51:33 — source: IIUG Forums & Mailing Lists

Hi. It's not stored in each chunk. There is one TBLSpace for each dbspace. This "table" can have several extents and they're not necessarily all in the first chunk. Having it spread across the chunks is bad. You can end up with two problems: 1- You don't have user objects on the chunk, and even so you can't drop the chunk because it contains one or more extents of TBLSpace 2- You can end up with the "no more extents" problem How to avoid it: - For root dbspace you have the settings in $ONCONFIG (TBLFIRST / TBLNEXT is memory serves me right) - For all other dbspaces you should define it in the onspaces command (check the syntax please) Regards. On Wed, Mar 31, 2010 at 3:41 PM, WILL LANDSTROM <willlandstrom@yahoo.com>wrote: > Hi everyone. I have a question regarding the "TBLSpace tblspace". I know > that > each individual chunk as a portion that is know as the "TBLSpace" that > tracks > the individual tables within a particular chunk..but..if you were to run > the > following example sql against the sysmaster database > select tabname from sysextents;> you will see a table called TBLSpace... > Could someone tell me what this table is really referring to? I would > assume > it is NOT the TBLSpace in each individual chunk. Also...when the TBLSpace > gets > highly fragmented is there a potential risk that it could reach the max > extents limit in Informix? > Thanks, Will > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6d566939b3273048319e15b

↪ replying to Fernando Nunes

WILL LANDSTROM — Wed, 31 Mar 2010 16:25:18 — source: IIUG Forums & Mailing Lists

Fernando. Thanks for your response. Since there is 1 TBLSpace per dbspace would you happen to know why when you run a query against sysexents it only lists one entry for TBLSpace? Do you what this 1 entry from sysexents represents?

↪ replying to WILL LANDSTROM

MIKE MAGIE — Wed, 31 Mar 2010 19:08:59 — source: IIUG Forums & Mailing Lists

It is the tablespace tablespace for the rootdbs. MM

↪ replying to WILL LANDSTROM

MIKE MAGIE — Wed, 31 Mar 2010 19:15:40 — source: IIUG Forums & Mailing Lists

There will be a 'tabname' entry of 'TBLspace' for each dbsname returned in the query.

↪ replying to WILL LANDSTROM

Fernando Nunes — Wed, 31 Mar 2010 21:21:07 — source: IIUG Forums & Mailing Lists

I get several records... Several extents from several dbspaces... How many dbspaces do you have? What query are you running? On Wed, Mar 31, 2010 at 5:25 PM, WILL LANDSTROM <willlandstrom@yahoo.com>wrote: > Fernando. Thanks for your response. Since there is 1 TBLSpace per dbspace > would you happen to know why when you run a query against sysexents it only > lists one entry for TBLSpace? Do you what this 1 entry from sysexents > represents? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016367fa6a2da9e9504831f5244

↪ replying to Fernando Nunes

Chin, Paul (TSG-OSDO) — Wed, 31 Mar 2010 22:49:38 — source: IIUG Forums & Mailing Lists

This will be the right CONFIG. TBLTBLFIRST and TBLTBLNEXT -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Thursday, 1 April 2010 1:52 AM To: ids@iiug.org Subject: Re: TBLSpace tblspace.... [19464] Hi. It's not stored in each chunk. There is one TBLSpace for each dbspace. This "table" can have several extents and they're not necessarily all in the first chunk. Having it spread across the chunks is bad. You can end up with two problems: 1- You don't have user objects on the chunk, and even so you can't drop the chunk because it contains one or more extents of TBLSpace 2- You can end up with the "no more extents" problem How to avoid it: - For root dbspace you have the settings in $ONCONFIG (TBLFIRST / TBLNEXT is memory serves me right) - For all other dbspaces you should define it in the onspaces command (check the syntax please) Regards. On Wed, Mar 31, 2010 at 3:41 PM, WILL LANDSTROM <willlandstrom@yahoo.com>wrote: > Hi everyone. I have a question regarding the "TBLSpace tblspace". I > know that each individual chunk as a portion that is know as the > "TBLSpace" that tracks the individual tables within a particular > chunk..but..if you were to run the following example sql against the > sysmaster database select tabname from sysextents; you will see a > table called TBLSpace... > Could someone tell me what this table is really referring to? I would > assume it is NOT the TBLSpace in each individual chunk. Also...when > the TBLSpace gets highly fragmented is there a potential risk that it > could reach the max extents limit in Informix? > Thanks, Will > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6d566939b3273048319e15b ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to WILL LANDSTROM

Art Kagel — Thu, 01 Apr 2010 01:24:27 — source: IIUG Forums & Mailing Lists

The entire TBLSPACE TBLSPACE across all dbspaces is treated as if it were a table fragmented across all dbspaces. It isn't really a normal table, but that's how it is treated in systabnames and sysextents (pseudo tables themselves). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 31, 2010 at 12:25 PM, WILL LANDSTROM <willlandstrom@yahoo.com>wrote: > Fernando. Thanks for your response. Since there is 1 TBLSpace per dbspace > would you happen to know why when you run a query against sysexents it only > lists one entry for TBLSpace? Do you what this 1 entry from sysexents > represents? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151748dd32146fda048322b9f7

Glossary

chunk