Mimer BSQL

This chapter discusses Mimer BSQL, a command line oriented tool for executing SQL statements both in scripts and interactively.

Other SQL Tools

DbVisualizer, by DbVis Software (https://www.dbvis.com), is included in the Mimer SQL distribution. (Not for the OpenVMS platform.)

Running BSQL

BSQL can be run by a script or interactively. Interactive operation can be used to execute statements entered directly or read from sequential files.

About Complex SQL Statements – @

Use the @ character to delimit a complex SQL statement where the normal end-of-statement character ‘;’ appears before the end of the statement (e.g. when creating functions, procedures and triggers.)

Example

@

create function capitalize(str nvarchar(1000)) returns nvarchar(1000)

begin atomic

  declare outstr nvarchar(1000);

  declare strlen integer;

  declare n integer default 2;

  set strlen = length(str);

  set outstr = upper(substring(str from 1 for 1));

  while n <= strlen do

    set outstr = outstr || case when substring(str from n - 1 for 1) = ' ' then

                                   upper(substring(str from n for 1))

                               else

                                   lower(substring(str from n for 1))

                          end;

    set n = n + 1;

  end while;

  return outstr;

end

@

Running BSQL From a Script

Create a script file with the following contents:

the command to start BSQL

the user name

the password

the SQL statements and BSQL commands

the EXIT command (or end of file).

Unicode Pipe Support in Console Programs on Windows

All Mimer SQL console programs such as BSQL, can pipe Unicode files. The files can be any of the Unicode formats supported by Mimer SQL such as UTF16 big and little endian, and UTF8.

When output is piped to a file, the input decides the type of the output file. If the input file is ASCII, the output will also be ASCII. If the input file is UTF16, the output will also be UTF16. If the input is from the keyboard, the output will be an UTF16 file on Windows. For example:

BSQL < UNIFILEIN.TXT > UNIFILEOUT.TXT

Security and Script Jobs

For unattended operation, a script file must either include the Mimer SQL ident user name and password in explicit form or connect using an OS_USER login.

For security reasons, make sure that your script files are well protected and/or remove your password from the file after execution.

Alternatively, SQL statements and BSQL commands may be written in a sequential file without user name and password, and executed with the READ command from an interactive BSQL session.

Running BSQL

How you start BSQL depends on your operating system.

BSQL Command-line Arguments

The BSQL command line argument syntax is:

bsql [-m|-s] [-u user] [-p pass] [-q SQL [-c]] [database]

 

bsql [--multi|--single] [--user=user] [--password=pass]

    [--query=SQL [--continue]] [database]

 

bsql [-v|--version] | [-?|--help]

Options

Windows & Unix-style

VMS-style

Function

-c

--continue

/CONTINUE

The switch can be used together with the --query switch to indicate that the BSQL program is not terminated after the execution of the query.

-m

--multi

/MULTI

Connects to the database in multi-user mode.

-p password

--password=password

/PASSWORD=password

Password for ident. If the switch is omitted the user is prompted for a password, unless OS_USER is specified with the username switch, as described above.

VMS:Note that in an Open VMS environment it might be necessary to enclose the password in quotation marks as the value otherwise is translated to upper case.

-q query

--query=query

/QUERY=query

query can be any BSQL command or SQL statement. If a query is supplied, BSQL will terminate immediately after the query has been processed.

-s

--single

/SINGLE

Connects to the database in single-user mode.

-u username

--username=username

/USERNAME=username

Ident name to be used in connect. If the switch is not given the user is prompted for a username.

To connect using OS_USER, give -u "", --username="", or /USERNAME="".

-v

--version

/VERSION

Display version information.

-?

--help

/HELP

Show help text.

database

database

Specifies the name of the database to access. If a database name is not specified, the default database will be accessed, see System Management Manual, The Default Database.

If a database name is not specified, the default database will be accessed.

If neither --single nor --multi is specified for the optional mode flag, the way the database is accessed will be determined by the setting of the MIMER_MODE variable, see System Management Manual, Specifying Single-user Mode Access.

If this is not set, it will be accessed in multi-user mode.

If multiple instances of a qualifier is given, the last one is used. For example

bsql --single --multi --username=t1 --username=t2

 

is valid and means connect user t2 in multi-user mode.

Linux examples

Start BSQL and connect user cosmo with password Kramer:

bsql --username=cosmo --password=Kramer

 

Start BSQL, connect using OS_USER, execute a query, and then leave:

bsql --user="" --query="select * from \"SomeTable\" where user = 'COSMO'"

VMS examples

Start BSQL and connect user cosmo with password Kramer:

bsql /USERNAME="cosmo" /PASSWORD="Kramer"

 

Start BSQL, connect using OS_USER, execute a query, and then leave:

bsql /USERNAME="" /QUERY="select * from ""SomeTable"" where user = 'COSMO'"

 

Note:You can also use the Unix-style syntax in OpenVMS.

Windows examples

Start BSQL and connect user cosmo with password Kramer:

bsql --username=cosmo --password=Kramer

 

Start BSQL, connect using OS_USER, execute a query, and then leave:

bsql --user="" --query="select * from \"SomeTable\" where user = 'COSMO'"

To start Mimer BSQL from the Windows Start button:

Click Start, navigate to your Mimer SQL program group and select Batch SQL which is found in the Utilities sub-group.

Logging IN

Starting BSQL displays the following:

$ bsql

Mimer SQL Command Line Utility, version 11.0.7A

Copyright (C) Mimer Information Technology AB. All rights reserved.

 

Username:

 

 

After you have entered a user name and a correct password, the BSQL prompt is displayed:

SQL>

 

You can now enter BSQL specific commands and general SQL statements.

BSQL Command Line Editing – Linux

Command line editing is available in the BSQL program, which uses a line-oriented interface.

The following functions are available:

 

Use:

To:

ctrl-a

Move to beginning of command

ctrl-b

Move backwards in command

ctrl-d

Delete current character

ctrl-e

Move to end of command

ctrl-f

Move forwards in command

ctrl-h

Delete previous character

ctrl-k

Delete after current position in command

ctrl-n

Next command

ctrl-o

Execute retrieved command and get next from history list

ctrl-p

Previous command

ctrl-r

Retrieve command by search condition

ctrl-t

Change place for the previous two characters

ctrl-u

Delete command

ctrl-w

Delete before current position in command

ctrl-<space>

Set mark in command (or ‘esc <space>’)

ctrl-x ctrl-x

Go to mark set by ‘ctrl <space>

ctrl-x ctrl-h

Show the history list

ctrl-x ctrl-r

Retrieve command by history list number

esc h

Delete previous word

esc d

Delete next word

esc b

Move to previous word

esc f

Move to next word

You can use the arrow keys for command retrieval and for positioning the cursor within a line, i.e. the same function as for ctrl-b, ctrl-f, ctrl-n and ctrl-p.

To change the number of commands that can be held in the history list, the environment variable MIMER_HISTLINES can be used (the default is 23).

Note:The operating system may have control sequences set that, if they overlap, override those described above. E.g. the settings can be listed using the Linux stty -a command.

BSQL Commands

Command

Function

CLOSE

Closes active log files, see CLOSE.

DESCRIBE

Describes a specified object, see DESCRIBE.

EXIT

Leaves BSQL, see EXIT.

GET DIAGNOSTICS

Presents status and diagnostics information, see GET DIAGNOSTICS

LIST

Lists information on a specified object, see LIST.

LOG

Logs input, output or both on a sequential file, see LOG.

READ INPUT

Reads commands from a sequential file, see READ INPUT.

READLOG

Obtains information about logged operations, see READLOG.

SET ECHO

Specifies whether lines are echoed to the BSQL window during READ INPUT, see SET ECHO.

SET EXECUTE

Activate or deactivate the execution of queries, see SET EXECUTE.

SET EXPLAIN

Activate or deactivate the explain facility, see SET EXPLAIN.

SET LINECOUNT

Sets the BSQL page size, see SET LINECOUNT.

SET LINESPACE

Sets the number of blank lines between each output record, see SET LINESPACE.

SET LINEWIDTH

Sets the BSQL page width, see SET LINEWIDTH.

SET LOG

Stops or resumes logging input, output or both, see SET LOG.

SET MAX_BINARY_LENGTH

Specifies the maximum display length for binary columns, see SET MAX_BINARY_LENGTH.

SET MAX_CHARACTER_LENGTH

Specifies the maximum display length for character columns, see SET MAX_CHARACTER_LENGTH.

SET MESSAGE

Specifies whether messages are displayed, see SET MESSAGE.

SET OUTPUT

Specifies whether output should be written, see SET OUTPUT.

SET PAGELENGTH

Defines the page length of output file, see SET PAGELENGTH.

SET PAGEWIDTH

Defines the page width of output file, see SET PAGEWIDTH.

SET SILENCE

Specifies whether or not messages or column headers should be displayed, see SET SILENCE.

SET STATISTICS

Specifies whether or not statement statistics should be displayed, see SET STATISTICS.

SHOW SETTINGS

Displays current values of all set options, SHOW SETTINGS.

TRANSACTIONS

Displays the menu for administration of distributed transactions, see TRANSACTIONS.

WHENEVER

Sets action to be taken in response to an error or warning, see WHENEVER.

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

Closes log files.

Syntax

close00052.png

 

Description

The command closes the specified log file. If no log type is specified, all active log files are closed.

DESCRIBE

Describes a specified object.

Syntax

describe00053.png

 

Where object-type is:

object_type.png

 

Description

The DESCRIBE command 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. Exit

 

Choosing an item presents a submenu for choosing between different DESCRIBE functions – 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 the DESCRIBE session.

Specifying an object type and name in the command executes the first menu choice for that object. 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 DATABANK

Result

BRIEF

Lists the following information on the specified databank:

creator
file space used
allocated size
file name
databank option
minsize
goalsize
maxsize
removable option
backup information
tables
sequences.

BY TABLE PRIVILEGE

Lists the following information on the specified databank:

idents with table privilege.

FULL

Lists the following information on the specified databank:

creator
file space used
allocated size
file name
databank option
minsize
goalsize
maxsize
removable option
backup information
tables
sequences
idents with table privilege
comment
creation date.

DESCRIBE DOMAIN Options

DESCRIBE DOMAIN

Result

BRIEF

Lists the following information on the specified domain:

data type
default value
check constraints.

BY REFERENCES

Lists the following information on the specified domain:

referenced objects
referencing objects.

BY ACCESS

Lists the following information on the specified domain:

idents with usage privilege.

FULL

Lists the following information on the specified domain:

data type
default value
check constraints
referenced objects
referencing objects
idents with usage privilege
comment
creation date.

DESCRIBE IDENT Options

DESCRIBE IDENT

Result

BRIEF

Lists the following information on the specified ident:

creator
ident type
YES/NO if the ident has a schema with the same name or not
YES/NO if the ident has a password or not
A list of OS_USER logins defined for the ident
privileges held by ident.

BY ACCESS

Lists the following information on the specified ident:

accessible objects.

BY OWNERSHIP

Lists the following information on the specified ident:

created objects.

FULL

Lists the following information on the specified ident:

creator
ident type
YES/NO if the ident has a schema with the same name or not
YES/NO if the ident has a password or not
A list of OS_USER logins defined for the ident
accessible objects
created objects
comment
creation date.

DESCRIBE INDEX Options

DESCRIBE INDEX

Result

BRIEF

Lists the following information on the specified index:

table name and columns on which the index is defined
sort order
uniqueness
index algorithm
comment
creation date.

DESCRIBE SYNONYM Options

DESCRIBE SYNONYM

Result

BRIEF

Lists the following information on the specified synonym:

schema and name of referenced table/view
comment
creation date

DESCRIBE TABLE Options

DESCRIBE TABLE

Result

VERY BRIEF

Lists the following information on the specified table or view:

column names and types.

BRIEF

Lists the following information on the specified table or view:

column names and types
default values
constraints
referenced domains
indexes
triggers.

BY ACCESS

Lists the following information on the specified table or view:

idents with access.

BY REFERENCES

Lists the following information on the specified table or view:

referencing objects
referenced objects.

FULL

Lists the following information on the specified table or view:

column names and types
default values
constraints
referencing objects
referenced objects
indexes
triggers
idents with access
databank location
comment
creation date
date when statistics were generated.

DESCRIBE VIEW Options

DESCRIBE VIEW

Result

BRIEF

Lists the following information on the specified view:

view definition
updatability
check option
comment
creation date.

DESCRIBE MODULE Options

DESCRIBE MODULE

Result

BRIEF

List the following information on the specified module:

module definition
comment
creation date.

DESCRIBE PROCEDURE Options

DESCRIBE PROCEDURE

Result

BRIEF

Lists the following information on the specified procedure:

parameters
return types
access mode.

BY ACCESS

Lists the following information on the specified procedure:

idents with execute privilege.

BY REFERENCES

Lists the following information on the specified procedure:

referencing objects
referenced objects.

FULL

Lists the following information on the specified procedure:

parameters
return types
access mode
idents with execute privilege
referencing objects
referenced objects
source definition
module name
comment
creation date.

DESCRIBE FUNCTION Options

DESCRIBE FUNCTION

Result

BRIEF

Lists the following information on the specified function:

parameters
return type
access mode.

BY ACCESS

Lists the following information on the specified function:

idents with execute privilege.

BY REFERENCES

Lists the following information on the specified function:

referencing objects
referenced objects.

FULL

Lists the following information on the specified function:

parameters
return type
access mode
idents with execute privilege
referencing objects
referenced objects
source definition
module name
comment
creation date.

DESCRIBE TRIGGER Options

DESCRIBE TRIGGER

Result

BRIEF

Lists the following information on the specified trigger:

table name on which trigger is defined
trigger event
trigger type
event time.

BY REFERENCES

Lists the following information on the specified trigger:

referenced objects.

FULL

Lists the following information on the specified trigger:

table name on which trigger is defined
trigger event
trigger type
event time
referenced objects
source definition
comment
creation date.

DESCRIBE SEQUENCE Options

DESCRIBE SEQUENCE

Result

BRIEF

List the following information about the specified sequence:

initial value
increment value
maximum value
databank location.

BY ACCESS

List the following information on the specified sequence:

idents with usage privilege.

BY REFERENCES

List the following information on the specified sequence:

referencing objects.

FULL

List the following information about the specified sequence:

initial value
increment value
maximum value
databank location
referencing objects
idents with usage privilege
comment
creation date.

DESCRIBE SCHEMA Options

DESCRIBE SCHEMA

Result

BRIEF

List the following information about the specified schema:

schema owner
contained objects
comment
creation date.

DESCRIBE SHADOW

DESCRIBE SHADOW

Result

BRIEF

List the following information on the specified shadow:

shadow creator
databank name
file name
comment
creation date

DESCRIBE COLLATION

 

 

 

DESCRIBE COLLATION

Result

BY REFERENCES

List the following information about the specified collation:

columns using the specified collation
all objects using the specified collation

FULL

List the following information on the specified collation:

character set schema
character set name
pad attribute
version
delta definition
idents with usage privilege on the specified collation
comment
creation date
columns using the specified collation
all objects using the specified collation

DESCRIBE SPECIFIC

Describe specific can be used to describe overloaded routines by using their specific name.

 

 

 

DESCRIBE SPECIFIC

Result

BRIEF

List the following information about the specified routine:

schema name
routine name
statement type
return type (if function)
determinism
access mode

For each parameter:
parameter name
parameter mode
data type

BY ACCESS

List the following information about the specified routine:

idents with execute privilege on the routine

BY REFERENCE

Lists the following information about the specified routine

objects referenced by the routine
objects referencing the routine

FULL

Lists the following information about the specified statement

schema name
routine name
statement type
return type (if function)
determinism
access mode

For each parameter:
parameter name
parameter mode
data type

idents with execute privilege on the routine
objects referenced by the routine
objects referencing the routine
creation date
comment

DESCRIBE STATEMENT

 

 

 

DESCRIBE STATEMENT

Result

BRIEF

List the following information about the specified statement:

schema name
statement name
statement type
statement definition

BY ACCESS

List the following information about the specified statement:

idents with execute privilege on the statement

BY REFERENCE

Lists the following information about the specified statement:

objects referenced by the statement

FULL

Lists the following information about the specified statement:

schema name
statement name
statement type
statement definition
idents with execute privilege on the statement
objects referenced by the statement
creation date
comment

EXIT

Leave BSQL.

Syntax

exit.png

 

Description

Terminates the BSQL session.

GET DIAGNOSTICS

Get diagnostics for statement.

Syntax

get_diagnostics00054.png

 

Description

Presents all status and diagnostics information for the preceding statement.

LIST

Lists information on a specified object.

Syntax

list.png

 

Where object-type is one of the object types listed below.

Description

The LIST command 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. Exit

 

Choosing an item presents a submenu for choosing between different LIST functions - 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

LIST COLLATIONS

Result

ALL

Lists all collations in the database.

IN SCHEMA

Lists collations in the specified schema.

LIST DATABANKS Options

LIST DATABANK

Result

ALL

Lists all databanks in the database.

CREATED BY

Lists databanks created by a specified ident.

LIST DOMAINS Options

LIST DOMAINS

Result

ALL

Lists all domains in the database.

IN SCHEMA

Lists domains in the specified schema.

LIST FUNCTIONS Options

LIST FUNCTIONS

Result

ALL

Lists all the functions the current ident has execute privilege on.

IN SCHEMA

Lists functions in the specified schema.

LIST IDENTS Options

LIST IDENTS

Result

ALL

Lists all idents in the database.

CREATED BY

Lists idents created by a specified ident.

LIST INDEXES Options

LIST INDEXES

Result

ALL

Lists the secondary indexes in the database.

IN SCHEMA

Lists secondary indexes in the specified schema.

LIST MODULES Options

LIST MODULES

Result

ALL

Lists all the modules in the database that are visible to (i.e. created by) the current ident.

LIST OBJECTS Options

LIST OBJECTS

Result

ALL

Lists objects in the database.

CREATED BY

Lists objects created by a specified ident.

WITH TYPE

Lists objects of a specified type.

LIST PROCEDURES Options

LIST PROCEDURES

Result

ALL

Lists all the procedures the current ident has execute privilege on.

IN SCHEMA

Lists procedures in the specified schema.

LIST SCHEMATA Options

LIST SCHEMATA

Result

ALL

Lists schemata created by the current ident.

LIST SEQUENCES Options

LIST SEQUENCES

Result

ALL

Lists all the sequences the current ident has usage privilege on.

IN SCHEMA

Lists sequences in the specified schema.

LIST SHADOWS Options

LIST SHADOWS

Result

ALL

List shadows created on databanks created by the current ident or all shadows if the current ident has shadow privilege.

LIST STATEMENTS Options

LIST STATEMENTS

Result

ALL

List all the precompiled statements the current ident has usage privilege on.

IN SCHEMA

List all statements belonging to the defined schema.

LIST SYNONYMS Options

LIST SYNONYMS

Result

ALL

Lists synonyms in the database.

IN SCHEMA

Lists synonyms in the specified schema.

LIST TABLES Options

LIST TABLES

Result

ALL

Lists tables in the database.

IN SCHEMA

Lists tables in the specified schema.

LIST TRIGGERS Options

LIST TRIGGERS

Result

ALL

List triggers defined on tables accessible to current user.

IN SCHEMA

Lists triggers in the specified schema.

LIST VIEWS Options

LIST VIEWS

Result

ALL

Lists views in the database.

IN SCHEMA

Lists views in the specified schema.

LOG

Logs input, output or both to a specified sequential file.

Syntax

log.png

 

Description

All input, output or both will be logged in the specified sequential file.

If ON is specified a new file will always be created, otherwise the log data is appended to the file.

Logging is paused with the SET LOG OFF command and is resumed with the SET LOG ON command. Use CLOSE to stop logging permanently.

Using the AS option, you can set the file format to LATIN1, UTF8, UTF16, UTF16BE, UTF16LE, UTF32, UTF32BE or UTF32LE.

WITH BOM and WITHOUT BOM can be used to override platform specific default BOM behavior.

Example

SQL>log input,output on 'create_environment_log.dat';

READ INPUT

Reads commands from a sequential file.

Syntax

read.png

 

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 ALL is default mode.)

