BookmarkSubscribeRSS Feed
MG18
Lapis Lazuli | Level 10

HI All,

 

One of our job is failing due to communication error,please guide how to reslove it.

 

[sas@computeprod Logs]$ grep "ERROR:" ccc_FLOW_NEW_02_07_xx_2017.07.28_09.56.34.log
ERROR: CLI execute error: [IBM][CLI Driver] CLI0106E  Connection is closed. SQLSTATE=08003
ERROR: CLI close cursor error: [IBM][CLI Driver] CLI0108E  Communication link failure. SQLSTATE=40003
ERROR: CLI close cursor error: [IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being
ERROR: Errors printed on page 4.

 

we are connecting DB2 using cataloguing DB2 from appliction server and job code is shown in below :-

 

LIBNAME TAGTLIB DB2  Datasrc=database  SCHEMA=schema_name  AUTHDOMAIN="Xyz-auth" ;

PROC SQL;
CONNECT TO DB2
(
Datasrc=database AUTHDOMAIN="xyz_auth"
);

EXECUTE
(

DELETE FROM schema_name.DEPOSITSTABLE WHERE START_EOD_DATE=(SELECT MIN(START_EOD_DATE) FROM schema_name.DEPOSITSTABLE_INTER);

)by DB2;

disconnect from DB2;
QUIT;

LIBNAME TAGTLIB DB2  Datasrc=database  SCHEMA=schema_name  AUTHDOMAIN="xyz_auth" ;

%MACRO DEPOSIT_ADHOC_RUN();
PROC SQL;
INSERT INTO TAGTLIB.DEPOSITSTABLE (DBCOMMIT=4000000)
SELECT * FROM TAGTLIB.DEPOSITSTABLE_INTER ;
QUIT;

%MEND;
%DEPOSIT_ADHOC_RUN;

 

Please suggest soemthing what all possible reasons for failure and what all code are needed ?

Many thanks in adavnce. ... 🙂

 

 

6 REPLIES 6
TomKari
Onyx | Level 15

Generally, that kind of meessage is just SAS passing on error messages from further down the software stack, in this case it looks like DB2. It seems to be referencing communications problems, but I'm not a DB2 guy.

 

First question, is this problem repeatable? If it only happened once, and can't be made to happen again, could it be a temporary hiccup in your network?

 

Tom

MG18
Lapis Lazuli | Level 10

This is repeatable problem and occurring very frequently. Could you please suggest some parameter check for network also ?

 

SASKiwi
PROC Star

How long does the job run for until you get the error? One possible explanation if this is a long-running job is there could be a timeout communications rule affecting it.

MG18
Lapis Lazuli | Level 10

JOB is running 5-6 hours and then it is failing and Only this job is failing frequently. Is it good advice if i use db2 bulk loader to load data from intermediate table to target table ? will it improve the job performenance.

SASKiwi
PROC Star

Testing bulk loading seems sensible if you want to improve the job performance. Have you found out why the connection is closing?

Patrick
Opal | Level 21

@MG18

With bulk-loading I normally try to load into an empty (staging) table without any constraints and then load the actual target table fully in-database with the staging table as source. 

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 2700 views
  • 0 likes
  • 4 in conversation