07-28-2017 09:47 AM
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" ;
CONNECT TO DB2
DELETE FROM schema_name.DEPOSITSTABLE WHERE START_EOD_DATE=(SELECT MIN(START_EOD_DATE) FROM schema_name.DEPOSITSTABLE_INTER);
disconnect from DB2;
LIBNAME TAGTLIB DB2 Datasrc=database SCHEMA=schema_name AUTHDOMAIN="xyz_auth" ;
INSERT INTO TAGTLIB.DEPOSITSTABLE (DBCOMMIT=4000000)
SELECT * FROM TAGTLIB.DEPOSITSTABLE_INTER ;
Please suggest soemthing what all possible reasons for failure and what all code are needed ?
Many thanks in adavnce. ...
07-28-2017 01:37 PM
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?
09-04-2017 03:42 AM
This is repeatable problem and occurring very frequently. Could you please suggest some parameter check for network also ?
07-28-2017 07:42 PM
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.
07-29-2017 06:41 AM
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.
09-04-2017 04:00 AM
Testing bulk loading seems sensible if you want to improve the job performance. Have you found out why the connection is closing?
09-04-2017 04:19 AM
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.