Usage Note 32742: DB2 packed decimal-data fields are read incorrectly from the mainframe when you use SAS/ACCESS® Interface to DB2 in the UNIX environment
 |  |  |  |
DB2 packed decimal-data fields are read incorrectly from the mainframe when you use SAS/ACCESS Interface to DB2 under UNIX.
For example, consider the following program:
/* Data from the z/OS mainframe DB2 table through SAS metadata. */
libname glm meta library="GLM" repname=Foundation;
proc sql;
connect to db2;
create view N420CTL_view as
select * from connection to db2
(select dbid,tblkey,data from GLM.N420CTL
where dbid ='VET'AND TBLKEY LIKE 'MALLITILOMA%'
with ur);
quit;
data N420ctl;
set N420ctl_view;
char_field1=put(substr(data,85,3),$ebcdic3.);
char_field2=put(char_field1,$hex6.);
if (substr(char_field2,6,1)='D') then
num_field = '-' !! compress(char_field2,'D');
else
num_field = compress(char_field2,'C,F');
run;
In this example, char_field2 has the value 35C00C under UNIX (AIX). However, on the mainframe (z/Os), the value is 35430C.
The problem occurs because the column is defined as type VARCHAR, and the DB2 client layer performs an EBCDIC-to-ASCII conversion when it brings the column over to UNIX.
To resolve this issue, alter the SQL procedure’s Pass-Through query by specifying a CAST clause and removing the EBCDIC format, as follows:
proc sql;
connect to db2 (ssid=DSN0);
create view N420CTL_view as
select * from connection to db2
(select dbid,tblkey,
cast (data as varchar(1712) for bit data) as data
from GLM.N420CTL
where dbid='VET' and tblkey like 'MALLITILOMA%'
with ur);
quit;
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to DB2 | 64-bit Enabled AIX | 9.1 TS1M3 SP4 | |
| z/OS | 9.1 TS1M3 SP4 | |||
| Type: | Usage Note |
| Priority: |
| Date Modified: | 2008-07-24 15:31:12 |
| Date Created: | 2008-07-22 09:56:59 |