Attributes used in SET and WHERE clauses

The attributes that are shown in the CM/PILOT DML panels for SET and WHERE clauses have the same names and characteristics as the column names for the Db2 catalog tables (which are documented in the IBM documentation).

Note

You can use any column name of any catalog table in a WHERE clause. However, for SET clauses, you can use only the column names of the catalog tables that are listed in the following tables.

Alias attributes for SET and WHERE clauses
Check constraint attributes for SET and WHERE clauses
Column (table and view) attributes for SET and WHERE clauses
Column mask attributes for SET and WHERE clauses
Database attributes for SET and WHERE clauses
Field (LIKE and UPDATE) attributes for SET and WHERE clauses
Foreign key attributes for SET and WHERE clauses
Index attributes for SET and WHERE clauses
Index key attributes for SET and WHERE clauses
Index partition attributes for SET and WHERE clauses
Parm attributes for SET and WHERE clauses
Relation attributes for SET and WHERE clauses
Routine attributes for SET and WHERE clauses
Row permission attributes for SET and WHERE clauses
Sequence attributes for SET and WHERE clauses
Storage group attributes for SET and WHERE clauses
Synonym attributes for SET and WHERE clauses
Table attributes for SET and WHERE clauses
Table space attributes for SET and WHERE clauses
Table space partition attributes for SET and WHERE clauses
Trigger attributes for SET and WHERE clauses
View attributes for SET and WHERE clauses
Volume attributes for SET and WHERE clauses

Alias attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
CREATOR	X	X
LABEL	X	N/A
LOCATION	X	X
NAME	X	X
REMARKS	X ¹	N/A
TBCREATOR	X	X
TBNAME	X	X

^{1 No input field exists in the panels for the REMARKS attribute. You must edit the SET clause to change it. The REMARKS column is updated by the COMMENT ON SQL statement.}

Check constraint attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
CHECKNAME	X	X
CHECKCONDITION	X	N/A
CREATOR	X ¹	X
TBNAME	X ¹	X
TBOWNER	X ¹	X

^{1 You can use this attribute only in the SET clause for a LIKE statement.}

Column (table and view) attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
COLCARD	X	X
COLCARDF	X	X
COLNO	X	X
COLTYPE	X	X
DEFAULT	X	X
DEFAULTVALUE	X	X
FLDPROC	X	X
FOREIGNKEY	X	X
HIDDEN ¹	X	X
KEYSEQ	X	X
LABEL	X	X
LENGTH	X	X
LENGTH2	X	X
NAME	X	X
NULLS	X	X
ORDER	X ²	X ²
PERIOD	X	X
REMARKS	X ³	N/A
SCALE	X	X
TBCREATOR	X ⁴	X
TBNAME	X ⁴	X
TYPENAME	X	X
TYPESCHEMA	X	X

^{1 This attribute applies only to a table.}

^{2 This attribute only applies to an identity column in a table. No input field exists in the panels for the ORDER attribute. You must edit the SET and WHERE clauses to change it.}

^{3 No input field exists in the panels for the REMARKS attribute. You must edit the SET clause to change it. The REMARKS column is updated by the COMMENT ON SQL statement.}

^{4 You can use this attribute only in the SET clause for a LIKE statement.}

Column mask attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
COLNAME	X	X
ENABLE	X	X
NAME	X	X
OWNER	N/A	X
REMARKS	X	X
SCHEMA	X	X
TBCORRELATION	X	X
TBNAME	X¹	X
TBSCHEMA	X¹	X

^{1 You can use this attribute only in the SET clause for a LIKE statement.}

Database attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
BPOOL	X	X
CREATOR	X	X
ENCODING_SCHEME	X	X
GROUP_MEMBER	N/A	X
INDEXBP	X	X
NAME	X	X
STGROUP	X	X
TYPE	N/A	X

Field (LIKE and UPDATE) attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
COLNO	N/A	X
COLTYPE	N/A	X
FLDPROC	X	X
LENGTH	N/A	X
NAME	X	X
SCALE	N/A	X
TBCREATOR	X ¹	X
TBNAME	X ¹	X

^{1 You can use this attribute only in the SET clause for a LIKE statement.}

Foreign key attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
COLNAME	X ¹	X
COLNO	N/A	X
COLSEQ	X	X
CREATOR	X ¹	X
DELETERULE	X	X
REFTBCREATOR	X	X
REFTBNAME	X	X
RELNAME	X	X
TBNAME	X ¹	X

^{1 You can use this attribute only in the SET clause for a LIKE statement.}

Index attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
BPOOL	X	X
CLOSERULE	X	X
CLUSTERING	X	X
COMPRESS	X	X
COPY	X	X
CREATOR	X	X
DEFINE	X	X
DSSIZE (SPE2010)	X	X
ERASERULE	X	X
FULLKEYCARDF	X	X
NAME	X	X
PADDED	X ¹	X ¹
PGSIZE	X	X
PIECESIZE	X	X
REMARKS	X ²	N/A
TBCREATOR	X ³	X
TBNAME	X ³	X
UNIQUERULE	X	X

