DB2 SQL - not like and <> get different result?

[martin_mikala]

Hi,

Do you know why DB2 10.5.7 (I didn't test other) get different result for selects with "NOT LIKE" and "<>", when I set exact string for both? Seems ,that this difference is not in everytime. Me do it for OWNER column in syscat.tables. Bellow is example.

> db2 "select SUBSTR(TABSCHEMA,1,10) AS TABSCHEMA,SUBSTR(TABNAME,1,20) AS TABNAME,SUBSTR(OWNER,1,10) AS OWNER, TYPE, STATUS, CREATE_TIME, ALTER_TIME from syscat.tables where TABNAME = 'Z70_RN_VEN_TABL' AND OWNER not like 'SAPEST'"

TABSCHEMA  TABNAME              OWNER      TYPE STATUS CREATE_TIME                ALTER_TIME

---------- -------------------- ---------- ---- ------ -------------------------- --------------------------

SAPSR3     Z70_RN_VEN_TABL      SAPEST     T    N      2013-05-11-15.17.26.378695 2013-05-11-15.17.26.658445

  1 record(s) selected.

> db2 "select SUBSTR(TABSCHEMA,1,10) AS TABSCHEMA,SUBSTR(TABNAME,1,20) AS TABNAME,SUBSTR(OWNER,1,10) AS OWNER, TYPE, STATUS, CREATE_TIME, ALTER_TIME from syscat.tables where TABNAME like 'Z70_RN_VEN_TABL' AND OWNER <> 'SAPEST'"

TABSCHEMA  TABNAME              OWNER      TYPE STATUS CREATE_TIME                ALTER_TIME

---------- -------------------- ---------- ---- ------ -------------------------- --------------------------

  0 record(s) selected.

B.R.

Martin

[Frank-Martin · Accepted solution]

Hi Martin,

interesting question. Looks like DB2 adds some trailing blanks to column OWNER in sysact.tables. I do not know why this is the case.

select length(OWNER), hex(owner)  from syscat.tables where owner = 'SAPN75' and tabname = 'SVERS'     

1               2

---------------- -------------------------------------                                                                     

8               5341504E37352020                                                                                                                                                                                                                                                 

1 record(s) selected.

Operators <> and NOT LIKE behave differently regarding trailing blanks. Trailing blanks in VARCHAR columns are significant for LIKE operators but not for = or <> comparisons.

select tabname , tabschema, owner from syscat.tables where tabname = 'SVERS' and owner <> 'SAPN75'

0 record(s) selected.

select tabname , tabschema, owner from syscat.tables where tabname = 'SVERS' and owner NOT LIKE 'SAPN75'

1 record(s) selected.

To circumvent the difference you may have to use two NOT LIKE operators.

select tabname , tabschema, owner from syscat.tables where tabname = 'SVERS' and owner NOT LIKE 'SAPN75' and owner NOT LIKE 'SAPN75 %'

1 record(s) selected.

For this reason you sometimes find SQL statements with double LIKE generated by the ABAP stack.

Regards

               Frank