SQL Keywords

Create Database through to Create Auxiliary Table show, for each of the Create panels (and, where applicable, for each subsidiary panel), the SQL keyword (or variable) that relates to each entry field on that panel.

For example, Create Database shows that, on the main Create Database panel, the optional entry field Storage group name relates to the SQL keyword STOGROUP. When FM/Db2 builds the CREATE DATABASE statement, if you have entered a name (stogroup-name) in the Storage group name entry field, FM/Db2 generates the storage group part of the CREATE DATABASE statement syntax as:

STOGROUP stogroup-name

Note:

In some cases, a keyword that is valid for the connected Db2® version is not displayed on any panel because, although the keyword is supported, it is not required to build the SQL statement. The create index TYPE parameter is an example of this.
Some keyword values are managed entirely by FM/Db2 and are displayed only as output fields on the panels, and are included here for reference. For example, for the PART keyword, FM/Db2 generates the value set for NUMPARTS. Fields that are displayed elsewhere as input fields are not redocumented if they reappear as output fields.
Where possible, fields are associated with SQL keywords (rather than the values associated with those keywords). Otherwise, they are associated with the value-names shown in the relevant SQL syntax diagram. Occasionally more detailed context is given to avoid ambiguity.

Table 1. Create Database
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create Database panel.)
	(Database) Name	database-name
	Storage group name	STOGROUP
	Buffer pool for table spaces	BUFFERPOOL
	Buffer pool for indexes	INDEXBP
	Database usage	AS WORKFILE
	Database usage	AS TEMP
	Data encoding	CCSID
	Member name	FOR MEMBER

Table 2. Create Table Space
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create Table Space panel.)
	(Table space) Name	table-space-name
	Database	IN
	Buffer pool	BUFFERPOOL
1. Type	(See Create Table Space: Type panel.)
	Table Space Type	LARGE
	Table Space Type	LOB
	Segment size	SEGSIZE
	No. of partitions	NUMPARTS
	Max. partition size	DSSIZE
2. Allocation	(See Create Table Space: Allocation panel.)
	VCAT name	VCAT
	Storage group name	STOGROUP
	Primary space	PRIQTY
	Secondary space	SECQTY
	Free pages	FREEPAGE
	Free space	PCTFREE
	Maximum rows per page	MAXROWS
3. Data Storage Options	(See Create Table Space: Data Storage Options panel.)
	Use data compression	COMPRESS
	Close if not in use	CLOSE
	Erase data on delete	ERASE
	Define data set now	DEFINE
	INSERT uses clustering index	MEMBER CLUSTER
	Track modified changes	TRACKMOD
	Log changes to LOB columns	LOG
	Data encoding	CCSID
4. Locking Options	(See Create Table Space: Locking Options panel.)
	Locksize parameter	LOCKSIZE
	Application Locking	LOCKMAX
5. Data Sharing Options	(See Create Table Space: Data Sharing Options panel.)
	Selective Partition Locking	LOCKPART
	Group Buffer Pool Usage	GBPCACHE
6. Define Partitions	(See Create Table Space: Define Partitions panel.)
	Pt. No.	PART
	VCAT name	VCAT
	Storage group name	STOGROUP
	Primary space	PRIQTY
	Secndry space	SECQTY
	Erase Data	ERASE
	Free pages	FREEPAGE
	Free space	PCTFREE
	Use Data Cmpr	COMPRESS
	Group BP Caching	GBPCACHE

Table 3. Create Table
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create Table panel.)
	(New Table) Owner	table-name
	(New Table) Name	table-name
	(New Table) Database	IN
	(New Table) Table Space	IN
	(Model Table) Owner	LIKE
	(Model Table) Name	LIKE
1. Columns	(See Create Table: Columns panel.)
	Column Name	column-name
	Data Type	built-in-data-type
	Len	built-in-data-type
	Pr	built-in-data-type (numeric only)
	Sc	built-in-data-type (decimal only)
	For Data	FOR … DATA
	User Defined Data Type	distinct-type-name
2. Default Values	(See Create Table: Nulls and Default Values panel.)
	Not Null	NOT NULL
	Default Value	WITH DEFAULT
3. Options	(See Create Table: Options panel.)
	Editproc	EDITPROC
	Validproc	VALIDPROC
	OBID value	OBID
	Audit Options	AUDIT
	Data Storage Options	CCSID
	Log data capture changes	DATA CAPTURE
	Restrict drop of table	WITH RESTRICT ON DROP