When READ COMMAND is 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 AS option, you can set the file format to LATIN1, UTF8, UTF16, UTF16BE, UTF16LE, UTF32, UTF32BE or UTF32LE.

Example

SQL>read input from 'create_environment.dat';

READLOG

Obtains information about logged operations.

Syntax

readlog.png

 

Description

READLOG is a Mimer SQL function which enables you to read the contents of LOGDB so 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 LOGDB on the basis of time interval, ident performing the operation, and specified databanks or tables.

This is particularly useful in production systems where LOGDB can contain a large number of entries.

Authorization

To list the log for selected tables or all tables in a databank, you must have SELECT access on the tables in question.

To list the log for the entire database, you must have BACKUP privilege.

Using the READLOG Functionality

You control the READLOG functionality 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. EXIT

 

List Definitions – Output Control

Log File

Choosing Log file allows 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 RDLOGL will 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 properties choice 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 endian

 

Default 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 restriction

 

A 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 restriction

 

A selected ident applies for all subsequent list operations in the current session until a new ident is specified.

Example:

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 restriction

 

A selected databank applies for all subsequent list operations in the current session until the databank is reset.

Example:

Select: 5

Databank: mimer_orders

List 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 READLOG must have SELECT access on the requested tables, otherwise the following message is displayed for the table in question:

** No select access on table

 

If a non-existent table is requested, the following message is displayed:

** No such table

 

Errors 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 entered

 

Time or ident restrictions selected with options 3 or 4 are applied.

Data is listed only for those tables to which the ident running READLOG has SELECT access.

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 READLOG must have BACKUP privilege.

If the privilege is not held by the current ident the following error message is displayed:

**   AUTHORIZATION FAILURE

Output Format

The output from READLOG is 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 tables or Tables 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 INPUT are echoed.

Syntax

set_echo.png

 

Description

When echo is set to ON, lines read during READ INPUT are echoed to the BSQL window or log file. When echo is set to OFF, these lines are not echoed. The default value is ON.

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

set_execute.png

 

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

set_explain.png

 

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 EXECUTE command can be used. (Note that SET EXECUTE OFF applies to DDL statements as well, e.g. CREATE INDEX will only verify correctness, no index will be created.)

The execution plan will show different operations and the sequence in which these operations are performed.

DbVisualizer Pro’s explain plan tool is the recommended choice when working with a Mimer SQL server. See https://www.dbvis.com/features/tour/explain-plan/.

The BSQL Explain output for Mimer SQL is XML based. The different node types are described in the following table:

Node

Description

cost

This is the cost of executing the current node and all underlying nested nodes. The unit of cost is a ~ row access.

