JDB SQL Reference

Chapter 4. Database Elements

This chapter describes JDB-specific elements, for example, the database and database column naming conventions and data types that are supported in a JDB database. The information about your JDB database and tables, such as its primary and foreign key specification, are stored in system tables which are automatically created.

Also discussed are:

Journal files which store information about database activities.
Configuration variables for setting up the JISQL (or isql) environment.

Naming Conventions

Databases

Each database is stored as an operating system file. Therefore, the name for the database must follow operating system naming conventions. If the database name contains characters that are not alphanumeric, the name specified in the CREATE DATABASE statement must be enclosed in single quotation marks.

You can create databases when you are connected to @system or when you are connected to another database. For more information on the CREATE DATABASE statement, refer to page 6-9.

Identifiers

Identifiers, such as table names and column names, must start with a letter, but they can contain letters, numbers, and underscores in any combination. They cannot contain dollar signs or periods. The maximum length of an identifier is 31 characters. If more than 31 characters are entered, the value is truncated.

Table names must be unique within the database. Each column name in a database table must be unique within that table.

Since column names can be duplicated in different tables, therefore, some statements might require that you uniquely identify a column name by including its database table name. For example, in the sample database, the last_name column appears in more than one table. To specify the last_name column in the actors table, use the following syntax:actors.last_name

You cannot use any of the JDB keywords as an identifier. For a list of the keywords, refer to page C-1.

JDB is case insensitive and stores the identifiers in lower case regardless of which case is used to enter them. If you enter address1, ADDRESS1, or Address1, JDB stores the column as address1.

Data Types

Table 4-1 lists the data types available in JDB:

**Table 4-1 Data types in JDB**
Data Type	Description
`INT`	Numeric value (stored as `LONG`)
`LONG`	Numeric value
`FLOAT`	Numeric value
`DOUBLE`	Numeric value
`DATETIME`	Date and time value–format `yyyy/mm/dd hh:mm:ss`
`CHAR`	Character string

For more information on data types, refer to page 6-15.

System Tables

When you create a new database, five system tables are automatically created that contain information (such as column names and primary/foreign key specifications) about the database itself. You can query for information stored in these tables just like any other database table; however, you can not edit these tables.

The systabs system table contains information about each database table.

**Table 4-2 Columns belonging to systabs system table**
Column Name	Description
tname	Table name
ttype	Table type
ncols	Number of columns
seek	Column for internal use

The syscols system table contains information about the columns in each database table.

**Table 4-3 Columns belonging to syscols system table**
Column Name	Description
tname	Table name
cname	Column name
ctype	Column type–numeric values correspond to the following data types: `101 INT` (stored as `LONG` in the current release) `102 LONG` `103 FLOAT` `104 DOUBLE` `105 DATETIME` `106 CHAR` `1125 INT, NOT NULL (stored as LONG, NOT NULL)` `1126 LONG, NOT NULL` `1127 FLOAT, NOT NULL` `1128 DOUBLE, NOT NULL` `1129 DATETIME, NOT NULL` `1130 CHAR, NOT NULL`
length	Column length

The syskeys system table specifies the primary and foreign keys.

**Table 4-4 Columns belonging to syskeys system table**
Column Name	Description
tname	Table name
keyno	Number assigned to the key column of the table–primary key is always 1
resolved	Column for internal use
hasreflist	Indicator specifying whether a reference list is included in the `REFERENCES` clause of the `CREATE TABLE` statement
rtname	Name of the database table specified in the `REFERENCES` clause of the `CREATE TABLE` statement
keytype	Indicator specifying primary key (P), foreign key (F), or unique entry (U)

The syskeycols system table contains information about each primary and foreign key column.

**Table 4-5 Columns belonging to syskeycols system table**
Column Name	Description
tname	Table name
keyno	Number assigned to the key column in `syskeys`
position	Order of the column in a composite key, if applicable
cname	Column name

The sysrkeycols system table contains information about the columns listed in the REFERENCES clause of a CREATE TABLE statement.

**Table 4-6 Columns belonging to sysrkeycols system table**
Column Name	Description
tname	Table name
keyno	Number assigned to the key column in syskeys
position	Order of the column in a composite key, if applicable
cname	Column name

Journal Files

JDB automatically creates journal files in your database directory. These files record your actions on the current database. The current journal file is named j1databaseName. When you start a JDB session, the current journal file is copied to a file named j0databaseName. If the file j0databaseName already exists, its contents are replaced. Journal files can be reinstated using the utility jdbroll.

Configuration

Specifying an Editor

The environment variables SMEDITOR or EDITOR determine which text editor is available in JISQL or in isql. When using JISQL, the specified editor can be used to make changes to the SQL text window. When using isql, entering the edit command displays the last statement in the specified text editor.

Error Messages

The error messages for JDB are stored in the Panther message file. If the program has trouble locating the error messages, check the setting of the variable SMVARS.

Connecting to a JDB Database

If you place your JDB database in the application directory or in one of the directories listed in SMPATH, you do not need to specify the path in the DBMS DECLARE CONNECTION statement.