OdbcDataReader hangs on long query
<p>I am trying to extract data from iSeries database and create Excel report from the data. I am using C# and EPPlus library to do this. Below is my code that works to generate the repor:</p>

<pre><code>queryString = @"SELECT oacspo, pldesc, obivdt, oainno, obitno, obitd1, obitd2, obqtsh, obaslp, oblnam
              FROM myLib.hshed 
              LEFT JOIN myLib.cusms 
                ON oacsno = cmcsno
              LEFT JOIN myLib.hsdet
                ON oahssq = obhssq
              LEFT JOIN myLib.itemctrl
                ON obitno = icitno
              LEFT JOIN myLib.plinesj
                ON icline = plcode
              WHERE (oaipdt BETWEEN ? AND ?) AND icdlcu = 'Y' AND oacsno = ?
              ORDER BY pldesc, oainno, obitno";
          command = new OdbcCommand(queryString, connection);
          command.CommandTimeout = 0;
          parameter = command.Parameters.Add("@DateStart", OdbcType.Int);
          parameter.Value = startDate;
          parameter = command.Parameters.Add("@DateEnd", OdbcType.Int);
          parameter.Value = endDate;
          parameter = command.Parameters.Add("@CustomerNo", OdbcType.Int);
          parameter.Value = custNo;
          OdbcDataReader readerRpt = command.ExecuteReader();
</code></pre>

<p>That one runs ok but when I add one more specification to the WHERE clause (to narrow down to a specific department), I got an error:</p>

<pre><code>WHERE (oaipdt BETWEEN ? AND ?) AND icdlcu = 'Y' AND oacsno = ? AND icline = 'O'
</code></pre>

<blockquote>
  <p>Error: ERROR [HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666
  - SQL query exceeds specified time limit or storage limit.</p>
</blockquote>

<p>I did some research and changed the CommandTimeout properties to 0</p>

<pre><code>command.CommandTimeout = 0;
</code></pre>

<p>But as a result the program just hang when I execute the line</p>

<pre><code>OdbcDataReader readerRpt = command.ExecuteReader();
</code></pre>

<p>There is not a lot of data (about 1,000 rows) so I do not think that it exceeds storage limit. Or does it based on how many characters I can put in the query string? (which means I cannot have a long SQL statement).</p>

<p>Some directions on how to get this to work is really appreciated. Thank you</p>
