Create Table: Nulls and Default Values panel

Panel and field definitions

  Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)       Create Table: Nulls and Default Values     Row 1 to 1 of 1

    Owner . . . . . . .                                                        +
    Name  . . . . . . . TAB1                                                   +
    Database  . . . . .
    Table Space . . . .

 Sel Column Name      + Data Type(Length)  + Nulls Default Value               +
     COL1               CHAR(3)                                                 
 ******************************* Bottom of data ********************************









 Command ===>                                                       Scroll PAGE
  F1=Help      F2=Split     F3=Exit      F4=Expand    F7=Backward  F8=Forward
  F9=Swap     F10=Left     F11=Right    F12=Cancel

Owner

The name of the owner of the table.

Name

The name of the table.

Database

The name of the database in which the table is to be created.

Table Space

The name of the table space in which the table is to be created.

Sel

Type S in this field to display a panel that you use to enter a long default value (up to 512 characters) for the column. See the description for the Default Value field.

Column Name

The name of the column.

Data Type (Length)

The data type and, where applicable, the length of the column.

Nulls

Determines if the field can contain null values, and the default value (if any) to be used for the column when a row is inserted in the table without explicitly specifying a value for the column. Valid values are:

(blank)

Permit nulls and use null as the default value. No NOT NULL or WITH DEFAULT clause is generated in the CREATE TABLE statement.

For non-identity columns, leaving the Nulls field blank and specifying DN are equivalent. For identity columns only, a blank value in the Nulls field is equivalent to specifying NN.

NN

Disallow nulls, with no default value. A NOT NULL clause is generated in the CREATE TABLE statement.

ND

Disallow nulls, and use the default value in the Default Value field or the system-defined non-null default value. A NOT NULL WITH DEFAULT clause is generated in the CREATE TABLE statement.

NU

Disallow nulls, and use the value of the user special register at the time of insert or load as the default value. A NOT NULL WITH DEFAULT USER clause is generated in the CREATE TABLE statement.

NS

Disallow nulls, and use the SQL authorization ID of the process at the time of insert or load as the default value. A NOT NULL WITH DEFAULT CURRENT SQLID clause is generated in the CREATE TABLE statement.

DN

Permit nulls and use null as the default value. A DEFAULT NULL clause is generated in the CREATE TABLE statement.

DD

Permit nulls, and use the default value in the Default Value field or the system-defined non-null default value. A WITH DEFAULT clause is generated in the CREATE TABLE statement.

DU

Permit nulls, and use the value of the user special register at the time of insert or load as the default value. A WITH DEFAULT USER clause is generated in the CREATE TABLE statement.

DS

Permit nulls, and use the SQL authorization ID of the process at the time of insert or load as the default value. A WITH DEFAULT CURRENT SQLID clause is generated in the CREATE TABLE statement.

Default Value

The value that a column contains when a row is inserted. If you specify a constant, you must specify either ND or DD in the Nulls field; otherwise, leave this field blank.

If you need to specify a default value that is too long to fit in the field on the panel, enter S in the Sel field to display a panel. If a long default value already exists and you alter the part of the value displayed in the Default Value field, FM/Db2 displays the same panel that you can use to confirm the changes you have made.

The following points apply to the Default Value field:

• In most cases, when you specify a character string as a default value, you do not need to enclose the string in quotation marks. FM/Db2 places quotation marks around unquoted character strings when it generates the SQL.
• You must enclose a character string in quotation marks when the string contains trailing blanks.
  Specify “Twenty characters ”, as:

  'Twenty characters   '

• If the string contains quotation marks, represent each quotation mark with two quotation marks.
  Specify It's Diana's house as:

  It''s Diana''s house

  Specify Captain Cook sailed on the 'Endeavour' as:

  Captain Cook sailed on the ''Endeavour''

  Note: If the string starts and ends with quotation marks, place the entire string within quotation marks and represent each quotation mark contained within the string with two quotation marks.

  Specify 'Harry's Practice' as:

  ''Harry''s Practice''

• If the data type of the column is a distinct type, specify the default value only (FM/Db2 generates the required casting function for the distinct type). If the distinct type is based on a string data type, enclose the constant within quotation marks.