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

START DBE NEWLOG
» 

Technical documentation

Complete book in PDF
» Feedback

 » Table of Contents

 » Index

spacerspacerspacer
spacer
spacer
  		 
 

The START DBE NEWLOG statement establishes a connection with a given DBEnvironment and creates one or two new log files for that 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. Any start up parameters not explicitly specified are taken from the DBECon file except the enabling of audit logging. This statement reinitializes log file(s) when you need to change the size, invoke a dual logging or startup, or alter audit logging.

Scope

ISQL or Application Programs

SQL Syntax — START DBE NEWLOG

  START DBE 'DBEnvironmentName' [AS 'ConnectionName'][MULTI]NEWLOG
  [{ARCHIVE
    DUAL 
    AUDIT}|...|LOG
   BUFFER = (DataBufferPages, LogBufferPages) 
   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             ]|,...| NewLogDefinition

Parameters — START DBE NEWLOG

DBEnvironmentName

identifies the DBEnvironment in which you want to initialize one or two new log files. 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. This name 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 after log initialization in multiuser mode.

ARCHIVE

causes ALLBASE/SQL to initialize a new log in archive mode. If you omit this parameter, the log starts in nonarchive mode.

DUAL

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. Normally, only one log is read; but if an error is encountered, ALLBASE/SQL switches to the other log. Data integrity is maintained provided at least one good copy of each log record is on at least one of the logs.

AUDIT

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 log buffer pages to be used. You as 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.

MaxTransactions

specifies the maximum number of concurrent transactions to be supported. You can specify a value from 2 to 240. The default is 50. This value overrides the maximum value stored in the DBECon file. 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 one time, you should allow 2 concurrent transactions for just being connected to the DBE.

MAXIMUM TIMEOUT

specifies the maximum user timeout value. This value temporarily overrides the maximum stored in the DBECon file. When no value is specified, the DBECon file value is the default.

DEFAULT TIMEOUT

