Managing database tables
In PATROL for DB2 UDB, you can view the tables associated with a database, check and reorganize the table organization, run table statistics, and run the Import and Export utilities. To complete the table management tasks, click on the icon that represents the database that you want to work with and use the following procedures.
To list the tables defined for a database
- Right-click on the database icon and select Database Administration > Table Mgmt > List Tables.
- Select one of the following types of tables and then click OK:
- ALL
- SYSTEM
- USER
To determine whether a table needs reorganization
- Right-click on the database icon and select Database Administration > Table Mgmt > Reorganize Check.
- Select the Yes or No option to indicate whether you want to update the statistics (the update occurs when you click the OK button).
- Select one of the following options to indicate which tables to check:
- USER
- SYSTEM
- ALL
- The following table
- If you select The following table, type a table name in the box.
- Click OK.
If the table needs to be reorganized, see the following procedure.
To reorganize a table
Reorganizing a table eliminates fragmented data and compacts sparse data in a table. Perform this procedure after you have determined that a database table warrants reorganization. You can run the Reorganize Check command to determine whether reorganization is needed.
- Right-click on the database icon and select Database Administration > Table Mgmt > Reorganize Table.
- In the window that appears, type the name of the table to reorganize.
- (Optional) Enter the Reorg index to use and the Temporary tablespace name.
To update statistical information for a table by using the runstats utility
After you have reorganized a table, perform this procedure to collect and update the new table statistics.
- Right-click on the database icon and select Database Administration > Table Mgmt > Run Statistics.
- In the window that appears, type the table name, and select the statistics to gather from the following options:
- BASIC (default)
- DISTRIBUTION
- NONE
- Indicate the Index Statistics from the following options:
- BASIC
- EXTENDED
- NONE (default)
- Select one of the following Table Share Level options, and then click OK:
- READ ONLY
- READ-WRITE
To import and export data
Perform the following procedure to run the Import and Export utilities in PATROL for DB2 UDB.
- Right-click on the database icon and select Database Administration > Table Mgmt.
- select one of the following commands:
- select Import Data and proceed to the following procedure.
- select Export Data and skip to the procedure on To export data from a table to an external source file.
To import data into a table or view from an external file
- Right-click on the database icon and select Database Administration > Table Mgmt > Import Data.
- In the Import Data window, enter the name of the source file and the node number of the node where the file resides.
- Select the Import Filetype from the list, enter any optional settings to apply, and select the Import Data Mode to use.
- Specify the table in which to import the data and click OK.
If a problem occurs
A problem will occur if you enter an incorrect file type or file name. Also, you must enter a valid table name. Verify that the information that you have entered is correct. To review the table names in the database, use the List Tables command from the database menu.
To export data from a table to an external source file
- Right-click on the database icon and select Database Administration > Table Mgmt > Export Data.
- In the Export Data window, enter the file name for the target file and the node number where the file resides or will reside*.*
- Select the Export filetype, enter the SQL statement to use for exporting the data, and specify any optional settings.
If a problem occurs
Verify that you have specified the correct directory to which to export data. Also, ensure that the file system to which you are exporting data has enough space to handle the exported data.