SQL statements allowed on the catalog

Certain SQL statements can be used to change the value of certain options for existing catalog indexes, sequences, and table spaces, or to add indexes to any of the catalog tables.

Table 1. SQL statements that can be used to change existing catalog indexes, sequences, and table spaces, or to add indexes to any of the catalog tables
SQL statement Index Allowable clauses and usage notes
ALTER INDEX IBM®-defined Only these clauses are allowed:
  • CLOSE
  • COPY
  • FREEPAGE
  • GBPCACHE
  • NOT PADDED
  • PADDED
  • PCTFREE
  • PIECESIZE

You cannot alter the GBPCACHE value for indexes DSNDXX01, DSNDXX02, and DSNDXX03, which are on catalog table SYSIBM.SYSINDEXES.
ALTER INDEX User-created All clauses are allowed, except for the following:
  • BUFFERPOOL
  • REGENERATE
  • COMPRESS YES
  • Any partitioning clause
ALTER SEQUENCE   The only clause allowed is MAXVALUE.

You can only change the MAXVALUE value of the catalog sequence DSNSEQ_IMPLICITDB. The only value specific must be an integer in the range 1–60000, inclusive.
ALTER TABLE  

Only these clauses are allowed:

  • DATA CAPTURE CHANGES
  • Start of changeADD VERSIONINGEnd of change
  • Start of changeDROP VERSIONINGEnd of change
  • Start of changeADD PERIODEnd of change
Start of changeThe ADD VERSIONING, DROP VERSIONING, and ADD PERIOD clauses can be specified on only the following tables:
  • SYSIBM.SYSTABLESPACESTATS
  • SYSIBM.SYSINDEXSPACESTATS
End of change
ALTER TABLESPACE   Only these clauses are allowed:
  • CLOSE
  • FREEPAGE
  • GBPCACHE
  • LOCKMAX
  • MAXROWS
  • PCTFREE
  • TRACKMOD

For DSNDB06.SYSSEQ, MAXROW can be specified only with a value of 1.

You can specify the LOCKSIZE keyword on the ALTER TABLESPACE statement for any catalog table spaces that are not LOB table spaces.

You cannot alter the GBPCACHE or MAXROWS value of some catalog table spaces. Do not specify GBPCACHE for the following table spaces:
Table spaces Table spaces
  • DSNDB06.SYSTSCOL
  • DSNDB06.SYSTSDBA
  • DSNDB06.SYSTSDBR
  • DSNDB06.SYSTSDBU
  • DSNDB06.SYSTSFAU
  • DSNDB06.SYSTSFLD
  • DSNDB06.SYSTSFOR
  • DSNDB06.SYSTSIPT
  • DSNDB06.SYSTSIXR
  • DSNDB06.SYSTSIXS
  • DSNDB06.SYSTSIXT
  • DSNDB06.SYSTSKEY
  • DSNDB06.SYSTSPKA
  • DSNDB06.SYSTSPKD
  • DSNDB06.SYSTSPKG
  • DSNDB06.SYSTSPKL
  • DSNDB06.SYSTSPKS
  • DSNDB06.SYSTSPKX
  • DSNDB06.SYSTSPKY
  • DSNDB06.SYSTSPLA
  • DSNDB06.SYSTSPLD
  • DSNDB06.SYSTSPLN
  • DSNDB06.SYSTSPLY
  • DSNDB06.SYSTSPVR
  • DSNDB06.SYSTSREL
  • DSNDB06.SYSTSSTM
  • DSNDB06.SYSTSSYN
  • DSNDB06.SYSTSTAB
  • DSNDB06.SYSTSTAU
  • DSNDB06.SYSTSTPT
  • DSNDB06.SYSTSTSP
CREATE INDEX User-created All clauses are allowed, except for:
  • CLOSE YES
  • CLUSTER
  • UNIQUE
  • DEFER YES (only on tables SYSINDEXES, SYSINDEXPART, and SYSKEYS)
  • COMPRESS YES
  • Any partitioning clause

The USING clause is ignored.

Indexes that are created with key-expressions are not allowed on the catalog.

The only value allowed for BUFFERPOOL is BP0.

You can create up to 500 indexes on the catalog.
DROP INDEX User-created The statement has no clauses.