PROC SQL disconnect error

advoss — Wed, 11 Jul 2018 16:39:35 GMT

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.
14 quit;

Thank you for your assistance.

Re: PROC SQL disconnect error

SASKiwi — Wed, 11 Jul 2018 20:56:47 GMT

Has there been any change in your DB2 environment? The only problem seems to be the failure to retrieve an error message.

Re: PROC SQL disconnect error

advoss — Wed, 11 Jul 2018 21:26:14 GMT

Not that I am aware of, but that doesn't mean that there hasn't been. I will pursue that line with our DBAs.

Re: PROC SQL disconnect error

advoss — Tue, 17 Jul 2018 14:25:35 GMT

As is often the case, "nothing has changed".

I was able to use PROC SQL option NOERRORSTOP to ignore the error.

Now it is time to retire SAS8.2 from our tool environment.

Re: PROC SQL disconnect error

SASKiwi — Tue, 17 Jul 2018 20:55:34 GMT

@advoss - Good to hear you have a workaround. Please mark the post as answered (by you). And yes, time to move on from SAS 8.2. SAS 9.5 is due by year end!

topic Re: PROC SQL disconnect error in SAS Procedures

PROC SQL disconnect error

Re: PROC SQL disconnect error

Re: PROC SQL disconnect error

Re: PROC SQL disconnect error

Re: PROC SQL disconnect error