Benjamin Gufler wrote:
Hi,
On 05/26/06 03:18, re***********@gmail.com wrote: Can you please let me know the procedure to reach db2 command prompt
from a cygwin window
Have a look at your Windows environment variables and add those used
by DB2 to your shell's .rc file in cygwin. I don't have a Windows
installation of DB2 to tell you what variables you need. On Linux, you
have DB2DIR, DB2INSTANCE, INSTHOME etc.
Ben: Sorry, but running DB2 under Cygwin on Windows is a bit of a black
art and is very different to running it on any *nix.
Sam: Although you're under a Linux-ish environment with Cygwin, DB2 on
Windows is still a Windows application, and won't change its behaviour
just because you're under Cygwin.
On Windows, DB2 CLP sessions *must* run within a db2cmd session, as the
db2cmd session sets up some environment variables which the CLP uses.
This means you have a couple of methods you can use to get the CLP
running under Cygwin.
Method 1 : db2 under db2cmd under Cygwin
========================================
With this method you run a DB2 CLP session within a db2cmd session from
within a Cygwin shell. From the command line try:
db2cmd -i -w -c db2 [clp parameters]
The options to db2cmd do the following:
-i : don't open a new console, share the existing console and stdin,
stdout handles
-c : run the specified command (db2 etc.) and terminate
-w : wait until the spawned cmd process ends
The problem with this method is that once the db2 process terminates,
the db2cmd process also terminates, closing the backend process (db2bp)
and hence closing any connections you may have open.
Hence, with this method you cannot work as you "normally" would under
Linux/UNIX. For example:
dave@homer ~
$ db2cmd -i -w -c db2 CONNECT TO TESTIW USER ... USING ...
Database Connection Information
Database server = DB2/NT 8.2.0
SQL authorization ID = DAVE
Local database alias = TESTIW
dave@homer ~
$ db2cmd -i -w -c db2 "SELECT COUNT(*) FROM SYSCAT.TABLES"
SQL1024N A database connection does not exist. SQLSTATE=08003
The advantages of this method are that it doesn't require messing
around with starting Cygwin in a different way (you'll see what I mean
with method 2), and it's perfectly good for running entirely
"self-contained" scripts, i.e. a CLP script which connects to a
database, performs some actions and disconnects. For example:
Database Connection Information
Database server = DB2/NT 8.2.0
SQL authorization ID = DAVE
Local database alias = TESTIW
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
....
Method 2 : db2 under Cygwin under db2cmd
========================================
With this method, you start a db2cmd session, and then start Cygwin
within that session. This means that Cygwin inherits the environment
that db2cmd sets up and can pass it on to any db2 CLP sessions you run
under Cygwin.
For example, if you're using the Cygwin X-Windows system, and you're
using the startxwindows.bat batch file to start the X server, just run
that batch file from a "DB2 Command Window" (a db2cmd session) instead
of an ordinary cmd session.
With this method you can use DB2 "normally" as you would under
Linux/UNIX. For example:
dave@homer ~
$ db2 CONNECT TO TESTIW USER ... USING ...
Database Connection Information
Database server = DB2/NT 8.2.0
SQL authorization ID = DAVE
Local database alias = BOBIW
dave@homer ~
$ db2 "SELECT COUNT(*) FROM SYSCAT.TABLES WITH UR"
1
-----------
270
1 record(s) selected.
That's not to say that this method isn't without its disadvantages. The
biggest is that, while in Cygwin if you spawn additional shells, there
is still only a *single* db2cmd session under which all the shells are
running. This means that only a single connection can be only at any
time across *all* shells.
For example, in your initial shell you open a connection to DB1. Next
you open a new shell and open a connection to DB2. If you now switch
back to your first shell and attempt a SELECT statement you'll find its
now connected to DB2!
Obviously, method 1 (which spawns a db2cmd session for each db2
session) doesn't suffer from this disadvantage.
Other Notes
===========
Some other things to be aware of when running under Cygwin: make sure
you haven't got "tty" set in your CYGWIN environment variable (either
that or make sure "notty" is set there. The interactive mode of the DB2
CLP tends to lose keystrokes when "tty" is set.
The DB2 CLP seems to output rather strange end-of-line sequences under
Windows. Specifically, it seems to use \r\r\n (!) so you may have
trouble piping the output to other tools unless you do some conversion
first (some tools, especially ones designed for cross platform use,
count this bizarre sequence as a double-line break, not surprising
given that \r is the Mac newline, and \r\n is the DOS newline).
The DB2 CLP commands that take paths must be given Windows paths (not
surprisingly, they won't accept Cygwin UNIX-style paths). That said,
they seem quite happy to use / as the path separator, so you can
specify things like:
db2 EXPORT TO C:/Temp/SOMEDATA.IXF OF IXF SELECT...
Of course, the "proper" way is to use cygpath to convert the paths from
UNIX-style to Windows-style in back-quotes:
db2 EXPORT TO `cygpath -w /tmp/SOMEDATA.IXF` OF IXF SELECT...
Finally, here's a little ksh function I've used in the past to account
for differences between the various platforms (I vaguely recall that
its tested under both "real" ksh and pdksh on Linux, and pdksh under
Cygwin).
It takes standard CLP options like -s, -c, +c and so on, and either
reads the SQL to execute from stdin (if no filename is specified, or
"-" is specified) or from a file (if a filename is specified WITHOUT
the -f option). On Cygwin under Windows, the output is line-feed
corrected with dos2unix and method 1 is used to execute the script
(hence the script must be "self-contained"; it can't rely on a
pre-existing connection). Finally its exit code is 0 on success and 1
on failure (i.e. it converts the any DB2 CLP exit code less than 4 to
zero).
exec_sql()
{
case "`uname -s`" in
CYGWIN_* )
REALUNIX=false
;;
* )
REALUNIX=true
;;
esac
while [ $# -gt 0 ]; do
case "$1" in
- )
shift
;;
-* | +* )
options="$options $1"
shift
;;
* )
# Reading input from filename, use -f to read input
if [ ! -e "$1" ]; then
print "Cannot find SQL script \"$1\"" >&2
return 1
fi
script="$1"
shift
;;
esac
done
if [ "x$script" != "x" ]; then
if $REALUNIX; then
options="$options -f $script"
else
options="$options -f \"${script}\""
fi
else
options="$options -p-"
fi
if $REALUNIX; then
db2 $options
rc=$?
else
typeset temp=`mktemp` || return 1
db2cmd -i -w -c db2 $options > $temp
rc=$?
cat $temp | dos2unix
rm -f $temp
fi
[ $rc -lt 4 ] && return 0 || return 1
}
Here's a quick example of usage:
cat << EOF | exec_sql -t
CONNECT TO $DBNAME USER $DBUSER USING $DBPASS;
SELECT COUNT(*) FROM SYSCAT.TABLES WITH UR;
CONNECT RESET;
EOF
HTH,
Dave.
--
