Possible DB2 12 incorrout (upward compat)

Peter Conlin

Possible DB2 12 incorrout (upward compat)

FYI upward compatibility Db2 12 (vs 11 & 10 & V9):

CREATE TABLE  TXT1                                             
 (CHAR10 CHAR(10),                                             
  CHAR20 CHAR(20));                                            
INSERT INTO  TXT1 VALUES ('MATCH','NOMATCH');                  
INSERT INTO  TXT1 VALUES ('MATCH','MATCH');                    
SELECT CHAR10, CHAR20,                                         
       SUBSTR(GETVARIABLE('SYSIBM.VERSION'),1,12) AS DB2VERSION,
  CASE WHEN CHAR10 = CHAR20                                    
    THEN 'Db2 for z/OS says MATCH'                             
    ELSE 'Db2 for z/OS says NOMATCH'                           
  END AS "COMPARE_FLAG"                                        
 FROM  TXT1;                                                   

We have an open service request, but need to set up a trapping DSNADMSB (ADMIN_INFO_SQL has always been fine according to DSNTIJRT) to proceed.

 

I'm not an SQL guy (1st time for CASE) & new to DSNADMSB, so this could certainly be user error, but it really looks like CASE now processes equality in a different manner than WHERE.

Roy Boxwell

AW: Possible DB2 12 incorrout (upward compat)
(in response to Peter Conlin)
Oh my… I hope this gets fixed pronto! Just tested here on 10, 11 and 12 get the same result

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

Von: Peter Conlin [mailto:[login to unmask email]
Gesendet: Montag, 18. September 2017 21:36
An: [login to unmask email]
Betreff: [DB2-L] - Possible DB2 12 incorrout (upward compat)


FYI upward compatibility Db2 12 (vs 11 & 10 & V9):

CREATE TABLE TXT1
(CHAR10 CHAR(10),
CHAR20 CHAR(20));
INSERT INTO TXT1 VALUES ('MATCH','NOMATCH');
INSERT INTO TXT1 VALUES ('MATCH','MATCH');
SELECT CHAR10, CHAR20,
SUBSTR(GETVARIABLE('SYSIBM.VERSION'),1,12) AS DB2VERSION,
CASE WHEN CHAR10 = CHAR20
THEN 'Db2 for z/OS says MATCH'
ELSE 'Db2 for z/OS says NOMATCH'
END AS "COMPARE_FLAG"
FROM TXT1;

We have an open service request, but need to set up a trapping DSNADMSB (ADMIN_INFO_SQL has always been fine according to DSNTIJRT) to proceed.



I'm not an SQL guy (1st time for CASE) & new to DSNADMSB, so this could certainly be user error, but it really looks like CASE now processes equality in a different manner than WHERE.

-----End Original Message-----

Binyamin Dissen

Possible DB2 12 incorrout (upward compat)
(in response to Peter Conlin)
I don't get it.

Shouldn't one row say MATCH and one row say NOMATCH (order of the rows
undetermined or there is no ORDER BY clause)?.

What do you see?

On Mon, 18 Sep 2017 12:35:41 -0700 (MST) Peter Conlin <[login to unmask email]>
wrote:

:>FYI upward compatibility Db2 12 (vs 11 & 10 & V9):

:>CREATE TABLE  TXT1                                             
:> (CHAR10 CHAR(10),                                             
:>  CHAR20 CHAR(20));                                            
:>INSERT INTO  TXT1 VALUES ('MATCH','NOMATCH');                  
:>INSERT INTO  TXT1 VALUES ('MATCH','MATCH');                    
:>SELECT CHAR10, CHAR20,                                         
:>       SUBSTR(GETVARIABLE('SYSIBM.VERSION'),1,12) AS DB2VERSION,
:>  CASE WHEN CHAR10 = CHAR20                                    
:>    THEN 'Db2 for z/OS says MATCH'                             
:>    ELSE 'Db2 for z/OS says NOMATCH'                           
:>  END AS "COMPARE_FLAG"                                        
:> FROM  TXT1;                                                   
:>We have an open service request, but need to set up a trapping DSNADMSB (ADMIN_INFO_SQL has always been fine according to DSNTIJRT) to proceed.
 
:>I'm not an SQL guy (1st time for CASE) & new to DSNADMSB, so this could certainly be user error, but it really looks like CASE now processes equality in a different manner than WHERE.

--
Binyamin Dissen <[login to unmask email]>
http://www.dissensoftware.com

Director, Dissen Software, Bar & Grill - Israel


Should you use the mailblocks package and expect a response from me,
you should preauthorize the dissensoftware.com domain.

I very rarely bother responding to challenge/response systems,
especially those from irresponsible companies.

Peter Conlin

RE: Possible DB2 12 incorrout (upward compat)
(in response to Binyamin Dissen)

Yes Binyamin,

One line should say MATCH, one NOMATCH (as in DB2 11 & 10), but in DB2 12, both entries are flagged as NOMATCH.

 

IBM has updated the SR as reproduced in house with the compatibility change noted (as have a couple of DB2-L members, thank you), no APAR assigned as yet. 

peter

Peter Conlin

APAR opened RE: Possible DB2 12 incorrout (upward compat)
(in response to Roy Boxwell)

An APAR is now open, PI87497 with a workaround included in the doc.

(Use the larger character field on the left hand side of the equality test (at least for Western left->right codepages, probably the opposite for Middle-Eastern (Arabic - Hebrew).) 

 

https://www-304.ibm.com/ibmlink/sis/viewAparDoc.wss?context=aparAndUsage&searchWords=PI87497&documentIds=PI87497&lc=en&cc=US

pete conlin