Table space versions
Db2 creates a table space version each time that you commit one or more specific schema changes by using the ALTER TABLE statement.
Versioning is always done at the table space
level. The version of a table matches the table space version that it corresponds with. For example,
consider that you have two tables in one table space, which is defined with DEFINE YES. The tables
are named TABLE1 and TABLE2. The version for both tables and the
table space is 0 (zero). If TABLE1 is altered, the version for
TABLE1 becomes SYSTABLES.VERSION = 1, and the table space version
becomes SYSTABLESPACE.CURRENT_VERSION = 1. At this point, the version for
TABLE2 is still SYSTABLES.VERSION = 0. Now, when the changes for
TABLE1 are committed, and TABLE2 is altered, the version for
TABLE2 becomes SYSTABLES.VERSION = 2, which corresponds with the
table space version of SYSTABLESPACE.CURRENT_VERSION = 2. The version of
TABLE2 skips in the range 0–2, because SYSTABLESPACE.CURRENT_VERSION =
1 was already used by TABLE1.
If you create table
TABLE3 in the same table space, when the definition of TABLE3 is
complete, the version for TABLE3 becomes SYSTABLESPACE.CURRENT_VERSION =
2, to match the current table space version.
- Extending the length of a character (CHAR data type) or graphic (GRAPHIC data type) column
- Changing the type of a column within character data types (CHAR, VARCHAR)
- Changing the type of a column within graphic data types (GRAPHIC, VARGRAPHIC)
- Changing the type of a column within numeric data types (SMALLINT, INTEGER, FLOAT, REAL, FLOAT8, DOUBLE, DECIMAL, BIGINT)
- Adding a column to a table
- Extending the length of a varying character (VARCHAR data type) or varying graphic (VARGRAPHIC data type) column
- Altering the maximum length of a LOB column
- Altering the inline length of a LOB column
- Extending the precision of the TIMESTAMP column
In general, Db2 creates only one table space version if you make multiple
schema changes in the same unit of work. If you make these same schema changes in separate units of
work, each change results in a new table space version. For example, the first three ALTER TABLE
statements in the following example are all associated with the same table space version. The scope
of the first COMMIT statement encompasses all three schema changes. The last ALTER TABLE statement
is associated with the next table space version. The scope of the second COMMIT statement
encompasses a single schema change.
ALTER TABLE ACCOUNTS ALTER COLUMN NAME SET DATA TYPE VARCHAR(40);
ALTER TABLE ACCOUNTS ALTER COLUMN ADDRESS SET DATA TYPE VARCHAR(60);
ALTER TABLE ACCOUNTS ALTER COLUMN BALANCE SET DATA TYPE DECIMAL(15,2);
COMMIT;
ALTER TABLE ACCOUNTS ALTER COLUMN ACCTID SET DATA TYPE INTEGER;
COMMIT;
When Db2 does not create a new table space version
Db2 does not create a table space version under the following circumstances:
- You add a column to a table in the following situations:
- You created the table space with DEFINE NO, the current version is 0, and you add a column before adding any data is added to the table. If you commit the change and add another column, the version is still 0.
- You created the table space with DEFINE YES. After adding a column or altering a column, committing the change, and adding no data to the table, you add another column.
- A non-partitioned table space and a table that it contains are not in version 0 format. No data is in the current committed version format. You add a column to the table.
- You specify the same data type and length that a column currently has, so that its definition does not actually change.
- You alter the maximum length of a LOB column and the table does not have a version number yet.