Note: If you plan to set up Expanded data fields, you should use those instructions to change all of the table names. This will accomplish both changes - expanded data fields and varchar columns - at the same time.
For SQL Server and MySQL databases, GainSeeker versions 8.6 and later will create any new tables with variable-length varchar columns instead of the fixed-length char columns that were created by previous GainSeeker versions. (GainSeeker does not currently support varchar columns on Oracle or Pervasive databases.)
These varchar columns use disk space much more efficiently than char columns.
If you initially deployed an earlier version of GainSeeker, your database administrator can reduce the size of your database by using GainSeeker to create new tables with varchar columns, and then using GainSeeker-generated (or other) scripts to copy your existing data records into these new tables.
|
|
Here are the high-level steps to perform this conversion:
Select a configuration whose tables you wish to convert. Anyone using the tables referenced in that configuration must log out of GainSeeker and remain logged out until this process is finished.
Ensure that you have a good backup of the tables you want to re-create. GainSeeker will not automatically drop or alter these tables or the data they contain, but you will probably want to manually drop the larger (char) versions of the tables after data has been copied into the smaller (varchar) versions.
Use the GainSeeker System Administration module to copy the current configuration to a new configuration and Submit the change.
The remaining steps instruct you to change the tables for your current GainSeeker configuration, using the new configuration in a purely temporary capacity. If you prefer instead to make all changes in the new configuration and "retire" the old configuration after the process is complete, you may reverse the configurations listed in the remaining steps.
Edit the current configuration by changing all of the table names and then using the Table Creation Utility to create those new tables with varchar columns.
On the Configurations tab, right-click the new temporary configuration and then choose Generate Script to Copy Data.
When prompted to choose the Configuration to copy the data to, select the current configuration and then click OK.
GainSeeker will generate a script to copy the data from the current table names to the new table names, and it prompts you to choose a file name and location for saving the generated script.
The script is then automatically opened in NotePad.
Review the entire script and make any desired changes.
If you have previously removed unused columns from tables such as VDAT_AUX and DDAT_AUX, you will need to either update the script by specifying column names to copy from these modified tables (recommended) or else remove the same columns from the newer tables so that the script will function correctly as written.
If your database server has enough free space to copy all of the data in the current tables to the new tables, you can execute the entire script on your GainSeeker database. After ensuring that all data was successfully copied, you can manually drop the larger versions of these tables.
If there is not adequate free space to copy data from all the tables at once, you will need to manually execute some INSERT statements and then drop the larger versions of those tables before proceeding to the next group of INSERT statements.
If you chose to reverse the older and newer configurations according to step 3 above, you will need to change any Users that use the old configuration to now use the new configuration instead. You can either perform this step manually for each user or un-comment and execute the line in the script that updates the HSICFG table to change all of these users at once.