Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Unanswered: DB21004E - Using Bash,DB2 and output to text file

    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

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    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.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You may want to change your script to:
    Code:
    ... db2 -tvf "$f" > "/tmp/$f.txt"; done
    Note the double-quotes around $f. That way, the shell will pass the value in $f as a single argument to the program.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jul 2007
    Posts
    2
    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

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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:
    Code:
    for f in *.sql; do db2 -tvf $f > /tmp/$f.txt; done
    The "for" loop will only work if you do not have spaces in the file names, of course.

    Alternatively:
    Code:
    find . -name "*.sql" -exec db2 -tvf "{}" > "/tmp/{}.txt" \;
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •