Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: DBMS_LOB.COMPARE usage

    How do we use the function SYSIBMADM.DBMS_LOB.COMPARE

    I am trying to use it on a table in below format:

    col1 and col2 are CLOB columns

    select * from schema.tablename r where DBMS_LOB.COMPARE(r.col1,col2)=0

    Using this way gives me the below error:

    SQL0390N The function "SYSIBMADM.DBMS_LOB.COMPARE" resolved to specific
    function "DBMS_LOB_COMPARE_4" that is not valid in the context where it is
    used. SQLSTATE=42887



    Can someone please help me out. Am I missing anything here.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Here is an example

    Code:
    with temp1(i1,ct1) as
    (
    values(1,clob('1')),(2,clob('2'))
    )
    ,
    temp2(i2,ct2) as
    (
    values(1,clob('1x')),(2,clob('2'))
    )
    select i1,ct1,ct2,case when DBMS_LOB.COMPARE(ct1,ct2)=0 then 'Identical' else 'XXXXX' end as diff from temp1,temp2 where  i1=i2
    ;
    gives

    I1 CT1 CT2 DIFF
    ----------- --- --- ---------
    1 1 1x XXXXX
    2 2 2 Identical
    Not sure what the problem with your query is
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2009
    Posts
    272
    Thanks Sathya.

    I ran the same query which you ran. But still I get the error. See below


    $db2 -tvf temp
    with temp1(i1,ct1) as ( values(1,clob('1')),(2,clob('2')) ) , temp2(i2,ct2) as ( values(1,clob('1x')),(2,clob('2')) ) select i1,ct1,ct2,case when DBMS_LOB.COMPARE(ct1,ct2)=0 then 'Identical' else 'XXXXX' end as diff from temp1,temp2 where i1=i2
    SQL0390N The function "SYSIBMADM.DBMS_LOB.COMPARE" resolved to specific
    function "DBMS_LOB_COMPARE_4" that is not valid in the context where it is
    used. SQLSTATE=42887

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    hmm .. works in DB2 9.7 FP5.

    Try using
    Code:
    DBMS_LOB.COMPARE(ct1,ct2,dbms_lob.lobmaxsize,1,1)
    instead.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jun 2009
    Posts
    272
    Still gives the same error but the specific function is different.

    with temp1(i1,ct1) as ( values(1,clob('1')),(2,clob('2')) ) , temp2(i2,ct2) as ( values(1,clob('1x')),(2,clob('2')) ) select i1,ct1,ct2,case when DBMS_LOB.COMPARE(ct1,ct2,dbms_lob.lobmaxsize,1,1)= 0 then 'Identical' else 'XXXXX' end as diff from temp1,temp2 where i1=i2
    SQL0390N The function "SYSIBMADM.DBMS_LOB.COMPARE" resolved to specific
    function "DBMS_LOB_COMPARE" that is not valid in the context where it is used.
    SQLSTATE=42887

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    db2level
    db2 get db cfg for <yourdb> | grep -i compat

  7. #7
    Join Date
    Jun 2009
    Posts
    272
    DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09075" with
    level identifier "08060107".
    Informational tokens are "DB2 v9.7.0.5", "s111017", "IP23292", and Fix Pack
    "5".
    Product is installed at "/opt/IBM/db2/V9.7FP5".

    db2 get db cfg for developdb | grep -i compat
    Number compatibility = OFF
    Varchar2 compatibility = OFF
    Date compatibility = OFF

Posting Permissions

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