Ant and DB2

[Rhino]

I am using DB2 V7.2.7 on Windows XP and I would like to write Ant 1.6.1
scripts that do some of my program preparation. For the benefit of those not
familiar with Ant, it is an open source equivalent to 'make'.

Although there are no specific DB2 tasks in Ant, there is an exec task that
is functionally equivalent to a command line. For instance, to run a 'dir'
command in an Ant exec task, you would write:
<exec executable="cmd.exe" os="Windows XP" output="dir.txt">

<arg value="/c dir"/>

</exec>

This executes the Windows XP cmd.exe program, executes the 'dir' command,
and writes the output to a file called 'dir.txt'.

With that background out of the way, can anyone tell me how to execute a db2
command that needs a db2 connection?

I can execute a db2 command that *doesn't* need a connection successfully
this way:

<exec executable="cmd.exe" os="Windows XP">

<arg value="/c db2cmd db2 get dbm cfg"/>

</exec>

However, I haven't figured out how to execute a command that *does* need a
connection. I'd like to be able to get a connection and then issue a CREATE
PROCEDURE or a COMMENT ON that affects objects in the database to which I'm
connected.

I'd appreciate any guidance that anyone could give.

I've googled but haven't seen anything concrete on how Ant could be used to
do what I'm trying to do.

--
Rhino
---
rhino1 AT sympatico DOT ca
"If you're getting something for nothing, you're not using your own credit
card."

[Kai-Uwe Klavei]

Rhino wrote:

I am using DB2 V7.2.7 on Windows XP and I would like to write Ant 1.6.1
scripts that do some of my program preparation.[...]

However, I haven't figured out how to execute a command that *does* need a
connection. I'd like to be able to get a connection and then issue a CREATE
PROCEDURE or a COMMENT ON that affects objects in the database to which I'm
connected.

You can use the Ant Core Task "sql" - take a look at the provided
examples in the documentation.

Here is an example target I used before - as you can see this one is for
an Oracle database:

<target name="dbinit" depends=""
description="Creates and populates database tables.">
<sql
classpath="${class.path}"
driver="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@//localhost:1521/orcl"
userid="technical_library"
password="na"
rdbms="oracle"
version="10.1.0.2.0"
onerror="continue">
<transaction src="${base.dir}/purge_technical_library.sql"/>
<transaction src="${base.dir}/create_technical_library.sql"/>
<transaction src="${base.dir}/populate_technical_library.sql"/>
</sql>
</target>

Kai-Uwe

[Dave Hughes]

On 2004-04-14, Rhino scribbled:

[Snip]

I can execute a db2 command that *doesn't* need a connection
successfully this way:

<exec executable="cmd.exe" os="Windows XP">

<arg value="/c db2cmd db2 get dbm cfg"/>

</exec>

However, I haven't figured out how to execute a command that does
need a connection. I'd like to be able to get a connection and then
issue a CREATE PROCEDURE or a COMMENT ON that affects objects in the
database to which I'm connected.

[Snip]

If Ant has some mechanism where it can interface to a database (through
JDBC I guess since it's a Java app), you could use that - though you
wouldn't be able to run DB2 utilities like RUNSTATS, IMPORT or LOAD
since they're not SQL commands.

I've come across a similar sitation when writing bash scripts for DB2
with Cygwin under Windows (which needed to run such utilities). The
problem is the peculiar "db2cmd" application. Under Linux/UNIX this
doesn't exist, and you'd be able to do what you're trying to.

I'm not 100% sure what's going on with it, but the following theory
seems to fit: The "db2cmd" application holds some sort of session or
connection handle open which the "db2" application uses. Therefore all
SQL commands which require an open connection must happen within a
series of "db2" commands running within a *continuous* "db2cmd" session.

I got around this in my bash scripts by generating a load of commands,
dumping them to a temporary text file, then running something like

db2cmd -iw -c db2 -tv -f some_temp_file.sql

To ensure that all the commands are run within a single "db2cmd"
session. I'm guessing you could resort to something similar with
Ant. Either get your script to write out the SQL to a temporary file
and remove it after it's done, or have an SQL file which accompanies
the Ant script, and use something like:

<exec executable="db2cmd.exe" os="Windows XP">
<arg value="-i"/>
<arg value="-w"/>
<arg value="-c db2 -tvf some_sql.sql"/>
</exec>

BTW, the -i and -w switches just ensure that the db2cmd session runs
under the same console and inherit the same stdin/stdout handles as Ant
is using - but you may not need them (under bash they were useful as I
could then use redirects for logging purposes, or pipe SQL commands
through stdin instead of using an input file).

HTH, Dave.

--
Dave
Remove "_nospam" for valid e-mail address

"Never underestimate the bandwidth of a station wagon full of CDs doing
a ton down the highway" -- Anon.

[Rhino]

"Kai-Uwe Klavei" <kl****@gmx.de> wrote in message
news:c5*************@news.t-online.com...

Rhino wrote:

I am using DB2 V7.2.7 on Windows XP and I would like to write Ant 1.6.1
scripts that do some of my program preparation.[...]

However, I haven't figured out how to execute a command that *does* need a connection. I'd like to be able to get a connection and then issue a CREATE PROCEDURE or a COMMENT ON that affects objects in the database to which I'm connected.

You can use the Ant Core Task "sql" - take a look at the provided
examples in the documentation.

Here is an example target I used before - as you can see this one is for
an Oracle database:

<target name="dbinit" depends=""
description="Creates and populates database tables.">
<sql
classpath="${class.path}"
driver="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@//localhost:1521/orcl"
userid="technical_library"
password="na"
rdbms="oracle"
version="10.1.0.2.0"
onerror="continue">
<transaction src="${base.dir}/purge_technical_library.sql"/>
<transaction src="${base.dir}/create_technical_library.sql"/>
<transaction src="${base.dir}/populate_technical_library.sql"/>
</sql>
</target>

For the sake of those following this thread, now or in the future, I was
not able to make this work using the following code:

<target name="Prep_Stored_Proc" description="Prepare stored procedure">

<sql classpath="C:/Program Files/SQLLIB/java/db2java.zip"

driver="COM.ibm.db2.jdbc.app.DB2Driver" url="jdbc:db2:sample" userid="rhino"

password="rhino" onerror="continue" caching="yes" print="yes"

output="sql.out">

comment on specific procedure income_proc is 'Rhino';

get dbm cfg;

</sql>

</target>

The first time I ran it, it *almost* worked - only the first of the two
commands executed - but on the second and subsequent attempts I got an error
message. Since I had the -debug and -verbose switches on, here is the entire
output stream:

Prep_Stored_Proc:

Adding reference: eclipse.progress.monitor

[sql] connecting to jdbc:db2:sample

[sql] Loading COM.ibm.db2.jdbc.app.DB2Driver using AntClassLoader with
classpath C:\Program Files\SQLLIB\java\db2java.zip

Finding class COM.ibm.db2.jdbc.app.DB2Driver

Loaded from C:\Program Files\SQLLIB\java\db2java.zip
COM/ibm/db2/jdbc/app/DB2Driver.class

Class java.lang.Object loaded from parent loader (parentFirst)

Class java.sql.Driver loaded from parent loader (parentFirst)

Class COM.ibm.db2.jdbc.app.DB2Driver loaded from ant loader (parentFirst)

Class java.util.Hashtable loaded from parent loader (parentFirst)

Class java.util.Properties loaded from parent loader (parentFirst)

Class java.sql.Connection loaded from parent loader (parentFirst)

Class java.lang.Throwable loaded from parent loader (parentFirst)

Class java.sql.SQLException loaded from parent loader (parentFirst)

Class java.lang.UnsatisfiedLinkError loaded from parent loader (parentFirst)

Class java.security.PrivilegedAction loaded from parent loader (parentFirst)

Class java.lang.String loaded from parent loader (parentFirst)

Finding class COM.ibm.db2.jdbc.app.DB2Driver$1

Loaded from C:\Program Files\SQLLIB\java\db2java.zip
COM/ibm/db2/jdbc/app/DB2Driver$1.class

Class COM.ibm.db2.jdbc.app.DB2Driver$1 loaded from ant loader (parentFirst)

Class java.security.AccessController loaded from parent loader (parentFirst)

Class java.lang.System loaded from parent loader (parentFirst)

Finding class COM.ibm.db2.jdbc.app.DB2Driver$3

Loaded from C:\Program Files\SQLLIB\java\db2java.zip
COM/ibm/db2/jdbc/app/DB2Driver$3.class

Class COM.ibm.db2.jdbc.app.DB2Driver$3 loaded from ant loader (parentFirst)

Finding class COM.ibm.db2.mri.DB2Messages

Loaded from C:\Program Files\SQLLIB\java\db2java.zip
COM/ibm/db2/mri/DB2Messages.class

Class COM.ibm.db2.mri.DB2Messages loaded from ant loader (parentFirst)

Class java.util.ResourceBundle loaded from parent loader (parentFirst)

Finding class COM.ibm.db2.mri.DB2ErrorMessages

Loaded from C:\Program Files\SQLLIB\java\db2java.zip
COM/ibm/db2/mri/DB2ErrorMessages.class

Class java.util.ListResourceBundle loaded from parent loader (parentFirst)

Class COM.ibm.db2.mri.DB2ErrorMessages loaded from ant loader (parentFirst)

Finding class COM.ibm.db2.mri.DB2ErrorMessages_en

Loaded from C:\Program Files\SQLLIB\java\db2java.zip
COM/ibm/db2/mri/DB2ErrorMessages_en.class

Class COM.ibm.db2.mri.DB2ErrorMessages_en loaded from ant loader
(parentFirst)

Finding class COM.ibm.db2.mri.DB2ErrorMessages_en_CA

Couldn't load ResourceStream for
COM/ibm/db2/mri/DB2ErrorMessages_en_CA.properties

Class java.lang.StringBuffer loaded from parent loader (parentFirst)

Class java.sql.DriverManager loaded from parent loader (parentFirst)

[sql] BUILD FAILED: D:\eclipse\workspace\Ron Devine\xml\build.xml:160:
java.sql.SQLException: java.lang.UnsatisfiedLinkError: Native Library
C:\Program Files\SQLLIB\bin\db2jdbc.dll already loaded in another
classloader

I googled but couldn't find out much of any use about this
java.lang.UnsatisfiedLinkError, particularly why the db2jdbc.dll is "already
be loaded in another classloader". The only remedy to the problem I could
find was "kill all the processes". Since no further details were provided, I
simply rebooted rather than trying to guess which of the processes on this
machine needed to be killed.

For what it's worth, I set caching=yes before attempting the Ant task again
(after I rebooted) but the same problem occurred.

I still have hopes of making this approach work someday - if someone can
tell me how to prevent this problem from recurring - but, for now I have
another solution that works. See my comments on the other reply I got to my
original post for the details.

Rhino

[Rhino]

"Dave Hughes" <da*********@waveform.plus.com> wrote in message
news:xn****************@usenet.plus.net...

On 2004-04-14, Rhino scribbled:

[Snip]

I can execute a db2 command that *doesn't* need a connection
successfully this way:

<exec executable="cmd.exe" os="Windows XP">

<arg value="/c db2cmd db2 get dbm cfg"/>

</exec>

However, I haven't figured out how to execute a command that does
need a connection. I'd like to be able to get a connection and then
issue a CREATE PROCEDURE or a COMMENT ON that affects objects in the
database to which I'm connected.

[Snip]

If Ant has some mechanism where it can interface to a database (through
JDBC I guess since it's a Java app), you could use that - though you
wouldn't be able to run DB2 utilities like RUNSTATS, IMPORT or LOAD
since they're not SQL commands.

I've come across a similar sitation when writing bash scripts for DB2
with Cygwin under Windows (which needed to run such utilities). The
problem is the peculiar "db2cmd" application. Under Linux/UNIX this
doesn't exist, and you'd be able to do what you're trying to.

I'm not 100% sure what's going on with it, but the following theory
seems to fit: The "db2cmd" application holds some sort of session or
connection handle open which the "db2" application uses. Therefore all
SQL commands which require an open connection must happen within a
series of "db2" commands running within a *continuous* "db2cmd" session.

I got around this in my bash scripts by generating a load of commands,
dumping them to a temporary text file, then running something like

db2cmd -iw -c db2 -tv -f some_temp_file.sql

To ensure that all the commands are run within a single "db2cmd"
session. I'm guessing you could resort to something similar with
Ant. Either get your script to write out the SQL to a temporary file
and remove it after it's done, or have an SQL file which accompanies
the Ant script, and use something like:

<exec executable="db2cmd.exe" os="Windows XP">
<arg value="-i"/>
<arg value="-w"/>
<arg value="-c db2 -tvf some_sql.sql"/>
</exec>

BTW, the -i and -w switches just ensure that the db2cmd session runs
under the same console and inherit the same stdin/stdout handles as Ant
is using - but you may not need them (under bash they were useful as I
could then use redirects for logging purposes, or pipe SQL commands
through stdin instead of using an input file).

Your suggested solution didn't quite work but helped me get to this, which
does work:

<exec executable="cmd.exe" os="Windows XP">

<arg value="/c db2cmd db2 -tvf db2_001.in -z db2_001.out"/>

</exec>
where db2_001.in, the input file, contained:

connect to sample;

comment on table employee is 'ZZZ';

quit;

For anyone following this thread now or in the future, I also tried using
the 'output' parameter in the exec tag rather than using the -z flag in the
arg tag but it didn't work; the output file was empty afterwards. The -o
flag would have worked instead of the -z flag but I prefer to see both the
command and its result in the same file so I use -z.

Each time this task is run, the output just keeps getting appended to the
output file, db2_001.out, so it would be a good idea to add a 'delete' task
that deletes db2_001.out each time before you run the <exec> task. That
would look like this:

<delete file="db2_001.out"/>

The entire task, with the delete step, would look like this:

<target name="DB2_Stuff" description="Do DB2 stuff.">

<delete file="db2_001.out"/>

<exec executable="cmd.exe" os="Windows XP">

<arg value="/c db2cmd db2 -tvf db2_001.in -z db2_001.out"/>

</exec>

<exec executable="cmd.exe" os="Windows XP">

<arg value="/c db2cmd db2 get dbm cfg"/>

</exec>

</target>

The second 'exec' step in this example shows how to execute a db2 command
that *doesn't* need a database connection without having to resort to input
files and db2 -tvf.

For what it's worth, all of these examples were executed on Windows XP using
Ant 1.6.1, the latest version. No guarantees that these same examples will
work the same on other operating systems or older versions of Ant!

I hope this helps someone down the road....

Rhino

[Kai-Uwe Klavei]

Rhino wrote:

For the sake of those following this thread, now or in the future, I
was not able to make this work using the following code:

<target name="Prep_Stored_Proc"
description="Prepare stored procedure">
<sql
classpath="C:/Program Files/SQLLIB/java/db2java.zip"
driver="COM.ibm.db2.jdbc.app.DB2Driver"
url="jdbc:db2:sample" userid="rhino" password="rhino"
onerror="continue" caching="yes" print="yes"
output="sql.out">
comment on specific procedure income_proc is 'Rhino';
get dbm cfg;
</sql>
</target>

The first time I ran it, it *almost* worked - only the first of the
two commands executed - but on the second and subsequent attempts I
got an error message. Since I had the -debug and -verbose switches
on, here is the entire output stream:
[...]
[sql] BUILD FAILED: D:\eclipse\workspace\Ron Devine\xml\build.xml:160:
java.sql.SQLException: java.lang.UnsatisfiedLinkError: Native Library
C:\Program Files\SQLLIB\bin\db2jdbc.dll already loaded in another
classloader

In fact COM.ibm.db2.jdbc.app.DB2Driver is a type 2 driver that relies on
a native library.

The error seems to be a bug - I found this:
http://jce.iaik.tugraz.at/products/1...index.php#Q1_4

The second command "get dbm cfg" is a CLP command, not an SQL command,
it cannot be executed via JDBC.

Execute it via the system command db2cmd in an Ant exec task as you did
before.

Kai-Uwe

[PM \(pm3iinc-nospam\) CGO]

You may scan db2dd just in case.
http://www-106.ibm.com/search/search...searchScope=dW

PM

[Rhino]

"PM (pm3iinc-nospam) CGO" <PM (pm3iinc-nospam)@cgocable.ca> wrote in message
news:qx*****************@charlie.risq.qc.ca...

You may scan db2dd just in case.
http://www-106.ibm.com/search/search...searchScope=dW
PM

Great suggestion!

I've already found a couple of articles that have enhanced my understanding
of Ant.

Rhino