cygwin - DB2 on windows

[reachsamdurai@gmail.com]

Hello,

Can you please let me know the procedure to reach db2 command prompt
from a cygwin window (hence without using Start-> Run -> db2cmd
method). I'm planning to write shell scripts which accesses DB2
database on windows platform hence I installed cygwin but I'm unable to
connect to database as it fails with the error message.
$ db2
DB21061E Command line environment not initialized.

Please advice.

Thanks,
Sam.

[Benjamin Gufler]

Re: cygwin - DB2 on windows

Hi,

On 05/26/06 03:18, reachsamdurai@g mail.com wrote:[color=blue]
> Can you please let me know the procedure to reach db2 command prompt
> from a cygwin window[/color]

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.

hth,
Benjamin
--
Please compose your messages as plaintext,
and do not send MS Office attachments:

http://www.efn.no/html-bad.html

http://www.goldmark.org/netrants/no-word/attach.html

[Dave Hughes]

Re: cygwin - DB2 on windows

Benjamin Gufler wrote:
[color=blue]
> Hi,
>
> On 05/26/06 03:18, reachsamdurai@g mail.com wrote:[color=green]
> > Can you please let me know the procedure to reach db2 command prompt
> > from a cygwin window[/color]
>
> 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.[/color]

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.b at 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="$optio ns $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="$optio ns -f $script"
else
options="$optio ns -f \"${script}\ ""
fi
else
options="$optio ns -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.

--

[Dave Hughes]

Re: cygwin - DB2 on windows

Dave Hughes wrote:

[snip][color=blue]
> 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:[/color]
[snip]

Oops ... quick correction to this bit (somehow I managed to miss most
of it out!). The example should have read:

dave@homer ~
$ cat << EOF | db2cmd -i -w -c db2 -t +p[color=blue]
> CONNECT TO TESTIW USER DAVE USING ...;
> CREATE TABLE TEST (A INTEGER NOT NULL PRIMARY KEY);
> INSERT INTO TEST VALUES (1), (2), (3);
> SELECT COUNT(*) FROM TEST;
> DROP TABLE TEST;
> CONNECT RESET;
> EOF[/color]


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.

1
-----------
3

1 record(s) selected.

DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.


HTH,

Dave.


--

[Jan M. Nelken]

Re: cygwin - DB2 on windows

reachsamdurai@g mail.com wrote:[color=blue]
> Hello,
>
> Can you please let me know the procedure to reach db2 command prompt
> from a cygwin window (hence without using Start-> Run -> db2cmd
> method). I'm planning to write shell scripts which accesses DB2
> database on windows platform hence I installed cygwin but I'm unable to
> connect to database as it fails with the error message.
> $ db2
> DB21061E Command line environment not initialized.
>
> Please advice.
>
> Thanks,
> Sam.[/color]

I personally think that when developing shell scripts - presumably to run on
Unix machine - best way is to develop them on Unix machines.

Each of the shells in simulated Unix environments - whether it is Cygwin, or
Microsfoft SFU or MKS toolkit - has little pecularities not applicable to real
Unix shells.

If you are developing shell scripts on Windows to access Db2 on Windows - then I
*really* think you should use DB2 scripting instead - invoked via db2 -tvf
inputscript...
This command in turn can be invoked from any command window via db2cmd command -
as other poster explained above.

But - if you really are stuborn to use Windows to develop shell script for Unix
- here is one way (using db2ntclp.exe *not* officially supported by IBM. Use at
own risk):

1. Establish PID of the shell you are running (in my case 5044);
2. Use db2ntclp tool with a/m PID as parameter;
3. export DB2CLP environment variable set to value printed in db2ntclp output
(in my case 8458152);

Presto - you have DB2 environment available in Cygwin shell.

If you are interested in this approach - let me know what level of DB2 you are
using (output of db2level command will do) and I will try to dig out
db2ntclp.exe for that level


Best regards,

Jan M. Nelken


Example:
-------------------------------------------------------------------------------------------------------------------
db2admin@nelken ~
$ ps
PID PPID PGID WINPID TTY UID STIME COMMAND
5044 1 5044 5044 con 1005 00:49:58 /usr/bin/bash
4868 5044 4868 4828 con 1005 00:50:24 /usr/bin/ps

db2admin@nelken ~
$ db2ntclp 5044
You MUST Set the environment variable DB2CLP=8458152
--> CLP Environment variable anchored to Pid = 5044

db2admin@nelken ~
$ export DB2CLP=8458152

db2admin@nelken ~
$ db2 connect to sample

Database Connection Information

Database server = DB2/NT 8.2.5
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE

db2admin@nelken ~
$
-------------------------------------------------------------------------------------------------------------------