IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Different result in different versions of DB2


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Mon May 25, 2009 3:45 pm
Reply with quote

Hi all

Can anyone post a replay in this regard which will be very helpfull to me.
The requirement is
I have to delete duplicate rows form the table keeping one of the duplicate row.
Duplicate in the sense, duplicate by the value of a coulmn. Here EMPNO.

The Issue is

It is working fine in DB2 V7.1, but not in DB2 V8.1.

The query as well as the result is given below. Any suggestion is valuable.

In DB2 V7.1
Code:
   SELECT * FROM REF;                                                 
---------+---------+---------+---------+---------+---------+---------+
EMPNO  EMPNAME                                                       
---------+---------+---------+---------+---------+---------+---------+
1234   RAM                                                           
1234   RAMRAJ                                                         
1234   RAMAN                                                         
1234   RAMKUMAR                                                       
1234   RAMANI                                                         
1233   ABY                                                           
1233   ABY K                                                         
1233   ABY MATHEW                                                     
1236   ABY KURUV                                                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 9                               
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100           
---------+---------+---------+---------+---------+---------+---------+
    DELETE FROM REF A WHERE EMPNO IN                                 
    (SELECT EMPNO FROM  REF                                           
                  WHERE EMPNO = A.EMPNO                               
                  GROUP BY EMPNO                                     
                  HAVING COUNT(*) > 1);                               
---------+---------+---------+---------+---------+---------+---------+
DSNE615I NUMBER OF ROWS AFFECTED IS 6                                 
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0             
---------+---------+---------+---------+---------+---------+---------+
   SELECT * FROM REF;                                                 
---------+---------+---------+---------+---------+---------+---------+
EMPNO  EMPNAME                                                       
---------+---------+---------+---------+---------+---------+---------+
1234   RAMANI                                                         
1233   ABY MATHEW                                                     
1236   ABY KURUV                                                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 3                               
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100           
---------+---------+---------+---------+---------+---------+---------+

Here one row each of EMPNO 1234 and 1233 is keeping in the table and the
remaining rows of value 1234 and 1233 get deleted.
Row belongs to EMPNO 1236 is keeping in the table since there is only
one row having this value.

DB2 V8.1

Code:
  SELECT * FROM REF;                                               
---------+---------+---------+---------+---------+---------+-------
EMPNO  EMPNAME                                                     
---------+---------+---------+---------+---------+---------+-------
1234   RAM                                                         
1234   RAMRAJ                                                     
1234   RAMAN                                                       
1234   RAMKUMAR                                                   
1236   ABY KURUV                                                   
1233   ABY THAMPY                                                 
1233   ABY K                                                       
1233   ABY                                                         
1234   RAMANI                                                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 9                             
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100       
---------+---------+---------+---------+---------+---------+-------
  DELETE FROM REF A WHERE EMPNO IN                                 
  (SELECT EMPNO FROM REF
                WHERE EMPNO = A.EMPNO                     
                GROUP BY EMPNO
                HAVING COUNT(*) > 1) ;                     
---------+---------+---------+---------+---------+---------+-------
DSNE615I NUMBER OF ROWS AFFECTED IS 8                             
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0         
---------+---------+---------+---------+---------+---------+-------
  SELECT * FROM REF;                                               
---------+---------+---------+---------+---------+---------+-------
EMPNO  EMPNAME                                                     
---------+---------+---------+---------+---------+---------+-------
1236   ABY KURUV                                                   
DSNE610I NUMBER OF ROWS DISPLAYED IS 1                             
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100       
---------+---------+---------+---------+---------+---------+-------

Here if the row is duplicated by the value of EMPNO, all the rows get
deleted.

The table declaration is same in both the versions. Tablespace is also same in nature(Both Segemented).

Any suggestion is valuable.
Please help

Regards
Raghu
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Mon May 25, 2009 4:18 pm
Reply with quote

it has been a while since I fiddled with DB2, but I dare to say that You have judged the wrong way around

also IMHO even if the query had worked the way You wanted You would be anyway taking a random chance on which employee name to keep

also I feel , but I might be wrong, that the constarint empno = a.empno is not needed

why my comments ...

the <inner> query gives as result set all the occurrences of duplicated empno' s
( no need for the where clause )

the delete, as I see it, deletes all the rows for which the empno is in the previously obtained result set

so after all V8 behavior is the right one, keep testing icon_biggrin.gif
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Tue May 26, 2009 10:49 am
Reply with quote

Hi enrico-sorichetti

Thanks for the response.

Quote:
also I feel , but I might be wrong, that the constarint empno = a.empno is not needed


Yes you are right. Without the where clause also the SQL will work, but gives the same result. i.e. deleting all duplicates rows based on the the value of EMPNO.