hits

Number of rows that are passed on from this part of the tree to higher nodes.

visits

Number of rows that the database server needs to process to find the requested rows.

index

This is the access path used to access the rows in the table.

type

This is the type of the index. It can be primary key, secondary index or similar. Some constraints are maintained through the use of hidden indexes. In these cases the name corresponds to the constraint name.

order

Each table/index access is ordered according these numbers. The order can also be determined by viewing the explain tree.

scan

This shows whether the system can process the rows in the table efficiently or not. Sequential scan means the entire table/index must be scanned. Leadingkeys means there are conditions on one or several of the leading columns in the table/index. Trailingkeys means there are conditions on one or several trailing keys. In practise this usually means that all rows have to be processed unless the leading columns contain very few values. Unique means the row is uniquely identified and the can be processed quickly.

index lookup only

When index lookup only is used the base table does not need to be accessed as the index contains all the necessary information to process the query. If index lookup only is not used for each row in the index the corresponding row in the base table will be accessed.

How to read the XML output and how to understand the DbVisualizer explain is described in Mimer SQL Explain.

SET LINECOUNT

Sets the length of the BSQL window.

Syntax

set_lc.png

 

Description

The LINECOUNT value defines the length of the BSQL window.

If LINECOUNT has 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 LINECOUNT is zero, the output will continue until all data is displayed.

