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

SQL

Tabfiles and Tables

A tabfile is a physical file on disk and is independent of all other tabfiles. A tabfile can hold multiple tables and is the largest unit that exists for security and access control.

An SQL session may be connected to multiple tabfiles and can retrieve data from tables in any connected tabfile.

Whenever tables are referenced, the tabfile can be specified or the default can be taken. One tabfile is always the default and can be any tabfile. If no other default is set, the $SYSTEM tabfile is the default.

Table

Index

Commands

The SELECT command also creates tables which are exactly the same as tables created in any other way. SELECT copies data definitions and populates the table and can be a much more convenient way to define new tables than using explicit commands.

You can also use VisualPQL or the SirSQL menus and SirDBMS menus to create tabfiles and tables.

There are five utilities which can be used with tabfiles. These are:

EXPORT which creates a text version of the tabfile or individual tables which can then be used by SQL to re-create the table. This can be used to move the tabfile from one machine to another.

VERIFY which checks tabfiles for possible corruptions.

BACKUP TABFILE which takes a sequential file copy of a tabfile .

RESTORE TABFILE which rebuilds a tabfile from the sequential copy and applies changes logged to a journal.

DISPLAY JOURNAL which lists the contents of a journal file.

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

CREATE TABFILE MYFILE FILENAME 'MYFILE.TBF'

IDENTIFIED BY
Starts to create security definitions for access to the tabfile. The group name and optional group password specifies a group name who has DBA permission for the tabfile. If the DBA wishes other groups to access this tabfile, the DBA gives permissions with the GRANT command. The user name and optional password further restrict original DBA access to the tabfile to a second level of name and password. For example to leave MYFILE available to everyone to connect to it:

CREATE TABFILE MYFILE.TBF

CREATE TABFILE MYFILE.TBF IDENTIFIED BY -
          SURGEON/BYPASS.JONES/INTERN

JOURNAL
Specifies that journaling is turned on for this tabfile and names the operating system file which is to be used. 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.

BLOCKS
Specifies the number of the blocks that are used to create a physical block. The default is 1. The actual block size is 2k bytes. A specification of 2 would give 4k bytes and so on. The number must be a positive integer.

In general the default is adequate. There is one circumstance where the block size must be specified. A block must be able to hold the largest physical row. If you plan to define very large rows, specify a BLOCKS clause to create a block large enough to accommodate this.

The BLOCKS clause may be specified for performance reasons. Larger blocks are more efficient for serial processing but take more memory. Small blocks are more efficient for random processing through indexes where each I/O probably accesses a different block.

CREATE TABLE  [tabfile.]table
              (column-name data-type [options] , ...  )
              [optional-table-clauses]

Specify the name of the table and a list of the columns that make up the table. A maximum of 250 columns can be specified for a table. The table and one column with its data type are the only required clauses.

Each column must have a name and a data type. Other column specifications are optional. Enclose the complete column list in parentheses. Optional specifications that are not explicitly defined take the default values SET in the current session.

tabfile
Specifies the tabfile where the table is located. A single tabfile can contain any number of tables. The table is stored in the default tabfile if a tabfile is not specified.

table
The table name is required. The table name must be unique within the tabfile.

column_name
Specify a name for each column. Column names must be unique within the table.

The Data Type controls whether a column is numeric, date or string, etc. and cannot be altered once the table is created.

The Column Options clauses control how the column is created and stored and cannot be altered once the table is created. The Display Format control the appearance of a column and these can be altered after the table is created.

Specify as many optional clauses as needed for any column (provided that the clauses used are compatible with the data type specified for the column).

There are further optional clauses which can be specified after all of the column specifications:

   [ FORMAT clauses ]
   [ PCTFREE (n)]
   CONSTRAINTS UNIQUE column-list ]

PCTFREE (n)
Specifies the percentage of free space that is reserved for future expansion in each data block of a table. The only purpose of allowing room for growth in this clause, is for existing rows to be modified where the modified records takes more space. Specify an integer from 1 to 99. Rows grow when column values increase in size. The default is 10 (percent).

CONSTRAINTS UNIQUE (column list)
Specifies a list of columns where combinations of the values in the columns must be unique for a number of rows. If an attempt is made to add a row where the combination is not unique, the row is rejected with an error message. The columns must also be specified as NOTNULL.

CONSTRAINTS UNIQUE creates a unique index for the table which is given a system generated name: '&UNIQUE_MULTIPLE_INDEX_I_n' where n is the number of the index.

Specify the column names enclosed within parentheses. Repeat the clause for as many combinations of columns as required. For example:

CONSTRAINTS UNIQUE (Name, Sex, Birthday).

CATEGORICAL [(n)]
CHARACTER | STRING [(n)]
DATE [('date_map')]
DEC | NUMERIC [(length,decimals)]
FLOAT [(n)] | REAL | DOUBLE]
TINYINT | SMALLINT | INT |
TIME [('time_map')]

CHARACTER
Defines the column as character. STRING is a synonym for CHARACTER.
n specifies the maximum string length. The default is 254 which is also the maximum. Strings are held as variable length unless the optional clause FIXED is specified. CHARACTER can be abbreviated to CHAR.

DATE
Defines the column as a date which is displayed or entered according to the date map. Internally, the date is held as a number of days since the start of the Gregorian calendar. Externally, the date is input and output in accordance with the date map. If a date map clause is not specified, the current system date map is used.

DEC
Defines the column as a scaled integer number. NUMERIC is a synonym for DEC. Length specifies the total length of the number. Decimals specifies how many of those digits are to come after the decimal point. For example: DEC (10,2) means that the integer is 10 digits long, with 2 digits to the right of the decimal point. This is equivalent to defining an integer type and the optional SCALE clause with a value of -2.

FLOAT n | REAL | DOUBLE .
Defines the column as a floating-point number. FLOAT n is either 4 or 8 and the default is 8. FLOAT (4) or REAL gives single precision. FLOAT (8) or DOUBLE gives double precision.

INT | SMALLINT | TINYINT
Defines the column as a fixed length integer.
INT is a 4 byte integer; SMALLINT is a 2 byte integer; TINYINT is a 1 byte integer;

TIME
Defines the column as a time which is displayed or entered according to the time map. Internally, the time is held as a number of seconds since midnight. Externally, the time is input and output in accordance with the time map. If a time map clause is not specified, the current system map is used.

[ BIAS (n) ]
[ FIXED  ]
[ MISSING  value 'label')]
[ NOTNULL [ UNIQUE ]]
[ PRESET  ]
[ SCALE  ]
[ VALID   (value) ]
[ VALUE LABELS  ]
[ VARYING  ]

FIXED
Specifies that the string column is stored as fixed length. Strings are variable length by default. FIXED may result in faster processing, but may use space inefficiently if there is a wide variation in lengths of values. For example, a string for social security number, which is always present and always the same length, could be specified as FIXED. If a string may vary considerably in length, let it default to variable length.

Numeric columns are always FIXED.

MISSING
Specifies the column's missing values and can associate a label with each value or range of values. Specify single missing values or ranges of missing values with optional labels for these values. The value may be a value that corresponds to the data type of the column or may be the keyword BLANK or UNDEFINED which are allowed for any data type. If the data type is a string, enclose the value in quotes.

The command may be specified as MISSING VALUES or MISSING RANGES as documentation but this has no effect on the specification.

Defining a value as MISSING is an implicit definition that it is a VALID value. MISSING and VALID values are stored as a single list which is searched serially. Once a match is found, the search stops. The list is checked for overlapping ranges which are reported as an error. Separate each entry with a comma. Enclose the whole list in parentheses.

Except for undefined or BLANK numeric variables, the actual value entered is still held in the table. However when the value is retrieved, it is flagged as a missing value. The MISS function can be used to retrieve values which include original values that would otherwise be missing.

For example:

MISSING  (8 'Refused to Answer',
          9 'Not Applicable',
          BLANK 'No Answer Coded')
MISSING  ('N/A' 'Not Applicable',
           BLANK 'No Answer Coded')

Specify a range of values and associate a single value label with any value entered in that range. A range is a pair of values, separated by a colon, which correspond to the data type of the column. To separate ranges for readability, use the square brackets [ ].

Parentheses specify ranges where the end points are not missing. When using parentheses, the keywords LOWEST and HIGHEST can be used to specify end points.

For example:

MISSING (0:18 'Under Age', 66:99 'Retired')
MISSING ([1:18],[50:59],[90:99])
MISSING  ( (LOWEST:18)  'Too Young',(65:HIGHEST) 'Too Old')

NOTNULL
Specifies that the column cannot be missing. An attempt to insert a row that contains a missing value for this column fails.

NOTNULL UNIQUE
Specifies that no two rows can have the same value for the column. An attempt to insert the same value twice fails, and an error message is issued. The CONSTRAINTS UNIQUE clause specifies combinations of columns to be unique.

PRESET (value)
Specifies a value that is stored if no value is explicitly given. The value must agree with the type, length, and map specifications for the column. Enclose string values in quotes. By default, columns are set to UNDEFINED and there is no need to specify this.

