Problem Note 62728: Merging a Hive table that contains a decimal data type in PROC DS2 fails when the DS2ACCEL=NO option is specified
 |  |  |  |  |  |
When you perform a merge in PROC DS2 of a Hive table that contains a decimal data type, the code fails when the DS2ACCEL=NO option is included in the PROC DS2 statement. An error message like the following is generated:
ERROR: Line 112: Unable to prepare merge statement (rc=0x80fff802U)
When the DS2ACCEL=YES option is specified, PROC DS2 can run thread and data programs inside Hadoop. The code runs locally in SAS when DS2ACCEL=NO is specified.
The sample code on the Full Code tab illustrates the problem.
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 | Base SAS | z/OS | 9.4 TS1M5 | 9.4 TS1M6 |
| z/OS 64-bit | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft® Windows® for x64 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows 8 Enterprise 32-bit | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows 8 Enterprise x64 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows 8 Pro 32-bit | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows 8 Pro x64 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Enterprise x64 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Pro 32-bit | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Pro x64 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows 10 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2008 | 9.4 TS1M5 | |||
| Microsoft Windows Server 2008 R2 | 9.4 TS1M5 | |||
| Microsoft Windows Server 2008 for x64 | 9.4 TS1M5 | |||
| Microsoft Windows Server 2012 Datacenter | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2012 R2 Datacenter | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2012 R2 Std | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2012 Std | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2016 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Windows 7 Enterprise 32 bit | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Windows 7 Enterprise x64 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Windows 7 Home Premium 32 bit | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Windows 7 Home Premium x64 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Windows 7 Professional 32 bit | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Windows 7 Professional x64 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Windows 7 Ultimate 32 bit | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Windows 7 Ultimate x64 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| 64-bit Enabled AIX | 9.4 TS1M5 | 9.4 TS1M6 | ||
| 64-bit Enabled Solaris | 9.4 TS1M5 | 9.4 TS1M6 | ||
| HP-UX IPF | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Linux for x64 | 9.4 TS1M5 | 9.4 TS1M6 | ||
| Solaris for x64 | 9.4 TS1M5 | 9.4 TS1M6 | ||
The sample code below shows fails if one of the variables in the KEEP statement is a decimal data type in Hive.
proc ds2 ds2accel=no;
thread t_pgm / overwrite=yes;
keep a b c;
method run();
merge hive.mydata hive.mydata2;
by score;
end;
endthread;
data new.mymerge (overwrite=yes);
declare thread t_pgm t;
method run();
set from t;
end;
enddata;
run;
quit;A fix for this issue for SAS Threaded Kernel Extension for Hadoop Hive 9.45 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/D2Q.html#62728A fix for this issue for SAS/ACCESS Interface to Hadoop 9.45 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/D7H.html#62728
PROC DS2 can run thread and data programs inside Hadoop when the DS2ACCEL=YES option is specified. When DS2ACCEL=NO is specified, the code runs locally in SAS. When a Hive decimal data type variable is involved, the code fails and an error message is generated.
| Type: | Problem Note |
| Priority: | high |
| Topic: | SAS Reference ==> Procedures ==> DS2 |
| Date Modified: | 2018-10-30 10:35:35 |
| Date Created: | 2018-08-09 09:08:58 |