The value of LINECOUNT must either be zero or >= 10.

Default

If BSQL is run from a script job, LINECOUNT is 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

set_ls.png

 

Description

The LINESPACE value defines the number of blank lines to be written between each output record. This value is only used when printing the result of a SELECT statement.

The maximum value for LINESPACE is 9. The default value is 0.

SET LINEWIDTH

Specifies the width of the output.

Syntax

set_lw.png

 

Description

The LINEWIDTH value defines the maximum line width for output to the BSQL window or log file.

The value for LINEWIDTH cannot be set to a value less than 20 or larger than 255.

SET LOG

Stops or resumes logging input, output or both.

Syntax

set_log.png

 

Description

When SET LOG is set to OFF, logging of input, output or both in a sequential file is temporarily stopped.

Resume logging with the SET LOG ON command.

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

set_mb.png

 

Description

The MAX_BINARY_LENGTH value 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

set_mc.png

 

Description:

The MAX_CHARACTER_LENGTH value 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

set_msg.png

 

Description

Specifies whether or not result messages such as One row found etc. are written.

The default setting is ON.

SET OUTPUT

Specifies whether or not output should be displayed.

Syntax

set_output.png

 

Description

When OUTPUT is set to ON, the output from BSQL is written to the BSQL window. When it is set to OFF, the output does not appear.

