Proc SQL code change between SAS and DB2 Server

[deleted_user]

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.

[Doc_Duke]

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]

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]

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]

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]

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).