SQLTalk : SQLTalk Command Reference

SQLTalk Command Reference
This chapter contains the syntax, description, and examples of each SQLTalk command. It is organized alphabetically by command name.
SQLTalk command summary
Changes database encryption key, database encryption level, and page alteration protection level
Establishes an explicit connection by issuing a connection handle to a specific database.
Establishes an implicit connection to a database or establishes a cursor that is part of an implicit or explicit connection.
Disconnects an implicit connection to a database or disconnects a cursor that is part of an implicit or explicit connection.
ALTER COMMAND
Use this command to flag a stored command with an AUTORECOMPILE setting. If you set this flag to ON and the stored command later becomes invalid, SQLBase automatically recompiles the stored command the next time you EXECUTE it.
A stored command becomes invalid when you alter its associated table or tables, such as dropping an index or column. Even if the table change does not directly affect the stored command, SQLBase still invalidates the command. You cannot use the stored command until you replace or recompile it.
You only set AUTORECOMPILE for stored commands created by users. SQLBase automatically recompiles all system-generated stored commands itself.
The UNLOAD and LOAD commands preserve the AUTORECOMPILE status for all stored commands.
To immediately recompile the stored command without having to wait until the next EXECUTE, execute the SQLBase RECOMPILE procedure, instead of running ALTER COMMAND.
Clauses
SET AUTORECOMPILE
If this setting is ON and the stored command later becomes invalid, SQLBase automatically recompiles the stored command the next time it is executed, either by the SQLTalk EXECUTE command or by the SQL/API sqlret function.
When you create a stored command, the default AUTORECOMPILE setting is ON.
Example
ALTER COMMAND ADDNAMES SET AUTORECOMPILE ON;
See also
STORE
EXECUTE
ALTER DBSECURITY
This command changes the database encryption key, database encryption level, and page alteration protection level. You can checksum without setting the key. You can set security and set the key in any order, but if you set security, you must set the key.
Note: You must be logged on as SYADM and you must be connected to a database to perform this command.
Warning: Back up the database before executing this command. You can only perform this command when there are no other connected users.
This command changes the structure of the database. If a failure occurs during the command, you cannot recover the database. You cannot rollforward through this operation. Like a DROP DATABASE, this command deletes any transaction logs, regardless of the BACKUP LOGS setting.
Warning: You cannot rollforward past a change in the security configuration, including changing the server security password, the database page encryption, and database page alteration protection. If you try to do this, the restore stops just before the security change. After making a security change, you should restart your backup procedure by making a fresh backup of the database and its logs because previous versions of the database cannot be recovered beyond the security change. Therefore, do this after making a security change:
1. Shut down the server.
2. Perform an offline backup
OR
Start the server and perform an online backup.
To change the database key or the database encryption level, you must specify the old password in an ALTER KEY, SET KEY, or USING KEY clause.
Because some forms of this command can take a long time, SQLBase reports progress messages to the process activity window every 5,000 pages.
Decrypting and encrypting an entire database is a processor and disk I/O intensive operation. Performance of the server generally may be reduced during some forms of this operation.
Important: Since this is a SQL command, you can compile and execute this command through the SQL/API.
Clauses
SET KEY
Specify this clause to create a database encryption key for the first secure operation on the database.
Important: A new database does not have a database encryption key or a security encryption level and is compatible with non-secure versions of SQLBase. However, once you set a security key for a database, it is not compatible with non-secure version of SQLBase, even if the security level is set to none.
To enable security features, give this command:
ALTER DBSECURITY SET KEY TO 'newkey'
to create a database encryption key.
After this command executes, the database is security-enabled and cannot be accessed by a non-secure version of SQLBase. However, until you choose an encryption method (via the SET SECURITY clause), the database is not secure.
Follow the same rules for creating a database key that you follow for long identifiers such as column names (18 character maximum).
USING KEY
Specify this clause to set the ket to the key used in the previous secure operation.
ALTER KEY
Specify this clause to change the database encryption key.
SET SECURITY TO NONE/LOW/MEDIUM/HIGH
Specify this clause to set the database page encryption level.
This process can take some time depending on the size of the database.
Because this operation can take some time, you should back up the database first. If there is some type of failure during the encrypting process (such as a power failure), you cannot recover the database.
The None level does not encrypt data.
The Low level converts each database page using a cryptogram (each character is replaced with a different character using a non-trivial formula). This level discourages the casual observer using a file viewer such as a hex editor but is not intended to be very secure. However, it offers reasonably high-performance.
The Medium level is very secure and has little impact on performance.
The High level is the most secure, with some impact on performance. Due to various governments’ import and export regulations, this level of encryption may not be available in your version of SQLBase.
SET CHECK TO NONE/CRC/SHA
Specify this clause to set the database page alteration protection level.
With the None level of protection, anyone can change a page in a database and SQLBase cannot detect it. If data page encryption is used, it would be difficult to determine exactly what to change the data to, but it still could be done.
With the 16-bit CRC (Cyclic Redundancy Check) level of protection, SQLBase can detect most changes to the database. The CRC is stored in the page before it is encrypted, preventing alteration of the CRC as well.
The SHA (Secure Hash Algorithm) level of protection provides a digital signature for each database page. SHA keys are impossible to reverse and alteration is easily detected. This level provides substantially more protection than CRC.
Example
Set the database encryption key and page encryption level:
alter dbsecurity set key to key1 set security to high;
Set the database page alteration protection level:
alter dbsecurity set check to sha;
You can combine clauses in one command. For example, the commands in the previous three examples can be combined into one:
alter dbsecurity set key to key1, set security to high, set check to sha;
To turn off database page encryption for a database, enter a command like this:
alter dbsecurity using key key1 set security to none;
To turn off database page alteration protection, enter a command like this:
alter dbsecurity set check to none;
See also
ALTER EXPORTKEY
ALTER EXPORTKEY
This command changes the server security password used to encrypt databases. You use this command to export (BACKUP) and import (RESTORE) databases to and from another server with a different server security password.
Note: You must be connected to a database to perform this command.
For BACKUP DATABASE, SQLBase encrypts the database file with the specified server security password instead of the default server security password. For RESTORE DATABASE, SQLBase decrypts the database file using the specified server security password instead of the default server security password.
During a restore, SQLBase tries to decrypt the database using the current server security password. If that password cannot decrypt the database, SQLBase uses the password specified by the most recent ALTER EXPORTKEY command. SQLBase then re-encrypts the database using the current server security password. During a backup, SQLBase uses the password specified by the most recent ALTER EXPORTKEY command to encrypt the database.
When you disconnect (SET SERVER OFF) or the server shuts down, the password reverts to the default.
Important: Since this is a SQL command, you can compile and execute this command through the SQL/API.
Clauses
DEFAULT
The normal server security password.
‘newkey’
The temporary server security password.
Example
To backup a database that will be restored on a different server with a different server security password, first give an ALTER EXPORTKEY command, specifying the other server’s password:
alter exportkey to password2;
Then back up the database:
backup snapshot to d:\backup;
To restore a database from a server with a different server security password, first give an ALTER EXPORTKEY command, specifying the other server’s password:
alter exportkey to password2;
Then restore the database:
restore snapshot from d:\backup to island;
See also
ALTER EXPORTKEY
BACKUP
This command backs up a database, transaction log files, or both.
You can backup a database to a file with the following name:
database-name.bkp
Transactions that have been committed when the backup starts are included in the backup. Active transactions are not included.
For all options except SNAPSHOT, the following rules apply:
You do not need to be logged on as SYSADM to perform a BACKUP SNAPSHOT. This allows users other than SYSADM to backup the MAIN database.
Once you backup a database or log files to a directory, you can transfer the backup files to archival media and then delete them from the hard disk.
You can backup a partitioned database and restore it to a non-partitioned database, and vice-versa.
To perform the backup operation in segments, you must specify a control file databasename.BCF. This control file describes the location and size of the segments to which you want to restore your database. Read the Database Administrator’s Guide for details on the format of the control file.
Note that there is no need to explicitly provide the name of the file in the BACKUP command’s syntax. If a control file (databasename.BCF) is present in the directory specified in the TO and FROM clause of the BACKUP command, SQLBase performs a segmented backup operation. If a control file is not present, SQLBase backs up the database to a single databasename.BKP file.
Note the following when you use the BACKUP command:
If you do not include a segmented backup control file (databasename.BCF) in the directory that you specified in the TO and FROM clause of the command, an unsegmented backup file is created (if less than the 2 gigabytes limit).
SQLBase issues an error if the control file you specify does not have a minimum aggregate size to account for the total database size.
Read the Database Administrator’s Guide for details on setting the SIZE parameter for the control file.
Be sure that the directories you designate in the BACKUP command and in the control file have sufficient space. For backup commands with the snapshot option, also be sure that the directory specified in the command contains sufficient space for the backup of the log files involved in the operation.
To back up a database encrypted with a different server security password than the one currently set, you must first give an ALTER EXPORTKEY command. For more, read ALTER EXPORTKEY on page 2-39.
Warning: You cannot rollforward past a change in the security configuration, including changing the server security password, the database page encryption, and database page alteration protection. If you try to do this, the restore stops just before the security change. After making a security change, you should restart your backup procedure by making a fresh backup of the database and its logs because previous versions of the database cannot be recovered beyond the security change. Therefore, do this after making a security change:
1. Shut down the server.
2. Perform an offline backup
OR
Start the server and perform an online backup.
Clauses
DATABASE
Copies the database (.dbs) file from its current directory to the specified backup directory.
LOGBACKUP must be ON to perform BACKUP DATABASE. You should never back up a database without also backing up the log files with it.
LOGS
Copies unpinned log files from the current log directory (by default, the database directory) to the specified backup directory. Once this completes successfully, SQLBase deletes the log files that were backed up from the current log directory.
You should back up log files from different databases to different directories since their file names could conflict.
LOGBACKUP must be ON to perform BACKUP LOGS.
SNAPSHOT
Copies the database and the associated log files from their current directory to the specified backup directory. Since BACKUP SNAPSHOT backs up a single recoverable database, it is suggested that you perform this function into an empty directory.
BACKUP SNAPSHOT is the recommended way to backup a database and its log files. There is only one step (RESTORE SNAPSHOT) needed to bring a database and its log files to a consistent state.
Alternatively, the files produced by BACKUP SNAPSHOT can be restored and recovered with the individual commands RESTORE DATABASE, ROLLFORWARD, and RESTORE LOGS.
The SNAPSHOT option does not require the SYSADM logon and password. This means that other users besides SYSADM can backup the MAIN database by using BACKUP SNAPSHOT.
This command causes a log rollover which closes the current log file so that it can be backed up. This means that a RELEASE LOG command is not necessary.
If LOGBACKUP mode is turned on, the log file is pinned until it is backed up using BACKUP LOGS. The backup command unpins the log, not BACKUP SNAPSHOT.
You cannot perform a BACKUP SNAPSHOT while in Read-Only (RO) isolation level.
Note that BACKUP SNAPSHOT does not backup a single file; you must provide directory name, not a file name.
FROM database name
This clause specifies the name of the database.
If the FROM clause is omitted, the database is assumed to be the one currently connected to the active cursor.
TO directory name
This specifies the destination directory for the backed up files.
This backup directory pathname can refer to the client or the server computer. You can specify the pathname with the ON SERVER or ON CLIENT clauses.
If you omit the TO clause, the current directory on the client computer is used as the backup directory.
If the destination files of the backup already exist (and the PAUSE option is turned ON), you are prompted with the following message before the files are overwritten:
Backup file already exists.
Overwrite it (Y/N)?
It is a good idea to place the backup on a different drive or device to isolate it from failure of the drive on which the database is located.
ON CLIENT or ON SERVER
This specifies whether the destination directory for the backed up files is on the client or the server. The default is ON CLIENT.
Example
The following example assumes that you have already entered a SET SERVER command.
BACKUP DATABASE TO \DEMOBKUP;
DATABASE BACKED UP
RELEASE LOG;
RELEASE LOG COMPLETED
BACKUP LOGS TO \DEMOBKUP;
2 LOGS BACKED UP
See also
RELEASE LOG
RESTORE
SET SERVER
BEGIN CONNECTION
This command establishes a new connection to a specified database. When you supply the name of the database along with the username and password required for its access, this opens a connection handle which identifies the specified database. You can create cursors to perform specific SQL commands within the connection handle. A connection handle represents a single database connection and transaction.
If the databasename, username, and password are not specified, SQLTalk uses the databasename, username, and password of the current cursor connection.
The beginning of a new connection does not modify the current cursor. SQLTalk always maintains what it considers to be the current cursor, which is the cursor used for any SQL command execution. For details, read Chapter 3, Connection Handles of the SQL/API Programming Language Reference.
Clauses
connection name
Specify the connection name, which can be no more than eight characters in length.
databasename/username/password
This specifies the database to connect to.
Regardless of how many database connections have been established, there is only one current database and one current cursor at any given moment. The current database and cursor is the one to which a connection was established at sign-on or by the last CONNECT or USE command. If the database name is not specified in a BEGIN CONNECTION command, it is assumed that the current database will remain unchanged.
If you are connecting to the MAIN database, specify the name of the server where the MAIN database is instead of the database name itself.
 
