|JDB SQL Reference|
This chapter describes the utilities available with JDB:
Access a command line interactive SQL utilityisql
- Specifies the name of the database or
@systemif a database isn't available.
isqlutility 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."
isqland 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
isqlend 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.
isql, at the command line, type:$SMBASE/jdb/bin/isql
databaseNameis the name of an existing database or
@systemif a database isn't available. The screen displays the following numeric prompt where you can enter any JDB statement:
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.isqlAt the prompt, logon toJDB using@system:logon @system
Once you are connected, create a new database by typing:create database
databaseNamemust 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
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
Displays last executed command.
Connects to another database file or
Redirects output to a file. If you specify output without a file name, it redirects output to the screen.
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.
Executes the named operating system command.
An automatic commit is generated when you exit the
isqlsession using either the
rollbackif you do not want to keep your database changes.
Restore a transaction logjdbroll
- Specifies the name of the database.
- Specifies the name of the journal file(s).
jdbrollutility 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
videobizwould 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 statementsmksql
- Specifies the name of the database.
mksqlutility 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 TABLEstatement, followed by a series of
INSERTstatements 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 filestbldata [-d
- 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,
TABas 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.
tbldatautility can be used two different ways:
- With the
-xargument to convert rows in a database table to a text file.
With these options, you must specify both the database and the database table.
-ioption, the database table must already exist. Also, the column values must be listed in the same order as the columns in the database table.