Removing unused columns to reduce database size

You can reduce the disk space needed for data records by removing unused columns from auxiliary tables. These tables may contain many records with unused columns if you define a seventh traceability label and/or enter SPC data with a subgroup size of nine or greater. If you have not yet converted your Pervasive database from file mode to database mode, this option is not available.

If your GainSeeker database is SQL Server or MySQL, a much better method for reducing database size is to re-create your database tables in GainSeeker version 8.6 or later, which now uses the varchar data type instead of creating fixed-width char columns. For more information, see Reducing database size with varchar columns.

Contents [Show][Hide]

Choosing which traceability columns to remove

Choosing which data columns to remove

Removing columns from auxiliary tables

Choosing which traceability columns to remove

GainSeeker stores the values for traceability fields 7-48 in the following auxiliary tables:

Auxiliary Table	System
VDAT_AUX	SPC
VMON_AUX
PLAN_AUX
DDAT_AUX	DMS

These traceability columns are named as follows:

Traceability field number	Column name
7	UDL7
8	UDL8
...	...
47	UDL47
48	UDL48

If you are sure that you will never use this many traceability fields, you can delete the unnecessary traceability columns from these auxiliary tables. For example, if the anticipated maximum number of traceability fields is 15, you can safely remove traceability columns 16-48 from the auxiliary tables.

Note: If you remove traceability columns, you must do so for all available auxiliary tables listed above. Failing to do so will generate error messages.

Choosing which data columns to remove

For SPC data with a subgroup size of nine or greater, GainSeeker stores the data values 9–72 in the following auxiliary tables:

Auxiliary Table	System
VDAT_AUX	SPC
VMON_AUX	SPC

These data columns are named as follows:

Data value	Column name
9	DATA9
10	DATA10
...	...
71	DATA71
72	DATA72

If you are sure that you will never enter data with such large subgroup sizes, you can delete the unnecessary data columns from these auxiliary tables. For example, if the maximum anticipated subgroup size is 25, then you can safely remove data columns 26-72 from the auxiliary tables.

Note: If you remove SPC data columns, you must do so for both _AUX tables listed above. Failing to do so will generate error messages.

Removing columns from auxiliary tables

To remove the unnecessary columns:

Identify all auxiliary tables to be modified.

If you have set up additional GainSeeker databases, you may have additional copies of each table. For example, if you have set up GainSeeker databases for Incoming, Production, and Service areas, you may need to modify the VDAT_AUX_INCOMING, VDAT_AUX_PRODUCTION, and VDAT_AUX_SERVICE tables.
Make a backup copy of the auxiliary tables.
Use the tools and utilities provided with your SQL Server, Oracle, or MySQL database to remove the unnecessary columns.
If the table from which you are removing unused columns is on a SQL Server that has a clustered index, use SQL Server tools to rebuild the clustered index.