Home	Starting	Environment	DBMS	VisualPQL	Procedures	SQL	Forms	Host/API	Index

Backup

Backup and Recovery

SIR/XS provides utilities to write all or part of the database to external files in either machine dependent or machine independent formats and to re-input these files back to a SIR/XS database. These utilities can be used for restructuring the database, backing up the database, porting all or part of the database to another database on the same operating system or creating a machine independent version of the database to move to a different operating system.

Note: If transferring export or other text (machine independent) files between machines using ftp, you must use ftp in ACSII mode (not BINARY)

There are various procedures and the utilities to assist in protecting a database and recovering it in the case of problems.

The key to a successful recovery operation is being prepared. Do not assume that the computer, disk drives or power supply are always 100% trouble free. Be prepared for unexpected problems by taking regular backups, saving journals and, in general, take reasonable precautions against losing much time or work.

The procedure for restructuring a database is the same as for backing up and recovery.

There are utilities that are designed to work in pairs with one providing input in a suitable format for the other.

Some utilities create or use binary files that are specific to an operating system. These files are created in "append" mode; that is they are added to the end of any existing unload or subset file with the same name. However, if you run these through the menu system, you are given the choice of deleting the old file first.

Other utilities create text files that are machine independent and can be viewed and updated by any text editor. These are produced independently and overwrite existing files.

EXPORT creates a file containing a text copy of the database, including the data dictionary, procedures and data. This can be used by IMPORT to create a new database on a different machine.

WRITE SCHEMA writes just the database definition in a similar format to export.

SUBSET writes a subset of the database to an unload binary file. This includes the schema and data for selected record types. This can be used by MERGE to combine with an existing database or RELOAD to create a new database that is a subset of the original.

UNLOAD writes a copy of the database to an unload binary file including the schema and the procedures.

UPLOAD creates a text copy from the journal of all updates that have been done. This can be used by DOWNLOAD to apply those changes to a second copy of the database. An example of this might be a central database with subsets on various PCs in remote locations. Updates might be done on the PCs and UPLOADed to the center, or updates might be done in the center and UPLOAD to the PCs to avoid re-transmitting the whole of the database.

There are two utilities that check on the contents of the system.

VERIFY checks on the contents of the structure of the database and gives details of any problems discovered. This utility has a PATCH option that recovers from many types of corruptions. If some type of problem has occurred, a SIR/XS process may warn that a corruption has occurred. If this happens, use the VERIFY FILE utility to find out more about the problem, and attempt to correct it.
ITEMIZE reports on the contents of either the journal file or files produced by UNLOAD or SUBSET.

LIST STATS reports on the current status of the database giving the number of records, data size and update level.

Journaling

Journaling can be turned "ON" for a database. This means that, each time that the database is updated (i.e. the update level increases), an entry is written to the journal file. The entry contains details of all of the updates done to take the database from one update level to the next. Each entry on the journal file consists of images of all of the data records that were updated during the update run. The images can be both before and after images of the records depending on the update. For a new record, there is an after image; for an updated record there is a before and after image; for a deleted record, there is a before image.

The journal can be used to recover in the case of an unexpected interruption in an update run and allows updates to be re-applied quickly and easily if a backup has to be restored. The journal can be used in VisualPQL to produce reports on updates or other audit trails.

If updates were incomplete or unsuccessful in some way, they can be 'undone' with a JOURNAL ROLLBACK that takes a database back to a previous update level.
When a database is connected, its status is checked to see if it was not closed properly when being updated e.g. the system 'crashed' while the database was open for update. If this is found to be the case, you are asked if you wish to recover automatically. If you choose to try to recover, a journal rollback is done.
If a database has to be recovered from backups, restore the database from the backup and then use JOURNAL RESTORE to bring that version of the database up to the level of the journal file.

Binary Files

Binary files are machine dependant and are NOT suitable for transferring between operating systems or different hardware. These are NOT suitable for long term archival storage. Subsequent versions of SIR may have updated file formats that are incompatible. The following utilities create binary files

A full database is created by UNLOAD FILE and read by RELOAD FILE
A partial database is created by SIR SUBSET and read by MERGE
Database changes are created by JOURNAL and read by JOURNAL RESTORE
A binary file can contain multiple sets of data. These are listed by ITEMIZE.

Text Files

Text files are suitable for transferring between machines and can be viewed with a normal editor. The following utilities create text files.

