JDB SQL Reference

Appendix A. JDB Utilities

This chapter describes the utilities available with JDB:


Access a command line interactive SQL utility

isql databaseName

Specifies the name of the database or @system if a database isn't available.


The isql utility is a command-line interactive editor for JDB that lets you to execute any database statement. It is provided as a command-driven alternative to the JISQL graphical environment described in Chapter 5, "Using JISQL."

While isql and JISQL are similar in many respects, they are not identical. JISQL has a series of macro commands that are not available in isql, so JISQL scripts containing these commands will not run under isql. Although the SQL commands in isql end with a ; as a termination character, all JISQL commands terminate with a ;. Use the interactive SQL capability described here only if you want to bypass the JISQL environment.

Starting ISQL

To start isql, at the command line, type:

$SMBASE/jdb/bin/isql databaseName

where databaseName is the name of an existing database or @system if a database isn't available. The screen displays the following numeric prompt where you can enter any JDB statement: 1>

Creating a New Database

If this is your first JDB session, or if you want to create a new database, first start isql. Generally, on UNIX systems, it is located in $SMBASE/jdb/bin.

At the prompt, logon to
JDB using
logon @system

Once you are connected, create a new database by typing:

create database databaseName;

databaseName must conform to the file naming conventions of the operating system.

To connect to the new database in order to create database tables and enter data, enter:

logon databaseName
Executing SQL Statements

You can execute any SQL statement available in JDB by ending each database statement with a ; as the command terminator. For example:

1> SELECT title_name, name, genre_code FROM titles;

A line without a trailing semi-colon is concatenated with the next line until a semicolon is reached. Therefore, one statement can span multiple lines.

Executing ISQL Statements

Table A-1 lists the commands available in isql. These commands allow you to edit a query, read in a query file, or execute an operating system command.

Note: These commands do not end with a semi-colon. Also, in order for these commands to be recognized, each must start on the first character of a command line.

Table A-1 Commands for isql

Command Syntax Description



Empties the input buffer. Command must start in the first column of a new line.



Specifies that the line is a comment.



Saves additions and edits you make to the database since the last commit or rollback or since connecting to the database.



Starts an editing shell for entering statements. The editing program is determined by the environment variable EDITOR or SMEDITOR.



Exits isql.



Displays last executed command.


logon databaseName
logon @system

Connects to another database file or @system.


output filename

Redirects output to a file. If you specify output without a file name, it redirects output to the screen.



Quits isql.


read filename

Reads and executes the SQL commands in a text file. To execute more than one command, each command must end with a semi-colon (;).



Undoes all additions and edits made to the database since the last commit or rollback or since connecting to the database.


system commandName

Executes the named operating system command.

Exiting ISQL

To exit isql, type:


An automatic commit is generated when you exit the isql session using either the quit or exit commands. Specify rollback if you do not want to keep your database changes.


Restore a transaction log

jdbroll databaseName journalName [journalName ...]

Specifies the name of the database.

Specifies the name of the journal file(s).


The jdbroll utility allows you to update the database based on your log files.

When you logon to a database for the first time, JDB creates a journal file named j1databaseName. For example, a database having the videobiz would have a journal file named j1videobiz. The next time you log on, the information in the current file (j1videobiz) is copied to the file j0databaseName. If the file already exists, it is overwritten.


Translate an existing JDB database into its CREATE TABLE and INSERT statements

mksql databaseName

Specifies the name of the database.


The mksql utility uses an existing JDB database to output a set of SQL statements from which the database could be rebuilt. For each table, it writes a CREATE TABLE statement, followed by a series of INSERT statements for the data in the table.


The following result set illustrates a portion of the output for the actors table in the videobiz database.


Read rows in a database table to/from text files

tbldata [-d delimiter] -x exportFile databaseName tableName
tbldata [-d delimiter] -i importFile databaseName tableName

-d delimiter
Specifies the column delimiter. The delimiter character might need to be enclosed in quotation marks. For example, to specify a space as the delimiter:
tbldata -d " " -x exportFile databaseName tableName

If no delimiter is specified, tbldata uses TAB as the delimiter.

Logs onto the specified database and writes each row of the specified table to the specified text file.

Logs onto the specified database and inserts each row of the specified text file into the specified table.

Name of the text file where the data will be written.

Name of the text file containing the data to be inserted into JDB.

Name of the JDB database.

Name of the database table.


The tbldata utility can be used two different ways:

With these options, you must specify both the database and the database table.

When using -i option, the database table must already exist. Also, the column values must be listed in the same order as the columns in the database table.