SCALE (n)
Specifies the power of 10 that a number is multiplied by as it is placed in storage as an integer. This provides efficient storage of large or small integers where the accuracy level is only required at the scaling factor.

For example, for a result in kilovolts, where the calculations are in volts, specify a scale of 3 for kvolts data, and the conversions are handled properly.

A scale of -n, specifies that there are n decimal positions. For example, decimal money can be held at a scale of -2.

Any calculations which refer to a scaled integer, should express the number as the external normal value of the number; the software deals with any internal scaling. For example, to select rows where an amount of money is greater than 100 dollars:

SELECT ...  WHERE  AMOUNT  GT 100

VALID (range list).

Specifies the valid list of values or ranges of values that are allowed in the column. The syntax rules for the range list are the identical to the range list in the MISSING clause. A specification of a value as missing, means that this is a legal value to be input.

The command may be specified as VALID VALUES or VALID RANGES as documentation but this has no effect on the specification.

All missing and valid values are stored as a list which is searched serially. Once a match is found, the search stops. The list is checked for overlapping ranges which are reported as an error.

CATEGORICAL variables must have individual valid values not ranges.

VALUE LABELS (value label list)

Specifies labels for particular values that occur in the column. Each entry consists of the value followed by the label. Separate multiple entries with commas and enclose the whole list in parentheses. For example:

VALUE LABELS (   'AL' 'Alabama',
                 'AK' 'Alaska',
                 ......,
                 'WY' 'Wyoming')

VARYING
Specifies that the column is variable length. This clause is documentary only. Strings are variable length by default and this clause has no effect on other data types.

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

UNIQUE
Specifies that two rows cannot have the same index value. Rows with a value the same as an existing row are rejected. When creating an index for an existing table, if existing rows contain identical values, the index is not built and an error message is issued.

index name
Specifies the name of the index. Index names must be unique on the tabfile.

ON
Specifies the table to index. If a tabfile is not specified, the default tabfile is used.

column
Specifies the column(s) to index in major to minor sequence. Specify DESC for any columns in descending order. For example: a specification of (Sex, Name) gives all males by name, then all females by name. A specification of (Name, Sex) gives everyone with the same name together, males preceding females.

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 static and the index is not going to be updated, specify a low figure. For example:

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)

Create a group by naming it on a the IDENTIFIED BY clause on the CREATE TABFILE or on a GRANT command. An individual user in a group may be granted permission to do additional operations. Permissions may be granted and revoked by users, who may only grant the permissions which they have been granted.

GRANT    {permission,...| ALL | ALL BUT permission,...}
         TO grpname[/grppass][.username[/userpass]],...
         ON tabfile | [tabfile.]table_name
         [ WITH GRANT OPTION ]

GRANT SELECT [ ( varname, varname... ) ] TO ... ON ...
GRANT UPDATE [ ( varname, varname... ) ] TO ... ON ...

ALL - all permissions are granted.

ALL BUT - all permissions except the specified permission(s) are granted.

DBA - permission to do anything to a tabfile. This is required for certain utilities, in particular EXPORT. There are no activities on an individual table that require DBA permission.

CONNECT - permission to connect to a tabfile.

CREATE - permission to create tables.

SELECT - permission to SELECT from tables.

DELETE - permission to DELETE rows from a table.

UPDATE - permission to UPDATE rows in a table.

INSERT - permission to INSERT rows in a table.

DROP - permission to DROP or delete tables.

ADDCOL* - permission to add columns to tables.

MODCOL* - permission to modify columns in tables.

DELCOL* - permission to delete columns from tables.

INDEX - permission to create or drop an index on table.

* These permissions are provided for the future implementation of the ALTER TABLE command. These currently do not have any effect.

Permissions exist at two levels, the tabfile level and, possibly, at the individual table level. When a user connects the tabfile, the appropriate set of permissions are retrieved and the user is only permitted to perform allowed activities. For example, a user must be allowed to SELECT at the tabfile level before being able to SELECT from a specific table. If a group(.user) has DBA permission at the tabfile level, they have all permissions on all tables, regardless as to how the tables were created or permissions assigned.

If a user is allowed an activity at the tabfile level, whether they are allowed that activity on any table on the tabfile depends on how the table is created and can be modified by specific GRANT/REVOKE commands. If the table is a PUBLIC table, then no further checking is done.

If the table is created in SQL with a SELECT, then a Public table can be created in SQL by clearing the Define_Security setting before doing the select. Otherwise the group(.user) that created the table has all permissions on that table and is the only group(.user) allowed any activities on that table until further permissions are granted.

