BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
advoss
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
advoss
Quartz | Level 8

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.

View solution in original post

4 REPLIES 4
SASKiwi
PROC Star

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

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

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.

SASKiwi
PROC Star

@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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1244 views
  • 0 likes
  • 2 in conversation