IBM Support

JR44066: 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

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * user who use federation oracle wrapper                       *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * upgrade to v9.7.0.8                                          *
    ****************************************************************
    

Problem conclusion

  • add new server option to block rtriming trailing blanks in
    insert/update statement if remote column has char semantics.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR44066

  • Reported component name

    FEDERATION SERV

  • Reported component ID

    5724N9700

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-09-12

  • Closed date

    2013-04-01

  • Last modified date

    2013-04-01

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

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

    IC88681

Fix information

  • Fixed component name

    FEDERATION SERV

  • Fixed component ID

    5724N9700

Applicable component levels

  • R970 PSN

       UP

  • R970 PSY

       UP



Document information

More support for: InfoSphere Federation Server
General Issues

Software version: 9.7

Reference #: JR44066

Modified date: 01 April 2013