Hi

I have created about 50 SQL statements to run against a DB2 database. Each SQL query is contained in a file for example XML-name.sql

I have written a bash script to cycle through each of the files and execute them against the database. Here is the shell script.

#!/bin/sh
find . -name "*.sql" | while read f; do db2 -tvf $f > /tmp/$f.txt; done

The script executes and creates the files. However each of the output files contains the following error.

"DB21004E You cannot specify both an input file and a command when invoking the Command Line Processor".

I have only been using DB2 for about a week so im a total newbie when it comes to it. It seems crazy to me that I cant use a statement thats contained in a file and write the results to a different text file.

Is there any workaround to this issue?

Thanks

Eamo

I have tried and do not have problems
there must be a file where the name contains a space and you get something like
db2 -tvf xx xx.sql > outfile
check the filenames you execute
sample :
db2 -tvf xx xx.sql
DB21004E You cannot specify both an input file and a command when invoking
the Command Line Processor.

You may want to change your script to:
Note the double-quotes around $f. That way, the shell will pass the value in $f as a single argument to the program.

thanks for the input so far.

My shell script currently looks like this:

#!/bin/sh
db2 connect to nameofdb
#db2 -tvf XML53300.sql > /tmp/XML53300.txt
find . -name "*.sql" | while read f; do db2 -tvf "$f" > "/tmp/$f.txt"; done

As you can see I have a line commented out. I used this as a test because when using the other line it was throwing a error that no connection to the database was present?

Its strange because the connect command is there at the top and works fine when used in conjunction with commented out line but fails when using the other line.

Any suggestions?

Cheers

Eamo

My guess is that the pipe will cause a new subshell to be started. This subshell won't inherit the connection of the outer shell. Therefore, DB2 complains about a missing connection. How about this:
The "for" loop will only work if you do not have spaces in the file names, of course.

Alternatively:
But I believe that the -exec option of "find" will also start a subshell and, thus, would need a new connection.

Yet another option is to do a connect/connect reset in each iteration of the loop or inside your SQL scripts.