Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Posts
    20

    Unanswered: Runs script via cron

    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

  2. #2
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    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

  3. #3
    Join Date
    Apr 2003
    Posts
    20
    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?

  4. #4
    Join Date
    Dec 2002
    Location
    Portland, OR, USA
    Posts
    26

    Smile More environmental info might help...

    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

  5. #5
    Join Date
    Apr 2003
    Posts
    20

    Re: More environmental info might help...

    Originally posted by astrue
    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.

  6. #6
    Join Date
    Dec 2002
    Location
    Portland, OR, USA
    Posts
    26

    OK, here goes...

    (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

  7. #7
    Join Date
    Apr 2003
    Posts
    20

    Re: OK, here goes...

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •