Topic
No replies
SystemAdmin
SystemAdmin
9 Posts
ACCEPTED ANSWER

Pinned topic Query Workload Tuner 3.1 and lock timeout

‏2012-02-14T12:34:51Z |
Hello,

I'am currently play with the data studio 3.1 and the query workload tuner (from passport advantage). The test database ist fully licensed.

BTW: someone should change the installer (database enabler on server). The installer only works with one database. For additionally databases, the shipped scripts must be altered and called manually.

I currently use DB2 9.5 FP8 64-Bit on Windows 2008 R2

Now one question and my real problem:

Why cannot use the DB2 buildin function to collect WLM workloads ?

F.e. http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.wlm.doc/doc/c0053139.html
My problem:

Workload, captured from the package cache may or may not ru into a lock timeout. Look into the attached trace file. Sometimes, it help in removing all "SYSTOOLS" queries from the statement list, but not always.

The lock owner is always "SELECT T.STMT_TEXT,T.STMT_TEXT_LONG,S.TYPE,S.NAME,I.INSTID,I.STMT_TEXT_ID,I.WLID,I.SRCID,I.EXPLAIN_STATUS,I.LAST_EXPLAIN_TS,I.PKGSCHEMA,I.PKGNAME,I.STMTNO,I.OWNER,I.DEFAULT_SCHEMA,I.UNIQUE_ID,I.TOTAL_SECT,I.ISOLATION,I.FUNC_PATH,I.QUERYOPT,I.EXPLAIN_LEVEL,I.EXPLAIN_MODE,I.EXPLICIT_BIND_TIME,I.LAST_BIND_TIME,I.DEGREE,I.DYNAMICRULES,I.REFRESHAGE,I.REOPTVAR,I.PKGVERSION,I.OPTPROFILESCHEMA,I.OPTPROFILENAME,I.LASTUSED,I.MEMBER,I.SECTION_TYPE,I.EXECUTABLE_ID,I.SECTION_NUMBER,I.NUM_EXECUTIONS,I.VALID,I.STMT_TYPE_ID,I.STMT_PKG_CACHE_ID,I.MAINTAINED_TABLE_TYPE,I.ROUTINESCHEMA,I.ROUTINEMODULENAME,I.ROUTINENAME,I.ROUTINETYPE,I.SPECIFICNAME,I.DETERMINISTIC,I.EXTERNAL_ACTION,I.NULLCALL,I.CREATE_TIME,R.NUM_EXEC_WITH_METRICS,(CAST(R.STMT_EXEC_TIME AS DOUBLE) / NULLIF(R.NUM_COORD_EXEC_WITH_METRICS, 0)) AS AVG_STMT_EXEC_TIME,(CAST(R.TOTAL_CPU_TIME AS DOUBLE) / NULLIF(R.NUM_COORD_EXEC_WITH_METRICS, 0)) AS AVG_CPU_TIME,(CAST(R.LOCK_WAIT_TIME AS DOUBLE) / NULLIF(R.NUM_COORD_EXEC_WITH_METRICS, 0)) AS AVG_LOCK_WAIT_TIME,(CAST((R.POOL_READ_TIME + R.POOL_WRITE_TIME + R.DIRECT_READ_TIME + R.DIRECT_WRITE_TIME) AS DOUBLE) / NULLIF(R.NUM_COORD_EXEC_WITH_METRICS, 0)) AS AVG_IO_WAIT_TIME,(CAST(R.ROWS_READ AS DOUBLE) / NULLIF(R.ROWS_RETURNED, 0)) AS ROWS_READ_PER_ROWS_RETURNED,R.EFFECTIVE_ISOLATION,R.PREP_TIME,R.TOTAL_ACT_TIME,R.TOTAL_CPU_TIME,R.POOL_READ_TIME,R.POOL_WRITE_TIME,R.DIRECT_READ_TIME,R.DIRECT_WRITE_TIME,R.LOCK_WAIT_TIME,R.TOTAL_SECTION_SORT_TIME,R.TOTAL_SECTION_SORT_PROC_TIME,R.TOTAL_SECTION_SORTS,R.ROWS_MODIFIED,R.ROWS_READ,R.ROWS_RETURNED,R.TOTAL_SORTS,R.NUM_COORD_EXEC,R.NUM_COORD_EXEC_WITH_METRICS,R.TOTAL_ROUTINE_TIME,R.TOTAL_ROUTINE_INVOCATIONS,R.QUERY_COST_ESTIMATE,R.COORD_STMT_EXEC_TIME,R.STMT_EXEC_TIME FROM SYSTOOLS.QT_WCC_STMT_INSTANCE AS I INNER JOIN SYSTOOLS.QT_WCC_STMT_TEXT AS T ON I.STMT_TEXT_ID = T.STMT_TEXT_ID AND I.WLID = ? LEFT OUTER JOIN SYSTOOLS.QT_WCC_WORKLOAD_SOURCE AS S ON S.SRCID = I.SRCID LEFT OUTER JOIN SYSTOOLS.QT_WCC_STMT_RUNTIME_METRICS AS R ON I.INSTID = R.INSTID FETCH FIRST 1000 ROWS ONLY OPTIMIZE FOR 1000 ROWS FOR READ ONLY"
Regards,

Matthias Zahn