Execute SQL from file in bash

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?

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

Redirect stdin from the file.

db2 < sample.sql

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.