Db2 (On Premises and Cloud)

Expand all | Collapse all

For lock history do we need to setup work load manager ?

  • 1.  For lock history do we need to setup work load manager ?

    Posted Wed January 08, 2020 12:42 PM
    ​to collect lock history do we need to setup work load manager ? do we need to run all the below statements provided in DMC message "Review and run theto make sure that your database satisfies all the prerequisites for event monitoring"   ?

    --NOTICE: please check whether there is 32kb pagesize temporary tablespace firstly, if not please create a new one.

    --create a bufferpool with page size 32kb
    CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUTOMATIC PAGESIZE 32768;
    --create a partition spannning all partitions
    CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS;
    --create the 32kb tablespace with max size 2G specified
    CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 2G BUFFERPOOL CONSOLEPOOL;
    --check whether there is 32kb pagesize temporary table space
    SELECT COUNT(1) FROM SYSCAT.TABLESPACES WHERE DATATYPE = 'T' AND PAGESIZE = 32768;
    --Don't create TEMPSPACE2 if there is already a 32kb pagesize temporary tablespace. If no 32kb pagesize temporary tablespace, create a required one.
    CREATE TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 BUFFERPOOL CONSOLEPOOL;

    --Define a workload to filter the monitored console executed statements. Because the COLLECT ACTIVITY DATA option is not specified, the statement history will not collect the statements of the workload.
    CREATE WORKLOAD DSM_WORKLOAD APPLNAME ('DSMAu*','DSMRt*','DS_ConnMgt*','DSSNAP*','DSMOQT','UC_*');
    --Capture the statements for the default two workloads on the coordinator node. If you want to collect the activity data for all nodes of a multi-partition database, change the COORDINATOR option to ALL.
    ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS;
    ALTER WORKLOAD SYSDEFAULTADMWORKLOAD COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS;
    --Capture the aggregate responsiveness workload information
    ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT AGGREGATE ACTIVITY DATA BASE;
    ALTER WORKLOAD SYSDEFAULTADMWORKLOAD COLLECT AGGREGATE ACTIVITY DATA BASE;

    ------------------------------
    srijith sudhakaran
    ------------------------------


  • 2.  RE: For lock history do we need to setup work load manager ?

    Posted Thu January 09, 2020 03:31 AM
    Edited by IBM Community 14 days ago


  • 3.  RE: For lock history do we need to setup work load manager ?

    Posted Thu January 09, 2020 08:37 AM
    Edited by Jessica Long 14 days ago

    Hi Roberto,

    If you want to collect event monitor data for some specified application, you can create a workload then contain application you wants, like this command:

    CREATE WORKLOAD WORKLOAD_XXX APPLNAME ('xxx*','xxx*','xxx*','xxx*','xxx','xxx*');

    then use alter workload command to collect related event monitor data you want, like this command:

    ALTER WORKLOAD WORKLOAD_XXX COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS;

    if you don't want to collect event monitor data for some applications, you also create a workload and include these applications and the COLLECT ACTIVITY DATA option should not specified

    I hope this answer can help you, thanks



    ------------------------------
    HAI LEI SONG
    ------------------------------



  • 4.  RE: For lock history do we need to setup work load manager ?

    Posted Fri January 10, 2020 02:25 AM
    Edited by IBM Community 14 days ago


  • 5.  RE: For lock history do we need to setup work load manager ?

    Posted 14 days ago
    Hi Roberto,

    From you description, I can see how you used the event monitor manually. Actually console uses the event monitor in a similar way.

    The purpose of statement "ALTER WORKLOAD WORKLOAD_XXX COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS" is to enable the specified workload to be captured by activity event monitor. That is , if you are only interested in some SQLs from certain workload, you can just enable that workload to be captured for activity event monitor, other workloads would be skipped, so that to save the event monitor cost.

    ------------------------------
    KAI DING
    ------------------------------



  • 6.  RE: For lock history do we need to setup work load manager ?

    Posted Thu January 09, 2020 08:38 AM

    Hi srijith,

    Lock event monitor don't need to setup workload manager ,thanks



    ------------------------------
    HAI LEI SONG
    ------------------------------



  • 7.  RE: For lock history do we need to setup work load manager ?

    Posted Mon January 13, 2020 12:33 PM
    Hi Lei
    I have the tablespaces created in the database we are monitoring 
    --create a bufferpool with page size 32kb
    CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUTOMATIC PAGESIZE 32768;
    --create a partition spannning all partitions
    CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS;
    --create the 32kb tablespace with max size 2G specified
    CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 2G BUFFERPOOL CONSOLEPOOL;

    i didnt create temp tablespace , sine there is already a 32k tempspace
     
    In the tool , under monitoring profile , under monitor settings , i have locking event monitor checked. i can see locks when it happens , but i don't see them when i check after few days ( Keep data for (days) is 14 )  i dont see the locks 

    for example i dont see the locks happened last week




    ------------------------------
    srijith sudhakaran
    ------------------------------



  • 8.  RE: For lock history do we need to setup work load manager ?

    Posted Tue January 14, 2020 04:51 AM
    Hi Srijith,

    When you mentioned "i can see locks when it happens", do you mean you can see the lock events in "locking event monitor" page? Also for "i dont see the locks happened last week", do you mean you cannot see them in "locking event monitor" page? If that is the case, it is possible, because when you choose a long historical time window (e.g., last month), there would be a lot of events. For performance consideration, console would use filter to get sampling data instead of full data.

    ------------------------------
    KAI DING
    ------------------------------



  • 9.  RE: For lock history do we need to setup work load manager ?

    Posted Tue January 14, 2020 01:29 PM
    Edited by IBM Community 14 days ago
    I have set the lock history 14 days, but i don't see the lock history

    ------------------------------
    srijith sudhakaran
    ------------------------------



  • 10.  RE: For lock history do we need to setup work load manager ?

    Posted Fri January 17, 2020 03:15 AM
    Hi Srijith,

    I don't quite get your point. When you mentioned "i can see locks when it happens" or "i don't see the lock history", do you mean on the "locking event monitor" page? Please help to clarify, thanks.

    ------------------------------
    KAI DING
    ------------------------------