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 between 1 and 60000, inclusive.
ALTER TABLE  

Only these clauses are allowed:

  • DATA CAPTURE CHANGES
Start of changeALTER TABLESPACEEnd of change Start of change End of change Start of changeOnly these clauses are allowed:
  • CLOSE
  • FREEPAGE
  • GBPCACHE
  • LOCKMAX
  • MAXROWS
  • PCTFREE
  • TRACKMOD
You cannot alter the GBPCACHE or MAXROWS value of some catalog table spaces. Do not specify GBPCACHE for the following 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

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.

End of change
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

Start of changeThe USING clause is ignored.End of change

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.