DBCLIENT_MAX_BYTES= LIBNAME Statement Option

Details

When using a multi-byte SAS session encoding, such as UTF8 or EUC-CN, it might be necessary to control the number of bytes that SAS allocates for each character in a double-byte or multi-byte character set. By default, SAS estimates how many bytes per character are required to avoid truncation or transcoding issues. This estimation might lead to overallocation.

For example, if a DBMS system contains single-byte ASCII data, but a column’s character set encoding is UTF8, SAS overallocates space because it assumes that the UTF8 source requires multiple bytes per character. This overallocation can cause an inflation issue when copying data back and forth between SAS data sets and the external DBMS. It can also lead to performance issues because of the additional blank padding that SAS must manage.

To address this situation, set DBCLIENT_MAX_BYTES=1. This tells SAS that the underlying data is encoded in a single-byte encoding.

The default session encoding for SAS Viya is UTF8. However, if SAS is running in a single-byte session encoding, then this option has no effect because SAS is limited to one byte per character by the encoding.

To prevent unwanted data expansion when you write data back to your data source, use the DBSERVER_MAX_BYTES= LIBNAME option, if it is supported for your data source. To write to a data type that differs from the default exported data type, use the DBTYPE= data set option.

DB2: By default, when loading graphic data types into SAS with a UTF8 session encoding, SAS/ACCESS allocates three times the length of the graphic type data. For example, SAS/ACCESS loads data of type GRAPHIC(100) into SAS as CHAR(300). If you specify a value for DBCLIENT_MAX_BYTES=, then that value is used as the multiplier for graphic data types. If you set DBCLIENT_MAX_BYTES=2, then VARGRAPHIC(100) data is loaded into SAS as CHAR(200). If you are not using UTF8 session encoding, then the default value of DBCLIENT_MAX_BYTES= for graphic data types is 1.

SAP HANA: To ensure that an error is generated in the SAS log if data is truncated, set the SAS_SAPHANA_EFTCH_INFO_ERROR environment variable to YES, and specify sastrace=',,d,' and sastraceloc=saslog in an OPTIONS statement.

Teradata: The DBCLIENT_MAX_BYTES= option is ignored for Teradata columns with a LATIN or UNICODE character set encoding when both the Teradata client encoding and the SAS session encoding are set to UTF8. Ignoring data with the LATIN or UNICODE encodings prevents the possibility of data truncation that is not called out in the SAS log and prevents possible data security issues.

%let engine=oracle;
%let connopt=path=myPath user=myUsr1 pw=myPwd1;

libname x5 &engine &connopt 
  dbclient_max_bytes=3 dbclient_encoding_fixed=yes;
proc contents data=x5.char_sem; run;
proc contents data=x5.nchar_sem; run;
proc contents data=x5.byte_sem; run;
proc contents data=x5.mixed_sem; run;