Variable in SQL-Script

[Stefan]

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

[Knut Stolze]

Re: Variable in SQL-Script

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

[Stefan]

Re: Variable in SQL-Script

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

[Knut Stolze]

Re: Variable in SQL-Script

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_scr ipt"

The content of "my_db2_scr ipt" 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

[Jan M. Nelken]

Re: Variable in SQL-Script

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

[Lennart]

Re: Variable in SQL-Script


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