HPlogo ALLBASE/SQL Reference Manual: HP 9000 Computer Systems > Chapter 12 SQL Statements S - Z

START DBE NEW
» 

Technical documentation

Complete book in PDF
» Feedback

 » Table of Contents

 » Index

spacerspacerspacer
spacer
spacer
  		 
 

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 
    AUDIT}|...|LOG
   BUFFER = DataBufferPages, LogBufferPages) 
   LANG = LanguageName
   TRANSACTION = MaxTransactions
   MAXIMUM TIMEOUT = {TimeoutValue [SECONDS 
                                    MINUTES] 
                      NONE                   } 
   DEFAULT TIMEOUT = {TimeoutValue [SECONDS 
                                    MINUTES] 
                      MAXIMUM                } 
   RUN BLOCK = ControlBlockPages]
   DEFAULT PARTITION = {DefaultPartitionNumber
                        NONE                 }
   COMMENT PARTITION = {CommentPartitionNumber
                        DEFAULT
                        NONE                 }
   MAXPARTITIONS = MaximumNumberOfPartitions
   AUDIT NAME = 'AuditName'
   {COMMENT 
    DATA 
    DEFINITION 
    STORAGE 
    AUTHORIZATION 
    SECTION 
    ALL           }|...|AUDIT ELEMENTS
   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. Unless you specify an absolute path name, ALLBASE/SQL assumes the name is relative to your current working directory.

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. The default is 50. 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.

MaximumNumberOfPartitions

specifies 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'

By default, DBEFile0 resides in the same directory as the DBECon file. However, you can use the SQLUtil MOVEFILE command to move it to another directory.

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 directory 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 relative to the directory specified in the DBEnvironment name parameter unless an absolute path name is specified. 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 DBELog2ID

are the basic names identifying the log files.

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 SystemFileName3

identify how the logs are known to HP-UX. The logs are created relative to the directory specified in the DBEnvironment name parameter unless an absolute path name is specified.

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.

  • The files created with this statement are owned by hpdb and have the following permissions: 

       -rw------

  • 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 cannot use the START DBE NEW statement on a diskless machine. A DBEnvironment for use in diskless clusters must be created on the cluster server. Refer to the ALLBASE/NET User's Guide for further information.

  • 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. hpdb must have write permission in the target directory for all the files the START DBE NEW statement creates.

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 '../sampledb/PartsDBE' 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 '../sampledb/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'


START DBE
  		 


START DBE NEWLOG  
Feedback to webmaster