SQL0666 SQL query exceeds specified time limit or storage limit

133595 pts.
Tags:
.NET
AS/400
DB2
iSeries
SQL
I have a web service using a iDB2 connection to return a Data Set. I am searching a large table on a partial string to find a portion of a serial number, I kept getting this SQL0666 error so I had a new LF created over the file on the i-Series side by the text field I am searching on and it now runs in a second. My problem is it works fine over the TEST data with 14 million rows. When I change this SQL string to point to the LIVE database with 15 million rows the error comes back. I read the other post about setting the timeout to *nomax but did not see it as part of the connection.
   strSQL = "SELECT O5ORD#,O5BTKY,ODTEXT,OHSM FROM livedta032.O5M01 "
        strSQL &= "JOIN OHP ON O5ORD# = OHORD# "
        strSQL &= "WHERE ODTEXT Like " & "'" & "%" & strSerialNumber & "%" & "'" & " AND O5BTKY = " & "'" & strCustomerNumber & "'" & " "

        Dim myConnection As New iDB2Connection(strConnection)

        Dim da As New iDB2DataAdapter(strSQL, myConnection)
        Dim dsOrders As New DataSet()
        da.Fill(dsOrders)
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    I've seen it done two ways. First:
    iDB2Connection _conn = new iDB2Connection(ConnectionString, IpAddress, User, Password); 
    iDB2Command = _conn.CreateCommand(); 
    _cmd.CommandTimeout = 0; 
    _cmd.CommandType = CommandType.StoredProcedure; 
    // Now add any parameters...
    And second:
    iDB2Connection con = new iDB2Connection("DataSource=*<ipaddress>:<Port#>*;
    userid=test;password=test123;LibraryList=TEST2,*USRLIBL;DefaultCollection=TEST2;
    ConnectionTimeout=60;");
    I don't do .NET programming myself, so it's only from code I've reviewed. I can track down documentation if needed.
    35,610 pointsBadges:
    report
  • ToddN2000
    Found the issue. When I had the user create the new LF for me they apparently did not build the index!! I could query the file fine using QMQRY and WRKQRY. When I compared the live and test files I saw in the description the test access path was not valid. So I did a STRDFU in the file, the index was built and now everything is working fine! Thanks for the input Raven.
    133,595 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: