Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Unanswered: Export results from stored procedure to local file

    Hello,

    I'm trying to export the result set from a DB2 stored procedure to a local file.

    I've figured out how to do the same for a select statement using the following code:

    Code:
    export to C:\Users\Comrade\Documents\output.csv \
    of del modified by nochardel \
    select * from someschema.sometable
    However, I'm not able to modify the same for a stored procedure. The following code

    Code:
    export to C:\Users\Comrade\Documents\output.csv \
    of del modified by nochardel \
    call someschema.somestoredproc
    gives the following error:

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following
    "<identifier>". Expected tokens may include: "SELECT". SQLSTATE=42601
    There are a few threads in which a similar question arises but I didn't find a satisfactory answer in either.

    Please let me know if you have any ideas.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It has to be a select statement. You cannot call a stored procedure.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you rewrite your stored procedure as a table function, you can use it as the source of rows in a SELECT statement.

  4. #4
    Join Date
    Nov 2011
    Posts
    5
    Thanks, Andy. At least I won't go on a wild goose chase.

    n_i: Unfortunately, I haven't written that stored procedure and am not in a position to modify it.

    I'm trying to find an alternate way. Connecting to DB2 from within is easy using the IBM_DB module provided by ruby. If I can get call the procedure and fetch the rows from within the script, I can then write it to file easily.

    UPDATE: Yeah, it works. If someone is looking to do the above, it might be worth your time to invest a little in interfacing your favorite scripting language to DB2.
    Last edited by Comrade; 11-09-11 at 20:02.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I saw many stored procedures which could be written by a single SQL statement.

    I want to recomend you to review the stored procedure to rewrite as an SQL statement or a table function(as n_i wrote).

  6. #6
    Join Date
    Nov 2011
    Posts
    5
    tonkuma: The procedure is not written by me. It is stored somewhere and cannot be modified. In any case, it is too complicated for me to understand and modify it

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
  •