DBMPLUS Version 0.7 =================== USER MANUAL =========== Installation ------------ 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 = '' spoolfile = '' where and 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 DBMPLUS / DBMPLUS DBMPLUS / 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: Purpose: Show help for DBMPLUS commands EXit ---- Parameter: Purpose: Exit from DBMPLUS. Prior to exiting from DBMPLUS, the databse connection is reset, but the database is not stopped. SPool ----- Parameter: or ON or OFF Purpose: Specify spoolfile and/or activate/deactivate spooling of SQL commands. : Specify spoolfile and activate spooling. ON: Activate spooling. If no spoolfile was specified, its name is prompted for. OFF: Deactivate spooling The spoolfile specification is kept for subsquent activation. Spooling is deactivated by default. If spooling is ON, the output of SQL statements is written to the spoolfile. It is equivalent to submitting SQL statement to dbm with the -r(spoolfile) parameter. ECho ---- Parameter: ON or OFF 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: Purpose: (Re)Execute the actual SQL statement List ---- Parameter: Purpose: Show the actual SQL statement Edit ---- Parameter: or or * Purpose: Invoke texteditor in order to edit an SQL statement : 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. : The texteditor is invoked for the specified file. The actual SQL statement is not affected. *: Like , but using the file which had been previously used in a DBMPLUS command. The user is prompted for confirmation. Save ---- Parameter: or * Purpose: Save actual SQL command in a file : name of the file *: Like , but using the file which had been previously used in a DBMPLUS command. The user is prompted for confirmation. LOad ---- Parameter: or * Purpose: Load file into actual SQL buffer : name of the file *: Like , but using the file which had been previously used in a DBMPLUS command. The user is prompted for confirmation. Compile ------- Parameter: or * Purpose: Compile actual SQL command into a Rexx program : name of the file *: Like , 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 '', '', ... '' STore ----- Parameter: Purpose: Store SQL statement in internal buffer for future use within the same DBMPLUS session.. : Any positive integer number. REcall ------ Parameter: Purpose: Load SQL statement from internal buffer, where it had been store by a STore command. : Positive integer number. Describe -------- Parameter: Purpose: Show the column definitions for the specified table. : name of the table optionally qualified by the creator name Tables ------ Parameter: or Purpose: Show list of tables : tables created by the actual user : tables created by Indexes ------- Parameter: or Purpose: Show information on indexes ... : ... for all tables : ... 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: or Purpose: Show information on foreign keys ... : ... for all tables : ... 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: Purpose: Replace the current SQL statement buffer by its previous contents HOst ---- Parameter: or Purpose: Execute CMD.EXE or specified OS/2 command Execute CMD.EXE Execute OS/2 command LOGon ----- Parameter: or / Purpose: Logon to another user : Logon to ; the password is prompted for, in which case it will not be echoed to the screen /: 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: 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 DBM> log peter/avocado Logon to user peter Database: testpt DBM> ho Execute a command processor [C:\DBMLIB]cmd.exe Operating System 2 Command interpreter version 3 [C:\DBMLIB]dir ... [C:\DBMLIB]exit Close command processor and return to DBMPLUS DBM> ho time Execute one command [C:\DBMLIB]cmd.exe /C time System time is: 11.52.42,22am Enter new time: DBM> ex Exit from dbmplus ... >