Problem executing long running query using ADO.Net and DB2

  • Thread starter Thread starter tukaram.thatikonda
  • Start date Start date
T

tukaram.thatikonda

Hi Guys,

I have written a small windows application in VB.Net to test ADO.Net
performance while executing long running query.

The query works most of the time but fails sometime. I am trying to
connect DB2 using federated database. The query returns around 25K
records from a total count of 10 million records based on criteria. It
takes roughly 20 mins to complete whenever the query works.

Following error is returned when the query fails: [DB2/NT] SQL1822N
Unexpected error code "-30081" received from data source "ECDTS".
Associated text and tokens are "10054 * 0 TCP/IP SOCKETS recv ".

Any help will be appreciated! I am not able to figure from where I
should start debugging this problem.

Many Thanks,
Ram
 
Hi,

I would say that 20 minutes is a pretty bad performance and I am sure it is
not a problem of ADO.NET. Most likely when you select records based on some
criteria, fields, involved into this criteria are not indexed in a database
and basically database engine loops through the whole table to find matching
records. Try to analyze it and build indexes. After that you should get
results in a seconds.
 
Hi Val,

Thanks for your reply. I agree the performance here has to do with
indexes not been created on the table. But I get the same error on
other queries too that takes not even a second. This error occurs few
times and not always. I need to know the real cause of the problem so
it can be fixed. I have seen similar error messages in other groups but
the replies point to creating federated database. The environment on
which these queries are executed is a federated database and so that is
definitely not the problem.

Thanks,
Ram
Hi,

I would say that 20 minutes is a pretty bad performance and I am sure it is
not a problem of ADO.NET. Most likely when you select records based on some
criteria, fields, involved into this criteria are not indexed in a database
and basically database engine loops through the whole table to find matching
records. Try to analyze it and build indexes. After that you should get
results in a seconds.
--
Val Mazur
Microsoft MVP

http://xport.mvps.org



Hi Guys,

I have written a small windows application in VB.Net to test ADO.Net
performance while executing long running query.

The query works most of the time but fails sometime. I am trying to
connect DB2 using federated database. The query returns around 25K
records from a total count of 10 million records based on criteria. It
takes roughly 20 mins to complete whenever the query works.

Following error is returned when the query fails: [DB2/NT] SQL1822N
Unexpected error code "-30081" received from data source "ECDTS".
Associated text and tokens are "10054 * 0 TCP/IP SOCKETS recv ".

Any help will be appreciated! I am not able to figure from where I
should start debugging this problem.

Many Thanks,
Ram
Click to expand...
Click to expand...
 
Hi,

Is it consistent with the specific queries or not?
--
Val Mazur
Microsoft MVP

http://xport.mvps.org



Hi Val,

Thanks for your reply. I agree the performance here has to do with
indexes not been created on the table. But I get the same error on
other queries too that takes not even a second. This error occurs few
times and not always. I need to know the real cause of the problem so
it can be fixed. I have seen similar error messages in other groups but
the replies point to creating federated database. The environment on
which these queries are executed is a federated database and so that is
definitely not the problem.

Thanks,
Ram
Hi,

I would say that 20 minutes is a pretty bad performance and I am sure it is
not a problem of ADO.NET. Most likely when you select records based on some
criteria, fields, involved into this criteria are not indexed in a database
and basically database engine loops through the whole table to find matching
records. Try to analyze it and build indexes. After that you should get
results in a seconds.
--
Val Mazur
Microsoft MVP

http://xport.mvps.org



Hi Guys,

I have written a small windows application in VB.Net to test ADO.Net
performance while executing long running query.

The query works most of the time but fails sometime. I am trying to
connect DB2 using federated database. The query returns around 25K
records from a total count of 10 million records based on criteria. It
takes roughly 20 mins to complete whenever the query works.

Following error is returned when the query fails: [DB2/NT] SQL1822N
Unexpected error code "-30081" received from data source "ECDTS".
Associated text and tokens are "10054 * 0 TCP/IP SOCKETS recv ".

Any help will be appreciated! I am not able to figure from where I
should start debugging this problem.

Many Thanks,
Ram
Click to expand...
Click to expand...
Click to expand...
 
Hi Val,

It is not really consistent with specific queries. Few new queries
after we discovered this problem last month failed with the same error.
SQL1822N Unexpected error code "-30081" received from data source
"WECDWTS3". Associated text and tokens are "10054 * 0 TCP/IP SOCKETS
recv ".
Even some of the earlier queries which were working now fails.
I suspect some process is issuing an interrupt that might be closing
the DB2 connection but not very sure which one.

Below is the query which works most of the times but fails at times

UPDATE LSTS.ACCT_REVIEW ACREV
SET LSTS_ACCT_STATUS ='Verified',
LSTS_ACCT_STAT_DT = CURRENT DATE
WHERE (DIVISION_NO = 3)
AND (BLOCK_NO = 3515)
AND (LOT_NO = 160)
AND (LSTS_ACCT_STATUS <> 'Verified')
AND (LSTS_ACCT_STATUS <> 'Collections Eligible')
AND (0 < (SELECT COUNT(*) FROM WECDWTS3.CSS_LIEN_DROP_RSN LDR
Where((VER_FLAG = 'Y') AND (DROP_FLAG <> 'Y')AND (YEAR(VER_CURR_DATE) =
YEAR(CURRENT DATE)) AND (LDR.ACCOUNT_NO = ACREV.ACCOUNT_NO)));

Values for column DIVISION_NO, BLOCK_NO AND LOT_NO are dynamically
assigned through string concatenation.

Please let me know if you can think of anything else.

Thank you,
Ram
Hi,

Is it consistent with the specific queries or not?
--
Val Mazur
Microsoft MVP

http://xport.mvps.org



Hi Val,

Thanks for your reply. I agree the performance here has to do with
indexes not been created on the table. But I get the same error on
other queries too that takes not even a second. This error occurs few
times and not always. I need to know the real cause of the problem so
it can be fixed. I have seen similar error messages in other groups but
the replies point to creating federated database. The environment on
which these queries are executed is a federated database and so that is
definitely not the problem.

Thanks,
Ram
Hi,

I would say that 20 minutes is a pretty bad performance and I am
Click to expand...
sure
it is
not a problem of ADO.NET. Most likely when you select records
Click to expand...
based
on some
criteria, fields, involved into this criteria are not indexed in a database
and basically database engine loops through the whole table to
Click to expand...
find
matching
records. Try to analyze it and build indexes. After that you
Click to expand...
should
get
results in a seconds.
--
Val Mazur
Microsoft MVP

http://xport.mvps.org



Hi Guys,

I have written a small windows application in VB.Net to test ADO.Net
performance while executing long running query.

The query works most of the time but fails sometime. I am trying to
connect DB2 using federated database. The query returns around 25K
records from a total count of 10 million records based on
Click to expand...
criteria.
It
takes roughly 20 mins to complete whenever the query works.

Following error is returned when the query fails: [DB2/NT] SQL1822N
Unexpected error code "-30081" received from data source "ECDTS".
Associated text and tokens are "10054 * 0 TCP/IP SOCKETS recv ".

Any help will be appreciated! I am not able to figure from where I
should start debugging this problem.

Many Thanks,
Ram
Click to expand...
Click to expand...
Click to expand...
 
Back
Top