Dave is correct here.

UDB (DB2 for Unix) will not allow you to declare variables at the script level, either.


Regarding your second question:

>I have another quick question.....
>How can we pass Input variables into a script like above while calling it from a Unix prompt. Like...
>$db2 -td@ -f abcd.pl <PARM1> <PARM2> ...etc.

the cleanest solution is to write the script as a shell script, not a SQL statement.  Then call the shell script and let the shell interpreter perform the substitutions.

Your example:

$db2 -td@ -f abcd.sql <PARM1> <PARM2> ...etc.

(Note that I've changed the name to abcd.sql as DB2/UNIX doesn't know what a PL/SQL file is.)

And let's assume that the abcd.sql file contains the query:

SELECT * FROM mytable WHERE City = <PARM1> and state = <PARM2>


The DB2/unix way to do this is a slight rewrite.  Create the file abcd.sh, containing the following:

#  abcd.sh
#  execute the former Oracle query from abcd.pl
#
# Check the first parameter.  If blank, default to "Tampa"
if [ "" = "$1" ]; then
  CITY="Tampa"
else
  CITY="$1"
fi
# Check the second parameter.  If blank, default to "FL"
if [ "" = "$2" ]; then
  STATE="FL"
else
  STATE="$2"
fi
db2 connect to database
db2 "SELECT * FROM mytable WHERE City = '$CITY' and state = upper('$STATE')"
db2 terminate


You would then execute abcd.sh from the command line, passing the desired parameters.

abcd.sh Atlanta Ga
abcd.sh Seattle Wa
etc...


If the query is more complex than you want to put on a single line, it can be written to another file, but parameter substitution may be different.  I'll gladly explain several ways to do that if necessary.


Good Luck,
Kent