SEO

#1 (**permalink**) 02-27-2008, 08:38 AM

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

#2 (**permalink**) 02-27-2008, 08:38 AM

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

#3 (**permalink**) 02-27-2008, 08:38 AM

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

#4 (**permalink**) 02-27-2008, 08:38 AM

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

#5 (**permalink**) 02-27-2008, 08:38 AM

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

#6 (**permalink**) 02-27-2008, 08:39 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Variable in SQL-Script