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

LOCKSIZE Parameter

Problem:

Our System has a segmented tablespace against which is accessed by multiple plans running at the same time. The concurrent plans try to insert and update the database at the same time...This causes lot of contention and occassionally our plans go down with -911 (deadlock condition)

Possible Solutions:

1) We could have the tablespace change to a Partitioned one to increase the parallelism.
2) Introduce -911 retry logic to have the plan wait for specified seconds and then try to access the tablespace.
3) Check the LOCKSIZE parameter of the tablespace definition...This parameter decides the degree of locks to be taken on tablespace be it page/row/table/Any...For better concurrency for random processing use LOCKSIZE = ROW...

We applied option 2 and 3 and all our concurrent plans are running better than before the change...

- Suresh Gangadharan

DB2 problem postings

This blog will primarily focus on the issues that I face in my work and also the solution for the problems that I fixed...Obviously, this would be updated only when I get a problem working in DB2...I pray to God that this one be updated the least, which means my work life is smooth :-)

One more thing is that I try to post up the situation and the scenario too along with each and every problem...So readers are advised to attempt the solution posted only if the scenario matches with theirs...Also the solutions posted in this forum are practically applied in my work...

- Suresh Gangadharan