How to invoke another SQL file from a SQL script? 1

[nshen]

Hi! With Oracle, I can use @mysql.sql to invoke another sql script from current sql script. How do I do that on DB2? Thanks in advance!
Regards, Nancy

 

[Dagon]

db2 -t -f <file_name>

But it is very limited. You can't pass parameters the way you can with SQL*Plus.

 

[Dagon]

Sorry, slight misunderstanding there. You wanted to call a SQL script from within a SQL script. I don't think you can do this. As I said, DB2 CLI isn't like SQL*Plus - it has hardly any programming capabilities at all.

 

[sathyarams]

Never tried, may be you can try something like

create table t1(i int)

!db2 &quot;select * from t1&quot;

drop table t1


HTH

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[nshen]

Dagon,
I totally agreed with you.
Now, is there a way to do select and base on the select result to invoke a DB2 SQL script from command line?
Any scripting language such as REXX or DOS command that will allow such a thing?
What I want to achieve is this:
-- pseude code...
DB2 CONNECT TO $mydb USER $user1 USING $pw1
DB2 SELECT col1 INTO $var1 FROM tab1 WHERE col2 = $user1
if [[-z $var1]]; then
myscript1.sh
fi

Thanks!
Regards, Nancy

 

[Dagon]

As I said, DB2 isn't a scripting language. The only way you are going to able to do something like that is to wrap a shell script around it or perhaps use a stored procedure instead.

 

[Dagon]

If it's of any help, you can use shell scripts as a way of getting parameters into db2. I use stuff like:

db2_cmd=&quot;delete from table where column = '$1'&quot;
db2 -s $db2_cmd
if [ &quot;$?&quot; -gt 2 ]
then
exit 4
fi

As for getting data back from db2, the only option I can think of would be to write it out to a file by capturing the output e.g.

db2 -t -f script.sql > x.x

I've not found any way of suppressing the headings and other stuff, so your best bet might be to preface the output with some sort of string so that you can locate it easily e.g.

DB2 SELECT 'QUERY RESULT: '||col1 INTO $var1 FROM tab1 WHERE col2 = $user1

You can then grep the output e.g.

db2 -t -f script.sql | grep &quot;QUERY RESULT&quot; | cut -d: -f2 > /tmp/file.dat
result=`cat /tmp/file.dat`

 

[nshen]

Thank you very much, Dagon!
Regards, Nancy