Visit Our UNIX and Linux User Community


Db2 command issues with cron


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Db2 command issues with cron
# 1  
Old 01-30-2014
[Solved] Db2 command issues with cron

Hi,

I have a very simple script that queries from a DB2 table. The script has 3 parts - (i) Sets the db2profile (ii) connects to db2 using credentials (iii) executes the query.This script works fine if i run it manually from the command prompt. However when scheduled in crontab, it proceeds fine upto step (ii) but fails with the error - "SQL1024N A database connection does not exist" just when the query is executed.

I could see from the logs that the db2 connection is indeed made successfully (connection sucess message displays and the return code is 0). But I am note sure why they query fails saying that a DB connection does not exist. Any immediate help is greatly appreciated.

Here is my CODE:

Code:
 
# Run DB2 profile on PRD
. /home/db2was/sqllib/db2profile
# Connect to Database
db2 CONNECT TO $DATABASE USER $JOB_USER USING $JOB_PASS
RETURN_CODE=$?
if test $RETURN_CODE -ne 0 ; then
 echo Script Failed. Error Connecting to Database
 exit 9999
fi
#Run SQL
PURGECOUNT=`db2  -x SELECT COUNT\(*\) PURGECOUNT FROM SCHEMA.TABLE WHERE TIME_COLUMN\<$PURGE_TIME WITH UR`
RETURN_CODE=$?

Here is the output from the log files when invoked by the crontab

Code:
++ db2 CONNECT TO DBXX USER USERXX USING PASSXX
   Database Connection Information
 Database server        = DB2/AIX64 9.7.7
 SQL authorization ID   = XXXX
 Local database alias   = DBXX
++ RETURN_CODE=0
++ test 0 -ne 0
+++ db2 -x SELECT 'COUNT(*)' PURGECOUNT FROM SCHEMA.TABLE WHERE 'TIME_COLUMN<1385884801949' WITH UR
++ PURGECOUNT='SQL1024N  A database connection does not exist.  SQLSTATE=08003'
+++ TEMP_RC=4

Moderator's Comments:
Mod Comment edit by bakunin: please use CODE-tags for output too. Thank you.

Last edited by bakunin; 01-30-2014 at 07:05 PM..
# 2  
Old 01-30-2014
Quote:
Originally Posted by VeePee
I could see from the logs that the db2 connection is indeed made successfully (connection sucess message displays and the return code is 0). But I am note sure why they query fails saying that a DB connection does not exist. Any immediate help is greatly appreciated.

Code:
 
PURGECOUNT=`db2  -x SELECT COUNT\(*\) PURGECOUNT FROM SCHEMA.TABLE WHERE TIME_COLUMN\<$PURGE_TIME WITH UR`

My suspicion is that the problem is with changing environments: the quoted line with the backticks opens a subshell and i am not sure if the db2-session extends to this. Try this:

Code:
 
db2  -x SELECT COUNT\(*\) PURGECOUNT FROM SCHEMA.TABLE WHERE TIME_COLUMN\<$PURGE_TIME WITH UR > /tmp/somefile
PURGECOUNT=$(cat /tmp/somefile)

or something similar, which avoids running the db2-command in the subshell.

I hope this helps.

bakunin
# 3  
Old 01-31-2014
Thanks a ton.. !!! That worked... !!!

Moderator's Comments:
Mod Comment edit by bakunin: great. I changed the thread status to "Solved".

Last edited by bakunin; 02-01-2014 at 09:42 AM..
 

Previous Thread | Next Thread
Test Your Knowledge in Computers #763
Difficulty: Medium
L4 caches are currently very common in computer systems.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Troubles running DB2 command in shell script via cron

Hi there, Now I'm facing error regarding running shell script via cron. The shell script which is required to get value from database. Below is the main part of shell script. #/bin/bash #connect to database(1) db2 connect to $database user xxxx using yyyy #set values from... (3 Replies)
Discussion started by: Rohan Kishibe
3 Replies

2. Shell Programming and Scripting

Bash/cron issues

Hi all, I am trying to run a cronjob to push my files to my git repo once a week and output a prompt to a logfile, my script works fine if I invoke it manually but my cronjob wont run for some reason, I have sourced the file, and restarted my Mac to no avail, right now I believe I have the cronjob... (8 Replies)
Discussion started by: gmenfan83
8 Replies

3. UNIX for Dummies Questions & Answers

AIX & DB2 paging issues

AIX & DB2 paging issues Hello I needs some help on AIX & DB2 paging issues. Recently a db2 query caused paging issues which then lead to memory problems and a server restart. Is there anyway of finding out what db2 queries were causing the problem.. Thanks in advance (2 Replies)
Discussion started by: nedkao
2 Replies

4. UNIX for Advanced & Expert Users

Cron security issues?

Does the use of cron (HP-UX 11) present a security risk IF it is only given to accounts which have shell access anyway. If it does present a risk, what is the risk? Can a script (or command) run via cron; run anything, write anywhere or read anywhere that the same user can not do outside of... (2 Replies)
Discussion started by: eileenkeeney
2 Replies

5. Shell Programming and Scripting

DB2: load command in unix

I am currently trying to load data from e.txt into tablea. I am getting error. db2 connect to qw01p user wcs using abc db2 "LOAD CLIENT FROM /dswork/ECONT/output/interface/e.txt OF DEL MODIFIED BY coldel| SAVECOUNT 0 ROWCOUNT 0 WARNINGCOUNT 0 MESSAGES XTMPRTI1.txt REPLACE into WCSADM.TABLEA... (1 Reply)
Discussion started by: skatpally
1 Replies

6. Shell Programming and Scripting

Executing DB2 command without using the DBI module

Hi Guys, I wonder if possible to execute a Db2 commands within a perl scripts without installing or invoking the DBI modules. I have a script that is written in kron shell and it looks like : DB=`db2 list db directory | egrep "Database alias|Directory entry type"|awk '{printf $0 ;... (0 Replies)
Discussion started by: arizah
0 Replies

7. Solaris

cron / crontab issues - solaris 10

I am having some issues with my cronjobs not running in solaris 10. Cron is running: ~> ps -ef | grep cron root 202 1 0 Jul 18 ? 0:01 /usr/sbin/cron bender 1646 1562 0 01:57:49 syscon 0:00 grep cron crontab -l lists the cronjob and I *think* its in the... (8 Replies)
Discussion started by: ippy98
8 Replies

8. AIX

db2 command on the AIX mechine

Hello, can any one help me to debug the following command. db2 -stec +pc ; Regards, SM (2 Replies)
Discussion started by: smolgara
2 Replies

9. Shell Programming and Scripting

for loop with db2 command

Having some trouble with usage of for loop and displaying words. Basically I had 3 words( some times more ) in the variable. I want to get one at a time to process. I am new to unix scripting so please bear with my question and appreciate your reply. I think this can also me done my awk... (1 Reply)
Discussion started by: Vaddadi
1 Replies

10. Shell Programming and Scripting

FTP Cron issues

I am on AS3 Update 4 Linux and am having an issue with an automated ftp script, I tried using the fd/sub proc method and that did not seem to work either. I normally use the following method to perform my ftp's but for some reason it works if I launch the script at the command line but in Cron it... (4 Replies)
Discussion started by: bryanthomas
4 Replies

Featured Tech Videos