The previous chapter described how to implement your database design. This chapter deals with the various maintenance tasks you may need to perform to maintain tables and dbspaces. The following tasks are discussed:
Maintaining Tables
There is considerable flexibility in how data can be entered.
When information is being shared, the owner of a table may choose to have other users copy it, so that they can make changes to their own copy of the table without affecting the original.
You may want to move tables to another dbspace to:
If a table resides in a PRIVATE dbspace and many users need to update that table at the same time, you should move it into a PUBLIC dbspace, which allows concurrent access.
You may want to move a table from a nonrecoverable dbspace to a recoverable one, or a recoverable dbspace to a nonrecoverable one.
The amount of information that you can store in a table depends on the size of the dbspace it is in, and the storage requirements of the other tables there. If a table requires more space for data or indexes, you should consider moving it to a larger dbspace.
It may be necessary to combine all the columns or a subset of the columns from different tables into a new table.
You may want to change the design of a table after it has been created: for example, add or delete columns, change the data type of a column, or change the name of the table.
You may wish to add referential integrity to tables that do not have it.
You may want to enforce the referential constraints when your tables are created, or defer enforcement until you have performed other activities.
The second application server can be a DB2 Server for VSE & VM application server, or another application server supporting IBM's implementation of the Distributed Relational Database Architecture (DRDA) protocol.
If tables are no longer required, you can remove them.
Maintaining Dbspaces
When you created a dbspace, you specified the following parameters for it: its potential size (in pages), its type (PUBLIC or PRIVATE), its storage pool assignment (STORPOOL), the number of pages for its header (NHEADER), the percentage of each page reserved for updates that cannot be placed in the original location (PCTFREE), the number of pages reserved for indexes (PCTINDEX), and the size of the locks (LOCK).
As requirements change, you may need to change some of these settings. You can change the PCTFREE and LOCK parameters with the ALTER DBSPACE statement. If any of the other parameters need to be changed, you will have to acquire a new dbspace (which satisfies your new requirements), and move all the tables from the old dbspace to the new one.
As part of maintaining your dbspaces, you may have to reorganize it to release pages back to a storage pool.
If a dbspace is no longer required, you can remove it and its contents by using the DROP DBSPACE statement.
There are VSAM restrictions when managing storage.
Reorganizing Catalog Table Indexes
The catalog tables have indexes to improve the speed of access. Occasionally, you should reorganize these indexes. See Reorganizing Indexes on the Catalog Tables.
After designing and creating a table, you may have to load data into it, copy it, move it from one dbspace to another, move data in it from one application server to another, change an aspect of its design, or remove it from the database.
This section reviews the possible ways to load data into tables. Many of these methods use the Database Services Utility commands: for more information on these commands, refer to the DB2 Server for VSE & VM Database Services Utility manual.
You can load data into tables interactively through the DBS Utility. To do this, invoke the utility so the terminal controls file input (SYSIN). You can then either enter multiple INSERT statements, or execute the DBS Utility DATALOAD TABLE command using the INFILE (*) subcommand.
The DBS Utility DATALOAD TABLE command will accept input data records in a user-created CMS file. One or more tables can be loaded during a single pass of the data records. The existing data in the tables loaded with this method are not affected. Rows are added to a table through the PREPARE, OPEN, PUT, and CLOSE facilities of SQL.
The DBS Utility DATALOAD TABLE command will also accept input data records in a CMS virtual reader file with no header. One or more tables can be loaded during a single pass of the data records. The existing data in the tables loaded with this method are not affected. Rows are added to a table through INSERT statements executed using the PREPARE and EXECUTE facilities of SQL.
Refer to the DB2 Server for VSE & VM Database Services Utility manual for more information.
To load data into a table from data records entered from a terminal, as an alternative to entering multiple INSERT statements, users can use the DBS Utility under VSE/ICCF in conversational mode. To initiate this, enter the following VSE/ICCF control statements:
/LOAD ARIDBS /OPTION GETVIS=AUTO /DATA INCON
In response to the prompt to ENTER DATA, the appropriate series of SQL statements or DBS Utility DATALOAD TABLE commands must be entered. After a DATALOAD TABLE command the user must enter the INFILE (*) subcommand to initiate input data record processing and the ENDDATA subcommand to end it. An outline of the interactive terminal input is:
CONNECT userid IDENTIFIED BY password;
DATALOAD TABLE (table-name)
column-name1 1-5
column-name2 6-7
·
·
·
INFILE (*)
data record
data record
·
·
·
ENDDATA
These commands are described in the DB2 Server for VSE & VM Database Services Utility manual.
Each record (row) is entered in a fixed format as defined by the column specifications in your DATALOAD command. In this example, the user enters column 1 data into typing positions 1-5 of the command line, column 2 data into positions 6-7, and so on.
Do not put quotation marks around character data, and do not use commas to separate data values. Such punctuation can be used outside the data positions of the command line defined by the column specifications of the DATALOAD command.
As an alternative to entering each input data record interactively, the user can embed DBS Utility commands and data records in the VSE/ICCF control statements. An outline of loading a table under VSE/ICCF in a nonconversational manner is:
/LOAD ARIDBS
/OPTION GETVIS=AUTO
/DATA
CONNECT userid IDENTIFIED BY password;
DATALOAD TABLE (table-name)
column-name1 1-5
column-name2 6-7
·
·
·
INFILE (*)
data record
·
·
·
ENDDATA
The ISQL INPUT statement enables a user to enter multiple rows of data into a table. The table name and (optionally) the column names need to be entered only once. The column names, along with their data types, are then displayed in the order that the data must be entered, and the user can then enter data one row at a time.
For data that is similar, the user can use the PF12 RETRIEVE function. That is, the user can retrieve the previous data row entered, and then type over the fields that are different. This can save keystrokes.
Data entered with an INPUT statement is not stored in the table until the INPUT statement is ended by an END statement. ISQL will issue an INSERT statement for every row entered, using the PREPARE and EXECUTE facilities of SQL. However, before the INPUT statement is ended, the data can be committed or backed out by the statement:
Remember that the AUTOCOMMIT mode affects the processing of the SAVE, BACKOUT, and CANCEL statements. For additional information on the ISQL INPUT, SAVE, and BACKOUT statements, refer to the DB2 Server for VSE & VM Interactive SQL Guide and Reference manual.
The DBS Utility DATALOAD TABLE command accepts SYSIPT data records or data records contained in a user-created sequential file. One or more tables can be loaded during a single pass of the data records. The existing data in the tables loaded with this method is not affected. The DATALOAD TABLE processing adds rows to a table through the PREPARE, OPEN, PUT, and CLOSE facilities of SQL.
A VSAM file can be converted to either of the following:
This sequential file can then be identified as the input data file to DBS Utility DATALOAD TABLE processing.
| Note: | The VSAM REPRO command should never be used to copy the DB2 database itself. |
Data can be copied into a table from other tables by using the following methods:
All of these techniques allow the source of the data to be copied to be identified by a view that is defined on one or more tables. A view can be used to identify the target table if the view definition meets the requirements defined for inserting rows into a view.
If referential constraints are in place on tables in which you wish to load data, you should consider whether you would like to enforce constraints while the data is loading or after it is loaded. See Enforcing Referential Constraints for more information.
To make a copy of an existing table, use the DBS Utility UNLOAD and RELOAD commands.
A user with the user ID SMITH has the SELECT privilege on the SQLDBA.EMPLOYEE table. To make a copy of this table, to be called SMITH.EMPLOYEE, in the PRIVATE dbspace SMITHDB, enter the following commands either in a CMS file called CONTROL DBSINPUT A or in the appropriate job control:
CONNECT SMITH IDENTIFIED BY SMITHPW;
UNLOAD TABLE (SQLDBA.EMPLOYEE) OUTFILE(TEMPFIL);
RELOAD TABLE (EMPLOYEE) NEW (SMITHDB)
INTABLE (SQLDBA.EMPLOYEE) INFILE(TEMPFIL);
To execute these commands in VM, invoke the DBS Utility, as follows:
FILEDEF TEMPFIL DISK MYDATA |MYFILE A4 (RECFM VBS BLOCK 800
SQLDBSU SYSIN(CONTROL DBSINPUT A) SYSPRINT(LIST DBSLIST A)
The RELOAD statement creates tables without const raints, losing all referential constraints on the table you are copying. You must reinstate referential constraints later with the ALTER TABLE statement. See Altering Referential and Unique Constraints.
The RELOAD statement with the 'NEW' parameter recreates the table without field procedures. Instead of reloading the table using the 'NEW' parameter, recreate the table to include field procedures and reload the table using the 'PURGE' parameter.
To move a table from one dbspace to another, you must first unload it using the DBS UNLOAD command, drop it from the database, then reload it into the new dbspace. When a table is dropped, all indexes, privileges, views, primary and foreign keys, and unique constraints for it are removed, and must be re-established.
As well, if a table has field procedures associated with it, the table should be dropped and recreated to include the field procedures and reloaded using the 'PURGE' parameter.
User SMITH has a table (called SMITH.MYTABLE) that he wishes to move from the SMITH.PERSONAL dbspace to the SMITH.SECRET dbspace.
Enter the following commands in either a CMS file called CONTROL DBSINPUT A, or inside the appropriate job control:
CONNECT SMITH IDENTIFIED BY SMITHPW;
UNLOAD TABLE (SMITH.MYTABLE) OUTFILE(TEMPFIL);
DROP TABLE SMITH.MYTABLE;
RELOAD TABLE (SMITH.MYTABLE)
NEW (SMITH.SECRET) INFILE(TEMPFIL);
In VM you run these commands by invoking the DBS Utility, as follows:
FILEDEF TEMPFIL DISK MYDATA |MYFILE A4 (RECFM VBS BLOCK 800
SQLDBSU SYSIN(CONTROL DBSINPUT A) SYSPRINT(LIST DBSLIST A)
It may be necessary to combine all columns or a subset of the columns from different tables into a new table. You can do this through ISQL or the DBS Utility using the following procedure:
To identify authorizations and views on the old tables, you can query the system catalog with a SELECT statement entered through ISQL or the DBS Utility. The following tables contain information pertinent to this task:
To identify the base tables for the view ORGANIZATION, enter the following query:
SELECT BNAME FROM SYSTEM.SYSUSAGE
WHERE DNAME = 'ORGANIZATION'
To identify the view definitions, enter the following query:
SELECT VIEWTEXT FROM SYSTEM.SYSVIEWS
WHERE VIEWNAME = 'ORGANIZATION'
To identify the users who have privileges to access the view or its base tables, enter the following query:
SELECT GRANTEE, STNAME FROM SYSTEM.SYSTABAUTH
WHERE TTNAME = 'ORGANIZATION'
If a view is defined for all the columns required in the new table, steps 1, 2, and 3 (needed to merge data from multiple tables) can be replaced by the following:
In VM: Include the following SQL statements and DBS Utility commands within your DBS Utility control file to perform the above task:
CONNECT userid IDENTIFIED BY userpw; UNLOAD TABLE creator.viewname OUTFILE(DUMPFIL); RELOAD TABLE creator.newtablename NEW INFILE(DUMPFIL);
Invoke the DBS Utility, as usual, to process the above statements and commands.
In VSE: Use the following job control commands, SQL statements, and DBS Utility commands to perform the above task:
// JOB MERGE DATA
// EXEC PROC=DBNAME01
// ASSGN SYS005,...
// ASSGN SYS004,...
// TLBL DUMPFIL,...
·
·
·
CONNECT userid IDENTIFIED BY userpw;
UNLOAD TABLE creator.viewname OUTFILE(DUMPFIL)
RELOAD TABLE creator.newtablename NEW INFILE(DUMPFIL)
/&
See the DB2 Server for VSE & VM Database Services Utility manual for details.
If you want to change the design of a table after it has been created, use the SQL ALTER TABLE statement. This will not change the data in the table; only its specifications. You can:
When you alter a table, information in the system catalog about it is also changed. For example, when you add a new column to a table, SYSTEM.SYSCOLUMNS is changed to record it, and the field in there that records the number of columns is increased by one.
To alter a table, you must have the ALTER privilege on it, and if the operation involves a primary key you must have the ALTER privilege on all dependent tables as well. If the operation involves a foreign key, you must have the REFERENCES privilege on the parent table.
You can alter any table if you have DBA authority.
You cannot delete a column, change the name of a column, change the data type of a column, or add or change a field procedure for a column for existing tables using the ALTER TABLE statement. To do these operations, you must drop the existing table and re-create it.
There are two ways to change the data type of the DEPTNAME column of the DEPARTMENT table from VARCHAR(36) to VARCHAR(40):
CREATE TABLE DEPT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(40) NOT NULL,
MGRNO CHAR(6) ,
ADMRDEPT CHAR(3) NOT NULL,
PRIMARY KEY (DEPTNO) )
INSERT INTO DEPT SELECT * FROM DEPARTMENT
Indexes, views, and privileges have to be reestablished for the new table DEPT; only the data is copied from the DEPARTMENT table. Also, all applications that used the original table must be changed to reflect the new table name, then re-preprocessed.
When you add a column to an existing table, it is placed on the far right.
The physical records are not actually changed until users insert values in the new column, so access time to the table is not affected immediately. After values are inserted, however, this could impact performance by forcing rows onto another physical page. To avoid that situation, define enough free space on each page ahead of time.
You cannot define the new column as NOT NULL; it must allow NULL values.
Add a new column to the table DEPARTMENT, containing a location code for the department. The column name is LOCNCODE, and its data type is CHAR (4).
ALTER TABLE DEPARTMENT
ADD LOCNCODE CHAR (4)
Table 15 shows part of the original table.
Table 15. Before Adding a New Column to a Table
| DEPTNO | DEPTNAME | MGRNO | ADMRDEPT |
|---|---|---|---|
| A00 | SPIFFY COMPUTER SERVICE DIV. | 000010 | A00 |
| B01 | PLANNING | 000020 | A00 |
| C01 | INFORMATION CENTER | 000030 | A00 |
Table 16 shows the table after adding the new column and updating a
location code in the third row.
Table 16. After Adding a New Column to a Table and Updating a Row
| DEPTNO | DEPTNAME | MGRNO | ADMRDEPT | LOCNCODE |
|---|---|---|---|---|
| A00 | SPIFFY COMPUTER SERVICE DIV. | 000010 | A00 | ? |
| B01 | PLANNING | 000020 | A00 | ? |
| C01 | INFORMATION CENTER | 000030 | A00 | B126 |
If you plan to let the database manager enforce referential integrity in a set of tables, see Considerations for Referential Integrity when Creating Tables and Specifying a UNIQUE Constraint.
The following terms are used in the discussion of the ALTER TABLE statement:
Table 17 is a summary of the authorization required to alter
referential constraints.
Table 17. Authorization Required when Altering Referential Constraints
| Statement | Parent Table | Dependent Table | ||
|---|---|---|---|---|
ADD
|
|
| ||
DROP
|
|
| ||
ACTIVATE
|
|
| ||
DEACTIVATE
|
|
| ||
| ||||
The following restrictions apply when you add a primary key, a foreign key, or a unique constraint to an existing table:
For further information on referential integrity, refer to Elements of Referential Integrity.
The following restrictions apply when you drop a primary key or a foreign key from an existing table:
In both situations, you should consider carefully the effects on your application programs of dropping keys. The primary key of a table is intended to serve as a permanent, unique identifier of the occurrences of the entities it describes, and quite likely some of your programs depend on that. The foreign key defines a referential relationship and a delete rule, and without it your programs must enforce the constraints.
To activate a primary key you must have ALTER privilege on the parent and dependent tables and REFERENCES privilege on all dependent tables.
If any dependent foreign keys were deactivated implicitly when the primary key was made inactive, they will be verified against the primary key. If the primary key index can be created successfully and the dependent foreign key values are found in the parent table's primary key, then the primary key and the dependent foreign keys will be activated. If any of these processes fail, none of the keys will be activated.
Activating the primary key will neither verify nor affect the status of any dependent foreign keys that were deactivated explicitly with the ALTER TABLE table-name DEACTIVATE FOREIGN KEY statement.
To activate a foreign key you must have ALTER privilege on the dependent table and REFERENCES privilege on the parent table.
If a foreign key is already active, attempts to activate it are ignored. If the primary key of the parent table referenced by this foreign key is inactive, the foreign key cannot be activated. Otherwise, the inactive foreign key will have its values verified against its parent table. If all values can be found in the parent's primary key, the foreign key will be activated.
To activate a unique constraint you must have ALTER privilege on the table. The unique constraint will be activated only if all values in its key are unique. If there are duplicate values you must change them to be unique before the constraint can be activated.
To activate the primary key, each unique constraint, and each explicitly inactive foreign key in a table, use the ACTIVATE ALL option. You must have the required ALTER and REFERENCES privileges.
Activating a primary key or unique constraint that is already active causes the unique index associated with the key or constraint to be reorganized. This is more efficient than deactivating the key or constraint (which would drop the underlying index), and then activating the key or constraint (which would re-create the underlying index). For more information on the benefits of reorganizing an index, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.
Deactivating a primary key drops the primary key index from the parent table and implicitly deactivates all active dependent foreign keys. This limits the access to all inactive dependent tables to the creator or a DBA, and allow deferred constraint enforcement. For information on deferred constraint enforcement see Enforcing Referential Constraints.
To deactivate a primary key you must have ALTER and REFERENCES privileges on the parent table, and ALTER privilege on all dependent tables.
To deactivate a referential constraint that is active, you must have ALTER privilege on the dependent table and REFERENCES privilege on the parent table.
If a foreign key has been explicitly deactivated already, attempts to deactivate it again are ignored.
Deactivating a foreign key will make the two tables in the relationship inactive. Access to the inactive table is limited to the creator or a DBA. For information on the effects of deactivating a foreign key, see Advantages and Disadvantages of Deferred Constraint Enforcement.
Deactivating a unique constraint drops the unique index associated with the constraint, causing the table to become inactive. This will limit access to the table to its creator or a DBA.
Two forms of enforcement are possible:
After the referential constraints have been defined, the enforcement of the referential constraint is immediate. That is, the insert, update, and delete rules are enforced when the INSERT, UPDATE, and DELETE statements are issued. During immediate constraint enforcement, keys and tables are in the active state.
A table can be made inactive by deactivating its primary key, any of its foreign keys, any of its unique constraints, or a dependent foreign key, by using the ALTER TABLE statement. A referential relationship is between two keys in different tables. If either a primary or foreign key is deactivated, both tables become inactive.
When a table is in an inactive state, only the owner or someone with DBA authority can issue Data Manipulation Language (DML) statements against it. No one can issue DML statements (for example, SELECT or UPDATE statements) against any table that would result in implicit access of an inactive table to enforce referential constraints.
When the keys are activated, the constraints will be verified automatically and the tables become active again.
You may want to deactivate the enforcement of referential integrity among tables to improve performance when you are loading data into a table.
When referential integrity is active between two tables, each INSERT statement on a dependent table causes a check to be issued against the parent table. This check verifies that the foreign key value being inserted has a matching primary key value in the parent table. When data is being loaded into a dependent table, each inserted row causes a check of the parent table; if many rows are being loaded, the overhead of this checking becomes significant. In this case, you may improve your overall performance of the load by deactivating any referential constraints. When the load completes, you then reactivate them to validate the data.
If referential integrity is in effect at the beginning of an LUW, and the constraints are deactivated, the data loaded and the constraints re-activated all within the same LUW, then referential integrity exists at the end of the LUW as well. However, within that LUW, referential constraints are not enforced. You could load rows into the dependent table that had no parents when loaded. Since the database can be in an inconsistent state during an LUW, but not at its completion, you can use a more flexible sequence of statements within an LUW. At some point you must load parent rows for the dependent rows into the parent table. Otherwise, you would be unable to reactivate the referential constraint. There are some disadvantages to deactivating a referential constraint between tables:
If a relatively small number of rows are added to the table by the load process, then the costs of reactivating the referential constraints may exceed the savings realized by deferring referential constraint enforcement on each row loaded.
You should deactivate the referential constraints between tables only when large amounts of data are to be loaded, or when a significant amount of data is to be loaded in an order that violates the referential constraint at some point during the data-loading operation. For example, you can load new rows into a dependent table before loading matching rows into the parent table only while the referential constraint is inactive.
If you deactivate a referential constraint in order to load data, then receive an error when you try to reactivate it, it could be for one of the following reasons:
| Note: | When the above error occurs, SQLCODE -667 (SQLSTATE 22519) and the name of the constraint in error are returned as a message token in SQLCA. |
To find duplicate primary key values, use the statement shown below. In the example, the name of the table is P1, and the primary key is represented by the columns PKCOL1, PKCOL2, and so on, for all columns that form the primary key:
SELECT PKCOL1, PKCOL2, ... FROM P1
GROUP BY PKCOL1, PKCOL2, ...
HAVING COUNT(*) > 1
You could then eliminate the duplicate values with UPDATE and DELETE statements, or move them to a special table if you do not want to eliminate them immediately.
To move the rows to a special table (called an EXCEPTION table in this explanation), create a table with the same column definitions as the original table (but with no key definitions). If there are many duplicate values, you may want to create a nonunique index for the duplicate primary key columns in the EXCEPTION table to improve performance.
Use the statements shown below to copy the rows with duplicate primary key values into the EXCEPTION table (called E1 in this example):
INSERT INTO E1
SELECT * FROM P1 A WHERE EXISTS
(SELECT PKCOL1, PKCOL2, ... FROM P1 B
GROUP BY PKCOL1, PKCOL2, ...
HAVING COUNT(*) > 1
AND B.PKCOL1 = A.PKCOL1
AND B.PKCOL2 = A.PKCOL2
... )
To remove these rows from P1, use this statement:
DELETE FROM P1 A WHERE EXISTS
(SELECT 1 FROM E1
WHERE E1.PKCOL1 = A.PKCOL1
AND E1.PKCOL2 = A.PKCOL2
... )
Foreign key values may not match primary key values because either of them may be wrong. This example shows you how to move the nonmatching foreign keys to a separate table. Then, you can determine whether the foreign or the primary keys are wrong, and fix them with INSERT, UPDATE, or DELETE statements.
This statement retrieves nonmatching foreign key values. In the example, P1 is the parent table; C1 is the dependent table; PKCOL1, PKCOL2, and so on form the primary key; and FKCOL1, FKCOL2, and so on form the foreign key.
SELECT FKCOL1, FKCOL2, ... FROM C1 A
WHERE (FKCOL1 IS NOT NULL AND
FKCOL2 IS NOT NULL AND
... )
AND NOT EXISTS
(SELECT 1 FROM P1 B
WHERE B.PKCOL1 = A.FKCOL1 AND
B.PKCOL2 = A.FKCOL2 AND
... )
To move the rows to a special table (called an EXCEPTION table in this explanation), create a table with the same column definitions as the dependent table (but with no key definitions). If there are many duplicate values, you may want to create a nonunique index for the foreign key columns in the EXCEPTION table to improve performance. To copy the rows with nonmatching foreign keys to the EXCEPTION table (E1 in this example), use the following statement:
INSERT INTO E1
SELECT * FROM C1 A
WHERE (FKCOL1 IS NOT NULL AND
FKCOL2 IS NOT NULL AND
... )
AND NOT EXISTS
(SELECT 1 FROM P1 B
WHERE B.PKCOL1 = A.FKCOL1 AND
B.PKCOL2 = A.FKCOL2 AND
... )
To remove the rows from C1, use the following statement:
DELETE FROM C1 A WHERE EXISTS
(SELECT 1 FROM E1
WHERE E1.FKCOL1 = A.FKCOL1
AND E1.FKCOL2 = A.FKCOL2
... )
You can use the DBS Utility to move data from one application server to another.
Moving data from a DB2 Server for VSE & VM application server to a remote DRDA application server requires unloading the data from the DB2 Server for VSE & VM application server using the DBS Utility DATAUNLOAD command and reloading the data into the other application server using the DBS Utility DATALOAD command. Moving data from one DB2 Server for VSE & VM application server to another local DB2 Server for VSE & VM application server can be done as above, or by using the DBS Utility UNLOAD and RELOAD commands.
For more information about DBS Utility commands, refer to the DB2 Server for VSE & VM Database Services Utility manual.
Notes:
For example, an application server uses a CHARNAME value of ENGLISH (or the CCSID equivalent), and another application server uses a CHARNAME value of GERMAN (or the CCSID equivalent). Issue the SQLINIT EXEC |(in VM), the transaction DSQU (in CICS), or the VSE batch program |ARIRBGUD (JCL: ARISBGUD.Z) and specify a CHARNAME for the application requester corresponding to the CHARNAME of one of the application servers (either ENGLISH or GERMAN). Then, to ensure the integrity of the data when moving it between these two application servers, specify the same CHARNAME value for the application requester for both the DATAUNLOAD (or UNLOAD) and DATALOAD (or RELOAD) operations. If ENGLISH is the CHARNAME value specified for the application requester for the data unload operation, then it must also be set to ENGLISH for the data load operation. You can then perform the data unloading and reloading operations.
For more information on CCSID conversion, see the DB2 Server for VSE System Administration manual.
To remove tables from the database, use the DROP TABLE statement. For example, to remove a table called PROJECT, enter:
DROP TABLE PROJECT
Only the table's creator or a user with DBA authority can remove the table. If you have DBA authority, include the user ID of the owner to remove a table. For example, to remove SMITH's table called PROJECT, enter:
DROP TABLE SMITH.PROJECT
When a table is dropped, the row in the SYSTEM.SYSCATALOG catalog table that contains information about it is deleted. Any other objects that depend on that table are also dropped. As a result:
You must commit the DROP statement on a table before you can re-create a table of the same name, or before you can create any new indexes with the same name as an index on the table being dropped.
You may need to change the parameters of a dbspace for any of the following reasons:
You may have underestimated the storage required by the tables in the dbspace, and need to increase its potential size (in pages).
You may want to change the storage pool assignment, which determines whether a dbspace is recoverable or nonrecoverable.
If the tables in a PRIVATE dbspace are to be shared by many users, then you should consider making it PUBLIC.
At the front of every dbspace are one to eight header pages, which contain control information on the tables and indexes stored there. You may need to increase the number of these pages.
If your dbspace contains more indexes than expected, you may need to increase the index space to accommodate them.
You may want to change the percentage of each data page reserved for updates of rows resulting in larger rows that cannot be placed in the original locations in the page.
For PUBLIC dbspaces, you may change the locking level. A lower lock level allows more users to access the same table at the same time; however, there is a cost because of lock acquisitions, an increased possibility of lock escalations. If lock escalation occurs frequently, you may want to increase the locking level. Refer to the DB2 Server for VSE & VM Diagnosis Guide and Reference manual. for more information about lock escalations.
For a review of these parameters, see Acquiring Dbspaces.
There is no statement to change these five parameters of a dbspace. If you need to change any of them, you must move all the data in the current dbspace to another dbspace that has the required characteristics. To do this:
To identify the tables, views, authorizations, and referential constraints related to the dbspace, query the system catalog.
To identify the tables with field procedures, query the SYSFILEDS and SYSFPARMS tables.
To increase the storage capacity of a PRIVATE dbspace called SMITH.SAMPLE to 2 048 pages with defaults for the other dbspace parameters, use the following SQL statements and DBS Utility commands:
CONNECT SQLDBA IDENTIFIED BY SQLDBAPW; UNLOAD DBSPACE (SMITH.SAMPLE) OUTFILE (TEMPFIL); DROP DBSPACE SMITH.SAMPLE; ACQUIRE PRIVATE DBSPACE NAMED (SMITH.SAMPLE) (PAGES=2048); RELOAD DBSPACE (SMITH.SAMPLE) NEW INFILE(TEMPFIL);
Invoke the DBS Utility, as usual, to process the above statements and commands (see the DB2 Server for VSE & VM Database Services Utility manual for details).
Indexes, views, authorizations, and referential constraints must be recreated for all the tables in the dbspace.
To change these parameters, use the ALTER DBSPACE statement. You must have DBA authority or (in the case of a PRIVATE dbspace) be the owner of the dbspace.
Change the PCTFREE parameter to 10 for the dbspace called MYDBSPACE. type:
ALTER DBSPACE MYDBSPACE (PCTFREE = 10)
To change both the PCTFREE and the LOCK parameters at the same time, type:
ALTER DBSPACE MYDBSPACE (PCTFREE = 10, LOCK = PAGE)
Reorganizing a dbspace releases pages in it back to its storage pool. There are two reasons why you might want to do this:
After the database has been restarted (with STARTUP=W), there will be a row in the catalog table SYSDROP for the dropped table. Any subsequent DROP TABLE statements will cause SYSDROP to be processed. When the database manager processes the row for the dropped table, it will end and issue a message indicating that the storage pool is full unless you take other steps to provide sufficient pages in the storage pool for shadow pages. You can provide sufficient pages in the storage pool by adding dbextents to the storage pool, or by reorganizing the dbspace where the table resides.
If reorganizing the dbspace does not provide sufficient shadow pages to allow you to drop the table, then you must add dbextents to the storage pool. For information on adding dbextents, see the DB2 Server for VM System Administration or DB2 Server for VSE System Administration manual.
Once a page is allocated to a dbspace, it remains allocated until you drop the dbspace. This can cause the storage pool that contains the dbspace to become short on storage. For example, if a large table occupied a dbspace, and has been dropped, all pages used to store the rows for that table are still allocated to the dbspace. To determine whether many empty pages are allocated to a dbspace, enter the SHOW DBSPACE operator command.
To reorganize a dbspace, follow these steps:
Notes:
To drop the contents of a dbspace and return it to the available state, issue the DROP DBSPACE statement. Dbspaces that are available can then be reacquired, using the ACQUIRE DBSPACE statement.
When a dbspace is dropped, all tables in it are also dropped. When a table is dropped, all authorizations, views, referential constraints, unique constraints, and field procedures relating to it are dropped.
If a dbspace contains only one table, it is more efficient to drop and then reacquire the entire dbspace later, than to drop the table.
The DROP DBSPACE statement may be carried out on both PUBLIC and PRIVATE dbspaces. You must have DBA authority to delete a dbspace or (in the case of a PRIVATE dbspace) be the owner. No user, not even one with DBA authority, can delete the dbspace that contains the system catalog.
To remove your own PRIVATE dbspace named MYDBSPACE, type:
DROP DBSPACE MYDBSPACE
VSAM defines storage for DB2 Server for VSE databases but it does not manage this storage. VSAM commands such as EXPORT, IMPORT, REPRO, and VERIFY should never be used on the DB2 Server for VSE database. If you receive an error message indicating an OPEN error (RC=74), ignore it and do not run VERIFY.
The catalog indexes need to be reorganized when indexes on the catalog tables become fragmented, and the database manager can no longer insert entries into the catalog dbspace.
Index fragmentation often happens in an application development environment. Application development requires frequent preprocessing; and each time a program is preprocessed, many entries are added to the catalog tables. It may not be possible to plan properly for the range of index keys that might be created.
Index fragmentation can lead to the inefficient use of the index pages of the catalog dbspace (SYS0001). If most of the index pages in your catalog dbspace are occupied, fragmentation is a likely cause.
To determine the number of index pages occupied in the catalog dbspace, enter the SHOW DBSPACE command. (The number of this catalog dbspace is 1; so you type SHOW DBSPACE 1.) If there is a high percentage of occupied pages, consider running the catalog index reorganization utility, which optimizes the indexes as they exist on the catalog tables.
To run the catalog index reorganization utility in a VSE environment, start the database in single user mode with STARTUP=I specified. Figure 20 shows an example of the job control statements.
Figure 20. Example Job Control to Reorganize the Catalog Indexes
// JOB REORG
|// EXEC PROC=ARIS71SL
// EXEC PROC=DBNAME01
// EXEC PGM=ARISQLDS,SIZE=AUTO,PARM='SYSMODE=S,STARTUP=I,PARMID=name'
/*
/&
Notes:
See the DB2 Server for VSE System Administration manual for a description of initialization parameters.
To avoid the processing involved in switching log modes, use the same LOGMODE that you normally use.
To run the catalog index reorganization utility in a VM environment:
Because the catalog index reorganization utility runs in single user mode, the only way to trace it is with the TRACRDS, TRACDBSS, TRACDSC, and TRACCONV initialization parameters. The TRACE operator command cannot be used in single user mode.