Changes characteristics of the database root file and storage
area files. When this statement executes, Oracle Rdb updates the
characteristics named in the statement. All other characteristics
remain the same.
Example:
RDO> CHANGE DATABASE FILENAME 'DISK2:[USER.TEST]PERSONNEL'
cont> SNAPSHOT ALLOCATION IS 200.
1 – More
You need the Oracle Rdb ADMINISTRATOR privilege to use the CHANGE
DATABASE statement.
Use CHANGE DATABASE to:
o Define, change, and delete storage areas in multifile
databases
o Enable or disable after-image journaling and change journal
file characteristics
o Enable or disable snapshot transactions and change snapshot
file characteristics
o Change a read/write storage area to read-only, or a read-only
storage area to read/write
o Change a storage area that contains only a segmented string to
a format that can be stored on a write-once, read-many (WORM)
device, using the WRITE_ONCE option.
o Change the number of recovery buffers
o Change physical parameters associated with the database
o Specify whether the database can be opened automatically or
manually
o Require the use of the data dictionary
o Enable or disable global buffers
o Enable or disable fast commit processing
o Enable or disable carry-over lock optimization
o Set a database-wide timeout interval
You cannot delete a storage area that is referred to in a storage
map.
2 – Examples
Example 1
Use the CHANGE DATABASE statement to enable after-image
journaling:
CHANGE DATABASE PATHNAME 'PERSONNEL'
JOURNAL FILE IS 'DEPT3:PERSONNEL.AIJ'.
Example 2
You can also use CHANGE DATABASE to disable after-image
journaling:
RDO> CHANGE DATABASE FILENAME 'PERSONNEL'
cont> NOJOURNAL.
Example 3
The database shutdown feature is designed to ensure that active
users on a single node or across a cluster cannot access the
database while you perform administrative and maintenance
tasks. For instance, use this shutdown feature when you need
the database in a stable condition while performing backup and
restore options, or when you are tuning the database.
In the following example, a complete database shutdown is
performed for all users of a database residing on a common
disk in a cluster. The CHANGE DATABASE ... OPEN IS MANUAL
statement is used to prevent new users from automatically opening
that database when the first DML statement is executed on the
database.
$ RDO = "$RDO"
$ RDO CHANGE DATABASE FILENAME "$222$DUA17:[DBS]PERS" OPEN IS MANUAL.
$ REPLY/ALL "PERS.RDB database will be shut down in 15 minutes"
$ REPLY/ALL "Please complete any work with PERS before then"
$ WAIT 00:15:00.00
$ RMU/CLOSE/ABORT=FORCEX/CLUSTER $222$DUA17:[DBS]PERS
At this point, you are assured that no unprivileged users can
invoke this database. After opening the database with the
RMU/OPEN/ACCESS=RESTRICTED command (the /ACCESS=RESTRICTED
qualifier restricts database access to users with the DBADM
privilege), you can perform maintenance tasks like tuning without
interference from unprivileged users. For example, you can add
or delete indexes and compare the performance results in a stable
testing environment.
When you are ready to make the database available again across
the cluster, enter RDO and type:
RDO> CHANGE DATABASE FILE "$222$DUA17:[DBS]PERS"
cont> OPEN IS AUTOMATIC.
Example 4
The following example shuts down access to a clusterwide database
and then reopens the database for a specific node:
$ RDO = "$RDO"
$ RDO CHANGE DATABASE FILENAME "$222$DUA17:[DBS]PERS" OPEN IS MANUAL.
$ REPLY/ALL "PERS.RDB database will be shut down in 15 minutes"
$ REPLY/ALL "Please complete any work with PERS before then"
$ WAIT 00:15:00.00
$ RMU/CLOSE/ABORT=FORCEX/CLUSTER $222$DUA17:[DBS]PERS
$ !
$ SET HOST MADABT
.
.
.
$ ! On node MADABT now
$ RMU/OPEN $222$DUA17:[DBS]PERS
The RMU/OPEN command opens the PERS database on MADABT only.
Users logged into other nodes in the cluster who normally have
access to the clusterwide database cannot access it until:
o The RMU/OPEN command is executed by a user with sufficient
privilege on his or her node.
o Or, the CHANGE DATABASE ... OPEN IS AUTOMATIC statement is
executed by a user with sufficient privilege from any node in
the cluster.
Example 5
The following example shows how to disable snapshots on a
database:
RDO> CHANGE DATABASE FILENAME 'PERSONNEL' SNAPSHOT IS DISABLED.
Example 6
The following example uses the CHANGE DATABASE statement to
define a new storage area for a multifile database:
RDO> CHANGE DATABASE FILENAME 'MF_PERSONNEL'
cont> DEFINE STORAGE AREA ARCHIVED_EMPS
cont> FILENAME DISK3:ARCHIVED_EMPS
cont> ALLOCATION IS 50 PAGES
cont> PAGE FORMAT IS MIXED
cont> SNAPSHOT_FILENAME IS DISK4:ARCHIVED_EMPS
cont> END ARCHIVED_EMPS STORAGE AREA.
Example 7
This example uses the DICTIONARY IS REQUIRED option to enforce
use of the data dictionary if metadata updates occur. Users must
invoke the database with the PATHNAME argument to perform any
metadata changes.
Note that when you specify the DICTIONARY option, that is the
only option you can specify in a CHANGE DATABASE statement. To
specify other options, you must issue another CHANGE DATABASE
statement.
RDO> CHANGE DATABASE FILENAME 'PERSONNEL'
cont> DICTIONARY IS REQUIRED.
Example 8
This example uses the READ_ONLY clause to change the ARCHIVED_
EMPS storage area to a read-only storage area.
RDO> CHANGE DATABASE FILENAME 'MF_PERSONNEL'
cont> CHANGE STORAGE AREA ARCHIVED_EMPS
cont> READ_ONLY.
Example 9
This example uses the GLOBAL BUFFERS clause to enable global
buffers for the MF_PERSONNEL database.
RDO> CHANGE DATABASE FILENAME MF_PERSONNEL
cont> GLOBAL BUFFERS ARE ENABLED
cont> (NUMBER IS 50,
cont> USER LIMIT IS 10).
In this example, 50 is the number of global buffers and 10 is the
maximum number (user limit) of global buffers per user.
Example 10
This example enables fast commit processing with a transaction
interval of 10, and specifies checkpoint intervals of 512 blocks
and 12 seconds:
RDO> CHANGE DATABASE FILENAME TEST1
cont> JOURNAL FAST COMMIT ENABLED
cont> (CHECKPOINT INTERVAL IS 512 BLOCKS,
cont> CHECKPOINT TIMED EVERY 12 SECONDS,
cont> TRANSACTION INTERVAL IS 10).
Example 11
This example disables the carry-over lock optimization:
RDO> CHANGE DATABASE FILENAME TEST1
cont> CARRY OVER LOCKS ARE DISABLED.
Example 12
This example sets the default database lock timeout interval to
25 seconds. This is the database wide timeout interval. It is
used as the default as well as the upper limit in determining the
timeout interval to use. For example, if LOCK TIMEOUT INTERVAL
IS 25 SECONDS is specified with the CHANGE DATABASE or DEFINE
DATABASE statement, and a user specifies 30 seconds with the
SQL SET TRANSACTION WAIT 30 statement or sets the logical name
RDM$BIND_LOCK_TIMEOUT_INTERVAL to 30, RDO would still use the
interval of 25 specified with the LOCK TIMEOUT INTERVAL clause.
The maximum number of seconds that can be specified with the LOCK
TIMEOUT INTERVAL clause is 65,000:
RDO> CHANGE DATABASE FILENAME TEST1
cont> CARRY OVER LOCKS ARE ENABLED
cont> LOCK TIMEOUT INTERVAL IS 25 seconds.
3 – Format
(B)0[m[4mCHANGE[m [4mDATABASE[m qqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqk
lqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqj
mwq>qqqqqqqqqqqqqqqqqqwqwq> [4mFILENAME[m qq> file-spec qwqk
mq> db-handle qq> = qj mq> [4mPATHNAME[m qq> path-name qj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj
mqwqwq> change-database-options1 qqqqqqqqqqqqqqqqqqwqwq> .
x tq> change-database-options2 qqqqqqqqqqqqqqqqqqu x
x tq> change-journal-file-options qqqqqqqqqqqqqqqu x
x tq> add-storage-area-clause qqqqqqqqqqqqqqqqqqqu x
x tq> change-storage-area-clause qqqqqqqqqqqqqqqqu x
x mq> [4mDELETE[m [4mSTORAGE[m [4mAREA[m qq> storage-area-name qj x
mqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqj
3.1 – db-handle
A host language variable or name that you associate with the
database. Use a database handle when you are accessing more than
one database at a time.
3.2 – file-spec
The name of the database file.
3.3 – path-name
The path name that refers to the data dictionary entity for the
database.
3.4 – change-database-options1
(B)0[mchange-database-options1 = qwqwqqqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqq> x tqq> [4mOPEN[m IS qqqqqqqqqqqqwqq> [4mAUTOMATIC[m qwqqqqqqqqqqqqqqqqqqqqu x x x mqq> [4mMANUAL[m qqqqqj x x x tqq> [4mNUMBER[m OF qqwq>[4m CLUSTER[m qqqqwq> [4mNODES[m IS number-nodes qqqqu x x x mq>[4m VAXCLUSTER[m qj x x x x x x x tqq> [4mNUMBER[m OF [4mBUFFERS[m IS qqqq> number-buffers qqqqqqqqqqqqqqqqu x x tqq> [4mNUMBER[m OF [4mUSERS[m IS qqqqqq> number-users qqqqqqqqqqqqqqqqqqu x x tqq> [4mADJUSTABLE[m [4mLOCK[m [4mGRANULARITY[m IS qwqq> [4mENABLED[m qqqwqqqqqqqqqu x x x mqq> [4mDISABLED[m qqj x x x tqq> [4mNUMBER[m OF [4mRECOVERY[m [4mBUFFERS[m IS qq> recovery buffers qqqqqqqu x x mqq> global-buffer-params qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
3.4.1 – OPEN Options
The OPEN options are OPEN IS AUTOMATIC and OPEN IS MANUAL.
These options determine whether any user can open a previously
unopened or a closed database by simply invoking it and executing
a DML statement (OPEN IS AUTOMATIC, the default). When OPEN IS
MANUAL is specified, only users with sufficient Oracle Rdb privilege
(ADMINISTRATOR privilege) for the database can enter an RMU/OPEN
command to open the database.
When you use the SQL ALTER DATABASE or RDO CHANGE DATABASE
OPEN IS MANUAL option in conjunction with the RMU/OPEN
/ACCESS=RESTRICTED command, access to the database is limited
to users with SQL DBADM or RDO ADMINISTRATOR privilege for
the database or OpenVMS BYPASS or SYSPRV privilege so that
maintenance (if the RMU image was not installed with the OpenVMS
SYSPRV privilege) operations can proceed without interference
from other users.
3.4.2 – number-nodes
The NUMBER OF CLUSTER NODES is clause and the NUMBER OF
VAXCLUSTER NODES is clause have exactly the same effect. The
option of using NUMBER OF CLUSTER NODES has been added to reflect
the fact that Oracle Rdb can run on different hardware platforms (in
addition to VAXclusters).
Sets the upper limit on the maximum number of nodes within a
cluster from which users can access the shared database. The
default is 16. The range is 1 node to 96 nodes. The actual
maximum limit is the current cluster limit.
In some cases, after you have specified a particular number of
users and nodes, doing a dump of the database root file will
display a different number of nodes than the value you set. The
following paragraphs explain why this occurs.
The relationship between the number of users and the number of
nodes supported on a database can be seen when you specify 2032
users and 4 nodes in a SQL CREATE/ALTER DATABASE or RDO DEFINE
/CHANGE DATABASE statement and then dump the database root file.
The dump displays values of 2032 users and 41 nodes.
To understand this relationship, Oracle Rdb uses a data structure
called a TSN Block (TSNBLK). A TSN Block keeps track of
transaction activity on a node and transaction information for
each user on a particular node. Each TSN Block is owned by a
particular node and can handle up to 50 users. For each group of
50 users one TSNBLK is allocated per node to cover the maximum
number of users and nodes in the cluster the database is expected
to support, which is determined as either one TSNBLK per node, or
one TSNBLK per 50 users, whichever is larger. The maximum number
of TSN blocks is equal to the value of the current maximum number
of nodes that are supported for a database.
For example, if the DBA specifies 2032 users and 4 nodes, this is
calculated as 2032/50 for a total of 41 TSNBLKs and this equates
to 41 nodes. The algorithm selects the maximum value of (# of
nodes specified, # of nodes calculated). So in this example, 41
is the maximum calculated value (calculated 41 > specified 4).
Had the DBA specified 2032 users and 50 nodes, 50 would be the
maximum value for the number of nodes (specified 50 > calculated
41) and 50 TSNBLKs would be allocated, one for each node.
As another example, if the DBA specifies 50 users and 10 nodes,
the maximum value is 10 nodes (specified 10 > calculated 1), so
ten TSNBLKs would be allocated, one for each node.
This parameter can only be used with multifile databases. To make
this change for a single-file database, first export the database
using the SQL EXPORT statement and then specify the change in the
SQL IMPORT statement.
3.4.3 – number-buffers
The number of buffers Oracle Rdb allocates per process using this
database. Specify an unsigned integer between 2 and 32768. The
default is 20 buffers.
3.4.4 – number-users
The maximum number of users allowed to access the database at one
time. The default is 50 users.
This parameter can only be used with multifile databases. To make
this change for a single-file database, first export the database
using the SQL EXPORT statement and then specify the change in the
SQL IMPORT statement.
3.4.5 – ADJUSTABLE_LOCK
Enables or disables whether or not the database system will
automatically maintain as few locks as possible on database
resources. The default is ENABLED, and results in fewer locks
against the database. However, if contention for database
resources is high, the automatic adjustment of locks can become a
CPU drain. Such databases can trade more restrictive locking for
less CPU usage by disabling adjustable lock granularity.
Disabling adjustable locking granularity may require that the
OpenVMS SYSGEN parameters for locks be increased.
3.4.6 – recovery-buffers
The number of database buffers used during the automatic recovery
process that is initiated after a system or process failure.
Specify an unsigned integer between 2 and 32768. The default is
20 buffers.
3.4.7 – global-buffer-params
(B)0[mglobal-buffer-params=
q> [4mGLOBAL[m [4mBUFFERS[m ARE qwq> [4mENABLED[m qqwqk
mq> [4mDISABLED[m qj x
lqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqj
mqwqqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqwqq>
mq> ( qq> [4mNUMBER[m IS number-glo-buffers qq> , qk x
lqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqj x
mq> [4mUSER[m [4mLIMIT[m IS max-glo-buffers qq> ) qqqqqj
3.4.7.1 – GLOBAL_BUFFERS
The GLOBAL BUFFERS ARE ENABLED clause specifies that Oracle Rdb
maintain one global buffer pool per node in the cluster for each
database. By default, Oracle Rdb maintains a local buffer pool for
each user. For more than one user to use the same page, each
must read it from disk into their local buffer pool. When the
GLOBAL BUFFERS ARE ENABLED clause has been specified, a page
in the global buffer pool may be read by more than one user at
the same time, although only one user reads the page from disk
into the global buffer pool. Global buffering provides improved
performance because I/O is reduced and memory is better utilized.
The default is GLOBAL BUFFERS ARE DISABLED, in which Oracle Rdb
maintains a local buffer pool for each user, and global buffers
are not enabled.
3.4.7.2 – NUMBER
When global buffers are enabled, the NUMBER IS clause is used to
specify the default number of global buffers per node.
The default number of global buffers is the maximum number
of users multiplied by 5. (In the RDO syntax for database
parameters, a user is the same as an attach.) You can override
the default by defining a value for the logical name RDM$BIND_
BUFFERS.
Although you can change the NUMBER IS parameter online, the
change will only take effect the next time that the database is
opened. By default, a database can be opened automatically (that
is, by any user who invokes the database and executes a data
manipulation language statement). If the database was modified
so that it must be manually opened, the RMU/OPEN command must be
used to open it.
3.4.7.3 – USER_LIMIT
The USER LIMIT clause specifies the maximum number of global
buffers each user allocates. Because global buffer pools are
shared by all users, you must define an upper limit on how many
global buffers a single user can allocate. This limit prevents a
user from defining the RDM$BIND_BUFFERS to use all the buffers in
the global buffer pool. The user limit cannot be greater then the
total number of global buffers. The default is 5.
See the Oracle Rdb Guide to Database Performance and Tuning for
information on determining the maximum number of global buffers a
user can allocate.
Although you can change the USER LIMIT IS parameter online, the
change will only take effect the next time that the database is
opened. By default, a database can be opened automatically (that
is, by any user who invokes the database and executes a data
manipulation language statement). If the database was modified
so that it must be manually opened, the RMU/OPEN command must be
used to open it.
3.5 – change-database-options2
(B)0[mchange-database-options2 = qwqwqq>qqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq> x tqq> [4mSNAPSHOT[m IS qqwq> [4mENABLED[m qwqqqqqqq>qqqqqqwqwqqqqqqqu x x x x tq> [4mIMMEDIATE[m qu x x x x x x mq> [4mDEFERRED[m qqj x x x x x mq> [4mDISABLED[m qqqqqqqq>qqqqqqqqj x x x tqq> [4mSNAPSHOT[m [4mALLOCATION[m IS q> snp-pages qq> PAGES qqqqqqu x x twq> [4mSNAPSHOT[m [4mEXTENT[m IS qwqqwq> extent-pages q> PAGES qwqu x x xmq> [4mEXTENT[m [4mIS[m qqqqqqqqqqj mq> (extension-options) qqqj x x x tqq> [4mCARRY[m [4mOVER[m [4mLOCKS[m ARE qwq> [4mENABLED[m qqwqqqqqqqqqqqqqqqu x x x mq> [4mDISABLED[m qj x x x tqq> [4mLOCK[m [4mTIMEOUT[m [4mINTERVAL[m IS number-seconds SECONDS qqqqu x x tqq> [4mREAD_ONLY[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x tqq> [4mREAD_WRITE[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x tqq> [4mDICTIONARY[m IS qqqqqqwq> [4mREQUIRED[m qqqqqqqwqqqqqqqqqqqu x x x mq> [4mNOT[m [4mREQUIRED[m qqqj x x x mqq> journal-fast-commit-clause qqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqj
3.5.1 – ENABLED-IMMEDIATE
Specifies that read/write transactions write copies of records to
the snapshot file before those records are modified, regardless
of whether a read-only transaction is active. The default is
SNAPSHOT IS ENABLED IMMEDIATE.
You enable snapshot writing to all snapshot files for all storage
areas when you specify the SNAPSHOT IS ENABLED clause.
3.5.2 – 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.
You enable snapshot writing to all snapshot files for all storage
areas when you specify the SNAPSHOT IS ENABLED clause.
3.5.3 – DISABLED
Disables snapshot transactions.
If snapshots are disabled and a user starts a READ_ONLY
transaction, the user will not receive an error message, but
their transaction will be converted to a READ_WRITE transaction,
but they will not be able to write.
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.
3.5.4 – snp-pages
The number of pages allocated for the snapshot file. The default
is 100 pages.
3.5.5 – extent-pages
The number of pages of each extent. The default is 100 pages.
3.5.6 – extension-options
Specifies the MIN, MAX, and percent growth of each database file
extent. Enclose the parameter list in parentheses.
(B)0[mextension-options =
qqq> ( qqq> [4mMINIMUM[m OF qq> min-pages qqq> PAGES, qk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqq> [4mMAXIMUM[m OF qq> max-pages qq> PAGES,qk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqq> [4mPERCENT[m [4mGROWTH[m IS qqq> growth qqqq> ) qqq>
3.5.6.1 – min-pages
The minimum number of pages of each extent. The default is 99
pages.
3.5.6.2 – max-pages
The maximum number of pages of each extent. The default is 9,999
pages.
3.5.6.3 – growth
The percent growth of each extent. The default is 20 percent
growth.
3.5.7 – CARRY_OVER_LOCKS
Enables or disables carry-over lock optimization. Carry-over
locks are enabled by default.
While attached to the database, a process can have some active
locks (locks attached to the database) and some carry-over
locks (locks requested in earlier transactions that have not
been demoted). If a transaction needs a lock it has currently
marked as carry-over, it can reuse the lock by changing it to an
active lock. The same lock can go from active to carry-over to
active multiple times without paying the cost of lock request and
demotion. This results in a substantial reduction in the number
of lock requests if a process accesses the same set of areas
repeatedly.
As part of the carry-over lock optimization, a NOWAIT transaction
requests, acquires, and holds a NOWAIT lock. This signals other
processes accessing the database that a NOWAIT transaction exists
and causes Oracle Rdb to release all carry-over locks. If NOWAIT
transactions are noticeably slow in executing, you can specify
CARRY OVER LOCKS ARE DISABLED with the CHANGE DATABASE or DEFINE
DATABASE statement.
This feature is available as an online database modification.
3.5.8 – LOCK_TIMEOUT
Specifies the number of seconds for processes to wait during a
lock conflict before timing out. The number of seconds can be
between one and 65,000.
The lock timeout interval is database-wide: it is used as the
default as well as the upper limit in determining the timeout
interval. For example, if LOCK TIMEOUT INTERVAL IS 25 SECONDS is
specified with the CHANGE DATABASE or DEFINE DATABASE statement,
and a user specifies SQL SET TRANSACTION WAIT 30 or sets the
logical name RDM$BIND_LOCK_TIMEOUT_INTERVAL to 30, RDO would
still use the interval 25 specified with the LOCK TIMEOUT
INTERVAL clause.
3.5.9 – READ_ONLY
The READ_ONLY option is used to change a read/write RDB$SYSTEM
storage area (and the Oracle Rdb system relations stored in the
area) to read-only. You might choose the READ_ONLY option if
your database is never or rarely updated. When the RDB$SYSTEM
storage area is changed to read-only, locking conflicts occur
less frequently, and the automatic updating of index and relation
cardinality is inhibited.
No write operation can be done in a read-only storage area except
a cardinality update. See the RMU/ANALYZE/CARDINALITY help topic
in the RMU help file for more information and restrictions on
updating cardinalities.
To change a storage area other than the RDB$SYSTEM storage area
to read-only, or to change a database to read-only, use the READ_
ONLY option of the change-storage-area-clause.
3.5.10 – READ_WRITE
The READ_WRITE option is used to change a read-only RDB$SYSTEM
storage area (and the Oracle Rdb system relations stored in the
area) to read/write. Select the READ_WRITE option to change a
read-only RDB$SYSTEM storage area to read/write.
To change a storage area other than the RDB$SYSTEM storage area
to read/write, or to change a database to read/write, use the
READ_WRITE option of the change-storage-area-clause.
3.5.11 – DICTIONARY
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.
If you specify the DICTIONARY option, you cannot specify any
other options in the same CHANGE DATABASE statement.
3.5.12 – journal-fast-commit-clause
(B)0[mjournal-fast-commit-clause=
qq> [4mJOURNAL[m [4mFAST[m [4mCOMMIT[m qwq> [4mENABLED[m qqwqqqk
mq> [4mDISABLED[m qj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>
m> ( qqwqwq> [4mCHECKPOINT[m [4mINTERVAL[m IS n BLOCKS qqqqqwqwq> ) qj
x tq> [4mCHECKPOINT[m [4mTIMED[m EVERY n SECONDS qqqqu x
x tq> [4mCOMMIT[m [4mTO[m JOURNAL OPTIMIZATION qqqqqqu x
x tq> [4mNO[m [4mCOMMIT[m [4mTO[m JOURNAL OPTIMIZATIONqqqqu x
x mq> [4mTRANSACTION[m [4mINTERVAL[m [4mIS[m number-txns qj x
mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqj
3.5.12.1 – FAST_COMMIT
By default, Oracle Rdb writes updated database pages to disk
each time a transaction executes the COMMIT statement. If a
transaction fails before committing, Oracle Rdb only needs to
rollback (undo) the current failed transaction; it never has
to redo previous successful transactions.
You can change the commit processing method by enabling journal
fast commit processing (fast commit). With fast commit enabled,
Oracle Rdb keeps updated pages in the buffer pool (in memory) and
does not write the pages to disk when a transaction commits. The
updated pages can remain in the buffer pool until the process
meets a condition specified by the database administrator or
applications programmer. At the moment the condition is met,
called the checkpoint, all the pages the process has updated, for
multiple transactions, are written to disk.
You can set a checkpoint for your process when:
o A fixed number of transactions have been committed or aborted
o A specified time interval has elapsed
o The after-image journal (AIJ) file has grown a certain amount
If a transaction fails, Oracle Rdb must undo the current, failed
transaction and redo all the committed transactions since the
last checkpoint. Redoing updates involves reading the AIJ file
and reapplying the changes to the relevant data pages.
The checkpoint interval value is set by the database
administrator and applies to all processes attached to a
database. Users can implement an alternate, process-specific
method of checkpointing by defining the logical name RDM$BIND_
CKPT_TRANS_INTERVAL. The mechanism uses transaction count as
the checkpoint. When fast commit processing is disabled, the
RDM$BIND_CKPT_TRANS_INTERVAL logical name is ignored. For more
information about the RDM$BIND_CKPT_TRANS_INTERVAL logical name,
see the Oracle Rdb Guide to Database Performance and Tuning.
Fast commit processing applies only to data updates, that
is erase, modify, and store operations. Transactions that
include data definition statements, such as DEFINE RELATION and
DEFINE INDEX statements, force a checkpoint at the end of the
transaction.
Note that in order to enable fast commit, you must enable after-
image journaling.
3.5.12.2 – CHECKPOINT_INTERVAL
You can limit how many transactions the Database Recovery process
(DBR) must redo by setting a checkpoint interval. Setting a
checkpoint interval instructs Oracle Rdb to periodically flush
updated pages. This shortens recovery time.
The value you assign to the checkpoint interval specifies the
number of blocks the .AIJ file is allowed to grow before updated
pages are flushed. For example, if you set the checkpoint
interval value equal to 100, all processes will flush updated
pages to disk when 100 blocks have been written to the .AIJ file
since the last checkpoint. Thus all processes contribute to .AIJ
growth.
If no checkpoint interval is established and a process completes
1000 transactions but fails during number 1001, the DBR must redo
transactions 1 through 1000 and undo number 1001.
When a process binds to the database, it writes a checkpoint
record to the .AIJ file and notes the virtual block number (VBN)
of the .AIJ file at which the checkpoint record is located. If
the checkpoint is located at VBN 120 and the checkpoint interval
is 100 blocks, the process will checkpoint again when VBN 220 is
reached.
A process will never checkpoint in the middle of a transaction.
Since all processes contribute to .AIJ file growth, a process
may be able to commit many transactions before checkpointing
if update activity by other processes is low. Conversely, if a
process' first transaction is long and if update activity by
other processes is high, the process may be forced to checkpoint
when it commits its first transaction.
When the database checkpoint interval value is reached, Oracle Rdb
executes the following steps:
o Updated pages are written to disk.
o A checkpoint record is written to the .AIJ file.
o The root user process block (RTUPB) for each process is
updated to indicate where the checkpoint record is stored in
the .AIJ file. The RTUPB is a data structure in the database
root file that maintains information on each process accessing
the database. The database recovery process (DBR) uses the
RTUPB checkpoint entry to determine where in the .AIJ file
recovery must start.
3.5.12.3 – CHECKPOINT_TIMED
Assigns a value to the checkpoint interval specifying the number
of seconds that can pass before updated pages are written. When
the specified number of seconds elapse, Oracle Rdb executes the
checkpoint steps described in the CHECKPOINT_INTERVAL topic. You
can set both a checkpoint based on time and a checkpoint based on
AIJ file growth: Oracle Rdb will checkpoint at whichever checkpoint
it reaches first.
For example, if you specify TIMED EVERY 100 SECONDS, each process
checkpoints when it completes a transaction after at least 100
seconds have passed since its last checkpoint.
For example, the following statement enables fast commit
processing and specifies checkpoint intervals of 512 blocks and
12 seconds:
RDO> CHANGE DATABASE FILENAME TEST1
cont> JOURNAL FILE IS TEST1_AIJ
cont> JOURNAL FAST COMMIT ENABLED
cont> (CHECKPOINT INTERVAL IS 512 BLOCKS,
cont> CHECKPOINT TIMED EVERY 12 SECONDS).
3.5.12.4 – COMMIT_TO_JOURNAL
If you enable COMMIT TO JOURNAL OPTIMIZATION when you enable
fast commit, Oracle Rdb does not write commit information to
the database root file. This option enhances performance in
database environments that are update intensive. Because
of the prerequisites for enabling the journal optimization
option, general use databases or databases that have many read-
only transactions may not benefit from this feature. For more
information see the Oracle Rdb Guide to Database Maintenance.
Note that if you specify COMMIT TO JOURNAL OPTIMIZATION, you must
disable or defer snapshots.
If you change snapshots to enabled immediate, then you must
disable COMMIT TO JOURNAL OPTIMIZATION.
3.5.12.5 – TRANSACTION_INTERVAL
The TRANSACTION INTERVAL IS n clause specifies the size of
the TSN range where n equals the number of TSNs (transaction
sequence numbers). Oracle Rdb uses transaction sequence numbers to
ensure database integrity. When you specify NO COMMIT TO JOURNAL
OPTIMIZATION, TSNs are assigned to users one at a time. When the
journal optimization option is enabled, each user is pre-assigned
a range of TSNs. Assigning a range of TSNs avoids the single-
threading problem because commit information need not be written
to the database root for each transaction. Oracle Rdb writes all
transaction information to the .AIJ file except for each user's
allocated TSN range, which it writes to the root.
The transaction interval value (the TSN range) must be a number
between 8 and 1024. The default value is 256. You need to decide
which constraint has precedence on your database: performance or
running out of TSNs.
As a general guideline, if your database has few users or if
all user sessions are long, select a high transaction interval.
If your database has many users or if user sessions are short,
select a smaller transaction interval.
You can specify a transaction interval with JOURNAL FAST COMMIT
IS DISABLED, but the interval will not be used until you enable
the fast commit protocol.
3.6 – change-journal-file-options
(B)0[mchange-journal-file-options = qwqwqqqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq> x tq> [4mJOURNAL[m [4mFILE[m [4mIS[m qq> aij-journal-file-spec qqqqqqqqqu x x tq> [4mNOJOURNAL[m qqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqu x x tq> [4mJOURNAL[m [4mALLOCATION[m IS q> journal-blocks q> BLOCKS qu x x mq> [4mJOURNAL[m [4mEXTENT[m IS qqq> extent-blocks qq> BLOCKS qqqj x mqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
3.6.1 – aij-journal-file-spec
The file specification for the after-image journal file.
The journal file specification must be fully qualified and
conform to file naming conventions, except that the file
specification must not include a DECnet node name. You can define
a system logical name to refer to the journal file. If you do not
include a file type in the journal file specification, Oracle Rdb
assigns a default file type of AIJ.
3.6.2 – journal-blocks
The number of blocks allocated to the after-image journal file.
The default is 0 blocks.
3.6.3 – extent-blocks
The size of each extent of the after-image journal file. The
default is 512 blocks.
3.7 – add-storage-area-clause
Allows you to define a new storage area for a multifile database.
To restructure a single-file database to a multifile database,
use the EXPORT and IMPORT statements.
(B)0[madd-storage-area-clause =
qqqq> [4mDEFINE[m [4mSTORAGE[m [4mAREA[m qqqqqqq> storage-area-name qqqqqqqqqqqk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqq> [4mFILENAME[m qqqq> file-spec qqqwqqqqqqqqqqqqqqq>qqqqqqqqqqqwqqqk
mqq> storage-area-options qqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqqqqqqq> [4mEND[m qqqwqqqqqqqqq>qqqqqqqqqqqwq> STORAGE AREA qqqq>
mq>storage-area-name qj
3.7.1 – storage-area-name
The name of the storage area you want to create.
3.7.2 – file-spec
The file specification for the storage area file. By default,
this file has the default file type RDA.
3.7.3 – storage-area-options
(B)0[mstorage-area-options = qwwqqqqqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwwq> xtqq> [4mALLOCATION[m IS qqq> number-pages qqqq>qqqqqqqqq PAGES qqqqux xtqq> [4mPAGE[m [4mSIZE[m IS qqqq> page-blocks qqqqq>qqqqqqqqq BLOCKS qqqux xtqq> [4mPAGE[m [4mFORMAT[m IS qqwqqqq> [4mUNIFORM[m qqqqwqqqqqqqqqqqqqqqqqqqqux xx mqqqq> [4mMIXED[m qqqqqqj xx xtqq> [4mTHRESHOLDS[m ARE q> ( q> val1 wqqqqqqqqqqqqqqqqqqqqwq> ) qqux xx m> ,val2 wqqqqqqqqqwqj xx xx m> ,val3 qj xx xtqq> [4mINTERVAL[m IS qqqqqqq> number-data-pages qqqqqqqqqqqqqqqqqqux xtqq> [4mSNAPSHOT_FILENAME[m [4mIS[m qqqq> file-spec qqqqqqqqqqqqqqqqqqqqux xtqq> [4mSNAPSHOT[m [4mALLOCATION[m IS qqq> snp-pages qqq> PAGES qqqqqqqqux xtwq> [4mSNAPSHOT[m [4mEXTENT[m IS qwqqwq> extent-pages qqqq> PAGES qwqqqux xxmq> [4mEXTENT[m IS qqqqqqqqqqj mq> extension-options qqqqqqqqj xx xmqq> [4mWRITE_ONCE[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqjx mqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
3.7.3.1 – number-pages
The number of database pages allocated to the storage area
initially. Oracle Rdb automatically extends the allocation to handle
the loading of data and subsequent expansion. The default is 400
pages.
3.7.3.2 – 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.
3.7.3.3 – 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.
3.7.3.4 – 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. 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. Threshold values can be set for
storage areas with MIXED or UNIFORM storage area page formats.
3.7.3.5 – 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.
3.7.3.6 – file-spec
Provides a separate file specification for the snapshot file.
Do not specify a file extension other than SNP to the file
specification.
3.7.3.7 – snp-pages
Specifies the number of pages allocated for the snapshot file.
The default is 100 pages. You can set the snapshot allocation to
0 pages. In the following cases, you may want to set the snapshot
allocation to 0 pages:
o If you have disabled snapshots. By setting the snapshot
allocation to 0, you may save space.
o If you have changed a read/write storage area to read-only.
The snapshot file is not used, and you can save space by
setting the snapshot allocation to 0 pages.
3.7.3.8 – extent-pages
Specifies the number of pages of each extent. The default is 100
pages.
3.7.3.9 – extension-options
Specifies the MIN, MAX, and percent growth of each database file
extent. Enclose the parameter list in parentheses.
(B)0[mextension-options =
qqq> ( qqq> [4mMINIMUM[m OF qq> min-pages qqq> PAGES, qk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqq> [4mMAXIMUM[m OF qq> max-pages qq> PAGES,qk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqq> [4mPERCENT[m [4mGROWTH[m IS qqq> growth qqqq> ) qqq>
o min-pages
Specifies the minimum number of pages of each extent. The
default is 99 pages
o max-pages
Specifies the maximum number of pages of each extent. The
default is 9,999 pages.
o growth
Specifies the percent growth of each extent. The default is 20
percent growth.
3.8 – change-storage-area-clause
Allows you to modify the extent and snapshot allocation of
a storage area. You cannot specify RDB$SYSTEM in the change-
storage-area-clause. To change the EXTENT, SNAPSHOT ALLOCATION,
or SNAPSHOT EXTENT for RDB$SYSTEM, specify these qualifiers as
part of the change-database-options. When you specify these
options as part of the change-database-options, they will be
changed for RDB$SYSTEM but not for any other storage areas.
(B)0[mchange-storage-area-clause =
qqqqqq> [4mCHANGE[m [4mSTORAGE[m [4mAREA[m qqqqqqqq> storage-area-name qqqqqk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj
mqwqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqwqwqwqq>
x x tq> [4mREAD_WRITE[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x
x x tq> [4mEXTENT[m IS qw> extent-pages qqqqqqqq PAGES qwqqqqqu x x
x x x m> extension-options qqqqqqqqqqqj x x x
x x tq> [4mSNAPSHOT[m [4mALLOCATION[m IS qq> snp-pages qqq> PAGES qu x x
x x mq> [4mSNAPSHOT[m [4mEXTENT[m IS qw> extent-pages qq> PAGES qwqj x x
x x m> (extension-options) qqqqj x x
x mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj x
tqqqqq> [4mREAD_ONLY[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu
mqqqqq> [4mWRITE_ONCE[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
3.8.1 – storage-area-name
The name of the storage area that you want to modify.
3.8.2 – READ_WRITE
Used to change any read-only storage area (except the RDB$SYSTEM
storage area) to a read/write storage area.
To change a read-only RDB$SYSTEM storage area to read/write, you
must use the READ_WRITE option of the change-database-options2
clause.
3.8.3 – snp-pages
Specifies the number of pages allocated for the snapshot file.
The default is 100 pages.
3.8.4 – extent-pages
Specifies the number of pages of each extent. The default is 100
pages.
3.8.5 – extension-options
Specifies the MIN, MAX, and percent growth of each database file
extent. Enclose the parameter list in parentheses.
(B)0[mextension-options =
qqq> ( qqq> [4mMINIMUM[m OF qq> min-pages qqq> PAGES, qk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqq> [4mMAXIMUM[m OF qq> max-pages qq> PAGES,qk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqq> [4mPERCENT[m [4mGROWTH[m IS qqq> growth qqqq> ) qqq>
3.8.5.1 – min-pages
Specifies the minimum number of pages of each extent. The default
is 99 pages.
3.8.5.2 – max-pages
Specifies the maximum number of pages of each extent. The default
is 9,999 pages.
3.8.5.3 – growth
Specifies the percent growth of each extent. The default is 20
percent growth.
3.8.6 – READ_ONLY
Used to change any read/write storage area (except the RDB$SYSTEM
storage area) to a read-only storage area.
To change a read/write RDB$SYSTEM storage area to read-only, you
must use the READ_ONLY option of the change-database-options2
clause.
You cannot change the parameters of a read-only storage area.
You must first change the storage area to a read/write area, then
change the parameters.
Also, no write operation can be done in a read-only storage area
except a cardinality update. See the RMU/ANALYZE/CARDINALITY help
topic in the RMU help file for more information and restrictions
on updating cardinalities.
3.8.7 – WRITE_ONCE
You can use the WRITE_ONCE option to change a storage area
containing stable segmented string data to a format that can
be stored on a write-once, read-many (WORM) optical disk. A WORM
optical disk offers a relatively inexpensive way of storing large
amounts of data for read-only access compared to other storage
media.
The following restrictions apply to the WRITE_ONCE option:
o Note that you cannot write data other than segmented strings
to a write-once storage area. Oracle Rdb issues an error message
if you try to create a storage map that stores data other than
segmented strings in a write-once storage area. Storage maps
for non-segmented-string data must be removed before you can
alter a storage area to WRITE_ONCE.
o When you create a storage area on WORM media, you must specify
that the snapshot area remains on read/write media: do not
give a snapshot file the WRITE_ONCE attribute.
o If you specify the WRITE_ONCE option when storing a segmented
string, database keys are not compressed. For more information
on database key compression, see the Oracle Rdb Guide to Database
Maintenance.
o Write-once storage areas do not use SPAM pages to look for
storage space, but to assist moving data back to non-WORM
media in which SPAM pages must be built again, space is still
allocated for them. Since SPAM pages are essential in uniform
areas, Write-once storage areas cannot be of uniform format
and therefore are required to be of mixed format.
o You can use the PAGE SIZE IS clause of CREATE STORAGE AREA to
change the default page size for a storage area. To optimize
storage, always specify an even number of blocks per page.
o Rdb does not support magnetic media for storing write-once
storage areas.
o After you move a storage area to or from a WORM device,
Do a full and complete backup of your database with the
RMU/BACKUP command and start a new after-image journaling
file. For more information on backup and recovery procedures
with write-once storage areas, see the Oracle Rdb Guide to
Database Maintenance.
3.9 – DELETE_STORAGE_AREA
Deletes the named storage area. You cannot delete the RDB$SYSTEM
storage area. You cannot delete a storage area if a storage map
refers to it, or if there is data in it.