I'm using DB2 for OS/390. I got headache with simple problem that I can't trace anymore.I'm doing the Cobol program but when i debug into the query, it's take a long time. My query as follows:
WHERE ASM_YR = :WS-ASM-YR
AND FK0_TAXP_REFNUM = :WS-REF-NUM
The tablespace creation specify LOCK SIZE ROW. Is it because of LOCK SIZE? When I check Explain table, my program using the index corretly. Anyone know about tablespace configuration? Or have I change the ISOLATION to RR?. I spent 3 days to debug this error but I not found the solution.
For your info, the table(TTSRTFM) have around 3 million of record. I don't think because of that it's take more than 2 minutes to read one record.
You did not exactly state what the index definition is that you think is being used correctly. That would help if you told us.
It is very hard to figure out what the problem is without more info, but here are some things to consider:
1. Reorg the table and all indexes on the table. Then do a runstats utility on the table and the indexes. Then rebind your program (package or plan).
2. You may have a lock contention problem with another program or utility. Row level locking and CS isolation level should be used to minimize the possibility of a problem. Are there any other programs or utilities running at the same time which may be causing contention on that table? What isolation level do the other programs use? Does the program always take the same amount of time to execute?
3. Make sure that your host variables are exactly the same data type and length as DB2 columns you reference in your SQL. You can use the DCLGEN to create the COBOL structure for the table with the correct column definitions.
4. If none of the above solves the problem, then I would consider using DB2 Performance Monitor if someone in your organization has that tool. There are other 3rd party tools that also may be used.
Just another thing about Marcus suggestion 3):
Check if your access path is using the index with matching columns = 0! If this is true, you're going through an index scan for each row and this could be very expensive.
Thank you very much. Your OPTION 3) is working. I miss out the last changes of data lenght. So, because of that my job running more then 12 hours rather then now(after changes host variable to same data lenght in reference table) just only take 5 minutes to successfully running the job.Unbelievable...thank you a alot..