Hello,

I'm fairly new to DB2 and mainly have been using SQL Server and Oracle over the last 15 years.

The Problem
------------
I need to access system scripts/command and files from within a stred procedure. In SQL Server I'd just use xp_cmdshell.

For example:
exec master..xp_cmdshell 'echo "hello world" > c:\hello.txt'

This would create a file on the C drive root dir.

Now I've been searching fro sometime for a solution and I found stolze's excellent article:
Making Operating System Calls from SQL

And I'm almost there! Now the final UDF mentioned in the article is just what I need: systemCall( command VARCHAR(2000) ). This UDF in turn executes a C program (complied and copied to ~sqllib/functions dir).

Now I can execute the UDF from the CLP and it works perfectly. I can also execute this from IBM Data Studio without an issue.

*****************db2inst1@MegaServ:~/sql$ db2 "VALUES systemCall('echo HelloWorld > /home/db2inst1/hw.txt')"

1
-----------
-1

1 record(s) selected.

db2inst1@MegaServ:~/sql$ ls -lrt /home/db2inst1/hw.txt
-rw-r--r-- 1 db2inst1 db2iadm1 11 2011-08-12 19:15 /home/db2inst1/hw.txt
*****************

BUT! As soon as I try to wrap it up within a Stored Procedure it bombs out:

db2inst1@MegaServ:~/sql$ db2 -td@ -vf runSHCommand.sql
CREATE PROCEDURE runSHCommand(IN commandtext VARCHAR(2000))
BEGIN ATOMIC
VALUES systemCall('echo HelloWorld > /home/db2inst1/hw.txt');
END
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 ")" was found following "ome/db2inst1/hw.txt'".
Expected tokens may include: ")". LINE NUMBER=3. SQLSTATE=42601


Are there any DB2 golden rules that I'm missing?

Environment:
DB2 V9.7.2 on Linux (Ubuntu Server 10.04)
Using CLP (Linux) and IBM Data Studio (Windows XP)

Many thanks,
Marc

The VALUES statement essentially creates a result set, which must be consumed somehow, either by assigning values to local variables (SELECT ... INTO ...) or by declaring a cursor and returning it to the client.

If you just need to call a function, you could assign its result to a local variable with a SET statement.

So if I ...

db2inst2@Adventi2:~/sql$ db2 -td@ -vf runSHCommand.sql
CREATE PROCEDURE runSHCommand(IN commandtext VARCHAR(2000))
BEGIN ATOMIC
DECLARE i INT DEFAULT 0;
SET i=(VALUES systemCall(commandtext));
END
DB20000I The SQL command completed successfully.

db2inst2@Adventi2:~/sql$ db2 "call runSHCommand('echo \"Hello World\" > /home/db2inst2/hw.txt')"

Return Status = 0
db2inst2@Adventi2:~/sql$ ls -l /home/db2inst2/hw.txt
-rw-r--r-- 1 db2inst2 db2iadm1 12 2011-08-12 22:30 /home/db2inst2/hw.txt
db2inst2@Adventi2:~/sql$ cat ../hw.txt
Hello World

And we have a winner!

Thank you very much n_i. This one's been bugging me for a few days.

Why not simply

Even better - didn't realise that you could completely omit the VALUES clause (anyways it's totally alien to me coming from SQL Server - except when using inserts).

Thanks again!

I think you might compare VALUES to the SQL Server's SELECT without the FROM clause.

Why not simply
It is not necessary to change statement termination character.

db2 => VALUES (2+2, CURRENT TIMESTAMP)

1 2
----------- --------------------------
4 2011-08-13-00.06.31.423446




Ahh penny finally drops.