https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917118#M41036 <P>This has nothing to do with DIS and likely not even with SAS but with the communication of the ODBC driver with the SQL server.&nbsp;</P> <P>Just use the exact error message to Google possible causes and remedies. Also ensure that the ODBC driver used meets both the SQL Server and SAS requirements.</P> Wed, 21 Feb 2024 10:14:01 GMT Patrick 2024-02-21T10:14:01Z https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917108#M41034 <P>Dear Team,</P> <P>Some ETLs are failed in SAS DI due to communication link failure as per the log and some other jobs are executing in a long running process like 1 Lakhs records taking 10 minutes or above to complete.</P> <P>We have checked the Ping and Telnet in SAS Env. and DB Connection also which is SQL Server, all are connecting successfully and also checked the I/O wait time, it's 2.1 and reduce to 0. still we are facing same issue like Communication link failure.&nbsp;</P> <P>Please suggest how to resolve this problem ?</P> <P>I have attached some screenshots for your references.</P> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Shakti_Sourav_1-1708503614829.png" style="width: 999px;"><img src="https://communities.sas.com/t5/image/serverpage/image-id/93961i132021083C36266D/image-size/large?v=v2&amp;px=999" role="button" title="Shakti_Sourav_1-1708503614829.png" alt="Shakti_Sourav_1-1708503614829.png" /></span></P> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Shakti_Sourav_2-1708503658368.png" style="width: 999px;"><img src="https://communities.sas.com/t5/image/serverpage/image-id/93962iDD086A03C9FC4D7A/image-size/large?v=v2&amp;px=999" role="button" title="Shakti_Sourav_2-1708503658368.png" alt="Shakti_Sourav_2-1708503658368.png" /></span></P> <P>&nbsp;</P> <P>&nbsp;</P> <P>Thanks &amp; Regards</P> <P>Shakti Sourav Mohapatra</P> <P>&nbsp;</P> <P>&nbsp;</P> <P>&nbsp;</P> <DIV id="tinyMceEditorShakti_Sourav_0" class="mceNonEditable lia-copypaste-placeholder">&nbsp;</DIV> <P>&nbsp;</P> Wed, 21 Feb 2024 08:21:30 GMT https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917108#M41034 Shakti_Sourav 2024-02-21T08:21:30Z https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917111#M41035 I am not 100% sure but it looks like odbc driver is not properly configure or it is not working Wed, 21 Feb 2024 08:32:40 GMT https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917111#M41035 mgallardo 2024-02-21T08:32:40Z https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917118#M41036 <P>This has nothing to do with DIS and likely not even with SAS but with the communication of the ODBC driver with the SQL server.&nbsp;</P> <P>Just use the exact error message to Google possible causes and remedies. Also ensure that the ODBC driver used meets both the SQL Server and SAS requirements.</P> Wed, 21 Feb 2024 10:14:01 GMT https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917118#M41036 Patrick 2024-02-21T10:14:01Z https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917748#M41075 Hello Patrick &amp; Mgallardo ,<BR />We have checked with odbc driver and restarted the db &amp; SAS server and also checked the network connection. All are connected successfully as per the admin statement. Problem is, sometimes ETL executing successfully and sometimes it's showing link failure. <BR />Please suggest what to do ? &amp;<BR />Is there any code or tool to check the proper root cause is SAS ? <BR /><BR />Thanks<BR /> Sun, 25 Feb 2024 02:24:48 GMT https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917748#M41075 Shakti_Sourav 2024-02-25T02:24:48Z https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917752#M41076 <P>Asking chatGPT what the possible root cause for intermittent communication link failures could be returned below answer.&nbsp;</P> <P>&nbsp;</P> <P>One of the usual questions: Did this ever work in the past without issues? And if yes: What has changed?</P> <P>&nbsp;</P> <P>I'd be looking first into the network, concurrency/resource contention and timeouts (like: long running process on the DB).</P> <P>Looking at the time when such errors occur and how many SAS and other processes connecting to the database exist at that time could help narrowing down the root cause.</P> <P>&nbsp;</P> <P>I believe if all available connection to a DB are consumed the error would be different (connection refused) but it's eventually still worth to check and potentially increase the number of allowed connections. You also need certainly to talk to your DBA to get the info if there has been a connection attempt to the DB or if the communication got interrupted earlier - and how busy the DB was at the time when you've run into the error.</P> <P>&nbsp;</P> <P>Also consider to contact SAS Tech Support directly and get their guidance.</P> <P>&nbsp;</P> <P>Below the chatGPT answer:</P> <P>&nbsp;</P> <DIV class="flex-1 overflow-hidden"> <DIV class="react-scroll-to-bottom--css-ponfz-79elbk h-full"> <DIV class="react-scroll-to-bottom--css-ponfz-1n7m0yu"> <DIV class="flex flex-col pb-9 text-sm"> <DIV class="w-full text-token-text-primary" data-testid="conversation-turn-5"> <DIV class="px-4 py-2 justify-center text-base md:gap-6 m-auto"> <DIV class="flex flex-1 text-base mx-auto gap-3 md:px-5 lg:px-1 xl:px-5 md:max-w-3xl lg:max-w-[40rem] xl:max-w-[48rem] group final-completion"> <DIV class="relative flex w-full flex-col agent-turn"> <DIV class="flex-col gap-1 md:gap-3"> <DIV class="flex flex-grow flex-col max-w-full"> <DIV class="min-h-[20px] text-message flex flex-col items-start gap-3 whitespace-pre-wrap break-words [.text-message+&amp;]:mt-5 overflow-x-auto" data-message-author-role="assistant" data-message-id="3e37554a-4dbb-4cdf-8844-caa0d04ad9b2"> <DIV class="markdown prose w-full break-words dark:prose-invert light"> <P>If the communication link failure occurs intermittently, it can be more challenging to pinpoint the exact root cause since it suggests that the issue is not consistently reproducible. However, here are some possible root causes for intermittent communication link failures between a client and a database:</P> <OL> <LI> <P><STRONG>Network Fluctuations</STRONG>: Variations in network conditions such as intermittent packet loss, congestion, or bandwidth fluctuations can cause occasional communication failures.</P> </LI> <LI> <P><STRONG>Transient Hardware Issues</STRONG>: Temporary hardware malfunctions such as a loose cable, overheating components, or failing network equipment may intermittently disrupt communication.</P> </LI> <LI> <P><STRONG>Software Glitches</STRONG>: Transient software issues such as memory leaks, race conditions, or intermittent bugs in the client application or the database management system can cause intermittent communication failures.</P> </LI> <LI> <P><STRONG>Load Balancing or Failover Issues</STRONG>: If load balancing or failover mechanisms are in place, occasional misconfigurations or failures in these systems can lead to intermittent communication issues.</P> </LI> <LI> <P><STRONG>Concurrency Problems</STRONG>: In multi-threaded or concurrent environments, occasional race conditions or synchronization issues may lead to intermittent communication failures.</P> </LI> <LI> <P><STRONG>Resource Contentions</STRONG>: Intermittent contention for resources such as CPU, memory, or disk I/O between the client and the database server can cause intermittent communication failures, especially during periods of high load.</P> </LI> <LI> <P><STRONG>Environmental Factors</STRONG>: External factors such as electromagnetic interference, power fluctuations, or environmental conditions (e.g., temperature, humidity) can intermittently disrupt communication.</P> </LI> <LI> <P><STRONG>Intermittent Software Updates</STRONG>: If software updates or patches are applied periodically, intermittent compatibility issues between different versions of software components may cause occasional communication failures.</P> </LI> <LI> <P><STRONG>Transient Security Issues</STRONG>: Occasionally, security mechanisms such as firewalls, intrusion detection systems, or access control lists may intermittently block or interfere with communication between the client and the database server.</P> </LI> <LI> <P><STRONG>Temporary DNS Resolution Problems</STRONG>: Intermittent DNS resolution issues, such as DNS server outages or transient network latency, can occasionally prevent the client from resolving the database server's hostname.</P> </LI> <LI> <P><STRONG>Intermittent Client-Side Issues</STRONG>: Issues specific to the client environment, such as sporadic software conflicts, background processes consuming resources, or intermittent network connectivity on the client side, can cause intermittent communication failures.</P> </LI> </OL> <P>Identifying the root cause of intermittent communication link failures may require thorough monitoring, logging, and diagnostic efforts to capture the issue when it occurs and analyze relevant system and network parameters.</P> </DIV> </DIV> </DIV> </DIV> </DIV> </DIV> </DIV> </DIV> </DIV> </DIV> </DIV> </DIV> <DIV class="w-full pt-2 md:pt-0 dark:border-white/20 md:border-transparent md:dark:border-transparent md:w-[calc(100%-.5rem)]"><FORM class="stretch mx-2 flex flex-row gap-3 last:mb-2 md:mx-4 md:last:mb-6 lg:mx-auto lg:max-w-2xl xl:max-w-3xl"> <DIV class="relative flex h-full flex-1 flex-col"> <DIV class="flex w-full items-center">&nbsp;</DIV> </DIV> </FORM></DIV> Sun, 25 Feb 2024 04:34:41 GMT https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917752#M41076 Patrick 2024-02-25T04:34:41Z https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917765#M41077 <P>Please do <STRONG>ALWAYS</STRONG> post logs as text, using the appropriate window; it saves you the hassle of creating and uploading a screenshot, and makes it much easier to read for us. On top of it, we can easily make annotations.</P> <P>&nbsp;</P> <P>Now, less than 20 seconds of CPU time took 1026 seconds of real time (a factor of 50!), so there's most probably a network bottleneck involved. During that time, 819175 (close to <EM>1 million</EM>, which translates to at least 8 "lakh")&nbsp;observations were processed, so less than 1000/sec. What is your observation size, which variables do you have?</P> <P>Do you have the same rate of CPU time vs. real time in successful jobs?</P> <P>&nbsp;</P> <P>Also check with your DB admins if they can find something in their server logs.</P> Sun, 25 Feb 2024 08:25:41 GMT https://communities.sas.com/t5/New-SAS-User/SAS-DI-ETL-failed-due-to-Communication-link-failure/m-p/917765#M41077 Kurt_Bremser 2024-02-25T08:25:41Z