A full or partial database is created by EXPORT and read by IMPORT. This is the recommended format for storing all long term SIR archives. It is machine and operating system independent and subsequent versions of SIR are always compatible with previous EXPORT formats.
Database definitions are written by WRITE SCHEMA
Database changes are created by UPLOAD and read by DOWNLOAD

IMPORT

To import and recreate a complete database, simply tell SIR/XS to read the export file generated by a previous EXPORT utility. This file is a text copy of a database consisting of commands to recreate the database and data to load into it.

This can be done in a number of ways:

From the Database\Recover\Import menu
By running the system in batch with the import file named as the IN = parameter
By "running" the import file from the Procedures - File menu

There is a command "IMPORT" that indicates that data, in a suitable format for importing, follows the command. eg:

IMPORT
0/1/1/2/4/1/12/John D Jones1/1/11/772-21-1321129754/1/M1/5/2150/145851/2/4/
145120/1500/1/2/5/145733/2000/1/3/4/145241/5/1650/2/3/4/145180/4/1600/2/3/5/
145851/5/2150/2/3/5/145794/4/2100/2/0/2/1/1/3/1/25/James A Arblaster        1/1/
...

EXPORT

EXPORT FILENAME  = fileid
     [BOOLEAN  = (logical_expr)]
     [COUNT    = total [,increment [,start]]]
     [LIST     = caseid, ...]
     [RECTYPES = ALL | rectype(logical_expr) ...]
     [SAMPLE   = fraction [,seed]]
     [DATABASE = new database name]
     [PASSWORD = new database password]
     [COMMON]
     [CSV]
     [ENCRYPT]
     [NODATA]
     [NOINDEX]
     [NOMAXKEY]
     [NOPASSWORDS]
     [NOPROCS]
     [NOTO]
     [NOTXS]
     [VARLIST]
     [VARSEQ]

Creates a file of text records containing the data dictionary, procedures and data from the database. Exports all non-compiled procedures, i.e members with a :T, :M or :P suffix (text, menu and picture (template) members). Compiled procedures (:E, :O and :V) are not machine independent and cannot be exported. Compiled procedures must be re-compiled after an IMPORT.

Database administrator security is required to use EXPORT. If the export does not run because record types in the database are locked (due to schema modifications) then restructure the database before rerunning the export.

Optionally separate multiple parameters on the command with slashes.

FILENAME

Specifies the name of the file to contain the exported database. This is required.

BOOLEAN

Specifies tests applied to cases before the case is written to the export file. A case is only written if the expression is TRUE. Only use common variables in the expression and only use for databases with a case structure. The test is applied after any SAMPLE, COUNT or LIST. If the case id is a categorical, date or time variable, specify either a string or numeric test and the equivalent variable value is used. VisualPQL functions can be used. For example:

BOOLEAN = (ID GT 5)

LIST

Exports only the specified cases. Specify the keyword THRU to select a range. For example:

LIST = 12 THRU 29, 33, 37

RECTYPES

Selects the data records written to the export file. Schema definitions are written for all record types, regardless of which rectypes are selected on the RECTYPES clause. If this clause is omitted, data for all record types in the database are exported.

Specify a logical expression to select records. Records that meet the criteria of the logical expression are selected. For example:

RECTYPES = EMPLOYEE (SALARY GT 2000)

SAMPLE

Exports a random sample of cases from the database. Fraction specifies the sample size (a decimal number) for selection. Seed specifies the starting seed for the random number generator. If seed is not specified, a default seed is used. For example, SAMPLE = .25 exports 25% of the cases from the database using a default seed.

COUNT

Exports a subset of cases from the database. Total is the number of cases to retrieve. Increment is a number that specifies the "skipping factor" for retrieving cases. For example, an increment of 3 produces every third case. Start specifies the first case processed. For example, a start of 3 starts with the third case.

DATABASE

Specifies a new name for the database on the export file. If this clause is omitted, the current database name is used.

PASSWORD

Changes the database password on the export file. If this clause is omitted, the current password is used.

COMMON

The record definition for the CIR includes definitions for all the common variables and so these do not need to be repeated on subsequent record types that include a common variable. Specify COMMON if commands are to be produced for each common variable on every record where it occurs.

CSV

Specifies that the data are written as comma separated values. This will be recognised on import. This format could be useful in debugging or for reading data using other software.

ENCRYPT

Specifies that the string data and passwords are written in encrypted format. The data are first encrypted then written to the export file in hexadecimal notation.

NODATA

Specifies that no data records are written to the export file.

NOINDEX

Specifies that no specifications for secondary indexes are written to the export file if transferring from SIR/XS or later to earlier versions that did not support indexes.

NOMAXKEY

