I have been give the task to update one column on our production database with information in the same column on the matching test database. This should be an easy task, but it has turned out to be anything but that.

The version of DB2 we are using is version 7 (I'll have the rest of the information required by morning) on an IBM mainframe running ZOS (latest version).

Here is three versions of the SQL code that I have been attempting to run:

#1

UPDATE EHP.ECD_TB A
SET A.ECD_CL_STATUS = (SELECT B.ECD_CL_STATUS
FROM EHT.ECD_TB B
WHERE A.ECD_RORO_ID = B.ECD_RORO_ID
AND A.ECD_ED_ID = B.ECD_ED_ID
AND A.ECD_DN = B.ECD_DN
AND A.ECD_DN_SEQ = B.ECD_DN_SEQ
AND A.ECD_SEQ_NO = B.ECD_SEQ_NO
AND A.ECD_REJECT_RSN <> ' ')
WHERE EXITS
(SELECT NULL FROM EHT.ECD_TB C
WHERE A.ECD_RORO_ID = C.ECD_RORO_ID
AND A.ECD_ED_ID = C.ECD_ED_ID
AND A.ECD_DN = C.ECD_DN
AND A.ECD_DN_SEQ = C.ECD_DN_SEQ
AND A.ECD_SEQ_NO = C.ECD_SEQ_NO
AND A.ECD_REJECT_RSN <> ' ');
************************************************** **************************
#2

UPDATE EHP.ECD_TB A
SET A.ECD_CL_STATUS = B.ECD_CL_STATUS
FROM EHP.ECD_TB A
EHT.ECD_TB B
WHERE B.ECD_CL_STATUS =(SELECT MAX(C.ECD_SEQ_NO),
C.CL_STATUS
FROM EHT.ECD_TB C
EHP.ECD_TB A
WHERE A.ECD_RORO_ID = C.ECD_RORO_ID
AND A.ECD_ED_ID = C.ECD_ED_ID
AND A.ECD_DN = C.ECD_DN
AND A.ECD_DN_SEQ = C.ECD_DN_SEQ
AND A.ECD_SEQ_NO = C.ECD_SEQ_NO
AND A.ECD_REJECT_RSN <> " ");
************************************************** ********
UDATE EHP.ECD_TB A
SET (A.ECD_CL_STATUS) = (SELECT B.ECD_CL_STATUS
FROM EHT.ECD_TB B
WHERE A.ECD_RORO_ID = B.ECD_RORO_ID
AND A.ECD_ED_ID = B.ECD_ED_ID
AND A.ECD_DN = B.ECD_DN
AND A.ECD_DN_SEQ = B.ECD_DN_SEQ
AND A.ECD_SEQ_NO = B.ECD_SEQ_NO
AND A.ECD_REJECT_RSN <> ' '
WHERE EXITS
(SELECT NULL
FROM EHT.ECD_TB B,
EHP.ECD_TB A
WHERE A.ECD_RORO_ID = B.RORO_ID
AND A.ECD_ED_ID = B.ECD_ED_ID
AND A.ECD_DN = B.ECD_DN
AND A.ECD_DN_SEQ = B.ECD_DN_SEQ
AND A.ECD_SEQ_NO = B.ECD_SEQ_NO
AND A.ECD_REJECT_RSN <> ' '
The table - EHP.EHCD_tb is the production table with EHT.EHCD_TB the test table. The column ehcd_claim_status on EHP is to be updated with the contents of the column EHCD_CLAIM_STATUS from EHT.

We have taken the basic shell of the SQL update and made it into a select and it will pull the data from either table with no problem. Just when we try to get it to update it will post a -104 error indicating that a token "." is in error.

The line where it would normally indicate what token that could be used instead, is blank.

Our problem is trying to find out "which" period is it complaining about so we can utilize this code to update our production database.

I'm posting here in the hopes that someone has gone through something similar or has the DB2 experience, and would be able to give me some guidance in what I have done wrong.

Thanking you all in advance,

Buttonpusher

The following is the information that I was missing last night when I posted my question: DB2 version 7.1.0.

The IBM Z/OS is 1.4 and ISPF is 5.2

If I have forgotten anything that you might need to help, please let me know and I'll dig it up.

Thanking all in advance,

Buttonpusher

I figured by now there would have been at least one person that would have made some suggestions.

My boss is asking if anyone has responded yet, I'm hoping that someone has an answer that will assist me in getting this figured out.

Buttonpusher

Have you tried from any other interface , say, QMF ??

Also, have you checked the manuals for the UPDATE statement ... Have a read on what is and what is not possible in the update stmt ... May be a sub-query type syntax you use is not valid

I'm sorry that I have not been able to give a definitive answer

Sathyaram

Are you sure you want to use "EXITS" instead of "EXISTS"?

Sathyaram_s - We do not have QMF on our system, so I'm have to work with Spufi. I will look back into the manuals on DB2 updates and see what is there. I did look it over, but didn't take the time to fully read the chapter (I will do it now).

sharrisdb2 - you caught my bad typing there had been three of us looking that that code and none of us saw that. I'll make that change and see if I have any better luck.


I'm still going to read the chapter on updates and make sure that correction to the code is done then see if I can make any better headway.


Thanks for the replies,
Buttonpusher

I corrected my typo (exits to exists) and tried again with the following results:

SQLCODE = -104, ERROR: ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE: =
SQLSTATE = 42601 SQLSTATE RETURN CODE
SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
SQLERRD = 0 0 0 -1 80 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'00000050' X'00000000' SQL DIAGNOSTIC INFORMATION

This is a major nightmare for me as I am not able to figure out what "." it is complaining about.

Buttonpusher

Can you give us the DDL (CREATE scripts) from the tables ? I'll create them in my environment and see what I can make of it.

UPDATE EHP.ECD_TB A
SET A.ECD_CL_STATUS = B.ECD_CL_STATUS
FROM EHP.ECD_TB A
EHT.ECD_TB B
WHERE B.ECD_CL_STATUS =(SELECT MAX(C.ECD_SEQ_NO),
C.CL_STATUS
FROM EHT.ECD_TB C
EHP.ECD_TB A
WHERE A.ECD_RORO_ID = C.ECD_RORO_ID
AND A.ECD_ED_ID = C.ECD_ED_ID
AND A.ECD_DN = C.ECD_DN
AND A.ECD_DN_SEQ = C.ECD_DN_SEQ
AND A.ECD_SEQ_NO = C.ECD_SEQ_NO
AND A.ECD_REJECT_RSN <> " ");

why the " " in the last predicate

should this not be : AND A.ECD_REJECT_RSN <> ' '

another strange syntax
SET A.ECD_CL_STATUS = B.ECD_CL_STATUS
FROM EHP.ECD_TB A
EHT.ECD_TB B
this implies a join (is it allowed with update ??) and should it not be
SET A.ECD_CL_STATUS = (select B.ECD_CL_STATUS --(the select
FROM EHP.ECD_TB A, EHT.ECD_TB B (and also , between tables)

Buttonpusher,

This code will work:
Didn't your boss promised a little present or a big bonus for the one who could solve this?

Wim
The select after the exists will only find one row to update, if I'm reading the code correctly. I have around 700 rows to update; would the "*" work as a replacement for the "1"?

And Thanks to Wim for his solution.

Buttonpusher

The code reads like this:

UPDATE EHP.ECD_TB A
- update the table EHP.ECD_TB that I will call "A" from here on

SET ECD_CL_STATUS =
- for each record in A, update the column ECD_CL_STATUS with ...

(SELECT B.ECD_CL_STATUS
FROM EHT.ECD_TB B
WHERE ....
- with the value of the field ECD_CL_STATUS comming from the table EHT.ECD_TB ("B") comming from a record in B that matches the record in A that you (DB2) are considering for an update

WHERE EXISTS (SELECT 1
FROM EHT.ECD_TB C
WHERE ...
- don't update all the records in the table "A", but only those for which you can find a matching record in EHT.ECD_TB ("C")

So the "WHERE EXISTS" clause tells DB2 what records of A it should update (this will give you a list of 700 records; You can check this, replace the UPDATE ... statement with a SELECT * )
and the "(SELECT B.ECD_CL_STATUS" clause will tell DB2 what value it should put in ECD_CL_STATUS.

The functionality of specifying "WHERE EXISTS (SELECT 1" or "WHERE EXISTS (SELECT *" is the same, but in the "*" case, DB2 would have to move a lot more data around, something that takes time and space. The "SELECT 1" basically does the same, but is more efficient.

I don't know if your original "WHERE EXISTS (SELECT NULL" would work. I guess so, but I've never seen it being used. It's somewhere not so intuitive: where there is something (select nothing when ...). I'd stick to "WHERE EXISTS (SELECT 1" ....

I want to thank all that responded to my posting. We are going to try the solution that Wim posted. Thanks again to everyone that provided suggestions and pointed out any errors that were in the code that I had listed.

Buttonpusher

In #2 there is a comma missing betweenso the "." it's complaining about is the one between EHP and ECD_TB.