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

DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

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