Datastage to Teradata Connectivity Issue

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Praful
Participant
Posts: 14
Joined: Fri Apr 12, 2013 10:29 pm
Location: United States

Datastage to Teradata Connectivity Issue

Post 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.
Regards,
Praful
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

My first question would be - if you switch to using the IP address does the problem go away?
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Do you have the Teradata Utilities installed on your DataStage engine?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post 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?
Praful
Participant
Posts: 14
Joined: Fri Apr 12, 2013 10:29 pm
Location: United States

Re: Datastage to Teradata Connectivity Issue

Post 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.
Regards,
Praful
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post 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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Praful
Participant
Posts: 14
Joined: Fri Apr 12, 2013 10:29 pm
Location: United States

Post 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.
Regards,
Praful
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post 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?
Praful
Participant
Posts: 14
Joined: Fri Apr 12, 2013 10:29 pm
Location: United States

Post 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?
Regards,
Praful
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post 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)
newie
Participant
Posts: 2
Joined: Sat Aug 11, 2012 1:50 am

Re: Datastage to Teradata Connectivity Issue

Post 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.
regards..
Praful
Participant
Posts: 14
Joined: Fri Apr 12, 2013 10:29 pm
Location: United States

Re: Datastage to Teradata Connectivity Issue

Post 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
Regards,
Praful
kandyshandy
Participant
Posts: 597
Joined: Fri Apr 29, 2005 6:19 am
Location: Singapore

Post by kandyshandy »

Do you have any update on this? Did IBM or DBA find the root cause?
Kandy
_________________
Try and Try again…You will succeed atlast!!
attu
Participant
Posts: 225
Joined: Sat Oct 23, 2004 8:45 pm
Location: Texas

Post by attu »

Praful - would you please like to share the resolution?

thanks
Praful
Participant
Posts: 14
Joined: Fri Apr 12, 2013 10:29 pm
Location: United States

Post 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.
Regards,
Praful
Post Reply