But my belief is that for a correlated subquery, the outer query will execute first and if it finds a row in the specified table, then only the inner query will execute. Am I right?
If I am right, the query will read a row from the table then execute the subquery. If the condition specified in the subquery is fullfilled, then it will delete the row whcih is already read. Am I right?

I also feel that DB2 8 is better than DB2 7. But in this case my request is not satisfied.


Thanks
Raghu
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue May 26, 2009 7:01 pm
Reply with quote

Raghu,

Quote:
the outer query will execute first and if it finds a row in the specified table, then only the inner query will execute.


I think inner query gets executed first, then the outer query while executing it gets the data compared with the inner query result based on the conditions given.

I hope, iam right,
Sushanth
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue May 26, 2009 8:30 pm
Reply with quote

Quote:

I hope, iam right


Nope ... for each outer row, the empno for that row is passed to the subquery and the empno satisfying the count(*) condition is passed back to the outer query for comparison ... in the above query this correlation itself is redundant ....
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Tue May 26, 2009 11:41 pm
Reply with quote

Hi sushanth bobby

Quote:
I think inner query gets executed first, then the outer query while executing it gets the data compared with the inner query result based on the conditions given.


Your statement is valid for Sub-query and not for correlated sub-query.
My query is a correlated sub query.
Actually the real issue is not related to the flow of execution of the query.
The issue is related to the result of the query -same query is giving different result in different versions of DB2.

Thanks anyway for responding sushanth bobby

Hi ashimer

Quote:
... in the above query this correlation itself is redundant ....


Thanks for your response.
If I remove WHERE EMPNO = A.EMPNO form the query, then both the versions will give the same result. If I put it on the query, I got different result.
Any suggestion is thankful.

Regards
Raghu
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Tue May 26, 2009 11:54 pm
Reply with quote

I did a bit of searching around and the general consensus is that V8 behavior is the right one ...
Code:
    DELETE FROM tablename  WHERE colname  IN                                 
    (SELECT colname FROM  tablename                                                             
                  GROUP BY colname                                     
                  HAVING COUNT(*) > 1);
should delete all the duplicate rows occurrences

after having investigated any other subtle differences in the two environments with Your support group

it might be worth opening an issue with IBM support
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed May 27, 2009 12:26 pm
Reply with quote

Raghu,

I got the results expected by you, but i don't know how this works for you. Your example had a pattern like retaining the last duplicate. This is how it goes.

THE QUERY
Code:
  DECLARE GLOBAL TEMPORARY TABLE SESSION.EMPTAB
    (EMPNO INTEGER NOT NULL WITH DEFAULT
    ,NAME  CHAR(25)  NOT NULL) on commit preserve rows;

  insert into session.emptab values(1234,'RAM');
  insert into session.emptab values(1234,'RAMRAJ    ');
  insert into session.emptab values(1234,'RAMAN     ');
  insert into session.emptab values(1234,'RAMKUMAR  ');
  insert into session.emptab values(1234,'RAMANI    ');
  insert into session.emptab values(1233,'ABY       ');
  insert into session.emptab values(1233,'ABY K     ');
  insert into session.emptab values(1233,'ABY MATHEW');
  insert into session.emptab values(1236,'ABY KURUV ');

  select * from  session.emptab;

  DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPTB
    (EMPNO INTEGER NOT NULL WITH DEFAULT
    ,NAME  CHAR(25)  NOT NULL
    ,PK_ROW_NUM                        DECIMAL(9 , 0)
         GENERATED ALWAYS AS IDENTITY
           (START WITH                                 1 ,
            INCREMENT BY                               1 ,
            CACHE                                     20 ,
            NO CYCLE                                     ,
            MINVALUE                          -999999999 ,
            MAXVALUE                           999999999 ,
            NO ORDER                                     )
    )on commit preserve rows
  ;
INSERT INTO SESSION.TEMPTB (empno,name)
select empno,name from session.emptab;

SELECT * FROM SESSION.TEMPTB;

delete from session.temptb where pk_row_num not in(
select Max(pk_row_num) from session.temptb
group by empno);
select * from session.temptb;


SPUFI RESULTS
Code:
---------+---------+---------+---------+---------+---------+----
  DECLARE GLOBAL TEMPORARY TABLE SESSION.EMPTAB
    (EMPNO INTEGER NOT NULL WITH DEFAULT
    ,NAME  CHAR(25)  NOT NULL) on commit preserve rows;