^{1 No input field exists in the panels for the PADDED attribute. You must edit the SET and WHERE clauses to change it.}

^{2 No input field exists in the panels for the REMARKS attribute. You must edit the SET clause to change it. The REMARKS column is updated by the COMMENT ON SQL statement.}

^{3 You can use this attribute only in the SET clause for a LIKE statement.}

Index key attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
COLNAME	X	X
COLSEQ	X	X
IXCREATOR	X ¹	X
IXNAME	X ¹	X
ORDERING	X	X
PERIOD	X	X

^{1 You can use this attribute only in the SET clause for a LIKE statement.}

Index partition attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
AVGKEYLEN	X	X
CARD	X	X
CARDF	X	X
DSSIZE (SPE2010)	X	X
FREEPAGE	X	X
GBPCACHE	X	X
IXCREATOR	X ¹	X
IXNAME	X ¹	X
PARTITION	N/A	X
PCTFREE	X	X
PQTY	X ²	X
ROWSPKEY	X	X
ROWSPKEYF	X	X
SECQTYI	X	X
SPACE	X	X
SQTY	X ²	X
STORNAME	X	X
VCATNAME	X ¹	X

^{1 You can use this attribute only in the SET clause for a LIKE statement.}

^{2 The value of PQTY or SQTY can be an expression that includes the BMCSPACE keyword. The BMCSPACE keyword represents the value that is returned from Change Manager space estimation.}

Parm attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
CCSID	X	X
ENCODING_SCHEME	X	X
LENGTH	X	X
LOCATOR	X	X
NAME	X	X
ORDINAL	X	X
OWNER	X	X
PARMNAME	X	X
ROWTYPE	X	X
SCALE	X	X
SCHEMA	X	X
SUBTYPE	X	X
TYPENAME	X	X
TYPESCHEMA	X	X
VERSION	X	X

Relation attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
CREATOR	X ¹	X
DELETERULE	X	X
ENFORCED	X	X
IXNAME	X ¹	X
IXOWNER	X ¹	X
REFTBCREATOR	X	X
REFTBNAME	X	X
RELNAME	X	X
TBNAME	X ¹	X

^{1 You can use this attribute only in the SET clause for a LIKE statement.}

Routine attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
ACTIVE	X	X
APPLCOMPAT	X	X
ARCHIVESENSITIVE	X	X
ASUTIME	X	X
BUSTIMESENSITIVE	X	X
COLLID	X	X
COMMIT_ON_RETURN	X	X
CONCUR_ACC_RES	X	X
DATE_FORMAT	X	X
DBINFO	X	X
DEBUG_MODE	X	X
DEC31	X	X
DEFERPREP	X	X
DEFERPREPARE	X	X
DEGREE	X	X
DETERMINISTIC	X	X
DYNAMICRULES	X	X
ENCODING_CCSID	X	X
EXPLAIN	X	X
EXTERNAL_NAME	X	X
EXTERNAL_SECURITY	X	X
FENCED	X	X
IMMEDWRITE	X	X
ISOLATION	X	X
JAR_ID	X	X
JARSCHEMA	X	X
JAVA_SIGNATURE	X	X
KEEPDYNAMIC	X	X
LANGUAGE	X	X
MAX_FAILURE	X	X
NAME	X	X
NULL_CALL	X	X
OPTHINT	X	X
ORIGIN	X	X
OWNER	X	X
PACKAGEPATH	X	X
PARAMETER_CCSID	X	X
PARAMETER_STYLE	X	X
PARAMETER_VARCHARFORM	X	X
PATHSCHEMAS	X	X
PROGRAM_TYPE	X	X
QUALIFIER	X	X
RELEASE	X	X
REMARKS	X	X
REOPTVAR	X	X
RESULT_SETS	X	X
ROUNDING	X	X
RUNOPTS	X	X
SCHEMA	X	X
SPECIAL_REGS	X	X
SYSTIMESENSITIVE	X	X
SQL_DATA_ACCESS	X	X
STAYRESIDENT	X	X
TIME_FORMAT	X	X
VALIDATE	X	X
VERSION	X	X
WLM_ENV_FOR_NESTED	X	X
WLM_ENVIRONMENT	X	X

Row permission attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
ENABLE	X	X
NAME	X	X
OWNER	N/A	X
REMARKS	X	X
SCHEMA	X	X
TBCORRELATION	X	X
TBNAME	X¹	X
TBSCHEMA	X¹	X

_{1 You can use this attribute only in the SET clause for a LIKE statement.}

Sequence attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
CACHE	X	X
COLTYPE	X	X
CYCLE	X	X
INCREMENT	X	X
MAXVALUE	X	X
MINVALUE	X	X
NAME	X	X
ORDER	X	X
PRECISION	N/A	X
REMARKS	X	X
SCHEMA	X	X
START	X	X
TYPENAME	X	X
TYPESCHEMA	X	X

