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;