---------+---------+---------+---------+---------+---------+----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
 
  insert into session.emptab values(1234,'RAM');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
  insert into session.emptab values(1234,'RAMRAJ    ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
  insert into session.emptab values(1234,'RAMAN     ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
  insert into session.emptab values(1234,'RAMKUMAR  ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
  insert into session.emptab values(1234,'RAMANI    ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
  insert into session.emptab values(1233,'ABY       ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
  insert into session.emptab values(1233,'ABY K     ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
  insert into session.emptab values(1233,'ABY MATHEW');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
  insert into session.emptab values(1236,'ABY KURUV ');
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
 
  select * from  session.emptab;
---------+---------+---------+---------+---------+---------+----
      EMPNO  NAME
---------+---------+---------+---------+---------+---------+----
       1234  RAM
       1234  RAMRAJ
       1234  RAMAN
       1234  RAMKUMAR
       1234  RAMANI
       1233  ABY
       1233  ABY K
       1233  ABY MATHEW
       1236  ABY KURUV
DSNE610I NUMBER OF ROWS DISPLAYED IS 9
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+----
 
  DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPTB
    (EMPNO INTEGER NOT NULL WITH DEFAULT
    ,NAME  CHAR(25)  NOT NULL
    ,PK_ROW_NUM                        DECIMAL(9 , 0)
         GENERATED ALWAYS AS IDENTITY
           (START WITH                                 1 ,
            INCREMENT BY                               1 ,
            CACHE                                     20 ,
            NO CYCLE                                     ,
            MINVALUE                          -999999999 ,
            MAXVALUE                           999999999 ,
            NO ORDER                                     )
    )on commit preserve rows
  ;
---------+---------+---------+---------+---------+---------+----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
INSERT INTO SESSION.TEMPTB (empno,name)
select empno,name from session.emptab;
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 9
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
 
SELECT * FROM SESSION.TEMPTB;
---------+---------+---------+---------+---------+---------+----
      EMPNO  NAME                        PK_ROW_NUM
---------+---------+---------+---------+---------+---------+----
       1234  RAM                                 1.
       1234  RAMRAJ                              2.
       1234  RAMAN                               3.
       1234  RAMKUMAR                            4.
       1234  RAMANI                              5.
       1233  ABY                                 6.
       1233  ABY K                               7.
       1233  ABY MATHEW                          8.
       1236  ABY KURUV                           9.
DSNE610I NUMBER OF ROWS DISPLAYED IS 9
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+----
delete from session.temptb where pk_row_num not in(
select Max(pk_row_num) from session.temptb
group by empno);
---------+---------+---------+---------+---------+---------+----
DSNE615I NUMBER OF ROWS AFFECTED IS 6
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
select * from session.temptb;
---------+---------+---------+---------+---------+---------+----
      EMPNO  NAME                        PK_ROW_NUM
---------+---------+---------+---------+---------+---------+----
       1234  RAMANI                              5.
       1233  ABY MATHEW                          8.
       1236  ABY KURUV                           9.
DSNE610I NUMBER OF ROWS DISPLAYED IS 3
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+----
---------+---------+---------+---------+---------+---------+----
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+----
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 16
DSNE621I NUMBER OF INPUT RECORDS READ IS 38
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 136


Sushanth
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Wed May 27, 2009 1:55 pm
Reply with quote

Hi enrico-sorichetti

Thanks for your comments. I hope you understand the issue fully. I will check with the DB2 support group.

Regards
Raghu

Hi sushanth bobby

Thanks for your effort. You had done a lot of work for that. I appreciate your work. Keep it up. But what I want is "why the same query is giving diffrent result in different version?". I didn't find any reson. I will be keeping research on it.

Regards
Raghu
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Wed May 27, 2009 1:56 pm
Reply with quote

Quote:
I hope you understand the issue fully.

what makes You think I did not understand the issue icon_question.gif icon_evil.gif

the difference could even be a maintenance issue on db2 v7

why haven' t yet involved Your support ?
forum should not be surrogates for proper in house support
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed May 27, 2009 9:53 pm
Reply with quote

Hi Enrico,

Quote:
I hope you understand the issue fully.
Once again language has caused misunderstanding.

I believe Raghu's comment meant that you clearly understood what was going on rather than you did not understand.
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Wed May 27, 2009 10:45 pm
Reply with quote

Hi dick and enrico-sorichetti

I simply trying to say that you have understood the issue raise by me and I got relevant replay from both of you. I am very much thankful to you both.

Regards
Raghu
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts first column truncated in search result IBM Tools 13
No new posts SDSF like solution in EJES (store com... All Other Mainframe Topics 4
No new posts executing XCTL command in COBOL witho... CICS 10
No new posts Undesired result while joining files. SYNCSORT 3
No new posts COBOL - Array result in single variab... COBOL Programming 3
Search our Forums:

Back to Top