Once you have unzipped DBMPLUS.ZIP to a directory (preferably one that is specified in the PATH statement of your CONFIG.SYS), DBMPLUS is ready to run. But you may wish to perform some tuning measurements with the file DBMPLUS.INI and to create views on some system tables.
This version of DBMPLUS allows you to specify the text-editor of your choice to be used for editing SQL statements, and the default name of a spool file.
The format of the DBMPLUS.INI specification is
editor = '<editor>'
spoolfile = '<spoolfile>'
where <editor> and <spoolfile> are to be replaced with the corresponding filenames, which may be qualified by drive and path name if required.
DBMPLUS is shipped with an DBMPLUS.INI file containing
editor = 'e,exe'
spoolfile = 'spool.txt'
In the current version of DBMPLUS, the file DBMPLUS.INI must reside in the directory, from which DBMPLUS is activated.
In order to use the DBMPLUS-commands 'Indexes' and 'Rels' you must run the commands 'usrindex.dbm' and 'usrrels.dbm' from within DBMPLUS once for each database and user (see descriptions of these commands below).
Starting DBMPLUS
----------------
On an OS/2 command line enter
DBMPLUS
You will be prompted for your userid, password and a database name. The password is not echoed on the screen. The database name is optional and refers to the database that DBMPLUS is initially connected to.
In order to shorten the initial DBMPLUS-dialog you may specify user-id, password and/or databasename with the DBMPLUS-command in one of the following ways:
DBMPLUS <userid>
DBMPLUS <userid>/<password>
DBMPLUS <userid> <database>
DBMPLUS <userid>/<password> <database>
Except for the last format, the missing parameters are prompted for. Remember that if the password is specified with the DBMPLUS command, it is naturally shown on the screen.
Within the initial dialog, you may abort the program by entering '.C' (without the surrounding apostrophes) as a response to any prompt.
DBMPLUS submits a STARTDBM command resulting in a message that STARTDBM was
successfull or that DB2/2 is already active.
When the program has been successfully initialized, it outputs
DBM>
and is waiting for your input, which may be a DBMPLUS command or an SQL statement.
DBMPLUS commands
----------------
DBMPLUS commands and its parameter may be entered in lower or upper case. They may be abbreviated, but must at least be specified with the characters that are represented in upper case letters in the following list.
List of DBMPLUS commands:
Help
----
Parameter: <none>
Purpose: Show help for DBMPLUS commands
EXit
----
Parameter: <none>
Purpose: Exit from DBMPLUS.
Prior to exiting from DBMPLUS, the databse connection is
Purpose: Activate/Deactivate the output of SQL statements
on the screen.
ON: Activate echoing (standard)
OFF: Deactivate echoing
If echo is OFF, the output of SQL statements to the
screen is suppressed. It is equivalent to
submitting SQL statements to dbm with the
-o parameter.
Run
---
Parameter: <none>
Purpose: (Re)Execute the actual SQL statement
List
----
Parameter: <none>
Purpose: Show the actual SQL statement
Edit
----
Parameter: <none> or <filename> or *
Purpose: Invoke texteditor in order to edit an SQL statement
<none>: The actual SQL statement is copied
to TEMP.DBM, before the texteditor
is invoked for editing this file;
on return from the texteditor
the contents of TEMP.BAT becomes the
actual SQL statement.
<filename>: The texteditor is invoked for the
specified file.
The actual SQL statement is not
affected.
*: Like <filename>, but using the
file which had been previously
used in a DBMPLUS command. The user
is prompted for confirmation.
Save
----
Parameter: <filename> or *
Purpose: Save actual SQL command in a file
<filename>: name of the file
*: Like <filename>, but using the
file which had been previously
used in a DBMPLUS command. The user
is prompted for confirmation.
LOad
----
Parameter: <filename> or *
Purpose: Load file into actual SQL buffer
<filename>: name of the file
*: Like <filename>, but using the
file which had been previously
used in a DBMPLUS command. The user
is prompted for confirmation.
Compile
-------
Parameter: <filename> or *
Purpose: Compile actual SQL command into a Rexx program
<filename>: name of the file
*: Like <filename>, but using the
file which had been previously
used in a DBMPLUS command. The user
is prompted for confirmation.
The resulting Rexx program has the format
/* Generated by DBMPLUS */
call dbm '<SQL statement line 1>',
'<SQL statement line 2>',
...
'<SQL statement line n>'
STore
-----
Parameter: <refno>
Purpose: Store SQL statement in internal buffer for future
use within the same DBMPLUS session..
<refno>: Any positive integer number.
REcall
------
Parameter: <refno>
Purpose: Load SQL statement from internal buffer, where
it had been store by a STore command.
<refno>: Positive integer number.
Describe
--------
Parameter: <tablename>
Purpose: Show the column definitions for the specified
table.
<tablename>: name of the table optionally
qualified by the creator name
Tables
------
Parameter: <none> or <creator>
Purpose: Show list of tables
<none>: tables created by the actual user
<creator>: tables created by <creator>
Indexes
-------
Parameter: <none> or <tablename>
Purpose: Show information on indexes ...
<none>: ... for all tables
<tablename>: ... for specific table
This command uses a special view on
sysibm.sysindexes; this view must be created by the
following DBM command sequence:
DBM> load usrindex.dbm
DBM> run
(It is assumed that the file usrindex.dbm,
which is shipped with DBMPLUS.ZIP, is located in
the current directory; otherwise the file must be
qualified accordingly)
Foreign
-------
Parameter: <none> or <tablename>
Purpose: Show information on foreign keys ...
<none>: ... for all tables
<tablename>: ... for specific table
This command uses a special view on
sysibm.sysrels; this view must be created by the
following DBM command sequence:
DBM> load usrrels.dbm
DBM> run
(It is assumed that the file usrrels.dbm,
which is shipped with DBMPLUS.ZIP, is located in
the current directory; otherwise the file must be
qualified accordingly)
Undo
----
Parameter: <none>
Purpose: Replace the current SQL statement buffer by its
previous contents
HOst
----
Parameter: <none> or <OS/2 command>
Purpose: Execute CMD.EXE or specified OS/2 command
<none> Execute CMD.EXE
<OS/2 command> Execute OS/2 command
LOGon
-----
Parameter: <user> or <user>/<password>
Purpose: Logon to another user
<user>: Logon to <user>; the password is
prompted for, in which case it will
not be echoed to the screen
<user>/<password>: No prompt for anything, but
password appears on screen
DBMPLUS prompts for the database, that the user is
initially connected to. The previous connection is
reset.
SHow
----
Parameter: <none>
Purpose: Show settings
Any other input statements than those listed above are assumed to be SQL statements and therefore directed to dbm (see below), with the following exception: a statement consisting of up to two characters, which is not equal to '?' and cannot be interpreted as an abbreviation of a DBMPLUS command are considered invalid.
If a DBMPLUS command requires a parameter which is not supplied by the user, this parameter is prompted for.
Any incomplete DBMPLUS command may be aborted by entering '.C' as an answer to a prompt.
SQL statements
--------------
Any input that is not recognized as a DBMPLUS command or considered invalid is assumed to be a line of an SQL statement.
The last lime of an SQL statement must be terminated by a ';'.
Continuation lines are prompted for by the line number.
If a prompt for a continuation line is answered by '.C' the input of the statement is aborted.
Once there is a terminating ';' the whole SQL statement is directed to dbm.
Example for a DBMPLUS session
-----------------------------
[C:\DBMLIB]dbmplus Start DBMPLUS session
User: joachim
Password:
Database: testdb
DBM> t Show list of tables in testdb
...
DBM> d persdat Show columns of table persdat
...
DBM> select persnr, name Execute an SQL statement
2 from persdat
3 where sex = 'M';
...
DBM> e Edit actual SQL statement using the
texteditor currently assigned to
DBMPLUS
*** Let line 3 be changed to 'where sex = 'F' ***
DBM> select persnr, name List SQL statement after edit
2 from persdat
3 where sex = 'F'
DBM> r Run SQL statement
...
DBM> st 1 Store SQL statement in buffer # 1
DBM> e c:\mydir\mystmt.dbm Edit file c:\mydir\mystmt.dbm
*** Suppose that c:\mydir\mystmt.dbm after ***
*** edit contains: ***
*** ***
*** select sex, count(*) ***
*** from persdat ***
*** group by sex ***
DBM> lo * Load previously used file
File c:\mydir\mystmt.dbm? (y/n): y and confirm
DBM> select sex, count(*) List loaded statement
2 from persdat
3 group by sex
DBM> r Run SQL statement
...
DBM> re 1 Recall SQL statement from buffer # 1
DBM> select persnr, name List SQL statement after recall
2 from persdat
3 where sex = 'F'
DBM> s c:\mydir\query1.dbm Save actual statement in file
