Connections Created (w/ ODBC/.NET) Are Surviving After conn.Close()

Connectivity

Connections Created (w/ ODBC/.NET) Are Surviving After conn.Close()

I have written a C# ASP.NET application that uses the Windows TD ODBC driver (v 13.10.00.10).  In my code, I create an open a connection, perform my query, then close the connection.  However, what we are seeing is that the connection (session) persists in Teradata for some arbitrary amount of time after the application has finished with the connection and closed it. My code is below. 

I'm trying to understand what happens on the Terdata side once an application closes a connection via ODBC.  Is there any way to explicitly end the session as soon as the application is done using it with ODBC?

Some of the connections disappear from DBC.SESSIONINFO in a matter of 10 seconds, some take 10-15 minutes before they finally disappear.

Any help would be most appreciated.

private void current_Way()
{
OdbcConnection conn = null;
try
{
String q = "SEL sessionno, username FROM DBC.SESSIONINFO WHERE USERNAME = 'my_user';";
Response.Write(q + "<br/>");

conn = new OdbcConnection("DSN=mydsn");
conn.Open();

OdbcCommand cmd = new OdbcCommand(q, conn);
OdbcDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
Response.Write(dr[0].ToString() + " " + dr[1].ToString() + "<br/>");
}

dr.Close();
conn.Close();
conn.Dispose();

Response.Write("Connection state? " + conn.State + "<br/>");
Response.Write("DataReader closed? " + dr.IsClosed);
}//end try
catch (Exception ex) { Response.Write(ex.Message); }
finally { }
}
3 REPLIES
Teradata Employee

Re: Connections Created (w/ ODBC/.NET) Are Surviving After conn.Close()

I recommend that you  switch to the .NET Data Provider for Teradata: http://downloads.teradata.com/download/connectivity/net-data-provider-for-Teradata

It is very difficult to debug Microsoft .NET Data Provider for ODBC.

Also as a general rule I recommend the "Using clause" (Automatic Dispose) for the Connection and the DataReader objects.

        private void current_Way()        
        {           
            try           
            {               
                String q = "SEL sessionno, username FROM DBC.SESSIONINFO WHERE USERNAME = 'my_user';";               
                Response.Write(q + "<br/>");

                using (OdbcConnection conn = new OdbcConnection("DSN=mydsn"))
                {
                    conn.Open();

                    OdbcCommand cmd = new OdbcCommand(q, conn);

                    using (OdbcDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            Response.Write(dr[0].ToString() + " " + dr[1].ToString() + "<br/>");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }           
            finally
            {
            }       
        }

Have you ruled out Connection Pooling? Microsoft ODBC Driver Manager supports connection pooling. The "Pool Timeout" defaults to 60 seconds.

Enthusiast

Re: Connections Created (w/ ODBC/.NET) Are Surviving After conn.Close()

ODBC by default doesn't do connection pooling, however the .Net Data Provider for Teradata does.  Connection pooling does have its pros and cons.  Personally I prefer the use of the .Net provider over ODBC due to the enhanced error messages and more detailed DBQL data that is logged.  If you don't which to use the connection pooling abilities of the .net provider you can turn it off.

//ODBC Example with no DSN used
    public static void current_WayODBC()
    {
      try
      {
        String q = "SEL sessionno, username FROM DBC.SESSIONINFO WHERE USERNAME = user;";
        Console.Write(q + "<br/>");
        using (OdbcConnection conn = new OdbcConnection("DRIVER=Teradata;DBCNAME=mysystem;UID=wamitef;PWD=Programming1;QUIETMODE=YES;"))
        {
          conn.Open();
          OdbcCommand cmd = new OdbcCommand(q, conn);
          using (OdbcDataReader dr = cmd.ExecuteReader())
          {
            while (dr.Read())
            {
              Console.Write(dr[0].ToString() + " " + dr[1].ToString() + "<br>");
            }
          }
        }
      }
      catch (Exception ex) { Console.Write(ex.Message); }
      finally { }
    }
//.Net Data Provider Example
    public static void current_WayDotNet()
    {
      try
      {
        String q = "SEL sessionno, username FROM DBC.SESSIONINFO WHERE USERNAME = user;";
        Console.Write(q + "<br/>");
        using (TdConnection conn = new TdConnection("Data Source=mysystem;User Id=wamitef;Password=Programming1;"))
        {
          conn.Open();
          TdCommand cmd = new TdCommand(q, conn);
          using (TdDataReader dr = cmd.ExecuteReader())
          {
            while (dr.Read())
            {
              Console.Write(dr[0].ToString() + " " + dr[1].ToString() + "<br>");
            }
          }
        }
      }
      catch (Exception ex) { Console.Write(ex.Message); }
      finally { }
    }
William Miteff

Re: Connections Created (w/ ODBC/.NET) Are Surviving After conn.Close()

After reinstalled tedadata database it is runing fine means i checked it on cmd prompt and it was showing started. After some time when i checked with this command pdestate -a it is showing 

PDE state is RUN/READY.

DBS state is 0/-1 :DBS is not running.

Please help me with this query.

Regards,

Vishnu