hi, We have encountered a serious DB2 Select problem that is almost stalling the user acceptance testing of the project in its tracks.
The problem is: we have user.sas_medsrv view which is a complex and big view and it is critical for almost all reports used in the project.
Currently, RADMPROD.MEDSRV_STATIC_FCT_CURR/HIST both are in TS_MDC1. All indexes and PK are built.
The following query returns in reasonable time (1-2 mins):
db2 "SELECT count(*) FROM user.sas_medsrv WHERE grp_num IN ( ‘002293316’,’ 004010339’)”
But the following query ran more than 7+ hrs last night!
db2 "SELECT count(*) FROM user.sas_medsrv WHERE grp_num IN ( SELECT grp_num FROM radmintr.sas_acct_grp_holder )"
The job status at time of kill was 'Pending remote request'.
The table ‘radmintr.sas_acct_grp_holder’ has only 2 rows. It is a SESSION table. The reason for using the SESSION table is: the number of group numbers can be large depending on user selection and when the number of items in the IN clause is more than about 50, we get 'where clause too complex' error. When we use the SESSION table, there was no problem.
The above query used to work earlier. However, recently a new column was added to the user.sas_medsrv view. And the problem started from then!