Using START DBE NEW [ ALLBASE/SQL Database Administration Guide ] MPE/iX 5.5 Documentation
ALLBASE/SQL Database Administration Guide
Using START DBE NEW
Configuring a DBEnvironment begins with using the START DBE NEW
statement, whether in interactive mode through ISQL, using ISQL command
files. Refer to the ALLBASE/ISQL Reference Manual for more information
on command files. START DBE NEW may be used only once for a given
DBEnvironment:
START DBE 'DBEnvironmentName' NEW [StartUp Values];
If you try to execute a START DBE NEW statement for an existing
DBEnvironment, ALLBASE/SQL returns an error. You must purge the existing
DBEnvironment and the log files associated with it using SQLUtil before
you can use START DBE NEW to create a DBEnvironment with the same name.
The START DBE NEW statement allows you to supply startup parameters,
which are used to set operating limits, such as the user mode and the
number of log buffers, each time the DBEnvironment is started. The
startup parameters are stored in a file called the DBECon file.
If no startup parameters are specified, ALLBASE/SQL provides default
values. Table 4-1 shows the startup parameters that are stored in
the DBECon file and their default values.
Table 4-1. DBECon Default Startup Parameters
---------------------------------------------------------------------------------------------
- Parameter - Default Startup Option -
---------------------------------------------------------------------------------------------
- DBECreator - your DBEUserID -
- Maintenance Word - none -
- DBEnvironment Language - NATIVE-3000 -
- AutoStart - ON -
- User Mode - SINGLE -
- DBEFile0 Name - DBEFILE0 -
- Log File Name(s) - DBELOG1 -
- - DBELOG2 * -
- Archive Mode - OFF -
- DDL Enabled - YES -
- Number of Run Time Control Block Pages - 37 -
- Number of Data Buffer Pages - 100 -
- Memory Resident Data Buffer Pages - NO -
- Number of Log Buffer Pages - 24 -
- Maximum Transactions - 2 -
- Maximum Timeout - None -
- Default Timeout - Maximum -
- Authorize once per session - OFF -
- - * for dual logging -
---------------------------------------------------------------------------------------------
You can override all these parameters except DBECreator, maintenance
word, autostart, archive mode, DDL Enabled, Memory Resident Data Buffer
Pages, and Authorize Once per Session by specifying other values in the
START DBE NEW statement.
After the DBEnvironment has been configured, you can change some startup
values using SQLUtil. See Table 7-1 in the "Maintenance" chapter for
a description of each parameter, including how it can be changed.
Supplying Startup Parameters with START DBE NEW
Your DBEnvironment may require startup parameters different from the
default values supplied by ALLBASE/SQL. For example, you may want to
create a multiuser DBEnvironment, or assign a more descriptive name to
DBEFile0.
The sample DBEnvironment was configured with the following statement:
isql=> 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';
Note that the ALLBASE/SQL defaults were used for the number of buffer
pages.
Figure 4-1 is a diagram of the PartsDBE DBEnvironment immediately
after configuration. The DBECon file is expanded to show startup
parameters and the log file directory.
Figure 4-1. The Sample DBEnvironment Immediately After Configuration
A newly configured DBEnvironment has the following elements:
* One DBECon file containing the DBEnvironment configuration
parameters. The name of the DBECon file is the same as the
DBEnvironment name.
* One or two log files. The system file name(s) are specified in
the NAME= clause of the LOG DBEFILE option of the START DBE NEW
statement. The default name(s) are DBELOG1 and DBELOG2.
* One DBEFileSet named SYSTEM.
* One DBEFile0 DBEFile to store the initial system catalog data.
The system file name is specified in the NAME= clause of the
DBEFILE0 DBEFILE option of the START DBE NEW statement. The
default name is DBEFILE0. This DBEFile is associated with the
SYSTEM DBEFileSet.
* One system catalog to store information about the DBEnvironment.
The tables, views, and indexes constituting the ALLBASE/SQL system
catalog (refer to the "System Catalog" chapter in this guide) are
created in the SYSTEM DBEFileSet. At first, entries in the system
catalog describe the initial state of the DBEnvironment, including
the pseudotables and views of the system catalog itself. As
objects are added, the system catalog is updated.
Refer to the "Maintenance" chapter for additional information about the
DBECon file parameters and how to change them.
Log Files
ALLBASE/SQL creates a log file when the DBEnvironment is configured. A
log file can be from 250 to 524,287 pages. Each page is 512 bytes. You
can set the size and name of the log file(s) using the LOG DBEFile option
of the START DBE NEW statement:
isql=> START DBE 'PartsDBE.SomeGrp.SomeAcct' MULTI NEW
> .
> .
> .
> LOG DBEFile PartsLg1
> WITH PAGES= 350,
> NAME= 'PartsLg1';
If you do not specify a file name or log size, ALLBASE/SQL creates a log
file with the default size of 250 pages and the default name of DBELOG1.
The file is created in the same group and account as that of the DBECon
file unless you specify a different group. The account name, if you
specify it, must be the same as that of the DBECon file. In the above
example, the name PartsLg1 was chosen for the sample DBEnvironment.
Refer to the "Physical Design" chapter of this guide for guidelines on
determining the size of the log file.
Dual Logging.
Successful recovery requires a good copy of each log record. Since a log
file is critical to the recovery procedure, ALLBASE/SQL provides dual
logging which improves the probability of successful recovery by
maintaining two log files.
You must specify two log file names when you specify dual logging:
isql=> START DBE 'PartsDBE.SomeGrp.SomeAcct' MULTI NEW
> DUAL LOG,
> LOG DBEFILE PartsDBELog1 AND PartsDBELog2
> WITH PAGES = 256,
> NAME = 'PartsLg1' AND 'PartsLg2';
The SQLUtil SHOWLOG command will display two log file names when dual
logging is in use.
A hard crash on a device containing a log file is potentially very
serious, since it reduces the chances of being able to recover the
DBEnvironment. Whenever you use dual logging, you can safeguard against
both log files being damaged by a hard crash by locating the two logs on
separate disks. Use the SQLUtil MOVELOG command to move log files to
different disks.
If you cannot afford the time to reissue transactions in case a media
failure corrupts your log file, use dual logging. When you use dual
logging, keep in mind that disk space use is doubled and performance may
be affected because the number of I/O operations is also doubled.
You can specify two archive log files or two nonarchive files, but you
cannot specify one of each with dual logging.
Archive Logging.
Once you have configured a DBEnvironment, you can convert to archive
logging in one of the following ways:
* If you have TurboSTORE software, do a complete online backup using
the SQLUtil STOREONLINE command. You use STOREONLINE after you
have loaded all the database tables and are ready to start using
the DBEnvironment in production.
* If you do not have TurboSTORE, stop the DBEnvironment, then
connect to the DBEnvironment in single user mode and use the
following SQL statements to initiate archive logging:
isql=> BEGIN ARCHIVE
isql=> COMMIT ARCHIVE
Exit from ISQL, then immediately use the SQLUtil STORE command to
create a backup of the DBEnvironment.
After you enable archive logging, you should add additional log files to
permit log switching, log backup, and reuse of logs. The next section
shows how to add log files; for complete information about managing logs,
refer to the "Backup and Recovery" chapter.
NOTE Once the DBEnvironment is running with archive logging, you must
use the START DBE NEWLOG statement to return to nonarchive logging.
Multiple Log Files.
Use the SQLUtil ADDLOG command to add additional log files to the
DBEnvironment for either nonarchive or archive logging. The following
example adds a second log file with 350 pages to the sample DBEnvironment
NewDBE:
>> addlog
DBEnvironment Name: NewDBE
Maintenance Word: Return
Enter Log File Name(s) Separated by a Blank? NewLg2
New Log File Size? 350
Add Log File (y/n)? y
Log file 'NewLg2' was Added.
Log Identifier Is: 2
NOTE ADDLOG adds a single log file at a time. In the case of dual
logging, two physical log files are added. You cannot add more
than one file in single logging mode. In dual logging mode, you
cannot add more than two or less than two files.
Specifying a Native Language Parameter
You can specify a native language parameter in creating a DBEnvironment.
Use the LANG = LanguageName option in the START DBE NEW statement to
specify a native language other than NATIVE-3000, as in the following
example:
START DBE 'SOMEDBE' NEW LANG = JAPANESE
If you want to specify the name of the DBEnvironment in a native
language, then the native language you specify in the LANG = clause must
be covered by the same character set as the current language. In other
cases, your current language can be different from that of the
DBEnvironment. All processing--including comparisons and sorting--will
take place in accordance with the language of the DBEnvironment, but
prompts and messages will appear in the current language if the
appropriate message catalog is available. Also, scanning of user input
will be in the current language. See "Native Language Support" in
Chapter 1 for information about specifying a native language as the
current language.
Looking at the DBEnvironment Elements
You can look at each of the elements created by the START DBE NEW
statement.
Examining MPE Files.
Use the MPE LISTF command to list all the files in the group and account
where your DBEnvironment resides. The DBEFiles, log files, and DBECon
file appear as privileged files:
: LISTF,2
ACCOUNT= SOMEACCT GROUP= SOMEGRP
FILENAME CODE ------------LOGICAL RECORD----------- ----SPACE----
SIZE TYP EOF LIMIT R/B SECTORS #X MX
PARTSDBE PRIV 125W FB 1 1 1 2 1 1
PARTSF0 PRIV 2048W FB 150 150 1 2416 5 5
PARTSLG1 PRIV 256W FB 256 256 1 514 2 2
PARTSLG2 PRIV 256W FB 256 256 1 514 2 2
Examining DBECon Parameters.
Run SQLUtil from MPE/iX or from ISQL to look at the startup parameters in
the DBECon file, as in the following example:
isql=> sqlutil; Return
MON, JAN 6, 1992, 11:11 AM
HP36216-02A.F0.08 DBE Utility/3000 ALLBASE/SQL
(C)COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988,
1989,1990,1991,1992. ALL RIGHTS RESERVED.
>> SHOWDBE Return
DBEnvironment Name: PartsDBE Return
Maintenance Word: Return
Output File Name (opt): Return
-> all Return
Maintenance word:
DBEnvironment Language: NATIVE-3000
DBECreator ID: 2204
AutoStart: ON
User Mode: MULTI
DBEFile0 Name: PARTSF0
DDL Enabled: YES
No. of Runtime Control Block Pages: 37
No. of Data Buffer Pages: 100
Data Buffer Pages Memory Resident: NO
No. of Log Buffer Pages: 24
Max. Transactions: 5
Maximum Timeout: NONE
Default Timeout: MAXIMUM
Authorize Once per session: OFF
->
The parameters are displayed as they appear in the DBECon file
illustration in Table 4-1 . For more information on SQLUtil, refer to
the "DBA Tasks and Tools" chapter and the "SQLUtil" appendix.
Examining the System Catalog.
The system catalog is a set of tables and views owned by special users
CATALOG and SYSTEM that describe the contents of a DBEnvironment. You
must be connected to a DBEnvironment and have SELECT authority or DBA
authority in order to query the SYSTEM views. Users without DBA
authority can examine the CATALOG views to see information about the
objects they own. As DBA, you can also grant or revoke SELECT authority
on SYSTEM views. You can query the system catalog views to look at the
initial DBEFileSet, DBEFile, and system views created when a
DBEnvironment is configured. You can also monitor space requirements,
user access, and performance, and generally keep track of what is in the
DBEnvironment. Some of the information contained in the system catalog
can also be examined with SQLMON, an online monitoring tool. SQLMON is
described in the ALLBASE/SQL Performance and Monitoring Guidelines.
A simple SELECT statement shows you all the system catalog views:
select name, owner, type, rtype, numc from system.table where owner = 'SYSTEM';
--------------------+--------------------+------+------+-----------
NAME |OWNER |TYPE |RTYPE |NUMC
--------------------+--------------------+------+------+-----------
ACCOUNT |SYSTEM | 0| 3| 6
CALL |SYSTEM | 0| 3| 5
CHECKDEF |SYSTEM | 1| 0| 6
COLAUTH |SYSTEM | 1| 0| 7
COLDEFAULT |SYSTEM | 1| 0| 6
COLUMN |SYSTEM | 1| 0| 13
CONSTRAINT |SYSTEM | 1| 0| 8
CONSTRAINTCOL |SYSTEM | 1| 0| 4
CONSTRAINTINDEX |SYSTEM | 1| 0| 11
COUNTER |SYSTEM | 0| 3| 3
DBEFILE |SYSTEM | 1| 0| 10
DBEFILESET |SYSTEM | 1| 0| 6
GROUP |SYSTEM | 1| 0| 4
HASH |SYSTEM | 1| 0| 11
INDEX |SYSTEM | 1| 0| 11
MODAUTH |SYSTEM | 1| 0| 3
PARAMDEFAULT |SYSTEM | 1| 0| 6
PARAMETER |SYSTEM | 1| 0| 12
PLAN |SYSTEM | 0| 3| 7
PROCAUTH |SYSTEM | 1| 0| 3
PROCEDURE |SYSTEM | 1| 0| 5
PROCEDUREDEF |SYSTEM | 1| 0| 6
RULE |SYSTEM | 1| 0| 10
RULECOLUMN |SYSTEM | 1| 0| 3
RULEDEF |SYSTEM | 1| 0| 6
SECTION |SYSTEM | 1| 0| 8
SPECAUTH |SYSTEM | 1| 0| 4
TABAUTH |SYSTEM | 1| 0| 14
TABLE |SYSTEM | 1| 0| 15
TEMPSPACE |SYSTEM | 1| 0| 4
TRANSACTION |SYSTEM | 0| 3| 4
USER |SYSTEM | 0| 3| 2
VIEWDEF |SYSTEM | 1| 0| 6
------------------------------------------------------------------------
Number of rows selected is 33
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>
To look at the DBEFile0 DBEFile created at configuration time, query the
SYSTEM.DBEFILE view using the following statement:
isql=> SELECT * FROM System.DBEFile;
SELECT * FROM System.DBEFile;
--------------------+--------+------------------------------------------
DBEFNAME |DBEFTYPE|FILEID
--------------------+--------+------------------------------------------
PARTSDBE0 | 90|PARTSF0
------------------------------------------------------------------------
Number of rows selected is 1
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>
Note that the DBEFile name stored in the system catalog is the name given
in the DBEFILE DBEFILE0 clause, and the FILE ID is the MPE/iX file name
given in the NAME clause of the START DBE NEW statement.
Refer to the "System Catalog" chapter for a complete description of each
view in the system catalog.
Examining Log File Characteristics.
Use the SQLUtil SHOWLOG command to display the characteristics of a newly
configured log. For example:
>> SHOWLOG
DBEnvironment Name: PartsDBE.SomeGrp.SomeAcct
Maintenance Word: Return
Connect (y/n) (opt): y
Archive Mode: OFF
Log Sequence Number Containing Most Recent Archive Checkpoint: 0
Current Log Sequence Number: 1
First Log Sequence Number Needed for Recovery: 0
Log Mode is: Dual
Number of Free Block(s): 340
First Log Name: PartsLG1.SomeGrp.SomeAcct
First Log File Status: Useable
First Log Name: PartsLG2.SomeGrp.SomeAcct
First Log File Status: Useable
Log File Size: 250
Log Identifier Is: 1
Log Sequence Number: 1
Log Backup Status: Backup Is Not Required
For an explanation of each parameter, refer to the description of the
SHOWLOG command in the "SQLUtil" appendix. See the "Backup and Recovery"
chapter for a complete explanation of how logging operates in
ALLBASE/SQL.
MPE/iX 5.5 Documentation