Problem Note 69369: Reading a DB2 Boolean column returns "ERROR: CLI cursor extended fetch error: [IBM][CLI Driver] CLI0102E Invalid conversion. SQLSTATE=07006"
 |  |  |  |  |
Beginning with DB2 Client 11.5, if your DB2 data contains a data type of Boolean, an error similar to the following might occur:
ERROR: CLI cursor extended fetch error: [IBM][CLI Driver] CLI0102E Invalid conversion. SQLSTATE=07006
This behavior is a result of a change in the DB2 client. Now it returns a BIT data type, whereas previously it returned SMALLINT.
Here are the workarounds:
- Revert to an earlier DB2 Client such as DB2 11.01.000.
- Define a DB2 View to CAST the Boolean columns to a different type such as INTEGER.
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 | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to DB2 | Solaris | ||
| OpenVMS Alpha | ||||
| Linux on Itanium | ||||
| HP-UX IPF | ||||
| Linux | ||||
| Linux for x64 | Viya | |||
| 64-bit Enabled Solaris | ||||
| AIX | ||||
| HP-UX | ||||
| 64-bit Enabled AIX | ||||
| 64-bit Enabled HP-UX | ||||
| Windows Vista for x64 | ||||
| Windows Vista | ||||
| Windows Millennium Edition (Me) | ||||
| Windows 7 Ultimate x64 | ||||
| Windows 7 Ultimate 32 bit | ||||
| Windows 7 Professional 32 bit | ||||
| Windows 7 Professional x64 | ||||
| Windows 7 Home Premium x64 | ||||
| Windows 7 Home Premium 32 bit | ||||
| Microsoft Windows Server 2019 | ||||
| Microsoft Windows Server 2022 | ||||
| Microsoft Windows XP Professional | ||||
| Windows 7 Enterprise 32 bit | ||||
| Windows 7 Enterprise x64 | ||||
| Microsoft Windows Server 2016 | ||||
| Microsoft Windows Server 2012 Std | ||||
| Microsoft Windows Server 2012 R2 Std | ||||
| Microsoft Windows Server 2012 R2 Datacenter | ||||
| Microsoft Windows Server 2012 Datacenter | ||||
| Microsoft Windows Server 2008 for x64 | ||||
| Microsoft Windows Server 2008 R2 | ||||
| Microsoft Windows Server 2008 | ||||
| Microsoft Windows Server 2003 for x64 | ||||
| Microsoft Windows Server 2003 Standard Edition | ||||
| Microsoft Windows Server 2003 Enterprise Edition | ||||
| Microsoft Windows 2000 Datacenter Server | ||||
| Microsoft Windows 2000 Advanced Server | ||||
| Microsoft Windows 95/98 | ||||
| Microsoft Windows 11 | ||||
| Microsoft Windows 10 | ||||
| Microsoft Windows 8.1 Pro 32-bit | ||||
| Microsoft Windows 8.1 Pro x64 | ||||
| Microsoft Windows 8.1 Enterprise x64 | ||||
| Microsoft Windows 8.1 Enterprise 32-bit | ||||
| Microsoft Windows 8 Pro x64 | ||||
| Microsoft Windows 8 Pro 32-bit | ||||
| Microsoft Windows 8 Enterprise x64 | ||||
| OS/2 | ||||
| Microsoft Windows 8 Enterprise 32-bit | ||||
| Microsoft® Windows® for x64 | ||||
| z/OS | ||||
| z/OS 64-bit | ||||
| Microsoft® Windows® for 64-Bit Itanium-based Systems | ||||
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | ||||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | ||||
| Microsoft Windows XP 64-bit Edition | ||||
| Microsoft Windows 2000 Server | ||||
| Microsoft Windows 2000 Professional | ||||
| Microsoft Windows NT Workstation | ||||
| Microsoft Windows Server 2003 Datacenter Edition | ||||
| Solaris for x64 | ||||
| Tru64 UNIX | ||||
A fix for this issue for SAS/ACCESS Interface to DB2 9.44 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/K2R.html#69369A fix for this issue for SAS/ACCESS Interface to DB2 9.43 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/F4B.html#69369
Beginning with DB2 11.5 clients, DB2 now returns a BIT data type for Boolean columns, whereas it previously returned SMALLINT. This change results in conversion issues during the binding process between DB2 and SAS, which prevents SAS from reading columns that are defined as Boolean.
| Type: | Problem Note |
| Priority: | high |
| Topic: | Data Management ==> Data Sources ==> External Databases ==> DB2 |
| Date Modified: | 2022-07-07 14:10:21 |
| Date Created: | 2022-07-05 02:56:41 |