If the table is created in SQL with the CREATE TABLE command, the group(.user) that created the table has all permissions on that table and is the only group(.user) allowed any activities on that table until further permissions are granted.

If the table is created by the VisualPQL procedure SAVE TABLE, the procedure can optionally specify groups(.users) that have full permissions on the table. The group name used to connect the tabfile is always granted all permissions on the table. The procedure can specify a group of Public to create a public access table.

TO
Specifies the group names to receive the permissions. If the group does not already exist for this tabfile, it is created. An optional group password may be specified. An optional username and user password may also be specified. Permissions granted to a group are granted to all members of the group. There is no need to specify individual users in a group unless you need to allow someone specific additional permissions. You cannot specify a user to be a member of a group and restrict them from any group permissions. If you specify a group and user, and the group does not exist, the group and group password are created but group level permissions are not set up automatically and you cannot use just a group name until permissions are set for the group.

Group names, user names and passwords are checked when a tabfile is connected. To update a group or user password, connect to the tabfile as that group/user and use the GRANT command with the special keyword PASSWORD. e.g. GRANT PASSWORD TO GROUP1/NEWPASSWORD. If a password is forgotten, a DBA can export the tabfile with the security option and the export file will contain GRANT commands with readable passwords.

If the group already exists, there is no need to respecify the group password. If the group/user combination already exists, there is no need to respecify the user password. In either of these cases, the original password is kept and any password specified on the command is ignored.

ON
Specifies the tabfile name or table name to which the permissions apply.

WITH GRANT OPTION
Specifies that the group or user can grant these permissions, or a subset of these permissions, to other groups or users. Without this clause, these permissions cannot be granted by this user to other users.

Examples:
Suppose you want to create a tabfile called MYFILE and restrict DBA authority to group DBAS and they have a password DBASPASS. Specify

CREATE TABFILE MYFILE IDENTIFIED BY DBAS/DBASPASS

Then, when people try to connect to that tabfile, they either specify CONNECT TABFILE MYFILE IDENTIFIED BY DBAS/DBASPASS and get full permissions or they will not be allowed even to connect to the tabfile. To allow connection, connect as a DBA and specify, for example, GRANT CONNECT, SELECT TO RESEARCH ON MYFILE. If you as DBA create a table called TABLE1 then you might GRANT SELECT TO RESEARCH ON MYFILE.TABLE1 to allow that group to select from Table1. Similarly if you had created a table EMPLOYEE on tabfile PERSONNEL and granted Connect and Select permissions to a Personnel group, you might then GRANT SELECT (ID, NAME, SEX) TO PERSONNEL ON CURRENT.EMPLOYEE WITH GRANT OPTION which allows them access to specific columns on that table with the ability to create other groups(.users) with those permissions.

REVOKE   {permission,...| ALL | ALL BUT permission,...}
         TO grpname[/grppass][.username[/userpass]],...
         ON tabfile | [tabfile.]table_name

You can revoke permissions only if you granted them. You do not have to REVOKE all permissions originally granted, you can revoke a subset.

If permissions are revoked, that user no longer has the rights accorded by those permission. This carries down to groups and users who have been granted permissions by that user. For example, if USERA has granted a permission to update a certain table to USERB, USERB is not able to update that table if update permission is revoked from USERA.

Permissions revoked from a group are revoked from all members of the group.

To revoke permissions you must be connected to the tabfile. The group(.user) specified at connection time is the grantor. The original creator of a tabfile has a special set of permissions with a special System grantor and you cannot revoke any permissions from that group(.user). Similarly, the original creator of a table using SQL has the same special grantor and you cannot revoke permissions from that group(.user) for that table.

If you are connected as a group(.user) you can revoke permissions from other group(.users) that you created directly. If those users have granted permissions to other users, and those permissions are affected by your revoke, then the revoking is carried down the hierarchy. However you cannot revoke permissions directly from group(.users) that you did not grant to directly. If you grant to groupa and they grant to groupb, you cannot revoke groupb even if you are the overall DBA for the tabfile. You also cannot revoke from yourself.

If you revoke all permissions from a group(.user) at the tabfile level, the group(.user) is deleted from the file.

DEFINE_SECURITY

Specifies that tables created with SELECT have security definitions. When connecting to a tabfile with security definitions, specify group(.user) names to connect to the tabfile. Anyone with DBA permission at the tabfile level, has full permissions for all tables. With DEFINE_SECURITY set, you have full permissions for the new table created with SELECT but any other groups(.users) (except DBAs) have no permissions on that table. You (or a DBA) can grant permissions to other users. (see Permissions)
CLEAR specifies that created tables are Public access.
Default: SET