Suppresses the writing of a specific MAX KEY command to the output file. Specify this option whenever schema modifications are being made to allow the system to recalculate the maximum key size unless a larger key is required for future expansion.

NOPASSWORDS

Specifies that the database password, security passwords and member passwords are not written to the export file. This has the effect of removing all password protection from the new database. New passwords can be assigned once the new database has been imported to its new location and recreated.

NOPROCS

Specifies that the procedures are not written to the export file.

NOTO

Where contiguous record variables share the same specification, a shorthand specification is written using the keyword TO. e.g.

MISSING VALUES  NAME
            TO  NDEPENDS                         (BLANK)

The NOTO keyword suppresses this shorthand and all variables are individually listed within the specification.

NOTXS

Specifies that the export is as compatible as possible for use by earlier versions of SIR (2002). New features such as STANDARD VARS and RECORD SCHEMA 0 are not written and slashes are generated as separators.

VARLIST

The standard style of schema output is to write variable names and input definitions as a DATA LIST. VARLIST specifies that variable names are written as a VARIABLE LIST command followed by input definitions as an INPUT FORMAT command. e.g.

VARIABLE LIST ID NAME GENDER MARSTAT ...
INPUT FORMAT (I4,T6,A25,I1,I1,...

VARSEQ

The standard style of schema output is to write each definition command once and to list variables within the command. The VARSEQ keyword specifies that the output is in variable sequence and that all commands that apply to a single variable are grouped together. e.g.

VAR LABEL       NAME                             'Name'
MISSING VALUES  NAME                             (BLANK)

VAR LABEL       GENDER                           'Gender'
VAR RANGES      GENDER                           (1 2)
MISSING VALUES  GENDER                           (BLANK)
VALUE LABELS    GENDER                           (1)'Male'
                                                 (2)'Female'

VAR LABEL       MARSTAT                          'Marital status'
VAR RANGES      MARSTAT                          (1 2)
MISSING VALUES  MARSTAT                          (BLANK)
VALUE LABELS    MARSTAT                          (1)'Married'
                                                 (2)'Not married'

Examples:
To export the entire database:

EXPORT FILENAME = 'COMPANY.EXP'

To export record types 5, 6, and 8 of the first 1000 cases:

EXPORT  FILENAME= 'SUBSET.EXP'
        RECTYPES= 5 6 8
        COUNT= 1000

The export procedure writes out a number of messages. These note the beginning and end of various stages of the export (Begin export of schema/Export of schema complete, Begin export of procedures/etc.).

Export writes a summary of the data records exported. This lists the number of cases, each record type exported and the number exported.

SIR SUBSET

SIR SUBSET FILENAME = filename
        [ BOOLEAN  = (logical expression)]
        [ LIST     = caseid list]
        [ RECTYPES = rectype [(logical expression)] ...]
        [ SAMPLE   = fraction [,seed]]
        [ COUNT    = total [,increment [,start]]]
        [ DATABASE = newdbname]

Creates a subset of a database. The subset file is a binary file in identical format to an unload. The schema and procedures are written in their entirety. Only the data that meets the criteria is subset. Database administrator security is required to use this utility.

FILENAME

Specifies the name of the output file. If this file already exists, the new subset is appended to the end of the file provided that the file is a valid unload/subset file for this database. If the file has multiple database copies, use the ITEMIZE FILE utility to determine the copies that are there.

BOOLEAN

Specifies conditions based on the values of common variables. BOOLEAN is applied after SAMPLE and COUNT. For example:

SIR SUBSET FILENAME = 'SUBSET.UNL'
            BOOLEAN =  (ID GT 5)

LIST

Subsets the specified cases. Enclose case ids that are character strings in quotes. Cannot be used with SAMPLE or COUNT. For example:

SIR SUBSET FILENAME = 'SUBSET.UNL'
    LIST= 1,3,5 thru 10

RECTYPES

Specifies the set of record types to copy. Specify a logical expression to select on data values. The expression may use common variables and variables in the rectype specified. For example:

SIR SUBSET FILENAME = 'SUBSET.UNL'
    RECTYPES = 1 (SALARY GT 2000),3

SAMPLE

Selects a random sample of cases from the database. Fraction specifies the sample size for selection. Seed specifies the starting seed for the random number generator. If seed is not specified, the default is used. Cannot be used with COUNT or LIST.

COUNT

Selects a specified number of cases from the database. Total specifies the number of cases to retrieve. Increment specifies the "skipping factor" for retrieving cases. For example, an increment of 3 produces every third case. Start specifies the ordinal of the first case processed. For example, a start value of 3 begins the processing at the third case. Cannot be used with SAMPLE or LIST

DATABASE

Specifies the name of the new subset database. The subset database password is the same as the password for the original database. For example:

SIR SUBSET FILENAME = 'SUBSET.UNL'
    DATABASE = TESTDBMS

UNLOAD FILE

UNLOAD FILE  FILENAME = filename
           [JOURNAL  = KEEP | PURGE]
           [NEWDB    = newname]
           [NEWPW    = newpassword]

Creates a machine dependent copy of the database. UNLOAD is used for backup and restructuring. Database administrator security is required to use this utility.

Use the UNLOAD FILE utility to back up the database. The old journal file can be deleted once an unload file is produced. A database may be recovered from an unload file plus any journals from the point the unload was done. Make sure that there is either a journal file that covers the entire history of the database, or an unload file and a journal file that covers modifications made to the database since the unload. The suggested procedure is:

Always have journaling ON for the database.
Backup the database with UNLOAD FILE on a regular basis.
It is good practice to run a complete VERIFY FILE before doing an unload)
After a successful backup has been made, copy the backup file to secure external media.

