Database 
|
| Database | | Schema |
PASSWORD command.
Attaching a database and supplying passwords is normally done through
menus or with
the Execution Parameters.
There are numerous commands to define the specific features of a database.
A set of commands that corresponds to the definition
of the attached database can be written to a file using
WRITE SCHEMA
and edited.
The schema definition can then be executed as any other SIR/XS procedure.
A report on the definition of the attached database can be produced
with the
SCHEMA LIST
command.
The complete database can be deleted. Individual record definitions can be deleted provided that there is no data for that record.
The format of schema commands is the same as other SIR/XS commands, that is new commands must begin at the start of a line and continuation lines for a command must have blanks at the start of the line. Individual clauses within a command may be separated by slashes for readability.
All changes to a database, including schema changes, are recorded on the journal file provided that journaling is on. Initial schema definition before any data is added to a database is not journaled. Once there is data in the database, each schema modification run increments the update level of the database.
Overall database commands precede record definition commands. There may be many sets of record definition commands (one set for each record type) and, within a record definition, there is an order for the various types of commands.
Secondary index definitions may follow a record definition. A secondary index is defined with a single
CREATE DBINDEX command. These can be done before or after any initial data loading. Defining an index builds the index automatically if there is existing data in the record.
[NO] CASE ID
DATA FILES
DATABASE LABEL
DOCUMENT
ENCRYPT [ON|OFF}
MAX INPUT COLS
MAX KEY SIZE
MAX REC COUNT
MAX REC TYPES
N OF CASES
N OF RECORDS
READ SECURITY
RECS PER CASE
RECTYPE COLS
SYSTEM SECURITY
SYSTEM SECURITY LEVEL
TEMP VARS
WRITE SECURITY
Record Definition
The record definition commands specify the name and number of the record, the key fields and any documentary text together with the name, type, and size of each variable. A further set of information may be specified that relates to the Batch Data Input utilities. This specifies how data is loaded from serial files, including any computations and logical accept/reject clauses.
Sometimes the same data with the same coding scheme appears on multiple record types. For example standard drug codes or states in a country. Rather than repeating definitions in multiple record types, a standard schema can be defined that contains all of the descriptions and codes for the variable and it can then simply be included as a standard var in records as necessary.
In a case structured database, a set of variables can be held at the case level in the CIR. Specify variables that are in the CIR with a RECORD SCHEMA 0 CIR record definition.
Within a record definition, there is an order for commands. The example record definition shows some of the most commonly used record definition commands.
The following commands are used to specify records.
ACCEPT REC IF
CAT VARS
CHARACTER*n
COMPUTE
CONTROL VARS
VALID VALUES or VALUE LABELS are control
variables. To specify other numeric variables as control variables, define
a VAR RANGE for them.
DATA LIST
DATE VARS
DOCUMENT
END SCHEMA
IF
INPUT FORMAT
VARIABLE LIST.
INTEGER*n
KEY FIELDS
MAX REC COUNT
MISSING VALUES
OBSERVATION VARS
REAL*n
REC SECURITY
RECODE
RECORD SCHEMA
REJECT REC IF
SCALED VARS
TIME VARS
VALID VALUES
VALUE LABELS
VARIABLE LIST
INPUT FORMAT as
alternative to DATA LIST.
VAR DOC
VAR LABEL
VAR RANGES
VAR SECURITY
| |
RECORD SCHEMA command to make specific changes. If a definition exists for a record and you do not submit a new DATA LIST command, then you are modifying the schema. If you do submit a new DATA LIST then you must re-submit the entire schema.
The ADD VARS, MODIFY VARS and DELETE VARS are equivalent to a DATA LIST when modifying a schema and have the same syntax.
If the STANDARD SCHEMA is modified, then all record types that have any STANDARD VARS are updated to reflect the changes. If a standard variable definition has been deleted, then standard variables that referenced that deleted variable are no longer standard variables.
There are a number of commands that are only applicable when modifying an existing record definition. Except as documented below, commands completely replace any existing definition.
ADD VARS
CLEAR BOOLEANS
ACCEPT/REJECT conditions.
CLEAR COMPUTES varname1,... | ALL
CLEAR RECODES varname1,... | ALL
CLEAR VALUE LABELS varname1,... | ALL
CLEAR VAR DOC varname1,... | ALL
CLEAR VAR LABEL varname1,... | ALL
COMPUTE
CLEAR COMPUTES command to delete old COMPUTE definitions.
DELETE VARS
EDIT LABELS
VALUE LABELS command. Any new values are added to the list, any existing values that are referenced are updated. Existing value labels that are not referenced are not altered.
MODIFY VARS
RENAME VARS existing_variable_list {AS new_variable_list | PREFIX 'text' | SUFFIX 'text'}
ALL or TO keywords. The new names can be specified individually as a list (which can also use the TO keyword), in which case there must be the same number of variable names in both lists. Alternatively the new names can be constructed by appending a prefix or suffix. If a prefix or suffix is specified, enclose the text in quotes. Note that this text is used exactly as specified so ensure that the correct case is used. Appending a prefix or suffix can result in non-standard names. The resulting names must fit within the 32 character limit on names.Examples:
RECORD SCHEMA 1
EDIT LABELS JOBCODE (21) Salesperson
(22) Senior Salesperson
(23) Sales Manager/
VAR RANGES JOBCODE (1 23)
The following example adds two variables to the Employee record type.
RECORD SCHEMA 1, EMPLOYEE
ADD VARS ETYPE 70 (I)/
PHONE 71 - 80 (A)
VAR RANGES ETYPE (1 3)
VAR LABEL ETYPE 'Employee Type'/
PHONE 'Home Phone Number'/
END SCHEMA
The following example modifies the variable label for Position and the variable
label for value 5 of Rating in the Review record type.
RECORD SCHEMA 3, REVIEW VAR LABEL POSITION 'Job Code' EDIT LABELS RATING (5) 'Excellent' END SCHEMA
Note: if you do two EDIT LABELS for the same variable in the same RECORD definition, only the effect of the final one will be applied. The original labels are reloaded for each EDIT.
| |
When the same command applies to several variables, you can specify multiple variables and definitions on a single command. You can optionally delimit the specifications for each variable with a slash for readability. For example,
VALID VALUES CODE1 (1 2 3)
CODE2 (1 2)
VALUE LABELS CODE1 (1) 'Tested'
(2) 'Preliminary'
(3) 'Passed'
CODE2 (1) 'Domestic'
(2) 'Overseas'
TO. For example, to define ten variables named VAR01, VAR02, ... VAR10:
VARIABLE LIST VAR01 TO VAR10
Once variables have been defined, they can be referenced as a list in other commands by using a pair of variable names separated by the word TO, regardless of the format of the variable names. The sequence of the variables included in the list is determined by the sequence in which the variables were defined. The TO list is inclusive and backwards references are not allowed. For example, suppose the following variables were defined on a variable list:
VARIABLE LIST ID EMPNO NAME STATUS1 TO STATUS3 GENDER
A reference on another command (such as MISSING VALUES) might be ID TO GENDER to include all the variables, or NAME TO STATUS3 to include NAME and the three STATUS fields. For
example:
MISSING VALUES EMPNO TO GENDER (BLANK)
The keyword ALL can be used to reference all of the variables in a record type.
| |
RECORD SCHEMA 0 CIR for the common vars on a case structured database.
The KEY FIELDS command and the DATA LIST or INPUT FORMAT / VARIABLE LIST can only occur once per record. Other commands may occur multiple times.
Within each record, there is a general order that must be followed. The commands to do with the overall record structure come first, then the definition of the variables and then additional specifications referring back to the defined variables. It is normal for the definition of all variables to precede the optional specifications for all variables, but this is not required. It is required that the definition of a particular variable precedes the specification for that variable.
| Group | Commands |
| 1 | RECORD SCHEMA * DOCUMENT KEYFIELDS/SORT IDS MAX REC COUNT REC SECURITY |
| 2 | DATA LIST or VARIABLE LIST/ INPUT FORMAT* INTEGER VARS REAL VARS CHARACTER VARS |
| 3 | CAT VARS CONTROL VARS DATE VARS MISSING VALUES OBSERVATION VARS TIME VARS SCALED VARS STANDARD VARS VALID VALUES VALUE LABELS VAR DOC VAR LABEL VAR RANGES VAR SECURITY |
| 4 | ACCEPT REC IF COMPUTE IF RECODE REJECT REC IF |
| 5 | END SCHEMA |
The * commands (RECORD SCHEMA and either DATA LIST or VARIABLE LIST/INPUT FORMAT) are mandatory and must be supplied. All other commands are optional.
COMPUTE, IF and RECODE commands or these commands can use previously defined variables. If the batch data input utilities are not relevant, this group of commands can be ignored.
| |
RECORD SCHEMA
KEY FIELDS
DATA LIST or VARIABLE LIST/INPUT FORMAT
DATE VARS
& TIME VARS
MISSING VALUES
VALID VALUES
VALUE LABELS
VAR LABEL
END
SCHEMA.
For example:
RECORD SCHEMA 1,PATIENT
KEY FIELDS ID
VARIABLE LIST ID,LNAME,FNAME,DOB,SEX,STATUS
INPUT FORMAT (I4,A40,A40,DATE'MM/DD/YYYY',I1,I1)
MISSING VALUES LNAME to STATUS (BLANK)
VALID VALUES SEX (1,2)
STATUS (1,2,3)
VALUE LABELS SEX (1) 'Male'
(2) 'Female'
STATUS (1) 'Inpatient'
(2) 'Outpatient'
(3) 'No Longer Attending'
VAR LABEL ID 'Patient Id'
LNAME 'Last Name'
FNAME 'First Name'
DOB 'Date of Birth'
STATUS 'Current Status'
END SCHEMAIn the above example the following commands were used:
RECORD SCHEMA
KEY FIELDS
RECORD SCHEMA
command.
VARIABLE LIST
INPUT FORMAT
VARIABLE LIST names the variables; the
INPUT FORMAT defines the data type, size and format. The
variable names are simply listed in the order they are to appear in the
database, separated by blanks or commas. Variables are held in the order they are
defined. The format specification is enclosed in parentheses and commas are used
as separators. "I" specifies integers, "A" specifies alphanumeric. Date formats
consist of the word DATE followed by a date format. See date formats for a complete description. Time formats
consist of the word TIME followed by a time format. See time formats for a complete description.
MISSING VALUES
BLANK specifies that blank input is treated as missing. When the same value is to be assigned to a set of variables the variable list format can be used. This consists of the two variable names that define the start and end of the list and the word "to".
VALID VALUES
VAR LABEL
VALUE LABELS
END SCHEMA
| |
CREATE DATABASE database_name
[JOURNAL = {ON | OFF}]
[PASSWORD = database_password]
[PREFIX = database_directory]
Creates a new database. (NEW FILE is a synonym.) A database name must be a valid SIR/XS a name. To connect to an existing database, use the CONNECT DATABASE command.
JOURNAL
ON. Journaling can be turned on or off with the JOURNAL command
PASSWORD
PASSWORD command that does not list the password in the output listing. If a password is not defined for a database, the password is set to blank. If a database has no password, future connections to the database need not specify a password. The UNLOAD FILE utility can change the database name and password.
PREFIX
| |
CONNECT DATABASE database_name
[JOURNAL = {ON | OFF}]
[PASSWORD = db_password]
[PREFIX = database_directory]
[SECURITY = {read_pw | *} [{write_pw | *}]]
[CREATE]
Connects the specified existing database. (OLD FILE is a synonym.) A database must be connected before it can be used. The last connected database is the default database. Al processes and utilities run on the default database.
A pre-compiled VisualPQL program can connect a database when it runs, but, if you need to compile a VisualPQL program that references a database, the database must be connected first.
JOURNAL
PASSWORD
The database password can be supplied with the PASSWORD command that does not list the password in the output listing.
PREFIX
SECURITY
It is only necessary to specify the write security password when updating the database. To specify a write password when the read password is null, specify the read password as an asterisk (*).
If the read password matches the highest (30) level of security, then the user has Data Base Administrator (DBA) level access to the database and can run all utilities. If the database does not have any read passwords assigned, then any connected user has DBA access. See READ SECURITY
CREATE
.
CONNECT DATABASE command. CREATE makes CONNECT DATABASE act identically to the CREATE DATABASE command.
| |
DISCONNECT DATABASE database_nameDisconnects a database. If this is the default database, the procedure file is set to
SYSPROC.
| |
SET DATABASE database_nameSets a previously connected database as the default.
SHOW DATABASEWrites a viewable list of connected databases.
LIST DATABASESends a list of connected databases to the OutputHandler callback routine in SirAPI when running in that mode.
| |
JOURNAL ON|OFFTurns journaling on or off. Journaling is a database characteristic and it is recommended that journaling is left on under normal circumstances.
| |
PASSWORD database_passwordSupplies the database password.
If this command immediately follows either the CREATE DATABASE (NEW FILE...) or CONNECT DATABASE (OLD FILE...) command. This means that the if the PASSWORD is to be specified then
it MUST be on the next physical command line.
When used with CREATE DATABASE, this command defines the new database password.
When used with CONNECT DATABASE, this command supplies the password needed to connect the database. If the database has no password, this command need not be specified. If an incorrect password is specified, access to the database is denied.
If the PASSWORD command is used at any other time when a database is connected then it will change the database password.
This command does not print the password in the output listing.
| |
SECURITY read_pw , write_pw
SECURITY supplies the read and write passwords. If an incorrect password is specified, level 0 (zero) security is assigned.
This command does not print the passwords in the output listing.
| |
PURGE SIR FILE
[JOURNAL = KEEP | PURGE]
[PROC = KEEP | PURGE]
JOURNAL
KEEP specifies that the journal file is not deleted and is the default. PURGE specifies that the journal file is deleted when the database is deleted.
PURGE SIR FILE JOURNAL = PURGE
PROC
KEEP specifies that the procedures are not deleted. When a new database is created or a database is reloaded, the old procedure file can be used as part of that database. This keeps the procedures from a corrupt database when a restore cannot be accomplished. PURGE specifies that the Procedure File is deleted and is the default.
PURGE SIR FILE PROC = KEEP
| |
DELETE SCHEMA recname | recnum
Deletes the record schema name or number from the database definition. DELETE SCHEMA only operates if there are no records for this record type. This is a DBA security level command.
When defining and redefining a record type, it is sometimes simpler to DELETE SCHEMA and redefine it through a complete new RECORD SCHEMA than to modify it over and over again.
When defining and testing a new database and wish to delete all of the test data for record type n prior to delete schema the following simple VisualPQL program does this (omit the case commands if it is a caseless database):
RETRIEVAL UPDATE PROCESS CASE PROCESS REC n DELETE REC END PROCESS REC END PROCESS CASE END RETRIEVAL
| |
CASE ID varname [(A)|(D)] |
[NO] CASE ID
CASE ID varname establishes the database with a case structure and specifies the name of the variable used on every record as the case identifier.
NO CASE ID establishes the database without a case structure.
Either CASE ID or NO CASE ID is required to set up the database and this command must be used prior to any other definition. Once the case specification and case id have been defined, these cannot be modified.
The case variable may be any data type. Avoid REAL for keys due to the difficulty of specifying exact numbers in floating point.
D specifies descending sort order for cases. If order is not specified, ascending is assumed. When all cases are processed sequentially, they are retrieved in this sequence.
| |
COMMON SECURITY rlevel, wlevelSpecifies the default minimum security levels for all common variables.
Rlevel (read level) and wlevel (write level) are integers between 0 (zero) the lowest, and 30, the highest. If no security levels are defined, level 0, the lowest, is assigned. Further security restrictions for individual common variables can be specified at the record level using the VAR SECURITY command. See READ SECURITY for an explanation of security levels.
Common security levels can be changed. Note, this affects only the security levels for new common variables that are defined or redefined. It does not affect the security levels of currently defined common variables.
| |
RECORD SCHEMA 0 CIR Specifies variables in the Common Information Record or CIR. A CIR exists for every case in a case structured database and holds counts plus the case identifier. It can hold Common variables that are typically those that are used repeatedly in retrieving data from the database. These variables can be referenced at any time regardless of the record type being processed. CIR entries can be updated directly when processing a case, or can take the value of the last entry in a given record type.
Note that common vars, except for the case id on a case structured database, cannot be used as key variables in a secondary index in a record as they are not stored as part of a record.
Specify the format here and then, when this variable is referenced on a subsequent record definition, there is no need to respecify formats except input-output columns and any batch data specifications (e.g. RECODE) for that record.
The specification of the CIR is identical to any other record except that the batch data input specification clauses (ACCEPT REC,REJECT REC,COMPUTE,IF and RECODE) are meaningless. Follow the RECORD SCHEMA 0 CIR with a DATA LIST to respecify completely the common vars or use ADD VARS or DELETE VARS to update the common vars.
Use any of the normal record variable definition commands such as VALUE LABELS as required.
The RECORD SCHEMA 0 CIR set of commands follows any STANDARD SCHEMA set of commands and precedes normal record definitions.
(Note: The older format of COMMON VARS is still supported for compatibility with earlier versions of SIR.)
| |
DATA FILES 'filename'
[FROM (key,...) 'filename']
[FROM (key,...) 'filename']
.....
Specifies that the data file for this database is not a standard data file. It may have a different name, be in a different directory or may be split across multiple data files.
Specify the command at the end of the schema definition - it cannot be processed before the type of the Case variable has been specified for a case structured database. If a record type is specified as a FROM key, then that record type must have been defined. When SIR/XS writes a schema, this command follows any secondary indexes.
If the command does not have any FROM clause, it specifies the name and location of the data file. This can be in a different directory from the other database files and named something other than the database name with a .sr3 extension.
The first specification names the original data file that holds all records up to the value specified on the first FROM key. The last specification names the final data file that holds all records from to the value specified on the last FROM key.
On a case structured database, the first key specified is the case id. If any further specification is required, the next key specified is a record number. On a caseless database, the first key specified is a record number. Subsequent keys can be specified up to the maximum number of keys on the record type.
The filenames must either be fully qualified filenames or simple filenames without any directory specification. If the files are not fully qualified then the data file is placed in the same directory as the other database files.
For example:
DATA FILES 'company.s31'
FROM (500) 'company.s32'
FROM (1000) 'company.s33'
A DATA FILES command with no other specifications removes any previous data file definition and sets the database to have a standard data file.
| |
DATABASE LABEL 'text'
Specifies a label for the database. This text can be up to 78 characters and is enclosed in quotes. The label
can be retrieved in VisualPQL using the RECDOC(0,0) function.
| |
DOCUMENT textSpecifies that the text following the command is commentary. This text is stored in the dictionary describing the overall database.
The text cannot be partially modified. To alter the text, run the DOCUMENT command with new text. The new document text completely replaces the old.
| |
ENCRYPT [ON |OFF]
ENCRYPT turns on data encryption for this database. This means that all data records in the database are encrypted on disk and are thus protected against scrutiny from software other than SIR/XS. The encryption method used is a version of the publicly available Blowfish algorithm using a 256 bit key.
All data records are encrypted, however keys in index blocks are held in unencrypted format. Do not use names or other recognisable strings as keys if this data is sensitive and requires protection. Unloads and journals for encrypted databases are themselves encrypted. Text files are all unencrypted. Schemas and procedures are unencrypted.
ENCRYPT OFF turns encryption off for a database. Encryption can be turned on and off without ill effect. Records are written according to the current setting; records are read and recognized as to whether they require decryption.
Passwords and security levels are encrypted on all databases. There are encryption/decryption functions in VisualPQL if users need to encrypt data for themselves but these use a user specified key - the SIR/XS system key is used for database encryption.
| |
MAX INPUT COLS n
Specifies the length of the largest input line for any record type in the database. N is rounded up to a number evenly divisible by eight. This command is necessary when there is any record type with a batch input format longer than 80. The MAX INPUT COLS can be increased at any time, but cannot be decreased once any record types have been defined.
| |
MAX KEY SIZE nSpecifies the maximum key size required for any record type in the database. The default is the size of the largest key currently defined for any record type in a database and is calculated automatically.
Only specify an explicit key size if you expect to define a new record type with a larger key than the current largest key after loading data into the database. If MAX KEY SIZE is set to the largest expected key, it avoids having to UNLOAD and RELOAD the database.
The keys for a record are: the Case Id, the record number and the key fields. The current MAX KEY SIZE can be obtained from the database statistics.
The absolute maximum key size possible, whether defined through this command or calculated from the keys specified, is 320 characters.
| |
MAX REC COUNT n
For case structured databases, this specifies the default MAX REC COUNT for individual record definitions. The overall MAX REC COUNT sets the default maximum of a record type for any one case in the database. The number specified for an individual record type may be larger than specified here. The default value is 100.
For caseless databases, use the command N OF RECORDS to specify the maximum number of records that the database can hold.
| |
MAX REC TYPES nSpecifies the maximum number of different record types that can be defined in the database. The default value is 30.
No record type number can exceed the value specified on the MAX REC TYPES command. For example, if MAX REC TYPES is 10, a record type 11 is not allowed, even if there are fewer than ten record types defined.
This number affects the size of the CIR. Space is reserved in the CIR for counts for as many record types as defined in MAX REC TYPES. The case level MAX REC COUNT determines how much space is held for the count of any record type as yet unspecified. For example, if there is a MAX REC TYPES of 30 and a MAX REC COUNT of 100, 30 bytes are reserved for record counts in each CIR. With a MAX REC TYPES of 100 and a MAX REC COUNT of 1,000,000, 400 bytes are reserved.
Changing this number requires a database UNLOAD / RELOAD once data has been loaded.
| |
N OF CASES nSpecifies the maximum number of cases, n, that can be entered in the database. The maximum number of cases is an integrity constraint that limits the number of cases that can be held in the database
The N OF CASES is an absolute value; it cannot be increased without doing an UNLOAD / RELOAD and so specify the value carefully to allow for the maximum number of cases ever wanted in the database.
N OF CASES is multiplied by the RECS PER CASE to establish the total number of records the database can handle. This limit cannot be exceeded and can only be changed with an UNLOAD / RELOAD.
There is no overhead with specifying a large value, the only constraint is that total number of records is a number that can be stored in one integer. This number is 2,147,483,648.
The default N OF CASES is 1000.
Not valid for caseless databases.
| |
N OF RECORDS nSpecifies the maximum number of records, n, that can be entered in a caseless database.
The N OF RECORDS is an absolute value; it cannot be exceeded and so specify the value carefully to allow for the maximum number of records ever wanted in the database. This limit can only be changed with an UNLOAD /
RELOAD.
There is no overhead with specifying a large value, the only constraint is that total number of records is a number that can be stored in one integer. This number is 2,147,483,648.
The default N OF RECORDS is 1,023,000.
Not valid for case structured databases.
| |
READ SECURITY (leveln) password ....
Establishes the read security levels and associated passwords. There are 31
levels of security, from 0 (zero), the lowest, to 30, the highest. Repeat the
complete specification for each password. Database passwords are a SIR/XS name and
must conform to the naming rules. For example
READ SECURITY (1) CLERK (2) SUPER (3) MANAGER
When a user logs in to the database, they specify a Read Security Password. If this matches a password in this list, then they are assigned that security level. If they do not login with a valid read security password, they are assigned level zero.
If security passwords are not defined, anyone who logs on to the database is assigned level 30 (database administrator) read permission.
If a security level 30 password is not defined, anyone who logs on with the highest level password to the database is assigned level 30 read permission.
One level is associated with one password. That is, there may be a password for level 1, a password for level 2, and so on. There cannot be two passwords for the same level. Read access at a particular level grants read access for all lesser levels.
A security level and associated password must be defined before that security level can be specified on a record type or variable.
| |
RECS PER CASE nSpecifies an average number of records per case. The default is 1023.
This is used to calculate the total number of records in the database.
The product of
N OF CASES multiplied by RECS PER CASE forms an upper
bound on the total number of records (not including CIRs) that can be stored in
the database.
The default N OF CASES is 1000 that means that 1,023,000
is the default total number of records for a database.
This can be updated once records have been entered without an
UNLOAD /
RELOAD.
This command has no meaning for caseless databases.
| |
RECTYPE COLS n[,m]When using Batch Data Input utilities, the input file can contain records of different types and an input record type is identified by its record type number. The record type number is an integer and must appear in the same position on all input records regardless of record type.
RECTYPE COLS specifies the columns that contain the record type number. 'n' specifies the start column 'm' specifies the last column. If the record type is in one column, i.e. MAX REC TYPES is less than 10, just specify the start column.
The columns specified must be within the range specified on the MAX INPUT COLS command. The record type number must be on the first line of any multi-line input records. (If records on input files are all of one type, the RECTYPE= clause can be used for the batch data input run and the
record type number omitted.)
Make the number of columns large enough to hold the value of the maximum record number. For example, if up to 99 record types are allowed, specify two columns.
The default is columns 79 and 80.
| |
SYSTEM SECURITY readpw, writepw
Specifies the passwords associated with the SYSTEM SECURITY
LEVEL, if specified. Log on to the database with these passwords to
access the system utilities. Currently, only
UNLOAD FILE is
restricted by the SYSTEM SECURITY LEVEL and therefore this
command is not usually specified.
Logging on with a password that is associated with the system security level gives access to all functions.
N specifies the security level at which a user can perform a set of DBA-only
commands. Log on to the database with the write password associated with this
level to access the system utilities. The default system security level is 30.
Currently, only
Names temporary variables for use in computations during entry of data with the Batch Data Input utilities. Computations include
Temporary variables are not stored in the database.
Establishes the write security levels and associated passwords. There are 31
levels of security, from 0 (zero), the lowest, to 30, the highest. Repeat the
complete specification for each password. Database passwords are a SIR/XS name and
must conform to the name format. For example:
When a user logs in to the database, they specify a Write Security Password.
If this matches a password in this list, then they are assigned that security
level. If they do not login with a valid write security password, they are
assigned level zero.
If security passwords are not defined, anyone who logs on to the database is
assigned level 30 (database administrator) write permission.
If a security level 30 password is not defined, anyone who logs on with the highest level password
to the database is assigned level 30 write permission.
One level is associated with one password. That is, there may be a password for
level 1, a password for level 2, and so on. There cannot be two passwords for
the same level. Write access at a particular level grants write access for all lesser levels.
Only applies to batch data input
Specifies the criteria for accepting records using the Batch Data Input
utilities. When the logical expression is
Note:
Specifies string variables that are held as categorical integers and defines the
set of string values that can be input for the variable.
The values in the value list are each enclosed in single quote marks (') and the
list for a variable is enclosed in parentheses. Specifications for multiple
variables may be separated with a slash (/) for readability.
Within the database, categorical variables are held as integers that are the
position of the string in the value list. For example:
The variable
When entering data into a
Specifies variables as character. n may be from 1 to 4094.
Example:
Only applies to batch data input
Note:
Case and Key variables cannot be computed.
Declares a list of variables that are Control variables for the
SYSTEM SECURITY LEVEL
SYSTEM SECURITY LEVEL nUNLOAD FILE is restricted by the SYSTEM
SECURITY LEVEL and therefore this command is not usually specified. TEMP VARS
TEMP VARS varlistCOMPUTE,
RECODE,
IF,
ACCEPT REC and
REJECT REC commands. WRITE SECURITY
WRITE SECURITY (leveln) password ....WRITE SECURITY (1) CLERK (2) SUPER (3) MANAGER ACCEPT REC IF
ACCEPT REC IF (logical expression)TRUE, the record is entered into
the database. Multiple ACCEPT REC IF commands can be defined
to specify multiple acceptance criteria. If a record passes any one test, it is
accepted. If ACCEPT REC IF is specified, all records that do
not pass a test are rejected.ACCEPT REC IF (AGE GE 16 AND LE 65)ACCEPT RECORD IF cannot be specified in a CIR definition. If an ACCEPT RECORD IF refers to a common variable then
it must appear in the record schema where that common variable is referenced rather than
at the CIR level. CAT VARS
CAT VARS varname ('value' .... ) varname ('value' .... ) ....
CAT VARS SEX ('MALE' 'FEMALE' )
STATE ('AL' 'AK' ....'WY')
Specifies that the variable SEX is categorical. On input 'MALE' is converted to a 1, 'FEMALE' to a 2.STATE definition illustrates a list of abbreviations of
American states. On input 'AL' is converted to a 1, 'AK' to a 2, etc.CAT VARS, the string value is input, not the code. Note the difference to value labels, where a code is input and a string is
associated with the code. CHARACTER
CHARACTER*n varname ....CHARACTER*40 NAME COMPUTE
COMPUTE variable =
expressionCOMPUTE performs arithmetic or string transformations on
common, record or temporary variables in Batch Data Input as each record is
read. If the computed variable has not been defined, a new database variable is
created at the end of the record. It is recommended that computed variables are
defined with the appropriate command.COMPUTE cannot be specified in a CIR definition. If a COMPUTE refers to a common variable then it must appear in the record schema where that common variable is referenced rather than
at the CIR level. CONTROL VARS
CONTROL VARS variable ....TABULATE
procedure.
These variables must be numeric and must have either
Valid Values
or Variable Ranges defined.
By default, variables that have
Valid Values or Value Labels are Control Variables. All other numeric variables
are Observation Variables, that is variables with continuous values. DATA LIST
DATA LIST [(num-lines)]
[line-no] varname [from-column [- to-column]]|[*] [(type)]
[line-no] varlist [from-column [- to-column]]|[*] [(type)]....
Defines the variables and input format for a record.
You can either use the DATA
LIST or the
VARIABLE LIST /
INPUT FORMAT to define the
record.
The definition consists of the name, batch data input column locations and data type for each variable. The sequence of the variables determines the order in TO lists and the sequence of the variables wherever they are referenced, regardless of the physical order on the batch data input record.
If the batch data input record requires more than one physical record or line, then the num-lines defines the number of lines which make up the complete record and the line-no defines which line each variable is on. When the input record is only one line, omit the number of lines and line number. The line-no can be omitted for any subsequent variables on the same physical input record.
The from-column determines the start position of the variable.
The to-column specifies the ending position for variables that are longer than one column.
Define the data type of each variable as follows:
If the type is omitted, the default is floating point with zero decimal portion.
Example:
Specifies that previously defined character variables are date integers with a given date format.
Date formats may be specified directly on the
Multiple variables in the same format can be defined with one format specification; additional specifications may be separated by slashes for readability.
See date formats for a complete description of date formats.
Specifies that the text following the command is commentary. This text is stored
within the database and can be printed using the utility
When
Specifies the end of the commands for a record set. If it is not specified, the end of the commands,
Only applies to batch data input
Assigns the result of an
expression to a variable if the
logical condition is true.
Multiple variables can be assigned values on a single condition. Variables referenced must be within this record or must be common vars. If the computed variable has not been previously defined, it is added to the defined database variables. For example:
Note:
Specify a data type, size and format for each variable on the
An input file may have multiple lines of data for each database record. Lines in an input record may be any length up to the length specified on
Separate each format specification by a comma or a space. If the format specifications require more than one line, continue the specification on the next line leaving column one blank.
Specify a single format and a repetition factor for multiple variables with the same format or groups of variables with the same format. To repeat a format, specify the number of times to repeat it, followed by either a single format or a group of formats enclosed in parentheses. For example:
The
The first repeating format (10I2) defines the first 10 variables that results in
Enclose the whole format specification in parentheses. The individual format
specifications are as follows:
This defines two alphanumeric variables.
This example defines three variables for a record type.
Specifies variables as integer that can hold positive or negative numbers. n
may be 1, 2 or 4 and refers to the internal storage size in bytes. 1 byte holds
numbers up to 123; 2 bytes holds numbers up to 32,763; 4 bytes holds numbers up
to 2,147,438,643.
If this is subsequently defined as a
Example:
Defines the keys for the record. Any record type that has more than one single physical record per case on a case structured database and every record type with more than one single physical record in a caseless database must have a key.
The
The key fields must appear in the
Key field variables cannot be created or modified by
The sort specification applies to each variable individually.
Example:
For a case structured database this command specifies the maximum occurrences of a record type for any one case in the database. If a value is not specified for a record type, the
Counts are kept for each record type in a case in the CIR for that case. They are stored as 1, 2, or 4 byte integers depending on the count specified. A count of less than 124 takes 1 byte, less than 32,763 takes 2, and larger numbers take 4. If
For caseless databases,
A
I
Fn
Dn
DATE
DATE and the date format, all enclosed in parentheses. See date formats for a complete description.
TIME
TIME and the time format, all enclosed in parentheses. See time formats for a complete description.
DATA LIST (2)
1 ID 1 - 4 (I)
POSITION 6 - 7 (I)
STARTDAT 8 - 15 (DATE,'MMIDDIYY')
STARTSAL 17 - 20 (F2)
DIVISION 21 (I)
2 NAME 6 - 30 (A)
GENDER 31 (I)
MARSTAT 32 (I)
SSN 33 - 43 (A)
If a varlist is specified, (that is either a list of variable names or a
list in the form varname to varname), multiple variables, all of the same size
and type, can be defined. The columns specified to contain these variables must
be evenly divisible by the number of variables in the list. DATE VARS
DATE VARS varname .... ('date_format') varname .... ('date_format') ....DATA LIST or
INPUT FORMAT commands.
If using DATE VARS, the variable is specified as a character string on the DATA LIST or INPUT FORMAT commands. DOCUMENT
DOCUMENT textLIST
SCHEMA.DOCUMENT is placed within record definition commands, it
becomes part of the definition for that record type.DOCUMENT text cannot be partially modified. To update the text, run the command with new text. The new document text completely replaces the old. END SCHEMA
END SCHEMASTART TASK or END TASK or the start of a new RECORD SCHEMA terminates definition of the record type. Any other commands are treated as record definition commands. IF
IF (logical-condition) varname = expression; ....IF (JOBCODE = 1) REVDATE = TODAY(0) + 365IF cannot be specified in a CIR definition. If an IF refers to a common variable then it must appear in the record schema where that common variable is referenced rather than at the CIR level. INPUT FORMAT
INPUT FORMAT (format specifications)INPUT FORMAT is associated with, and immediately follows, the VARIABLE LIST command.VARIABLE LIST.MAX INPUT COLS.
Specify a slash (/) to indicate the start of the second and subsequent lines of data. The slash can be used to skip one or more lines of an input record.VARIABLE LIST VAR01 TO VAR50
INPUT FORMAT (10I2, 20(I1,I4))
VARIABLE LIST with the TO format defines 50 integer variables named VAR01, VAR02, ... VAR50.VAR01 to VAR10 as two digit integers.
The repeating group of formats, 20(I1,I4), defines 20 sets of two
alternate variables. This results in VAR11, VAR13 and subsequent
odd numbered variables as one digit integers (I1) and VAR12, VAR14
and subsequent even numbered variables as four digit integers (I4).Fw.d or Dw.d
VARIABLE LIST TEMP SALARY
INPUT FORMAT (F5.3, D8.2 )
Iw
VARIABLE LIST STATUS, AGE
INPUT FORMAT ( I1 , I2 )
Aw
VARIABLE LIST NAME ADDRESS
INPUT FORMAT ( A25 , A40 )
nX
VARIABLE LIST NAME ADDRESS
INPUT FORMAT ( A25 , 4X , A40 )
NAME occupies positions 1 through 25 of the input record. 4X skips the next 4 columns (after NAME). ADDRESS is 40 characters long beginning in position 30.Tn
VARIABLE LIST NAME ADDRESS PHONE AREACODE
INPUT FORMAT (T3,A20,T25,A40,T72,A12,T72,A3)
This defines four string variables using the T operator to locate the beginning of each variable. Note that the variables PHONE and AREACODE both begin in column 72.DATE 'date format'
TIME'time format'
VARIABLE LIST NAME BIRTHDAY BIRTHTIM
FORMAT (A25, DATE 'MM/DD/YYYY', TIME 'HH:MM')
NAME is in the first 25 positions; BIRTHDAY is in the next ten positions and is a date variable; BIRTTIM is a time variable. The first two characters are Hours (24 hour clock), the third character is a separator and the last two are minutes. INTEGER
INTEGER*n varname ....SCALED VAR, the internal
integer must be able to store the significant digits needed for the number. For
example if Scale (-2) is specified, the largest number that can be held in I*4
is 21,474,386.43
INTEGER*4 SALES VAR1 to VAR5 KEY FIELDS
KEY FIELD[S] varname [(A|D|I)] ....KEY FIELD command must be before the DATA LIST or VARIABLE LIST.DATA LIST or VARIABLE LISTCOMPUTE, IF or RECODE commands.(A) specifies ascending sort order - this is the default.
(D) specifies descending sort order.
(I) specifies this is an Auto Increment key. When records with an auto increment key are created, the value of the specified key is tested. If the creation process sets this key value to zero, then the system automatically finds the last occurrence of the record type in the case and takes the value from that record, increments this by 1 and uses this value as the key. If a record of this type does not exist, the value 1 is used as the key. Auto increment keys must be integer variables. If a key is an auto increment key, it must be the final key in a record type.
CASE ID ID
RECORD SCHEMA 3 REVIEW
KEY FIELDS POSITION REVDATE (D)
VARIABLE LIST ID POSITION REVDATE RATING NEWSAL IDSUPER
INPUT FORMAT (I4 I2 DATE('MMIDDIYY') I2 F8.2 I4)
MAX REC COUNT
MAX REC COUNT nMAX REC COUNT from the database specification is used. The number specified for a record type may be larger or smaller than specified at the database level. The default value is 100.MAX REC COUNT is modified after data for that record type has been loaded, and the new number takes the same size integer, restructuring is unnecessary. If a larger size integer is needed, the database must be restructured. MAX REC COUNT specifies the maximum number of records of this type that the database can hold. The default value is 1,023,000. This limit cannot be exceeded but can be changed without a database restructure. There is no overhead with specifying a large value, the only constraint is that total number of records is a number that can be stored in one integer. The total number of records allowed is 2,147,483,648. MISSING VALUES
MISSING VALUES varname (value1 [value2 [,value3]] ) ....
varlist (value1 [,value2 [,value3]] ) ....
Specifies up to three values that are missing values for the variable(s).
Missing values are excluded from
statistical procedures and
functions. When the
variable is input or modified, and one of the specified values is input, the
appropriate missing value is set.
The value can be a numeric constant, a string constant, or the keyword
Any variable can be missing and has a system missing value. When a record is
written to the database, a variable is assigned the system missing value when it
cannot be assigned a legal value or a specified missing value. This happens
when:
Specify any missing values for string, categorical, date and time variables as strings. If the variable is longer than the missing value, then the variable is set to missing values if the leftmost characters match the specified missing value.
If a string is read into a date or time, that string is first checked to see if it is a missing value string for the target variable. If it is then a missing value is stored. Any string can be defined as missing - it need not be a valid date. If the missing value is a valid date string then assigning that string to the variable naturally results in a missing being stored. However assigning the numeric date value corresponding to the missing string stores a valid date.
Specify any missing values for scaled variables as the unscaled value with the decimal point specified where necessary.
Example:
Specifies variables that the
Specifies variables as real. n may be 4 or 8.
Example:
Defines the default minimum security levels required for reading (rlevel), or
writing (wlevel), any variable in the record. The read and write levels are
integers between 0 (zero), the lowest and 30, the highest.
This command sets the minimum
The default is 0 level security.
Example:
The variables in this record type can be read by anyone logged in with a read
security password that has level 10 security or higher. Write access is
restricted to personnel logged in with a write security password that has level
30 security.
Only applies to batch data input
The
Old values not specified in the recode specification are not affected when recoding variables into themselves.
Old numeric variables can be recoded into new string variables, or vice versa. When recoding into new variables, if the data types of an old variable and new variable are the same, unrecoded old values are stored unchanged in the new variable after data editing checks are performed. If the data types of the old and new variable are different, unrecoded old values are stored as missing values in the new variable.
Note the sequence of the variables in the variable specification and the sequence of values in the recode specification:
Note:
Begins the set of commands to define a database record. This defines the name and number of the record and is a required command to define a record type. Name is optional for existing record types;
if specified and different to the existing name, the record name is changed.
The label is optional but if specified then the name is required. Specify up to 78 characters enclosed in quotes. e.g.
It is good practice not to specify lock on the record schema statements except when the change is expected to lock the record and you are prepared to do a restructure.
A restructure may be required when modifying the key structure definition of a record type that already has data loaded into it.
Only applies to batch data input
Defines criteria for rejection of records during Batch Data Input.
When the condition is true,
the record is rejected and not entered into the database.
Multiple
The alternative method of specifying consistency criteria is with
The record must pass all specified tests before being added to the database.
Note:
Specifies that the previously defined integer variables are scaled.
The full, unscaled number, including the decimal point where necessary, is specified whenever the number is input by the user.
The scaled number is expected on batch data input.
Examples:
Certain definitions can be supplied locally. The
Optionally the record variable can have one name and can refer to a standard variable with the
Declares string variables as times. See time formats for a complete description of time formats.
Example:
The specified valid values are checked whenever a variable is input or modified.
If a value is assigned to the variable that is not in the
The varlist may be a specific list of variables or may be in the format
Example:
Enclose each value for a variable in parentheses. The value may be numeric or character depending on the variable type. Enclose character strings in quotes. The keywords
The label is up to 78 characters long. It is good practice to enclose the label in quotes as this is a character string. If the label contains slashes or brackets then the label must be enclosed in quotes.
The varlist may be a specific list of variables or may be in the format
Example:
Names the variables on the input record for a given record type. Together with the
The sequence of the variables on the
Separate variable names by spaces or commas. To continue the list on multiple lines, leave column one blank on each subsequent line.
Example:
The documentation can extend over multiple lines, each up to 254 characters. There are no restrictions on characters and the format of lines is maintained. Each continuation line must leave the first column blank. Note that because documentation can contain any characters, the command must be specified separately for each variable - it cannot be continued for multiple variables.
Example:
Example:
Specifies the range of values for a variable. The ranges defined for a variable are checked whenever that variable is input or modified. Input values outside the specified range are set to undefined.
Example:
BLANK. If BLANK is specified as a missing
value for a numeric variable, then a blank field on input results in a missing
value, otherwise a blank numeric field is translated to zero.
MISSING VALUES ID POSITION DIVISION (BLANK)
STARTDAT ('01/01/01')
OBSERVATION VARS
OBSERVATION VARS varname ....TABULATE procedure uses as observation variables. An observation variable is one that is aggregated rather than treated as a control. By default, variables that have VALID VALUES or VALUE LABELS are Control Variables. OBSERVATION VARS makes these observation variables. REAL
REAL*4 varname ....
REAL*8 varname ....
REAL*8 SALARY REC SECURITY
REC SECURITY rlevel , wlevelVAR SECURITY for each variable
in the record type. Individual variables within a record type can be assigned
higher security levels using the VAR SECURITY command.REC SECURITY 10,30 RECODE
RECODE variable specification (recode specification)RECODE changes the values of a string or numeric variable into new values. A variable can be recoded into itself or the content of the original variable can be left unchanged and a value assigned to another variable.RECODE has two parts the Variable specification and the Recode specification.Variable Specification
The variable specification takes four forms:
Recode Specification
A recode specification follows the variable specification and consists of a number of clauses, one per new value to assign. Enclose each clause in parentheses. These can take a number of forms:
THRU oldvalue2 = newvalue) - recodes a
range of values to a single new value. The keyword LO can be
used as oldvalue1. This selects the lowest possible value as the start of the
range. The keyword HI can be used as oldvalue2. This selects
the highest possible value as the end of the range.MISSING = newvalue) -
Specifies that
all missing values are recoded.MISSING(0 | 1 | 2 | 3) = newvalue) - Specifies
that the first, second or third missing value is recoded. Zero (0) recodes
undefined values.UNDEFINED = newvalue) - Specifies
that undefined is recoded. This is the same as MISSING (0).BLANK = newvalue)
- Specifies that
BLANK values are recoded. This can only be specified for
numeric variables that have missing values specified as blank.ELSE = newvalue) - Specifies a value used to
recode if the variable is not recoded in any other category.
Newvariable = Oldvariable (Oldvalue = Newvalue)
Recode Examples:
RECODE A (1,3,5,7,9=1)(2,4,6,8=2)
This sets A to 1 if it is odd, 2 if it is even, and leaves zero and missing
values as is.RECODE B = A (1,3,5,7,9=1)(2,4,6,8=2)
This sets B to 1 if A is odd, 2 if A is even and zero if A is zero. If A is
missing, B is set to the value of A (whether this is a missing value for B or
not). A is unchanged.RECODE B = A (1,3,5,7,9='O')(2,4,6,8='E')
This sets B (a string variable) to 'O' for odd values of A, 'E' for even values
of A and to the value of A if A is missing or has any other values.RECODE STATUS = AGE (LO THRU 18 = 1) (19 THRU 21 = 2)
(22 THRU 65 = 3) (66 THRU HI =4)
This sets STATUS depending on the AGE
of the subject. STATUS is 1 for ages 18 or under,
2 for ages 19 thru 21, 3 for 22 to 65 and 4 for over 65.RECODE STATUS1 to STATUS10 = TEST1 to TEST10(1 THRU 49=1)
(50 thru 99=2)
This sets up ten status fields depending on the result of 10 tests.RECODE cannot be specified in a CIR definition. If a RECODE refers to a common variable then it must appear in the record schema where that common variable is referenced rather than
at the CIR level. RECORD SCHEMA
RECORD SCHEMA rectype [,name ['label']]
[LOCK]
[NOOLD]
[NONEW]
This is a DBA only command.
RECORD SCHEMA 3 OCCUP 'Position Details'
LOCKLOCK keyword is omitted and a lock condition occurs. If the record type is locked, an UNLOAD /
RELOAD is required. Lock
conditions occur when:
If the LOCK parameter is omitted and changes are specified that would cause a locked record type, a warning is issued and the schema changes do not take place. This means that a restructure is only done when expected.
NOOLDNONEW REJECT REC IF
REJECT REC IF (logical condition)REJECT REC IF commands can be specified.ACCEPT REC IF.REJECT RECORD IF cannot be specified in a CIR definition. If a REJECT RECORD IF refers to a common variable then it must appear in the record schema where that common variable is referenced rather than at the CIR level. SCALED VARS
SCALED VARS varname (power) ....SCALED VARS are stored in the database as integers. This saves space and can be more accurate for fixed format numbers since it avoids the inherent inaccuracies of floating point representation.POWER is a positive or negative number representing the power of ten used to scale the values.
DATA LIST VAR1 1-3 (I)
VAR2 4-11 (I)
SCALED VARS VAR1 (6)
VAR2 (-2)
This declares two variables as scaled variables. When used in a program:
COMPUTE VAR2 = 345.67 |(34567 is stored)
COMPUTE VAR1 = 1000000 |(1 is stored)
COMPUTE VAR3 = VAR1 * 3
WRITE VAR3 |(3000000 is printed)
If a batch data input record has 123 in positions 1 to 3 and 00001234 in positions 4 to 11, then VAR1 equates to 123,000,000 and VAR2 to 12.34. STANDARD SCHEMA
The schema command STANDARD SCHEMA is similar to a RECORD SCHEMA command in that it signifies the start of a set of variable definitions. The set is ended with an END SCHEMA command. Variables are defined using a DATA LIST command together with any of the normal variable definition commands such as MISSING VALUES, VALUE LABELS or VAR RANGES. e.g.
STANDARD SCHEMA
DATA LIST
POSITION * (I1)
SALARY * (I2)
SALDATE * (DATE'MMIDDIYY')
VAR RANGES POSITION (1 18)
SALARY (600 9000)
VAR SECURITY SALARY (30,30)
MISSING VALUES POSITION TO
SALDATE (BLANK)
VALUE LABELS POSITION (1)'Clerk'
(2)'Secretary'
.............
VAR LABEL POSITION 'Position'
SALARY 'Salary'
SALDATE 'Date Salary Set'
END SCHEMA
Once a variable has been defined in the standard schema it can be referenced in any normal record definition with the STANDARD VARS command. The benefit of this is that coding does not have to be repeated for the variable when it occurs in multiple records. Further, if the standard definition details are updated (such as value labels), the change is reflected in all records referencing the standard.
Note that the extended batch data input processing definitions of ACCEPT REC,REJECT REC,COMPUTE,IF and RECODE are not specific to a variable and thus cannot be specified as standard and copied in.
STANDARD VARS
STANDARD VARS varname [AS stdvarname]
The STANDARD VARS command names a variable or list of variables that have previously been defined as part of this record (on the DATA LIST). This includes all the standard definitions for the variable as part of this record without the need to respecify these definitions. If these definitions are changed for the standard variable, all derived record definitions are updated.VAR SECURITY, VAR LABEL and VAR DOC can be specific to the variable in this record type and override any specified as standard. All other definitions such as MISSING VALUES, VALUE LABELS etc, are taken from the standard definition.AS keyword. e.g.
RECORD SCHEMA 1 EMPLOYEE
DATA LIST
ID 1 - 4 (I2)
NAME 6 - 30 (A25)
GENDER 31 (I1)
MARSTAT 32 (I1)
SSN 33 - 43 (A11)
BIRTHDAY 44 - 51 (DATE'MMIDDIYY')
EDUC 52 (I1)
NDEPENDS 53 - 54 (I1)
CURRPOS 55 - 56 (I1)
STANDARD VARS CURRPOS AS POSITION
If an existing variable is modified to be a standard variable, any local definitions are overwritten. Submitting local definitions for components of the schema that are derived from the standard is treated as an error. To change a variable from a standard definition to be a normally defined variable is not possible; delete the standard variable and add a new variable (with the same name if necessary).
TIME VARS
TIME VARS varname .... (time format) varname .... (time format) ....
TIME VARS ELAPSED ('HH:MM')
MIN1 to MIN10 ('MM:SS')
VALID VALUES
VALID VALUES varname (value, .... )
varlist (value, .... ) ....
Defines the set of valid values for a variable. Valid values can only be specified for numeric variables. (Use the
CAT VARS command to create a list of valid values for string variables.)VALID VALUES list or in the
MISSING VALUES
list, the value is not stored and the variable is set to undefined.VARA to VARX that specifies all the numeric variables between the named variables (listed in sequential order) in the record schema.
VALID VALUES JOBCODE (1,3,4,5)
TEST1 to TEST9 (1,2,3,4,5,9)
TYPE KIND REASON (1,2,3,9) VALUE LABELS
VALUE LABELS varname1 (value1) 'label1'
(value2) 'label2' ....
varlist (value1) 'label1' .... ....
Specifies labels for particular values of a variable.UNDEFINED and BLANK can be used as a value to assign labels to the system missing value (undefined) or blank missing values.VARA to VARX that specifies all the numeric variables between the named variables (listed in sequential order) in the record schema.
VALUE LABELS GENDER (1) 'Male'
(2) 'Female'
MARRIED ('Y') 'Married'
('N') 'Not Married'
TEST1 to TEST9 (1) 'Passed'
(2) 'Failed'
VARIABLE LIST
VARIABLE LIST varname1 varname2 ....INPUT FORMAT, this describes the input record. There is a one-to-one correspondence between the variables on the VARIABLE LIST and the format specifications on the INPUT FORMAT.VARIABLE LIST determines the order in TO lists and the sequence of the variables wherever they are referenced, regardless of the physical order on the batch data input record.VARIABLE LIST ID JOBCODE REVDATE SALARY
INPUT FORMAT (I4,I2,DATE'MM/DD/YYYY',I4) VAR DOC
VAR DOC varname text
text line 2
text line .....
Specifies documentation for a single variable.
VAR DOC ID This is the main identification for participants in the study.
The code is in two parts separated by slash '/' - the first part identifies the institution, the second the individual.
VAR DOC DOB ...........................
.........................................
VAR DOC VAR1 ...........................
VAR LABEL
VAR LABEL
varname1 'label'
varname2 'label' ....
Specifies a label for variables. The label is a string up to 78 characters. It is good practice to enclose the string in quotes. This is used by SIR/XS instead of the variable name wherever it is appropriate, for example, on report headings, screen layouts, etc.
VAR LABEL ID 'Identification Number'
POSITION 'Position Level'
STARTDAT 'Starting date'
STARTSAL 'Starting salary'
DIVISION 'Division'
In this example, "Starting date" is printed or displayed instead of "STARTDAT" and "Starting salary" instead of "STARTSAL". VAR RANGES
VAR RANGES varname (min,max) ....
VAR RANGES POSITION (1 18)
STARTSAL (10000 90000)
DIVISION (1 3)
STARTDAT ('01/01/00', '01/01/06')
If both VAR RANGES and VALID VALUES are
specified, both specifications apply. Only values consistent with both specifications are allowed into the database. Normally, specify only one of these.
| |
VAR SECURITY varname (rlevel, wlevel) ....Defines security level values for reading and writing individual variables.
Rlevel and wlevel are integers between 0 (zero) the lowest, to 30 the highest. The VAR SECURITY assigned to an individual variable must be higher than the security level assigned to the record through REC SECURITY. (Assigning a lower level is possible but has no effect.)
Example:
RECORD SCHEMA .... REC SECURITY 0 5 . VAR SECURITY SALARY (6 10)In this example, anyone can read the data in this record type, but only users logged in with passwords at level 5 or above for write security can write data. The variable
SALARY is made more secure, since it requires that read access is at level 6 or above and write security at least at level 10.
| |
CREATE [UNIQUE] DBINDEX index_name
ON [database.]recname
(var_name [ASC|DESC] [UPPER] [, ...] )
CREATE DBINDEX creates the index structure. If the command is part of a schema definition, it must follow the complete definition for the record type referenced. There may be multiple indexes for a given record type.If the record type already contains data, the index is automatically built from the values of any existing records. As records are added, deleted or modified through any of the SIR/XS modules including VisualPQL, batch data input, Forms and SQL, the index is automatically maintained. VisualPQL and PQLForms are the only modules that process database records using secondary indexes.
Indexes are maintained across cases in a case-structured database and, if a record is retrieved using a secondary index, the appropriate case is automatically found. Index variables can be any record variable plus the case id. Common vars cannot be used in an index.
Indexes are rebuilt whenever the database is recovered i.e. from an Import or a Reload. The backups contain only the index definition not the index data.
UNIQUE
index_name
ON recname
var_name
Specify the keyword DESC to maintain the index in descending sequence of the variable.
Specify the keyword UPPER to use an uppercase representation of a character variable in the index.
By default, all record types in the database are listed. By default, all variables in the record type are listed. Each variable has a label that describes the variable and its position within the record. Positions are shown as Cnnn, Rnnn, or Tnnn where C stands for common variable, R for record variable and T for temporary variable. The nnn denotes the position of the variable within the record.
The exact format of the report depends on the options specified:
This is the default.
Examples: To list the name and labels of all the variables in record type 1 and the specified variables in record 3.
Example Output:
With the
With the
If no record types are specified and the CIR, STANDARD and DEFDB keywords are not used then the database definition and all record definitions are written (excluding passwords).
When modifying data types for variables that already have data stored in the
database, the data must be reformatted. Be careful if modifying a variable's data type. The following
table indicates the result of changing data types.
Var
When transferring values, ranges, missing values, valid values and all schema
definitions are checked and appropriately handled. Strings are truncated if they
cannot fit in the new definition. Floating point numbers are truncated if they
are converted to unscaled integers.
DROP DBINDEX
DROP DBINDEX index_name ON [database.]recname | ALL
DROP DBINDEX deletes either a specific index or all indexes for a database. SIR SCHEMA LIST
SIR SCHEMA LIST
[FILENAME= 'fileid']
[DETAILED|LABELS|REGULAR]
[LONG|SHORT]
[STRUCTURED]
[CIR]
[RECTYPES={recname|recnum} [(variable list)]... ]
Produces a listing of the current data definitions. The FILENAME specifies the output file. If this is omitted, the output is displayed in the scrolled output window.
DETAILED
lists all the information in the REGULAR listing, plus the value labels for each variable.LABELS lists just the variables and the variable label. This is the shortest form of the report.REGULAR lists the following information for each variable:SHORT
specifies that just the variable label (up to 78 characters) is printed and no headings or document text.LONG specifies labels and all document text. This is the default.STRUCTURED lists just case id, key fields and document text for each record type.CIR
lists all variable in the CIR. If CIR is specified, only the CIR is listed. Specify individual RECTYPES if these are required.RECTYPES specifies the name or number of individual record types to list and individual variables to list for each record type.
SIR SCHEMA LIST LABELS
RECTYPES = 1 , 3 (POSITION REVDATE RATING)
To list all the variables in all record types without value labels.
SIR SCHEMA LIST
1.1 LIST SCHEMA LONG 07/09/05 10:05:57
*** RECORD 1 (EMPLOYEE) DEFINITION ***
Record Type 1 contains demographic information.
There is one record per employee. The record contains
current position level and salary and the date
on which the salary was last changed.
MAXIMUM NUMBER OF RECORDS/CASE 1
NUMBER OF THIS TYPE IN FILE 20
LINES IN EACH INPUT RECORD 1
ENTRY USE COUNT 1
CASE IDENTIFIER: ID (A)
*** INPUT VARIABLE DEFINITIONS ***
C1. ID, Identification Number
INPUT ON LINE: 1
COLUMNS: 1 - 4
FORMAT: I4
DATA TYPE: I*2
MISSING VALUE: (BLANK)
R1. NAME, Name
INPUT ON LINE: 1
COLUMNS: 6 - 30
FORMAT: A25
DATA TYPE: A*27
MISSING VALUE: (BLANK)
R2. GENDER, Gender
INPUT ON LINE: 1
COLUMN: 31
FORMAT: I1
DATA TYPE: I*1
RANGE: 1/2
MISSING VALUE: (BLANK)
VALUE LABELS: (1) Male
(2) Female
WRITE SCHEMA
WRITE SCHEMA
[FILENAME= fileid]
[RECTYPES= rectype list]
[CIR]
[COMMON]
[DBDEF]
[FORMS]
[BOOLEANS]
[COMPUTES]
[LOCK]
[MASKPW]
[NOCOLS]
[NOINDEX]
[NOMAXKEY]
[NOTO]
[NOTXS]
[PASSWORD]
[PQLFORM]
[NOUPDATE]
[SUBROUTINE]
[SECURITY]
[STANDARD]
[VARLIST]
[VARSEQ]
Writes a text file containing the schema definition. This might be used to recreate the database without data or procedures or, if it is the schema for a single record type, as the starting point to update that record definition. If mass changes are required to a database definition, it is sometimes easier to create a schema file and use a text editor to do global changes rather than modifying individual records through menus.PQLFORMS parameter, this utility creates a default PQLForm.FORMS parameter, this utility creates a default old Form.FILENAMERECTYPES
CIRCIR is specified, then just the CIR is written.COMMON
COMMON if commands are to be produced for each common variable on every record where it occurs.DBDEF
FORMSBOOLEANS to use any ACCEPT REC IF or REJECT REC IF specifications to generate appropriate commands in the output form. Specify the keyword COMPUTES to use any COMPUTE and IF commands to generate appropriate commands in the output form.LOCK
MASKPW
NOCOLS
NOINDEX
NOMAXKEY
NOTO
TO. e.g.
MISSING VALUES NAME
TO NDEPENDS (BLANK)
Specify the NOTO keyword to suppress this shorthand and to list all variables individually.NOTXS
STANDARD VARS and RECORD SCHEMA 0 are not written and slashes are generated as separators.PASSWORDPQLForms
NOUPDATE for a read-only PQLForm. Specify the keyword SUBROUTINE for a PQLForm that compiles and saves as a sub-routine.SECURITYSTANDARD
STANDARD is specified, then just the STANDARD schema is written.VARLIST
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
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'
Resubmitting Whole Schema
If you specify a DATA LIST for a record schema that is already defined, then all existing definitions are ignored and the record is defined according to the new schema. Otherwise, RECORD SCHEMA modifies the definition. Modifications include labels and codes. You can add new variables and delete existing variables. You can modify a variable's data type, external format or position for batch data input.
T - Transfer value
C - Convert to new type
X - Convert to numeric if
string is a valid number
U - Convert to undefined To
String Cat Date Time Integer Real
From String T C C C X X
Catvar C T U U T C
Date C U T U T C
Time C U U T T C
Integer C T T T T C
Real C C C C C T