HP 3000 Manuals HP 3000 Manuals
START DBE NEW
The START DBE NEW statement configures and establishes a connection with
a new DBEnvironment. It establishes a set of startup parameters that
apply to this and all subsequent connections until all connections to the
DBEnvironment have been terminated. Startup parameters are also stored
in the DBECon file.
Scope
ISQL or Application Programs
SQL Syntax--START DBE NEW
START DBE 'DBEnvironmentName' [AS 'ConnectionName'] [MULTI] NEW
[{DUAL } |...| LOG ]
[{AUDIT} ]
[ ]
[BUFFER = (DataBufferPages, LogBufferPages) ]
[LANG = LanguageName ]
[TRANSACTION = MaxTransactions ]
[ {TimeoutValue [SECONDS]} ]
[MAXIMUM TIMEOUT = { [MINUTES]} ]
[ { } ]
[ {NONE } ]
[ ]
[ {TimeoutValue [SECONDS]} ]
[DEFAULT TIMEOUT = { [MINUTES]} ]
[ { } ]
[ {MAXIMUM } ]
[ ]
[RUN BLOCK = ControlBlockPages ]
[DEFAULT PARTITION = {DefaultPartitionNumber}] |,...|
[ {NONE }]
[ ]
[ {CommentPartitionNumber}]
[COMMENT PARTITION = {DEFAULT }]
[ {NONE }]
[ ]
[MAXPARTITIONS = MaximumNumberOfPartitions ]
[AUDIT NAME = 'AuditName' ]
[{COMMENT } ]
[{DATA } ]
[{DEFINITION } ]
[{STORAGE } |...| AUDIT ELEMENTS ]
[{AUTHORIZATION} ]
[{SECTION } ]
[{ALL } ]
[ ]
[DBEFile0Definition ]
[DBELogDefinition ]
Parameters--START DBE NEW
DBEnvironmentName identifies the DBEnvironment name used in the
CONNECT statement. This name also identifies the
DBECon file that stores the values of all
parameters specified in the START DBE NEW
statement that are also used in the CONNECT
statement. Name qualification follows
standard MPE/iX file naming conventions.
DBEnvironmentName cannot exceed 36 bytes. Unless
you specify a group and account name, ALLBASE/SQL
assumes the name is relative to your current
group and account. You can also use an MPE/iX
back reference for DBEnvironmentName as shown in
the following example:
:FILE DBE = PartsDBE.SomeGrp.Acct
START DBE '*DBE' NEW
ConnectionName associates a user specified name with this
connection. ConnectionName must be unique for
each DBEnvironment connection within an
application. If a ConnectionName is not
specified, DBEnvironmentName is the default.
ConnectionName cannot exceed 128 bytes.
MULTI indicates the DBEnvironment can be accessed by
multiple users simultaneously. If omitted, the
DBEnvironment can be accessed only in single-user
mode.
DUAL LOG causes ALLBASE/SQL to maintain two separate logs,
preferably on different media. Keeping the log
files on separate media ensures that a media
failure on one device leaves the other log
undamaged. Each log write operation is performed
on both logs; if an error is detected, the write
continues on the good log only. Normally, only
one log is read, but if an error is encountered,
ALLBASE/SQL switches to the other log. Data
integrity is maintained provided is at least one
good copy of each log record is on at least one
of the logs.
AUDIT LOG identifies the DBEnvironment as one that will
have audit logging performed on it with the
elements specified in the AUDIT ELEMENTS clause.
This causes ALLBASE/SQL to create audit log
records as well as normal log records in the log
file so that the database can be audited.
DataBufferPages specifies the number of 4096-byte data buffer
pages to be used. Data buffer pages hold index
and data pages.
You can request up to 50,000 data buffer pages.
The minimum number of data buffer pages is 15.
The default number is 100. The total number of
data buffer pages and runtime control block pages
cannot exceed 256 Mbytes.
LogBufferPages specifies the number of 512-byte log buffer pages
to be used. You can request from 24 to 1024 log
buffer pages, limited by the amount of storage
available. The default number of log buffer
pages is 24.
LANG
specifies the language for the DBEnvironment. If
the name of the language contains a hyphen, use
double quotes in specifying it, as in the
following (C-FRENCH means Canadian French):
LANG = "C-FRENCH"
MaxTransactions
specifies the maximum number of concurrent
transactions to be supported. You can specify a
value from 2 to 240.[REV BEG] The default is 50.
[REV END] Any attempt to start a transaction
beyond the maximum limit waits for the specified
TIMEOUT and returns an error if TIMEOUT is
exceeded. For each user logged on to the system
at any point in time you should allow 2
concurrent transactions for just being connected
to the DBE.
MAXIMUM TIMEOUT specifies the maximum user timeout value that is
stored in the DBECon file. The default is the
MAXIMUM.
DEFAULT TIMEOUT specifies the default user timeout value that is
stored in the DBECon file. The default is NONE
(infinity).
TimeoutValue is an integer literal greater than zero. If the
TimeoutValue is not qualified by MINUTES, SECONDS
is assumed. If representing seconds,
TimeoutValue must be in the range of 1 to
2,147,483,647. If representing minutes,
TimeoutValue must be in the range of 1 to
35,791,394.
ControlBlockPages specifies the number of runtime control blocks to
be allocated. The value specified is stored in
the DBECon file.
You can specify a value from 17 to 2,000 pages
for this parameter. The default is 37 pages.
The total number of data buffer pages and runtime
control block pages cannot exceed 256 Mbytes.
DefaultPartitionNumber specifies the default partition number for the
DBEnvironment. This clause must be specified if
AUDIT LOG is specified. DefaultPartitionNumber
must be in the range 1 and 32767. If NONE is
specified, tables in the DBEnvironment that are
in the default partition do not generate audit
log records. See the CREATE TABLE and ALTER
TABLE statements for information on assigning a
table to a partition.
CommentPartitionNumber specifies the partition number for comments made
in the DBEnvironment. CommentPartitionNumber
must be a number between 1 and 32767. If no
COMMENT PARTITION is specified, DEFAULT is
implied.
If the comment partition is DEFAULT and the
default partition number is later changed in a
START DBE NEWLOG statement (but the comment
partition is not changed from DEFAULT), the
comment partition number will also change to the
new default partition number.
MaximumNumberOfPartitionspecifies the maximum number of partitions for
the DBEnvironment. This clause must be
specified if AUDIT LOG has been specified.
MaximumNumberOfPartitions is required to be a
number between 1 and 831. This number indicates
the number of partition instances the
DBEnvironment is expected to track.
For audit logging purposes, the number of
partition instances is calculated as the sum of
the number of DATA partitions and the number of
elements (not counting the DATA element)
specified in the AUDIT ELEMENTS clause.
Specifying ALL audit elements (see below)
includes 6 elements, implying that 6 partitions
are used. Set this value only as high as needed
so that unnecessary space is not reserved unless
you plan more partitions or audit elements in the
future.
AuditName specifies the name of this audit DBEnvironment.
AuditName is limited to 8 bytes. This clause
must be specified if AUDIT LOG has been
specified. The AuditName appears in outputs of
the Audit Tool.
AUDIT ELEMENTS Specifies the types of audit logging that will be
done for the database. If this clause is omitted
and AUDIT LOG is specified, DATA AUDIT ELEMENTS
is implicit. The audit elements are as follows:
COMMENT permits use of the LOG COMMENT
statement in the DBEnvironment.
Comments are logged to the defined
COMMENT PARTITION. If this element
is not chosen, the LOG COMMENT
statement returns an error.
DATA is the default element. It causes
audit log records to be done for
any data operations (INSERT,
UPDATE, or DELETE) on tables that
are in an audit partition of the
DBEnvironment other than NONE.
(Tables can be specified to be in
partition NONE and thus not
participate in the audit logging
process.)
DEFINITION includes audit logging of the
following statements:
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
CREATE VIEW
DROP VIEW
CREATE RULE
DROP RULE
CREATE PROCEDURE
DROP PROCEDURE
TRANSFER OWNERSHIP
CREATE GROUP
DROP GROUP
CREATE DBEFILESET
DROP DBEFILESET
CREATE PARTITION
DROP PARTITION
TRUNCATE TABLE
STORAGE includes audit logging of the
following statements:
CREATE DBEFILE
DROP DBEFILE
ADD DBEFILE TO DBEFILESET
REMOVE DBEFILE FROM DBEFILESET
CREATE TEMPSPACE
DROP TEMPSPACE
AUTHORIZATION includes audit logging of the
following statements:
GRANT
REVOKE
ADD TO GROUP
REMOVE FROM GROUP
SECTION includes audit logging of the
creation and deletion of permanent
sections or procedures. Permanent
sections or procedures are created
when a program is preprocessed, and
are deleted by the DROP MODULE
statement. The DROP PROCEDURE
statement deletes procedures.
Logging of section creation does
not include any SETOPT information
associated with the section. See
the SETOPT statement.
ALL is equivalent to specifying COMMENT
DATA DEFINITION STORAGE
AUTHORIZATION SECTION AUDIT
ELEMENTS as described above.
DBEFile0Definition is a clause that provides the information
ALLBASE/SQL needs to automatically create
DBEFile0 and add it to the SYSTEM DBEFileSet.
The syntax for this clause is presented
separately below. If DBEFile0Definition is
omitted, ALLBASE/SQL assumes the following:
DBEFILE0 DBEFILE DBEFILE0
WITH PAGES = 150,
NAME = 'DBEFile0'
DBEFile0 always resides in the same group and
account as the DBECon file. However, you can use
the SQLUtil MOVEFILE command to move it to
another device with the same file, group, and
account name.
DBELogDefinition is a clause that provides ALLBASE/SQL with the
information needed to create one or more log
files. Syntax for this clause is presented
separately below. If DBELogDefinition is
omitted, ALLBASE/SQL assumes the following:
LOG DBEFILE DBELOG1
WITH PAGES = 250,
NAME = 'DBELOG1
By default, DBELOG1 resides in the same group and
account as the DBECon file.
SQL Syntax--DBEFile0Definition
DBEFILE0 DBEFILE DBEFile0ID
WITH PAGES = DBEFile0Size,
NAME = 'SystemFileName1'
Parameters--DBEFile0Definition
DBEFILE0 DBEFILE describes a DBEFile known as DBEFile0, which
contains the portion of the system catalog needed
for activating a DBEnvironment, including
definitions of other DBEFiles. Each
DBEnvironment must have a DBEFile0 associated
with a unique SystemFileName, which is assigned
in this clause.
DBEFile0ID is the basic name identifying DBEFile0.
DBEFile0Size specifies the number of 4096-byte pages in
DBEFile0. You can specify from 150 to 524,287
pages. The default and minimum is 150.
SystemFileName1 identifies how DBEFile0 is known to the operating
system. DBEFile0 is created in the same group
and account as the DBECon file by default. You
can specify a different group name for each log
file, but the account name, if given, must be the
same as that of the DBECon file. The default
file name is 'DBEFile0'.
SQL Syntax--DBELogDefinition
LOG DBEFILE DBELog1ID [AND DBELog2ID]
WITH PAGES = DBELogSize,
NAME = 'SystemFileName2'
[AND 'SystemFileName3']
Parameters--DBELogDefinition
LOG DBEFILE describes the two log files if the DUAL LOG
option is specified, or a single log file
otherwise. If you give information for two log
files but omit the DUAL LOG option, the
information for the second log file is ignored.
DBELog1ID and are the basic names identifying the log files.
DBELog2ID
DBELogSize specifies the number of 512-byte pages in one log
file. If dual logging is used, both logs must be
the same size. The DBE log size should be at
least 250 pages and no greater than 524,287
pages. The default is 250. If you choose an odd
number of pages, the number is rounded up to an
even number.
SystemFileName2 and identify how the logs are known to MPE/iX. The
SystemFileName3 logs are created in the same group and account as
the DBECon file by default. You can specify a
different group name for each log file, but the
account name, if given, must be the same as that
of the DBECon file.
Description
* When you issue this statement, ALLBASE/SQL creates a DBECon file
with the same name as the DBEnvironmentName.
* The following parameters defined in the START DBE NEW statement
are stored in the DBECon file:
* DBEnvironment language
* User mode (single versus multi)
* Number of data buffer pages
* Number of log buffer pages
* Maximum transactions
* Maximum timeout value
* Default timeout value
* Number of runtime control block pages
* DBEFile0 system file name
* Log system file name(s)
* Audit logging (chosen versus not)
* Audit name
* Audit elements
* Default partition
* Comment partition
* Maximum number of partitions
* The following additional parameters are stored in the DBECon file:
* The autostart flag determines how DBE sessions are started.
If the value of autostart is ON, a DBE session can be
established by using the CONNECT statement. If the value
of autostart is OFF, the START DBE statement must be used
to start up a DBEnvironment; if the START DBE statement
contains the MULTI option, other users establish DBE
sessions with the CONNECT statement. Autostart is on by
default.
* The DDL Enabled flag determines whether data definition is
enabled for the DBEnvironment. The DDL Enabled flag is set
to YES by default. See "Maintenance" in the ALLBASE/SQL
Database Administration Guide for additional information
about the DDL Enabled flag.
* The archive mode flag determines whether the DBEnvironment
is operating in archive mode. In archive mode, ALLBASE/SQL
does rollforward logging. The rollforward log can be used
to redo transactions in case it is necessary to restore the
DBEnvironment from a backed up (archival) copy. When
archive mode has the value of OFF, log space can be
recovered by using the CHECKPOINT statement. If you want
to do rollforward recovery, you must always operate in
archive mode. Rollback recovery is enabled regardless of
the archive mode. Archive mode is off by default.
* When you choose an odd number of log pages using the WITH PAGES
clause of the DBEFile definitions, the number is rounded up to an
even number, which is displayed in SHOWLOG.
* The size of DBEFile0 is fixed at the time you configure a
DBEnvironment and cannot be changed. If you need more space at a
later time, add a DBEFile to the SYSTEM DBEFileSet.
* DBEFile0 cannot be restricted to containing data pages only or
index pages only; the storage in DBEFile0 is used for both data
and index pages.
* You can reconfigure a DBEnvironment by using SQLUtil to alter
DBECon file parameters. All parameters except the name of the
DBECon file and DBEFile0 may be changed. Refer to the ALLBASE/SQL
Database Administration Guide for additional information.
* If no MAXIMUM TIMEOUT limit is specified, or if MAXIMUM TIMEOUT =
NONE, infinity (no timeout) is assumed. If no DEFAULT TIMEOUT
value is specified, or if DEFAULT TIMEOUT = MAXIMUM, the value of
MAXIMUM TIMEOUT is assumed. The DEFAULT TIMEOUT value may not
exceed the MAXIMUM TIMEOUT value.
* You can reconfigure a DBEnvironment by using SQLUtil to alter
DBECon file parameters. All parameters except the audit
information (logging, audit elements, name, default, comment and
maximum partition), or the name of the DBECon file and DBEFile0
may be changed. Refer to the ALLBASE/SQL Database Administration
Guide for additional information.
* If AUDIT LOG is specified, the clauses AUDIT NAME, DEFAULT
PARTITION, and MAXPARTITIONS must also be specified. Further, if
no AUDIT ELEMENTS are specified, DATA is used as a default. If no
COMMENT PARTITION is specified, DEFAULT is assumed. The DEFAULT
PARTITION or the COMMENT PARTITION can be specified as NONE.
* Use of the clause ALL AUDIT ELEMENTS implies specification of all
of the audit elements.
* Additional log files should be added using the SQLUtil ADDLOG
command.
Authorization
No authorization is needed for using the START DBE NEW statement.
Example
The DBEnvironment for the sample database is a multiuser DBEnvironment in
which as many as five transactions can execute concurrently. The
DBEnvironment is initially configured for two rollback logs and a
DBEFile0 residing in PartsF0. The number of runtime control pages to be
used is 500. By default, autostart mode is set to ON.
START DBE 'PartsDBE.SomeGrp.SomeAcct' MULTI NEW
DUAL LOG,
TRANSACTION = 5,
DBEFILE0 DBEFILE PartsDBE0
WITH PAGES = 150, NAME = 'PartsF0',
LOG DBEFILE PartsDBELog1 AND PartsDBELog2
WITH PAGES = 256, NAME = 'PartsLg1' AND 'PartsLg2',
RUN BLOCK = 500
The DBEnvironment has all the above parameters listed and it is enabled
for audit logging. All DML and DDL changes in the DBEnvironment are
subject to audit logging since all audit elements are selected. Up to 20
partitions can coexist in this DBEnvironment, allowing for 14 data
partitions in addition to the other elements' partitions. The log files
should be made large enough for the added audit log records.
START DBE 'PartsDBE' MULTI NEW
DUAL AUDIT LOG,
TRANSACTION = 5,
RUN BLOCK = 500,
AUDIT NAME = 'PrtsDBE1',
DEFAULT PARTITION = 1,
COMMENT PARTITION = 2,
MAXPARTITIONS = 20,
ALL AUDIT ELEMENTS,
DBEFILE0 DBEFILE PartsDBE0
WITH PAGES = 150, NAME = 'PartsF0',
LOG DBEFILE PartsDBELog1 AND PartsDBELog2
WITH PAGES = 1000, NAME = 'PartsLg1' AND 'PartsLg2'