Db2 – How to use a variable date when running a db2 command in a shell script

db2unix

I need to use a date variable in a select, like bellow, but I'm getting this error: Expected tokens may include: "". SQLSTATE=42601

How can use these variables in a shell script?

day0_T1=`TZ=CST+12 date +"%Y-%m-%d %T"`
day0_T0=`TZ=CST+24 date +"%Y-%m-%d %T"`
dayn_Tn=`TZ=CST+n date +"%Y-%m-%d %T"`

db2 connect to BD
db2 "/path/result.tmp  OF DEL MODIFIED BY COLDEL; decplusblank striplzeros
select count(*),user from table1
and close_datetime between cast(date('$day0_T0') as timestamp) AND cast(date('$day0_T1') as timestamp)
and task = 'task1' 
group by user
with ur"
db2 terminate

Best Answer

Shell quotes typically delimit arguments or escape other special shell characters from shell interpretation. You can contain (and therefore escape) different kinds of shell-quotes within other kinds based on various rules:

  • "''''" - a soft-quoted string can contain any number of hard-quotes.
  • "\"" - a \ backslash can escape a "soft-quote within a "soft-quoted string.
  • In this context a \\backslash also escapes itself, the \$expansion token, and \newlines as noted below, but is otherwise treated literally.
  • "${expand} and then some" - a soft-quoted string can contain an interpreted shell $expansion.
  • '"\' - a 'hard-quoted string can contain any character other than a 'hard-quote.
  • \ - an unquoted backslash will escape any following character for literal interpretation - even another backslash - excepting a \newline.
  • In a \\newline case both the \ backslash and the \newline are completely removed from the resulting interpreted command. ${parameter+expand "$parameter"} - quotes resulting from a shell expansion almost never serve as delimiter markers excepting a few special cases. I won't venture to describe these further here.

Test:

root@onare:/home/onare# echo $day0_T1
2015-07-16 14:10:44
root@onare:/home/onare# db2="something '${day0_T1}'"
root@onare:/home/onare# echo $db2
something '2015-07-16 14:10:44'
root@onare:/home/onare#
Related Question