System Tables

New and changed system tables in Adaptive Server 15.0

Adaptive Server version 15.0 provides the necessary row-locked catalog infrastructure to support enhanced, multi-user-concurrent data-definition language (DDL) operations. However, this release does not change the catalog locking behavior for DDL operations. Applications that perform heavy multi-user DDL operations (for example, creating or dropping tables in tempdb, will not see any change in behaviour in this release for catalog blocking, or any increased DDL concurency.

New system tables

Table

Description

syspartitions

syspartitions is completely changed from the pre-15.0 version of the table. All columns are new. syspartitions supports both semantic and round-robin partitioning of tables and indexes.

syspartitionkeys

Contains a row for each column in a partition key for each hash-, range-, and list-partitioned table.

Changed system tables

Table

Change

syscolumns

New fields:

  • computedcol

  • status3

New columns:
  • encrtype –Type of encryption

  • encrlen – Length of encrypted column

  • encrkeydid – Encryption key id

  • encrkeydb – Database name containing encryption key

  • encrdate – Date column was encrypted.

New bits in status2 field:

  • Hex: 0x00000010, Decimal 16 – the column is a computed column.

  • Hex: 0x00000020, Decimal 32 – the column is a materialized computed column.

  • Hex: 0x00000040, Decimal 64 – the column is a computed column in a view.

sysconstraints

New internal bit in status field: Hex 0x0100, decimal 265 – indicates a computed column object.

sysdevices

New columns:
  • vdevno – device identification number

  • crdate – date device created

  • resizedate – date size of device changed

  • status2 – Additional status2 bits.

sysusages

New column: vdevno – device identification number

sysstatistics

New columns:
  • indid – index ID of the data partition. Always 0.

  • partitionid – ID of the data partition

  • ststatus – Internal status bits

Unique placement index on id, indid, partitionid, statid, colidarry, formatid, sequence

systabstats

New columns:
  • partitionid – ID of data or index partition

  • statmoddate – Date when statistics were last modified on disk.

  • unusedpgcnt – Number of unused pages.

  • oampagecnt – Number of OAM pages for each partition.

syspartitions

syspartitions is completely changed from the pre-15.0 version of the table. All columns are new. syspartitions supports both semantic and round-robin partitioning of tables and indexes.

syscomments

New column: partitionid – ID of data or index partition

Table enhanced to store the text of computed column or function-based index key expression.

sysindexes

New columns:
  • partitiontype – partitioning strategy: 1 – range, 2 – hash, 3 – round-robin, 4 – list

  • conditionid – ID of the partition condition

New rows: contains one row for each function-based index or index created on a computed column.

One new internal status bit added to the status2 field: Hex 0x8000, decimal 32768 – the index is a function-based index.

syslocks

New columns:
  • nodeid – Reserved for future use.

  • partitionid – ID of data or index partition. Reserved for future use. Always 0.

sysobjects

New object in type column: N – partition condition

New column:
  • identburnmax – For an identity column, maximum burned identity value

  • spacestates – Number of space states being tracked. (Only applies for DOL tables.)

  • erlchgts – Timestamp when expected row length was last changed. (Only applies to DOL tables.)

New row: one row for each computed column and function-based index key object

  • type field: type “C” added to the type field, when the object is a computed column

  • status2 field: new bit added to indicate that the table contains one or more function-based indexes.

sysprocedures

Stores a sequence tree for each computed column or function-based index definition, in binary form

Datatype changes in system table columns

System table

Changed column

Datatype changes

Identifier name

sysattributes

object_cinfo

char_info

varchar(30) null to varchar(255) null

varchar(255) to varchar(768)

Identifier for the object

sysaudits01sysaudits08

objname

varchar(30) not null to varchar(255) not null

Object name

syscolumns

name

varchar(30) not null to varchar(255) not null

Column name

remote_name

varchar(30) null to varchar(255) null

Maps local names to remote names

sysconfigures

name

varchar(80) null to varchar(255) null

sysindexes

name

varchar(30) null to varchar(255) null

Index for the table name

sysjars

jname

varchar(30) null to varchar(255) null

JAR name

sysobjects

name

varchar(30) not null to varchar(255) not null

Object name

sysprocesses

hostname

char(10) not null to varchar(30) null

Host computer name

program_name

char(16) not null to varchar(30) null

Name of front_end module

hostprocess

char(8) not null to varchar(30) null

Host process ID number

cmd

char (16) not null to varchar(30) null

Command or process currently being executed. Evaluation of a conditional statement, such as an if or while loop, returns.

systimeranges

name

varchar(30) not null to varchar(255) not null

Unique name of the time range

systypes

name

varchar(30) to varchar(255)

Datatype name

sysdatabases

def_remote_loc

varchar(255) null to varchar(349) null.

See the Reference Manual: Tables.