SQL in Unix Script Help Needed

everpro · #1 (**permalink**) 05-26-04, 12:18

Hi all,

I am running in AIX 4 using DB2 Connect DB2 SDK 7.2.5. I am writing a script that connects to a UDB running on the mainframe. Here is a snippet of the code:

for APPSERV in TESTDB
do

( db2 <<-END_DB2
connect to ${APPSERV} user userid using password
DB2 UPDATE ${APPSERV}.PSPRCSRQST SET RUNSTATUS = '1',PRCSRTNCD = 0,CONTINUEJOB = 0,ENDDTTM = CURRENT TIMESTAMP,LASTUPDDTTM = CURRENT TIMESTAMP WHERE PRCSINSTANC
E = 7431

END_DB2

The above SQL runs correctly in db2 interactive mode, however, it fails with the following error when I run it via the script:

DB20000I The SQL command completed successfully.
db2 => db2 => DB21034E The command was processed as an SQL statement because it
was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0199N The use of the reserved word "SET" following "" is not valid.
Expected tokens may include: "IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING> ".
SQLSTATE=42601

Any ideas?

Thanks!

n_i · #2 (**permalink**) 05-26-04, 14:09

What's that with the 'DB2 UPDATE...'? At that point you're already in the CLP; shouldn't you just use 'UPDATE...'?

everpro · #3 (**permalink**) 05-27-04, 11:22

I did not have it originally but then added it after doing some research/troubleshooting..... anyway I still get the failure without it in the script.

n_i · #4 (**permalink**) 05-27-04, 14:10

Try invoking db2 with the "-v" flag: it'll show the the exact statement it is trying to execute. You may be able to see the reason for the error.

Damian Ibbotson · #5 (**permalink**) 05-27-04, 18:12

Try this...

Code:

for APPSERV in TESTDB
do
  ( 
      db2 -vt <<- END_DB2

      connect to ${APPSERV} user userid using password
      ;

      UPDATE ${APPSERV}.PSPRCSRQST 
      SET RUNSTATUS = '1'
      ,     PRCSRTNCD = 0
      ,     CONTINUEJOB = 0
      ,     ENDDTTM = CURRENT TIMESTAMP
      ,     LASTUPDDTTM = CURRENT TIMESTAMP 
      WHERE PRCSINSTANCE = 7431 
      ;

END_DB2

  )

done

everpro · #6 (**permalink**) 06-01-04, 15:07

Many thanks!!! It is working, now on to the next problem!

Internet Services

Web Development

Digital Marketing

Consumer Tech