Associating iietab "Extended tables" with base Table
Bloomfield, Martin (CRD)
As part of my routine maintenance I run a script that lists all the database tables and their associated file system object name (from iifile_info), I then check the actual size of these files on the disk to ensure we are not approaching the 2Gb disk file limit imposed by 32-bit Windows. I have discovered that several of our iietab files are approaching 1.5Gb in size. We have about 18 tables in our database which have “long byte” database columns which result in these iietab files being created. The problem I am having is making the association between these system extension tables (iietab) and the “real” table that we have created, with a view to either partitioning the tables, or investigating whether some data can be archived. I’ve had a look in the documentation and cannot find any information on how to link the iietab file back to its parent table. I know this information must be in the system catalogues somewhere as Ingres has to make the connection for SQL.
I therefore have 2 questions:
1. How do I associate the iietab table with its parent database table so I can identify which is approaching the 2Gb file system limit?
2. If an underlying system file associated with an iietab table reaches the file system limit, what will Ingres do? Will Ingres create another iietab table or just stop working to prevent database inconsistency?
Many thanks,
Martin Bloomfield.
_________________________________________________
Application Developer & Database Administrator
IT Branch
Chemicals Regulation Directorate
Health and Safety Executive
YORK
Email: martin.b...@hse.gsi.gov.uk
Website: www.pesticides.gov.uk
P Save a tree... please don't print this e-mail unless you really need to
The Chemicals Regulation Directorate (CRD) was created on the 1st April 2009.
This email may contain sensitive information. Please ensure that you refer to our Statement of Service Standards for guidance on secure handling of information
***********************************************************************************
Please note: Incoming and outgoing e-mail messages are routinely monitored for compliance with our policy on the use of electronic communications and may also be automatically logged, monitored and/or recorded for lawful purposes by the GSi service provider.
Interested in Occupational health and safety information?
Please visit the HSE website at the following address to
keep yourself up to date.
www.hse.gov.uk
or Contact HSE Infoline on 0845 345 0055 or Email on hse.in...@natbrit.com
***********************************************************************************
The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
Karl Schendel
On Dec 11, 2009, at 5:53 AM, Bloomfield, Martin (CRD) wrote:
> I therefore have 2 questions:
>
> 1. How do I associate the iietab table with its parent database table so I can identify which is approaching the 2Gb file system limit?
>
> 2. If an underlying system file associated with an iietab table reaches the file system limit, what will Ingres do? Will Ingres create another iietab table or just stop working to prevent database inconsistency?
>
name of the etab, iietab_xx_yy. The xx part is the reltid of the base
table, in hex. You can look up the table name (relid) with
that reltid and reltidx zero.
2) It's SUPPOSED to create a new etab and keep on going.
The internal linkages support multiple etabs per column per
base table.
Apparently this doesn't always work, but I am unsure of when it
works and when it doesn't.
By the way, partitioning the base table won't help, alas. Ingres
initially creates an etab per partition, but then due to a goof,
puts all the data into the etab for the base table instead of the
partitions! I don't know whether anyone has fixed this (I know
I haven't remembered to!).
Karl