Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    13

    Unanswered: deleting duplicates in db2 db

    I've managed to cobble together a query to remove duplicates from an MVS DB. I can identify them, but my brain has been fried by getting this far and I'm missing the final step... can anyone put me right...

    This is what I have:

    select * from
    --delete from
    (SELECT ROW_NUMBER() OVER (partition by col1, col2, col3 order by col1)
    FROM MYDB.PTB WHERE VALID = 'Y' AND PLATFORM = 'AIX' and DATA='ABC')
    as E(RN) where RN>1

    I was hoping it would be as simple as changing select * from to delete from.. but that doesn't work.

    Thanks in advance..

    Extra info:

    Database server = DB2 z/OS 9.1.5
    Using QMF for Windows 8.1 FP 10 to connect to it
    Running on Windows 7

    The error is not particularly helpful

    [IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found following "". Expected tokens may include: "<IDENTIFIER>". SQLSTATE=42601

    so in otherwords, syntax is wrong
    Last edited by rnem170; 11-18-11 at 12:12.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... but that doesn't work.
    Please read this carefully, especially 1) and 2)
    http://www.dbforums.com/db2/854783-m...e-posting.html
    and supply more information.

  3. #3
    Join Date
    Nov 2011
    Posts
    13
    sorry, didn't realize this was needed as it's an sql syntax problem.

    I've updated my original post

  4. #4
    Join Date
    Nov 2011
    Posts
    13
    I found another similar hit which said to use this with z/OS 9.1:

    Code:
    DELETE FROM emp1
     WHERE RID(emp1)
           IN (SELECT rid
                 FROM (SELECT RID(emp1) rid
                            , ROWNUMBER() OVER(PARTITION BY emp_no , salary) rn
                         FROM emp1 )
                WHERE rn > 1 )
    but when I use my own version of this:

    DELETE FROM MYDB.PTB
    WHERE RID(MYDB.PTB)
    IN (SELECT rid
    FROM (SELECT RID(MYDB.PTB) rid
    , ROWNUMBER() OVER(PARTITION BY col1, col2, col3) rn
    FROM MYDB.PTB where VALID = 'Y' AND PLATFORM = 'AIX' and data = 'ABC')
    WHERE rn > 1 )

    I get another error:

    An error occurred while running the query.

    [IBM][CLI Driver][DB2] SQL0104N An unexpected token "<EMPTY>" was found following "". Expected tokens may include: "CORRELATION NAME". SQLSTATE=42601

    or the same in QMF on MVS:
    SQL error at or before <EMPTY>

    or in Command Editor (DB2 Connect for Windows 9.5 FP1)
    SQL0104N An unexpected token "<EMPTY>" was found following "". Expected tokens may include: "CORRELATION NAME". SQLSTATE=42601

    SQL0104N An unexpected token "<EMPTY>" was found following "". Expected tokens may include: "CORRELATION NAME".

    Explanation:

    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Add the correlation name after the innermost subselect, as the error message suggests.

  6. #6
    Join Date
    Nov 2011
    Posts
    13
    Quote Originally Posted by n_i View Post
    Add the correlation name after the innermost subselect, as the error message suggests.
    please can you give me a better clue?

    I changed it to :

    ... 'ABC') as XX

    and got:

    [IBM][CLI Driver][DB2] SQL0171N The data type, length or value of argument "1" of routine "RID" is incorrect. SQLSTATE=42815

    with:

    ... 'ABC') as XX(rn)

    [IBM][CLI Driver][DB2] SQL0158N The number of columns specified for "" is not the same as the number of columns in the result table. SQLSTATE=42811

    and:

    ... 'ABC') as XX(dum, rn)

    [IBM][CLI Driver][DB2] SQL0171N The data type, length or value of argument "1" of routine "RID" is incorrect. SQLSTATE=42815

  7. #7
    Join Date
    Nov 2011
    Posts
    13
    I think I have it working...

    DELETE FROM HLQ.TABLENAME
    WHERE RID(HLQ.TABLENAME) IN
    (SELECT RID FROM (
    SELECT RID(HLQ.TABLENAME) AS RID,
    ROWNUMBER() OVER(PARTITION BY field1, field2, field3)
    -- field 1 +2 +3 is unique
    FROM HLQ.TABLENAME WHERE field1 = 'X' AND
    field2 = 'Y' AND field3='Z') AS E(RID,RN)
    WHERE RN > 1)
    Last edited by rnem170; 11-19-11 at 13:14.

Tags for this Thread

Posting Permissions

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