Master Contents

Documentation

SQL Reference

Chapter 1 Introduction
About this Manual
Chapter 2 Reading SQL Syntax Diagrams
Key to Syntax Diagrams
Reading Standard Compliance Tables
Chapter 3 Introduction to SQL Standards
X/Open-95 SQL
SQL-92
SQL/PSM
SQL-99
Chapter 4 Mimer SQL Database Objects
The Data Dictionary
Databanks
Idents
Schemas
Tables
Base Tables and Views
Primary Keys and Indexes
Stored Procedures
Synonyms
Shadows
Triggers
Sequences
Mimer SQL Character Sets
Collations
Data Integrity
Privileges
Chapter 5 SQL Syntax Elements
Characters
Identifiers
Data Types in SQL Statements
Literals
Chapter 6 Operators and Values
Operators
Value Specifications
Default Values
Assignments
Comparisons
Result Data Types
Chapter 7 Functions
About Set Functions
About Scalar Functions
Scalar Numeric Functions
Scalar String Functions
Scalar Interval Functions
Datetime Pseudo-Literals
String Pseudo-Literals
Chapter 8 Expressions and Predicates
Expressions
CASE Expression
CAST Specification
Predicates
Chapter 9 Search Conditions and Joins
Search Conditions
Joined Tables
INNER JOINs
OUTER JOINs
Chapter 10 The SELECT Specification
SELECT Specification Syntax
Chapter 11 Vendor-specific SQL
Escape Clauses
Processing Escape Clauses
Standard Compliance
Chapter 12 SQL Statement Descriptions
Usage Modes
ALLOCATE CURSOR
ALLOCATE DESCRIPTOR
ALTER DATABANK
ALTER DATABANK RESTORE
ALTER IDENT
ALTER SHADOW
ALTER TABLE
CALL
CASE
CLOSE
COMMENT
COMMIT
COMPOUND STATEMENT
CONNECT
CREATE BACKUP
CREATE DATABANK
CREATE DOMAIN
CREATE FUNCTION
CREATE IDENT
CREATE INDEX
CREATE MODULE
CREATE PROCEDURE
CREATE SCHEMA
CREATE SEQUENCE
CREATE SHADOW
CREATE STATEMENT
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DEALLOCATE DESCRIPTOR
DEALLOCATE PREPARE
DECLARE CONDITION
DECLARE CURSOR
DECLARE HANDLER
DECLARE SECTION
DECLARE VARIABLE
DELETE
DELETE CURRENT
DESCRIBE
DISCONNECT
DROP
ENTER
EXECUTE
EXECUTE IMMEDIATE
EXECUTE STATEMENT
FETCH
GET DESCRIPTOR
GET DIAGNOSTICS
GRANT ACCESS PRIVILEGE
GRANT OBJECT PRIVILEGE
GRANT SYSTEM PRIVILEGE
IF
INSERT
ITERATE
LEAVE
LEAVE (PROGRAM ident)
LOOP
OPEN
PREPARE
REPEAT
RESIGNAL
RETURN
REVOKE ACCESS PRIVILEGE
REVOKE OBJECT PRIVILEGE
REVOKE SYSTEM PRIVILEGE
ROLLBACK
SELECT
SELECT INTO
SET
SET CONNECTION
SET DATABANK
SET DATABASE
SET DESCRIPTOR
SET SESSION
SET SHADOW
SET TRANSACTION
SIGNAL
START
UPDATE
UPDATE CURRENT
UPDATE STATISTICS
WHENEVER
WHILE
Chapter 13 Data Dictionary Views
INFORMATION_SCHEMA
INFO_SCHEM
FIPS_DOCUMENTATION
Appendix A Reserved Words
Appendix B Character Sets
Character Data
National Character Data - Unicode
Appendix C Limits
Appendix D Deprecated Features
Host Variables
Operators
Statements
Communication Areas
Program Idents
Datetime Scalar Functions
Data Dictionary Views
Host Variable Types
Appendix E Return Status and Conditions
SQLSTATE Return Codes

Programmer's

Chapter 1 Introduction
About this Manual
Database APIs
Chapter 2 Mimer SQL and the ODBC API
The Mimer ODBC Driver
Required Files
Unicode and ANSI Interfaces
Examples in this Chapter
Operating Systems
Declarations
Initializing the ODBC Environment
Error Handling
Transaction Processing
Executing a Command
Repeating - Prepared Execution
Result-set Processing
Updating Data
Chapter 3 Mimer SQL and the JDBC API
The Mimer JDBC Driver
Required Files
Examples in this Chapter
Connecting with the Mimer JDBC Driver
Error Handling
JDBC 2 and Batch Update Operations
Transaction Processing
Executing a Command
Enhancing Performance
Result Set Processing
Updating Data
Chapter 4 Embedded SQL
General Principles for Embedding SQL Statements
Processing ESQL
Essential Program Structure
Linking Applications
Chapter 5 Idents and Privileges
Mimer SQL Idents
Database Privileges
Chapter 6 Connecting to a Database
The CONNECT Statement
Chapter 7 Communicating with the Application Program
Using Host Variables
The SQLSTATE Variable
The Diagnostics Area
The SQL Descriptor Area
Chapter 8 Accessing Data
Retrieving Data Using Cursors
Retrieving Single Rows
Retrieving Data from Multiple Tables
Entering Data into Tables
Chapter 9 Transaction Handling and Database Security
Transaction Principles
Transaction Control Statements
Transactions and Logging
Protecting Against Data Loss
Chapter 10 Distributed Transactions
How are distributed transactions handled?
Chapter 11 Dynamic SQL
Principles of Dynamic SQL
General Summary of Dynamic SQL Processing
SQL Descriptor Area
Preparing Statements
Extended Dynamic Cursors
Describing Prepared Statements
Handling Prepared Statements
Example Framework for Dynamic SQL Programs
Chapter 12 Mimer SQL Stored Procedures
About Routines
Syntactic Components of a Routine Definition
Modules
SQL Constructs in Routines
Manipulating Data
Result Set Procedures
Managing Exception Conditions
Access Rights
Using DROP and REVOKE
The Mimer SQL PSM Debugger
Chapter 13 Triggers
Creating a Trigger
Trigger Time
Trigger Event
Trigger Action
Comments on Triggers
Using DROP and REVOKE
Chapter 14 Handling Errors and Exceptions
Syntax Errors
Semantic Errors
Run-time Errors
Appendix A Host Language Dependent Aspects
ESQL in C/C++ Programs
ESQL in COBOL Programs
ESQL in FORTRAN Programs
Appendix B Return Codes
SQLSTATE Return Codes
Internal Mimer SQL Return Codes
Appendix C Deprecated Features
INCLUDE SQLCA
SQLCODE
SQLDA
Parameter Marker Representation
VARCHAR(size)
SET TRANSACTION
DBERM4

User's

Chapter 1 Introduction
About this Manual
Database APIs
Chapter 2 Introducing the Example Environment
Installing the Example Environment
The Example Environment Structure
Accessing the Environment
Chapter 3 Basic Concepts
The Mimer SQL Relational Database
Data Integrity
Access Rights and Privileges
Mimer SQL Statements
Chapter 4 Managing Database Connections
Connecting to a Database
PROGRAM Idents - ENTER and LEAVE
Chapter 5 Retrieving Data
Simple Retrieval
Result Order
Table Names
Setting Column Labels
Eliminating Duplicate Values
Selecting Specific Rows
Retrieving Computed Values
Using Set Functions
Grouped Set Functions - the GROUP BY Clause
Selecting Groups - the HAVING Clause
Ordering the Result Table
Using Scalar Functions
Using the CASE Expression
Using the CAST Specification
Datetime Arithmetic and Functions
Retrieving Data from More than One Table
Handling NULL Values
Conceptual Description of the Selection Process
Chapter 6 Collations
About Collations
Using Collations
Using Collations - Examples
Chapter 7 Working with Data
Inserting Data
Updating Tables
Deleting Rows from Tables
Calling Procedures
Updatable Views
Chapter 8 Managing Transactions
Transaction Principles
Logging Transactions
Handling Transactions
Chapter 9 Creating a Database
Creating Idents and Schemas
Creating Databanks
Creating Tables
Creating Sequences
Creating Domains
Creating Modules, Functions, Procedures and Triggers
Creating Views
Creating Secondary Indexes
Creating Synonyms
Commenting Objects
Altering Databanks, Tables and Idents
Dropping Objects from the Database
Chapter 10 Defining Privileges
Ident Structure
Granting Privileges
Revoking Privileges
Chapter 11 Mimer BSQL
Running BSQL
BSQL Commands
Variables in BSQL
Errors in BSQL
Error Messages
LOBs in BSQL
Appendix A The Example Environment
The MIMER_STORE Schema
Procedures
Views
Triggers
Idents
The MIMER_STORE_MUSIC Schema
The MIMER_STORE_BOOK Schema
The MIMER_STORE Schema Revisited
The MIMER_STORE_WEB Schema
Synonyms
Appendix B Deprecated Features
BSQL Commands

System Management

Chapter 1 Introduction
About this Manual
System Management Responsibilities
Chapter 2 The Database Environment
The Data Dictionary
Idents
Schemas
Databanks
Databank Options
Locating Databank Files
Organizing Databank Files
Altering Databank Locations
Accessing Databank Files
Databank File Deletion
Transaction Control
Database Security
Data Integrity
Chapter 3 Creating a Mimer SQL Database
Registering the Database
The Local Database
Accessing a Database Remotely
Mimer SQL License Key
Generating the Mimer SQL System Databanks
Managing Database Connections
Running Mimer BSQL and UTIL
Chapter 4 Managing a Database Server
Database Server Memory Areas
Threads
Database Server System Requirements
Controlling the Database Server - MIMCONTROL
System Information - MIMINFO
Database Server Log
Other Log Files
Several Installations on One Machine
Chapter 5 Backing-up and Restoring Data
Database Consistency
LOGDB and TRANSDB Importance
Updates Recorded in LOGDB
TRANSDB Considerations
SQLDB Considerations
Databank Backups
System vs. Online Backups
SQL Statements for Backing-up Databanks
Backing-up and Restoring Databanks
Restoring a Databank
Restoring SYSDB
Re-creating TRANSDB, LOGDB and SQLDB
Chapter 6 Databank Check Functionality
DBC Syntax
Functions
Authorization
Result File Contents
Chapter 7 DBOPEN Functionality
DBOPEN Syntax
Functions
Authorization
DBOPEN Output Example
Chapter 8 Loading and Unloading Data
Loading Data
Unloading Data
Data Description Headers and Files
File Format Specifications
MIMLOAD
Using STDIN/STDOUT/STDERR
Chapter 9 Mimer SQL Shadowing
About Databank Shadowing
Levels of Data Protection
Creating and Managing Shadows
SQL Shadowing Commands - an Example Session
Shadowing Utilities
Shadowing System Databanks
Data Protection Strategy
Configuring Your System
Performance Aspects of Shadowing
Troubleshooting
Chapter 10 READLOG
Functions
Authorization
Using the READLOG Functionality
Output Format
Chapter 11 Database Statistics
Statistical Information
Authorization
The SQL Statistics Statements
When to Use the SQL Statistics Statements
Appendix A Executing in Single- user Mode
File Protection in Single- and Multi-user Mode
Specifying Single-user Mode Access
Accessing in Single-user Mode
The SINGLEDEFS Parameter File
Appendix B The SQLHOSTS File on VMS and UNIX
The SQLHOSTS File
Appendix C The MULTIDEFS File on VMS and UNIX
The MULTIDEFS Parameter File
MULTIDEFS Parameters
Appendix D UNIX Specifics
Using Raw Device Partitions
Creating a Raw Disk Partition
Arranging to Put a Databank on a Raw Device
Arranging to Take a Databank off a Raw Device
Environment Variables
UNIX Commands
Appendix E Data Dictionary Tables
Summary of Data Dictionary Tables
Appendix F Deprecated Features
Export/Import
Load/Unload

Working

Chapter 1 Introducing Mimer SQL Mobile
About Mimer SQL Mobile
Database APIs
About this Manual
Related Mimer SQL Publications
Chapter 2 Developing Applications with Mimer SQL Mobile
Mimer SQL Mobile and ESQL
Mimer SQL Mobile and ODBC
Mimer SQL Mobile and JDBC