Execute SQL from file in bash

Question

I'm trying to load a sql from a file in bash and execute the loaded sql. The sql file needs to be versatile, meaning it cannot be altered in order to make things easy while being run in bash (escaping special characters like * ) So I have run into some problems:

If I read my sample.sql

SELECT * FROM SAMPLETABLE

to a variable with

ab=`cat sample.sql`

and execute it

db2 `echo $ab`

I receive an sql error because by doing a cat the * has been replaced by all the files in the directory of sample.sql.

Easy solution would be to replace "" with "\" . But I cannot do this, because the file needs to stay executable in programs like DB Visualizer etc.

Could someone give me hint in the right direction?

* is getting expanded because you're not quoting your variable: $(echo "$ab"). But use one of the answers below instead. — glenn jackman
– glenn jackman, Commented Apr 26, 2011 at 15:10

Fred Sobotka · Accepted Answer · 2011-04-26 14:47:28Z

3

The DB2 command line processor has options that accept a filename as input, so you shouldn't need to load statements from a text file into a shell variable.

This command will execute all SQL statements in the file, with newline treated as the statement terminator:

db2 -f sample.sql

This command will execute all SQL statements in the file, with semicolon treated as the statement terminator:

db2 -t -f sample.sql

Other useful CLP flags are:

-x : Suppress the column headings
-v : Echo the statement text immediately before execution
-z : Tee a copy of all CLP output to the filename immediately following this flag

answered Apr 26, 2011 at 14:47

Fred Sobotka

5,33225 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ignacio Vazquez-Abrams · Accepted Answer · 2011-04-26 08:30:10Z

2

Redirect stdin from the file.

db2 < sample.sql

answered Apr 26, 2011 at 8:30

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Comments

eeerahul · Accepted Answer · 2011-11-21 11:43:03Z

2

In case, you have a variable used in your script and wanted to get it replaced by the shell before executed in DB2 then use this approach:

Contents of File.sql:

cat <<xEOF
insert values(1,2) into ${MY_SCHEMA}.${MY_TABLE};
select * from ${MY_SCHEMA}.${MY_TABLE};
xEOF

In command prompt do:

export MY_SCHEMA='STAR'
export MY_TAVLE='DIMENSION'

Then you are all good to get it executed in DB2:

eval File.sq |db2 +p -t

The shell will replace the global variables and then DB2 will execute it. Hope it helps.

edited Nov 21, 2011 at 11:43

eeerahul

1,6254 gold badges27 silver badges41 bronze badges

answered Oct 26, 2011 at 19:05

Neil

211 bronze badge

Collectives™ on Stack Overflow

Execute SQL from file in bash

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related