The default value is ON.

SET PAGELENGTH

Specifies the page size of the output log file.

Syntax

set_pl.png

 

Description

The PAGELENGTH value 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 PAGELENGTH value 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

set_pw.png

 

Description

The PAGEWIDTH value defines the page width of the output file. The value should be greater than 20. The default value is 132.

SET SILENCE

Specifies whether or not column headers or information messages should be displayed.

Syntax

set_silence.png

 

Description

If SET SILENCE ON then BSQL will not display any column headers nor any messages when selecting or modifying data.

SET SILENCE OFF will revert to the default behavior.

SET STATISTICS

Specifies whether or not statement statistics should be displayed.

Syntax

set_statistics.png

 

Description

When STATISTICS is set to ON, additional information about INSERT, DELETE, UPDATE and SELECT statements will be shown. This includes number of table operations and transaction records.

A table operation is either a read or write to a table, index or temporary table that occurred during the statement. For instance, if a select statement read an index record and then reads additional columns (not present in the index) from the base table this will be counted as two table operations. Likewise when doing update statements, operations on base tables and operations on indexes will be counted as separate operations.

The number of transaction records for a statement includes the number of records insert, deleted or updated and any records that needs to be kept for checking constraints and transaction consistency.

The default value is OFF.

SHOW SETTINGS

Displays the current values of all set options.

Syntax

show_settings.png

 

Description

Displays the current values for all SET options, i.e. ECHO, EXECUTE, EXPLAIN, LINECOUNT, LINESPACE, LINEWIDTH, LOG, MAX_BINARY_LENGTH, MAX_CHARACTER_LENGTH, MESSAGE, OUTPUT, PAGELENGTH, PAGEWIDTH, SILENCE, 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 administration of distributed transactions.

Syntax

transactions.png

 

Description

You can use the TRANSACTIONS command 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 the List transactions option is used again. Note that since transactions are normally short-lived, the same transaction may be assigned different sequence numbers each time the List transactions option 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. Exit

 

The List transactions option 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 found

 

To 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 found

 

More 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

whenever00055.png

 

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

EXIT

Leaves BSQL if script mode. Returns to prompt if interactive mode. I.e. if interactive mode and file input mode, the remaining file input is ignored and a new prompt is received.

CONTINUE

Continues execution.

Variables in BSQL

Host variables are used in SQL statements to pass values between the database and an application program, see the Mimer SQL Programmer’s Manual.

Host variable syntax is also supported in BSQL to facilitate interactive design and testing of SQL statements intended for use in SQL application programs.

In BSQL, host variables serve as parameter markers, and the user is prompted for parameter values when the statement is executed.

You can use host variables used to:

assign values to columns in the database (UPDATE and INSERT statements)

to manipulate information taken from the database or contained in other variables (in expressions)

to provide values for comparison predicates.

In all these contexts, the data type and length of the host variable must be compatible with that of any database values within the same syntax unit.

Writing Host Variables in SQL

Host variables are written in SQL as:

:host-identifier

 

or

:host-identifier :indicator-identifier

 

or

:host-identifier INDICATOR :indicator-identifier

 

In the first construction, the host identifier is the name of the main host variable.

In the second and third constructions, the main variable host-identifier is associated with an indicator variable indicator-identifier, used to signal the assignment of a null value to the main variable.

See the Mimer SQL Programmer’s Manual for a description of the use of indicator variables.

Scope of Host Variables

The scope of host variables in BSQL is restricted to the individual usage instance in each statement.

Variables may not be used to pass values between separate statements, and the same variable name used more than once in a statement represents separate, independent variables.

Using Host Variables

When host variables are used in BSQL, BSQL prompts for the variable values, for example:

SQL>SELECT * FROM countries WHERE country = :COUNTRY;

COUNTRY: Spain

 

This corresponds to the statement:

SQL>SELECT * FROM countries WHERE country = 'Spain';

 

Note:The entered variable is not enclosed between single quotation marks, in contrast to the corresponding string value.
Variables enclosed in single quotation marks will be interpreted as literal strings.

