
Database		Tabfiles and Tables

Tabfiles and Tables

A Table is a Relational Table (or flat file) which is a number of occurrences (from 0 to n) of a single type of record which has a number of variables (or columns). For example, a CUSTOMER table might have all of the customers with customer number, name, address and credit limit as variables. The individual variables that make up a table are defined including the variables name, format, data type, missing values and value labels. Tables can be created, defined, populated, modified and retrieved from.

Tables are physically held in Tabfiles. A Tabfile is a physical file on disk which contains relational data tables, schema definitions for those tables, indexes to the tables and system tables. A tabfile is independent of all other tabfiles and is independent from any SIR2002 database. A tabfile is the largest unit that exists for security and access control. A tabfile can hold multiple tables. Before accessing a table, the appropriate tabfile must be connected.

Tables from multiple tabfiles can be accessed and retrieved by SQL, VisualPQL and FORMS.

A SIR2002 session may be connected to multiple tabfiles at the same time. A default tabfile can be defined and this tabfile is used whenever a tabfile name is not specified. Whenever tables are referenced, the tabfile can be specified explicitly or the default can be used. Tabfiles can only be updated by one user at one time.

An Index is a way of accessing a table using the values of a particular variable as the key. Indexes can be defined on any variable or combination of variables. An index can be defined as only allowing unique values (for example Customer Number) or can have multiple entries for records all with the same value (for example Last Name). Indexes can be used to process tables randomly or in index sequence. If a table is processed without an index, it is retrieved sequentially in the order in which it was created. Once an index is defined, it is built from any existing data and is automatically maintained as the table is updated.

Tabfiles, tables and indexes may be defined in a number of ways using SQL, the VisualPQL procedure SAVE TABLE or the menus. In addition, there are specific SIR2002 commands which deal with tabfiles and tables. These are:

CONNECT TABFILE

CONNECT TABFILE tabfile  [ON filename]

Connects the specified tabfile. A tabfile must be connected before it can be used. A pre-compiled VisualPQL program can connect a tabfile when it runs, but, if you need to compile a VisualPQL program which references a tabfile, the tabfile must be connected first.

The ON clause identifies the physical file where the name of the physical file is not the internal tabfile name plus .tbf.

CREATE TABFILE

CREATE TABFILE   tabfile-name
      [FILENAME filename]
      [IDENT BY grpname [/grppass] [.username[/userpass]]]
      [JOURNAL filename]
      [BLOCKS n]

Creates a tabfile. The tabfile name is the name by which this is referenced in all other commands. This name is stored on the physical file and must be the same name used to CONNECT to this file in subsequent sessions. A tabfile is automatically connected after being created.

tabfile_name

The internal name of the tabfile as used in subsequent commands.

FILENAME

A filename for the tabfile. If this is not specified, the tabfile name plus a suffix of .tbf is used as the filename and must be a valid filename.

JOURNAL filename

Specifies that journalling is turned on and names the operating system file to use. If the journal file is not there when the tabfile is updated, a new journal is created, otherwise new journal data is added to the end of the file.

IDENT BY

Creates the initial security definitions for access to the tabfile.

Group name and password: Specify a group name who has DBA permission for the tabfile. If this is not specified, the tabfile is created with no security; this cannot be changed and no security can be assigned to any individual table on that tabfile. Optionally specify a group password
User name and password: Further restricts DBA access to a second level of name and optional password.

BLOCKS n

Specifies the number of blocks to create a physical block. In general do not specify this as the default is adequate. The default is 1 which gives an actual block size of 2k bytes. A specification of 2 gives 4k bytes and so on. The number must be a positive integer. A block must be able to hold the largest physical record.

CREATE INDEX

CREATE [UNIQUE] INDEX index-name
                ON [tabfile.]table (column [ASC|DESC], ...)
                [PCTFREE integer_value]

Creates an index for a table. An index provides direct access to a subset of records.

ON: Select the tabfile and table to create the index on.
index name: The name used to refer to the index. Must be unique on this table.
UNIQUE: Specifies that two rows cannot have the same index value. Rows with a value the same as an existing row are rejected. If an index is created for a table, and existing rows contain identical key values, then the index is not built and an error message is issued.
Columns: Specifies the column(s) comprising the index in major to minor sequence. For example: if (Sex, Name) is the index, this retrieves all Males by name, then all females by name. If (Name, Sex) is the index, everyone with the same name is retrieved together.
ASC | DESC specifies Ascending or Descending sequence for a particular variable. Ascending is the default.
PCTFREE: Specifies the percentage of free space to leave in the index blocks. This is used as new index entries are made. If the table is updated on a regular basis, take the 50% default. If the table is very static and the index is not updated, or is updated sequentially, specify a low figure.

Examples:

CREATE UNIQUE INDEX XID ON MYFILE.EMPLOYEE (ID)

CREATE INDEX XNAME ON MYFILE.EMPLOYEE (LASTNAME,FIRSTNAME)

CREATE INDEX XREVIEW-DATE ON MYFILE.EMPLOYEE (REVDATE DESC)

VERIFY TABFILE

VERIFY TABFILE tabfile  [ON filename]

Checks all of the tables on the specified tabfile. If a table or tables are corrupt, VERIFY issues a notice of the affected tables and prompts on whether to purge the corrupted tables.

If a tabfile is corrupt, you may have difficulty CONNECTing to it to verify it. If you have DBA permissions, CONNECT to a corrupt table by specifying READ access only.

The ON clause is used to identify a the physical file where the name of the physical file is not the internal tabfile name plus .tbf.