How to keep an Informix database connection open in a shell script?
The poster wanted a shell script that opens an Informix connection once and keeps it open across many statements, as DB2's CLI allows; piping each statement to dbaccess separately reopened and closed the database each time. Replies suggested several approaches: a here-document fed to dbaccess, writing the generated SQL to a .sql file and running dbaccess DB file, grouping echo statements with braces (including semicolons) piped to 'dbaccess - -', or using SQLCMD's sqlserver/sql client-server mode from the IIUG archive. The poster confirmed the brace-grouping pipe into dbaccess did what he needed.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Using dbaccess I am trying to keep a database connection open in a shell script until I want to close it. It may be easiest to explain what I'm trying to do with a simple shell script which works fine for DB2.
db2 -v "connect to $DATABASE"
db2 -v "set current schema dba"
db2 -v "create table test (col1 int not null) in userspace1"
while [ $i -le 10 ]
do
db2 -v "insert into test(col1) values($i)"
(( i=i+1 ))
done
db2 -v "commit work"
db2 -v "connect reset"
db2 -v "terminate"
If I try similar logic with informix, the database is repeatedly opened and closed:
echo "database $DATABASE;" |dbaccess
Database selected.
Database closed.
echo "insert into test(col1) values($i);" | dbaccess -e $DATABASE
Database selected.
insert into test(col1) values(1);1 row(s) inserted.
Database closed.
With Informix's dbaccess, is there a way I can open the database once, perform the inserts, and then close the database, similar to the way I can for DB2?
Thanks.
Try using the sqlcmd suite of tools available from the iiug repository.
Signature
> Date: Wed, 29 Aug 2012 00:05:43 -0700
> Subject: How to keep an Informix database connection open in a shell script?
> From: davies_ms@yahoo.com.au
> To: informix-list@iiug.org
>
> Using dbaccess I am trying to keep a database connection open in a shell script until I want to close it. It may be easiest to explain what I'm trying to do with a simple shell script which works fine for DB2.
>
> db2 -v "connect to $DATABASE"
> db2 -v "set current schema dba"
> db2 -v "create table test (col1 int not null) in userspace1"
On Wednesday, August 29, 2012 3:05:43 AM UTC-4, Desmodromic wrote:
> Using dbaccess I am trying to keep a database connection open in a shell script until I want to close it. It may be easiest to explain what I'm trying to do with a simple shell script which works fine for DB2.
>
>
>
> db2 -v "connect to $DATABASE"
>
> db2 -v "set current schema dba"
>
> db2 -v "create table test (col1 int not null) in userspace1"
>
>
>
> while [ $i -le 10 ]
>
> do
>
> db2 -v "insert into test(col1) values($i)"
>
> (( i=i+1 ))
>
> done
>
>
>
> db2 -v "commit work"
>
> db2 -v "connect reset"
>
> db2 -v "terminate"
>
>
>
>
>
> If I try similar logic with informix, the database is repeatedly opened and closed:
>
>
>
> echo "database $DATABASE;" |dbaccess
>
>
>
> Database selected.
>
>
>
> Database closed.
>
>
>
> echo "insert into test(col1) values($i);" | dbaccess -e $DATABASE
>
>
>
> Database selected.
>
>
>
> insert into test(col1) values(1);>
> 1 row(s) inserted.
>
>
>
> Database closed.
>
>
>
> With Informix's dbaccess, is there a way I can open the database once, perform the inserts, and then close the database, similar to the way I can for DB2?
>
>
>
> Thanks.
In Bash you can use a "Here Document" `man bash' for more info on that.
dbaccess $DATABASE << SQL
create temp table test (col1 integer);
insert into test(col1) values(1);SQL
I'm not sure about using the loop as you did in your example since only SQL statements would be allowed inside the `dbaccess' here document, but you can put multiple SQL lines inside it. The database won't be closed until the end of the here document is reached.
OK, you could do something like:
(
echo "begin work;"
echo "select .... from sometable into temp fred;""
echo "insert into othertable select * from fred;"
...
...
echo "commit work;"
) | dbaccess mydatabase -
However, personally I like the sqlcmd "server mode" better.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Aug 30, 2012 at 10:56 AM, loc <c320sky@gmail.com> wrote:
> On Wednesday, August 29, 2012 3:05:43 AM UTC-4, Desmodromic wrote:
> > Using dbaccess I am trying to keep a database connection open in a shell
> script until I want to close it. It may be easiest to explain what I'm
> trying to do with a simple shell script which works fine for DB2.
> >
> >
> >
> > db2 -v "connect to $DATABASE"
> >
> > db2 -v "set current schema dba"
> >
> > db2 -v "create table test (col1 int not null) in userspace1"
> >
> >
> >
> > while [ $i -le 10 ]
> >
> > do
> >
> > db2 -v "insert into test(col1) values($i)"
> >
> > (( i=i+1 ))
> >
> > done
> >
> >
> >
> > db2 -v "commit work"
> >
> > db2 -v "connect reset"
> >
> > db2 -v "terminate"
> >
> >
> >
> >
> >
> > If I try similar logic with informix, the database is repeatedly opened
> and closed:
> >
> >
> >
> > echo "database $DATABASE;" |dbaccess
> >
> >
> >
> > Database selected.
> >
> >
> >
> > Database closed.
> >
> >
> >
> > echo "insert into test(col1) values($i);" | dbaccess -e $DATABASE
> >
> >
> >
> > Database selected.
> >
> >
> >
> > insert into test(col1) values(1);> >
> > 1 row(s) inserted.
> >
> >
> >
> > Database closed.
> >
> >
> >
> > With Informix's dbaccess, is there a way I can open the database once,
> perform the inserts, and then close the database, similar to the way I can
> for DB2?
> >
> >
> >
> > Thanks.
>
> In Bash you can use a "Here Document" `man bash' for more info on that.
>
> dbaccess $DATABASE << SQL
> create temp table test (col1 integer);
> insert into test(col1) values(1);> SQL
>
> I'm not sure about using the loop as you did in your example since only
> SQL statements would be allowed inside the `dbaccess' here document, but
> you can put multiple SQL lines inside it. The database won't be closed
> until the end of the here document is reached.
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
On Wed, Aug 29, 2012 at 12:05 AM, Desmodromic <davies_ms@yahoo.com.au>wrote:
> Using dbaccess I am trying to keep a database connection open in a shell
> script until I want to close it. It may be easiest to explain what I'm
> trying to do with a simple shell script which works fine for DB2.
>
> db2 -v "connect to $DATABASE"
> db2 -v "set current schema dba"
> db2 -v "create table test (col1 int not null) in userspace1"
>
> while [ $i -le 10 ]
> do
> db2 -v "insert into test(col1) values($i)"
> (( i=i+1 ))
> done
>
> db2 -v "commit work"
> db2 -v "connect reset"
> db2 -v "terminate"
>
>
> If I try similar logic with informix, the database is repeatedly opened
> and closed: [...]
>
There are several options:
{
echo "connect to $DATABASE;"
echo "set current schema dba;" # Not in Informix
echo "create table test (col1 int not null) in userspace1;"
while [ $i -le 10 ]
do
echo "insert into test(col1) values($i);"
(( i=i+1 ))
done
echo "commit work;"
echo "connect reset;" # Not in Informix
echo "terminate;" # Not in Informix
} | dbaccess - -
Note the semi-colons; they are critical.
The commands annotated 'Not in Informix' are simply not a part of the
Informix command set. But you can substitute appropriate other commands.
Alternatively, the SQLCMD program available from the IIUG archive has a
pair of scripts, sqlserver and sqlclient (normally abbreviated to just
'sql') that can be used:
sqlserver -d dbase@server
sql "begin work"
sql "create table test (col1 int not null) in userspace1"
while [ $i -le 10 ]
do
sql "insert into test(col1) values($i)"
(( i=i+1 ))
done
sql "commit work"
sql "connect reset" # Not in Informix
sql "exit" # Or 'quit' or 'bye', or 'q', or 'b', or 'e'; but not
'terminate' (6 alternatives is too many already).
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
You can echo and append your sql instructions in an sql file and run it using
dbaccess $DATABASE $SQL_FILE
Cheers
HM
Le 29 août 2012 à 08:05, Desmodromic <davies_ms@yahoo.com.au> a écrit :
> Using dbaccess I am trying to keep a database connection open in a shell script until I want to close it. It may be easiest to explain what I'm trying to do with a simple shell script which works fine for DB2.
>
> db2 -v "connect to $DATABASE"
> db2 -v "set current schema dba"
> db2 -v "create table test (col1 int not null) in userspace1"
>
> while [ $i -le 10 ]
> do
> db2 -v "insert into test(col1) values($i)"
> (( i=i+1 ))
> done
>
> db2 -v "commit work"
> db2 -v "connect reset"
> db2 -v "terminate"
>
>
> If I try similar logic with informix, the database is repeatedly opened and closed:
>
> echo "database $DATABASE;" |dbaccess
>
> Database selected.
>
> Database closed.
>
> echo "insert into test(col1) values($i);" | dbaccess -e $DATABASE
>
> Database selected.
>
> insert into test(col1) values(1);> 1 row(s) inserted.
>
> Database closed.
>
> With Informix's dbaccess, is there a way I can open the database once, perform the inserts, and then close the database, similar to the way I can for DB2?
>
> Thanks.
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
On Friday, August 31, 2012 1:50:40 AM UTC+10, Jonathan Leffler wrote:
> On Wed, Aug 29, 2012 at 12:05 AM, Desmodromic <davi...@yahoo.com.au> wrote:
>
>
>
> Using dbaccess I am trying to keep a database connection open in a shell script until I want to close it. It may be easiest to explain what I'm trying to do with a simple shell script which works fine for DB2.
>
>
>
> db2 -v "connect to $DATABASE"
>
> db2 -v "set current schema dba"
>
> db2 -v "create table test (col1 int not null) in userspace1"
>
>
>
> while [ $i -le 10 ]
>
> do
>
> db2 -v "insert into test(col1) values($i)"
>
> (( i=i+1 ))
>
> done
>
>
>
> db2 -v "commit work"
>
> db2 -v "connect reset"
>
> db2 -v "terminate"
>
>
>
>
>
> If I try similar logic with informix, the database is repeatedly opened and closed: [...]
>
>
> There are several options:
>
> {
> echo "connect to $DATABASE;"
>
> echo "set current schema dba;" # Not in Informix
>
> echo "create table test (col1 int not null) in userspace1;"
>
>
>
> while [ $i -le 10 ]
>
> do
>
> echo "insert into test(col1) values($i);"
>
> (( i=i+1 ))
>
> done
>
>
>
> echo "commit work;"
>
> echo "connect reset;" # Not in Informix
>
> echo "terminate;" # Not in Informix
> } | dbaccess - -
>
>
> Note the semi-colons; they are critical.
>
> The commands annotated 'Not in Informix' are simply not a part of the Informix command set. But you can substitute appropriate other commands.
>
>
>
>
> Alternatively, the SQLCMD program available from the IIUG archive has a pair of scripts, sqlserver and sqlclient (normally abbreviated to just 'sql') that can be used:
>
> sqlserver -d dbase@server
>
>
>
> sql "begin work"
>
> sql "create table test (col1 int not null) in userspace1"
>
>
>
> while [ $i -le 10 ]
>
> do
>
> sql "insert into test(col1) values($i)"
>
> (( i=i+1 ))
>
> done
>
>
>
> sql "commit work"
>
> sql "connect reset" # Not in Informix
>
> sql "exit" # Or 'quit' or 'bye', or 'q', or 'b', or 'e'; but not 'terminate' (6 alternatives is too many already).
>
>
> --
> Jonathan Leffler <jonathan...@gmail.com> #include <disclaimer.h>
>
>
>
> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
> "Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
Thanks Jonathan, that brace-grouping technique with dbaccess did exactly what I wanted.