EXPORT [ FILENAME ] filename
       [ RECSIZE n ]
       [ NOSECURITY ]
       [ NOINDEXES ]
       [ NODATA ]
       [ NOTABFILE ]
       [ NOWORKSPACE ]
       tabfile [(table [(column, ......)], ......)]

RECSIZE
Specifies the record length for the named file. The minimum recsize is 80. This is the default. Records are fixed length.

NOSECURITY
Prevents the security privileges from being written to the exported file.

NOINDEXES
Prevents indexes from being written to the export file. By default, all indexes are written to the export file.

NODATA
Prevents the data from being written to the export file. Only the schema is written. By default, the export includes the data for each table.

NOTABFILE
Prevents information specific to the overall tabfile from being exported. When the export file is imported all of the tables are placed on the default tabfile of the user performing the import.

NOWORKSPACE
Prevents information about the current settings from being exported.

tabfile
Specifies the tabfile to export. If a tabfile is not specified, all connected tabfiles are exported.

table
Specifies the table(s) to export. If no tables are specified, all the tables on the tabfile are exported. The entire table or selected columns of that table can be exported. More than one table can be specified. The entire table is exported when columns are not specified.

(column, ...)
Specifies individual column(s) to export.

Example:

EXPORT 'EXPORT.DAT'  MYTABFILE (MYTABLE (COL1 COL2) MYTABLE2 )

You can only export tables and columns for which you have read security.

An SQL export file is simply a set of SQL commands and data in textual form. It can be imported in three ways:

It can be used on the IN parameter when executing SQL to import the file in batch mode.

It can be imported through the utilities menu option.

You can read the file into the input area (with cut and paste or by 'Opening' it) and execute the statements directly. If you do this, first delete the initial BEGIN IMPORT line.

VERIFY

VERIFY tabfile  [ ON filename ]

Checks all of the tables on the specified tabfile. If a table or tables are found to be corrupt, SQL issues a notice of the affected tables and purges the corrupted tables. Any tabfile can be verified, it does not have to be connected.

If a tabfile is corrupt, there may be difficulty connecting to it. CONNECT to a corrupt tabfile with READ access only. If the $PASSWORD or $SECURITY system tables are corrupted, then all users have DBA permissions on the tabfile.

Specify the ON clause to identify the physical file where the name of the physical file differs from the internal tabfile name.

BACKUP TABFILE

BACKUP TABFILE  tabfile_name FILENAME filename [ FULL | DATA ]

Backs up a tabfile to an operating system sequential file. Specify the keywords BACKUP TABFILE and FILENAME. The filename is the name of the file being created as the backup.

FULL
Specifies that each block of the tabfile is compressed and written to the output file. When it is restored, the tabfile is the exact size as before (no pointer restructuring of the indexes is done).

DATA
Specifies that only the physical data records and definitions of the index(es) are written to the backup file. The backup file is smaller but indexes have to be rebuilt when the file is restored.

RESTORE TABFILE

RESTORE TABFILE tabfile_name [ FILENAME filename ]
         [ FROM   filename ]
         [ JOURNAL        filename ]
         [ APPLY  filename , ...  ]

Restores a tabfile from a backup file and/or applies journalised updates. RESTORE TABFILE does not overwrite existing tabfiles. The FROM clause specifies the name of the backup file; the FILENAME clause specifies the operating system name of the restored tabfile if the tabfile name is not the operating system filename.

Specify JOURNAL to assign a new journal file to this tabfile. If JOURNAL is not specified, the original journal file is used for journaling.

APPLY applies journal files changes to the tabfile. Specify the journal file to be used. All journals applied must be in order with no gaps.

Example:

BACKUP TABFILE mytabfile FILENAME 'MYTAB.BAK'
RESTORE TABFILE mytabfile FROM 'MYTAB.BAK'

DISPLAY JOURNAL

DISPLAY JOURNAL filename         FILENAME  filename
         [ HEADER         [ TF | TABLE | INDEX ]...  ]
         [ DETAILED       [ TF | TABLE | INDEX | ROW ]...  ]

Lists information about the contents of a specified journal file. The tabfile must be connected to display the journal. The FILENAME clause specifies an output file for the listing. Specify both the journal filename and the output filename. By default, tabfile headers, table and index entries are listed (headers are one line of information ). Specify detailed information on the tabfile (TF), a table, index or rows with the DETAILED clause.