specifies the default user timeout value. This value temporarily overrides the maximum stored in the DBECon file. When no value is specified, the DBECon file value is the default.

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. Any value specified here temporarily overrides the value specified 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. DefaultPartitionNumber must be in the range 1 and 32767. If NONE is specified, tables assigned to the DEFAULT PARTITION do not generate audit log records. no tables in the DBEnvironment are prepared for audit logging and no operation done on these tables is logged. See the CREATE TABLE and ALTER TABLE statements for information on assigning a partition for a table.

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. 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 (except the DATA audit 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 for more partitions or audit elements.

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 follow:

COMMENT

This 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

This is the default element. It causes audit log records to be generated 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

This includes audit logging of the following statements: 

   CREATE TABLE
   ALTER TABLE
   DROP TABLE
   CREATE INDEX
   DROP 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

This includes audit logging of the following statements: 

   CREATE DBEFILE
   DROP DBEFILE
   ADD DBEFILE TO DBEFILESET
   REMOVE DBEFILE FROM DBEFILESET
   CREATE TEMPSPACE
   DROP TEMPSPACE
AUTHORIZATION

This includes audit logging of the following statements: 

   GRANT
   REVOKE
   ADD TO GROUP
   REMOVE FROM GROUP
SECTION

This includes audit logging of the creation and deletion of permanent sections. Permanent sections are created when a program is preprocessed, and are deleted by the DROP MODULE statement. Logging of section creation does not include any SETOPT information associated with the section. See the SETOPT statement in this chapter.

ALL

This is equivalent to specifying COMMENT DATA DEFINITION STORAGE AUTHORIZATION SECTION AUDIT ELEMENTS as described above.

NewLogDefinition

is a clause that provides ALLBASE/SQL with the information needed to create one or more new log files. The syntax for this clause is presented in the next section.

SQL Syntax — NewLogDefinition

   LOG DBEFILE DBELog1ID [AND DBELog2ID]
   WITH PAGES = DBELogSize, 
   NAME = 'SystemFileName1' [AND 'SystemFileName2']

Parameters — NewLogDefinition

LOG DBEFILE

describes the two log files if the DUAL LOG option is specified, or a single log file otherwise.

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.

SystemFileName1 and SystemFileName2

identify how the logs are known to the operating system. The logs are created relative to the DBECon file directory unless an absolute path name is specified. If a log file by the same name already exists, use SQLUtil to purge it before issuing the START DBE NEWLOG statement.

Description

  • The usual reason for using START DBE NEWLOG is to increase or decrease log file space or to invoke dual logging.

  • The logs are always created in the same group and account as the DBEnvironment.

  • When you choose an odd number of log pages using the WITH PAGES clause of the new log definition, the number is rounded up to an even number, which is displayed in SHOWLOG.

  • Do not use this statement unless you are certain that the preceding termination of ALLBASE/SQL was normal and all active sessions terminated normally. Before using the START DBE NEWLOG statement, it is recommended that you issue a START DBE statement in single user mode to ensure the DBEnvironment is in a consistent state before the existing log(s) are disassociated from the DBEnvironment.

  • Use the ARCHIVE option only as a part of a static backup procedure with archive logging. Refer to the "Backup and Recovery" chapter in the ALLBASE/SQL Database Administration Guide for more information. The preferred method for starting archive logging is to use the SQLUtil STOREONLINE command after initial loading of the DBEnvironment is complete.

  • No DBE sessions for the DBEnvironment can be in effect when this statement is processed.

  • Timeout values set in the START DBE NEWLOG statement remain in effect only as long as there is a DBEnvironment session connected to the DBEnvironment, and do not modify the values stored in the DBECon file.

  • If no MAXIMUM TIMEOUT limit is specified, the MAXIMUM TIMEOUT limit stored in the DBECon file remains in effect. If no DEFAULT TIMEOUT value is specified, the DEFAULT TIMEOUT value stored in the DBECon file remains in effect.

  • If MAXIMUM TIMEOUT = NONE, infinity (no timeout) is assumed. If DEFAULT TIMEOUT = MAXIMUM, the value of MAXIMUM TIMEOUT is assumed. The DEFAULT TIMEOUT value may not exceed the MAXIMUM TIMEOUT value.

  • 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

  • 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.

  • Use of the clause ALL AUDIT ELEMENTS implies specification of all of the audit elements.

  • The usual reason for using START DBE NEWLOG is to increase or decrease log file space, to invoke dual logging or audit logging, or to alter audit logging parameters.

  • Audit parameters set in the START DBE NEWLOG statement modify the values stored in the DBECon file.

  • If an audit parameter is not specified in the statement, the audit parameter remains unchanged. The parameters AUDIT NAME, DEFAULT PARTITION, MAXPARTITIONS, COMMENT PARTITION, and AUDIT ELEMENTS can be changed at any time through the START DBE NEWLOG statement.

  • If AUDIT LOG is not specified in this statement, the default is that it is disabled. Thus if the DBEnvironment had audit logging enabled and then specified a START DBE NEWLOG statement without AUDIT LOG, audit logging would then be disabled.

  • Changing MAXPARTITIONS on a START DBE NEWLOG prevents roll forward recovery through prior log files, since their structure will differ from the new logs' structure. The same is true of going from non-audit logs to audit logs and vice versa. Therefore, it is recommended that an audit DBEnvironment be designed with a large enough MAXPARTITIONS when it is created.

  • Additional log files should be created with the SQLUtil ADDLOG command.

  • Refer to the ALLBASE/SQL Database Administration Guide for additional information on log file management.

Authorization

You need to be the DBECreator or super-user to issue the START DBE NEWLOG statement. hpdb must have write permission in the target directory for the log file(s).

Example

The DBEnvironment is restored to a consistent state. Any transactions incomplete when the DBEnvironment was last shut down are rolled back, and work done by completed transactions is committed. 

   START DBE '../sampledb/PartsDBE'

SQLUtil is used to delete the existing log files: PartsLg1 and PartsLg2.

   STOP DBE
 
   The log files are reinitialized.
 
   START DBE '../sampledb/PartsDBE' MULTI NEWLOG DUAL LOG
             LOG DBEFILE PartsDBELog1 AND PartsDBELog2
               WITH PAGES = 250, NAME = 'PartsLg1' AND 'PartsLg2'

The DBEnvironment is restored to a consistent state. Any transactions incomplete when the DBEnvironment was last shut down are rolled back, and work done by completed transactions is committed. 

   START DBE '../sampledb/PartsDBE'
 
   STOP DBE

After the DBEnvironment is stopped, the log files can be purged.

New log files are reinitialized and audit logging is enabled. 

   START DBE '../sampledb/PartsDBE' MULTI NEWLOG DUAL 
             AUDIT LOG,
             AUDIT NAME = 'PrtsDBE1',
             DEFAULT PARTITION = 1,
             MAXPARTITIONS = 20,
             DATA AUDIT ELEMENTS,
             LOG DBEFILE PartsDBELog1 AND PartsDBELog2
               WITH PAGES = 1000, NAME = 'PartsLg1' AND 'PartsLg2'

You must create additional log files with the SQLUtil ADDLOG command.



START DBE NEW
  		 


STOP DBE  
Feedback to webmaster