Database

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

Database

Tabfiles and Tables

A Table is a Relational Table (or flat file) that is a number of occurrences (from 0 to n) of a single type of record that 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 that 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 SIR/XS 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 SIR/XS 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 SIR/XS commands that 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 that 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 used in all other commands. This name is stored on the physical file and is the same name used to CONNECT to this file in subsequent sessions. A tabfile is automatically connected when created.

FILENAME

A filename for the tabfile. If this is not specified, the filename is created from the tabfile name plus a suffix of .tbf and this must be a valid filename on your operating system.

JOURNAL filename

Specifies that journaling 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. If the journal is there, 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 of 1 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.

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.