We have an application that interfaces to SAS8.2. Although SAS8.2 is no longer supported by SAS, we need to support it until the application is replaced. The UNIX sas program retrieves data from DB2 successfully, but when we do a disconnect from db2; we receive a CLI error (DB2) that the disconnect was unsuccessful, and, of course everything is broken from that point on. Everything works fine if I run with SAS9.3/SAS9.4 but there are issues, that prevent us from moving to either of those environments.
This program worked fine until this week. The only change in the SAS environment was the installation of HADOOP connectivity for SAS9.4. It should have had no impact on SAS8.2.
Adding OPTION SASTRACE gives me:
3 options sastrace=',,t,d' sastraceloc=saslog;
4 proc sql;
5 /* CONNECT TO DB2(DB=EDW5P1 USER=&RACF PASSWORD=&PASSW READBUFF=200 ); */
6 /* EXECUTE (SET CURRENT QUERY OPTIMIZATION =2) by db2; */
7 CONNECT TO DB2 (DB=EDW5P1 USER=&RACF PASSWORD=&PASSW);
TRACE: Successful connection made, connection id 0 0 1531324028 sqpcon 0 SQL
TRACE: Database/data source: EDW5P1 1 1531324028 sqpcon 0 SQL
TRACE: USER=HCD, PASS=XXXXXXX 2 1531324028 sqpcon 0 SQL
TRACE: AUTOCOMMIT is YES for connection 0 3 1531324028 sqpcon 0 SQL
8 create table TS as
9 select * from connection to db2 (
10 select current timestamp as TS1
11 from SYSIBM.SYSDUMMY1
12 WITH UR);
Prepare stmt: select current timestamp as TS1 from SYSIBM.SYSDUMMY1 WITH UR 4 1531324028 sqprslv 0 SQL
TRACE: SQL stmt prepared on statement 0, connection 0 is:
select current timestamp as TS1 from SYSIBM.SYSDUMMY1 WITH UR 5 1531324028 sqprslv 0 SQL
TRACE: DESCRIBE on statement 0, connection 0. 6 1531324028 sqprslv 0 SQL
TRACE: Open cursor from statement 0 on connection 0 7 1531324028 sqxextr 0 SQL
NOTE: Table WORK.TS created, with 1 rows and 1 columns.
TRACE: Close cursor from statement 0 on connection 0 8 1531324028 sqpclos 0 SQL
13 disconnect from DB2;
TRACE: Successful disconnection, id 0 9 1531324028 sqpdis 0 SQL
TRACE: Successful CLI free environment from connection 0 10 1531324028 sqpdis 0 SQL
ERROR: CLI disconnect failed: Unable to retrieve error message.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
Thank you for your assistance.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.