I am facing problems with a select query in DB2.The query normally returns results in about 30 seconds when i run it,but sometimes it takes around 2 hrs to return the results.The query reads from a Datamart database and is populated by running batch scripts.
Is there any explanation for this kind of behaviour??
The version of DB2 i am using is DB2 v220.127.116.115
There can be numerous reasons why. Anything from poorly written SQL, lock contention, DB and DBM configuration issues, old or nonexistant runstats, etc, etc.
The first place I would start would be to generate access plans on the EXACT SQL running. This will let you know if you are doing table scans on the wrong tables. Also you need to monitor the system while queries are active to check for lock contention.