Export results from stored procedure to local file

Comrade · #1 (**permalink**) 11-09-11, 16:58

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:

Quote:

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.

ARWinner · #2 (**permalink**) 11-09-11, 17:09

It has to be a select statement. You cannot call a stored procedure.

Andy

n_i · #3 (**permalink**) 11-09-11, 17:39

If you rewrite your stored procedure as a table function, you can use it as the source of rows in a SELECT statement.

Comrade · #4 (**permalink**) 11-09-11, 18:53

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.

tonkuma · #5 (**permalink**) 11-09-11, 19:01

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).

Comrade · #6 (**permalink**) 11-09-11, 19:05

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

Internet Services

Web Development

Digital Marketing

Consumer Tech