Including Indicator Variables

If an indicator variable is included, you will be prompted for whether to use a null value.

If you answer the prompt with No, you will then be prompted for a value.

If you answer Yes, the null value will be used. For example:

SQL>UPDATE currencies SET exchange_rate = :RATE:IND

SQL&  WHERE code = 'BND';

Null ?n

RATE: 1.34

 

SQL>UPDATE currencies SET exchange_rate = :RATE:IND

SQL&  WHERE code = 'BND';

Null ?y

 

In the first example above, the exchange_rate value is updated to 1.34. In the second example, the exchange_rate value is set to null.

Note:The prompts appear in the order in which the variables are used in the statement.

BSQL and Multiple Connections

After logging in using BSQL, additional connections can be established using the CONNECT statement, which has the following form:

CONNECT TO 'database' [AS 'connection_name']

  USER 'username' USING 'password';

 

This statement establishes a new connection between the user and a database, see the Mimer SQL Reference Manual, CONNECTfor details.

The database may be given an explicit connection name for use in DISCONNECT and SET CONNECTION statements. If no explicit connection name is specified, the database name is used as the connection name.

Changing Connections

BSQL may make multiple connections to the same or different databases using the same or different idents, provided that each connection is identified by a unique connection name. In this situation only one connection is active and the other connections are inactive. A connection established by a successful CONNECT statement is automatically active.

A connection may be made active by the SET CONNECTION statement.

For example:

SET CONNECTION 'connection_name';

Disconnecting

The DISCONNECT statement breaks the connection between the user and a database. The connection to be broken is specified as the connection name or as one of the keywords ALL, CURRENT or DEFAULT.

DISCONNECT 'connection_name';

 

A connection does not have to be active in order to be disconnected. If an inactive connection is broken, BSQL still has uninterrupted access to the database through the current (active) connection, but the broken connection is no longer available for activation with SET CONNECTION.

If the active connection is broken, BSQL cannot access any database until a new CONNECT or SET CONNECTION statement is issued.

Note:The distinction between breaking a connection with DISCONNECT and making a connection inactive by issuing a CONNECT or SET CONNECTION for a different connection is, a broken connection has no saved resources and cannot be reactivated by SET CONNECTION.

The table below summarizes the effect on the connection con1 of CONNECT, DISCONNECT and SET CONNECTION statements depending on the state of the connection.

Statement

con1 non–existent

con1 current

con1 inactive

CONNECT TO 'DB1' AS 'CON1'

con1 current

error – connection already exists

error – connection already exists

DISCONNECT 'CON1'

error – connection doesn’t exist

con1 disconnected

con1 disconnected

SET CONNECTION 'CON1'

error – connection doesn’t exist

ignored

con1 made current

CONNECT TO 'DB2' AS 'CON2'

con1 made inactive

con1 unaffected

DISCONNECT 'CON2'

con1 unaffected

con1 unaffected

SET CONNECTION 'CON2'

con1 made inactive

con1 unaffected

Transaction Handling in Mimer BSQL

Normal Mimer SQL transaction handling behavior applies in Mimer BSQL. The default transaction start setting of implicit means that, by default, a transaction is started whenever one is needed.

For a detailed description of transaction handling behavior in Mimer SQL, refer to the Mimer SQL Programmer's Manual, Transaction Handling and Database Security.

A special feature of BSQL is that all implicitly started transactions are automatically committed at the end of each statement, so that by default no attention needs to be paid to transaction handling at all in BSQL.

The START and COMMIT (or ROLLBACK) statements may be used together to group a number of statements into a single transaction when this is required.

Any transactions explicitly started using START will not be automatically committed by BSQL, so COMMIT or ROLLBACK must be used.

LOBs in BSQL

Although BSQL is not designed to handle Large OBjects (LOBs), it does provide limited LOB support for testing purposes.

A LOB is a column defined as being of type Character Large OBject (CLOB), National Character Large OBject (NCLOB) or Binary Large OBject (BLOB).

Columns defined as CLOB will, in all essentials, be treated as being the same as VARCHAR with a maximum length of 15 000 characters.

Columns defined as NCLOB will, in all essentials, be treated as being the same as NCHAR VARYING with a maximum length of 5 000 characters.

Columns defined as BLOB will be treated as BINARY VARYING with a maximum length of 15 000.

LOBs larger than these limits cannot be entered as input, and will be truncated as output.

For more information on LOB data types, see the Mimer SQL Reference Manual, Data Types in SQL Statements.

Errors in BSQL

Error messages are shown when you attempt to execute an erroneous SQL statement. There are two types of errors: semantic errors and syntax errors.

Semantic Errors

Semantic errors arise when SQL statements are formulated with correct syntax, but do not reflect the user’s intentions.

For example, suppose that a user wishes to select the string constant Hotel: and the actual hotel name from the table HOTEL, but uses double quotation marks instead of single quotation marks around the string constant:

SELECT  "Hotel:",NAME

FROM    HOTEL;

 

Double quotation marks are used to delimit identifiers containing special characters, so that the statement is interpreted as a request to select two columns, called Hotel: and NAME, from the table. The first column does not exist.

This example will in fact lead to an execution error, and is easily detected. Other semantic mistakes can be more difficult to find, when the statement is executed but gives the ‘wrong’ answer.

An example is the incorrect use of null in a search condition:

SELECT  RESERVATION FROM BOOK_GUEST

WHERE   CHECKOUT = CAST(NULL as DATE);

 

This will always give an empty result set, since null is not equal to anything.

The correct formulation would read WHERE CHECKOUT IS NULL.

Always check that the result of an SQL query looks reasonable, in particular if the query is complicated.

Syntax Errors

Syntax errors are constructions which break the rules for formulating SQL statements.

For example:

spelling errors in keywords

SLEECT (for SELECT)

incorrect or missing delimiters

DELETEFROM (for DELETE FROM)

SELECT column1 column2 (for SELECT column1,column2)

incorrect clause ordering

UPDATE table WHERE condition SET values

(for UPDATE table SET values WHERE condition)

Syntactically incorrect statements are not accepted and an appropriate error message is displayed.

The error must be corrected before the statement can be executed.

For syntax errors, BSQL analyzes the statement and makes an intelligent guess as to where the error lies. This guess is based upon the most likely syntax or appearance of the statement in question. The system then points out the error and lists an error message based on this analysis. The appearance of this pointer on your screen is machine dependent. In the examples shown in this chapter, the pointer appears as ‘^’. The messages are self-explanatory.

The statement analysis is however not completely foolproof and misleading error messages may arise. If the message seems to be inaccurate, check the statement construction against the syntax diagram in the Mimer SQL Reference Manual.

Error Examples

Some examples of errors and resulting error messages are listed below.

Incorrect statement:

SELECT  AVG(country) FROM countries;

Error message:

SELECT  AVG(country) FROM countries;

           ^

Invalid operand type, expected type is NUMERIC or INTERVAL

Incorrect statement:

SELECT  country FROM countries

WHERE   currency_code ON ('USD','GBP','SEK');

Error message:

SELECT  country FROM countries

WHERE   currency_code ON ('USD','GBP','SEK');

                     ^

Syntax error, 'ON' assumed to mean 'IN'

Incorrect statement:

In the following example, the error analysis is misleading:

SELECT  country FROM countries

WJERE   currency_code = 'USD';

Error message:

SELECT  country FROM countries

WJERE   currency_code = 'USD';

       ^

Syntax error, END-OF-QUERY assumed missing

 

The misspelled word WJERE is not recognized as an attempt to write WHERE, so that the second line is not interpreted as a selection condition.

Error Messages

Error messages from BSQL are shown when you enter an illegal BSQL command or attempt to execute an erroneous SQL statement.

The error messages for erroneous SQL statements are the same as the return codes found in the Mimer SQL Programmer's Manual.

Error messages that can be received for illegal BSQL commands are:

Code

Message

-1

String exceeds 256 characters which is not allowed

-2

File could not be opened

-3

Too many files have been opened

-5

Conflict. One of COMMIT or ROLLBACK and EXIT or CONTINUE

-100

Undefined command <%>

-101

Ambiguous command <%>

-102

<%> command not valid in this context

-103

Missing semicolon

-104

Missing statement terminator (@)

-201

Syntax error

-202

Undefined keyword

-203

String expected

-204

Filenames must be enclosed in apostrophes

-205

Invalid numerical literal

-206

Unexpected end of command

-207

Too many parameters

-300

Failed to read dictionary

-400

Record too large for one page (<%> lines required) Increase value of LC/PL or set them to zero

-600

The number of host variables cannot exceed 20

-666

Space area exhausted

-700

Help databank not installed or inaccessible

-701

Help topic not found

-776

Maximum record length <%> exceeded

-777

Maximum header length exceeded

-800

Load/unload is not allowed within a transaction

-801

Pending transaction , Commit or Rollback

-802

Invalid transaction number, must be between 1 and <%>

-803

Server version and BSQL version must be the same when using READLOG

-804

The READLOG statement cannot be used within a transaction

-805

Invalid string literal, missing delimiter (')

-806

Error when reading from terminal

-807

<%> logging has not been activated with the LOG command

-900

No buffer saved

-999

Too long statement

-1001

Syntax error in file name

-1002

File not found

-1003

File protection violation

-1004

File locked

-1005

Maximum number of opened files exceeded

-1006

Disk space exhausted

-1007

** Installation dependent **

-1008

** Installation dependent **

-1009

Unspecified file open error

-1101

Disk space exhausted

-1200

Previous perform file is not finished

-1300

Only select statements can be used with PRINT

-1400

Invalid numerical argument

-1500

Illegal value for <%>

-1600

The routine is overloaded. Use describe specific instead.

-1700

Maximum number of result items exceeded, limited to 300.

-1800

Maximum number of input items exceeded, limited to 300.