[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

odbc timeout problem

Posted on 2003-11-07
13
Medium Priority
?
595 Views
Last Modified: 2012-06-21
Using DMX to create page that connects to our corp AS400 via ADO ODBC connection... here is the string that DMX created;

<%
Dim AIROUTBSHIP
Dim AIROUTBSHIP_numRows

Set AIROUTBSHIP = Server.CreateObject("ADODB.Recordset")
AIROUTBSHIP.ActiveConnection = MM_LTL400TAF_ASP_STRING
AIROUTBSHIP.Source = "SELECT * FROM BLAH"
AIROUTBSHIP.CursorType = 0
AIROUTBSHIP.CursorLocation = 2
AIROUTBSHIP.LockType = 1
AIROUTBSHIP.Open()

AIROUTBSHIP_numRows = 0
%>


When I test the connection I get the following error;

"[IBM][CLIENT ACCESS EXPRESS ODBC DRIVER(32-BIT)][DB2/400 SQL]SQL0666 - Estimated query processing time 1290 exceeds limit 30."

Is there a setting in DMX to override this 30 default or can I change the string to override? If so, how? I have looked at the ODBC administrator and see no option for changing the timeout setting there. Thanks!
0
Comment
Question by:MilburnDrysdale
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
13 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9701490
Please show me your code for where you create the connection. I don't think that you can set the timeout for the recordset object.

Fritz the Blank
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9701494
YOu should be able do something like this:

      MM_LTL400TAF_ASP_STRING.ConnectionTimeout = 40

Fritz the Blank
0
 

Assisted Solution

by:ScotterMonkey
ScotterMonkey earned 1000 total points
ID: 9701542
The problem may be with your ASP timeout. In IIS you can go to properties for a site, click on the HOME DIRECTORY tab, click on CONFIGURATION button, APP OPTIONS tab, and change session timeout to be a higher number.
PLEASE NOTE: This may not be a good way to deal with this problem. Problem might be the cursor choices combined with recordset result size might be taking too long to pull your data. I usually use code like this to read most recordsets, as long as you don't have to go backwards (rs.movefirst) in your set:

'set up conn object
Set conn = Server.CreateObject("ADODB.Connection")
ConnectionString = "DSN=shaolin"
conn.Open ConnectionString

'set up querystring
s = "SELECT"
s = s & " ID"
s = s & ",s_name_contact"
s = s & ",s_name_company"
s = s & ",s_title"
s = s & ",d_created"
s = s & ",s_state"
s = s & ",s_zip"
s = s & ",m_description"
s = s & ",s_type"
s = s & ",c_discount"
s = s & ",n_discount"
s = s & ",d_created"
s = s & " FROM coupons"
s = s & " WHERE ("
s = s & " ID_user=" & session("ID_user")
s = s & ")"
s = s & " ORDER BY " & s_sort
Set rs = Server.CreateObject("ADODB.Recordset")
rs.open s, conn, 0, 1 '0=forward only, 1=lock read only

I've found this method to be quite fast. I hear you can get even faster if you use a DSN-less connection. And of course faster still if you use SQL Server and then faster still if you use Stored Procedures with SQL Server.

Hope this helps some!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9701596
That is a good point--the code that I provided above will increase the timeout on your connection object, but if the process takes longer than 15 minutes, you may need to increase the timeout for the page.

Rather than doing on IIS, I prefer to do it just on the page to conserve resources. You can do that with this:

<%session.timeout=40%>

FtB
0
 

Author Comment

by:MilburnDrysdale
ID: 9701749
I've used the built-in DMX tools to set up  the connection. I created a system DSN in the ODBC adminstrator. You can then add that as an available "database" by selecting that DSN in a pull down menu, then create a recordset from that DSN (as you can see, I'm not a hand-coder). I am thinking this error is coming from the ODBC administrator and wondering if I should create a DSN-less connection where I can set the timeout. Any ideas on how to do this?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9701773
Two things:

1) If you go to the DMX tools, browse for available connections, find the connection in question, then you should be able to find a properties tab where you can adjust the time out value.

2) I don't know that there is an OLEDB driver for that database. Let me take a look.

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9701825
I haven't tested this (I can't) but this might work:


dim strConnectString  
dim objConnection

strConnectString = "Provider=IBMDA400;Data Source=" & SystemName & ";", "", ""

      set objConnection=Server.CreateObject("ADODB.Connection")
      objConnection.ConnectionTimeout = 15
      objConnection.CommandTimeout =  10
      objConnection.Mode = 3 'adModeReadWrite
      if objConnection.state = 0 then
            objConnection.Open strConnectString
      end if


Where system name is the name of your database.

FtB
0
 

Author Comment

by:MilburnDrysdale
ID: 9735378
Sorry for the late response (travelling this week)...I've tried all of the above and am still getting the same error message...
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9735421
So you were able to find the timeout propery of your connection object? If so, what did you set it to?

FtB
0
 

Author Comment

by:MilburnDrysdale
ID: 9735532
No...nothing in the ODBC administrator and nothing in DMX that I have found (poked around on their website,but found no references).
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 1000 total points
ID: 9735794
That is where the answer lies. I would recommend a call to their tech support department to find out how to get at this.

FtB
0
 

Author Comment

by:MilburnDrysdale
ID: 9776483
Sorry for the late response...hope you guys don't mind if I split the points...never really found an answer with DMX, but not sure their is one...think this is more of a AS 400 issue...thanks for your help!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9779354
Thank you and good luck!

FtB
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question