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 “DBECon Default Startup Parameters” 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 ^[1]
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
^[1]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 “DBECon Parameters” in the "Maintenance" SECT 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 “The Sample DBEnvironment Immediately After Configuration” 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" SECT.




	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 “DBECon Default Startup Parameters”. 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.