470,636 Members | 1,566 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,636 developers. It's quick & easy.

Variable in SQL-Script

Hi,
I have an SQL Script (test.sql) which I have to call via: su -root -c
"db2 test.sql".
Now I need a variable in the SQL-Script which is stored i a different
propertie file.
How could I to something like that?

Thank you...
Stefan

Nov 9 '06 #1
5 6483
Stefan wrote:
Hi,
I have an SQL Script (test.sql) which I have to call via: su -root -c
"db2 test.sql".
Now I need a variable in the SQL-Script which is stored i a different
propertie file.
How could I to something like that?
Two approaches come up:
(1) Use a UDF or SP to access the other file and extract the value in
question.
(2) Make your "test.sql" a regular shell script that invokes DB2 itself.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 9 '06 #2
Hi Knut,
thank you for your answer.
I'm a db2 beginner, so I have some problems with your answer.
My script is called like this:
su -root -c "db2 -tvf test.sql"
In the SQL-Script test.sql are about 10 sql-statements.

I think (2) sounds quit easy, but do you have a small example?

Thank's a lot!!!

Knut Stolze schrieb:
Stefan wrote:
Hi,
I have an SQL Script (test.sql) which I have to call via: su -root -c
"db2 test.sql".
Now I need a variable in the SQL-Script which is stored i a different
propertie file.
How could I to something like that?

Two approaches come up:
(1) Use a UDF or SP to access the other file and extract the value in
question.
(2) Make your "test.sql" a regular shell script that invokes DB2 itself.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 9 '06 #3
Stefan wrote:
Hi Knut,
thank you for your answer.
I'm a db2 beginner, so I have some problems with your answer.
My script is called like this:
su -root -c "db2 -tvf test.sql"
In the SQL-Script test.sql are about 10 sql-statements.

I think (2) sounds quit easy, but do you have a small example?
I would do this:

su -root -c "my_db2_script"

The content of "my_db2_script" would be:
------------------snip--------------------------
#!/bin/sh

VAR1=...
VAR2=...

db2 "connect to database"
db2 "first sql statement using ${VAR1}"
db2 "second sql statement"
db2 "another sql statement using ${VAR2}"
db2 "connect reset"

------------------snip--------------------------

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 9 '06 #4
Stefan wrote:
Hi,
I have an SQL Script (test.sql) which I have to call via: su -root -c
"db2 test.sql".
Now I need a variable in the SQL-Script which is stored i a different
propertie file.
How could I to something like that?

Thank you...
Stefan
I will advise against using root account for db2 access and/or managing. It is
safer to use normal account for db2 accessing and processing - or instance owner
account to manage db2.

Jan M. Nelken
Nov 9 '06 #5

Stefan wrote:
Hi,
I have an SQL Script (test.sql) which I have to call via: su -root -c
"db2 test.sql".
Now I need a variable in the SQL-Script which is stored i a different
propertie file.
How could I to something like that?
Depending on the complexity of the sql I either put the sql inside a
shell script, or use sed to replace the variable in the sql file.
Example

[lelle@53dbd181 lelle]$ cat template.sql
select * from account where accound_id = '@@account_id@@';

[lelle@53dbd181 lelle]$ sed -e "s/@@account_id@@/whatever/g" <
template.sql /tmp/worker.sql
[lelle@53dbd181 lelle]$ db2 -tf /tmp/worker.sql

HTH
/Lennart

Thank you...
Stefan
Nov 13 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by tim.pascoe | last post: by
1 post views Thread by gregbacchus | last post: by
3 posts views Thread by claus.hirth | last post: by
1 post views Thread by Korara | last post: by
???
reply views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.