SAS DI JOB is failing ERROR: CLI execute error: [IBM][CLI Driver] CLI0106E Connection is closed.

[MG18]

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

[TomKari]

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]

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

[SASKiwi]

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]

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]

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

[Patrick]

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