I have a perl script that I am using to generate a file with all the sql statements from an Informix database. Currently I am manually ftping this file over to the Informix server and then manually running this file via dbaccess.

Here is my question:

Can someone assist me, possibly with some examples, as to how I can automate this. That is automate running a script with Informix SQL commands via a cron. Please note that I would like to run this file all at once. Usually it takes me five minutes to run that script manually.

Any help would be greatly appreciated.

islamabadi

I dont know if this is what you need, but to run a sql commands through dbacces in a script you could do the following:

#!/bin/ksh

#SET ENVIRONMENT
. ./ids.ksh
# Could also set INFORMIXDIR/PATH/INFORMIXSERVER manually but it
# is wise to use a script for this you can re-use

DBNAME=sysmaster
TMPFILE="/tmp/rootname.tmp"

dbaccess ${DBNAME} > /dev/null 2>&1 <<EOF
UNLOAD TO "${TMPFILE}" DELIMITER "|"
SELECT *
FROM sysconfig
WHERE cf_name = "ROOTNAME"
EOF

cat ${TMPFILE} | awk ### Process the output for example

rm ${TMPFILE}

#####################

I don't know if this is what you need, but maybe it helps.


Rob Prop

Thanks for the reply. Actually what I am looking for is a bit different. Lte me explain that.

I have a script with all the commands (insert, update, delete) in a text file.

I am at a server different than the informix database server.

Question is: How can I run this file with the commands against the database on a server different than the informix database server. In other words how can I automate the following manual steps I am performing:

1) ftp file over to informix db server
2) telnet to the informix db server
3) launch dbaccess
4) select choose and then Run file that was ftp's in 1)

Any clues?

I might be able to offer some guidance, but it would sure help to know which OS is running on these servers, and which version of Informix you are using... if you would care to share that info, it might help you get useful posts faster.

Regards,
Joe

Here is the information you requested.

The server where my file with sql commands is: SunOS (Solaris) 5.6
The server for informix database: SunOS (Solaris) 5.8
Informix database: DB-Access Version 9.21.UC1

I hope this helps you assist me.

(I am hoping that other folks will jump in with help as well, especially in areas where I am not doing a great job in helping you...)

It still really depends on several things, such as which version of the shell you are using, Bourne(bash), Korn(ksh), or C(csh). Since I'm doing mostly bash stuff, I'll use that in my examples, and if you're in a different world, you'll have to translate, OK?

Another thing it depends on is whether you have root access to these servers. Without it, you may not get as far.

Finally, an important thing to know is whether these servers have a trusted host relationship, but once again, that is not necessarily an obstacle, it just makes the process take more steps if they don't.

You want to copy files over from Server A to Server D (database server), citing ftp as the example. That's fine as long as Server D is running an ftp daemon that allows you to post files to the directory path you need, and a certain amount of ftp access can be scripted so you don't have to babysit it. Another option to consider, though is scp, which is a secure (SSL) version of the "regular" cp command for copying stuff. An example might look like this:

[root@plato /]# scp /tmp/myfilename.sql root@dbserver:/opt/informix/dbpath

In this case I am assuming that target directory is the DBPATH on the Informix host. If there is a trusted host setup (administratively risky, but for closed networks, it can be ok) the scp command above won't even require a password. If you don't have root access on dbserver, then substitute your ID on dbserver for root as the target of the scp command, but keep in mind that it will only allow you to put the file(s) in a directory path that you have permissions to... this can be done with a series of files or a filename mask just like a cp, so you don't necessarily have to issue a separate copy command for each file to port to the other box.

That takes care of step 1.

Steps 2 through 4 all occur over on dbserver. While I do not know what ID you plan to log on there with, it likely falls into one of the following:
(a) root
(b) informix
(c) some other defined user that has some database access granted to it
(d) something else that just won't work

Assuming it's not (d), let's move forward. Depending on the trusted hosts/security model on your network, you could theoretically script the whole thing, log onto dbserver with an ssh command, execute your script and leave, but that would likely require you to be root as well.

Once you are logged onto the dbserver, either manually or via script, you can easily execute dbaccess with your SQL. Once again, it kind of depends whether the database server is hosting more than one database at a time, which I cannot tell.

A simple form, if your're logged on dbserver, it only hosts one database, you have rights to the database, and the file is located in $DBPATH, looks like this:

cd $DBPATH;dbaccess - filename.sql

A slightly fancier version that could be run as root, runs dbaccess, and the SQL, as informix(rights to everything):

su -c 'cd ${DBPATH};${INFORMIXDIR}/bin/dbaccess - filename.sql' informix

This should be done with care, and it depends on what kind of table updates you are doing.

Let me know if this helps- I am by no means a Linux or Informix guru, but I've been doing a lot of both for almost two years now, so I like to think I'm getting better at it.

Regards,
Joe

Simply Awesome........Problem solved.

Thanks for being so descriptive.

The only change I had to make is use the following format of the command since I have more than one database on the server:

dbaccess database filename.sql

Cheers!