4. Primary Key	(See Create Table: Unique Constraints panel.)
	Constraint Name	CONSTRAINT constraint-name PRIMARY KEY
	Order	PRIMARY KEY(column-name … )
5. Foreign Key	(See Create Table: Column Referential Constraints panel.)
	Constrnt Name	FOREIGN KEY
	Owner	REFERENCES
	Table Name	REFERENCES
	Column Name	REFERENCES
	ON DLT	ON DELETE
6. Check Constraints	(See Create Table: Column Check Constraints panel.)
	Table Constraint	CONSTRAINT constraint-name CHECK
	Check Condition	CHECK(check-condition … )
	(Constraint) Name	CONSTRAINT constraint-name CHECK
	(Constraint) Condition	CHECK(check-condition … )
7. Procedure Exits	(See Create Table: Procedure Exits panel.)
	Name	FIELDPROC
	Parameters	FIELDPROC
8. Generate Values	(See Create Table: Generate Values panel.)
	Gen (A/D)	GENERATE
	As Idty (Y/N)	AS IDENTITY
	Start value	START WITH
	Incr value	INCREMENT BY
	Caching Option	(NO) CACHE

Table 4. Create View
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create View panel.)
	Owner	view-name
	Name	view-name
	Select statement	AS
	Column names	column-name …
	With Check Option	WITH … CHECK OPTION

Table 5. Create Alias
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create Alias panel.)
	(Alias) Owner	alias-name
	(Alias) Name	alias-name
	(Table or View) Location	FOR
	(Table or View) Owner	FOR
	(Table or View) Name	FOR

Table 6. Create Index
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create Index panel.)
	Owner	index-name
	Name	index-name
	Table Owner	ON
	Table Name	ON
	Buffer Pool	BUFFERPOOL
1. Index Type	(See Create Index: Type panel.)
	Uniqueness	UNIQUE WHERE NOT NULL
	Internal format	TYPE
	Clustering Index Option	CLUSTER
	Piecesize	PIECESIZE
	Multiplier	PIECESIZE
2. Column Selection	(See Create Index: Column Selection panel.)
	Order	column-name
	(A/D)	column-name
3. Allocation	(See Create Index: Allocation panel.)
	VCAT name	VCAT
	Storage group name	STOGROUP
	Primary space	PRIQTY
	Secondary space	SECQTY
	Free pages	FREEPAGE
	Free space	PCTFREE
4. Options	(See Create Index: Options panel.)
	Close if not in use	CLOSE
	Erase data on delete	ERASE
	Defer building index	DEFER
	Define data set now	DEFINE
	Allow COPY of index	COPY
	Group Buffer Pool Usage	GBPCACHE
5. Partitions	(See Create Index: Partitions panel.)
	Parttn. Number	PART
	VCAT name	VCAT
	Storage group name	STOGROUP
	Primary space	PRIQTY
	Secndry space	SECQTY
	Erase Data	ERASE
	Free pages	FREEPAGE
	Free space	PCTFREE
	Group BP Caching	GBPCACHE
6. Partition Values	(See Create Index: Partition Values panel.)
	Value	VALUES

Table 7. Create Synonym
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create Synonym panel.)
	(Synonym) Name	synonym
	(Table) Owner	FOR
	(Table) Name	FOR

Table 8. Create Distinct Type
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create Distinct Type panel.)
	Schema	distinct-type-name
	Name	distinct-type-name
	Source Type	AS
	Length	AS
	Scale	AS
	For Data	FOR … DATA
	Encoding Method	CCSID

Table 9. Create Function
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create Function panel.)
	Schema	function-name
	Name	function-name
	Unique Name	SPECIFIC
1. Parameters	(See Create Function: Parameters panel.)
	Parameter Name	parameter-name
	Data Type	built-in-data-type
	Len	built-in-data-type
	Pr	built-in-data-type
	Sc	built-in-data-type
	For Data	FOR … DATA
	User Defined Data Type	distinct-type-name
2. Parameter Types	(See Create Function: Parameter Types panel.)
	As LOC	AS LOCATOR
	Owner	TABLE LIKE
	Name	TABLE LIKE
3. Returned Data Type	(See Create Function: Returned Data Type panel.)
	(Returns) Data Type	RETURNS
	(Returns) Schema	RETURNS
	(Returns) Length	RETURNS
	(Returns) Scale	RETURNS
	(Returns) For Data	RETURNS … FOR … DATA
	(Returns) Encoding Method	RETURNS … CCSID
	(Cast From) Data Type	CAST FROM
	(Cast From) Length	CAST FROM
	(Cast From) Scale	CAST FROM
	(Cast From) For Data	CAST FROM … FOR … DATA
	(Cast From) Encoding Method	CAST FROM … CCSID
	As Locator	RETURNS … AS LOCATOR