At this point, previous unload files and journal files can be renamed or deleted.

The options and keywords are:

FILENAME

Specifies the name of the output file. If the output file already exists as an unload file for this database, the utility adds the latest output to the end of the file. Use ITEMIZE FILE to see what is on the output file. If multiple copies of a database are on one physical file, specify the file number or update level to restore the correct copy of the database. For example:

UNLOAD FILE FILENAME = 'COMPANY.UNL'

JOURNAL

KEEP is the default and specifies that the current journal file is retained.
PURGE specifies that the current journal file is deleted when the unload run is completed. Journaling then starts on a new file.

NEWDB

Specifies a database name for the database copy. By default, the name of the database is used.

NEWPW

Specifies a new database password for the database copy. By default, the current password is used. For example

UNLOAD FILE FILENAME  = 'COMPANY.UNL'
            NEWDB  = TESTDBMS
            NEWPW  = TESTPASS

UPLOAD

UPLOAD FILENAME= filename
     [JOURNAL  = filename]
     [UPDATE   = update level [THRU update_level]]
     [RECTYPES = ALL | rectype (variable_list), ...]
     [TITLE    = 'upload_file_title']

Reads a journal file and outputs all the journaled changes to a file. This file is a text file so that it can be transferred to another machine. The DOWNLOAD utility reads the file produced by UPLOAD and applies the changes to the new database. Database administrator security is required to use this utility.

FILENAME

Specifies the name of the output file. This is required.

JOURNAL

Specifies the journal file. If the journal file has a different name, specify the name used. The current journal file, (database file 5), is the default.

UPDATE

Specifies update levels or date/time stamps to upload from the journal file. Specify a specific update level, update date, a range of update levels or a range of update dates. The default is the most recent, single set of updates on the journal file.

If this is specified, a report is produced showing each update level that is written to the upload file. For example:

UPLOAD  FILENAME = 'JOURNAL.UPL'
        UPDATE= 10 THRU 30

RECTYPES

Selects rectypes to upload. A variable list specifies individual variables. If the variable list is omitted, all variables are processed. The keyword CIR selects the common information record variables. ALL selects all record types, including CIR and is the default. For example:

UPLOAD  FILENAME = 'JOURNAL.UPL'
        RECTYPES= 1,3

TITLE

Specifies the title of the upload file. This is written as the first line of the file and is used to identify the file. DOWNLOAD prints this title in the summary report. This title may be up to 45 characters and is enclosed in quotes. For example:

UPLOAD FILENAME = 'JOURNAL.UPL'
       TITLE= 'Department 3 Changes'

ITEMIZE FILE

ITEMIZE FILE   [FILENAME= fileid]

Reports on the contents of a unload and journal files. An unload file may contain multiple unloads taken at different update levels. A journal file typically has journals from multiple update levels. This information is necessary when restoring a database or applying journals.

The options on the command are:

FILENAME: Specifies the name of the binary file. The default is the journal (fifth database file).

The report produced is similar to the following:

Itemize File 'C:\sir2004\alpha\COMPANY.sr5' is a JOURNAL file for database COMPANY
Update level:    1 -    2  Dec 08, 2005/10:46:13 Journal data    to Dec 08, 2005/10:47:07
Update level:    2 -    3  Dec 08, 2005/10:48:07 Journal data    to Dec 08, 2005/10:49:03

