ALTER SCHEMA statement

The ALTER SCHEMA statement modifies the data capture attribute of an existing schema.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

Owner of the schema, as recorded in the OWNER column of SYSCAT.SCHEMATA catalog view
DBADM authority

Syntax

Description

schema-name

Identifies the schema to be altered. The schema-name must identify a schema that exists at the current server (SQLSTATE 42704).

DATA CAPTURE

Indicates whether extra information for data replication is to be written to the log.

NONE: Indicates that no extra information for data replication will be logged.
CHANGES: Indicates that extra information regarding SQL changes to this schema will be written to the log. This option is required if this schema will be replicated and a replication capture program is used to capture changes for this schema from the log.

ENABLE ROW MODIFICATION TRACKING

Indicates tables created in the schema are to be enabled for logical backup. This only applies to columnar organized tables. For a list of restrictions, see Schema enabled for row modification tracking.

Notes

Altering the DATA CAPTURE attribute at the schema level causes newly created tables to inherit the DATA CAPTURE attribute from the schema if one is not specified at the table level. Altering the DATA CAPTURE attribute at the schema level does not affect the DATA CAPTURE attribute of existing tables within that schema. If the DATA CAPTURE attribute is changed and any existing tables do not match the new attribute, a warning is returned (SQLSTATE 01696).
To find the list of tables that have the DATA CAPTURE attribute set to CHANGES, issue the following query:
```
SELECT TABNAME, TABSCHEMA FROM SYSCAT.TABLES
  WHERE TYPE IN ('T','S','L') 
    AND DATACAPTURE <> 'N'
```
To find the list of tables that have the DATA CAPTURE attribute set to NONE, issue the following query:
```
SELECT TABNAME, TABSCHEMA FROM SYSCAT.TABLES
  WHERE TYPE IN ('T','S','L')
    AND DATACAPTURE = 'N'
```