|
|
|
|
|
|
BSQL Commands
Command
Function
READ INPUT, see SET ECHO.BSQL commands are not case sensitive.
About BSQL Syntax Descriptions
For information on how to read the syntax diagrams that follow, please refer to Mimer SQL Reference Manual, Reading SQL Syntax Diagrams.
CLOSE
Syntax
Description
The command closes the specified log file. If no log type is specified, all active log files are closed.
DESCRIBE
Syntax
Where
object-typeis one of the object types listed below.The
FOR type_nameclause can only be specified if object_class is eitherMETHODorSPECIFICATION.Description
The
DESCRIBEcommand presents the following menu, when no object is specified:Menu for describe 1. Databank 8. View 15. Collation 2. Domain 9. Module 16. Type 3. Ident 10. Procedure 17. Method 4. Index 11. Function 18. Specification 5. Synonym 12. Trigger 19. Specific 6. Table 13. Sequence 20. Statement 7. View 14. Shadow 0. ExitChoosing an item presents a submenu for choosing between different
DESCRIBEfunctions - see the table that follows for details.Entering an exclamation mark (
!) in the Select field returns to the previous menu level. Entering a double exclamation mark (!!) terminates theDESCRIBEsession.Specifying an object type and name in the command executes the first menu choice for that object. The for type-name option is used when describing methods and method specifications. If no object name is given, the user is prompted for a name.
Selection numbers should not be used in script files, since they may change in future versions.
Note: DESCRIBE is not available when connected to a Mimer SQL server of version 8.1 or older.
DESCRIBE DATABANK Options
DESCRIBE IDENT Options
DESCRIBE INDEX Options
Result
sort order
uniqueness
index algorithm
comment
creation date.DESCRIBE SYNONYM Options
Result
comment
creation dateDESCRIBE TABLE Options
DESCRIBE VIEW Options
Result
updatability
check option
comment
creation date.DESCRIBE MODULE Options
Result
comment
creation date.DESCRIBE PROCEDURE Options
DESCRIBE FUNCTION Options
DESCRIBE TRIGGER Options
DESCRIBE SEQUENCE Options
DESCRIBE SCHEMA Options
Result
contained objects
comment
creation date.DESCRIBE SHADOW
Result
databank name
file name
comment
creation dateDESCRIBE COLLATION
DESCRIBE TYPE
DESCRIBE METHOD
DESCRIBE SPECIFICATION
Describe a method specification.
DESCRIBE SPECIFIC
Describe specific can be used to describe overloaded routines by using their specific name.
DESCRIBE STATEMENT
EXIT
Syntax
Description
GET DIAGNOSTICS
Get diagnostics for statement.
Syntax
Description
Presents all status and diagnostics information for the preceding statement.
LIST
Lists information on a specified object.
Syntax
Where
object-typeis one of the object types listed below.Description
The
LISTcommand presents the following menu, if no object-type is specified:Menu for List 1. Databanks 8. Views 15. Shadows 2. Domains 9. Modules 16. Collations 3. Idents 10. Procedures 17. Methods 4. Indexes 11. Functions 18. Specifications 5. Objects 12. Triggers 19. Types 6. Synonyms 13. Sequences 20. Statements 7. Tables 14. Schemata 0. ExitChoosing an item presents a submenu for choosing between different
LISTfunctions - see the table that follows for details.Entering an exclamation mark (
!) in the Select field returns to the previous menu level. Entering a double exclamation mark (!!) returns two levels.Giving an object type in the command executes the first menu choice for that type.
Selection numbers should not be used in script files, because the may change in future versions.
Note: LIST is not available when connected to a Mimer SQL server of version 8.1 or older.
LIST COLLATIONS Options
Result
LIST DATABANKS Options
Result
LIST DOMAINS Options
Result
LIST FUNCTIONS Options
Result
LIST IDENTS Options
Result
LIST INDEXES Options
Result
LIST MODULES Options
Result
LIST METHODS Options
LIST OBJECTS Options
Result
LIST PROCEDURES Options
Result
LIST SCHEMATA Options
LIST SEQUENCES Options
Result
LIST SHADOWS Options
Result
LIST SPECIFICATIONS Options
LIST STATEMENTS Options
Result
LIST SYNONYMS Options
Result
LIST TABLES Options
Result
LIST TRIGGERS Options
Result
LIST TYPES Options
Result
LIST VIEWS Options
LOG
Logs input, output or both to a specified sequential file.
Syntax
Description
All input, output or both will be logged in the specified sequential file.
If
ONis specified a new file will always be created, otherwise the log data is appended to the file.Logging is paused with the
SET LOG OFFcommand and is resumed with theSET LOG ONcommand. UseCLOSEto stop logging permanently.Using the
ASoption, you can set the file format toLATIN1,UTF8,UTF16,UTF16BE,UTF16LE,UTF32,UTF32BEorUTF32LE.READ INPUT
Reads commands from a sequential file.
Syntax
Description
Commands and SQL statements are read from the specified file.
When
READ ALL, both commands and prompt answers are read from the sequential file. (READ ALLis default mode.)When
READ COMMANDis specified, commands are read from the input file while prompt answers are taken from the script file or interactively (depending on the situation).Using the
ASoption, you can set the file format toLATIN1,UTF8,UTF16,UTF16BE,UTF16LE,UTF32,UTF32BEorUTF32LE.READLOG
Obtains information about logged operations.
Syntax
Description
READLOG is a Mimer SQL function which enables you to read the contents of
LOGDBso that you can check logged operations performed on the database since the last backup copy or incremental backup was taken.You can use READLOG as an audit trail or, in the event of a system failure, to determine which databanks need to be restored (i.e. which databanks have been altered since the last backup).
Functions
READLOG enables you to select information from
LOGDBon the basis of time interval, ident performing the operation, and specified databanks or tables.This is particularly useful in production systems where
LOGDBcan contain a large number of entries.Authorization
To list the log for selected tables or all tables in a databank, you must have
SELECTaccess on the tables in question.To list the log for the entire database, you must have
BACKUPprivilege.Using the READLOG Functionality
You control the
READLOGfunctionality using a menu from which different listing options may be set before finally performing the read operation.The different listing options are set by using menu selections 1-5. The menu is re-displayed after selecting any of these so that further options may be set for the listing.
When all the desired listing options have been set in this way, a listing is produced from the log by choosing menu selection 6, 7 or 8 from under
List operations.-- Read log -- List definitions List restrictions List operations ---------------- ----------------- --------------- 1. Log file 3. Time interval 6. Specified tables 2. File properties 4. Ident 7. Tables in databank 5. Databank 8. All (no data) 0. EXITList Definitions - Output Control
- Log File
Choosing
Log fileallows you to specify the name of a sequential file into which the listing is to be placed. In systems where the terminal may be addressed by a logical file name, this may be given to display the listing on the terminal.If this option is not selected, a sequential file with the default name
RDLOGLwill be used.The following example sets the log file explicitly:
SQL>READLOG; -- Read log -- List definitions List restrictions List operations ---------------- ----------------- --------------- 1. Output 3. Time interval 6. Specified tables 2. Properties 4. Ident 7. Tables in databank 5. Databank 8. All (no data) 0. EXIT Select: 1 Output to file or terminal (F/T) [F]: F Log list file: READLOG.DAT
- List Properties
The
file propertieschoice is used to set either the width of a report or the format for the log file. The file format can be one of the following encodings:1. Default 2. Latin 1 3. UTF 8 4. UTF 16 5. UTF 16 big endian 6. UTF 16 little endian 7. UTF 32 8. UTF 32 big endian 9. UTF 32 litte endianDefault means the encoding specified by the locale settings for the client.
List Restrictions
- Time interval
This option allows the listing to be restricted to a given time interval, specified as a starting time and a finishing time.
Times are given as a single parameter representing year, month, day, hour, minute and second in the format
YYYYMMDDHHMMSS.If an incomplete time specification is given (truncated from the right), the remaining parameters are taken as low for the starting time and high for the finishing time. Thus giving 200211 as both the starting and finishing time, lists the log from the beginning to the end of November 2002.
A default time value is assumed if no time interval is specified, or may be chosen for starting or finishing time by specifying a `blank' time.
If no start time is specified, the time at the beginning of the log is assumed. If no end time is specified, the time at the end of the log is assumed.
If neither a start time nor an end time is specified, the following message is displayed:
** No time restrictionA selected time interval applies for all subsequent list operations in the current session until the time interval is reset.
A time interval of two months has been selected in the following example:
Select: 3 Format : YYYYMMDDHHMMSS Starttime: 201211 Endtime : 201212
- Ident
Selecting an ident restricts the listing to operations performed by that ident. Only one ident may be selected for a given listing.
The default setting lists operations performed by all idents.
The default applies if no ident restriction is selected, or may be chosen by specifying a blank ident. If the default is chosen, the following message is displayed:
** No ident restrictionA selected ident applies for all subsequent list operations in the current session until a new ident is specified.
Select: 4 Identname: mimer_store
- Databank
Selecting a databank restricts the listing to operations performed on that databank. This option must be specified if the list operation 7 (
Tables in databank) is to be used. Only one databank may be selected for a given listing.If no databank is specified, the list operation is done for all databanks. If this is the case, the following message is displayed:
** No databank restrictionA selected databank applies for all subsequent list operations in the current session until the databank is reset.
Select: 5 Databank: mimer_ordersList Operations
- Specified Tables
This option activates listing of the log for selected tables in the database.
As many tables may be specified as are required, with the table name qualified, if necessary, by the name of the schema to which it belongs.
If no schema is specified, the schema with the same name as the current ident is assumed.
Databank restrictions selected with option 5 are ignored if specified tables are selected. However, any ident and time restrictions selected with options 3 and 4 are applied.
The ident running
READLOGmust haveSELECTaccess on the requested tables, otherwise the following message is displayed for the table in question:** No select access on tableIf a non-existent table is requested, the following message is displayed:
** No such tableErrors of this type do not abort the listing if valid and invalid requests are mixed in the same operation.
The list operation is activated by giving a blank response to the prompt for a table name when all the required tables have been specified, as in the following example:
Select: 6 Table: HOTELADM.EMPLOYEE Table: HOTELADM.STAFF Table: HOTELADM.SALARY Table:Note: The list operation can be interrupted by entering an exclamation mark !.
- Tables in Databank
Operations on all tables in the databank specified under option 5 are listed. If no databank has been selected, the following message is displayed and the user must select a new option:
**Databank not enteredTime or ident restrictions selected with options 3 or 4 are applied.
Data is listed only for those tables to which the ident running
READLOGhasSELECTaccess.Tables to which access is denied are indicated by the following message in the log list file:
Table <schema-name.table-name> - No select access
- All (No Data)
This option lists logged operations without details of data records (see below). The ident running
READLOGmust haveBACKUPprivilege.If the privilege is not held by the current ident the following error message is displayed:
** AUTHORIZATION FAILUREOutput Format
The output from
READLOGis divided into transactions, showing the date and time, the ident performing the transaction (with entered program idents where appropriate) and the number of database records read during the transaction.Note: The output does not contain statements for reconstructing the logged operations - it is simply a documentary record of the transactions performed on the database
If list operations 6 or 7 (select by
Specified tablesorTables in databank) are selected, the contents of the affected rows in the table are displayed. Insert and delete operations are listed as a single row. Update operations are recorded as the state of the row before and after the update.If the list operation 8,
All (no data), is selected the operations are listed without the data records.SET ECHO
Controls whether or not lines read during
READ INPUTare echoed.Syntax
Description
When echo is set to
ON, lines read duringREAD INPUTare echoed to the BSQL window or log file. When echo is set toOFF, these lines are not echoed. The default value isON.The setting has no effect on the output of responses to BSQL commands and statements.
SET EXECUTE
Activate or deactivate the execution of queries.
Syntax
Description
When execute is set to off no queries will be executed. This can be useful when using the explain facility or when testing a script for correctness.
Note: The SET EXECUTE OFF mode also affects statements like CREATE INDEX and DROP INDEX. I.e. do not forget to SET EXECUTE ON to be able to create or drop an index when examining different explain outputs for a query.
SET EXPLAIN
Activate or deactivate the explain facility.
Syntax
Description
When the explain facility is activated the execution plan for the query is shown. By default the query will be executed, to avoid this behavior the
SET EXECUTEcommand can be used. (Note thatSET EXECUTE OFFapplies to DDL statements as well, e.g.CREATE INDEXwill only verify correctness, no index will be created.)The execution plan will show different operations and the sequence in which these operations are performed. The following operations can be shown when the explain facility is active:
Example:
SQL>CREATE TABLE expltab (c1 integer primary key, c2 character(10)); SQL>SET EXPLAIN ON; SQL>SET EXECUTE OFF; SQL>SELECT * FROM expltab ORDER BY c2; Start of explain result L1: Sequential read JAMES.EXPLTAB, end of table goto L2 Insert into temporary table (1) (25 bytes) goto L1 L2: Sort temporary table (1) L3: Sequential read temporary table (1), end of table goto end Record found, goto L3 end: End of explain result SQL>SET EXECUTE ON; SQL>CREATE INDEX expltab_c2 ON expltab (c2); SQL>SET EXECUTE OFF; SQL>SELECT * FROM expltab ORDER BY c2; Start of explain result L1: Index sequential read using index JAMES.EXPLTAB_C2, end of table goto end Record found, goto L1 end: End of explain result SQL>For more Explain details, see the article http://developer.mimer.com/howto/howto_46.htm.
SET LINECOUNT
Sets the length of the BSQL window.
Syntax
Description
The
LINECOUNTvalue defines the length of the BSQL window.If
LINECOUNThas a value greater than zero, output will temporarily be stopped after the number of lines defined for the value.After the
Continue-prompt, the user will have the choice of either continuing with the display or terminating the output.Answering `
Y' (default) implies that the output will continue until the number of lines is reached again.Answering `
N' terminates the output. Answering `G' will ignore the line count and the output will continue until all data are displayed.If
LINECOUNTis zero, the output will continue until all data is displayed.The value of
LINECOUNTmust either be zero or >= 10.Default
If BSQL is run from a script job,
LINECOUNTis zero by default. For interactive operation, the default value is environment-dependent.SET LINESPACE
Sets the number of blank lines between each output record.
Syntax
Description
The
LINESPACEvalue defines the number of blank lines to be written between each output record. This value is only used when printing the result of aSELECTstatement.The maximum value for
LINESPACEis 9. The default value is 0.SET LINEWIDTH
Specifies the width of the output.
Syntax
Description
The
LINEWIDTHvalue defines the maximum line width for output to the BSQL window or log file.The value for
LINEWIDTHcannot be set to a value less than 20 or larger than 255.SET LOG
Stops or resumes logging input, output or both.
Syntax
Description
When
SET LOGis set toOFF, logging of input, output or both in a sequential file is temporarily stopped.Resume logging with the
SET LOG ONcommand.If no input/output log is specified, all active logs are stopped or resumed.
SET MAX_BINARY_LENGTH
Specifies the maximum display length for binary columns.
Syntax
Description
The
MAX_BINARY_LENGTHvalue defines the number of elements that are displayed when selecting data that is defined as binary, binary varying or binary large object.The default value is 15 000 and the value must be between 1 and 15 000.
As a binary string is shown as a hexadecimal string with two characters for each element the display length will be twice the value of
MAX_BINARY_LENGTH.SET MAX_CHARACTER_LENGTH
Specifies the maximum display length for character columns.
Syntax
Description:
The
MAX_CHARACTER_LENGTHvalue defines the number of characters that are displayed when selecting data that is defined as any character data type.The default value is 15 000 and the value must be between 1 and 15 000.
SET MESSAGE
Specifies whether or not messages should be displayed.
Syntax
Description
Specifies whether or not result messages such as
One row foundetc. are written.SET OUTPUT
Specifies whether or not output should be displayed.
Syntax
Description
When
OUTPUTis set toON, the output from BSQL is written to the BSQL window (or log file). When it is set toOFF, the output does not appear.SET PAGELENGTH
Specifies the page size of the output log file.
Syntax
Description
The
PAGELENGTHvalue defines the page size of the file on which output is logged, i.e. at what interval a page break will be performed. A value of zero will result in no page breaks.The
PAGELENGTHvalue can either be set to zero or >= 10. The default value is machine-dependent.SET PAGEWIDTH
Specifies the page width of the output log file.
Syntax
Description
The
PAGEWIDTHvalue defines the page width of the output file. The value should be greater than 20. The default value is 132.SHOW SETTINGS
Displays the current values of all set options.
Syntax
Description
Displays the current values for all set options, i.e. ECHO, EXECUTE, EXPLAIN, LINECOUNT, LINESPACE, LINEWIDTH, LOG, MB, MC, MESSAGE, OUTPUT, PAGELENGTH, PAGEWIDTH, TRANSACTION START, TRANSACTION ISOLATION LEVEL, TRANSACTION MODE (read only or read write).
Current server name, server version, and connection names are also displayed.
TRANSACTIONS
Displays the menu for administrating distributed transactions.
Syntax
Description
You can use the
TRANSACTIONScommand to monitor distributed transactions that are in a prepared or heuristically completed state. Note that all transactions are uniquely identified by the XID string. Because those strings are somewhat long, BSQL assigns a small sequence number to each line to be used as a shorthand. This shorthand is only valid until theList transactionsoption is used again. Note that since transactions are normally short-lived, the same transaction may be assigned different sequence numbers each time theList transactionsoption is used.Note: The TRANSACTIONS command should only be used in exceptional circumstances, such as when a transaction monitor has crashed or a network failure has occurred making it is impossible to establish contact with a transaction monitor.
The command will present the following menu:
Menu for handling distributed transactions 1. List transactions 2. Heuristic commit 3. Heuristic rollback 0. ExitThe
List transactionsoption displays a list of all distributed transactions that are either in a prepared ore heuristically completed state. For example:NUMBER STATUS XID ====== ========== =============================================================== 1 Prepared 34C6F6E675849446E616D6520 === 2 Prepared C6F6E675849446E616D6520 === 2 transactions foundTo heuristically commit or rollback a distributed transaction, you can choose option 2 or 3 in the menu. This will prompt for a transaction number which should correspond to a number in the listing. It is not possible to heuristically commit or rollback a distributed transaction without a prior listing.
When a distributed transaction is heuristically committed or rolled back it will remain in the list until it has been forgotten by the transaction monitor.
If the transaction with sequence number 1 (XID
34C6F6E675849446E616D6520) was heuristically committed a subsequent listing would look like this:NUMBER STATUS XID ====== ========== =============================================================== 1 Prepared C6F6E675849446E616D6520 === 2 Committed 34C6F6E675849446E616D6520 === 2 transactions foundMore information on distributed transactions in general, can be found in the Mimer SQL Programmer's Manual, Distributed Transactions.
WHENEVER
Determines which actions should be taken in the event of an error or warning.
Syntax
Description
If an error or warning should occur in a file being run in a script, there are different action options that may be chosen to determine what should happen:
Execution Flow
 Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se |
|
|
|
|
|
|