Itemize File 'C:\sir2004\alpha\COMPANY.unl' is an UNLOAD file for database COMPANY
Update level:           2  Dec 02, 2005/13:08:17 Unload schema  Record:     1
Update level:           2  Dec 02, 2005/13:08:17 Unload data    Record:     2
Update level:           3  Dec 08, 2005/10:49:03 Unload schema  Record:     3
Update level:           3  Dec 08, 2005/10:49:03 Unload data    Record:     4

The information reported is the name of the file, the type of file and the database that the file refers to. This is then followed by a list of the records on the file. Each entry has the following information:

UPDATE LEVEL: The update level is a sequential number incremented each time the database is updated. On a journal, it is the update level from - to where these are always one different and the 'to' is the update level that resulted after the update run. Journals are expected to be contiguous and a warning is given if any update levels are missing. On an Unload, it is the current update level at the time of the unload.
DATE & TIME: The date that the update was done followed by the time the update was done. On an unload, this is not when the unload was done but rather the date and time of the last update that resulted in that update level on the database.
TYPE OF RECORD: The record may by a journal of a schema or a data update or may be an unload for schema or for data.
Record Number: Each record on the journal or unload is assigned a sequential number. When specifying processing on the file, the unload/journal to be processed can be selected with either the update level or the record number.

LIST STATS

LIST STATS

Provides a status report about the database similar to the following:

Statistics for  COMPANY
Database name                          COMPANY

Creation Date/Time                     Dec 06, 2005      10:46:12
Last update Date/Time                  Dec 08, 2005      10:49:03
Update level                           3

Average Records per Case               1023
Max/Current Number of Cases            1000/20
Max/Current Number of Records          1023000/114

Max/Current Number of Record Types     30/3
Maximum Input Columns/Lines            80/1
Rectype Column                         5
Journal For Database                   ON
Case Id Variable                       ID                               (A)

Number of Index Levels                 2
Max Entries Per Index Block            509
Index/Data Block Size                  1019/1019
Active/Inactive Data Blocks            2/0
Active/Inactive Index Blocks           2/0

Keysize In Bytes                       8
Min/Max Record Size                    1/8
Number of Temporary Variables          0
Maximum Number of Data Variables       10

Record Record                            Number of  Maximum   Total In  Size In  Entry Use
 No.   Name                              Variables  Per Case  Database  Words     Count
----   --------------------------------  ---------  --------  --------  -------  ---------
   0   CIR                                       1         1        20        5          1
   1   EMPLOYEE                                 10         1        20        8          1
   2   OCCUP                                     4       100        30        1          1
   3   REVIEW                                    5       100        64        2          1

Secondary Indexes
Index Name                              Record                           Variables
--------------------------------        -------------------------------- --------------------------------
NAME                                    EMPLOYEE                         NAME ASC
BIRTHDAY                                EMPLOYEE                         BIRTHDAY ASC
EDUC                                    EMPLOYEE                         EDUC ASC
                                                                         GENDER ASC
EDUCID                                  EMPLOYEE                         EDUC ASC
                                                                         GENDER ASC
                                                                         ID ASC

The information includes:

A) Overall Database Information

database name
current update level
date of creation, last update and last access
the current and maximum number of cases
the current and maximum number of records
the maximum key size
the minimum and maximum record size
the total number of common variables
size of the CIR

B) Information about each Record Type

number of variables
maximum records per case
total currently in the database
length of the record
number of times the record schema has been defined

C) Restructure Information (if any)

number of original variables
number of restructured variables
update level for restructured records

D) Secondary index information (if any)

name of each index
record indexed
variables indexed and whether Ascending/Descending and if Upper case

JOURNAL RESTORE

JOURNAL RESTORE   [FILENAME = fileid]
[FROM = n]
[THRU = n] | [COUNT = n]
[NEXT]

Applies journal files to a database to update it to a more current level. Any schema changes are applied as well as updates to the data. The process expects that the database has been recovered from a backup and, by default, looks for journal records that correspond to updates starting at the current update level on the database. It then applies all journals forward from that point to arrive at the most up to date database possible from that journal.

Update level information may be obtained by LIST STATS and ITEMIZE FILE. The update level listed for journals is the level the database was at after the update was originally done. So, for example, if the restored database is at level 40, the first journal to be applied would be update level 41.

JOURNAL RESTORE can restore partial journal records from abrupt interruptions of journaled update sessions. If a premature End-of-Record condition is encountered, the database is restored to a useable (non-corrupt) state, with as much data intact as possible. However if a logical set of updates were being done and were interrupted, data may be in an inconsistent state between records. It is recommended that a VERIFY FILE is done after a journal has been restored.

