Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36

    Unanswered: Calling UDF (that makes system calls) from a Stored Procedure

    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
    Last edited by marc_; 08-17-11 at 11:37.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36

    Cool

    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by marc_ View Post
    SET i=(VALUES systemCall(commandtext));
    Why not simply
    Code:
    SET i=systemCall(commandtext);

  5. #5
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36

    Thumbs up

    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!

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you might compare VALUES to the SQL Server's SELECT without the FROM clause.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CREATE PROCEDURE runSHCommand(IN commandtext VARCHAR(2000))
    BEGIN ATOMIC
    DECLARE i INT DEFAULT 0;
    SET i=(VALUES systemCall(commandtext));
    END
    Why not simply
    Code:
    CREATE PROCEDURE runSHCommand(IN commandtext VARCHAR(2000))
    RETURN systemCall(commandtext)
    ;
    It is not necessary to change statement termination character.

  8. #8
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36

    Thumbs up

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

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




    Ahh penny finally drops.

Tags for this Thread

Posting Permissions

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