The bulk-load process in SAS® 9 pads character variable fields of type VARCHAR with blanks when you use SAS/ACCESS® Interface to DB2 under z/OS


For SAS® 8 under z/OS, when SAS builds a file that is used by the DB2 bulk-loading process, the software sets asides two extra bytes for variables of type VARCHAR. The two extra bytes store the length of the actual value. For example, suppose a field is defined as VARCHAR(255), but it has the value 'ABC' for a record. In this case, 5 bytes are set aside for that row: 3 bytes for the actual data, and 2 extra bytes that define the actual length of the value (in this case, the length is 3).

In SAS® 9, however, those extra bytes are not added because the columns are now treated as type CHAR rather than type VARCHAR. So when you insert a VARCHAR variable in SAS 9, the resulting variable value is the full length of the DB2 column.

As an example, in SAS 8, the value 'A' is passed as 'A'. In SAS 9, the value passed is 'A', followed by 254 blanks. If the LENGTH function is processed in DB2, the resulting variable length will be 1 when the bulk loading is done SAS 8. The length will be 255 if the bulk loading is done in SAS 9. However, if the LENGTH function is processed in SAS, the results for both releases of SAS will be the same because SAS does not include the blanks in its calculation. Therefore, if you are processing the length in DB2, you need to use the DBTYPE= data set option in order to obtain the same behavior in both SAS 8 and SAS 9.

Example

Suppose you submit the following code to load and query the data:

data db2lib.bulktest bulkload=yes
                     bl_db2tblxst=yes
                     bl_db2ldct1='RESUME YES NOCOPYPEND LOG NO');
   length var1 $ 255;
   var1='aa'; 
   output;
   var1='a'; 
   output;
run;

proc sql;
select var1, length(var1) as length_of_var1
from db2lib.bulktest;
quit;

The results look similar to the following (trailing blanks are not shown for display purposes):

In SAS 8

VAR1 LENGTH_OF_VAR1 ---------------------- AA 2 A 1

In SAS 9

VAR1 LENGTH_OF_VAR1 ---------------------- AA 255 A 255

To get the same behavior and similar results in SAS 9 as you did in SAS 8, modify the code to define the variable as VARCHAR(255) and include it in the DBTYPE= data set option, as shown here:

data db2lib.bulktest (dbtype=(var1='VARCHAR(255)')
                     bulkload=yes
                     bl_db2tblxst=yes
                     bl_db2ldct1='RESUME YES NOCOPYPEND LOG NO');
   length var1 $ 255;
   var1 ='aa'; 
   output;
   var1 ='a'; 
   output;
run;

proc sql;
select var1, length(var1) as length_of_var1
from db2lib.bulktest;
quit;

The results of this code will be similar to the SAS 8 results.