BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I was wondering if anyone ever had a problem where they submitted a Proc SQL query in the morning that generated no error messages, to a DB2 server and it took minutes and reran the exact same query later and it ran for hours with an error message.

We use the libname approach.

We suspect that the SAS query changed but we would like to have more information as to how and why.

Any feedback or references would be appreciated.
5 REPLIES 5
Doc_Duke
Rhodochrosite | Level 12
And the error message is...

I've had identical queries run very quickly in the early morning and die totally in the afternoon. It's often a function of the load on the server and on the communications part of the network. I was using passthrough queries, so there wasn't any 'optimization' being done at the SAS end.

Another possibility is that a table was locked from reading and the query finally timed out.

Doc Muhlbaier
Duke
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Not much info to go on here - need to understand where SAS is executing (local, remote server, mainframe batch, etc.), and how about where the DB2 instance resides (OS platform, dedicated or shared server).

Any chance you might have the output for the SAS execution, which would provide some elapsed and CPU usage statistics, possibly? Something for your team to review in addition to asking questions about DB2 server (system or application) degradation at the time of the SAS execution.

At a minimum, sure, the DB2 server may be the bottleneck, serving your request, as well as others, or because of a overall system degradation condition. And, of course, it may be that the SAS program changed which could be contributing to the behavior/outcome.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
Here is a "representation" of the code:
LIBNAME Schema1 ODBC DSN="DB2X" SCHEMA="Schema1" PRESERVE_TAB_NAMES=NO PROMPT;
NOTE: Libref Schema1 was successfully assigned as follows:
Engine: ODBC
Physical Name: DB2X
7352 LIBNAME Schema2 ODBC DSN="DB2X" SCHEMA="Schema2" PRESERVE_TAB_NAMES=NO PROMPT;
PROC SQL;
7328 CREATE TABLE Test_01 AS
7329 SELECT T1.*,
7330 T2.Var1,
7331 T2.Var2,
7332 T2.Var3,
7333 T2.Var4,
7334 T2.Var5,
7335 T2.Var6
FROM Schema1.TestA T1 INNER JOIN Schema2.INFO T2
7345 ON ((T1.Var1=T2.Var1) AND (T1.Var2=T2.Var2))
7346 WHERE (
7347 (T2.Var3~='V') AND (T2.Var5='Y')
7348 )
7349 ORDER BY Var1, Var2
7350 ;

The error message we get in the afternoon is:
ERROR: This SQL statement will not be passed to the DBMS for processing because it involves a join across librefs with different connection properties.

(we do not get it in the morning)

SAS is local and I think the servers are shared (we do load balancing).

Thanks for the help!
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Have you discussed this condition with anyone at your site familiar with the DB2 deployment who may be able to identify a circumstance or condition unique to the problem time-period?

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
Actually I wanted to see if I had a bite before supplying more info:

Here is a "representation" of the code:
LIBNAME Schema1 ODBC DSN="DB2X" SCHEMA="Schema1" PRESERVE_TAB_NAMES=NO PROMPT;
NOTE: Libref Schema1 was successfully assigned as follows:
Engine: ODBC
Physical Name: DB2X
7352 LIBNAME Schema2 ODBC DSN="DB2X" SCHEMA="Schema2" PRESERVE_TAB_NAMES=NO PROMPT;
PROC SQL;
7328 CREATE TABLE Test_01 AS
7329 SELECT T1.*,
7330 T2.Var1,
7331 T2.Var2,
7332 T2.Var3,
7333 T2.Var4,
7334 T2.Var5,
7335 T2.Var6
FROM Schema1.TestA T1 INNER JOIN Schema2.INFO T2
7345 ON ((T1.Var1=T2.Var1) AND (T1.Var2=T2.Var2))
7346 WHERE (
7347 (T2.Var3~='V') AND (T2.Var5='Y')
7348 )
7349 ORDER BY Var1, Var2
7350 ;

The error message we get in the afternoon is:
ERROR: This SQL statement will not be passed to the DBMS for processing because it involves a join across librefs with different connection properties.

(we do not get it in the morning)

SAS is local and I think the servers are shared (we do load balancing).

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 995 views
  • 0 likes
  • 3 in conversation