Storage group attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
CREATOR	X	X
NAME	X	X
VCATNAME	X	X
DATACLAS	X	X
MGMTCLAS	X	X
STORCLAS	X	X

Synonym attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
CREATOR	X	X
NAME	X	X
TBCREATOR	X	X
TBNAME	X	X

Table attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
ARCHIVING_SCHEMA	X	X
ARCHIVING_TABLE	X	X
AUDITING	X	X
CARD	X	X
CARDF	X	X
CHECKRID⁴	X	X
CHECKS	N/A	X
CLUSTERTYPE	X	X
CLUSTERRID	X	X
CREATOR	X	X
DATACAPTURE	X	X
DBNAME	X	X
EDPROC	X	X
ENABLE	X	X
ENCODING_SCHEME ¹	X	X
LABEL	X	N/A
MAINTENANCE	X	X
NAME	X	X
OBID	X	X
REMARKS	X	X
SPLIT_ROWS ²	X	X
TCPART	X	N/A
TSNAME	X	X
TYPE	X ³	X
VALPROC	X	X
VERSIONING_SCHEMA	X	X
VERSIONING_TABLE	X	X

^{1 This attribute applies only to global temporary tables. If you specify a value for the encoding scheme for a table, the product adds TYPE = ‘G’ to the WHERE clause}

^{2 The SPLIT_ROWS attribute corresponds to the VOLATILE parameter.}

^{3 You can use this attribute only in the SET clause for a LIKE statement.}

^{4 You need to specify values for CHECKRID containing x'00' using the x'FFFFFF00' format instead of coding the actual hexadecimal values.}

Table space attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
BPOOL	X	X
CLOSERULE	X	X
CREATOR	X	X
DBNAME	X	X
DEFINE	X	X
DSSIZE	X	X
ENCODING_SCHEME	X	X
ERASERULE	X	X
LOCKMAX	X	X
LOCKPART	X	X
LOCKRULE	X	X
LOG	X	X
MAXPARTITIONS	X	X
MAXROWS	X	X
NAME	X	X
PAGENUM (SPE2010)	X	X
SEGSIZE	X	X
TYPE	X	X

Table space partition attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
CARD	X	X
COMPRESS	X	X
DBNAME	X ¹	X
DSSIZE (SPE2010)	X	X
EPOCH	X	X
FREEPAGE	X	X
GBPCACHE	X	X
PARTITION	^N/A	X
PCTFREE	X	X
PCTFREE_UPD	X	X
PQTY	X ²	X
SECQTYI	X	X
SQTY	X ²	X
STORNAME	X	X
TRACKMOD	X	X
TSNAME (like only)	X	X
VCATNAME	X	X

^{1 You can use this attribute only in the SET clause for a LIKE statement.}

^{2 The value of PQTY or SQTY can be an expression that includes the BMCSPACE keyword. The BMCSPACE keyword represents the value that is returned from Change Manager space estimation.}

Trigger attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
CREATEDBY	N/A	X
CREATEDTS	N/A	X
DBID	N/A	X
GRANULARITY	N/A	X
NAME	X	X
OBID	N/A	X
OWNER	X	X
REMARKS	X	X
SCHEMA	X	X
SECURE	X	X
SEQNO	N/A	X
TBNAME	X	X
TBOWNER	X	X
TRIGEVENT	N/A	X
TRIGTIME	N/A	X

View attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
CHECK	X	X
CREATOR	X	X
LABEL	X	N/A
NAME	X	X
REMARKS	X ¹	N/A

^{1 No input field exists in the panels for the REMARKS attribute. You must edit the SET clause to change it. The REMARKS column is updated by the COMMENT ON SQL statement.}

Volume attributes for SET and WHERE clauses

Attribute	SET clause	WHERE clause
SGCREATOR	X ¹	X
SGNAME	X ¹	X
VOLID	X	X

^{1 You can use this attribute only in the SET clause for a LIKE statement.}

Attributes used in SET and WHERE clauses

Alias attributes for SET and WHERE clauses

Check constraint attributes for SET and WHERE clauses

Column (table and view) attributes for SET and WHERE clauses

Column mask attributes for SET and WHERE clauses

Database attributes for SET and WHERE clauses

Field (LIKE and UPDATE) attributes for SET and WHERE clauses

Foreign key attributes for SET and WHERE clauses

Index attributes for SET and WHERE clauses

Index key attributes for SET and WHERE clauses

Index partition attributes for SET and WHERE clauses

Parm attributes for SET and WHERE clauses

Relation attributes for SET and WHERE clauses

Routine attributes for SET and WHERE clauses

Row permission attributes for SET and WHERE clauses

Sequence attributes for SET and WHERE clauses

Storage group attributes for SET and WHERE clauses

Synonym attributes for SET and WHERE clauses

Table attributes for SET and WHERE clauses

Table space attributes for SET and WHERE clauses

Table space partition attributes for SET and WHERE clauses

Trigger attributes for SET and WHERE clauses

View attributes for SET and WHERE clauses

Volume attributes for SET and WHERE clauses

On this page