SQL to Out File

[DB2Problem]

Hi,

I need the output from a DROP Statement in a file. I am running DB2 from Windows Environment.

Also i need to check the return code (if any) of the DROP Statement for success/failure of DROP without opening the out file.

Appreciate your help

 

[gregsimpson]

The following contains a script which puts output into a logfile.

thread178-258682

 

[DB2Problem]

Hi,

Thanks for the reference link, but i am intersted in getting the result of DROP Statement in a file, without creating a separate batch program.

Is there anyway i can specify from the CLP on db2 prompt, any feature which output's and re-directs the result to oufile

Appreciate your suggestion

 

[gregsimpson]

Does this help.

Create a file called MYFILE containing

SELECT * FROM SYSIBM.SYSDUMMY1


run it through DB2 putting output into your OUTFILE

db2 <MYFILE >OUTFILE

 

[DB2Problem]

Thanks again for your suggestion. But Unfortunately it did not work.

I saved the SQL command in a file and run it after connecting to the database from CLP.

db2> db2 <MYFILE >OUTFILE

MYFILE.TXT contains a simple test SQL
SELECT * FROM MYSCHEMA.MYTABLE

At the CLP Prompt it gives following error message

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token &quot;END-OF-STATEMENT&quot; was found following &quot;MYFILE.TXT&quot;. Expected tokens may include: &quot;JOIN <joined_table>&quot;.
SQLSTATE=42601

 

[gregsimpson]

Sorry it doesn't work for you. I am actually using AIX and it works fine on there. The only thing I can see with the example you have posted is that you already seem to be in DB2 when you are running the file, so entering DB2 again would give you an error.

The way I did it was I issued a DB2 &quot;CONNECT TO DATABASE...&quot; from my operating system command prompt.

Then I simpy type in the following

db2 <MYFILE >OUTFILE


I didn't go into DB2 first. Going from your example you already appear to be inside db2. Sorry I can't help further but I don't have much recent experience on the Windows versions.

 

[lydiap]

What about putting your sql in a file and
running the file as such:
db2 -tvf filename.sql -r results.txt >>logname.txt


If this is on Windows, it can be put into a batch file..let me know if you need
help with this.

 

[Raanon]

Here are two ways that you can check the return code of a db2 command from the dos prompt or in a script:

(1)

db2 drop table kuku
if not %errorlevel% == 0 .....

(2) This works on my Windows2000 platform, but I don't know about other Windows platforms. This was run from the command prompt (all on one line).

for /f %r in ('db2 -o- -ec drop table kuku') do @if &quot;%r&quot; == &quot;-204&quot; echo TABLE NOT FOUND

Note:
-o- Suppresses any output
-ec Returns the SQLCODE