Shell Script to execute DB2 Stored Procedure

[horserider]

I have a DB2 Stored Procedure that takes input parameter and returns me result set (5 columns ) from cursor. I am able to connect to DB2 Server from my UNIX BOX and run the stored procedure and I get the resultset on my screen. Below is what I do:-

source /mycomp/thisproj/proj/Ascential/DataStage/DSEngine/dsenv
~db2inst1/sqllib/bin/db2
Connect to db2dsn user DB2SPUSER using pass@#45;
CALL SYSPROC.MYSTPROC(?,?,?,?,'U');

I want to write a SHELL SCRIPT that I want to execute and it should save the output data into a TEXT file on my UNIX Server

(1) How do I write a SHELL SCRIPT to do the above steps.
(2) How do I save all my output data into a TEXT file that I will read in my
next job

[AmeyJoshi14]

Hi,
We have implemented the same thing...but our database is Oracle.
Following is the sample script which we use to connect to Oracle and which will save the output in .txt file.

Code: Select all

cat 1.sh

sqlplus username/password@sid <<EOF
spool 1.txt
select sysdate from dual;
exit;
spool off
EOF

The output of the file is :

Code: Select all

cat 1.txt
SQL> select sysdate from dual;

SYSDATE 
--------------- 
14-MAY-08 

SQL> exit;

The file 1.txt can be edited as per the requirement.I hope you will get some idea ...