Results 1 to 14 of 14

Thread: DB2 Quagmire

  1. #1
    Join Date
    Oct 2006
    Posts
    7

    Unanswered: DB2 Quagmire



    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

  2. #2
    Join Date
    Oct 2006
    Posts
    7

    DB2 version information

    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

  3. #3
    Join Date
    Oct 2006
    Posts
    7


    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

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jul 2005
    Location
    Irvine, CA
    Posts
    23
    Are you sure you want to use "EXITS" instead of "EXISTS"?

  6. #6
    Join Date
    Oct 2006
    Posts
    7

    Thumbs up

    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

  7. #7
    Join Date
    Oct 2006
    Posts
    7


    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

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Jun 2006
    Posts
    471

    104

    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)
    Last edited by guyprzytula; 10-18-06 at 07:56.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Buttonpusher,

    This code will work:
    Code:
    UPDATE EHP.ECD_TB A
    SET 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 EXISTS (SELECT 1 
    		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 <> ' '
    		)
    ;
    Didn't your boss promised a little present or a big bonus for the one who could solve this?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Oct 2006
    Posts
    7
    Quote Originally Posted by Wim
    Buttonpusher,

    This code will work:
    Code:
    UPDATE EHP.ECD_TB A
    SET 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 EXISTS (SELECT 1 
    		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 <> ' '
    		)
    ;
    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

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The select after the exists will only find one row to update, if I'm reading the code correctly.
    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.

    I have around 700 rows to update; would the "*" work as a replacement for the "1"?
    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" ....
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  13. #13
    Join Date
    Oct 2006
    Posts
    7

    Thumbs up A Big Thanks



    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

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    In #2 there is a comma missing between
    Code:
    FROM EHT.ECD_TB C
    EHP.ECD_TB A
    so the "." it's complaining about is the one between EHP and ECD_TB.
    Last edited by Peter.Vanroose; 10-20-06 at 19:23.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •