70219 - Running PROC SORT on a DB2 table with OUT= produces fewer rows than expected

Problem Note 70219: Running PROC SORT on a DB2 table with OUT= produces fewer rows than expected

When you run the SORT procedure on a DB2 table to create a new table, the number of rows written to the output table is fewer than the number of rows read from the DB2 table.

This issue might occur when all the following are true

The client DB2CODEPAGE is different from the CODEPAGE of the DB2 table, which requires the DB2 client to transcode values.
The data in the DB2 table contains values that cannot be transcoded by the DB2 client.
You use PROC SORT to sort a DB2 table, which creates a new output table.

For example, when the issue is observed, the SAS log might show three rows as being read, but there are only two rows in the output table:

1 * OPTIONS MSGLEVEL=I ;

libname DB2LIB db2 db=sample user=userid pw=XXXXXXXXX schema="schema" ;

proc sort data=DB2LIB.DB2TABLE out=WORK.SORTED ;

   by VAR ;

quit ;

NOTE: There were 3 observations read from the data set DB2LIB.DB2TABLE.

NOTE: The data set WORK.SORTED has 2 observations and 2 variables.

If you use OPTIONS MSGLEVEL=I before the PROC SORT step, an additional note similar to the following is written to the long:

Note: This note does not always mean that data has been lost, but it is a more obvious clue that there might be an issue.

NOTE: There were 3 observations read from the data set DB2LIB.DB2TABLE.

NOTE: SAS threaded sort was used.

NOTE: The data set WORK.SORTED has 2 observations and 2 variables.

When you run PROC SORT code similar to the example with MSGLEVEL=I , you would expect a different note (similar to the following). The following note shows that the data did not have any problematic values, so three rows were read and written.

NOTE: Sorting was performed by the data source.

NOTE: There were 3 observations read from the data set DB2LIB.DB2TABLE.

NOTE: The data set WORK.SORTED has 3 observations and 2 variables.

The issue is triggered by data that cannot be transcoded by the DB2 client, which results in an unexpected internal error in SAS and PROC SORT not processing all the data.

Workarounds

Here are some workarounds:

Copy the DB2 table to SAS and then sort it. An additional warning message (shown below) might occur:

data WORK.SASCOPY ;

   set DB2LIB.DB2TABLE ;

run ;

WARNING: Encountered following warning/error 1 time(s) during read: [IBM][CLI Driver][DB2/LINUXX8664] SQLSTATE 01517: A character that could not be converted was replaced with a substitute character.  .

NOTE: There were 3 observations read from the data set DBMS.SUKMRD_DB2TABLE.

NOTE: The data set WORK.SASCOPY has 3 observations and 2 variables.

Ensure that the client DB2CODEPAGE matches the Code Page of the DB2 table. Although this workaround prevents loss of rows, data values might contain unexpected characters.
Use PROC SQL with ORDER BY instead of PROC SORT. Here is an example:

proc sql ; 

   create table WORK.SORTED as 

      select * 

        from DB2LIB.DB2TABLE 

       order by VAR ; 

quit ;

Click 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
Product Family	Product	System	Reported	Fixed*
SAS System	Base SAS	z/OS
		z/OS 64-bit
		Microsoft® Windows® for 64-Bit Itanium-based Systems
		Microsoft Windows 10
		Microsoft Windows 11
		Microsoft Windows Server 2012 Datacenter
		Microsoft Windows Server 2012 R2 Datacenter
		Microsoft Windows Server 2012 R2 Std
		Microsoft Windows Server 2012 Std
		Microsoft Windows Server 2016
		Microsoft Windows Server 2019
		Microsoft Windows Server 2022
		64-bit Enabled AIX
		64-bit Enabled HP-UX
		64-bit Enabled Solaris
		AIX
		HP-UX
		HP-UX IPF
		Linux for x64
		Linux on Itanium
		Solaris for x64

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

Type:	Problem Note
Priority:	high
Topic:	SAS Reference ==> Procedures ==> SORT Data Management ==> Access ==> SAS I/O Data Management ==> Data Sources ==> External Databases ==> DB2

Date Modified:	2023-06-28 13:13:11
Date Created:	2023-06-28 10:07:52

Support

Problem Note 70219: Running PROC SORT on a DB2 table with OUT= produces fewer rows than expected

Workarounds

Operating System and Release Information