ant and db2cmd help please

[wb5plj]

Hi I am having a problem passing some sql to the db2cmd via ant. This
is very confusing as I am doing this exact thing elseware with no
problem (just differant sql, and I have verified the sql is valid) but
in this one I am having problems. Any time I run this I get the
folowing message from the db2 clp window that opens up.

SQL0104N An unexpected token "SET" was found following
"<identifier>".
Expected tokens may include: "NEW". SQLSTATE=42601
C:\eclipse_workspace\massSQL>
I don't know what is going on here but I am by no means familiar with
db2cmd and all that so any help is apreachated. If I could get this to
work in a batch file or from a command line I am sure I could get the
ant part of it on my own. it is the db2 that is giving me issues.

thanks. Dwayne


this is the console log from ant of what is being passed.
Apache Ant version 1.7.0 compiled on December 13 2006
Buildfile: C:\eclipse_workspace\massSQL\count.xml
parsing buildfile C:\eclipse_workspace\massSQL\count.xml with URI =
file:/C:/eclipse_workspace/massSQL/count.xml
Project base dir set to: C:\eclipse_workspace\massSQL
Build sequence for target(s) `query' is [init, query]
Complete build sequence is [init, query, query3, query2, ]
init:
[antlib:org.apache.tools.ant] Could not load definitions from resource
org/apache/tools/ant/antlib.xml. It could not be found.
[loadfile] loading C:\eclipse_workspace\massSQL\transactionCount.sql
into property sql
[loadfile] loaded 383 characters
[echoproperties] #Ant properties
[echoproperties] #Mon Apr 09 11:12:51 CDT 2007
[echoproperties] DSTAMP=20070409
[echoproperties] TODAY=April 9 2007
[echoproperties] TSTAMP=1112
[echoproperties] ant.core.lib=C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant.jar
[echoproperties] ant.file=C\:\\eclipse_workspace\\massSQL\\count.xm l
[echoproperties] ant.file.dailyTransactions=C\:\\eclipse_workspace\
\massSQL\\count.xml
[echoproperties] ant.home=C\:\\eclipse\\plugins\\org.apache.ant_1.7 .0
[echoproperties] ant.java.version=1.5
[echoproperties] ant.library.dir=C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib
[echoproperties] ant.project.name=dailyTransactions
[echoproperties] ant.version=Apache Ant version 1.7.0 compiled on
December 13 2006
[echoproperties] awt.toolkit=sun.awt.windows.WToolkit
[echoproperties] basedir=C\:\\eclipse_workspace\\massSQL
[echoproperties] database=db0z
[echoproperties] eclipse.pdebuild.home=/C\:/eclipse/plugins/
org.eclipse.pde.build_3.2.1.r321_v20060823/./
[echoproperties] eclipse.pdebuild.scripts=/C\:/eclipse/plugins/
org.eclipse.pde.build_3.2.1.r321_v20060823/scripts/
[echoproperties] eclipse.pdebuild.templates=/C\:/eclipse/plugins/
org.eclipse.pde.build_3.2.1.r321_v20060823/templates/
[echoproperties] errorFile=dailyTransactionsError.txt
[echoproperties] file.encoding=Cp1252
[echoproperties] file.encoding.pkg=sun.io
[echoproperties] file.separator=\\
[echoproperties] java.awt.graphicsenv=sun.awt.Win32GraphicsEnvironm ent
[echoproperties] java.awt.printerjob=sun.awt.windows.WPrinterJob
[echoproperties] java.class.path=C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-antlr.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-apache-bcel.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-apache-bsf.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-apache-log4j.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-apache-oro.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-apache-regexp.jar;C\:\\eclipse\\plugins
\\org.apache.ant_1.7.0\\lib\\ant-apache-resolver.jar;C\:\\eclipse\
\plugins\\org.apache.ant_1.7.0\\lib\\ant-commons-logging.jar;C\:\
\eclipse\\plugins\\org.apache.ant_1.7.0\\lib\\ant-commons-net.jar;C\:\
\eclipse\\plugins\\org.apache.ant_1.7.0\\lib\\ant-jai.jar;C\:\\eclipse\
\plugins\\org.apache.ant_1.7.0\\lib\\ant-javamail.jar;C\:\\eclipse\
\plugins\\org.apache.ant_1.7.0\\lib\\ant-jdepend.jar;C\:\\eclipse\
\plugins\\org.apache.ant_1.7.0\\lib\\ant-jmf.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-jsch.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-junit.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-launcher.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-netrexx.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-nodeps.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-starteam.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-stylebook.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-swing.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-testutil.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-trax.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant-weblogic.jar;C\:\\eclipse\\plugins\
\org.apache.ant_1.7.0\\lib\\ant.jar;C\:\\eclipse\\ plugins\
\org.apache.ant_1.7.0\\lib\\commons-net-1.4.1.jar;C\:\\eclipse\\plugins
\\org.apache.ant_1.7.0\\lib\\jakarta-oro-2.0.8.jar;C\:\\eclipse\
\plugins\\org.apache.ant_1.7.0\\lib\\xercesImpl.ja r;C\:\\eclipse\
\plugins\\org.apache.ant_1.7.0\\lib\\xml-apis.jar;C\:\\eclipse\
\configuration\\org.eclipse.osgi\\bundles\\10\\1\\ .cp\\lib\
\remoteAnt.jar;C\:\\IBM\\WebSphere\\AppServer\\jav a\\lib\\tools.jar;C\:
\\eclipse\\plugins\\org.eclipse.swt.win32.win32.x8 6_3.2.2.v3235a.jar
[echoproperties] java.class.version=49.0
[echoproperties] java.endorsed.dirs=C\:\\Program Files\\Java\
\jre1.5.0_06\\lib\\endorsed
[echoproperties] java.ext.dirs=C\:\\Program Files\\Java\\jre1.5.0_06\
\lib\\ext
[echoproperties] java.home=C\:\\Program Files\\Java\\jre1.5.0_06
[echoproperties] java.io.tmpdir=C\:\\DOCUME~1\\WalkeD\\LOCALS~1\\Te mp\
\
[echoproperties] java.library.path=C\:\\WINDOWS\\system32;.;C\:\
\WINDOWS\\system32;C\:\\WINDOWS;C\:\\Program Files\\IBM\\WebSphere MQ\
\Java\\lib;C\:\\WINDOWS\\system32;C\:\\WINDOWS;C\: \\WINDOWS\\System32\
\Wbem;C\:\\Program Files\\IBM\\IBM CICS Transaction Gateway\\BIN;C\:\
\apache-ant-1.7.0\\bin;C\:\\j2sdk1.4.1_01\\bin;C\:\\Program Files\\IBM\
\WebSphere MQ\\bin;C\:\\Program Files\\IBM\\WebSphere MQ\\WEMPS\\bin;C
\:\\Program Files\\TextPad 4;C\:\\Program Files\\PuTTY;C\:\\PROGRA~1\
\IBM\\SQLLIB\\BIN;C\:\\PROGRA~1\\IBM\\SQLLIB\\FUNC TION;C\:\\PROGRA~1\
\IBM\\SQLLIB\\SAMPLES\\REPL;;C\:\\Program Files\\Visual Explain\\lib;C
\:\\Program Files\\CVSNT\\;C\:\\eclipse\\configuration\
\org.eclipse.osgi\\bundles\\718\\1\\.cp
[echoproperties] java.runtime.name=Java(TM) 2 Runtime Environment,
Standard Edition
[echoproperties] java.runtime.version=1.5.0_06-b05
[echoproperties] java.specification.name=Java Platform API
Specification
[echoproperties] java.specification.vendor=Sun Microsystems Inc.
[echoproperties] java.specification.version=1.5
[echoproperties] java.vendor=Sun Microsystems Inc.
[echoproperties] java.vendor.url=http\://java.sun.com/
[echoproperties] java.vendor.url.bug=http\://java.sun.com/cgi-bin/
bugreport.cgi
[echoproperties] java.version=1.5.0_06
[echoproperties] java.vm.info=mixed mode
[echoproperties] java.vm.name=Java HotSpot(TM) Client VM
[echoproperties] java.vm.specification.name=Java Virtual Machine
Specification
[echoproperties] java.vm.specification.vendor=Sun Microsystems Inc.
[echoproperties] java.vm.specification.version=1.0
[echoproperties] java.vm.vendor=Sun Microsystems Inc.
[echoproperties] java.vm.version=1.5.0_06-b05
[echoproperties] line.separator=\r\n
[echoproperties] os.arch=x86
[echoproperties] os.name=Windows XP
[echoproperties] os.version=5.1
[echoproperties] outputFile=dailyTransactions.out.txt
[echoproperties] path.separator=;
[echoproperties] schema=isdb1
[echoproperties] sql=connect to db0z user dmfstr1 using dfstr101; SET
SCHEMA isdb1; Select count (*) from isdb1.SUBTRANSACTION s,
isdb1.subtransactiontype st where s.SUBTRANSACTIONTYPE_SUBTRANSA12 \=
st.subtransactiontypeid and s.subtransactionstatusid \= 2 and
st.SUBTRANSACTIONMODULEID in (1,2,5,7,8,9) and s.createdon between
'2007-04-09 00\:01\:01.000000' and '2007-04-09 23\:59\:01.424000';\t
\t \t quit;
[echoproperties] sqlCommands=transactionCount.sql
[echoproperties] sun.arch.data.model=32
[echoproperties] sun.boot.class.path=C\:\\Program Files\\Java\
\jre1.5.0_06\\lib\\rt.jar;C\:\\Program Files\\Java\\jre1.5.0_06\\lib\
\i18n.jar;C\:\\Program Files\\Java\\jre1.5.0_06\\lib\\sunrsasign.jar;C
\:\\Program Files\\Java\\jre1.5.0_06\\lib\\jsse.jar;C\:\\Progr am Files\
\Java\\jre1.5.0_06\\lib\\jce.jar;C\:\\Program Files\\Java\\jre1.5.0_06\
\lib\\charsets.jar;C\:\\Program Files\\Java\\jre1.5.0_06\\classes
[echoproperties] sun.boot.library.path=C\:\\Program Files\\Java\
\jre1.5.0_06\\bin
[echoproperties] sun.cpu.endian=little
[echoproperties] sun.cpu.isalist=
[echoproperties] sun.desktop=windows
[echoproperties] sun.io.unicode.encoding=UnicodeLittle
[echoproperties] sun.jnu.encoding=Cp1252
[echoproperties] sun.management.compiler=HotSpot Client Compiler
[echoproperties] sun.os.patch.level=Service Pack 2
[echoproperties] today=2007-04-09
[echoproperties] user.country=US
[echoproperties] user.dir=C\:\\eclipse_workspace\\massSQL
[echoproperties] user.home=C\:\\Documents and Settings\\WalkeD
[echoproperties] user.language=en
[echoproperties] user.name=walked2
[echoproperties] user.timezone=
[echoproperties] user.variant=
[echoproperties] userName=********
[echoproperties] userPass=********
query:
[delete] Could not find file C:\eclipse_workspace\massSQL
\dailyTransactions.out.txt to delete.
[exec] Current OS is Windows XP
[exec] Error redirected to C:\eclipse_workspace\massSQL
\dailyTransactionsError.txt
[exec] Executing 'cmd.exe' with arguments:
[exec] '/c db2cmd db2 -tx connect to db0z user ******* using
*******; SET SCHEMA isdb1; Select count (*) from isdb1.SUBTRANSACTION
s, isdb1.subtransactiontype st where s.SUBTRANSACTIONTYPE_SUBTRANSA12
= st.subtransactiontypeid and s.subtransactionstatusid = 2 and
st.SUBTRANSACTIONMODULEID in (1,2,5,7,8,9) and s.createdon between
'2007-04-09 00:01:01.000000' and '2007-04-09 23:59:01.424000';
quit; -z dailyTransactions.out.txt'
[exec]
[exec] The ' characters around the executable and arguments are
[exec] not part of the command.
[sleep] sleeping for 20000 milliseconds
BUILD SUCCESSFUL
Total time: 20 seconds

the contents of the folowing files are being used.
===count.xml===(pass and username have been blanked for posting)
<project name="dailyTransactions" default="query" basedir=".">

<target name="init" description="prep directories and set times
etc...">
<tstamp>
<format property="today" pattern="yyyy-MM-dd" />
</tstamp>
<property name="schema" value="isdb1" />
<property name="database" value="db0z" />
<property name="userName" value="*******" />
<property name="userPass" value="******" />
<property name="outputFile" value="dailyTransactions.out.txt" />
<property name="sqlCommands" value="transactionCount.sql" />
<property name="errorFile" value="dailyTransactionsError.txt" />
<loadfile property="sql" srcFile="transactionCount.sql" >
<filterchain>
<striplinebreaks/>
</filterchain>
<filterchain>
<replacestring from="THESCHEMA" to="${schema}" />
</filterchain>
<filterchain>
<replacestring from="DATABASE" to="${database}" />
</filterchain>
<filterchain>
<replacestring from="THEUSERNAME" to="${userName}" />
</filterchain>
<filterchain>
<replacestring from="THEPASSWORD" to="${userPass}" />
</filterchain>
<filterchain>
<replacestring from="THEDATE" to="${today}" />
</filterchain>
</loadfile>
<echoproperties />
</target>

<target name="query" depends="init" description="execute sql quarrey
">
<delete file="${outputFile}" />
<exec executable="cmd.exe" os="Windows XP" error="${errorFile}"
resultproperty="queryResult" failonerror="false" timeout="180000">
<arg value="/c db2cmd db2 -tx ${sql} -z ${outputFile}" />
</exec>
<sleep seconds="20" />
</target>
</project>

===transactioncount.sql===
connect to DATABASE user THEUSERNAME using THEPASSWORD;

SET SCHEMA THESCHEMA;

Select count (*)
from THESCHEMA.SUBTRANSACTION s, THESCHEMA.subtransactiontype st
where s.SUBTRANSACTIONTYPE_SUBTRANSA12 = st.subtransactiontypeid and
s.subtransactionstatusid = 2 and
st.SUBTRANSACTIONMODULEID in (1,2,5,7,8,9) and
s.createdon between 'THEDATE 00:01:01.000000' and 'THEDATE
23:59:01.424000';

quit;