Interface tools for DB2
Bob
Hello and Welcome to Moviephone!
I have been working in DB2 8.x (on UNIX) for a couple of months now
using Windows as a client machine. Coming from an Oracle/SQLPlus
world, I have certain expectations of a tool like "Command Editor" -
or at least I did.
So I have a couple of questions for you db gurus out there that are
familiar with DB2 8.x. I have searched the web and have yet to find
any information.
1. Can I tell DB2 Command Editor that my script directory = "c:
\whatever\myscripts" so I don't have to navigate to it each time I
reopen the tool?
2. Can I save and open common scripts without dealing with a dialog
(similar to SQLPlus for those who are familiar with it)? This would
be - run a command and find I am happy with it, type "save myscript".
Then, the next day, type @myscript and have the query run again.
3. Can I define input parameters in saved scripts so that I can run
them and have it prompt me with something to the effect of "input for
Schema:" (or prompt me at all)?
If I can do all of these things, I will sing DB2's praises. I have no
problem with the database itself. If "Command Editor" cannot perform
these things BUT I can go in through a DOS cmd window, I would be just
as happy!
Also, how is DWB for 8.x (I cannot use DWB for 9.x - foolish
bureaucracy!)
Lennart
Many of the developers where I work, use squirrel (
http://www.squirrelsql.org/ ) as an sql frontend for db2, and they seem
to be happy with it. I havent used it much my self, so I can't really
tell whether it meet your needs or not, but it's free of charge so it
might be worthwhile to evaluate.
/Lennart
Dave Hughes
> Hello and Welcome to Moviephone!
>
> I have been working in DB2 8.x (on UNIX) for a couple of months now
> using Windows as a client machine. Coming from an Oracle/SQLPlus world,
> I have certain expectations of a tool like "Command Editor" - or at
> least I did.
>
> So I have a couple of questions for you db gurus out there that are
> familiar with DB2 8.x. I have searched the web and have yet to find any
> information.
>
[snip several command editor questions]
In general I avoid the GUI tools and stick strictly to the command line;
there's very little one can't do from the CLP (Command Line Processor) in
DB2 that one can do from the GUI tools (and I find the CLP much more
powerful and convenient). Hence, I can't really answer these questions
regarding the Command Editor, sorry!
> If I can do all of these things, I will sing DB2's praises. I have no
> problem with the database itself. If "Command Editor" cannot perform
> these things BUT I can go in through a DOS cmd window, I would be just
> as happy!
Okay - here I can help :-)
The CLP in DB2 is very flexible, once one realizes that it can operate in
several different ways. Given the reference to DOS above, I'll assume
you're in Windows (as opposed to some Linux/UNIX).
Start a "DB2 Command Window" from the DB2 group under the Start Menu.
This starts up "db2cmd" which is basically a customized cmd.exe
environment.
Typing "db2 -t" from the command line will start the CLP in "interactive"
mode (the -t specifies that semi-colon will be used as the statement
terminator). From here you can run SQL statements or CLP commands
directly. For example:
C:\Program Files\IBM\SQLLIB\BIN>db2 -t
[snip lots of preamble]
db2 => CONNECT TO MYDB USER DAVE;
Enter current password for DAVE:
Database Connection Information
Database server = DB2/NT 8.2.7
SQL authorization ID = DAVE
Local database alias = MYDB
db2 => SELECT COUNT(*) FROM COUNTRIES;
1
-----------
120
1 record(s) selected.
db2 => TERMINATE;
DB20000I The TERMINATE command completed successfully.
Nothing particularly special there, although it can be useful for pasting
in statements for immediate execution.
The second mode is "batch". Fire up your favourite text editor and stick
something like the following in it:
CONNECT TO MYDB USER username USING password;
SELECT * FROM sometable;
TERMINATE;
Save it as "script.sql" and from the same command line run:
db2 -t -v -f script.sql
This will execute the saved script. Again, -t specifies that semi-colon
is the statement terminator. The -v switch causes the statements to be
echoed as they are executed, and -f indicates the file containing the
statements to be run.
Again, nothing particularly special there. However, it's the final mode
that's the really interesting one. The "customization" of the cmd.exe
environment referred to earlier is that a persistent "background"
connection to a DB2 database can be maintained *between* calls to the CLP
in this environment. Hence:
C:\Program Files\IBM\SQLLIB\BIN>db2 CONNECT TO MYDB USER DAVE
Enter current password for DAVE:
Database Connection Information
Database server = DB2/NT 8.2.7
SQL authorization ID = DAVE
Local database alias = MYDB
C:\Program Files\IBM\SQLLIB\BIN>db2 "SELECT COUNT(*) FROM COUNTRIES"
1
-----------
120
1 record(s) selected.
C:\Program Files\IBM\SQLLIB\BIN>db2 TERMINATE
DB20000I The TERMINATE command completed successfully.
Notice that the above statements are being run directly from the command
line, "outside" the interpreter and yet the connection to MYDB persists
between executions of the db2 process. Also note one has to be slightly
careful about quoting SQL statements used from the command line. Had I
done something like:
db2 SELECT COUNT(*) FROM COUNTRIES WHERE POPULATION > 1000000
The "greater-than" symbol would have been interpreted by the command line
as a redirection character, and a new file called "1000000" would have
been created (and an incomplete query passed to the CLP which would
reject it with an error). Simply double-quote the statement, and
everything works as expected.
This brings up numerous interesting possibilities when combined with
batch files. For example, using some of the extensions available to the
cmd.exe batch language under 2000/XP one can implement a crude form of
parameter passing to queries.
Again, fire up your favourite text editor and stick something similar to
the following in it:
@echo off
setlocal enableextensions
set /p POP=Population to query?
db2 "CONNECT TO MYDB USER username USING password" >nul 2>&1
if ERRORLEVEL 4 (
echo Unable to connect to the database!
goto exit
)
echo The following countries have populations bigger than %POP%:
db2 -x "SELECT NAME" ^
"FROM COUNTRIES" ^
"WHERE POPULATION > %POP%"
if ERRORLEVEL 4 (
echo Oh no! Something went horribly wrong!
goto exit
)
db2 "TERMINATE" >nul 2>&1
:exit
endlocal
Save it as "script.cmd" and then run it from the DB2 Command Window:
C:\Documents and Settings\dave> script
Population to query? 1000000
The following countries have populations bigger than 1000000:
United Kingdom
United States
[snipped a load more ouptut]...
The above script demonstrates several things:
* prompting the user for values (set /p)
* substituting environment variables into SQL to be run by the CLP
* quoting SQL statements that involve redirection characters (>)
* splitting long statements over lines with the cmd escape char (^)
* checking the exit code of the CLP (>=4 indicates an error, <4 indicates
success, although possibly with warnings if not zero)
* suppressing all CLP output (>nul 2>&1)
* suppressing headers and footers in CLP queries (-x)
* how ugly the cmd.exe batch language is :-)
Frankly this is a bit more useful on Linux/UNIX (which have considerably
more powerful & flexible command lines than cmd.exe), although as can be
seen above quite a bit is still possible with cmd.exe (it's also possible
to loop over results from a query in batch file with the "for" batch
statement).
That said, there are some decent replacement shells for XP (JPSoft's 4NT
springs to mind, as does the bash implementation under Cygwin although it
can be a bit of an effort to get that "playing nicely" with DB2).
Note that the script above *must* be run from within db2cmd.exe session
(i.e. a DB2 Command Window) rather than "pure" cmd.exe (otherwise the CLP
will refuse to operate). However, db2cmd.exe can be invoked within an
ordinary cmd.exe session like so:
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\dave> db2cmd -i -w -c script.cmd
Population to query? 1000000
..
HTH,
Dave.
Bob
Dave - I really appreciate the extended and timely reply.
I am also a command-line type of developer. Its faster, more control,
and it forces you to know more about what is going on - and you can do
things in batch (I generate a lot of batch db scripts using Excel).
I also have the ability to go directly into Unix and use that command
line. Do you know of a good reference for commands in there?
Lenhart - this place is so locked down on tool control its ridiculous
but I have a number of acquaintances that use squirrel and love it.
Bob
Dave Hughes
> Dave - I really appreciate the extended and timely reply.
>
> I am also a command-line type of developer. Its faster, more control,
> and it forces you to know more about what is going on - and you can do
> things in batch (I generate a lot of batch db scripts using Excel).
Hear hear! :-)
> I also have the ability to go directly into Unix and use that command
> line. Do you know of a good reference for commands in there?
Well, the DB2 CLP is basically the same in both Windows and Linux/UNIX
although there are some subtle differences. The most important to be
aware of is that there's no direct equivalent to db2cmd.exe on UNIX;
instead the "customization" of the shell environment is handled by a
shell script which you need to source prior to using the DB2 CLP.
The script is under the home directory of the database instance user,
usually ~db2inst1/sqllib/db2profile so a typical session on Linux might
look like the following:
dave@server ~ $ source ~db2inst1/sqllib/db2profile
dave@server ~ $ db2 CONNECT TO TEST
Database Connection Information
Database server = DB2/LINUX 8.2.7
SQL authorization ID = DAVE
Local database alias = TEST
dave@server ~ $ db2 "CREATE TABLE TEST (A INTEGER NOT NULL PRIMARY KEY)"
DB20000I The SQL command completed successfully.
dave@server ~ $ db2 "INSERT INTO TEST VALUES (1), (2), (3)"
DB20000I The SQL command completed successfully.
dave@server ~ $ db2 "SELECT * FROM TEST"
A
-----------
1
2
3
3 record(s) selected.
You may wish to add the "source ~db2inst1/sqllib/db2profile" line to the
end of your ~/.bashrc script so it's always done (IIRC, the installer
already does this for the instance user, i.e. db2inst1).
Note that this applies even on a Linux/UNIX box which is just a client.
The DB2 client on Linux/UNIX sets up a sort of "fake" database instance
similar to a DB2 server but incapable of actually hosting a database.
The other difference to be aware of is that it's even more important to
quote SQL statements executed directly from the command line on Linux/
UNIX than it is on Windows. This is due to the shell performing wildcard
expansion on UNIX (as compared to Windows in which it's the
responsibility of the application to expand (or not) any wildcards passed
to it).
In the session above, had I specified the following:
dave@server ~ $ db2 SELECT * FROM TEST
The * would have been expanded into the name of every file in my home
directory. This occassionally bites me when I've been working in Windows
for a couple of days (where the above will work), go back to Linux and
forget to quote SQL at the command line :-)
Naturally, this also means you can take advantage of the more powerful
scripting possibilities under Linux/UNIX. For example, it's much easier
to retrieve and act upon data obtained from a query in bash:
#!/bin/bash
source ~db2inst1/sqllib/db2profile
echo -n "Table to query? "
read tabname
db2 "CONNECT TO dbname USER username USING password" >/dev/null || {
echo "Failed to connect to database"
exit 1
}
count=$(db2 -x "SELECT COUNT(*) FROM $tabname") || {
echo "Failed to query table $tabname"
exit 1
}
if [[ $count -eq 0 ]]; then
echo "Table is empty!"
else
echo "Table has $count rows"
fi
db2 "TERMINATE" >/dev/null
The above is actually possible in XP's CMD to with the FOR command's
"usebackq" option, it's just a lot more tricky/messy.
You can also pipe SQL to execute to the CLP (again, this is possible on
Windows, but the command line environment on Linux/UNIX makes this much
more useful). For example, if I need to reverse the effects of a script
I've run which contains a pile of CREATE TABLE/VIEW/INDEX/TRIGGER
statements:
dave@server ~ $ tac myscript.sql | egrep "^CREATE" | sed -re 's/^CREATE
(UNIQUE )?(TABLE|VIEW|INDEX|TRIGGER) ([^ ]*).*$/DROP \2 \3;/' | db2 +p
(the +p option just tells the CLP to skip its huge boiler plate startup
text :-)
Finally, some CLP related documentation references:
DB2 CLP command line invocation:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/
com.ibm.db2.udb.doc/core/r0010409.htm
DB2 CLP options:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/
com.ibm.db2.udb.doc/core/r0010410.htm
DB2 CLP return codes:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/
com.ibm.db2.udb.doc/core/r0010411.htm
Cheers,
Dave.
Bob
Excellent! Now I have hope!
Of course, it takes time to get familiar with new tools but I am going
to be the DBA on this project through production deployment in early
2009. I have the baseline OLTP up and running for the developers and
now I can get into refining how to work more efficiently rather than
hacking my way through using the first method that gets the job done.
Dave - Really appreciate the response. As noted earlier - I do a lot
of script automation for DBs using MS Excel (use it for database
design on the front end because that is where I work for the most
part). No more looking at object create scripts or insert scripts -
removes human error and provides uniform naming conventions for
everything. If you ever have interest in this area let me know and
maybe I can hook you up with some tips on automating repetitive
tasks). Contact me directly on this.
Cheers yourself - you in the UK?
Bob