The options on the command are:

FILENAME

Specifies the name of the file that contains the journal. The default journal file is database file .sr5. For example:

JOURNAL RESTORE  FILENAME = 'COMPANY.JNL'

FROM

Specifies that when journal entries are applied, instead of starting from the current database level, they start from the specified level. This may be higher or lower than the current database level. Specify the starting update level which is one less than the first journal to be applied.

NEXT

Specifies that one journal entry is applied to the database to take it to the next update level.

THRU

Specifies that journal entries going up to and including the one at update level "n" are applied to the database. UPDATE is a synonym. For example:

JOURNAL RESTORE  FILENAME = 'COMPANY.JNL'
        THRU = 42

COUNT

Specifies that journal entries on the file from the start including the "nth" specified on the count are applied to the database. This is an alternative to specifying update level, which is the recommended approach. Do not specify both options. For example:

JOURNAL RESTORE  FILENAME = 'COMPANY.JNL'
        COUNT = 10

JOURNAL ROLLBACK

JOURNAL ROLLBACK   [FILENAME = fileid]    [UPDATE = n]  [COUNT = n]

Applies journal files to a database to undo updates and roll it back to a previous level. Only applies to data updates.

If a database update run is interrupted, this might be used to roll back to a known update level before re-running the update process

JOURNAL ROLLBACK can restore partial journal records from abrupt interruptions of journaled update sessions. It is recommended that a VERIFY FILE is done after a journal has been rolled back.

The options on the command are:

FILENAME

Specifies the name of the file that contains the journal. The default journal file is database file .sr5. For example:

JOURNAL ROLLBACK FILENAME = 'COMPANY.JNL'

UPDATE

Specifies that the database is rolled back to this update level. If no update level is specified, it is expected that the database update run was interrupted and that the update level was not changed. This means that only journal records that were created as part of the last, interrupted run are rolled back. The database remains at its current update level and, after the rollback, should be in the same state as when the aborted run started. This would normally be what was wanted. Update level information may be obtained by LIST STATS and ITEMIZE FILE.

COUNT

Specifies that all journal entries on the file, starting at the last and including the "nth" specified on the count, are rolled back and so 'undone'. This is an alternative to specifying update level, which is the recommended approach. Do not specify both options.

VERIFY FILE

VERIFY FILE [ALL]
       [CIRKEY]
       [CIRDATA]
       [CHECK]
       [CCF]
       [RECKEY]
       [RECDATA]
       [RCF]
       [COUNT= total,increment,start]
       [PATCH]
       [STATUS= global_name]

VERIFY FILE examines the database files for damage and corrects errors where possible. DBA-level security clearance is needed if any keywords are specified, since potentially secure data might be revealed.

The corruption flag is set when any errors are detected in the database. It is cleared when the database is verified and found to contain no errors.

The keywords control the amount of checking and the amount of output generated when verifying each data record. The error message number is followed by a character that signals the type of error message: I for Informative, N for Non-correctable, C for Correctable, F for Fatal. The loading factors are printed with 2 decimal digits. Errors are listed by type with informative messages as appropriate.

ALL: Selects all the options. Use this option carefully since the output generated is voluminous. (Not an option on the menu system but equivalent to selecting all options.)

STATUS

Puts a status code into the named global variable. The codes are:
0 = Database is OK;
1 = Verify produced warning messages;
2 = Verify shows correctable errors;
3 = Verify shows non-correctable errors.

VERIFY FILE Error Codes

The following error messages show the types of problems detected. Most of these errors are "major", and if any of them occur, the data file is probably unsaveable. (For an explanation of the structure of the database, what is a PRU, etc., please see Tuning and Efficiency.)

