The Table Reorganization and Redefinition tool of Control Center provides a means to reorganize, migrate, backup, and redefine tables within databases. This function provides you with improved control and flexibility to manage database objects for better performance and adaptability to changing needs.
The Table Reorganization and Redefinition tool can be executed in command mode (see Appendix G, Command Mode Interface) and is more commonly referred to by its command name, SQLTABLE.
SQLTABLE differs from the System Administration tools of Control Center because it executes as a database application, rather than as a function within the database virtual machine. SQLTABLE therefore normally executes on the user's virtual machine and only requires that you have needed authority within the target database, rather than Control Center authority.
The database authorities necessary to use SQLTABLE differ depending on which SQLTABLE function is executed. Users with INSERT and DELETE authority to a table can perform the DATAUNLOAD and DATALOAD functions on that table. The DDL capture required for the full table reorganization or redefinition function of SQLTABLE (see Figure 191, Selection Options 2, 3, or 4) will need DBA authority within the target database.
The following comments apply to a full table reorganization and not to several of the other options available. Refer to the DB2 Server for VSE & VM Database Administration manual for a thorough understanding of the database TABLE reorganization process.
SQLTABLE should be used during non-peak hours to prevent locking contention with other users of the database. Due to the intensive updating of the database system catalogs, use of this tool is prone to lock contention during heavy multiple user sessions. To avoid catalog contention, do not run more than one SQLTABLE job against different tables simultaneously within a single database.
Installations that want to schedule reorganizations and maintenance jobs should implement a separate Control Center support machine to provide the scheduling capability without impacting the responsibilities of the primary Control Center service machine. SQLTABLE jobs should not be scheduled to execute on the managing Control Center service machine, since all automated operations functions with all databases will be disabled while the maintenance functions execute. Although SQLTABLE can be executed in batch mode with any scheduling product, a separate Control Center support machine will allow these jobs to be scheduled easily from the Control Center panel interface.
Use of this tool requires Control Center database Administration-level or greater authorization.
The following is a summary of the various SQLTABLE processing options:
If only the source database and table is specified (meaning that this is not a migration), SQLTABLE will perform a table reorganization, using DATAUNLOAD to capture the data from the table and DATALOAD to restore the data to the table. If the DATA only option is specified, SQLTABLE will delete all rows in the table prior to performing the DATALOAD. The DATA only option will require that you have SELECT and INSERT authority to the table.
If the DDL only option is specified, SQLTABLE will capture the table create statement and all associated database objects, including indexes, views, and grants.
When data and DDL are both captured for a reorganization, you must have DBA authority for complete capture of all dependent objects within the database (including those of other users). The table reorganization for DDL and data will capture the data using DATAUNLOAD, will DROP and CREATE the table, DATALOAD the data, and then recreate all dependent objects (indexes, views, and grants).
SQLTABLE will migrate data or table DDL between databases if the NEW DATABASE parameter is specified. The source table will not be dropped or modified in any way.
SQLTABLE will copy either data or the table definition (DDL) or both if a NEW OWNER.TABLE is specified. If DATA only is specified, the target table must already exist and the column definitions must match the source table exactly. If DDL is specified, you must have DBA authority to capture all dependent database objects. It is important to note that if the target OWNER is the same as the source table OWNER, the INDEX and VIEW create names will be identical to the existing ones on the old table, so the create statements will fail unless you take steps to eliminate the duplication.
SQLTABLE will move a table to a new DBSPACE if the DATA and DDL option is specified with the NEW OWNER.DBSPACE parameter. Note that the old table will only be deleted if the new OWNER and TABLE remain the same as the old OWNER and TABLE.
SQLTABLE will allow you to REDEFINE a table, changing column names, adding columns, deleting columns, changing column datatypes and lengths. Since the DATAUNLOAD/DATALOAD functions of the Database Services Utility (DBSU) are used for this process, all restrictions described in the "Data Conversion Summary" section of the DB2 Server for VSE & VM Database Services Utility manual apply. You should be aware of which datatypes are compatible and which ones will cause data truncation or dataload failures.
When SQLTABLE is invoked, the following steps will be performed:
Each file created by SQLTABLE will have a filename that is created from the specified target table name. If the table name has any special characters (non-alphanumeric), they will be removed. If the table name is longer than eight characters, then it will be truncated to eight. The basic files created are:
The LOADDBSU file is processed first to drop the old table or delete data (as required), create the new table, and perform the dataload. The subsequent DDLDBSU file is then processed separately, since several of the SQLTABLE options can result in duplicate view and index names, causing the DBSU process to fail. Manual intervention by you will then be required to either modify the failing statements in the DBSU file before rerunning it, or to issue comparable statements within ISQL or another database interface.
All grants are issued in the same chronological order in which they were originally issued, in order to retain hierarchical dependencies.
In order to grant authority to an object, SQLTABLE must first connect as the user who originally issued the grant. Therefore, SQLTABLE must gather database connect passwords for all grantors. If a grantor does not have a connect password, SQLTABLE will give the user a temporary one and will later remove it.
The database does not remove grant information from the system catalogs when a user is removed from the SYSTEM.SYSUSERAUTH table. Therefore, the possibility exists that SQLTABLE needs to connect as a nonexistent user in order to reestablish a grant. If this situation occurs, SQLTABLE will temporarily grant connect authority and will later revoke it.
The SQLTABLE tool has these features:
The SQLTABLE tool panel shown in Figure 191 is reached from the Control Center Main Menu by choosing the Database Utilities (U) option and then choosing the SQLTABLE (T) option.
Figure 191. SQLTABLE Data Entry Panel
+--------------------------------------------------------------------------------+ | ********************* SQLTABLE TABLE UTILITY ******************* | | SELECTION ==> 3 ( 1=DATA only <DELETE *>, 2=DDL only, | | - 3=DATA and DDL <DROP TABLE>, 4=REDEFINE columns) | | DATABASE (DBNAME) ==> SQLDBA ( example: SQLDBA ) | | -------- | | OWNER.TABLE_NAME ==> ( example: MY.USER_TABLE ) | | --------------------------- | | NEW DATABASE ==> ( defaults to same ) | | -------- | | NEW OWNER.TABLE ==> ( COPY table and/or ) | | --------------------------- | | NEW OWNER.DBSPACE ==> ( MOVE table and/or ) | | --------------------------- | | DATALOAD PCTFREE ==> ALTER PCTFREE ==> ( after dataload ) | | --- --- | | COMMITCOUNT ==> 1000 (1-50000) REPREP PKGS ==> YES ALLSTATS ==> NO | | ----- --- --- | | PAUSE ==> NO (YES / NO) TAPE ==> NO (YES / NO) | | --- --- | | FILEMODE ==> (R/W disk) | | --- | |PF: 1 HELP 3 QUIT 4 EXIT 5 Main Menu 6 SCHEDULE 10 PROCESS NOW | | | +--------------------------------------------------------------------------------+
The SELECTION parameter specifies which of several functions should be performed.
The DATABASE and OWNER.TABLE_NAME parameters are required for all SQLTABLE options to indicate the target table for the selected function. The database specified must match either the database parameter or the nickname parameter of an entry in the DBINIT CONTROL file. The OWNER.TABLE_NAME parameter must identify a valid table within the target database.
All additional parameters on the SQLTABLE panel are optionally used to specify additional characteristics of the SQLTABLE function to be performed.
After entering all desired SQLTABLE parameters, PF10 can be used to perform the table reorganization immediately.
| Usage Consideration: | When used in this manner, the tool will run on your virtual machine, thus tying up your ID for the duration of the reorganization job. |
Optionally, PF6 can be used to schedule the reorganization activity using the Job Scheduling tool. Refer to Chapter 10, Job Scheduling Tool.
The Definition Change function of the database allows columns to be added, deleted, column order within a table to be changed, column names to be changed, and column datatypes and sizes to be changed. Tape cannot be specified using the SQLTABLE REDEFINE tool.
SQLTABLE uses the DATAUNLOAD/DATALOAD function of the Database Services Utility (DBSU) to transfer data between the previous table definition and the new table definition. Any datatype changes for column definitions are therefore restricted to those that are compatible as described in the DB2 Server for VSE & VM Database Services Utility manual under DATALOAD/DATAUNLOAD Data Conversion Summary. The DATALOAD for the new table definition will fail if any of the Data Conversion restrictions are violated.
When the REDEFINE columns option is selected, SQLTABLE will provide the existing table column definitions to you for modification. An example of the display panel format is:
Figure 192. SQLTABLE REDEFINE Columns Panel
+--------------------------------------------------------------------------------+ |**************** SQLTABLE TABLE DEFINITION CHANGE *************** | | Source Table: OWNER.TABLE | | Colno Column Name Datatype Nulls(Y/N) | | 1 CCODE CHAR( 3) Y | | 2 CUSTNO CHAR( 6) N | | 3 CUSTOMER CHAR( 32) Y | | 4 NAME CHAR( 34) Y | | 5 ADDRESS CHAR( 65) Y | | 7 CITY CHAR( 30) Y | | 8 COUNTRY CHAR( 25) Y | | 9 NEW_TEST CHAR( 5) N | | | | | | | | | | | | | | Make desired changes on ALL pages, press PF10 when ready to PROCESS | | | | Page 1 of 1 | | PF: 1 Help 2 Reset values 3 QUIT | | 4 ADD new column 10 PROCESS Table Redefine | | | | | | | +--------------------------------------------------------------------------------+
Use selection Option 4 from the SQLTABLE Utility menu to redefine a table's columns. See Figure 192 which displays the current table definitions. New columns can be added by pressing PF4.
The panel which follows, Figure 193, will then allow the user to define the new column(s).
Figure 193. SQLTABLE Column Addition Panel
+--------------------------------------------------------------------------------+ |***************** SQLTABLE TABLE COLUMN ADDITION **************** | | Source Table: OWNER.TABLE | | Colno Column Name Datatype Nulls(Y/N) Default Value | | 99 SAMPLE_COLUMN CHAR(101) N UNDEFINED | | ----- ------------------ ---------------------------- - --------------- | | 10 NEW_FIELD CHAR(10) N xxxxxx | | 11 | | 13 | | 14 | | 15 | | | | | | | | | | Add columns and press PF10 when ready to PROCESS column adds | | | | PF: 1 Help 3 QUIT (additions) 10 PROCESS Additions | | | | | +--------------------------------------------------------------------------------+
The rows shown that begin with 10, 11, 12, allow inputs from the user to define new columns for the existing table. The first row, with a COLNO value of 10, indicates that the table currently contains 9 columns, so the first column added will be column number 10. The COLNO value can be changed on this screen to add a new column elsewhere in the table, but the provided COLNO value must not be duplicated on a prior panel (you will need to change COLNO values of existing columns before coming to this screen).
To add a new column, enter the column name and datatype in the entry fields under the appropriate heading, and specify Y or N to indicate whether the new column should allow NULLS (Y) or be defined as NOT NULL (N). If Y is specified, there are 2 choices available regarding defaults: 1) If no default value is entered on the panel, the new column will be added to the table and any rows of data which currently exist in the table will set the new column value to NULL; 2) If a default value is specified on the panel, the new column will be initialized to that value for any currently existing rows in the unloaded file. If N is specified, you MUST specify a default value for numeric data in the rightmost entry field of this panel. For CHAR type data, if no default value is specified, a default of blanks will be assumed. The new column will be added to the table as NOT NULL and any existing rows of data will have the default value entered for the new column.
After any new columns are defined, press PF10 to save your definitions and toggle back to the previous "CHANGE TABLE" panel. You can continue switching between the CHANGE TABLE panel and the ADD COLUMNS panel until you press PF10 or PF3 on the CHANGE TABLE panel to PROCESS the table redefinition or CANCEL the redefinition. Note, however, if you press PF3 from the ADD COLUMN panel, all previously entered new columns will disappear if you go back into this function with PF4 from the main table definition column. PF3 from the Table Column Addition panel resets this panel to blanks.
Note that SQLTABLE will always attempt a DATAUNLOAD and DATALOAD of the table(s) specified in the REDEFINE function. The DATALOAD will fail if the data stored in the source table does not match the data format specified by a redefined datatype in the object table. If you want a new table without the data from the source table, SQLTABLE should first be run using Option 2 (DDL only) to create a copy of the original table without data. The REDEFINE function (Option 4) can then be used against the new table without concern about dataload errors.
The REDEFINE function will also attempt to reconstruct all DDL associated with the table, including index and view definitions and all grants. If the owner of the new table is the same as the owner of the source table, but the table name is different, the INDEX and VIEW create statements will fail due to duplication (non-uniqueness) within the database. The PAUSE option can be used to capture the INDEX and VIEW create statements within the DBSU format and allow the index and view names to be manually modified before completing the execution of the SQLTABLE function.
You cannot use SQLTABLE to unload/reload (reorg) the QMF table Q.OBJECT_DATA. The run will be stopped after a warning message is issued. The reason for this is that the database DBSU suppresses blanks at the end of VARCHAR and LONGVARCHAR fields when doing a DATALOAD (which is what SQLTABLE uses). This action renders QMF FORMS and some PROCS and QUERIES unusable resulting in loss of QMF objects. In order to prevent this situation, SQLTABLE will display a warning message and stop the processing. You can still use the SQLREORG tool to UNLOAD/RELOAD the DBSPACE in which this table resides (PUBLIC.DSQTSCT3).
When using the TAPE option, you must issue the appropriate FILEDEF, LABELDEF, and TAPE MOUNT commands prior to invoking SQLTABLE. The DDNAME for the unloaded data must be DBSFILE. SQLTABLE executes the DATAUNLOAD utility. DATAUNLOAD supports all record format (RECFM) values with the exception of undefined (U). For instance, an example of a FILEDEF and LABELDEF command are given below:
FILEDEF DBSFILE TAP1 SL (RECFM VBS BLKSIZE 28672 LABELDEF DBSFILE VOLID SCRATCH |
If the unloaded data will fit on a single tape, then SQLTABLE can be executed without the PAUSE option, and the tape will automatically be rewound between the DATAUNLOAD and DATALOAD steps.
If the unloaded data will span multiple tapes, then the proper LABELDEF command must be issued to handle the multiple volume tape and the PAUSE option must be specified so that the appropriate sequence of DETACH and MOUNT commands can be issued between the DATAUNLOAD and DATALOAD processing steps. After the PAUSE, the last tape must be detached and the first tape must again be mounted before the DATALOAD process can begin.
SQLTABLE will display status information at your terminal to indicate what step is being performed. If SQLTABLE fails at any time prior to the "Reloading..." message, the table has not been changed and SQLTABLE should be restarted from the beginning. If the failure occurs during the "Unloading..." step and you are using a temporary disk, you can detach the temporary disk by issuing the CMS RELEASE command with the DET option.
If SQLTABLE fails at any time after the "Reloading..." message has been displayed, the table must be recovered using the created REXX exec and related files. This exec will begin the dataload process by using the DBSU files and the unloaded data file. If the data was unloaded to TAPE, then the proper FILEDEF, LABELDEF, and tape MOUNT commands must be used before the created exec can be invoked.
| Note: |
|---|
|
If SQLTABLE unloads data to a temporary disk and SQLTABLE fails, you should be aware that the temporary disk (and unloaded data) will be purged if you log off. Therefore, the created exec must be run successfully before logging off. |
As a special precaution to prevent loss of data, SQLTABLE sends the unloaded data file to your reader if a temporary disk is used. If the temporary disk is lost, the reader copy can be received onto a new disk and the created exec can then be executed. Under this scenario, you should verify that all file mode references in the created exec correctly point to the new location of the data file.
The same cautions discussed in "Failure Restart" also apply to the use of the SQLTABLE PAUSE option. Each time that SQLTABLE is run, a new exec and related files will be created. Any previous copy of these files will be renamed by reversing the filename and filetype and reversing all letters in each. This implies that if a second SQLTABLE is executed prior to completion of the first SQLTABLE, the first one can be used again if the files are renamed back again.
The PAUSE option can be very useful to an experienced user of SQLTABLE and very dangerous to an inexperienced user. If you have a good working knowledge of the Database Services Utility (SQLDBSU) and of SQLTABLE, the PAUSE option will allow you a great deal of flexibility and control in the SQLTABLE functions. The SQLDBSU file can be examined and modified prior to running the created exec in order to change or remove certain characteristics of the table. Once again, care should be exercised to prevent loss or contamination within the database.
SQLTABLE will unload the database data within a table to an OS format file on a CMS minidisk or tape using the DBS Utility DATAUNLOAD command. This unloaded format file can be modified using XEDIT or other file editors to modify the data prior to the reload. Caution should be exercised to ensure that this data file is not modified in such a way that will cause the DATALOAD to fail. The default XEDIT setting of NULLS ON will cause automatic truncation of lines within the unloaded file, which will result in the file being corrupted.
SQLTABLE uses a DBS Utility command file to execute the DATAUNLOAD portion of the reorganization. Detailed output from the DATAUNLOAD portion is captured in a file on your 191 A-disk with a file type of UNLIST. This file can be examined to determine the reason for failure.
During the "Reloading..." portion of SQLTABLE processing, the SQLDBSU command file is executed using the DBS Utility. Detailed output of this processing is captured within a file on the target disk with a filetype of LOADLIST or DDLLIST and a filename equal to the first eight alphanumeric characters of the table name. If a failure occurs during this step of processing, this file can be examined to determine the cause of failure.
One fundamental difference between SQLREORG, the DBSPACE Reorganization tool, and SQLTABLE is that SQLREORG uses the UNLOAD/RELOAD commands of DBSU, while SQLTABLE uses DATAUNLOAD/DATALOAD. This provides several advantages to the SQLTABLE user. The RELOAD command performs the entire data load function as a single Logical Unit of Work (LUW). If the table or DBSPACE is very large, the entire LUW can be too large to be recorded within the database log file, forcing an archive in the middle of the RELOAD. The archive cannot take place until a checkpoint is taken, which requires a breakpoint without any active LUWs. The RELOAD LUW must therefore be rolled back by the database before the archive can complete. By using the DATALOAD command with the COMMITCOUNT option, SQLTABLE can successfully perform a table-level reorganization with an archive during the process.
Another SQLTABLE advantage is the use of a simple CMS file format by DATAUNLOAD that allows modification prior to the DATALOAD. The disadvantages of SQLTABLE mostly relate to the pure reorganization function. UNLOAD/RELOAD requires less external disk space than DATAUNLOAD/DATALOAD and will also execute faster. The DBSPACE level processing of SQLREORG also provides a considerably more efficient reorganization than the table-level processing of SQLTABLE, allowing empty pages to be returned to the database for use elsewhere and providing much better clustering properties to be maintained.