|
|
|
SET
Syntax
SET set_option ;
set_option ::
HISTORY number_statements
| PAGESIZE number_lines
| LINESIZE number_characters
| COMMAND LINES number_lines
| REPORT { ON | OFF }
| ECHO { ON | OFF }
| PAUSE { ON | OFF }
| TIME { ON | OFF }
| DISPLAY COST { ON | OFF }
| AUTOCOMMIT { ON | OFF }
| TRANSACTION ISOLATION LEVEL isolation_level
| CONNECTION { database_name | DEFAULT }
Description
The SET statement changes various characteristics of an interactive c-treeSQL session.
Arguments
HISTORY
Sets the number of statements that ISQL will store in the history buffer. The default is 1 statement and the maximum is 250 statements.
PAGESIZE number_lines
Sets the number of lines per page. The default is 24 lines. After each number_lines lines, ISQL executes any DISPLAY ON PAGE statements in effect and re-displays column headings. The PAGESIZE setting affects both standard output and the file opened through the SPOOL statement.
LINESIZE
Sets the number of characters per line. The default is 80 characters. The LINESIZE setting affects both standard output and the file opened through the SPOOL statement.
COMMAND LINES
Sets the number of lines to be displayed. The default is 1.
REPORT ON | OFF
SET REPORT ON copies only the results of SQL statements to the file opened by the SPOOL filename ON statement. SET REPORT OFF copies both the SQL statement and the results to the file. SET REPORT OFF is the default.
ECHO ON | OFF
SET ECHO ON displays SQL statements as well as results to standard output. SET ECHO OFF suppresses the display of c-treeSQL statements, so that only results are displayed. SET ECHO ON is the default.
PAUSE ON | OFF
SET PAUSE ON prompts the user after displaying one page of results on the screen. SET PAUSE ON is the default.
TIME ON | OFF
SET TIME ON displays the time taken for executing a database query statement. SET TIME OFF disables the display and is the default.
DISPLAY COST ON | OFF
SET DISPLAY COST ON displays the values the c-treeSQL optimizer uses to calculate the least-costly query strategy for a particular c-treeSQL statement.
The UPDATE STATISTICS statement updates the values displayed by SET DISPLAY COST ON. SET DISPLAY COST OFF suppresses the display and is the default.
AUTOCOMMIT ON | OFF
SET AUTOCOMMIT ON commits changes and starts a new transaction immediately after each SQL statement is executed. SET AUTOCOMMIT OFF is the default. SET AUTOCOMMIT OFF requires that you end transactions explicitly with a COMMIT or ROLLBACK WORK statement.
Note: Be careful using the automatic commit logic when working with cursors. When automatic commit is enabled, the logic will execute a commit operation after each database access, which will close any open cursor. If you receive an error ‑20039 (Open for non-select statement) then most likely your cursor has been closed.
TRANSACTION ISOLATION LEVEL isolation_level
Specifies the isolation level. Isolation levels specify the degree to which one transaction can modify data or database objects being used by another concurrent transaction. The default is 3. See the SET TRANSACTION ISOLATION LEVEL statement in the c-treeSQL Reference Manual for more information on isolation levels.
CONNECTION { database_name | DEFAULT}
Sets the active connection to database_name or to the default connection. See the description of the CONNECT statement in the c-treeSQL Reference Manual for details on connections.
Notes
SET REPORT and SET ECHO are similar:
Other statements control other characteristics of an interactive SQL session:
Examples
ISQL> -- Illustrate PAGESIZE
ISQL> DISPLAY "Here's a page break!" ON PAGE
ISQL> SET PAGESIZE 4
ISQL> BREAK ON PAGE;
ISQL> SELECT TBL FROM SYSTABLES;
TBL
---
sys_chk_constrs
Here's a page break!
TBL
---
sys_chkcol_usage
sys_keycol_usage
Here's a page break!
.
.
.
ISQL> SET DISPLAY COST ON
ISQL> -- Select from the one-record SYSCALCTABLE table:
ISQL> SELECT * FROM SYSCALCTABLE;
Estimated Cost Values :
-----------------------
COST : 8080
CARDINALITY : 200
TREE SIZE : 3072
FLD
---
100
