Re: croning updates

Subject: Re: cron and isql In-Reply-To: <hqcso0171611480> On Tue, 17 Jan 1995 johnson@hqcso.attmail.com wrote: > Hi! > We are successful running loads and ACE reports in cron, but are > not able to do isql updates in cron. We were told that this is not > supported. Is this true? Anyone have any suggestions? We regularly cron updates with a script that sets up the environment and does dbaccess db_name sqlscript_name I have included one of our more elaborate scripts the runs a bunch of stored procedures to import production updates (a copy of the day's work done on the legacy system) into our warehouse. I shortened the lengthy list of files, and removed the part that mails the reports, but the core of the system is there. The legacy system has a job that creates a set of update logs and ftp's them to the warehouse. Each site creates a directory $UPDATE/WCsiteid or $UPDATE/WDsiteid. This script reads these directories to update the warehouse at 11 each night. #-------------------------------------------------------------------- # load # # Description # This file contains a script designed to load information # from the Cobol systems into an Informix warehouse database. # These files are expected to be uploaded to the # $UPLOAD # directory of this machine. # # Basically, this is a three step process. First, we load the # file contents into a series of holding tables in the # whse database. # Next, we invoke a series of stored procedures # designed to unload these holding tables into the warehouse # database. # Finally, there is sometimes work to be done bye 4GL programs, # usually stuff that SPLs don't do well (Truncation, soundex, etc). #-------------------------------------------------------------------- #!/bin/sh # this will be run from cron, so set up environment. Set up EVERYTHING. INFORMIXDIR=/usr/informix export INFORMIXDIRSQLEXEC=$INFORMIXDIR/lib/sqlturbo export SQLEXEC DBTEMP=/tmp export DBTEMP PATH=$INFORMIXDIR/bin:/bin/posix:/bin:/usr/bin:/usr/contrib/bin:/usr/local/binexport PATH DBNAME=whse export DBNAME UPLOAD=/users/upload export UPLOAD DBDATE=Y2MD0 export DBDATE INFORMIXSERVER=whse export INFORMIXSERVERSERVER=$INFORMIXSERVER export SERVER TZ=MST7MDT export TZ TBCONFIG=tbconfig.whse export TBCONFIG # # Create our log file names. Delete any old log files with the # same name. # INFO_FILE=${UPLOAD}/`date +%a_%m%d%y`.info ERR_FILE=${UPLOAD}/`date +%a_%m%d%y`.err load_file() { # # Keep a size record of the files we process. # if [ ${FILE} != "CLEAN_UP" ] then # # Use dbload to load the file into the holding table # msg="`date +%H:%M:%S` Loading ${FILE}" echo $msg >> $ERR_FILE echo $msg >> $INFO_FILE dbload -d $DBNAME \\ -c ${UPLOAD}/support/${FILE}.com \\ -l ${UPLOAD}/${FILE}.tmp_bad \\ >> $ERR_FILE 1>/dev/null 2>&1 fi # [ ${FILE} != "CLEAN_UP" ] # # Execute any stored procedures needed to migrate data from the # holding table. # PROCS="" EXES="" case ${FILE} in "HEARCODE" ) PROCS="wsp_hearcode" ;; "CASECIV" ) PROCS="fsp_caseciv wsp_caseciv" ;; "CASECRMC" ) PROCS="fsp_casecrmc wsp_casecrmc" ;; <snip> "CLEAN_UP" ) PROCS="clean_up" ;; esac for PROC in ${PROCS} do msg="`date +%H:%M:%S` Executing Procedure ${PROC}" echo $msg >> $INFO_FILE # here is the key line where it executes the stored procedure !!! echo "execute procedure ${PROC}();" \\ | dbaccess $DBNAME - 1>/dev/null 2>&1 done # # Execute any 4gl executables needed to clean data. # for EXE in ${EXES} do msg="`date +%H:%M:%S` Executing Program ${EXE}" echo $msg >> $INFO_FILE ${UPLOAD}/procs/${EXE} >>${INFO_FILE} 2>>${ERR_FILE} done } # lose any info and error files make for the day of the week RMFILES=${UPLOAD}/`date +%a`* /bin/rm -f ${RMFILES} touch $INFO_FILE touch $ERR_FILE # # In order to maintain a record of the files we process, we destroy # and recreate the file $REPORT_FILE each time we run. # /bin/rm -f $REPORT_FILE touch $REPORT_FILE # # Loop through all the upload directories # DIR_LIST=`/bin/ls -d ${UPLOAD}/W[CD]*` for NEXT_DIR in $DIR_LIST do cd $NEXT_DIR # # Loop through all the files we expect to find # for FILE in HEARCODE JUDGE CASECIV CASECRMC CASECRMD NAME \\ XREF CHARGE AMEND EVENTS CALENDAR ARFINES TRACKING \\ TRANS TRDIST CLEAN_UP do # # If the file doesn't exist, put out a warning # if [ ! -w ${FILE} ] then echo "Warning: no file ${FILE} found" >> $INFO_FILE else # if the file is zero-sized, put out a warning if [ ! -s ${FILE} ] then echo "Warning: ${FILE} file was empty" >> $INFO_FILE else # call the function that does the work load_file fi # [ ! -s ${FILE} ] fi # [ ! -a ${FILE} ] done # for FILE in HEARCODE JUDGE CASECIV CASECRMC CASECRMD ... # # Lose the directory once it's been processed # cd .. /bin/rm -rf ${NEXT_DIR} done -- -- Kai. \\\\//_ kaih@courtlink.utcourts.gov Live long and prosper.