| ||
| 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.
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
CREATE TABFILE tabfile-name
[FILENAME filename ]
[IDENTIFIED BY grpname [/grppass] [.username[/userpass]]]
[JOURNAL filename]
[BLOCKS n]
CREATE TABFILE creates a tabfile to store one or more tables.
The tabfile name is the name by which this is referenced in all other commands.
This name must be used to
CONNECT to this file in subsequent
sessions. A tabfile is automatically connected after being created.
For example:
CREATE TABFILE MYFILE FILENAME 'MYFILE.TBF'
FILENAME
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.TBFTo require that the group name
SURGEON password
BYPASS and user name JONES password
INTERN are needed when connecting to this tabfile as a DBA in
subsequent sessions:
CREATE TABFILE MYFILE.TBF IDENTIFIED BY -
SURGEON/BYPASS.JONES/INTERN
If the IDENTIFIED BY clause is not specified, the
tabfile is created with DBA access assigned to a special group named
PUBLIC. This permits any user to access the tabfile with all
permissions. This cannot be changed because all users are either signed on as
PUBLIC or as a group that PUBLIC has created
and a user cannot revoke permissions from themselves or their grantor.
JOURNAL
Specifies that journalling 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
CREATE TABLE [tabfile.]table
(column-name data-type [options] , ... )
[optional-table-clauses]
Creates a definition of a new table.
A SELECT does this
automatically.
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 ]
FORMAT
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).The same result could also be achieved with the CREATE UNIQUE INDEX command.
With
The command may be specified as
Defining a value as
The actual value entered is not held in the table. The value held is
a number between 0 and 127 corresponding to the matching entry in the missing
value list. The order of the list is the order of the missing value from 1 to
127. 0 is used for the system
For example:
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
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:
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
The command may be specified as
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.
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:
For most applications, the default of fixed
numeric is correct. However for a large table with many instances of missing
numeric data, this clause could be used to save disk space.
Permissions may apply to the tabfile as a whole, to individual tables and views
and to individual columns. By default, all users have all permissions.
Permissions may be granted to and revoked from groups of users and individual users.
A group is a set of users who, by and large, are all allowed to do the same
operations. A group has a name and may have a password.
There is no commonality between tabfiles for groups or users; groups and users
only exist within a tabfile. To use standard names for groups to access
multiple tabfiles, create naming standards and conventions which are then used
for each individual tabfile.
Create a group by
naming it on a 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.
Creating permissions typically involves specifying a groupname for DBA
permissions when the tabfile is created and then, as that group, granting
permission to other groups to perform various operations. Permissions can be
restricted to particular tables and to individual columns in those tables as
required.
Create a set of permissions by specifying who can access the tabfile
with the IDENTIFIED BY
clause on the
Permissions start with the creator of a tabfile. People who are allowed to do
anything to a tabfile are said to have DataBase Administrator (DBA) permission.
When a tabfile is created, the
If DBA permission is not set to specific groups or users at the tabfile level,
it is impossible to restrict access to any tables in the tabfile.
When the
* These permissions are provided for the future implementation of the
Examples:
You can revoke permissions only if you granted them. You do not have to
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.
Follow the filename with any keywords:
Example:
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
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
If a tabfile is corrupt, there may be difficulty connecting to it.
Specify the
Specify
Example:
Column Data types
There are a number of possible specifications for column data types,
some of which are synonyms for others.
CATEGORICAL [(n)]
CHARACTER | STRING [(n)]
DATE [('date_map')]
DEC | NUMERIC [(length,decimals)]
FLOAT [(n)] | REAL | DOUBLE]
TINYINT | SMALLINT | INT |
TIME [('time_map')]
CATEGORICAL
Defines the column as character which contains
one value from a pre-specified list of values. The input data is checked
against the list. The position in the list that corresponds to the input data
is stored in the table rather than the value of the entry. Typically this is
used for a list of names (e.g. Names of States), where it is more efficient to
store a code rather than a value.
n is the number of entries in the
list. Values for the strings are then defined in the VALID
VALUES clause.
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.
Column Options
The clauses controlling how the column is stored are :
[ BIAS (n) ]
[ FIXED ]
[ MISSING value 'label')]
[ NOTNULL [ UNIQUE ]]
[ PRESET ]
[ SCALE ]
[ VALID (value) ]
[ VALUE LABELS ]
[ VARYING ]
BIAS
Specifies an integer constant that is added to an integer
before it is stored. For example, this might be used in a study with
questionnaires from multiple sources, each of which was numbered from 1. To
avoid multiple questionnaires numbered the same, BIAS the
question number in each table by a different amount such that one table had
questionnaires 1 - 99, the next 101 - 199, 201 - 299, etc. SCALED integers, the BIAS is done before
scaling, so express the bias as the unscaled number. For example, if the scale
is 2 (hundreds) a BIAS of 1, results in a bias of 100.FIXED
Specifies that the column is stored as fixed length, regardless of
the length of the values. Strings are variable length by default.
FIXED results 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.
FIXED is the default for numeric data.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. Specify up to
127 different missing values or ranges of missing 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.MISSING VALUES or
MISSING RANGES as documentation but this has no effect on the
specification.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.UNDEFINED value.
MISSING (8 'Refused to Answer',
9 'Not Applicable',
BLANK 'No Answer Coded')
MISSING ('N/A' 'Not Applicable',
BLANK 'No Answer Coded')LOWEST and
HIGHEST can be used to specify end points.66:99 is coded as missing, any number in that range is
equivalent to any other number. When dealing with original data, it is unusual
to use missing value ranges; these are normally used on extract or analysis
tables. 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')
The second example creates ranges without labels. The third
example specifies that those younger than 18 and older than 65 are missing. The
parentheses specifies that the actual quoted value is not missing but that all
values from that point on are. This is useful for real numbers, where it may be
impossible to specify the actual end of a range.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) | MISSING (n)
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.MISSING (0) sets the value to UNDEFINED.
MISSING (n) (where n is between 1 and 127) sets one of the defined
missing values. This number corresponds to the position in the definition of
missing values. Default: PRESET MISSING (0)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.SELECT ... WHERE AMOUNT GT 100
Scaled integers, by definition, cannot hold data at less than their
scale. Any computation is rounded. For example, setting KVOLTS
(scale 3) to any number which is not a round thousand, results in the
number being rounded to the nearest thousand.VALID (range list).MISSING clause. A specification of a
value as missing, means that this is a legal value to be input.VALID VALUES or
VALID RANGES as documentation but this has no effect on the
specification.CATEGORICAL variables must have individual valid
values not ranges.VALUE LABELS (value label list)
VALUE LABELS ( 'AL' 'Alabama',
'AK' 'Alaska',
......,
'WY' 'Wyoming')
This associates the full state name with the abbreviation. When
referencing CATEGORICAL variables, specify the equivalent
integerVARYING
Specifies that the column is variable length . This clause
can be used with all data types but is only needed for numeric data. Strings
are variable length by default.
CREATE INDEX
CREATE [UNIQUE] INDEX index-name ON [tabfile.]table
(column [ASC|DESC], ...)
[PCTFREE integer value]
CREATE INDEX creates an index for a table providing direct
access to a subset of records. Index usage is automatic in SQL once the index is
defined.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)
Permissions
The ability to perform various types of operations ( such as the ability to use a
tabfile or a
table, to update a table, create a new table, etc) may be restricted to specific
users or sets of users. Users are allowed
to perform particular activities through a set of permissions.CREATE TABFILE
command. Use the GRANT
and REVOKE
commands to control permissions.IDENTIFIED BY clause restricts
DBA permission to a group or user.IDENTIFIED BY clause is not specified,
the tabfile creator (and DBA) is set to the special group,
PUBLIC. Anyone connecting to that tabfile without specifying
a specific group or user is assigned the group PUBLIC. When
PUBLIC is the DBA for a tabfile, users who do not specify a
group name have permission to do anything.
GRANT
GRANT {permission,...| ALL | ALL BUT permission,...}
TO grpname[/grppass][.username[/userpass]],...
ON tabfile | [tabfile.]table_name
[ WITH GRANT OPTION ]
GRANT gives permissions on tabfiles, tables,
views or individual columns to specified groups or users.DBA, CONNECT, and CREATE are only applicable
at the Tabfile level. All other permissions may be granted at the individual
table or view level. Permission may be granted on individual columns for
SELECT and
UPDATE which restricts access to those specific columns.
To give permissions for particular columns,
specify a list of column names on the permission clause:
GRANT SELECT [ ( varname, varname... ) ] TO ... ON ...
GRANT UPDATE [ ( varname, varname... ) ] TO ... ON ...
permission
Specifies the permission(s) being granted. The following permissions may be granted:ALL - all permissions are granted.ALL BUT - all permissions except the specified
permission(s) are
granted.DBA - permission to do anything to a tabfile.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.ALTER TABLE command. These currently do not have any effect.TO
Specifies the group names that are to receive the permissions. If
the group does not already exist for this tabfile, it is created. An optional
group name password may be specified. An optional username and username
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 special permissions.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.
GRANT CONNECT TO RESEARCH ON FILE1
GRANT ALL BUT DROP TO RESEARCH ON FILE1.TABLE1
GRANT SELECT (ID, NAME, SEX) TO PERSONNEL ONCURRENT.EMPLOYEE WITH GRANT OPTION
REVOKE
REVOKE {permission,...| ALL | ALL BUT permission,...}
TO grpname[/grppass][.username[/userpass]],...
ON tabfile | [tabfile.]table_name
REVOKE revokes permissions on tabfiles or on individual tables
for the specified groups or users. REVOKE is the opposite of
GRANT and has
identical keywords and syntax.REVOKE all permissions originally granted, you can revoke a
subset.
EXPORT
EXPORT [ FILENAME ] filename
[ RECSIZE n ]
[ NOSECURITY ]
[ NOINDEXES ]
[ NODATA ]
[ NOTABFILE ]
[ NOWORKSPACE ]
tabfile [(table [(column, ......)], ......)]
Exports tabfiles, tables, or selected columns from tables.
EXPORT creates a text file from which the exported elements
can be imported on any machine running SIR2002.filename
Specifies the file to contain the exported data. The filename must
be the first clause in the export. The keyword FILENAME can be
specified for readability.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.
EXPORT 'EXPORT.DAT' MYTABFILE (MYTABLE (COL1 COL2) MYTABLE2 )
You can only export tables and columns for which you have read security.IN parameter when
executing SQL to import the file in batch mode.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.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.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.JOURNAL to assign a new journal file to this tabfile.
If JOURNAL is not specified, the original journal file is used
for journalling.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.
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.