4. Option List (1/2)	(See Create Function: Option List (1/2) panel.)
	External Name	EXTERNAL NAME
	Scratchpad	(NO) SCRATCHPAD
	Package Collection	(NO) COLLID
	Workload Manager Environment	WLM ENVIRONMENT
	Time Limit	ASUTIME (NO) LIMIT
	Language	LANGUAGE
	SQL	(READS)(NO)(MODIFIES) (CONTAINS) SQL (DATA)
	Security	SECURITY
	Parallel	(DIS)ALLOW PARALLEL
	Run-time Options	RUN OPTIONS
5. Option List (2/2)	(See Create Function: Option List (2/2) panel.)
	Main Program	PROGRAM TYPE
	Stay Resident	STAY RESIDENT
	Deterministic	(NOT) DETERMINISTIC
	Called on null input	(RETURNS NULL)(CALLED) ON NULL INPUT
	No external action	(NO) EXTERNAL ACTION
	Final Call	(NO) FINAL CALL
	DBINFO argument passed	(NO) DBINFO

Table 10. Create Procedure
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create Procedure panel.)
	Schema	procedure-name
	Name	procedure-name
	Result Sets	DYNAMIC RESULT SET(S)
1. Parameters	(See Create Procedure: Parameters panel.)
	Parameter Name	parameter-name
	Data Type	built-in-data-type
	Len	built-in-data-type
	Pr	built-in-data-type
	Sc	built-in-data-type
	For Data	FOR … DATA
	User Defined Data Type	distinct-type-name
2. Parameter Types	(See Create Procedure: Parameter Types panel.)
	As LOC	AS LOCATOR
	Owner	TABLE LIKE
	Name	TABLE LIKE
	IN OUT INOUT	(IN)(OUT)(INOUT)
4. Option List (1/2)	(See Create Procedure: Option List (1/2) panel.)
	External Name	EXTERNAL NAME
	Package Collection	(NO) COLLID
	Workload Manager Environment	(NO) WLM ENVIRONMENT
	Time Limit	ASUTIME (NO) LIMIT
	Language	LANGUAGE
	SQL	(READS)(NO)(MODIFIES) (CONTAINS) SQL (DATA)
	Security	SECURITY
	Parameter Style	PARAMETER STYLE
	Run-time Options	RUN OPTIONS
5. Option List (2/2)	(See Create Procedure: Option List (2/2) panel.)
	Main Program	PROGRAM TYPE
	Stay Resident	STAY RESIDENT
	Deterministic	(NOT) DETERMINISTIC
	DBINFO argument passed	(NO) DBINFO
	Commit on return	COMMIT ON RETURN

Table 11. Create Trigger
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create Trigger panel.)
	Schema	trigger-name
	Name	trigger-name
1. Type	(See Create Trigger: Details panel.)
	(Order) No cascade before	NO CASCADE BEFORE
	(Order) After	AFTER
	(Type) Insert	INSERT
	(Type) Delete	DELETE
	(Type) Update	UPDATE
	Columns	UPDATE OF
	(Trigger Table) Name	ON
	(Trigger Table) Owner	ON
	Old correlation name	OLD
	New correlation name	NEW
	Old table identifier	OLD TABLE
	New table identifier	NEW TABLE
	(Executed) For each modified row	FOR EACH ROW
	(Executed) Once	FOR EACH STATEMENT
2. Search Condition	(See Create Trigger: Search Condition panel.)
	Search Condition	WHEN
3. SQL Statement	(See Create Trigger: SQL statement panel.)
	Triggered SQL Statement(s)	BEGIN ATOMIC

Table 12. Create Auxiliary Table
This table has three columns, except for those rows with a value in column 1, when the "(See ...)" comment spans columns 2 and 3.
Main/subsidiary panel	Field	SQL Keyword(s)
Main	(See Create Auxiliary Table panel.)
	(Auxiliary Table) Owner	aux-table-name
	(Auxiliary Table) Name	aux-table-name
	(Table Space) Name	IN
	Database	IN
	(Base Table) Owner	STORES
	(Base Table) Name	STORES
	(Base Table Column) Name	COLUMN
	Partition	PART