DB2 V8 Upgrade

Here is another problem which is still unresolved and it is haunting us for the past two days...Wonder! where in the world DB2 gets such strange problems like these...more it poses these kinda of problems more interesting it is...

Problem:

Our DB2 system was upgraded to Version 8...All our jobs were running fine these days until yesterday we noticed a problem with an Online app which accesses our database...Whenever we try to run the query through this Online app we are getting the following TCP/IP communication protocol error...We tried running the DB2 query in SPUFI which abended with the following hex abend...

Abend 0C4000 hex occurred processing command 'DSN '.,
***,

We checked out the DSNMSTR and we could repeatedly see the following error piling up in the DSNMSTR whenever we try to run the query against the DB2 databases.

- SERVER DISTRIBUTED AGENT WITH 485 LUWID=GA6FD43E.BF95.02BE01202707=33439
THREAD-INFO=userid:server:userid:dllhost.exe
RECEIVED ABEND=0C4
FOR REASON=00000000
- GA6FD43E.BF95.02BE01202707=33439 486
ACCESSING DATA FOR
LOCATION ipaddress
IPADDR ipaddress

Initially we thought that there is something wrong with the query...But the same query worked fine for a different search criteria which made us think that the DB2 objects used by the query might have some possible data corruption...Hence we did a CHECK PAGE on all the Tablespaces to see if any page of the tablespace is corrupted or not by running the Image copy jobs with CHECK PAGE option...But they didnt throw any error...We even tried out CHECK DATA and CHECK INDEX utilities against the Tablespaces/Indexspaces but no use...Still the page was abending with the communication protocol error pasted below....


Error Number: -2147168228
Error Description: SQL30081N
A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "ipaddress".
Communication function detecting the error: "recv". Protocol specific error code(s): "*", "*", "0". SQLSTATE=08001
Error Source: IBM OLE DB Provider for DB2

Query:

(Select
VT.COL1
,VT.COL2
,rtrim(ltrim(VT.COL3))
,Case(when VT.COL4 is null
then ' '
else VT.COL4
end)
from
(
Select
COL1
,COL2
,COL3
,COL4
from

T1 inner join T2
on T1.col1 = T2.col1
and T1.col2 = T2.col2
and T1.col1 = 1729

... where ..
)VT
)

Seeing the query we feel that it is having a problem during the sort phases in the buffer since when we run the inner query the response time is fast and no errors are observed. Also the additional predicate to the inner query works which is even more strange since both of the inner queries for the specific example that fails will never return and rows that would require the sort/merge to the outer portion of the SQL.

Solution:

Still the problem persists...Donno whats going on in the background of DB2...We have opened a ticket with IBM and concluded this problem as DB2 V8 problem as doing a search in the web...

http://www.dbtalk.net/ibm-software-db2-mvs/informatica-problem-after-db2-version-280956.html

Still our app gets this error...Thank God its a less frequently used Online App and users hardly give it a try which is welcome news for us...But surely this is haunting us and we are waiting on IBM for the fix....Once i have the fix from IBM i will post the solution to this problem by editing the article...

If anyone of u reading this article have faced similar problem while working in DB2 please post the comments on how you tackled it....

- Suresh Gangadharan