Help using Base SAS procedures

PROC SQL disconnect error

Reply
Frequent Contributor
Posts: 93

PROC SQL disconnect error

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.

Super User
Posts: 3,909

Re: PROC SQL disconnect error

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

Frequent Contributor
Posts: 93

Re: PROC SQL disconnect error

Not that I am aware of, but that doesn't mean that there hasn't been. I will pursue that line with our DBAs.
Ask a Question
Discussion stats
  • 2 replies
  • 44 views
  • 0 likes
  • 2 in conversation