username/password
Each valid user of the database is assigned a username (authorization ID) and password.
The BEGIN CONNECTION command can establish only one connection to the database through a username.
If you do not specify a username/password, the connection takes place with the same username/password as the current cursor. The current cursor is the one that was established at sign-on or with the last CONNECT or USE command.
Example
The following example creates a new connection:
BEGIN CONNECTION CH1 ISLAND/SYSADM/SYSADM;
See also
END CONNECTION
SHOW CONNECTION
BREAK
This command suppresses repeating values for a specified column within a group. It inserts a blank line when the column changes value or after the last row of a report has been displayed.
If you entered the COMPUTE command, appropriate totals and subtotals are computed and printed for each group of rows separated by blank lines.
Once you enter the BREAK ON command, all subsequent SQL queries are displayed with the specified breaks. To add or remove a break specification, you must enter a new BREAK OFF command.
Unlike the COMPUTE and COLUMN commands, the BREAK command is not cumulative.
To turn off breaks, enter the BREAK OFF command. The BREAK OFF command also removes all specified COMPUTEs.
Clauses
ON/OFF
Turns BREAK capability on or off.
REPORT
This specifies that a break occurs after the last row of the report. This is usually specified when an aggregate for the entire report needs to be computed (such as a grand total).
column list
The column list contains column-IDs separated by one or more blanks.
Column-ids can be either:
The breaks occur in the same order as specified in the column list.
SQLTalk does not check to see if a SQL query results in a sorted order that is the same as the specified break order. You must ensure that a subsequent SELECT command contains an appropriate ORDER BY clause.
Examples
The following command requests a break on the column DEPTNO.
BREAK ON 1;
SELECT DEPTNO, PROJNO, SUM(SAL) FROM EMP ORDER BY
DEPTNO, PROJNO;
The following command requests a break on the columns DEPTNO and PROJNO, and on the last page of the report.
BREAK ON REPORT 1 2;
SELECT DEPTNO, PROJNO, SUM(SAL) FROM EMP ORDER BY
DEPTNO, PROJNO;
See also
COLUMN
COMPUTE
SHOW REPORT
BTITLE
This command displays a title on the bottom of each page of a report. Once you have entered this command, the results of all subsequent SELECT commands are displayed with this bottom title.
You can change the bottom title by giving another BTITLE command or it can be turned off by giving the BTITLE OFF command. You can turn on BTITLE again with the BTITLE ON command.
This command is cumulative, meaning that once the title has been turned on, portions of it can be changed (such as adding a date or a page number).
Clauses
'footer string'
You must enclose the footer string in single quotes.
The string can consist of up to three separate substrings, each delimited by the vertical bar ( | ) character. The | causes the substring that follows it to be displayed on a new line (multi-line footer).
All the lines of the footer are automatically centered within the display width of the page. The display width of the page is specified by the SET LINESIZE command.
DATE
If this is ON, the current date appears on the bottom left hand corner of each page of the report. The date is displayed in Mon dd, yyyy format (such as April 25, 1996). The date is always displayed on the last line of the page, regardless of the number of lines in the bottom title.
PAGE
If this is ON, the current page number is displayed on the bottom right hand corner of each page of the report as 'Page n', where n is the page number. The page number is always displayed on the last line of the page, regardless of the number of lines in the bottom title.
Example
Shown below is a BTITLE command and the resulting title.
BTITLE 'COMPANY CONFIDENTIAL|***' DATE ON
PAGE ON;
See also
SET LINESIZE
SHOW REPORT
TTITLE
COLUMN
This command sets attributes that control the display format and break processing for a column in a report.
After you have entered this command, the results of all subsequent SELECT commands reflect the attributes that you specified.
You can specify more than one COLUMN command for a single column. The effect of this is cumulative, except that a new COLUMN command overrides an attribute that you specified in a previous COLUMN command.
An empty string specified as a pair of single quotes (' ') removes a previously-defined attribute for a column.
Clauses
column ID
This specifies the column ID. Column IDs can be one of the following values:
ALIAS OFF or ALIAS alias name
This specifies an alias name for a column. The alias is used as a column-ID.
The rules for naming an alias are the same as for SQL columns. An alias name must begin with a letter (A through Z and the special characters #, @, and $) and must not exceed 18 characters. The alias name must not be enclosed in quotes.
HEADING ‘column title’
This specifies the heading for a given column.
The heading can consist of up to three separate substrings, each delimited by the vertical bar ( | ) character. The | is a delimiter that causes the following substring to be displayed on a new line.
The width of a column heading cannot be greater than the line size (specified by the SET LINESIZE command). If any of the substrings in the column title are wider than line size, the extra characters are truncated at display time.
The heading string must be enclosed in single quotes ('').
To remove a previously-defined column heading and return to the default heading, define column-title-string to be two single quotes ('').
WIDTH OFF or WIDTH data width
Each column can have a width specified for the data. This is a number from 1 to line size.
If the data width is less than the specified or default width of the column heading, the display width of the column is the greater of the data width or the column-heading width.
If the column has a PICTURE specified for it, the data width is ignored.
If the data width is not specified, or if it is turned OFF, the display width is equal to the size of the data as stored in the database.
PICTURE ‘picture string’
This specifies the display format of the data in a column. The picture string is a character-string constant that describes the format that is used to display numeric and date columns.
SQLTalk supports free formatting in an output picture. Characters that represent numeric time and date elements are replaced on output with database values.
The PICTURE can be up to 40 characters in length.
Picture strings are enclosed in single quotes.
A picture specified for a character column is ignored.
Numeric Pictures
A numeric picture string must represent a valid number. For example, commas must be spaced three to the left of the decimal point and only one decimal point is allowed per number.
If the number of significant digits in the input exceeds the number of significant digits in the picture string, the number is not displayed. Instead, the string '**' is displayed, indicating an error.
If a number contains decimal digits but there are not decimal digits in the picture, the decimal digits are truncated.
The following table explains the picture characters that can be used for a numeric column.
Picture Character
Displays a comma in this position. Commas must be spaced three to the left of the decimal point.
Replaces a leading 0 in this position with a blank (zero-suppression). This symbol must be to the left of any digit specification in the picture string.
Displays a dollar sign in this position. It can appear at the beginning of a picture or it can be used as a floating character with the $symbol appearing adjacent to the most significant digit in a numeric field. The $ symbol cannot appear to the right of a 9, Z, or decimal point in a picture-string.
Displays a minus sign if the value is negative. The minus sign is displayed to the left of the value. If the value is positive, a blank space is displayed to the left of the data.
Displays data in scientific notation. The default width for a scientific notation string is 10 characters.
Examples of Numeric Pictures
COLUMN AMT PICTURE '$ZZZZ.99';
COLUMN AMT PICTURE '9999';
Date/Time Pictures
The standard output of a date/time value in SQLTalk is illustrated in the following diagram.
You can substitute slashes or spaces for the hyphens in the diagram above.
For a DATE data type, the time portion is omitted and for a TIME data type, the date portion is omitted.
For example, January 12, 1996, 3:15 PM would be output as
12­jan­1996 03:15:00 PM.
Date and time format strings must be enclosed in single quotes.
Note that the time can be added to any of the date pictures to give date and time, or the time picture can be used alone.
The following table explains the picture characters that can be used for a date/time column.
9
99
999
9999
99999
999999
A number with 1 to 6 digits that represents fractions of a second. Only the least-significant 6 digits are considered.
A backslash prevents substitution and forces the next character into the output from the picture. For example, the following picture:
Mo\mmy was born in YYYY
produces the output string of "Mommy was born in 1950" instead of "Mo04y was born in 1950."
If a date value is being formatted, time symbols such as HH or MI are not recognized. If a time value is being formatted, time symbols such as DD or MM are not recognized. All symbols are recognized for DATETIME (TIMESTAMP).
Examples of Date/Time Pictures
The date output for July 14, 1996 is shown below with corresponding picture clauses.
NULLS ‘replacement string’
This indicates that if the value of the column is null, it is displayed as a string of characters specified by replacement-string. Otherwise, the value is whatever is specified by the SET NULLS command (the default is blank).
The string must be enclosed in single quotes (' ').
If the replacement-string is specified as two single quotes (' '), this removes any previously-defined value for the replacement-string and returns it to the default.
ADJUST OFF/LEFT/RIGHT/CENTER
This indicates whether the data and column headings of a column are left-justified, right-justified or centered within the width of the column.
If the data is decimal, then centering means that the data is aligned on the decimal point and the decimal point is centered within the column width.
ADJUST OFF removes a previously-specified setting.
The default settings are:
DUP OFF/ON
This attribute applies only to columns specified in the BREAK command. If this is ON, the data in this column is displayed for each row, regardless of value.
The default is OFF, which means that the data in the column is displayed only when its value changes (when a break occurs on the column).
WRAP OFF/ON
If this is ON, column data is wrapped for up to five successive lines. No attempt is made to wrap at word boundaries.
This option is only valid for character columns.
PRINT OFF/ON
If this is OFF, the column results are not displayed even if the query returned the results successfully.
All columns to the right of the column are adjusted leftward on the display screen.
Turning PRINT ON or OFF does not change the column-IDs of the displayed columns.
SUBTITLE OFF/ON
This attribute applies only to columns specified in the BREAK command. If this is ON, the data in the column specified by the BREAK is not displayed in rows and columns, but instead on a separate subtitle line when its value changes (when a break occurs on the column). The subtitle line includes the column heading on the left of the data.
This attribute can be ON for more than one column, in which case the column data is printed on the subtitle line in the order the columns are specified in the select list of the query.
The default is OFF which means that the data is not printed on a subtitle line.
NEWPAGE OFF/ON
This attribute applies only to columns specified in the BREAK command. If this is ON, the report skips to a new page after a break occurs for this column. If a COMPUTE command was given, the new page occurs after the computations have been displayed.
The default is OFF, which means that a break does not cause the report to start on a new page.
Examples
These commands format the first column in the select list to be displayed with a heading of Employee Name with a width of 20 characters.
COLUMN 1 ALIAS EMPNAME;
COLUMN EMPNAME HEADING
'EMPLOYEE|NAME' WIDTH 20;
 
This command formats the second column in the select list.
COLUMN 2 HEADING 'BIRTHDATE'
PICTURE 'DD-MM-YY';
See also
BREAK
COMPUTE
SET LINESIZE
SET NULLS
SHOW REPORT
COMPUTE
This command performs aggregate computations at breakpoints and prints the results. The computations are:
These computations should not be confused with the SQL functions that have similar names. The SQL functions calculate and display rows of summary information for groupings without displaying detail data. The COMPUTE functions calculate and display summary rows of data along with detail data.
Before running this command, you must first issue a BREAK command. If a COMPUTE is given without a BREAK command, an error message is displayed.
Each aggregate function requires a separate COMPUTE command.
COMPUTE commands are cumulative. This permits multiple calculations for a single column. For example, if a COMPUTE specifies MIN of a column, and a subsequent COMPUTE specifies MAX for the same column, then both the minimum and maximum for that column are calculated and printed.
Clauses
OFF
Specify OFF to turn off a specific COMPUTE option. To turn on the function again, the COMPUTE command must be given again for that function.
MAX
This function calculates the maximum value of the items in the compute list for each group of rows specified in the break list.
The computed values are printed at each break point on one or more lines with a blank line separating the summary calculations from the detail rows.
MIN
This function calculates the minimum value of the items in the compute list for each group of rows specified in the break list.
The computed values are printed at each break point on one or more lines with a blank line separating the summary calculations from the detail rows.
SUM
This function calculates the total value of the items in the compute list for each group of rows specified in the break list.
The computed values are printed at each break point on one or more lines with a blank line separating the summary calculations from the detail rows.
AVG
This function calculates the average value of the items in the compute list to be calculated for each group of rows specified in the break list.
The computed values are printed at each break point on one or more lines with a blank line separating the summary calculations from the detail rows.
Null values are not included in the calculation.
COUNT
This function calculates the number of the items in the compute list for each group of rows specified in the break list.
The computed values are printed at each break point on one or more lines with a blank line separating the summary calculations from the detail rows.
Null values are not included in the count calculations.
OF column ID
The column-ID can be:
The calculations are performed for each of the column-IDs in the compute-list.
ON break list
This contains column IDs separated by blanks and/or the keyword REPORT. All items in the break list must also have been specified as break points in the BREAK command (but the reverse is not true). No computations are performed for break points that are specified in the COMPUTE command but not in the BREAK command. If the break order is different in the two commands, the order specified in the BREAK command takes precedence.
Examples
The example below shows a sequence of commands that assigns aliases with the COLUMN command, sets BREAK fields, and finally specifies three calculations using the COMPUTE command.
CREATE TABLE X (DEPTNO INT, LNAME CHAR(10), PROJNO INT,
SAL INT);
INSERT INTO X VALUES (1, 'Wayne', 200, 1000);
COLUMN 1 ALIAS DEPTNO;
COLUMN 2 ALIAS LNAME;
COLUMN 3 ALIAS PROJNO;
COLUMN 4 ALIAS SAL;
BREAK ON REPORT DEPTNO PROJNO;
COMPUTE SUM OF SAL ON DEPTNO PROJNO REPORT;
COMPUTE AVG OF SAL ON DEPTNO;
COMPUTE COUNT OF LNAME ON PROJNO;
The following command requests a break on the columns DEPTNO and PROJNO in the SELECT command shown later.
BREAK ON REPORT 1 3;
Display salary totals by department (DEPTNO), project (PROJNO) and report.
COMPUTE SUM OF SAL ON 1 3 REPORT;
Display the average salary of each department. Give DEPTNO the alias DEPT.
COLUMN 1 ALIAS DEPT;
COMPUTE AVG OF SAL ON DEPT;
Display the number of people on each project.
COMPUTE COUNT OF LNAME ON PROJNO;
The following example uses the BREAK command and the COMPUTE command on the following query.
SELECT DEPTNO, LNAME, PROJNO, SAL FROM EMP;
See also
BREAK
SHOW REPORT
CONNECT
You can use this command to perform one of the following functions:
When you connect a database to a specified cursor, SQLBase automatically assigns an implicit connection handle to the cursor. All cursors connecting to the same database, regardless of the username and authorization ID, are part of the same transaction.
When you specify a cursor as part of an explicit connection, you must specify the connection name which identifies a connection handle. Each connection handle represents a specified database connection and a separate, independent transaction in the server. For details, read BEGIN CONNECTION on page 44.
Generally, cursors established implicitly apply to applications in which you are connecting cursors to a specific database that belong to a single transaction.
To create multiple, independent connections, SQLBase allows you to explicitly create multiple connection handles. You can use the CONNECT command to connect cursors to specific connection handles. Using explicit connection handles allow you to implement multiple transactions to the same database within an application. If you are using a connection handle and associating a cursor with the specified connection handle, you can only have one user per transaction.
Note: You can use both implicit and explicit connections within an application.
In SQLTalk, regardless of the number of connections established, there is only one current connection and one current cursor. The current cursor is the one that is executing the current SQL command and the current connection is the one to which the current cursor belong.
The current cursor and subsequently the current connection is the cursor established by the last CONNECT or USE command, or the cursor most recently established at SQLTalk start up time (if no cursor is specified at start up time, cursor 1 is the default). Cursors are associated with an implicit or explicit connection handle that identifies a connection to a specific database.
Clauses
database name
Specify a database name only if you are establishing an implicit database or cursor connection and want to specify a database to connect to.
If the database name is not specified in a CONNECT command, it is assumed that the current database will remain unchanged.
If you are connecting to the MAIN database, specify the name of the server where the MAIN database is instead of the database name itself.
cursor number
A cursor number is required when you are establishing an explicit connection.
This is a unique number that identifies a single connection between SQLTalk and SQLBase. You can establish multiple cursors within an implicit or explicit database connection by giving each cursor in a connection a new cursor number.
If you are establishing an implicit connection to a database, you assign the cursor connection a specified database name. If you are connecting a cursor to an explicit database connection issued through a connection handle created with the BEGIN CONNECTION command, you assign the cursor connection a specified connection name. By connecting more than one cursor to the same database or connection, each cursor represents a SQL command or activity that is part of a single transaction.
If you use the CONNECT command to specify an existing cursor number, the old cursor is removed, and a new cursor is established with the specified parameters.
connection name
Specify a valid connection name only if you are connecting a cursor to an explicit connection. An explicit connection is created with the BEGIN CONNECTION command, which issues a connection handle associated with a specific database connection. For details, read BEGIN CONNECTION on page 44.
auth ID/password
If you are establishing an implicit connection to a database, you can assign each valid user of the database an authorization ID and password. For implicit database connections, the CONNECT command can establish multiple connections to the database through multiple authorization IDs. This allows multiple users within one transaction.
Each authorization ID must be accompanied by its corresponding password (and separated by a /).
If you do not specify a authorization ID/password, the connection takes place with the same authorization ID/password as the current cursor. The current cursor is the one that was established at sign-on or with the last CONNECT or USE command.
Examples
Implicit connections
Connect a second cursor to the database TESTDB with John's authorization ID and password.
CONNECT TESTDB 2 JOHN/280Z;
Connect to a second cursor within the same database.
CONNECT 2;
Explicit connections
Connect a second cursor to the connection named CH2.
CONNECT 2 TO CONNECTION CH2;
Connect to a third cursor within the same connection.
CONNECT 3 to CONNECTION CH2;
See also
BEGIN CONNECTION
GRANT (SQL command)
SET CURSORNAME
USE
SHOW CONNECTION
COPY
This command copies data from a source table to an existing destination table in the same or a different database.
COPY puts a shared lock on the source table, which means that other users can read the table, but are unable to modify it. The new data copied to the destination table gets an exclusive lock.
Clauses
cursor number
To copy data to a destination table on another database, prefix the destination and source table name with the cursor number that identifies a connection to the database.
If the cursor number is omitted, the system assumes that the source table and destination table exist on the current database. The current database is the one to which connection was established at sign-on or with the last CONNECT or USE command.
The default connection is with cursor 1.
source table
The name of the table from which data is copied.
destination table
The name of the table to which data is copied. This table must exist. The COPY command does not create the destination table. The columns of the destination table must be compatible in length and data type with the columns of the source table.
Examples
Copy the employee data from the current database to the TEST database. The TEST database is connected by cursor 2.
COPY EMPLOYEES TO 2.EMPLOYEES;
Copy the employee data to another table, EMPLOYEE_LIST, which is on the current database.
COPY EMPLOYEES TO EMPLOYEE_LIST;
This example illustrates connecting to another database and copying from another table.
CONNECT PERSONEL 2 JENNY/BEAR;
CREATE TABLE CODES (CODE CHAR(3),DESCRIPTION CHAR(50));
COPY 1.CODES TO CODES;
See also
CONNECT
USE
DBERROR
This command displays the message text, reason, and remedy code for an error code.
Clauses
error code
To display information about a specific error code, enter the error’s associated numeric error code contained in the error.sql file.
If you do not enter a specific error code, the command displays information about the last error code accessed.
Example
The following example displays the message text, reason, and remedy for SQLBase error 1805.
DBERROR 1805;
01805 LKM TMO Time out
Reason: The transaction is waiting for a resource currently locked by another transaction. The transaction will wait by default 275 seconds unless specified otherwise.
Remedy: Determine what transaction is locking the resources and attempt to make changes so that the wait situation does not occur frequently. A COMMIT as often as possible often helps to avoid these time out condition. To reduce the time wait period you can use the set timeout function call (sqltio) or in SQLTalk you can SET TIMEOUT n where n is the number of seconds. The number of seconds can range from 1 to 1800 seconds (30 minutes).
DISCONNECT
Use this command to disconnect a cursor that is part of an implicit or explicit connection handle by specifying the cursor number.
Note that the cursor that you specify cannot be the current cursor, which is the one you most recently connected to at SQLTalk start up time, or with the most recent CONNECT or USE command.
When you connect a database to a specified cursor, SQLBase automatically assigns an implicit connection handle to the cursor. When you use the DISCONNECT command to disconnect from this database, SQLBase automatically destroys the implicit connection handle to the cursor.
When closing the final cursor in an explicit connection handle, the transaction remains pending. Depending on the CLOSETRANSID option setting, it is either committed or rolled back when you terminate the connection handle using the END CONNECTION command. When closing the final cursor in an implicit connection handle, the transaction is either committed or rolled back depending on the CLOSETRANSID option setting. For details on specifying connect closure behavior, read the CLOSETRANSID option described in the SET command in this chapter.
 
Clauses
cursor number
A cursor number is required whether you are disconnecting a cursor that was connected implicitly or explicitly.
Specify the cursor from which you are disconnecting. You cannot disconnect from the current cursor (the cursor that is currently being used).
database name
If you are disconnecting an implicit connection, you can specify the name of the database for the specified cursor you are disconnecting. If you disconnect the database name, all cursors for the current database are disconnected. The current database is the database to which you are connected a sign-on or with the most recent CONNECT or USE command.
Examples
Disconnect cursor 2. Note that this example can apply to either a cursor that was connected implicitly or explicitly.
DISCONNECT 2;
Disconnect DATABASE TESTDB that was implicitly connected to cursor 2.
DISCONNECT TESTDB 2;
See also
BEGIN CONNECTION
CONNECT
END CONNECTION
USE
END CONNECTION
This command terminates a specified connection that you created explicitly with the BEGIN CONNECTION command. You supply the name of the connection that you want to terminate. This closes the corresponding connection handle that is associated with the database, thereby terminating the connection.
Note that you cannot terminate the connection of the current cursor. SQLTalk always maintains what it considers to be the current cursor. The current cursor is the cursor used for any SQL command execution.
By default, the END CONNECTION command causes an implicit commit for a SQLBase database associated with the connection. Use the SHOW CLOSETRANSMETHOD to display whether a COMMIT or ROLLBACK will be issued before the connection handle is terminated. To change the setting to ROLLBACK for SQLBase databases, use the SET CLOSETRANSMETHOD command. For details, read the CLOSETRANSMETHOD option described in the SET command in this chapter.
Note: You can disconnect an implicit connection to a database or cursor using the DISCONNECT command. For details, read DISCONNECT on page 65.
Clauses
connection name
Specify the connection name, which can be no more than eight characters in length.
Example
The following example terminates a specified connection:
END CONNECTION CH2;
See also
BEGIN CONNECTION
CONNECT
USE
SHOW CONNECTION
ERASE
This command deletes a stored command or procedure.
Clauses
auth ID.
If you are not the creator of the command or procedure, specify the authorization ID of the owner.
stored command/procedure name
The name of the previously compiled and stored command or procedure to delete.
Examples
The following commands store a query and then erase it.
STORE myquery SELECT * FROM BIRTHDAYS;
ERASE myquery;
The following command erases the strdproc_pres stored procedure.
ERASE strdproc_pres;
See also
EXECUTE
STORE
ERASE FILTER
This command erases a previously saved set of ROWID values in system tables, using a name that you previously specified in SAVE FILTER. Such ROWID sets are created when restriction mode is active.
Clauses
filter name
This is a character string that must obey the constraints of a SQLBase long identifier.
Examples
This script erases a set of restriction mode ROWIDs that had been saved earlier under the name ‘Italia_company’.
ERASE FILTER Italia_company;
See also
SET FILTER
SAVE FILTER
EXECUTE
This command executes a stored command or procedure.
To manually recompile stored commands, use the RECOMPILE clause documented in Appendix B of the Database Administrator’s Guide. This clause is itself a stored procedure provided by SQLBase. For more information on procedures, read the Procedures and Triggers chapter in the SQL Language Reference.
You cannot execute stored commands or procedures while in restriction mode. However, you can turn on result set and restriction modes inside of a procedure. In other words, you cannot save the results of a stored procedure in a result set, but you can manipulate result sets inside of a stored procedure.
Other users can execute your stored commands by retrieving them as user.command name. For example, if you stored a procedure as SYSADM, other users can retrieve it as SYSADM.command. The users must have access privileges to the tables involved.
If you are executing a static stored procedure, you must first store it with the STORE command.SQLTalk User GuideClauses
auth ID.
If you are not the creator of the command or procedure, specify the authorization ID of the owner. For example, assume that SYSADM created a stored command. To execute it, enter this command:
EXECUTE SYSADM.command-name
stored command/procedure name
The name of the stored command or procedure to execute.
If the SQL command contains bind variables, the EXECUTE command must be followed by the input data. The delimiter for the EXECUTE command must be a line containing a backslash (\). This notifies SQLTalk that input data comes from the keyboard. The end of the input data is marked with a forward slash (/) on a new line.
The RECOMPILE procedure
SQLBase provides a procedure called RECOMPILE. Use this stored procedure to manually recompile a stored command. To run RECOMPILE for a specific database, first run the recomp.sql script against the database. For information on this procedure, read Appendix B in the Database Administrator’s Guide.
Examples
This example compiles and stores a command ADDNAMES. The EXECUTE command contains bind variables, so you must enter data.
STORE ADDNAMES
INSERT INTO FRIENDS (NAME) VALUES (:1);
EXECUTE ADDNAMES
\
PROCESSING DATA
LEN
MARGE
BETTY
/
3 Rows Inserted
The following commands store and execute the pr_pres procedure.
STORE strdproc_pres
PROCEDURE pr_pres
Local Variables
Sql Handle Cur1
Actions
Call SqlConnect (Cur1)
Call SqlStore (Cur1, ‘presname’, \
‘SELECT pres_name FROM sysadm.president’)
Call SqlDisconnect (Cur1);
 
EXECUTE strdproc_pres;
The following example recompiles all of SYSADM’s stored commands, both valid and invalid.
EXECUTE RECOMPILE
\
‘SYSADM’,%,_,0,0,0
/
The following example shows possible output from this command:
RETURN_CODE NUMBER_FOUND NUMBER_DONE
----------- ------------ -----------
0 11 11
See also
ALTER COMMAND
ERASE
PREPARE
STORE
EXIT
This command exits SQLTalk and returns to the native operating system. It disconnects all cursors and causes an implicit COMMIT for all cursors and all databases.
Example
EXIT;
FETCH
This command fetches the number of rows that you specified.
You can only use a FETCH in result set mode or after a PERFORM.
Clauses
integer constant
This specifies the number of rows to retrieve, starting with the row pointed to by the last SET SCROLLROW command.
Example
The example below uses an ADJUSTING clause and a result set.
SET CURSORNAME MYCUR;
SET SCROLL ON;
SELECT * FROM PRESIDENT;
SET SCROLLROW 3;
FETCH 10;
A different cursor is used to INSERT, preserving the result set.
CONNECT DEMO 2;
INSERT into result set.
INSERT INTO PRESIDENT (PRES_NAME)
VALUES ('George Bush') ADJUSTING MYCUR;
Return to the result set cursor.
USE 1;
The result set is unaffected, so you can execute a FETCH without executing the SELECT again.
FETCH 5;
See also
PERFORM
PREPARE
SET SCROLL
SET SCROLLROW
LEFT
This command shifts the displayed output of a SELECT command to begin with a column to the left of the current first column of the output.
This command enables you to view a wide report on a device whose LINESIZE is smaller than the DEVICESIZE.
This command does not affect the column-IDs of the columns in the select list.
Clauses
number of columns
This indicates how many columns to the left of the currently displayed first column to shift the report.
If the specified number is greater than the number of columns existing to the left, then the report begins at the first column. Columns that you specified as PRINT OFF in the COLUMN command are ignored.
Example
SELECT ITEMNO, ITEM, COST FROM INVENTORY;
ITEMNO ITEM COST
====== ========= ====
1 CHOCOLATE 1.59
2 COCOA 2.48
3 COLA 1.46
First, the RIGHT command displays the rightmost column first.
RIGHT 2;
SELECT ITEMNO, ITEM, COST FROM INVENTORY;
COST
====
1.59
2.48
1.46
The LEFT command shifts the column, making the column to the left of the current first column the leftmost column.
LEFT 1;
SELECT ITEMNO, ITEM, COST FROM INVENTORY;
 
ITEM COST
========= ====
CHOCOLATE 1.59
COCOA 2.48
COLA 1.46
See also
COLUMN
RIGHT
SET DEVICESIZE
SET LINESIZE
LIST
This command displays the most recently-entered command. This command lets you view the input before editing it with the EDIT command.
Clauses
line number
This indicates the line of the command to list. If you did not specify a line, the entire command is displayed.
Examples
SELECT A, B, C FROM TAB1
WHERE A = 'N';
LIST;
SELECT A, B, C FROM TAB1
WHERE A = 'N'
LIST 1;
SELECT A, B, C FROM TAB1
See also
EDIT
PAUSE
This command suspends the SQLTalk session until you press the enter key.
This command can be used in a SQLTalk command file to cause a pause between groups of commands or to mark a transition point.
Example
PAUSE;
See also
REMARK
PERFORM
This command runs a PREPAREd (compiled) SQL command or procedure. You must execute the PERFORM command immediately after a PREPARE command.
You can bind data to variables before execution.
For SELECT commands, the PREPARE/PERFORM sequence only initializes for the first FETCH. SQLBase does not automatically fetch rows; you must explicitly request that rows be fetched with the FETCH command.
For SQL commands other than SELECT, the behavior of the PREPARE/PERFORM sequence is the same as if the SQL command had been entered from the keyboard or from a RUN file.
Example
PREPARE PROCEDURE pr_pres
Local Variables
Sql Handle Cur1
Actions
Call SqlConnect (Cur1)
Call SqlStore (Cur1, 'presname', 'Select pres_name from \
sysadm.president')
Call SqlDisconnect (Cur 1);
PERFORM;
See also
FETCH
PREPARE
SET SCROLL
SET SCROLLROW
PREPARE
This command compiles a SQL command or procedure that is not stored, but does not execute it.
Four things happen when SQLBase compiles a SQL command or procedure:
1.
The command itself or the SQL commands in the procedure are parsed. SQLBase detects syntax errors and verifies the existence of database objects.
2.
3.
4.
If you do not precompile commands with PREPARE, you must compile them at run time. In a production application, precompilation makes complex SQL operations perform better.
Clauses
SQL command/procedure
Specify the command or procedure to compile. You cannot prepare a stored command or stored procedure.
Example
The following example prepares a SQL command:
PREPARE SELECT * FROM CUST WHERE CUST_NAME = 'jones';
The following example prepares a procedure:
PREPARE
PROCEDURE WithDraw
Parameters
NUMBER: nAccount
NUMBER: nAmount
RECEIVE NUMBER: nNewBalance
Local Variables
STRING sUpdate
STRING sSelect
Actions
SET sUpdate = 'UPDATE Checking SET \
Balance = Balance - :nAmount '|| 'WHERE \
AccountNum = :nAccount'
CALL SQLImmediate(sUpdate)
SET sSelect = 'SELECT Balance FROM Checking ' || \
'WHERE AccountNum = :nAccount ' || \
'INTO :nNewBalance'
CALL SQLImmediate(sSelect);
PERFORM
\
100, 200, 0
/
See also
EXECUTE
FETCH
PERFORM
SET SCROLL
SET SCROLLROW
PRINT
This command prints a report on a printer or writes a report to an external file. When you enter the PRINT command, it causes the specified command file to be run (see the RUN command) and the results are printed or written to a specified file.
Clauses
command file
This file contains SQLTalk report formatting commands and one SELECT command. The file can be produced with the SAVE REPORT command. You can also produce a command file manually.
If any of the commands result in an error, an error message is displayed on the screen and the report is not produced.
All commands after the first SELECT are ignored.
If the command file name is omitted, the most recent SQL command is executed and the results printed.
TO output file
This names the file where the report lines are written. If the TO clause is omitted, report lines are sent to the system printer.
Examples
This command prints the results of the most recently-executed command on the system printer.
PRINT;
This command prints the results of the command file MYREPORT on the system printer.
PRINT MYREPORT;
This command writes the results of the command file MYREPORT to the file MYREPORT.RPT.
PRINT MYREPORT TO MYREPORT.RPT;
See also
RUN
SAVE REPORT
RELEASE LOG
A new log file is created automatically when the current active log file becomes full (this is referred to as log rollover). RELEASE LOG forces a log rollover and so is useful when executed just before a BACKUP command. In releasing the current active log file, you can back it up (if LOGBACKUP is enabled) and SQLBase can then delete it. In doing so, the most up-to-date backup is created.
You must issue a SET SERVER command before this command.
RELEASE LOG is not needed if the BACKUP SNAPSHOT command is used. BACKUP SNAPSHOT automatically forces a log rollover.
Example
BACKUP DATABASE TO \DEMOBKUP;
DATABASE BACKED UP
RELEASE LOG;
RELEASE LOG COMPLETED
BACKUP LOGS TO \DEMOBKUP;
2 LOGS BACKED UP
See also
BACKUP
SET LOGBACKUP
SET SERVER
REMARK
This command is used in command files to display explanatory text. The text is displayed exactly as entered.
The REMARK command is echoed to the screen regardless of whether ECHO is ON or OFF.
Example
REMARK
\
This is a comment. The comment starts with a backslash signalling data input. The comment ends with a forward slash and a new line.
/
See also
PAUSE
REORGANIZE
This command reorganizes the current database using the following steps:
1.
2.
3.
4.
If recovery (logging) is enabled, REORGANIZE turns it off before reloading and back on after loading.
You should reorganize a database to correct file fragmentation. A database file can become fragmented from repeated changes and dropping of tables and so forth. In time, this fragmentation can affect the speed of database operations.
You must be the only user connected to the database. Other users cannot be connected to the database during a REORGANIZE.
A temporary file is used for unloading, called sqltmp.nnn. The nnn in the name is a unique serial number. The file is placed in the directory pointed to by the TMP environment variable. If the TMP environment variable is not defined, the file is placed in the current directory.
Important: Make a copy of the database file before executing this command. If an error occurs during the reorganization, both the temporary file and the database itself could be lost.
A REORGANIZE operation retains all AUTORECOMPILE settings.
If you have changed the SYSADM password, a subsequent REORGANIZE command retains these new settings.
Unlike a LOAD or UNLOAD command, the unload/load processing for a REORGANIZE command can only take place on the client, not the server.
IMPORTANT: REORGANIZE is a SQLTalk construct that combines a series of operations in their simplest format. For some databases and situations it is necessary to use the individual component statements used by REORGANIZE to gain more control. Typically these situations include:
Larger databases exceeding 1 gigabyte, where the unload file must be segmented into smaller files to avoid creating an individual file that is too large and/or to use multiple drives.
REORGANIZE with encrypted databases
When you perform a REORGANIZE, SQLBase does an unload (unencrypted) and then creates a new unencrypted database. Unify Software recommends that instead of performing a REORGANIZE with encrypted databases, you manually reorganize by following the steps below:
1.
2.
3.
4.
5.
6.
Warning: Unload files are unencrypted and if you intend to retain them you should take steps to protect them such as by using the encryption feature of a file compression utility.
Example
REORGANIZE;
See also
CHECK DATABASE
RESTORE
This command restores a database, one or more transaction logs, or both. The database is always restored from the following file:
database-name.BKP
A RESTORE cannot be performed while users are connected to the database (including the DBA doing the RESTORE). To handle this, execute a SET SERVER command to switch your connection to the server rather than the database.
Note if you are using batch mode, you need to first specify NOCONNECT in the SQLTalk command line to prevent connecting to the database:
C:\Unify> SQLTALK BAT NOCONNECT
Then after SQLTalk starts, you enter a SET SERVER command to connect to the server.
You can restore and recover backups made with BACKUP SNAPSHOT in one of two ways:
You can restore a non-partitioned database to a partitioned database, and vice versa.
To perform the restore operation from segmented backups, you must specify a control file databasename.BCF (created from a successful segmented database backup). This control file describes the location and size of the segments to which you want to restore your database. Read the Database Administrator’s Guide for details on the format of the control file.
Note that there is no need to explicitly provide the name of the file in the RESTORE command’s syntax. If the restore control file is present in the directory specified in the TO and FROM clause of the RESTORE command, the restore operation is performed from the backup segments specified in the control file. Otherwise, the RESTORE command expects to find the restore file databasename.BKP to restore the database.
To restore a database encrypted with a different server security password than the one currently set, you must first give an ALTER EXPORTKEY command. For more, read ALTER EXPORTKEY on page 2-39.
Warning: You cannot rollforward past a change in the security configuration, including changing the server security password, the database page encryption, and database page alteration protection. If you try to do this, the restore stops just before the security change. After making a security change, you should restart your backup procedure by making a fresh backup of the database and its logs because previous versions of the database cannot be recovered beyond the security change. Therefore, do this after making a security change:
1. Shut down the server.
2. Perform an offline backup
OR
Start the server and perform an online backup.
Clauses
DATABASE
Copies the database backup to the current database directory.
LOGS
Copies the transaction log files associated with a database to the current log directory and applies them to the restored database. This command continues restoring logs until all the logs in the backup directory that need to be applied have been exhausted.
After each execution of RESTORE LOGS, a message showing the next log file to be restored is displayed. If there are more logs to be processed than can fit on disk at one time, you can use the RESTORE LOGS command repeatedly to process all the necessary logs.
If a log file with the same name already exists in the current directory, you are prompted with this message:
Log file already exists. Overwrite it (Y/N)?
If the log file requested is not available, you can use the ROLLFORWARD database END command to terminate media recovery and recover the database using the information obtained up to that point (if possible).
SNAPSHOT
This option restores the database and the associated log files that were created with a BACKUP SNAPSHOT command. The log files, if there are any, are automatically applied to the database. Therefore, a subsequent ROLLFORWARD command should never follow a RESTORE SNAPSHOT.
FROM directory name
This specifies the directory name where the files to be restored are located.
ON CLIENT or ON SERVER
This specifies the directory name where the backup files to be restored are located. This directory can be on the client or server. The default is ON CLIENT.
TO database
This specifies the name of the database that is to be restored.
If a database file with the same name already exists, you are prompted with this message:
Database file already exists. Overwrite it (Y/N)?
Example
RESTORE DATABASE FROM \DEMOBKUP TO DEMO;
DATABASE RESTORED
ROLLFORWARD DEMO;
ROLLFORWARD STARTED
The log file 1.LOG could not be found. Use the RESTORE LOGS command to restore this log and continue the rollforward process. If this log is not available, use the ROLLFORWARD database END command to complete the recovery process.
RESTORE LOGS FROM \DEMOBKUP TO DEMO;
4 LOGS RESTORED
The log file 5.LOG could not be found. Use the RESTORE LOGS command to restore this log and continue the rollforward process. If this log is not available, use the ROLLFORWARD database END command to complete the recovery process.
ROLLFORWARD DEMO END;
ROLLFORWARD COMPLETED
See also
BACKUP
RELEASE LOG
ROLLFORWARD
SET SERVER
RETRIEVE
This command retrieves a stored SQL command or stored procedure, but does not execute it. Once a command or procedure has been retrieved, data can be bound if needed and the command/procedure can be executed.
Once a command or procedure is retrieved, it cannot be destroyed by a commit or rollback.
If another transaction changes the system catalog items that the retrieved command or procedure depends on between the commit and the execute, the execute fails.
You cannot use stored commands/procedures while in restriction mode.
Clauses
stored command/procedure
Specify the command or procedure to retrieve. You can only retrieve one stored procedure at a time. You can retrieve multiple stored commands, but the last command in the series must be a SELECT command.
Example
The following example stores, retrieves, and performs a SQL command. Since the stored command contains a SELECT statement, you must issue a FETCH command after the PERFORM to retrieve the output rows.
STORE sceng SELECT * FROM ENGINEERS;
RETRIEVE sceng;
PERFORM;
FETCH 10;
EMPL_NUM NAME REP_OFFICE TITLE HIRE_DATE
========= ======= =========== ==== ===========
100 Paul Atkins 10 Manager 12-FEB-1988
10 Bob Smith 20 Sen.Engineer 05-SEP-1992
107 Murray Roch 30 Sen.Engineer 25-JAN-1991
102 Larry Sanchez 10 Sen.Engineer 12-JUN-1989
101 Sheila Brown 10 Engineer 10-OCT-1990
06 Sam Valdez 30 Manager 20-APR-1990
105 Rob Jones 20 Engineer 08-SEP-1991
103 Anna Rice 20 Manager 10-JUL-1985
108 Mary Adams 40 Manager 10-AUG-1988
109 Nancy Bonet 40 Sen.Engineer 12-NOV-1989
10 ROWS SELECTED
The following example stores, retrieves, and performs a stored procedure:
STORE procwd
PROCEDURE WithDraw
Parameters
NUMBER: nAccount
NUMBER: nAmount
RECEIVE NUMBER: nNewBalance
Local Variables
STRING sUpdate
STRING sSelect
Actions
SET sUpdate = 'UPDATE Checking SET \
Balance = Balance - :nAmount '|| 'WHERE \
AccountNum = :nAccount'
CALL SQLImmediate(sUpdate)
SET sSelect = 'SELECT Balance FROM Checking ' || \
'WHERE AccountNum = :nAccount ' || \
'INTO :nNewBalance'
CALL SQLImmediate(sSelect);
RETRIEVE procwd;
PERFORM
\
100, 200, 0
/
See also
EXECUTE
FETCH
PERFORM
PREPARE
SET SCROLL
SET SCROLLROW
RIGHT
This command shifts the displayed output of a SELECT command to the right of the first column.
This command enables you to view a wide report on a device whose LINESIZE is smaller than the DEVICESIZE.
This command does not affect the column-IDs of the columns in the select list.
Clauses
number of columns
This indicates how many columns to the right of the currently displayed first column the report must begin. If the specified number is greater than the number of columns existing to the right, no columns are displayed. Columns that are specified as PRINT OFF (see COLUMN command) are ignored for purposes of this command.
Example
SELECT ITEMNO, ITEM, COST FROM INVENTORY;
ITEMNO ITEM COST
====== ========= ====
1 CHOCOLATE 1.59
2 COCOA 2.48
3 COLA 1.46
First, the RIGHT command displays the rightmost column first.
RIGHT 2;
SELECT ITEMNO, ITEM, COST FROM INVENTORY;
COST
====
1.59
2.48
1.46
The LEFT command shifts the column, making the column to the left of the current first column the leftmost column.
LEFT 1;
SELECT ITEMNO, ITEM, COST FROM INVENTORY;
ITEM COST
========= ====
CHOCOLATE 1.59
COCOA 2.48
COLA 1.46
See also
COLUMN
LEFT
SET DEVICESIZE
SET LINESIZE
ROLLFORWARD
This command brings a database backup to a consistent and up-to-date state by applying log files to it after a RESTORE.
You must have backed up all the database's log files and must apply them in order or the ROLLFORWARD will fail. If you are missing any of the log files, you will not be able to continue rolling forward from the point of the last consecutive log. For example, if you have 1.log, 2.log, 4.log and 5.log, but 3.log is missing, you will only be able to recover the work logged up to 2.log. The 4.log and 5.log log files cannot be applied to the database. An unbroken sequence of log files is required by recover a database backup to its most consistent state.
The SET SERVER command must precede a ROLLFORWARD command.
Warning: You cannot rollforward past a change in the security configuration, including changing the server security password, the database page encryption, and database page alteration protection. If you try to do this, the restore stops just before the security change. After making a security change, you should restart your backup procedure by making a fresh backup of the database and its logs because previous versions of the database cannot be recovered beyond the security change. Therefore, do this after making a security change:
1. Shut down the server.
2. Perform an offline backup
OR
Start the server and perform an online backup.
Clauses
TO END
This option rolls forward through all log files available. This option is the default because users normally want to recover as much of their work as possible.
TO BACKUP
This option rolls forward to the end of the backup restored. This recovers all committed work up to the point of the last database backup.
TO TIME
This option rolls forward to a specified date and time. The date and time are specified in the format "mm/dd/yy hh:mi:ss".
This allows you to recover a database up to a specific point in time, and in effect rolls back large "chunks" of committed and logged work that you no longer want applied to the database. For example, if data is erroneously entered into the database, you would want to restore the database to the state it was in before the bad data was entered.
END
If a log file requested is not available, you can enter a ROLLFORWARD database END command to complete recovery with only the information obtained up to that point.
CONTINUE
Use this option when restoring logs manually with a mechanism other than RESTORE LOGS (such as with a tape drive that is directly attached to the server that is used for restoring logs).
You should also use this option when the rollforward operation stops because SQLBase cannot find a log file that it needs. Use the RESTORE LOGS command (or sqlrlf SQL/API function) to copy the log files needed from the backup directory to the current log directory. Then enter a ROLLFORWARD database CONTINUE command (or sqlcrf SQL/API function) to resume the rollforward operation.
Example
RESTORE FROM \DEMOBKUP TO DEMO;
DATABASE RESTORED
ROLLFORWARD DEMO;
ROLLFORWARD STARTED
The log file 1.LOG could not be found. Use the RESTORE LOGS command to restore this log and continue the rollforward process. If this log is not available, use the ROLLFORWARD database END command to complete the recovery process.
RESTORE LOGS FROM \DEMOBKUP TO DEMO;
4 LOGS RESTORED
The log file 5.LOG could not be found. Use the RESTORE LOGS command to restore this log and continue the rollforward process. If this log is not available, use the ROLLFORWARD database END command to complete the recovery process.
ROLLFORWARD DEMO END;
ROLLFORWARD COMPLETED
See also
BACKUP
RESTORE
SET SERVER
RUN
This command executes the commands stored in the specified file.
If you are using a console-mode version of SQLTalk and want to run a script without pausing, do SET PAUSE OFF before doing the RUN command.
Clauses
command file
This is the name of the command file that is executed. If the command file name is omitted, the RUN command executes the most recent SQL command.
If the command file contains SQLTalk report formatting commands, the environment created by the command file remains for successive SQL commands entered at the keyboard until changed by other environment commands.
The backslash (\) character marks the start of input data. The end of the data is marked with a forward slash (/) character on a new line followed by a carriage return.
The file can contain nested RUN commands. RUN commands can be nested within command files up to ten levels deep.
If the command file contains SQL commands that use bind variables, the input data can come from:
$DATA Keyword
If the data is not included in the command file, the keyword $DATA can be used to signal input from another source. This source is always a previous file, which can be a text file or the keyboard. When this keyword is encountered, SQLTalk looks for a backslash, followed by data lines, in the previous file.
DETECT ERRORS
An error during command file processing causes execution to be interrupted. Otherwise, the error message is displayed and the next command in the command file is executed.
Examples
Data and commands in one file
The following command file (named myproc1.cmd) contains a SQL command and data:
UPDATE ITEMS SET PRICE =:1 WHERE ITEM_NAME = :2
\
.89,rice
.85,spaghetti
.83,rigatoni
/
This command executes the myproc1.cmd command file:
RUN MYPROC1.CMD;
PROCESSING DATA
.89,rice
.85,spaghetti
.83,rigatoni
3 ROWS UPDATED
Take Input from the Keyboard
The following command file (named myproc2.cmd) contains a SQL command. The $DATA keyword indicates that you will enter data at the keyboard:
UPDATE ITEMS SET PRICE =:1 WHERE ITEM_NAME = :2
\
$DATA
/
The following session updates the prices for items using the above command file. First, execute the RUN command and terminate it with the standard delimiter.
RUN MYPROC2.CMD;
The SQL command is displayed:
UPDATE ITEMS SET PRICE = :1 WHERE ITEM_NAME = :2
At this point, SQLTalk has read the $DATA keyword variable from the command file and is looking for a backslash from the keyboard to signal the beginning of input data. The backslash and data are entered. You can enter a forward slash to signal the end of input data:
\
1.50,tortellini
.89,macaroni
.99,linguine
/
3 ROWS UPDATED
Take Input from Another File
The command file myproc3.cmd uses the data in myproc3.dat. The myproc3.dat file contains the data, so that when the $DATA keyword is encountered in myproc3.cmd, it goes back to the previous source (myproc3.dat) for data.
The contents of myproc3.dat are:
RUN MYPROC3.CMD
/
\
1.85,tortellini
.69,macaroni
.85,linguine
/
The contents of myproc3.cmd are:
UPDATE ITEMS SET PRICE = :1 WHERE ITEM_NAME = :2
\
$DATA
/
RUN myproc3.dat in the SQLTalk window:
RUN MYPROC3.DAT;
RUN MYPROC3.CMD
UPDATE ITEMS SET PRICE = :1 WHERE ITEM_NAME = :2
 
PROCESSING DATA
1.85,tortellini
.69,macaroni
.85,linguine
3 ROWS UPDATED
Files with Multiple SQL Commands
In this example, two SQL commands require data input from another file. The data is in myproc4.dat and the SQL commands are in myproc4.cmd.
The file myproc4.dat contains the following lines:
RUN MYPROC4.CMD
/
\
1.00,bread
.75,tuna
.97,cheese
/
\
2.25,tortellini
/
The file myproc4.cmd contains the following lines:
INSERT INTO ITEMS (PRICE, ITEM_NAME) VALUES (:1, :2)
\
$DATA
/
UPDATE ITEMS SET PRICE =:1 WHERE ITEM_NAME =:2
\
$DATA
/
Run myproc4.dat in the SQLTalk Window:
RUN MYPROC4.DAT;
RUN MYPROC4.CMD
INSERT INTO ITEMS (PRICE, ITEM_NAME) VALUES (:1, :2)
PROCESSING DATA
1.00,bread
.75,tuna
.97,cheese
3 ROWS INSERTED
UPDATE ITEMS SET PRICE =:1 WHERE ITEM_NAME =:2
PROCESSING DATA
2.25,tortellini
1 ROW UPDATED
See also
SAVE
SET PAUSE
SAVE
This command writes the most recently executed SQL command or a series of SQLTalk commands to a command that can then be RUN. You can save the current SQL command or the current SQLTalk environment.
Clauses
SQL
If you specified this, the most recent SQL command is saved in the specified file.
REPORT
If you specified this, all the SQLTalk commands making up the current display environment and report format are saved in the specified file. These include SET, BTITLE, BREAK, COLUMN, COMPUTE, and TTITLE commands. Not all SET options are saved (such as TIME, CURSOR, or PAUSE) since they do not affect report format.
You can save the default SQLTalk environment by entering the SAVE REPORT command during a session before making any formatting changes.
command file
This is the name of the command file in which to store the commands. If the file already exists, you are prompted to append to the file, to abort the command, or to continue (write over the file). You can use the append feature to add a SQL command to an existing report environment file.
Examples
This command changes the SQLTalk environment and writes the report commands to a file.
SET LINESIZE 60;
COLUMN 1 HEADING 'New Customers';
SAVE REPORT NEWCUST.TLK;
The following SQL command is executed and saved in the file NEWCUST.SQL.
SELECT CUSTNAME FROM CUSTOMER WHERE
DATE >@MONTH(SYSDATE) - 1;
SAVE SQL NEWCUST.SQL;
To include the above command in the report NEWCUST.TLK, execute the following command:
SAVE SQL NEWCUST.TLK;
The following prompt is displayed. If you type 'P', the above SQL command is appended to NEWCUST.TLK.
File NEWCUST.TLK already exists. Type 'P' to append, 'C' to
continue....
See also
BREAK
BTITLE
COLUMN
COMPUTE
RUN
SET
TTITLE
SAVE FILTER
This command writes the most recently retrieved set of ROWID values into system tables, using a name that you specify. You can retrieve these ROWID values later by using that name with the SET FILTER command. In addition, this command performs the same functions as SET RESTRICTION OFF and SET SCROLL OFF, ending both restriction mode and result set mode.
This command is only valid when restriction mode is active.
Clauses
filter name
This is a character string that must obey the constraints of a SQLBase long identifier.
Examples
This script sets restriction mode on, establishes a set of ROWID values for a table, then saves those ROWID values for later use, ending restriction mode at the same time.
SET FILTER ON;
SELECT * FROM COMPANY WHERE CITY=’Milano’;
SAVE FILTER Italia_company;
See also
SET FILTER
ERASE FILTER
SET
This command sets environment options for the SQLTalk session. Some options can be turned ON or OFF; others require a numeric or string constant assignment.
The SHOW command displays the setting of environment options.
Clauses
option name
This is the name of the option that is being set. The options are described below.
ACTIVITYLOG
Sends all messages which would appear on the server Process Activity screen to a file whose name and location you specify. These messages are helpful when debugging, but they are not meant to be logged in a production environment. Logging incurs overhead, and the log file can become large very quickly if your site is an active one.
Example: SET ACTIVITYLOG "c:\my documents\sqlbase.log"
AUTOCOMMIT
If this is ON, the database is committed automatically after each SQL command. Otherwise, the database is committed only when you issue a COMMIT command. A commit via AUTOCOMMIT is identical to one issued explicitly.
Any operation performed on the cursor causes a commit for all cursors connected to the database. However, an operation on a cursor where autocommit is not turned on does not cause an automatic commit.
For a cursor in a distributed transaction, AUTOCOMMIT causes an implicit coordinated commit.
The AUTOCOMMIT option cannot be turned on at the same time as the BULK option. Turning on the BULK option automatically disables AUTOCOMMIT.
AUTOCOMMIT only affects the following operations:
For other types of operations, you should do an explicit commit, even if you have RL isolation.
There is no specific authority required to run SET AUTOCOMMIT.
The default is OFF.
BELL
If this is ON, the bell sounds when user interaction is desired.
There is no specific authority required to run SET BELL.
The default is OFF.
BM integer constant
This sets the bottom margin of a report (the number of blank lines that are displayed at the bottom of a page). The value can range from 0 to PAGESIZE.
There is no specific authority required to set this option.
The default is 0.
BULK
If this is ON, operations are buffered in the output message buffer as much as possible.
The bulk execute feature reduces the network traffic for multi-row inserts, deletes, and updates, particularly across a network. In bulk execute mode, data values are buffered so that many rows can be sent to the server in one message.
Increasing the size of the output message buffer with the SET OUTMESSAGE command increases the number of operations that can be buffered in one message to the server, which improves performance. The minimum setting for this buffer is 320 bytes.
The BULK option cannot be turned on at the same time as the AUTOCOMMIT option. Turning on BULK automatically disables AUTOCOMMIT.
This setting is cursor specific.
You must have CONNECT authority to set this option.
The default is OFF.
CHECKPOINT integer constant
This specifies how often a recovery checkpoint operation is performed. This operation is required for the automatic crash recovery mechanism.
Depending on the applications running against the server, a checkpoint operation can affect performance. If this happens, the checkpoint interval can be increased until the desired performance is attained.
The default checkpoint time interval is very small, yielding a crash recovery time of less than a minute. If a longer crash recovery time can be tolerated, the interval can be increased up to 30 minutes.
You must have DBA authority to set this option.
The default is one minute.
CLOSETRANSMETHOD [COMMIT | ROLLBACK | DEFAULT]
This option specifies whether a COMMIT or ROLLBACK is issued before a connection handle is destroyed. The default is DEFAULT which means that this behavior is dependent on the database server to which the user is connected. In the case of SQLBase, the DEFAULT setting issues a COMMIT before a connection handle is terminated. Refer to documentation for your specific server for other default settings.
This option also specifies whether a COMMIT or ROLLBACK is issued before disconnecting a final cursor that was implicitly connected or connected using the CONNECT command with the cursor number along with the following clause options: databasename, username, and password.
COLLATION collation
Sets the collation for the connection. Collation specifies the language that SQLBase uses to sort data on a server, a particular database, or a particular connection. Setting connection collation overrides both the database collation and server collation during that session. See the SQLBase Database Administrator's Guide for a list of supported collations.
CURSORNAME string
This assigns a name to the current cursor. SET CURSORNAME is used before executing an UPDATE or DELETE with a CURRENT OF clause or an INSERT with an ADJUSTING clause.
The cursor name must be 18 characters or less.
There is some overhead for fetches when a cursor name is assigned because the server must keep track of the current cursor position. You can de-assign a cursor name by specifying a null value. The server optimizes fetches when a cursor is not assigned.
You must have CONNECT authority to set this option.
DATABASECOLLATION collation
Sets the default collation for the database. Collation specifies the language that SQLBase uses to sort data on a server, a particular database, or a particular connection. Setting database collation overrides the collation for the server upon which the database resides. See the SQLBase Database Administrator's Guide for a list of supported collations.
Setting database collation through a particular connection does not change the collation of that connection.
Note: Collation cannot be set on partitioned databases.
DATEPICTURE ‘picture’
This edits DATE data type values according to the specified print picture. See the explanation of the COLUMN command for information about print pictures.
There is no specific authority required to set this option.
DATETIMEPICTURE ‘picture’
This edits DATETIME data type values according to the specified print picture. See the explanation of the COLUMN command for information about print pictures.
There is no specific authority required to set this option.
DB2
If this is ON, SQLBase is DB2 compatible.
If this is ON, you can use the FOR UPDATE OF clause in a SELECT command.
You must have CONNECT authority to set this option.
The default is OFF.
DECHO
If this is ON, data accompanying a SQL command in a bind variable is echoed to the screen.
If this is OFF, the data is not echoed.
This command is useful when INSERTing large amounts of data.
If ECHO is OFF, DECHO cannot be set ON.
There is no specific authority required to set this option.
The default is ON.
DEVICESIZE integer constant
This indicates the width of the output device in number of characters. Typical screens are 80 characters wide. Printers usually range from 80 to 132 characters. If the LINESIZE is less than the DEVICESIZE, excess characters to the right are truncated, unless the LEFT or RIGHT commands are used to position the displayed columns appropriately.
There is no specific authority required to set this option.
The default DEVICESIZE is 8192.
DISTRANS
This indicates that all subsequent commands will be part of a distributed transaction, which spans multiple databases. Currently, you cannot include a BACKUP or RESTORE command in a distributed transaction. You cannot turn DISTRANS ON if connection handles exist. The default is OFF.
Note: Connection handles are not supported for distributed transactions. Therefore, if you are using connection handles, distributed transactions cannot be enabled. For details on connection handles, read the sections in this chapter on the BEGIN CONNECTION and CONNECT commands.
You can set DISTRANS ON in the same connection in which it was set OFF. All connects after DISTRANS has been set OFF belong to non-distributed transactions.
There is no specific authority required to set this option.
ECHO
If this is ON, command text is echoed to the screen when executing a command file using the RUN command or a precompiled command with the EXECUTE command.
If this is OFF, the text and any associated data is not echoed.
There is no specific authority required to set this option.
The default is ON.
ERRORLEVEL n
This controls the level of detail in the error messages that SQLTalk displays.
The number that you specify can be from 1 to 3. The table below explains the meaning of each number.
Error Level
For example, if you enter the SQL command
“CREATE JUNK;”, you get this message:
The error message text.
The error code number.
The mnemonic.
For example, if you enter the SQL command
“CREATE JUNK;”, you get this message:
The error message text.
The error code number.
The mnemonic.
The error reason.
The error message text.
The error code number.
The mnemonic.
The error reason.
The error remedy.
For example, if you enter the SQL command
“CREATE JUNK;”, you get this message:
Remedy: Verify that the keyword immediately following the CREATE verb keyword is either TABLE, INDEX, UNIQUE (index), SYNONYM, PUBLIC (synonym), or VIEW.
There is no specific authority required to set this option.
ETIME
If this is ON, SQLBase displays the time spent to generate the results, including formatting them and displaying them on the screen. A before system time is recorded before beginning a SQL operation (before the compile or before the retrieve of a stored command). An after system time is recorded when the statement has completed processing (after the execute or final fetch) and displaying all of the data on screen with the final times.
TIME is a better indicator of database performance than ETIME. TIME is the time it takes to execute each API call.
TIME is automatically turned OFF when ETIME is turned ON.
There is no specific authority required to set this option.
The default is OFF.
EXTENSION integer constant
This option requires DBA authority.
This sets the extension size of a database file in kilobytes.
SQLBase databases grow dynamically as data is added, and they expand in units called extensions (extents). When a database becomes full, SQLBase must add another extent to the database. You can set the size of the extent. This option sets the extension size for both partitioned and non-partitioned databases.
In non-partitioned databases, the .dbs file grows in 100 kilobyte extents by default.
In partitioned databases, the default extent is one megabyte. The extension size is rounded up one megabyte from the user setting. For example, if you set the extension at 1024 kilobytes, the actual extension size is still 1024 kilobytes. However, if you set the extension to 1025 kilobytes, the actual extension is two megabytes (2048 kilobytes).
FETCHTHROUGH
This feature is used to avoid retrieving rows from the client's input message buffer that have been updated.
When FETCHTHROUGH is OFF, SQLBase fetches rows from the client's input message buffer if possible.
When FETCHTHROUGH is ON, SQLBase fetches data from the backend, ensuring the most up-to-date data. Only one row is fetched from the back-end at a time.
Note: For procedures, if you want the On Procedure Fetch section to execute exactly once for every fetch call from the client (returning one row at a time) set FETCHTHROUGH mode on at the client (the default is off).
Using FETCHTHROUGH increases response time. Only use FETCHTHROUGH when it is necessary to get the most up-to-date row.
This setting is cursor specific.
You must have CONNECT authority to set this option.
The default is OFF.
SQLBase creates a virtual table with a pseudo row ID if a SELECT statement containing one or more of the following options creates a result set:
In these cases, rows in the result set cannot be mapped to the rows in the database. If you UPDATE in this situation and later fetch an UPDATEd row, the row will not reflect the UPDATE even if FETCHTHROUGH is ON.
FILTER ON | OFF | name
Using this clause with ON or name is equivalent to issuing a SET SCROLL ON followed by SET RESTRICTION ON. Both result set mode and restriction mode are activated.
If name is used, a set of restriction-mode ROWIDs that was saved previously under name (see SAVE FILTER command) will be retrieved and activated.
If OFF is used, both result set mode and restriction mode are deactivated.
HEADING
If this is OFF, default headings are not displayed above each column of data during query output.
If this is ON, default headings are displayed above columns for which a COLUMN HEADING has been given.
Column headings specified with the COLUMN command are displayed regardless of the setting of the HEADING option. Default headings contains column names (or expressions) as they appear in the select list of a SELECT command.
There is no specific authority required to set this option.
The default is ON.
HISFILESIZE integer constant
This option requires DBA authority.
If the READONLY option is enabled for a database, this parameter limits the size of the read-only history file. The size is specified in kilobytes.
The default history file size is one megabyte (1000 kilobytes).
INDENT integer constant
This sets the number of spaces that each wrapped line in a multi-line row is indented. Usually, one or two spaces are appropriate.
The default is one.
There is no specific authority required to set this option.
If you set this to zero (0), continuation lines are not indented.
INMESSAGE integer constant
This sets the default maximum size (in bytes) for the SQLBase input message buffer. The input message buffer refers to input to the application (such as the result of a query).
The INMESSAGE size affects the cursor that is active when the command is given.
Most query data will not exceed the default input message buffer size, but if it does, you can use this command to raise the size of the INMESSAGE.
The input message buffer is allocated on both the client computer and on the database server. The database server builds an input message in this buffer on the database server computer and sends it across the network to a buffer of the same size on the client. It is called an input message buffer because it is input from the client's point of view.
There is one input message buffer per connected cursor on the client computer. The server maintains one input message buffer that is the size of the largest input message buffer on the client computer.
The input message buffer can receive a return code indicating that the specified operation was successful, the data that is being fetched, and other information. While fetching data from the database, SQLBase compacts as many rows as possible into one input message buffer.
Each FETCH reads the next row from the input message buffer until they are exhausted. At this instant, SQLBase transparently fetches the next input buffer of rows depending on the isolation level.
A large input message buffer can help performance while fetching data from the database because it reduces the number of network messages. Note that a large input message buffer can have a diverse effect on the entire system throughput because of concurrence. Any row currently in the input message buffer can have a shared lock on it (depending on the isolation level) preventing other users from changing that row. Therefore, a large input message buffer can cause more shared locks to remain than are necessary.
Read the explanation of ISOLATION (SET option) for more information about how each isolation level uses the input message buffer.
SQLBase automatically maintains an input message buffer large enough to hold at least one row of data. Despite the specified input message size, SQLBase dynamically allocates more space if necessary.
A large input message buffer helps performance when reading LONG VARCHAR columns.
You can also improve overall system performance by decreasing the size of the input message buffer when an application does not need to fetch data.
Each cursor has one input message buffer associated with it on the client.
You must have CONNECT authority to set this option.
ISOLATION level
This sets the isolation level in a multi-user environment. The isolation level controls the effect that changes made by one user have on another user accessing the same tables. SQLBase supports these isolation levels:
Choose an isolation level based on the application's requirements for consistency and concurrence.
The isolation level you set applies to all the cursors that the program connects to the database. It also persists across COMMIT statements.
If you change isolation levels, it causes an implicit commit for all cursors that the program has connected to the database.
Changing the isolation level to a different level causes an implicit commit which destroys compiled commands. However, performing the SET ISOLATION command and specifying an isolation level that is the same as the current isolation level does not cause an implicit commit.
You must have CONNECT authority to set this option.
Isolation Levels and the Input Message Buffer
Each isolation level uses the input message buffer differently. This buffer is allocated on the client computer and the server computer. The database server builds a message and sends it to the input message buffer on the client computer. This buffer is considered "input" with respect to the client computer.
There is one input message buffer per connected cursor on the client computer. On the server, there is one input message buffer that is the size of the largest input message buffer on the client computer.
The input message buffer receives data requested by the client that has been sent by the server.
The Read Repeatability, Read Only, and Release Lock isolation levels each fill the input message buffer with as many rows as it can accommodate. The Cursor Stability isolation level sends only one row at a time to the input message buffer.
Any row in the input message buffer may have a shared lock on it depending on the isolation level setting, preventing other users from changing that row.
The table below summarizes how page locking and the input message buffer are affected by each isolation level.
Read
Repeatability
Cursor
Stability
Read Repeatability (RR). This isolation level means that all pages which you access stay locked for other users until you COMMIT your transaction. If the same data is read again during the transaction, those rows would not have changed. This guarantees that the data accessed is consistent for the life of the transaction. Identical SELECT commands will return identical rows since data cannot be changed by other users during the transaction. In this situation, other users may wait for your COMMIT command.
Read Repeatability is the default isolation level.
The Read Repeatability isolation level fills the input message buffer with rows. All shared locks remain regardless of the size of the input message buffer until the application issues COMMIT or ROLLBACK.
Cursor Stability (CS). This isolation level means that only the page you are processing at the moment is locked to other users. A shared lock is placed on a page for as long as the cursor is on that page (while the cursor is stable). Exclusive locks are held until a COMMIT. Shared locks, on the other hand, are only held while the cursor is stable on a page. When the cursor moves off the page and the page is no longer in the message buffer, the page’s shared lock is dropped. Other pages you accessed during the transaction are available to other users and they do not have to wait for your COMMIT.
Data that has been read during a transaction may be changed by other users when the cursor moves to a new page.
Only one row is sent in the input message buffer under the Cursor Stability isolation level despite the size of the input message buffer. In other words, each FETCH causes the client and server to exchange messages across the network.
Use Cursor Stability when you want to update one row at a time using the CURRENT OF cursor clause. When the row is fetched to the client input message buffer, its page will have a shared lock, which means that no other transaction will be able to update it.
Read Only (RO). This isolation level places no locks on the database and can only be used for reading data. DML and DDL operations are not allowed while in Read-Only isolation. This isolation level provides a view of the data as it existed when you gave the SELECT command. If you request a page that is locked by another concurrent transaction, SQLBase provides an older copy of the page from the read-only history file. The read-only history file maintains multiple copies of database pages that have been changed.
This is an appropriate isolation level if the data wanted must be consistent but not necessarily current. This isolation level also guarantees maximum concurrence.
Read-only transactions may affect performance, so they are disabled by default. Read-only transactions can be turned on with the SET READONLY command or by specifying the readonly keyword in sql.ini.
This isolation level fills the input message buffer with rows.
The Read-Only isolation level is disabled when the READONLYDATABASE attribute is on.
Release Lock (RL). Under Cursor Stability, when a reader moves off a database page, the shared lock acquired when the page was read is dropped. However, if a row from the page is still in the message buffer, the page is still locked.
In contrast, the Release Locks (RL) isolation level increases concurrence by releasing all shared locks by the time control returns to the client.
When the next message or command is sent to the database, SQLBase acquires share locks on only those pages that belong the current cursor. The locks are obtained regardless of the current command type. Just before returning to the user, SQLBase releases all shared locks. It also internally notes the page numbers of those pages that had locks on them.
This isolation level fills the input message buffer with rows, which minimizes network traffic.
Use this isolation level for browsing applications which display a set of rows to a user.
LIMIT integer constant | OFF
This specifies the maximum number of rows that are displayed as a result of a SELECT command. This is useful when testing queries on a large database or where displaying a few rows is enough indication of success for that query. The number must be a positive and greater than zero.
If set to OFF, the default, the previous limit is removed.
There is no specific authority required to set this option.
LINESIZE integer constant
This sets the width of an output line in number of characters. If the LINEWRAP option is ON, LINESIZE can be set to be equal to or less than DEVICESIZE to avoid truncation of rows.
There is no specific authority required to set this option.
The default is 80. The range is 0-132.
LINESPACE integer constant
This sets the spacing between rows:
There is no specific authority required to set this option.
Rows that wrap to the next line are always at least double spaced. The default is single spacing (1).
LINEWRAP
If this is ON, each row is wrapped to a new line if its width exceeds the LINESIZE. Otherwise, the row is truncated after the last column that fits on the line. Wrapping occurs up to a maximum of 5 lines.
There is no specific authority required to set this option.
The default is OFF.
LM integer constant
This sets the left margin of a report. The value of LM can range from 0 to LINESIZE. The default is 0. There is no specific authority required to set this option.
LOADBUFFER integer constant
This sets the size of the load buffer used to read either ASCII of DIF files during a LOAD operation. The load buffer does not have to equal the size of the entire input buffer; it only needs to be as large as the largest row of data. It processes a minimum of one LOAD file row at time.
The default LOADBUFFER size is 1000 bytes. The minimum size for a LOAD ASCII operation is 50; a LOAD DIF requires a setting of 150.
This option requires CONNECT authority.
LOADVERSION integer constant
This option is not applicable to SQLBase v6.0. If you are using a LOAD file that was created by a previous SQLBase release, this option lets you specify what version created the LOAD file.
This setting is cursor specific.
You must have CONNECT authority to set this option.
LOGBACKUP
This option requires DBA authority.
If media recovery is important to your site, set the LOGBACKUP parameter to ON, specifying that logs are to be backed up before SQLBase deletes them.
By default, LOGBACKUP is OFF and SQLBase deletes log files as soon as they are not needed to perform transaction rollback or crash recovery. This is done so that log files do not accumulate and fill up the disk. If LOGBACKUP is OFF, you will not be able to recover the database if it is damaged by a user error or a media failure.
This option is database-specific and should be set ON only once. The setting will stay active until changed. You do not need to set this each time a database is brought back on-line. Resetting this option affects whether log files are deleted or saved for archiving. To avoid gaps in your log files, set this option ON only once.
LOGBACKUP must be on to do a BACKUP DATABASE or a BACKUP LOGS, but does not need to be on to do a BACKUP SNAPSHOT.
The default is OFF.
LOGFILEPREALLOC
This option requires DBA authority.
If this is OFF, the current log file grows in increments of 10% of its current size. This uses space conservatively but can result in a fragmented log file which may affect performance.
If this is ON, all log files for the database are preallocated full sized (1 megabyte) and do not "grow" as needed.
This option only needs to be turned on once for a database (such as immediately after it is created). Once you turn LOGFILEPREALLOC ON, it stays on, even if you take the server down and bring it back up later.
The default setting is OFF.
LOGFILESIZE integer constant
This option requires DBA authority.
This sets the size of the transaction log file in kilobytes. When the current transaction log file grows to the specified size, a new transaction log file is created. A large transaction log file is best for performance because it ensures that log files are not created too often and the log files do not become fragmented, as are log files which grow in chunks. However, if the transaction log file is too large, it wastes disk space.
This option only needs to be set once for a database (such as just after it is created). Once you enter SET LOGFILESIZE, the database remembers that size even if you take the server down and bring it back up later.
The extension size is rounded up one megabyte from the user setting. For example, if you set the extension at 1024 kilobytes, the actual extension size is still 1024 kilobytes. However, if you set the extension to 1025 kilobytes, the actual extension is two megabytes (2048 kilobytes).
The default is 1 megabyte (1024 kilobytes).
LONGINFERS
This lets you read and write long data when using front end result sets with SQLNetwork routers and gateways.
This option is cursor specific.
You must have CONNECT authority to set this option.
NEXTLOG integer constant
This option requires DBA authority.
If LOGBACKUP is ON, this option needs to be set after an off-line backup is done. It tells SQLBase that an off-line backup has been made and that there are now log files eligible for deletion. The integer constant is the number of the next log to be backed up.
For example, if you make an off-line backup of mydbs.dbs, 1.log, 2.log, and 3.log, set the NEXTLOG option to 4. SQLBase then knows that 1.log, 2. log and 3.log need to be deleted (because they have been backed up), and that 4.log and all other logs which follow need to be saved for archiving.
NOPREBUILD
If this is OFF, result sets are prebuilt on the database server. The client must wait until the entire result set is built before it can fetch the first row.
If this is ON, result sets are not prebuilt if the client:
The advantage of SET NOPREBUILD ON is that the client does not have to wait very long to fetch the first row. The result set is built as the data is fetched.
Note that setting NOPREBUILD to OFF does not reduce any client memory consumption. Nor does it necessarily reduce the number of shared locks set by each user; this depends on the isolation level.
The server releases shared locks before control returns to the client.
NOPREBUILD is cursor specific.
You must have CONNECT authority to set this option.
The default is OFF.
NULLS 'string '
This substitutes null values in the output with the specified string. The string must be enclosed in single quotes. The string can be up to 10 characters long. If the field width is smaller than the string, the string is truncated upon display. An empty string consisting of two single quotes causes nulls to be displayed as a string of blanks. The default is blanks.
There is no specific authority required to set this option.
OPTIMIZEDBULK
This option is similar to the BULK option, with two basic differences:
A typical situation that uses this option is a LOAD where you cannot set RECOVERY OFF, such as when you are loading a single table into an on-line database. In this type of situation, the transaction is rolled back when an error occurs, but you can just restart the LOAD.
This option requires CONNECT authority.
OPTIMIZEFIRSTFETCH
This parameter lets you set the optimization mode for a particular cursor. The cursor executes the command; all queries that are compiled or stored in this cursor inherit the optimization mode in effect. This setting overrides the value set for optimizefirstfetch in sql.ini.
If set to 0, the cursor optimizes the time it takes to return the entire result set. If set to 1, the cursor optimizes the time it takes to fetch the first row of the result set.
If sql.ini does not have an optimizefirstfetch keyword, the default setting is 0 (optimize the time it takes to return the entire result set).
Note that a command that was earlier stored, retrieved, and executed will continue to use the execution plan with which it was compiled.
OPTIMIZERLEVEL integer constant
This sets the optimizer techniques that SQLBase uses for the current cursor. This lets you experiment with beta optimizing techniques in new versions of SQLBase.
When this option is 1, SQLBase uses current optimizing techniques.
When this option is 2, SQLBase uses new optimizing techniques.
This option overrides the setting of the optimizerlevel keyword in sql.ini. If sql.ini does not have an optimizerlevel keyword, the default setting is 2 (use new optimizing techniques).
This setting is cursor specific.
You must have CONNECT authority to set this option.
OUTMESSAGE integer constant
This sets the maximum size (in bytes) for the output message buffer. The output message buffer refers to the output of a command.
If you are inserting extremely wide table rows or creating large tables, you can increase the buffer size. Usually the default is sufficient.
The output message buffer is allocated on both the client computer and on the database server. The client builds an output message in this buffer and sends it to a buffer of the same size on the database server. It is called an output message buffer because it is output from the client's point of view.
The most important messages sent from the client to the database server are SQL commands to compile or a row of data to insert.
A large output message buffer does not necessarily increase performance because it only needs to be large enough to hold the largest SQL command to compile or large enough to hold the largest row of data to insert. A large output message buffer can allocate space unnecessarily on the both the client and the server. Rows are always inserted and sent one row at a time (except in bulk execute mode). A larger output message buffer does not reduce network traffic.
SQLBase automatically maintains an output message buffer large enough to hold any SQL command or a row to insert of any length (given available memory). Despite the specified output message buffer size, SQLBase dynamically allocates more space for the output message buffer if needed.
A large output message buffer can help performance when writing LONG VARCHAR columns.
You must have CONNECT authority to set this option.
PAGESIZE integer constant
This sets the size of a display page in number of lines. The number must be a positive and greater than 1. The default is 20 lines.
There is no specific authority required to set this option.
PARTITIONS
This enables and disables access to partitioned databases.
When this option is OFF:
You cannot access partitioned databases. An exception is when you do not have a main.dbs. After creating the first dbarea, SQLBase creates main.dbs and sets partitions to on (1) in sql.ini.
When you change this option, it changes the setting of the partitions keyword in sql.ini.
You must run SET SERVER prior to running SET PARTITIONS.
PAUSE
If this is ON, you are prompted to continue or abort after each screen of data is displayed.
If this is OFF, no pause occurs. Also, if OFF is specified, the PAUSE command is ignored while executing a command file with the RUN command.
There is no specific authority required to set this option.
In the Windows GUI version of SQLTalk (sqltalk.exe) the default is OFF. In the console-mode versions (sqlnttlk.exe on Windows and sqllxtlk on Linux) the default is ON. If you are using a console-mode version of SQLTalk and want to run a script without pausing, do SET PAUSE OFF before doing the RUN command.
When SQLTalk is running in batch mode ( see Batch option (BAT) on page 1-14), the PAUSE value is ignored.
PLANONLY
If this is ON, SQLBase displays the execution plan for a compiled SQL command. An execution plan, also called a query plan, shows what optimization techniques or particular index SQLBase is using. To compile an execution plan, set this option to ON before issuing a PREPARE statement. The default is OFF.
You must have CONNECT authority to set this option.
When PLANONLY is ON, SELECT commands are only compiled, not executed. The corresponding execution plan is saved in the PLAN_TABLE table.
PLAN_TABLE. SQLBase creates this table automatically when you execute a SQL statement with PLANONLY set to ON. It is not a system catalog table, and users can delete it.
PLAN_TABLE contains the following columns:
Additional information on the execution plan for complex queries. Read the end of this section for details.
In addition to storing the query plan in PLAN_TABLE, SQLBase displays important information about the plan on-line. The online query plan displays the following information from PLAN_TABLE:
Each line in the plan represents a temporary (result) table needed to process the SQL command. Generated by SQLBase, these are logical, not physical tables. They are listed in the order in which SQLBase processes them.
In the online execution plan, each index and base table name is truncated to six characters. Truncated names are suffixed with an ellipse (...).
Joins. Joins are listed in the order in which they are processed. The execution plan shows information such as each join’s inner and outer tables, the final result table, and any indexes used.
If a table in a join is a base table, the execution plan displays the table name. If it is a view or temporary table, SQLBase assigns it a name of TMPx, where x is a number corresponding to the join order. The TMPx table names are in ascending order, following the join order. The last temporary table is the result of the entire join command, and is called RESULT. See the following sample plan for an example.
Sometimes, the process of generating a temporary table is itself divided into subprocesses. A subprocess can create a temporary table of its own. For example, assume the process to generate the temporary table TMP1 requires several subprocesses that create temporary tables TMP1,1 and TMP1,2. Each of these subprocesses represents a small piece in the overall query plan called a subplan.
Online query plan examples. The following example shows a sample online query plan:
SET PLANONLY ON;
SELECT PRES_NAME FROM PRESIDENT X
WHERE STATE_BORN IN
(SELECT STATE_NAME FROM STATE
WHERE YEAR_ENTERED + 40 >= ANY
(SELECT MIN(YEAR_INAUGURATED)
FROM ADMINISTRATION WHERE ADMINISTRATION.PRES_NAME
= X.PRES_NAME));
EXECUTION PLAN:
OUTER TBL INDEX USED-O INNER TBL INDEX USED-I RESULT TBL JOIN METHOD
========= ============ ========= ============ ========== ==========
ADMINI... TMP1
STATE TMP1 TMP2 NESTED LOOP
TMP2 PRESIDENT RESULT HASH JOIN
 
The execution plan shows that SQLBase processes this command in the following sequence.
The first row of the plan indicates that SQLBase processes the innermost subquery first, since the table referenced is ADMINISTRATION.
(SELECT MIN(YEAR_INAUGURATED) FROM ADMINISTRATION
WHERE ADMINISTRATION.PRES_NAME = X.PRES_NAME)
TMP1
SQLBase accesses the base table ADMINISTRATION by performing a table scan; there is no index used. The qualified rows from this subquery form a temporary table called TMP1.
The second row from the plan references the next subquery, which joins TMP1 with the STATE table. The qualified rows from this subquery form another temporary table TMP2:
(SELECT STATE_NAME FROM STATE
WHERE YEAR_ENTERED + 40 >= ANY (TMP1))
TMP2
This process uses a nested loop join.
Finally, the outermost select joins TMP2 with the PRESIDENT table to get the final query result, which is stored in the RESULT temporary table. This process uses a hash join.
SELECT PRES_NAME FROM PRESIDENT X
WHERE STATE_BORN IN (TMP2);
RESULT
The following example shows another query plan:
SET PLANONLY ON;
SELECT DISTINCT SPJ.S#, S.S#, SPJ.QTY
FROM SPJ,SP,S
WHERE QTY = MAXQTY
AND (SPJ.P# = S.P#)
AND (SP.P#=S.P#);
EXECUTION PLAN:
OUTER TBL INDEX USED-O INNER TBL INDEX USED-I RESULT TBL JOIN METHOD
========= ============ ========= ============ ========== ==========
S SPJ SPJX TMP1 INDEX LOOP
TMP1 SP RESULT HASH JOIN
In this plan, the temporary table TMP1 is derived from a join of the S and SPJ tables. SPJ is the inner table, and is accessed via an index called SPJX. Since there is an index, SQLBase joins S and SPJ using an index loop method.
SQLBase then joins the temporary table (TMP1) to SP with a hash join to get the final result.
To actually execute the prepared query, issue a PERFORM command, then set PLANONLY to OFF.
Result set mode query plan example. The following example shows how to obtain a query plan in result set mode.
SET PLANONLY ON;
PLANONLY is ON
PREPARE SELECT * FROM SYSTABLES;
STATEMENT PREPARED
PERFORM;
EXECUTION PLAN:
OUTER TBL INDEX USED-O INNER TBL INDEX USED-I RESULT TBL JOIN METHOD
========= ============ ========= ============ ========== ==========
SYSTABLES RESULT
 
SET PLANONLY OFF;
PLANONLY is OFF
Retrieving the query plan from PLAN_TABLE. The online query plan does not display all the query plan information. To see the entire plan, including comments on the execution plan, issue a query against PLAN_TABLE. For example, the following is a partial view of the PLAN-TABLE contents for a sample SQL statement.
SELECT * FROM PLAN_TABLE;
 
EXECUTION PLAN:
PLANNO OUTER_TBL INNER_TBL RESULT_TBL COMMENT
========== ============ ========== ============ ==========
1 P TMP1 SQ:2
2 SP TMP3 UNION
4 P TMP4 SQ:5
5 SP TMP3 UNION
 
Comment. The COMMENT column contains information to help you understand the execution plan for complex queries. Here is how to interpret the COMMENT column in the previous example:
For PLANNO 1, the COMMENT column contains “SQ:2”. The “SQ” means that the result table (TMP1) is created from a subquery. The “2” means that the subquery itself is used in the subselect identified by PLANNO 2.
For PLANNO 4, the COMMENT column contains “SQ:5”. The “SQ” means that the result table (TMP4) is created from a subquery. The “5” means that the result of the subquery is used in the subselect identified by PLANNO 5.
For PLANNOs 2 and 5, “UNION” in the COMMENT column means that both plans are two subselects of a UNION view (their output produces a conceptual table called TMP3).
PRESERVECONTEXT
If this is ON, SQLBase maintains result sets after a COMMIT (cursor-context preservation). A COMMIT does not destroy an active result set (cursor context). This enables an application to maintain its position after a COMMIT, INSERT, or UPDATE. The cursor context is not preserved after an isolation level change or after system-initiated ROLLBACKs, such as deadlocks, timeouts, etc.
The context is preserved after a user-initiated ROLLBACK if both of the following are true:
You set PRESERVECONTEXT for one cursor at a time. All cursors with PRESERVECONTEXT set to on have their cursor context preservation maintained after a COMMIT or ROLLBACK.
For fetch operations, locks are kept on pages required to maintain the fetch position. This can block other applications that are trying to access the same data. Also, locks can prevent other applications from doing DDL operations.
This setting is cursor specific. One cursor context can be preserved while others are destroyed. The preserved context itself can be destroyed in the following situations:
A DDL command is issued in the same transaction (which may or may not be using another cursor) and the transaction is rolled back.
Only the contexts of cursors with CCP on are maintained.
This feature can be on with or without restriction mode and result set mode.
You must have CONNECT authority to set this option.
The default setting is OFF.
If the result set was created by a SELECT command that contains DISTINCT, GROUP BY, HAVING, UNION, ORDER BY, an aggregate function, or a complex view, then SQLBase creates a virtual table. In these cases, rows in the result set cannot be mapped to the rows in the database. If you UPDATE in this situation and later fetch an UPDATEd row, the row will not reflect the UPDATE even if PRESERVECONTEXT is ON.
PRINTLEVEL integer constant
This sets the level of detail for the messages on the process activity server display (0-4).
The default is 0.
You must have DBA authority to set this option.
READONLY
If this is ON, the read only isolation level can be used. The read-only isolation level gives a view of the data as it was when the transaction began. If this is ON, a read-only history file is maintained that contains multiple copies of database pages that are being modified.
This feature can be set in sql.ini or in a SET command. If specified in sql.ini, the setting applies to all databases on the server. The SET READONLY command changes the setting for the current database.
Read-only transactions may negatively affect performance, so they are disabled by default.
If you give the command SET READONLY DEFAULT, the setting in sql.ini is used; if not set in sql.ini, then the internal default is used.
You must have DBA authority to set this option.
The default is OFF.
READONLYDATABASE
If this is ON, the database is set to read-only.
If this is OFF, the database is read-write.
Once you set a database to read-only, no update statements are allowed, and log files are disabled. You must be the only connected user to turn read-only on or off.
When READONLYDATABASE is on, the Read-Only isolation level is disabled.
You must set tempdir (in sql.ini or autoexec.bat) to tell SQLBase where to store temporary files. The temporary files are stored in a subdirectory of the directory pointed to by tempdir. The name of the subdirectory is the same as the database name.
You must have DBA authority to set this option.
The default setting is OFF.
RECOVERY
In most situations you should leave RECOVERY turned ON. However, when RECOVERY is turned OFF, there is less file I/O (and a corresponding increase in performance) and less disk space is used. Therefore, you may want to SET RECOVERY OFF when loading large amounts of data.
You must have CONNECT authority to set this option.
When RECOVERY is OFF, transaction logging is not performed. Changes to the database before a COMMIT cannot be rolled back if the transaction fails, and the database will not be salvageable if the database is damaged by an user error, media failure, or power failure. Often, your database will corrupt if you run SET RECOVERY OFF and your application crashes.
For a multi-user database server, recovery can only be established by the first user connecting to a database, and SET RECOVERY must be the first command given in the SQLTalk session.
Since RECOVERY is a database-specific option, if that first user sets RECOVERY ON or OFF, it stays that way for all other users who connect to the same database. After RECOVERY is set OFF, all users who later try to connect to the database will get a message saying that recovery is not being performed.
If you turn recovery OFF, the following SET options will be reset to their default values:
The default is ON.
RESTRICTION
If this is ON, each query is a subquery of the previous result set. Therefore, if you are in restriction mode and your first query selects everyone living in California, and a second query selects all people with brown eyes, the second query returns only people who live in California and who have brown eyes. Input to each select for the current SELECT is the output of the previous SELECT.
You can only turn on restriction mode after turning on scroll mode (SET SCROLL ON). Note that restriction mode is not supported for procedures.
This setting is cursor specific.
You must have CONNECT authority to set this option.
The default is OFF.
RM integer constant
This sets the right margin of a report. The value can range from 0 to LINESIZE. The default is 0.
There is no specific authority required to set this option.
ROLLBACK
If this is ON, the entire transaction is rolled back when there is a lock timeout. If this is OFF, only the current command is rolled back when there is a lock timeout.
This setting is cursor specific.
You must have CONNECT authority to set this option.
The default is ON.
SCREEN
If this is OFF, no output is displayed to the screen.
There is no specific authority required to set this option.
The default is ON.
SCROLL
If this is ON, you can randomly position within a set of rows and begin fetching instead of always fetching from the beginning. This is called result set mode.
If you turn on result sets on the client, you can scroll forwards and backwards through the result set returned by a procedure.
The result set for procedures is preserved across COMMIT and ROLLBACK operations, even if preserve context mode is off.
You can position anywhere using the SET SCROLLROW command. Subsequent fetches can be performed with the FETCH command. Input to a SELECT in scroll mode is all the rows of the SELECTed tables, which is how SELECTs normally work outside of scroll mode.
This is similar to the SCROLL CURSOR capability in the ANSI SQL standard.
This setting is cursor specific.
You must have CONNECT authority to set this option.
The default is OFF.
Note: If you do not want to use scrollable cursors, do not turn result set mode on. SQLBase builds the result set for a procedure if result set mode is turned on; this can result in unnecessary performance degradation.
SCROLLROW integer constant
This positions the scroll cursor to a specific row where the first row is relative to zero. When a subsequent FETCH command is given, the display starts at the row specified by the SET SCROLLROW command. The SET SCROLL ON command (result set mode) must have been previously given.
You must have CONNECT authority to set this option.
SPACE integer constant
This indicates the number of characters that are blank between each column in a report. The value of SPACE can range from 0 to LINESIZE. The default is 1.
There is no specific authority required to set this option.
SPANLIMIT integer constant
This option requires DBA authority.
This specifies the number of log files that an active transaction is allowed to span. As new log files are created, this limit is checked for all active transactions. Long running transactions that violate the limit are rolled back.
Long running active transactions can pin down log files that otherwise could be deleted (or backed up and deleted if LOGBACKUP is enabled). You can limit the amount of log pinned down by active transactions by specifying the transaction span limit.
By default, the transaction span limit is set to zero which disables the limit checking.
TIME
If this is ON, the results of each SQL command are followed by the elapsed time taken to obtain the results. TIME is the time it takes to perform each function call. The system time is recorded before and after each API call.
TIME is a better indicator of database performance than ETIME. TIME is the time it takes to execute each API call. ETIME is the time it takes to generate the results, including formatting them and displaying them.
There is no specific authority required to set this option.
The default is OFF.
TIMEOUT integer constant
This specifies the number of seconds to wait for a database lock to be acquired. After the specified time has elapsed, the transaction or command is rolled back (see the ROLLBACK option).
Valid timeout values are:
Table 1:  
Wait forever for a lock held in an incompatible mode by another transaction (infinite timeout).
The default setting is 300 seconds.
You must have CONNECT authority to set this option.
This setting is transaction specific, like ISOLATION. If you have two cursors connecting to the same database, for example, they are in the same transaction, and cannot have different TIMEOUT settings. However, two cursors connecting to two different databases belong to two transactions, and do reserve a unique TIMEOUT setting.
The timeout is set on a per-connect basis and remains in effect until the next SET TIMEOUT command is given.
SET TIMEOUT is not meaningful in a single-user system.
TIMEPICTURE 'picture'
This edits TIME data type values according to the specified print picture. See the description of the COLUMN command for information about print picture.
There is no specific authority required to set this option.
TIMESTAMP
If this is ON, each activity log entry has a timestamp.
You must have DBA authority to enable this option.
The default is OFF.
TM integer constant
This sets the top margin of a report (the number of blank lines that is displayed at the top of a page). The value can range from 0 to PAGESIZE. The default is 0.
There is no specific authority required to set this option.
TRACE
If this is ON, statement tracing is enabled. It is cursor specific.
When tracing is enabled, SQLBase displays each line of the procedure before it executes. The output includes the SQLBase process number, the line number of each statement, and the statement being executed.
By default, statement tracing is OFF.
TRACEFILE
If you are accessing a multi-user server, this option directs statement trace output to a file on the server or to the server’s Process Activity screen. If you are accessing a single-user engine, this option directs statement trace output to the screen. This option is cursor specific.
If you have one or more TRACE statements embedded in a procedure and statement tracing is enabled as well, the output of both traces is displayed on the server’s Process Activity screen. Redirecting statement trace output to a file in this situation can make it easier to read both traces.
Specify a file name and optionally, a path, to which to direct trace output. Specify OFF to direct trace output to the server’s Process Activity screen.
Examples
COMMIT after each SQL command.
SET AUTOCOMMIT ON;
Measure and display elapsed time.
SET ETIME ON;
Suppress default column headings.
SET HEADING OFF;
Change the isolation level to Read Only.
SET ISOLATION RO;
Return only one row after a SELECT.
SET LIMIT 1;
Set the line size to 75.
SET LINESIZE 75;
Wrap wide rows.
SET LINEWRAP ON;
When a NULL is encountered in a column, display 'NA'.
SET NULLS 'NA';
Do not pause for user confirmation of command execution.
SET PAUSE OFF;
See also
SHOW
SET SERVER
This command establishes a server connection.
A server connection is required to perform the following administrative operations:
It is not necessary to give a SET SERVER command when using a single-user version of SQLBase.
Clauses
server ID
The name of the SQLBase server. Database servers are named by the servername keyword in sql.ini.
server password
If the server password is set (with the password keyword in sql.ini), a case insensitive comparison is performed between the server password and the specified password. If a server password has not been set in sql.ini, it does not need to be specified.
DBDIR
This specifies the drives, paths, and directory names for the home database directory or directories. If you execute a SET SERVER DBDIR command, it changes the setting of the dbdir keyword in the sql.ini file.
OFF
A SET SERVER OFF command breaks the server connection.
Example
SET SERVER PROD/SECRET;
SERVER IS SET
...
SET SERVER OFF;
SERVER IS OFF
See also
BACKUP DATABASE
RESTORE DATABASE
SET RECOVERY
These commands are listed in the SQL Language Reference.
CREATE DATABASE
DEINSTALL DATABASE
DROP DATABASE
INSTALL DATABASE
SET SPOOL
This command records the SQLTalk session. A spool file records all screen activity for the SQLTalk session. Spooling does not affect screen display. This file is maintained on a per user session basis.
Spooling is turned off by the SET SPOOL OFF command or when you exit from the SQLTalk session.
There is no specific authority required to set this option.
The default for spooling is OFF.
Clauses
file name
You can specify the name of the spool file or a default name will be assigned when you specify ON. You can specify any file name, including a drive and path.
If you start SQLTalk with the NOCONNECT option, the only way that you can turn on spooling is by executing a SET SPOOL command with a file name. SET SPOOL ON does not work when you start SQLTalk with the NOCONNECT option.
If you start spooling to the same file at a later session, you will be asked the choice of appending to or writing over the existing file.
The default spool file name is:
current directory\database name.spl
If the spool file already exists, you are given a choice to append to the file, overwrite the file, or abort the command.
Examples
SET SPOOL ON;
SET SPOOL OFF;
SET SPOOL MYFILE;
SHOW
This command displays information about the SQLTalk environment.
Clauses
options
This is the name of one of the SET options. The current value of the specified option is displayed. The SHOW command can also display its own options settings, which are listed below.
cursor-number | ALL
This displays information associated with the specified cursor or all cursors in the current database. It includes the connection handle associated with each cursor and augments the information that you can obtain using the CONNECT option described in this section or SHOW CONNECTION on page 136.
CLOSETRANSMETHOD
This displays whether a COMMIT or ROLLBACK is issued before closing a connection handle or disconnecting a final cursor that was implicitly connected or connected using the CONNECT command (which specifies the cursor number along with the following clause options: databasename, username, and password).
COLLATION
This displays the collation for the current connection. Collation specifies the language that SQLBase uses to sort data on a server, a particular database, or a particular connection.
CONNECT
This displays information associated with the current database, including cursor number, database name, and user name. The current database is that which was established at sign-on or with the most recent CONNECT or USE command.
DATABASECOLLATION
This displays the default collation for the database. Collation specifies the language that SQLBase uses to sort data on a server, a particular database, or a particular connection.
LOG
This displays the log parameter information.
REPORT
This displays the current report format options. These include the SET commands (not all options are included, however), and the BREAK, BTITLE, COMPUTE and TTITLE commands.
ROWCOUNT
This displays the number of rows in a result set. INSERTs into a result set increase the ROWCOUNT. However, DELETEs, which show up as blanked-out rows in result set mode, do not decrease the ROWCOUNT. The deleted rows disappear on the next SELECT.
SYSTEM
This displays configuration information, such as the heap size and whether a single-user or a multi-user system of SQLBase is being used.
VERSION
This displays the current release version of the server software.
Examples
Display the size of each display line.
SHOW LINESIZE;
Find out if all rows selected are being displayed.
SHOW LIMIT;
Display cursor, database and user information.
SHOW CONNECT;
Display the status of the activity log.
SHOW ACTIVITYLOG;
Display the status of the activity log timestamp.
SHOW TIMESTAMP;
See also
SET
See also
COLUMN
COMPUTE
SHOW REPORT
SHOW CONNECTION
This command displays information about your specified connection or the connection information for the current cursor if you do not specify a connection name. The current cursor is the one that is executing the current SQL command.
The information displayed for the connection includes the database name and the user name for the connection.
Clauses
connection name
If desired, specify the name of the connection. If you omit the connection name, connection information is displayed for the current cursor.
Example
The following example displays information on connection CH2:
SHOW CONNECTION CH2;
See also
BEGIN CONNECTION
 
SHOW DATABASES
This command displays the databases available on a given server.
On a single-user system, this command displays the databases accessible on that computer. On a network, this command displays the databases installed on the network.
Clauses
LOCALLY
This shows all databases available on the local computer.
ON SERVER
This displays the databases on the specified server. Database servers are named by the servername keyword in sql.ini.
Example
SHOW DATABASES ON SERVER ACCTPAY;
See also
SET SERVER
SHUTDOWN
This command shuts down SQLBase server or a database.
Note: Do not use the DISCONNECT clause if you are not using transaction logging.
Clauses
SERVER
This performs the operation at the server level.
ON
This stops the server from accepting new connections.
Anyone trying to connect to the database receives a “shutdown server in progress” message. All current users remain connected and all current transactions continue.
OFF
This starts accepting new connections again after you have given a SHUTDOWN SERVER ON command. You can also use this after SHUTDOWN SERVER EXIT, before the last user has disconnected, to make the server available for connections again, and to turn off the exit condition.
EXIT
This stops accepting new connections and exits the server after the last user disconnects.
DISCONNECT
This shuts down the server immediately. Any connected users are disconnected.
Warning: Only give this command when recovery is turned on.
DATABASE
This performs the operation at the database level.
databasename
The name of the database on which to perform the operation.
ON
This shuts down the specified database. You must be connected to the database as DBA.
Anyone (except for a user with DBA authority) trying to connect to the database receives a “shutdown in progress” message. All current users remain connected and all current transactions continue.
OFF
This makes the database available for connections again.
Example
This following example shuts down the server, disallowing new connections and waiting until all active users have disconnected:
SET SERVER servername;
SHUTDOWN SERVER EXIT;
SET SERVER OFF;
SQLGET
This command retuns a single value, usually related to the configuration of the SQLBase server or client. It is functionally similar to the sqlget API function described in the SQLBase API Guide, and it uses many of the same parameter values as the API function. However, the API function processes more parameter values; the SQLGET command in SQLTalk processes only a subset of the API’s parameter values.
All parameter values require that your SQLTalk session already have an existing server or database connection before they can be used successfully with this command. This is noted below.
The meaning of each parameter value listed below is explained in detail in the sqlget section of chapter 5 of the SQLBase API Guide. Briefer summaries are included here.
An example of the SQLGET command is:
SQLGET SQLPSINI
This command would return a value like c:\program files\Gupta, since SQLPSINI is the parameter value used for querying the file name and location of the server’s configuration file.
Here are the parameter values supported by the SQLGET command:
The following parameters return a string, and require an existing server connection.
SQLPTPD: the directory where SQLBase places temporary files
SQLPNPF: net prefix character
SQLPNLG: net log file
SQLPERF: error code translation file
SQLPCTY: country file section
SQLPCTS: character set file name
SQLPLRD: local result set directory
SQLPALG: process activity file name
SQLPPTH: path separator on server machine
SQLPCLN: client name with internal cursor
SQLPMID: mail identification string
SQLPAID: adapter identification string
SQLPNID: network identification string
SQLPUID: user app identification string
SQLPCIS: client identification string
SQLPSINI: server configuration file location
SQLPSDIR: server module location
 
The following parameters return a string, and require an existing database connection.
SQLPLSS: last SQL statement
SQLPEXP: execution plan
SQLPSVN: name of server
SQLPCINI: client configuration file location
 
The following parameters return a number, and require an existing server connection.
SQLPOOJ: Oracle outer join
SQLPNCT: netcheck algorithm
SQLPNCK: check network transmission errors
SQLPLCK: lock limit allocations
SQLPSWR: write defaults to configuration file
SQLPCCK: client check
SQLPCGR: contiguous cache pages
SQLPANL: apply net log (Unify tech support only)
SQLPWKL: maximum work space limit
SQLPWKA: work space allocation unit
SQLPUSR: maximum number of users
SQLPTMO: client request timeout
SQLPTSS: thread stack size
SQLPTHM: thread mode
SQLPSTC: sort cache size, in pages
SQLPSIL: silent mode
SQLPROM: read-only isolation level
SQLPTMZ: system timezone
SQLPDLK: number of deadlocks
SQLPAPT: activate process timing
SQLPSTA: statistics for server
SQLPCTL: command time limit
SQLPPLV: level of process activity display
SQLPTMS: timestamps on process activity
SQLPOSR: operating system statistics sample rate
SQLPAWS: operating system averaging window size
SQLPCAC: database cache size, in KB
 
The following parameters return a number, and require an existing database connection.
SQLPDTL: database command time limit
SQLPCXP: execution plan cost
SQLPIMB: input message buffer size
SQLPOMB: output message buffer size
SQLPCVC: catalog version counter
SQLPCLG: commit-order logging
 
SQLSET
This command sets a single value, usually related to the configuration of the SQLBase server or client. It is functionally similar to the sqlset API function described in the SQLBase API Guide, and it uses many of the same parameter names as the API function. However, the API function processes more parameter names; the SQLSET command in SQLTalk processes only a subset of the API’s parameter names.
Some configuration values that could theoretically be handled by the SQLSET command are instead handled in the SET command. Read the documentation for that command to see these additional configuration values.
All parameter names require that your SQLTalk session already have an existing server or database connection before they can be used successfully with this command. This is noted below.
The meaning of each parameter name listed below is explained in detail in the sqlset section of chapter 5 of the SQLBase API Guide. Briefer summaries are included here.
An example of the SQLSET command is:
SQLSET SQLPCGR 60
This command would set the cachegroup size to 60 pages.
Here are the parameter names supported by the SQLGET command:
The following parameters require a string value, and require an existing server connection.
SQLPTPD: the directory where SQLBase places temporary files
SQLPNPF: net prefix character
SQLPNLG: net log file
SQLPERF: error code translation file
SQLPCTY: country file section
SQLPCTS: character set file name
SQLPLRD: local result set directory
SQLPSPF: server prefix
SQLPALG: process activity file name
SQLPPTH: path separator on server machine
SQLPCLN: client name with internal cursor
SQLPMID: mail identification string
SQLPAID: adapter identification string
SQLPNID: network identification string
SQLPUID: user app identification string
SQLPPSW: server password
 
The following parameters require a string value, and require an existing database connection.
SQLPSVN: name of server
 
The following parameters require a number value, and require an existing server connection.
SQLPOOJ: Oracle outer join
SQLPNCT: netcheck algorithm
SQLPNCK: check network transmission errors
SQLPLCK: lock limit allocations
SQLPSWR: write defaults to configuration file
SQLPCCK: client check
SQLPCGR: contiguous cache pages
SQLPANL: apply net log (Unify tech support only)
SQLPWKL: maximum work space limit
SQLPWKA: work space allocation unit
SQLPUSR: maximum number of users
SQLPTMO: client request timeout
SQLPTSS: thread stack size
SQLPTHM: thread mode
SQLPSTC: sort cache size, in pages
SQLPSIL: silent mode
SQLPROM: read-only isolation level
SQLPTMZ: system timezone
SQLPAPT: activate process timing
SQLPSTA: statistics for server
SQLPCTL: command time limit
SQLPPLV: level of process activity display
SQLPTMS: timestamps on process activity
SQLPOSR: operating system statistics sample rate
SQLPAWS: operating system averaging window size
SQLPCAC: database cache size, in KB
SQLPBSS:
 
The following parameters require a number value, and require an existing database connection.
SQLPDTL: database command time limit
SQLPIMB: input message buffer size
SQLPOMB: output message buffer size
SQLPCLG: commit-order logging
 
STORE
This command compiles and stores a query, data manipulation command, or procedure for later execution. SQLBase stores the command’s or procedure’s execution plan as well, so subsequent execution is very fast.
If your data changes frequently, you should update the statistics and re-store stored procedures on a regular basis. This recompiles the stored procedures and recreates their execution plans.
You cannot store data definition or data control commands (such as ALTER, CREATE, DROP, or GRANT).
SQLBase keeps information about stored commands and procedures in the SYSADM.SYSCOMMANDS system table.
Other users can use stored commands by retrieving them as user.command name. For example, if you stored commands as SYSADM, other users can retrieve them as SYSADM.command. The users must have access privileges to the tables involved.
Stored commands and procedures remain in the database until you drop them with ERASE.
If you create a procedure with PROCEDURE and specify it as static, you must store the procedure with this command before you execute it.
Clauses
auth ID.
If you are not the creator of the command or procedure, specify the authorization ID of the owner.
cmd/procedure name
The name of the command or procedure to store. If you specify a name that already exists, SQLBase prompts you for confirmation to overwrite it.
cmd/procedure text
The text of the command or procedure to compile and store. You can include bind variables in the command or procedure, but not input data. You supply input data at execution time; data binding takes place when SQLBase executes the command or procedure.
Example
This following example compiles and stores the command MYQUERY:
STORE myquery
SELECT * FROM EMP WHERE DEPTNO = :1;
The next example compiles and stores a command called ADDNAMES. The EXECUTE command contains bind variables, so you must enter data.
STORE ADDNAMES INSERT INTO FRIENDS (NAME) VALUES (:1);
EXECUTE ADDNAMES
\
PROCESSING DATA
LEN
MARGE
BETTY
/
3 Rows Inserted
The following command stores a procedure.
STORE PRESPROC
Procedure PR_PRES
Local Variables
Sql Handle Cur1
Actions
Call SqlConnect (Cur1)
Call SqlStore (Cur1, 'presname', 'Select pres_name from \ sysadm.president')
Call SqlDisconnect (Cur1);
See also
ERASE
EXECUTE
TTITLE
This command displays a top title on each page of a report. Once you have entered this command, the results of all subsequent SELECT commands are displayed with the top title.
You can change the top title by entering another TTITLE command, or you can temporarily turn off TTITLE by entering the OFF command. You can turn TTITLE on again with the ON command.
Clauses
'header-string'
The header string must be enclosed within single quotes.
The string can consist of up to three separate substrings, each delimited by the vertical bar ( | ) character. The | represents a new-line character that causes the subsequent substring to be displayed on a new line.
The lines of the title are automatically centered. The display width of the page is specified by the SET LINESIZE command.
DATE
If this is ON, the current date is displayed in the top left-hand corner of each page of the report. The date is displayed in Mon dd, yyyy format (such as April 25, 1996). It is always displayed on the first line of the page, regardless of the number of lines in the title.
PAGE
If this is ON, the current page number is displayed on the top right hand corner of each page of the report. The display format is Page n (such as Page 11). It is always displayed on the first line of the page.
Example
Shown below is a TTITLE command and the resulting title.
TTITLE 'EMPLOYEE LIST|as of December 1996'DATE
ON PAGE ON;
See also
BTITLE
SET LINESIZE
SHOW REPORT
UNDO
This command is used in restriction mode to undo the current result set and return the result set to the state it was in before the last SELECT. You cannot issue UNDO multiple times to return to even earlier result set states; it can only be issued once.
Example
SET SCROLL ON;
SET RESTRICTION ON;
SELECT PRES_NAME, STATE_BORN, PARTY FROM
PRESIDENT WHERE STATE_BORN = 'VIRGINIA';
PRES_NAME STATE_BORN PARTY
============ ========== ==========
Washington G Virginia Federalist
Jefferson T Virginia Demo-Rep
Madison J Virginia Demo-Rep
Monroe J Virginia Demo-Rep
Harrison W H Virginia Whig
Tyler J Virginia Whig
Taylor Z Virginia Whig
Wilson W Virginia Democratic
8 ROWS SELECTED
SELECT PRES_NAME, STATE_BORN, PARTY FROM
PRESIDENT WHERE PARTY = 'Whig';
PRES_NAME STATE_BORN PARTY
============ ========== ======
Harrison W H Virginia Whig
Tyler J Virginia Whig
Taylor Z Virginia Whig
3 ROWS SELECTED
UNDO;
UNDO COMPLETED
SELECT PRES_NAME, STATE_BORN, PARTY FROM PRESIDENT;
PRES_NAME STATE_BORN PARTY
============ =========== ===========
Washington G Virginia Federalist
Jefferson T Virginia Demo-Rep
Madison J Virginia Demo-Rep
Monroe J Virginia Demo-Rep
Harrison W H Virginia Whig
Tyler J Virginia Whig
Taylor Z Virginia Whig
Wilson W Virginia Democratic
8 ROWS SELECTED
See also
SET FILTER
SET RESTRICTION
SET SCROLL
USE
This command establishes a new current cursor. You can use this command to switch from one cursor to another in a multi-cursor transaction after a CONNECT has been established for each cursor.
Once a CONNECT authorizes a cursor, you can switch to it later without further authorization.
Clauses
cursor number
This indicates the cursor that will become the current cursor. You must have previously established a connection to the specified cursor with the CONNECT command.
Examples
You establish a connection to a second authorization ID, using cursor 2. After you enter some commands using that cursor, cursor 1 is activated with the USE command.
CONNECT 2 TRUDY/X;
CURSOR 2 CONNECTED TO DEMO
CREATE TABLE X(A INT);
TABLE CREATED
INSERT INTO X VALUES (:1)
\
1
2
/

2 ROWS INSERTED
 
SELECT * FROM X;
A
===
1
2
2 ROWS SELECTED
 
USE 1;
SELECT * FROM X;
SELECT * FROM X
^
ERROR: TABLE HAS NOT BEEN CREATED
 
USE 2;
DROP TABLE X;
TABLE DROPPED
See also
CONNECT
 
SQLTalk Command Reference
 

Unify Inc.