topic Re: SAS DI JOB is failing ERROR: CLI execute error: [IBM][CLI Driver] CLI0106E Connection is closed in SAS Data Management

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

MG18 — Fri, 28 Jul 2017 13:47:46 GMT

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

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

TomKari — Fri, 28 Jul 2017 17:37:16 GMT

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

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

SASKiwi — Fri, 28 Jul 2017 23:42:03 GMT

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.

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

MG18 — Sat, 29 Jul 2017 10:41:47 GMT

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.

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

MG18 — Mon, 04 Sep 2017 07:42:50 GMT

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

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

SASKiwi — Mon, 04 Sep 2017 08:00:40 GMT

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

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

Patrick — Mon, 04 Sep 2017 08:19:30 GMT

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