I am having a problem with a row count query in v 4.1 running against DB2 z/OS v9. The query will run fine on a small result set but chokes on a bigger set. I have captured the query using ORM Profiler and nothing looks wrong with it.

SELECT COUNT(*) AS NumberOfRows
FROM   (SELECT "WELDIVDX"."FIN_TRANSACTION"."F_TRAN_ID" AS "TransactionId"
        FROM   "WELDIVDX"."FIN_TRANSACTION"
        WHERE  (("WELDIVDX"."FIN_TRANSACTION"."FR_ACCT_NUM" = '0000000040' /* ? */
                  OR "WELDIVDX"."FIN_TRANSACTION"."TO_ACCT_NUM" = '0000000040' /* ? */)
            AND ("WELDIVDX"."FIN_TRANSACTION"."F_TRAN_DT" BETWEEN '20120102' /* ? */ AND '20120107' /* ? */)
            AND ("WELDIVDX"."FIN_TRANSACTION"."F_TRAN_TYP_CD" <> 'ALOC' /* ? */
                  OR "WELDIVDX"."FIN_TRANSACTION"."TRAN_RMNG_AMT" > 0 /* ? */))) TmpResult

This same query will return a row count of approx 15K in 1.5 seconds when I run the query in IBM Data Studio. It will run without any issues on small row counts.

I get the same results when I replace the BETWEEN statement with >= and <= operators.

SELECT COUNT(*) AS NumberOfRows
FROM   (SELECT "WELDIVDX"."FIN_TRANSACTION"."F_TRAN_ID" AS "TransactionId"
        FROM   "WELDIVDX"."FIN_TRANSACTION"
        WHERE  (("WELDIVDX"."FIN_TRANSACTION"."FR_ACCT_NUM" = ?
                  OR "WELDIVDX"."FIN_TRANSACTION"."TO_ACCT_NUM" = ?)
            AND ("WELDIVDX"."FIN_TRANSACTION"."F_TRAN_DT" >= ?
                 AND "WELDIVDX"."FIN_TRANSACTION"."F_TRAN_DT" <= ?)
            AND ("WELDIVDX"."FIN_TRANSACTION"."F_TRAN_TYP_CD" <> ?
                  OR "WELDIVDX"."FIN_TRANSACTION"."TRAN_RMNG_AMT" > ?))) TmpResult 

Any ideas why this would be happening?

Needs a DB admin.

What code did you use to produce that SQL?

Indeed, it's unclear what exactly fails: choking means it's slow or that it crashes?

Sorry, I did not put enough specific information in my previous post.

The query fails with a timeout exception. ORM Profiler shows that the connection has been open for 55 seconds.

Code to get the rowcount looks like this.

 Dim fields As IEntityFields2 = New EntityFields2(1)
 fields.DefineField(field, 0)
 Dim dataAdapter As New DataAccessAdapter(GetConnectionString())
 toReturn = dataAdapter.GetDbCount(fields, bucket)

