I am currently running the following Korn shell script which works fine:

#!/usr/bin/ksh
count=`db2 -x "select count(*) from schema.tablename"`
echo "count"

I would like to add a "where" clause to the 2nd line that would allow me to get a record count of all the records from schema.tablename where PROCESS_DT(a datetime variable in tablename which is formatted as DATE9.) is equal to the today's unix system date - 1. I'm not sure of the proper syntax. Please help.

count=`db2 -x "select count(*) from schema.tablename where PROCESS_DT=UNIX system date-1"`

A link that may helps you:

Getting yesterday DATE

Regards

I now know how to find yesterdays date, however, I get an error message stating that the operands (PROCESS_DT and DATE_STAMP) are not compatible. PROCESS_DT is a DB2 date time variable with a DATE9 format. How can I declare DATE_STAMP to be a date time variable?

#!/usr/bin/ksh
DATE_STAMP=`TZ=CST+24 date +%y%m%d`
count=`db2 -x "select count(*) from schema.tablename where PROCESS_DT="$DATE_STAMP""`
echo "$count"

I'm not familiar with db2 but you should do something like:

I changed the date format and in the sql statement I convert the string in a date value.


Regards

Franklin52,

After trying your suggestion, I got the following error:

SQL0183N A datetime arithmetic operation or a datetime scalar function has a result that is not within the valid range of dates. SQLSTATE=22008

Sorry, I forgot to change the date format before running again. It worked, but returned a zero value for count. I'm trying to figure out why that happened when PROCESS_DT in the db2 table is equal to yesterday's date.

I just tried changing the format of DATE_STAMP to +%m/%d/%Y to match the format of the values of PROCESS_DT (04/22/2008), but still got the same error as listed earlier.