01 index pru out of range.: 1 index level
2 index pru that is out of range
3 index pru in error
02 index entry count error. Printed if the header contains an illegal entry count.: 1 index level
2 index entry found
3 index pru in error
03 index entry count mismatch. Printed if total records below count does not match upper level index count.: 1 index level
2 index count calculated
3 index count in upper level
4 index pru in error
04 db pru out of range.: 1 illegal pru ordinal
2 db pru in error
05 db entry count mismatch. Printed if the entry count in the header does not match the db.: 1 db entry found
2 db entry in header
3 db pru in error
06 db size (words used) mismatch. Printed if the number of words used in the header does not match the db.: 1 db used found
2 db used in header
3 db pru in error
07 CIR record count mismatch. Printed on completion of case, if the correct number of records for each record type is in error.: 1 case data file ord:case ord (last case)
2 rectype
3 mismatch (- if too many, + if not enough)
08 CIR record count limit error. Printed if a record count field exceeds some limit.: 1 case data file ord:case ord (last case)
2 rectype
3 count field in error
4 legal max record count for rectype
09 CIR record count exceeded. Printed for any record detected that exceeds the CIR record count field.: 1 case data file ord:case ord (last case)
2 record ordinal
3 rectype
10 record locked. Informational only, not an error per se.: 1 case data file ord:case ord (last case)
2 record ordinal
3 rectype
11 wrong length CIR.: 1 case data file ord:case ord (last case)
2 incirn detected
3 incirn
4 db pru in error
12 wrong length data record.: 1 case data file ord:case ord (last case)
2 record ordinal
3 recdrn detected
4 recdrn
5 db pru in error
13 illegal rectype encountered.: 1 case data file ord:case ord (last case)
2 record ordinal
3 rectype detected
4 db pru in error
14 rectype record total mismatch. Printed if at end of run the number of records for a given rectype is in error: 1 rectype
2 reccnt detected
3 reccnt
15 database record total mismatch. Printed if at end of run there is a record total mismatch: 1 dinrec detected
2 dinrec
16 case total error. Printed if at end of run total number of cases found does not equal count.: 1 dincas detected
2 dincas
17 Record or Case limit exceeded.: 1 record of case limit reached
2 master index overflow
3 data file is full
18, 19 used ind block error. Printed if number of ind or db blocks read is in error: 1 number detected
2 number should be
20 data error: missing error
21 data error: range error
22 data error: catint error
23 data error: valid error
24 Master Index is Full. If this occurs, the capacity of the database has been reached. Possible solutions are to increase PRU size or to decrease maximum key size.
25 index key out of order. Current key in an index block is not greater than the previous key: 1 index block pru
2 index block level
3 case data file ord:case ord (last case)
4 record ordinal
26 data key out of order. Current key in the data block is not greater than the previous key: 1 data block pru
2 case data file ord:case ord (last case)
3 record ordinal
27 non-matching index block keys. First key in index block does not match key in higher level block pointing to it: 1 index block pru
2 index block level
3 case data file ord:case ord (last case)
4 record ordinal
28 non-matching data index block keys. First key in data block does not match key in index block pointing to it: 1 data block pru
2 case data file ord:case ord (last case)
3 record ordinal
29 overflow block has been used message. An overflow block is reserved when the database is created. If the database requires more space and cannot obtain it, it uses the overflow block to attempt to maintain database integrity.
30 Missing CIR: case id changed but no CIR record.: 1 case data file ord:case ord (last case)
2 rec ordinal
3 rectype

Secondary Index verification messages

Any secondary indexes on the database are verified. If there is something wrong, the following error messages may be produced. All of these are serious errors and you need to drop and rebuild the index:

***ERROR - couldn't read index PRU - unable to read an index block from disk.
***ERROR - Zero index PRU - should have a block number but have zero.
***ERROR - couldn't read data PRU - unable to read a data block from disk.
***ERROR - Zero data PRU - should have a block number but have zero.
***ERROR - index key mismatch - as the various index levels were processed, a mismatch on the key was found.
***ERROR - data key mismatch - at the bottom level the data block pointed to by the index did not match on key
***ERROR - index count mismatch - as the various index levels were processed, a mismatch on the counts was found ***ERROR - data count mismatch - at the bottom level the data block pointed to by the index did not match on counts
If one of these errors occurs, supplementary information is printed including: LEVEL - The index level being processed
PRU - The block being referenced
ENTRIES - The number of entries
COUNT - The count of entries
CURRENT ENTRY - The entry being processed

DOWNLOAD

DOWNLOAD FILENAME=  filename
        [MESSAGES=  ON | OFF]

Reads the text file produced by UPLOAD from a journal and applies these changes to the database. Database administrator security is required to run this utility.

FILENAME

Specifies the name of the input file.

DOWNLOAD  FILENAME = 'JOURNAL.UPL'

MESSAGES

Specifies whether messages are issued. Messages include whether a record exists in the database that is marked as a new record on the upload file. By default, messages are off.

For example

DOWNLOAD FILENAME = 'JOURNAL.UPL' MESSAGES = ON

SIR MERGE

SIR MERGE FILENAME = input_file
          DATABASE = database [PASSWORD = password]
                              [SECURITY = read password]
          RECTYPES = ALL | source [:targetno,name] [(expression)]
         [BOOLEAN  = (log_expr)]
         [NODATA]
         [RENAME   = [source](source_list = target_list)]
         [UPDATE   = ADD | REPLACE]

