IC88681: ORACLE NICKNAME - BLANKS ARE RTRIMED AT THE END OF AN VARCHAR HOST VARIABLE IN INSERT/UPDATE STATEMENT

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Oracle wrapper removes trailing blanks at the end of a varchar
    host variable in insert/update statement.
    The problem only happens if remote Oracle table has varchar
    columns defined with char semantic.
    
    We can recreate the issue with following scripts:
    
    bad.sql
    ~~~~~~~~~~~
    drop server testserv@
    create server testserv type oracle version 11 wrapper net8
    options(node 'oracl',VARCHAR_NO_TRAILING_BLANKS 'Y')@
    create user mapping for db2inst1 server testserv
    options(remote_authid 'scott',remote_password 'tiger')@
    
    set passthru testserv@
    drop table test@
    create table test( c1 VARCHAR2(20 CHAR))@  <== char semantic
    set passthru reset@
    
    create nickname testn for testserv.scott.test@
    drop PROCEDURE testp@
    CREATE PROCEDURE testp ()
         SPECIFIC testp
         LANGUAGE SQL
       BEGIN
         DECLARE v1 varchar(20) ;
         set v1 = '123  ';
         insert into testn values(v1);
       END @
    
    insert into testn values('123  ')@
    call testp()@
    
    select hex(c1) from testn@
    
    db2 -td@ -f bad.sql
    
    DB20000I  The SQL command completed successfully.
    
    
      Return Status = 0
    
    
    1
    ----------------------------------------
    3132332020
    313233 ==> blanks at the end are removed.
    
      2 record(s) selected.
    
    A server option DB2_RTRIM_FOR_CHAR_SEM_COL will be added with
    'Y' as default. By default, it will work as before, column will
    be rtrimed in some cases as above. If DB2_RTRIM_FOR_CHAR_SEM_COL
    was set to 'N', no columns would be rtrimed.
    
    
    LOCAL FIX:
    
    Create remote Oracle table without char semantic, then recreate
    the nickname.
    

Local fix

Problem summary

  • User affected:
      Users who use Oracle wrapper of  in InfoSphere Federation
    Server
    Problem description and summay:
      See error description
    

Problem conclusion

  • Problem was fistly fixed in Version 10, FixPak 2. This
    fix should be applied on the federation Server.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC88681

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-11-28

  • Closed date

    2013-01-04

  • Last modified date

    2013-01-04

  • APAR is sysrouted FROM one or more of the following:

    JR44066

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSY

       UP



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

10.1

Reference #:

IC88681

Modified date:

2013-01-04

Translate my page

Machine Translation

Content navigation