Exception details

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled
  HResult=-2146232832
  Message=An exception was caught during the execution of a retrieval query: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 165.176.44.160:33003. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  QueryExecuted=
    Query: SELECT COUNT(*) AS NumberOfRows FROM (SELECT "WELDIVDX"."FIN_TRANSACTION"."F_TRAN_ID" AS "TransactionId" FROM "WELDIVDX"."FIN_TRANSACTION" WHERE ( ( "WELDIVDX"."FIN_TRANSACTION"."FR_ACCT_NUM" = ? OR "WELDIVDX"."FIN_TRANSACTION"."TO_ACCT_NUM" = ?) AND ( "WELDIVDX"."FIN_TRANSACTION"."F_TRAN_DT" >= ? AND "WELDIVDX"."FIN_TRANSACTION"."F_TRAN_DT" <= ?) AND ( "WELDIVDX"."FIN_TRANSACTION"."F_TRAN_TYP_CD" <> ? OR "WELDIVDX"."FIN_TRANSACTION"."TRAN_RMNG_AMT" > ?))) TmpResult
    Parameter: @p1 : AnsiStringFixedLength. Length: 10. Precision: 0. Scale: 0. Direction: Input. Value: "0000000040".
    Parameter: @p2 : AnsiStringFixedLength. Length: 10. Precision: 0. Scale: 0. Direction: Input. Value: "0000000040".
    Parameter: @p3 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1/2/2012 12:00:00 AM.
    Parameter: @p4 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1/2/2012 12:00:00 AM.
    Parameter: @p5 : AnsiStringFixedLength. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: "ALOC".
    Parameter: @p6 : Decimal. Length: 0. Precision: 10. Scale: 2. Direction: Input. Value: 0.

  RuntimeBuild=12132013_WithAsync
  RuntimeVersion=4.1.0.0
  Source=DCSE.iAPECS.BusinessLogic
  StackTrace:
       at DCSE.iAPECS.BusinessLogic.EntityBizLogicBase.GetRowCount(IEntityField2 field, IRelationPredicateBucket bucket) in C:\VS-Projects\iAPECS\DCSE.iAPECS.BusinessLogic\EntityBL\EntityBizLogicBase.vb:line 932
       at DCSE.iAPECS.BusinessLogic.FinancialTransactionEntityBL.GetAccountStatementRowCount(String accountNumber, Nullable`1 startDate, Nullable`1 endDate) in C:\VS-Projects\iAPECS\DCSE.iAPECS.BusinessLogic\EntityBL\Custom\FinancialTransactionEntityBLCustom.vb:line 3614
       at DCSE.iAPECS.ConsoleApp.ProgramEntry.PerformanceTesting(String accountNumber, Nullable`1 startDate, Nullable`1 endDate) in C:\VS-Projects\iAPECS\DCSE.iAPECS.ConsoleApp\ProgramEntry.vb:line 146
       at DCSE.iAPECS.ConsoleApp.ProgramEntry.Main() in C:\VS-Projects\iAPECS\DCSE.iAPECS.ConsoleApp\ProgramEntry.vb:line 84
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Net.Sockets.SocketException
       ErrorCode=10060
       HResult=-2147467259
       Message=A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond XXX.XXX.XXX.XXX:33003
       NativeErrorCode=10060
       Source=System
       StackTrace:
            at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
            at System.Net.Sockets.Socket.Connect(EndPoint remoteEP)
            at System.Net.Sockets.TcpClient.Connect(IPEndPoint remoteEP)
            at System.Net.Sockets.TcpClient.Connect(IPAddress address, Int32 port)
            at DDInt.Common.c.a(String , Int32 )
            at DDTek.DB2.DRDA.o..ctor(bq , String , Int32 )
            at DDTek.DB2.bq.ba()
            at DDTek.DB2.bq.aq()
            at DDTek.DB2.DRDA.ao.b(Byte[] , Int32 , Int32 )
            at DDTek.DB2.DRDA.ao.ad()
            at DDTek.DB2.DRDA.bm.g(Int32 )
            at DDTek.DB2.DRDA.bm.a0(Int32 )
            at DDTek.DB2.DB2Command.a(CommandBehavior , Boolean )
            at DDTek.DB2.DB2Command.ExecuteScalar()
            at SD.Tools.OrmProfiler.Interceptor.ProfilerDbCommand.ExecuteScalar()
            at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.ExecuteScalar() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 137
       InnerException: 

Console output on this with a small rowcount result vs a large rowcount result.

Enter the account number:
2008
Start date:
1/1/2009
End date:
3/1/2014
Row count: 67
Run time: 74 ms




Enter the account number:
40
Start date:
1/2/2012
End date:
1/7/2012
(timeout exception thrown here)

Yes, I can talk to our DBA about this but I'd rather look hard at my application first when it is appears to be such a simple query.

In your first post you used a BETWEEN operator, and at your last post you used <= >=, I guess that there is no difference, right?

Now, What if you run the queries on your query editor? Do you get similar results? And How would you do it instead? Indeed maybe your DBA should look into the query and evaluate the query plan to understand what should be changed in your indexes or in your filter bucket.

Isn't it a connection error? See the inner exception:

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond XXX.XXX.XXX.XXX:33003

Also if you use the non-async call, does it succeed then? It might be the async code in the DB2 provider you're using isn't up to par (yet)

The connection only fails when I change the predicate in such a way that the row count is relatively high.

I'll take a look at the non-async call.