SUPPORT / SAMPLES & SAS NOTES
Problem Note 18990: Incorrect data inserted into DB2 column defined as "CHAR() FOR BIT DATA"
 |  |  |  |  |
Incorrect data is being inserted into DB2 using SAS/ACCESS® Interface to ODBC. The problem surfaces if DB2 strings are defined as "CHAR() FOR BIT DATA". When attempting to insert a string into a DB2 column defined as "FOR BIT DATA", we pad the string with ‘30’x.
The following program illustrates the different behavior between DB2 and SAS:
proc sql noprint;
connect to odbc (dsn=db2lunix user=sas pw=sas91pw);
execute (drop table bitdata) by odbc;
execute (create table bitdata (col1 char(6) for bit data)) by odbc;
execute (insert into bitdata values (x'414141')) by odbc;
disconnect from odbc;
quit;
bc;
libname db odbc dsn=db2lunix user=sas pw=sas91pw;
insert into db.bitdata values ('414141'x);
quit;
data _null_;
set db.bitdata;
put col1= $hex.;
run;
/* LOG :
COL1=414141202020
COL1=414141303030
*/
The problem was caused because the code was trying to pad binary data with the character '0' for all cases of SQL_BINARY data.
To resolve this problem, apply the hotfix.
Select the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to ODBC | Microsoft Windows XP Professional | 9.1 TS1M3 | 9.2 TS1M0 |
| Microsoft Windows NT Workstation | 9.1 TS1M3 | |||
| Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 | 9.2 TS1M0 | ||
| 64-bit Enabled HP-UX | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Linux | 9.1 TS1M3 | 9.2 TS1M0 | ||
| 64-bit Enabled Solaris | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows 2000 Professional | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows 2000 Server | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 | 9.2 TS1M0 | ||
| 64-bit Enabled AIX | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Tru64 UNIX | 9.1 TS1M3 | 9.2 TS1M0 | ||