Merges record types from a copy of one database (source) into an existing database (target) that is the database currently being used. The source is a binary file. The FILENAME, DATABASE, and PASSWORD, SECURITY clauses if required on this database, must appear before any other clauses. DBA write security for the target database is required to use this command. This utility is not available through the menu system.

If the record type is already defined in the target database schema, the source and target record type definitions must match exactly. If a new record type is being merged, the schema for the new record type is created containing everything from the source database schema definition except the IF, COMPUTE, RECODE, ACCEPT REC IF and REJECT REC IF statements.

If the target database is caseless, the case id and CIR's on the source database are ignored. A caseless source cannot be merged into a case structured target. (Use SIR SAVE FILE to create a case structured database from a caseless database.)

The options on this command are:

FILENAME

Specifies the name of the source binary file to merge.

DATABASE

Specifies the source database name.

PASSWORD

Specifies the source database password.

SECURITY

Specifies the read security password of the source database. The read password must be the DBA level password.

SIR MERGE  FILENAME  = 'COMPANY.UNL'
           DATABASE  = COMPANY
           PASSWORD  = COMPANY
           SECURITY  = HIGH

RECTYPES

Specifies the record types to merge. The CIR of the source database is merged if the variables in the target CIR match exactly.

ALL: Merges all source record types.
source: Merges the specified record types. The record type may be a name or number.
:targetno,name: Merges the source record types with the specified target record types. Do not leave any blanks between the colon : and the number. Specify both the number and name of the target record.
(expression): Specifies a logical expression to select records. This can reference both common variables and record variables from the source record type(s).

If the RENAME clause is used, specify the new name of the variable in this clause.

BOOLEAN

Specifies a logical expression referencing common variables to select cases. If the expression is TRUE the case is merged.

NODATA

Specifies that no data is merged. The schema for the specified (new) record type(s) is added.

RENAME

Specifies new names for variables merged from the source record types. Use if the source and target records have different names for the same variable or to change a variable name from the source name when a new record type is being created. RENAME does not change variable names on existing target records. Specify the RENAME= rectype (source variable list = target variable list) form when more than one record type is being merged. The rectype is the source rectype:

SIR MERGE ... RENAME = 1 (EMPNAME = NAME)

UPDATE

Specifies the action to take when the record identifiers on the source record match those of a record in the target database.

ADD: Specifies that only new records are created. If a source record has a key that matches an existing record on the target database, the source record is rejected and a message is issued.
REPLACE: Specifies that records are only replaced. If a source record has a key that does not match a record on the target database, the source record is rejected and a message is issued.
By default, both new records are added to the database and existing records are replaced.

RELOAD FILE

RELOAD FILE  dbname
       FILENAME =  fileid
       [PASSWORD = password]
       [SECURITY = rsec,wsec]
       [UPDATE = n | FILE= n]
       [LOADING = n]
       [NOFCASES = n]
       [AVGREC = n]
       [RESTART]

Recreates a database. The input is a binary file that is a copy of a database.

The reload database name and password must be the name and password of the database on the unload file. To change database names and passwords, specify the new name and password on the UNLOAD.

Optionally separate multiple parameters on the command with slashes.

FILENAME: Specifies the name of the binary file that contains the input. If there is more than one copy of a database on the file (which happens if the database is UNLOADed to the same file more than once), specify UPDATE= n or FILE= n to reload a copy other than the first.
PASSWORD: Specifies the database password. Must match the password of the unloaded database.
SECURITY: Specifies the read and write security of the database. Specify an asterisk '*' for a null security password.
UPDATE: Specifies the update level to reload if there are multiple copies of the database on the unload file. ITEMIZE FILE reports the update levels of multiple database copies on a file.
FILE: Specifies the file number of the database to reload if there are multiple copies of the database on the unload file.
LOADING: Specifies the fraction of each disk block to fill with data.
AVGREC: Specifies a new value for RECS PER CASE in the Case Schema definition for a case-structured database. The specified value is the average number of records per case.
NOFCASES: Specifies a new value for N OF CASES in the Case Schema definition for a case-structured database. The specified value is an upper limit on the number of cases in the reloaded database.
RESTART: Resets the database update level to 1. This is done automatically when the update level on the reloaded database would be greater than 32268.

Example:

RELOAD FILE MYDBMS
       FILENAME = 'COMPANY.UNL'
       UPDATE = 52