Page 1 of 1

Datastage to Teradata Connectivity Issue

Posted: Mon May 13, 2013 9:46 am
by Praful
Currently we are facing teradata connectivity issue, we are using Datastage 8.5 to connect to Teradata (Version - 13.10.05.02). We are using complete URL to connect teradata server instead of IP address. There are around 200 jobs on Datastage which runs on every 4 hr, in every cycle 1 or 2 Jobs are failing due to teradata connectivity issues, when we rerun the job then it completes successfully. Now a days this issue is coming very frequently. We implemented ping shell script (running continuously) on Unix to check the if we get in the logs, but log does not showing any connectivity issue.

Below are the errors getting in the director, Please reply me if anyone has faced this type of issue and how you resoled it.


CDW_TblInsert,1: RDBMS code 207: MTDP: EM_NETCONN(207): Error in doing a network connect. SQL statement: INSERT INTO

CDW_TblInsert,3: The connector could not establish a connection to Teradata server (teradata server URL). RDBMS code 207:MTDP: EM_NETCONN(207): Error in doing a network connect. (CC_TeraConnection::connect, file CC_TeraConnection.cpp, line 845)

CDW_TblInsert,3: RDBMS code 207: MTDP: EM_NETCONN(207): Error in doing a network connect. SQL statement: INSERT INTO

CDW_TblInsert,1: The connector could not establish a connection to Teradata server (teradata server URL). RDBMS code 207:MTDP: EM_NETCONN(207): Error in doing a network connect. (CC_TeraConnection::connect, file CC_TeraConnection.cpp, line 845)


Thank you.

Posted: Mon May 13, 2013 11:10 am
by chulett
My first question would be - if you switch to using the IP address does the problem go away?

Posted: Mon May 13, 2013 11:11 am
by ray.wurlod
Do you have the Teradata Utilities installed on your DataStage engine?

Posted: Mon May 13, 2013 12:58 pm
by PaulVL
Are you having intermittend DNS issues?

Might not be on the datastage or TD side.

Formulate a timeline, see if it's always at a certain time of the day...

Did your DNS servers have maintenance during that timeframe?

Re: Datastage to Teradata Connectivity Issue

Posted: Mon May 13, 2013 2:16 pm
by Praful
We have implemented with IP address but still problem persists. Even getting more no of job failure as compared with URL.

There is no specific day or time jobs are failing. Also there is no maintenance on teradata. Each day we are running three cycles. For each cycle getting 1-2 times getting this connectivity issue.

Teradata Utilities installed on DataStage engine.

Posted: Mon May 13, 2013 3:06 pm
by ray.wurlod
You need to involve your Teradata DBA and your network administrator - clearly the problem is an intermittent network connection failing to be made.

Posted: Mon May 13, 2013 3:15 pm
by Praful
DBA also unable to see any problem. Today we have increased the Sync timeout from 300 to 600 second. Also opened a ticket to IBM support.

Posted: Mon May 13, 2013 7:17 pm
by PaulVL
Are you in a grid?

If the execution of the job always on the same server?

If you are on a grid and only "some" servers are failing, look at your /etc/hosts.


When you "rerun" are you following the same steps as the original execution? Or are you kicking it off manually via director/designer?

Is this behavior present in any of your other environments? DEV/QA or just PROD?

Posted: Mon May 13, 2013 10:11 pm
by Praful
This scenario we are facing only in PROD and very rarely in QA. No issue in Dev enviornment. We are using ZEKE schedular (on mainframe) for scheduling the job. So restart of the job is trough scheduler.

Can you please give more input on grid?

Posted: Wed May 15, 2013 7:22 am
by PaulVL
You "should" know if your datastage environment is executing in a GRID or Cluster environment. Do you ever see any APT_GRID_xxx variables?

The question I was posing was: Does your job ALWAYS run on the same datastage server?


If your problem is sometimes happening in QA than it's NOT unique to PROD and may reside outside of your DataStage system configuration. You shall need networking involved in troubleshoting this issue. What does your Information Server Admin think about the issue?


Try to replicate the issue outside of datastage yet on the same hardware.

It would be easy to write a bteq script to connect to Teradata and do nothing, then just test the return code of the bteq. Execute that in a loop.

Since this happens in QA, do your testing there.


You are probably hitting in more in PROD since you run more jobs in PROD. (and care more about bad runs)

Re: Datastage to Teradata Connectivity Issue

Posted: Thu May 16, 2013 4:33 am
by newie
Try Restarting the Database. We encountered the same Problem Connecting to Progress Database, a simple restart of database resolved the Issue. Let me know if that works for you.

Re: Datastage to Teradata Connectivity Issue

Posted: Thu May 30, 2013 10:18 pm
by Praful
Datastage executing on Cluster environment and not on Grid. As I said this issue we are facing on Production only, and not on QA or Dev server.

We implemented one shell script to ping database server but it is not helping to find the root cause, log is not showing any error. I will implement below BTEQ method mentioned by PaulVL.

For temp we have increased Sync time from 300 to 600 sec. Now only one time got connectivity issue in one week.

CDW_TblInsert,3: Sync timeout of 600 seconds expired while waiting for the other instances to reach state 1,005 (CC_TeraAdapter::waitForState, file CC_TeraAdapter.cpp, line 5,908

Posted: Wed Jul 31, 2013 1:55 am
by kandyshandy
Do you have any update on this? Did IBM or DBA find the root cause?

Posted: Wed Feb 26, 2014 12:30 pm
by attu
Praful - would you please like to share the resolution?

thanks

Posted: Thu Apr 17, 2014 10:33 am
by Praful
Sorry I missed to update the resolution last time.

We have contacted Teradata Support team and as per their suggestion we have added cop entries in host file (with the help of admin team). After adding cop entries we have not faced any Teradata connection issue till date.