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.

Start of change 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 from 0 to 2, because SYSTABLESPACE.CURRENT_VERSION = 1 was already used by TABLE1. End of change

The following schema changes might result in DB2 creating a 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 (SMALL INTEGER, INTEGER, FLOAT, REAL, FLOAT8, DOUBLE, DECIMAL)
Adding a column to a table
Extending the length of a varying character (VARCHAR data type) or varying graphic (VARGRAPHIC data type) column, if the table already has a version number that is greater than 0
Altering the maximum length of a LOB column, if the table already has a version number that is greater than 0
Altering the inline length of a LOB column
Extending the precision of the TIMESTAMP column

Begin general-use programming interface information.

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;

End general-use programming interface information.

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 extend the length of a varying character (VARCHAR data type) or varying graphic (VARGRAPHIC data type) column, and the table does not have a version number yet.
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.