Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Malaysia
    Posts
    2

    Unanswered: Query statement too slow DB2 OS/390

    Hi All,

    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:
    SELECT MAIN_BUS_CD
    INTO :WS-BUS-CD
    FROM TTSRTFM
    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  3. #3
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    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.
    Rodney Krick

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    We could probably tell what the matching columns are if we knew the exact definition of the index(es) on the table.

  5. #5
    Join Date
    Sep 2003
    Location
    Malaysia
    Posts
    2
    Hi Marcus/RKrick,

    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..

    Regards,
    MSANI
    Malaysia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •