RDB/VMS Relational Database Operator DEFINE_DATABASE — VMS RDB_4.0
Creates a database file, specifies its name, and determines its
physical characteristics. This statement also creates a directory in
the data dictionary where definitions of other database elements can
be stored.
Example (single-file database):
RDO> DEFINE DATABASE 'PERSONNEL'.
Additional information available:
More
By using the RDBVMS$CREATE_DB logical name and the RDBVMS$CREATE_DB
identifier, you can restrict the ability of users to create databases
on your system. For more information on the RDBVMS$CREATE_DB logical
name and identifier, see the chapter on defining database protection
in the VAX Rdb/VMS Guide to Database Design and Definition manual.
If your system does not use the RDBVMS$CREATE_DB logical name and
identifier, all users on the system have the ability to create
databases.
When the DEFINE DATABASE statement executes, Rdb/VMS:
o Creates a database file, a snapshot file, and, if the database is
multifile, storage area files.
o Creates a data dictionary entity for the database, including all
the system fields and relations, if the dictionary is installed.
o Assigns database parameters, and, if the database is multifile,
storage area parameters.
o Creates a default access control list.
o Invokes the newly created database, using the database file name
as the database handle.
You cannot issue the DEFINE DATABASE statement when a transaction is
active. It is best to make this the first statement of an RDO
session. Do not issue an INVOKE DATABASE statement after you use
DEFINE DATABASE. Rdb/VMS automatically invokes the database after
creating the database file.
Other users are not allowed to be attached to the database when you
issue the DEFINE DATABASE statement.
By using the RDBVMS$CREATE_DB logical name and the RDBVMS$CREATE_DB
identifier, you can restrict the ability of users to create databases
on your system. For more information on the RDBVMS$CREATE_DB logical
name and identifier, see the chapter on defining database protection
in the VAX Rdb/VMS Guide to Database Design manual.
Format
DEFINE DATABASE ─────> file-spec ───┬───────────>──────────┬───────┐ └─> invoke-options ────┘ │ ┌───────────────────────────<──────────────────────────────────────┘ └─┬─┬───────────>────────────┬─┬──┬────────────────────────┬───────┐ │ ├──> db-wide-options-1 ─┤ │ └─> storage-area-options─┘ │ │ └──> db-wide-options-2 ──┘ │ │ └───────────<────────────────┘ │ ┌─────────────────────────────<────────────────────────────────────┘ └┬┬──────────────>──────────────────────────────>───────────┬──────┬─> . │├─> define-storage-area-clause ────────────────>──────────┤ │ │└─>
S
E
G
M
E
N
T
E
D
S
T
R
I
N
G
S
T
O
R
A
G
E
A
R
E
A typebox (I)typebox (S) ─> storage-area-name ┘ │ └───────────────────────────────<─────────────────────────────────┘
Additional information available:
file-specinvoke-optionsdb-wide-options-1db-wide-options-2
storage-area-optionsdefine-storage-area
file-spec
A file specification that names the database file. Put this file
specification in quotation marks. You should use either a full file
specification, including device, directory, and file name without the
file type, or a simple file name. In the second case, Rdb/VMS
creates the database in the current default directory. In a
single-file database, the file name specified becomes the file name
for two files:
o The database file. The default file type is RDB.
o The snapshot file. This file is used as a temporary file for
read-only transactions. The default file type is SNP.
Type the name of the file-spec in uppercase letters when you define
your database if you use the CDD/Plus data dictionary or may use it
in the future. If you type the name of the file-spec in lowercase
letters and try to use the DEFINE GENERIC command of the dictionary's
CDO utility to create a directory name for the database in the
dictionary, the command will fail.
In a multifile database, the file name specified becomes the file
name for three files:
o The database root file. The default file type is RDB.
o The default storage area file. The default file type is RDA.
o A snapshot file. The default file type is SNP.
invoke-options
invoke-options = ──┬───────────────────────────>──────────────────┬─────────────────> ├─────>
D
B
H
A
N
D
L
E typebox (I)typebox (S) ───────> db-handle ───────┤ └─────>
D
B
K
E
Y
S
C
O
P
E typebox (I)typebox (S) ──┬──>
C
O
M
M
I
T ───┬──────┘ └──>
F
I
N
I
S
H ───┘
Additional information available:
db-handle
The name of the variable you will use to refer to the database. Do not use a file name for the db-handle.
COMMIT
During the session of the user who entered DEFINE DATABASE, specifies that the database key of each record used is guaranteed not to change only during each transaction. That is, if a record is erased, its dbkey is not reused by another database user until after a COMMIT statement is executed.
FINISH
During the session of this user who entered the DEFINE DATABASE statement, specifies that the dbkey of each record used is guaranteed not to change until this user detaches from the database (usually, by using the FINISH statement).
db-wide-options-1
db-wide-options-1 = ──┬─>
I
N ──────> path-name ──────────────────────────────────┬─> ├─>
C
O
L
L
A
T
I
N
G
S
E
Q
U
E
N
C
E typebox (I)typebox (S) sequence-name ──┐ │ │ ┌──────────────────────────────────────┘ │ │ └─┬───────────────────────────────────┬──> ncs-name ──┐ │ │ └─>
D
E
S
C
R
I
P
T
I
O
N typebox (I)typebox (S) ──> typebox (/)typebox (*) text */ ──┘ │ │ │ ┌───────────────────────────────────────────────────┘ │ │ └─┬───────────────────────┬───────────────────────────┤ │ └─>
F
R
O
M library-name ──┘ │ ├─>
D
E
S
C
R
I
P
T
I
O
N typebox (I)typebox (S) ───────> typebox (/)typebox (*) text */ ────────────────────┤ ├─>
N
U
M
B
E
R typebox (O)F
U
S
E
R
S typebox (I)typebox (S) ──> number-users ───────────────────┤ ├─>
N
U
M
B
E
R typebox (O)F
B
U
F
F
E
R
S typebox (I)typebox (S) ─────> number-buffers ────────────┤ ├─>
N
U
M
B
E
R typebox (O)F
V
A
X
C
L
U
S
T
E
R
N
O
D
E
S typebox (I)typebox (S) ──> number-nodes ────────┤ └─>
N
U
M
B
E
R typebox (O)F
R
E
C
O
V
E
R
Y
B
U
F
F
E
R
S typebox (I)typebox (S) ───> recovery-buffers ───┘
Additional information available:
path-namencs-nametextlibrary-namenumber-usersnumber-buffers
number-nodesrecovery-buffers
path-name
The data dictionary path name for the dictionary path name where the database definition is stored. Use this qualifier to store the data dictionary definitions for the database in a data dictionary entity other than the default path, which is defined by the name of the database file.
COLLATING_SEQUENCE
Specifies a collating sequence to be used for all fields in the database. Sequence-name is a name of your choosing; use this sequence-name in any subsequent statements that refer to this collating sequence. The VMS National Character Set (NCS) Utility provides a set of pre-defined collating sequences and also lets you define collating sequences of your own. The COLLATING_SEQUENCE clause accepts both pre-defined and user-defined NCS collating sequences. If you do not specify a collating sequence, the default is ASCII (shown as "no collating sequence" in some displays).
ncs-name
Specifies the name of a collating sequence in the default NCS library, SYS$LIBRARY:NCS$LIBRARY, or in the NCS library specified by the argument library-name. (In most cases, it is probably simplest to make the sequence-name the same as the ncs-name: for example, COLLATING_SEQUENCE IS FRENCH FRENCH.) You can view the collating sequence names by using the command NCS/LIST at DCL level. The collating sequence can be either one of the pre-defined NCS collating sequences or one that you have defined yourself using NCS.
text
Provides a comment for a collating sequence or database being defined.
library-name
Specifies the name of an NCS library other than the default. The default NCS library is SYS$LIBRARY:NCS$LIBRARY.
number-users
The maximum number of users allowed to access the database at one time. The default is 50 users. The largest number of users you can specify is 2032, and the fewest number of users is 1.
number-buffers
The number of buffers Rdb/VMS allocates per process using this database. Specify an unsigned integer between 2 and 32768. The default is 20 buffers.
number-nodes
Sets the upper limit on the maximum number of VAXcluster nodes from which users can access the shared database. The default is 16 nodes. The range is 1 node to 64 nodes. The actual maximum limit is the current VMS VAXcluster limit.
recovery-buffers
The number of database buffers used during the automatic recovery process that is initiated after a system or process failure. This recovery process uses the recovery-unit journal file. Specify an unsigned integer between 2 and 32768. The default is 20 buffers.
db-wide-options-2
db-wide-options-2 = ──┬─>
B
U
F
F
E
R
S
I
Z
E typebox (I)typebox (S) ─────> buffer-blocks ──>
B
L
O
C
K
S──────┬─> ├─>
A
D
J
U
S
T
A
B
L
E
L
O
C
K
G
R
A
N
U
L
A
R
I
T
Y typebox (I)typebox (S) ─┬───>
E
N
A
B
L
E
D ───┬───┤ │ └───>
D
I
S
A
B
L
E
D ──┘ │ ├─>
S
N
A
P
S
H
O
T typebox (I)typebox (S) ────┬─────>
E
N
A
B
L
E
D ─┬─>
I
M
M
E
D
I
A
T
E──┬─┬─┤ │ │ └─>
D
E
F
E
R
R
E
D ──┘ │ │ │ └─────>
D
I
S
A
B
L
E
D ───────────>──────┘ │ └─>
D
I
C
T
I
O
N
A
R
Y typebox (I)typebox (S) ───┬───>
R
E
Q
U
I
R
E
D ───────┬─────────────┘ ├───>
N
O
T
R
E
Q
U
I
R
E
D ───┤ ├───>
U
S
E
D ───────────┤ └───>
N
O
T
U
S
E
D ───────┘
Additional information available:
ADJUSTABLE_LOCKENABLED-IMMEDIATEENABLED-DEFERREDDISABLED
DICTIONARY
buffer-blocks
The number of blocks Rdb/VMS allocates per buffer. Specify an unsigned integer greater than zero. If you do not specify this parameter, Rdb/VMS uses a buffer size that is three times the PAGE SIZE value. Buffer size is a global parameter and the number of blocks per page (or buffer) is constrained to less than 64 blocks per page. The page size can vary by storage area for multifile databases, so you should determine the page size of each storage area based on the sizes of records that will be stored in each storage area. When you choose the number of blocks per buffer, choose a number that is wholly divisible by all page sizes for all storage areas in your multifile database. For example, if your database has three storage areas with page sizes of 2, 3, and 4 blocks respectively, choosing a buffer size of 12 blocks will ensure optimal buffer utilization. If you choose a buffer size of 8, the storage area with a page size of 3 blocks will waste 2 blocks per buffer. Rdb/VMS reads as many pages as will fit into the buffer. In this case, Rdb/VMS reads two pages of 3 blocks apiece into the buffer, wasting 2 blocks.
ADJUSTABLE_LOCK
The ADJUSTABLE LOCK GRANULARITY clause enables or disables adjustable locking granularity. Generally, enabling adjustable locking granularity results in fewer locks being used. However, when contention for database pages is high, performance may be impaired as locking granularity is adjusted to a lower level. If your application is query intensive, enable adjustable locking granularity. If your application processes specific records and performs a substantial number of update operations, you might want to disable adjustable locking granularity. Disabling adjustable locking granularity may require that the VMS SYSGEN parameters for locks be increased. The default is that lock granularity is ENABLED.
ENABLED-IMMEDIATE
The default, ENABLED IMMEDIATE causes read/write transactions to write copies of records to the the snapshot file before those records are modified, regardless of whether a read-only transaction is active. If you use the SNAPSHOT IS ENABLED clause to enable snapshots on a multifile database, writing to all snapshot files for all storage areas is enabled.
ENABLED-DEFERRED
Specifies that read/write transactions not write copies of records they modify to the snapshot file unless a read-only transaction is active. Read-only transactions that attempt to start after an active read/write transaction begins must wait for all active read/write users to complete their transactions. If you use the SNAPSHOT IS ENABLED clause to enable snapshots on a multifile database, writing to all snapshot files for all storage areas is enabled.
DISABLED
Disables snapshot transactions. If you use the SNAPSHOT IS DISABLED clause to disable snapshots on a multifile database, writing to all snapshot files for all storage areas is disabled.
DICTIONARY
The DICTIONARY IS [NOT] REQUIRED clause determines whether the database must be invoked by path name for data definition changes to occur. If you specify the DICTIONARY IS REQUIRED option, the database must be invoked by path name to change metadata and the data dictionary will be maintained. If you specify the DICTIONARY IS NOT REQUIRED option, the database can be invoked by either file name or path name to change metadata. The default is DICTIONARY IS NOT REQUIRED. The DICTIONARY IS [NOT] USED clause determines whether the definition of the database and definitions of database elements will be stored in the data dictionary. If you specify the DICTIONARY IS USED option, the definition of the database and definitions of database elements will be stored in the data dictionary. If you specify the DICTIONARY IS NOT USED option, no definitions will be stored in the data dictionary. The default is DICTIONARY IS USED. You receive an error message if you specify incompatible options, such as the DICTIONARY IS REQUIRED option and the DICTIONARY IS NOT USED option.
storage-area-options
storage-area-options = ─┬┬───────────────────────────>─────────────────────────────────┬┬─> │├──>
A
L
L
O
C
A
T
I
O
N typebox (I)typebox (S) ───> number-pages ────>───────── typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ────┤│ │├──>
P
A
G
E
S
I
Z
E typebox (I)typebox (S) ────> page-blocks ─────>───────── typebox (B)typebox (L)typebox (O)typebox (C)typebox (K)typebox (S) ───┤│ │├──>
P
A
G
E
F
O
R
M
A
T typebox (I)typebox (S) ──┬────>
U
N
I
F
O
R
M ────┬────────────────────┤│ ││ └────>
M
I
X
E
D ──────┘ ││ │├──>
T
H
R
E
S
H
O
L
D
S typebox (A)typebox (R)typebox (E) ─> (─> val1─┬─────────────────────┬───> )─┤│ ││ └─> ,val2 ─┬────────┬─┘ ││ ││ └>,val3 ─┘ ││ │├──>
I
N
T
E
R
V
A
L typebox (I)typebox (S) ───────> number-data-pages ──────────────────┤│ │├──>
S
N
A
P
S
H
O
T
F
I
L
E
N
A
M
E
I
S ────> file-spec ────────────────────┤│ │├──>
S
N
A
P
S
H
O
T
A
L
L
O
C
A
T
I
O
N typebox (I)typebox (S) ───> snp-pages ───> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ────────┤│ │└┬─>
S
N
A
P
S
H
O
T
E
X
T
E
N
T typebox (I)typebox (S) ─┬──┬─> extent-pages ────> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S) ─┬───┘│ │ └─>
E
X
T
E
N
T typebox (I)typebox (S) ──────────┘ └─> extension-options ────────┘ │ └────────────────────────────<──────────────────────────────────┘
Additional information available:
number-pagespage-blocksnumber-data-pagesfile-specsnp-pages
extent-pagesextension-options
number-pages
The number of database pages allocated to the database initially. Rdb/VMS automatically extends the allocation to handle the loading of data and subsequent expansion. The default is 400 pages.
page-blocks
The size in blocks of each database page. Page size is allocated in 512-byte blocks. The default is two blocks (1024 bytes). If your largest record is larger than approximately 950 bytes, allocate more blocks per page to prevent records from being fragmented.
PAGE_FORMAT
Specifies whether a storage area contains uniform or mixed pages. You can use the PAGE FORMAT option with multifile databases only. In storage areas with uniform page format, all pages in a specific logical area contain records from the same relation. In storage areas with mixed page format, pages can hold records from different relations. The default is UNIFORM.
THRESHOLDS
Specifies one, two, or three threshold values. The threshold values represent a fullness percentage on a data page and establish four possible ranges of guaranteed free space on the data pages. When a data page reaches the percentage defined by a given threshold value, the SPAM entry for the data page is updated to reflect the new fullness percentage and its remaining free space. The default thresholds are 70, 85, and 95 percent. When THRESHOLDS ARE is used, the first threshold value is required. If you specify only one or two values, unspecified values default to 100 percent. You can specify the THRESHOLDS option only on a storage area for a multifile database. The storage area page format must be MIXED.
number-data-pages
Specifies the number of data pages between SPAM pages in the physical storage area file, and thus the maximum of data pages each SPAM page will manage. The default, and also the minimum interval, is 256 data pages. The first page of each storage area is a SPAM page. The interval you specify determines where subsequent SPAM pages are to be inserted, provided there are enough data pages in the storage file to require more SPAM pages. You can specify the INTERVAL option only on a storage area for a multifile database. The storage area page format must be MIXED.
file-spec
Provides a separate file specification for the snapshot file. Do not specify a file extension other than SNP to the file specification. You cannot specify a global default for the SNAPSHOT_FILENAME. Thus, in a multifile database, the SNAPSHOT_FILENAME option must be within a DEFINE STORAGE AREA definition. The SNAPSHOT_FILENAME option cannot be specified for a single-file database.
snp-pages
Specifies the number of pages allocated for the snapshot file. The default is 100 pages.
extent-pages
Specifies the number of pages of each extent. The default is 100 pages.
extension-options
Specifies the MIN, MAX, and percent growth of each database file extent. Enclose the parameter list in parentheses. extension-options = ───> ( ───>
M
I
N
I
M
U
M typebox (O)F ──> min-pages ───> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S), ─┐ ┌────────────────────────────────────────┘ └──>
M
A
X
I
M
U
M typebox (O)F ──> max-pages ──> typebox (P)typebox (A)typebox (G)typebox (E)typebox (S),─┐ ┌────────────────────────────────────────┘ └──>
P
E
R
C
E
N
T
G
R
O
W
T
H typebox (I)typebox (S) ───> growth ────> ) ───>
Additional information available:
min-pages
Specifies the minimum number of pages of each extent. The default is 99 pages.
max-pages
Specifies the maximum number of pages of each extent. The default is 9,999 pages.
growth
Specifies the percent growth of each extent. The default is 20 percent growth.
define-storage-area
define-storage-area-clause = ────>
D
E
F
I
N
E
S
T
O
R
A
G
E
A
R
E
A ──┬────> storage-area-name ────┬──────┐ └────> typebox (R)typebox (D)typebox (B)typebox ($)typebox (S)typebox (Y)typebox (S)typebox (T)typebox (E)typebox (M) ───────────┘ │ ┌────────────────────────────────<──────────────────────────────┘ └─────>
F
I
L
E
N
A
M
E ─────> file-spec ──┬────────────>─────────────┬───┐ └─> storage-area-options ──┘ │ ┌──────────────────────────────────<───────────────────────────────┘ └─────>
E
N
D ───────┬──────────>───────────┬─> typebox (S)typebox (T)typebox (O)typebox (R)typebox (A)typebox (G)typebox (E) typebox (A)typebox (R)typebox (E)typebox (A) ────> ├─> storage-area-name ─┤ └─> typebox (R)typebox (D)typebox (B)typebox ($)typebox (S)typebox (Y)typebox (S)typebox (T)typebox (E)typebox (M) ────────┘
Additional information available:
storage-area-namefile-specstorage-area-options
storage-area-name
The name of the storage area you want to create.
file-spec
The storage area file that is associated with the named storage area. Put this file specification in quotation marks. If you use a simple file name, Rdb/VMS creates the storage area file in the current default directory. Use a name that is unique among all storage area files defined for the database.
RDB$SYSTEM
The default storage area. If you directly specify RDB$SYSTEM in the DEFINE STORAGE AREA clause, you can override the default characteristics for the main storage area.
storage-area-options
Ask for HELP on "DEFINE_DATABASE Format storage-area-options" for information on storage area options.
SEGMENTED_STRING
The name of the storage area that will hold all segmented strings. For a single file database or multifile database, if you do not explicitly define a storage area for segmented strings, segmented strings will be stored in the default storage area, RDB$SYSTEM. If your database is a single-file database and you specify a storage area other than RDB$SYSTEM, you receive an error message because RDB$SYSTEM is the only storage area in a single-file database. The page format for the segmented string storage area can be UNIFORM or MIXED. However, Digital Equipment Corporation recommends that if you store segmented strings in a MIXED storage area, that area contain only segmented strings.
Examples
Example 1
Use the default DEFINE DATABASE Statement:
RDO> DEFINE DATABASE 'PERSONNEL'.
Example 2
Use DEFINE DATABASE with qualifiers:
RDO> DEFINE DATABASE 'PERSONNEL'
cont> NUMBER OF BUFFERS IS 10
cont> BUFFER SIZE IS 10 BLOCKS.
This statement performs the following actions:
o Names the database.
o Specifies a number of buffers, and the length of each buffer.
o Commits the database definition implicitly.
o Invokes the database, using the file name as the database handle.
Example 3
The scope of each record's database key is set to FINISH during the
RDO session of the user who entered DEFINE DATABASE:
RDO> DEFINE DATABASE DISK2:[USER.DBS]PERS2
cont> DBKEY SCOPE IS FINISH.
Note that this command does NOT set a "default" DBKEY SCOPE
characteristic for the database. For each user, the default is
COMMIT unless that user specifies INVOKE DATABASE...DBKEY SCOPE
FINISH.
Example 4
The upper limit on the number of VAXcluster nodes from which users
can access the common, cluster-wide database is set to 8:
RDO> DEFINE DATABASE ACCTING NUMBER VAXCLUSTER NODES IS 8.
If you attempt to access the shared database from a VAXcluster node
and, in doing so, exceed the maximum nodes parameter, an error is
signaled to your RDO session or program.
Example 5
The following RDO command procedure shows a part of the multifile
MF_PERSONNEL database definition. The example:
o Defines database wide characteristics
o Defines global storage area defaults
o Specifies local attributes for RDB$SYSTEM, the default storage
area
o Defines a storage area for segmented strings
o Defines other storage areas
This example includes only a few storage area definitions. The full
definition includes all of the storage area definitions.
DEFINE DATABASE 'DB_DISK:MULTI_PERS'
! Define database-wide characteristics
DESCRIPTION IS /* Sample multifile definition */
NUMBER OF USERS IS 60
NUMBER OF VAXCLUSTER NODES IS 22
NUMBER OF RECOVERY BUFFERS IS 200
DICTIONARY IS NOT USED
! Define global storage area characteristics
ALLOCATION IS 500 PAGES
PAGE FORMAT IS MIXED
! Specify local attributes for the default storage area
! Override the global default of MIXED page format
DEFINE STORAGE AREA RDB$SYSTEM
FILENAME 'DISK1:PERS_DEFAULT'
PAGE FORMAT IS UNIFORM
ALLOCATION IS 300 PAGES
SNAPSHOT_FILENAME IS 'DISK2:PERS_DEFAULT'
END RDB$SYSTEM STORAGE AREA
! Define storage area for segmented strings
DEFINE STORAGE AREA PERS_SEGSTR
FILENAME 'DISK1:PERS_SEGSTR'
PAGE FORMAT IS UNIFORM
END PERS_SEGSTR STORAGE AREA
SEGMENTED STRING STORAGE AREA IS PERS_SEGSTR
! Definition of some sample storage areas
DEFINE STORAGE AREA CANDIDATES
FILENAME 'DISK3:CANDIDATES'
PAGE FORMAT IS UNIFORM
SNAPSHOT_FILENAME IS 'DISK4:CANDIDATES'
END CANDIDATES STORAGE AREA
DEFINE STORAGE AREA EMPIDS_LOW
FILENAME 'DISK5:EMPIDS_LOW'
SNAPSHOT_FILENAME IS 'DISK6:EMPIDS_LOW'
END EMPIDS_LOW STORAGE AREA
DEFINE STORAGE AREA EMPIDS_MID
FILENAME 'DISK7:EMPIDS_MID'
SNAPSHOT_FILENAME IS 'DISK8:EMPIDS_MID'
END EMPIDS_MID STORAGE AREA.