Global Data Management Forum

Hello Everyone,

we are using Db2 11.5.9.0 + special_43682, we need to avoid some userid will run SQL queries with ACTIVITYTOTALTIME > 10 SECONDS. What we don't know are the APPLNAME the queries came from and all the userid that could run queries. What we know is that the userid we want to monitor start with the IT characters.

We have configured a THRESHOLD at the moment in COLLECT mode (in the final version we will change in STOP EXECUTION mode).

------------------------------
Roberto Stradella
------------------------------

The following workload connection attributes support the use of wild cards:

• APPLNAME
• CURRENT CLIENT_ACCTNG
• CURRENT CLIENT_APPLNAME
• CURRENT CLIENT_USERID
• CURRENT CLIENT_WRKSTNNAME
  
  

Specifying unique client attributes from the application server enables the specialized treatment of requests within Db2 and the assignment of requests from different clients to different workloads (and to different service classes).

Did you look into setting client attributes e.g CLIENT_USERID or CLIENT_ACCTNG for users IT* - for example by WLM SP WLM_SET_CLIENT_INFO called from logon procedure for users 'IT*'?

------------------------------
Jan Nelken
------------------------------

Original Message:
Sent: Tue October 22, 2024 06:30 AM
From: Roberto Stradella
Subject: ACTIVITYTOTALTIME Threshold

Hello Everyone,

we are using Db2 11.5.9.0 + special_43682, we need to avoid some userid will run SQL queries with ACTIVITYTOTALTIME > 10 SECONDS. What we don't know are the APPLNAME the queries came from and all the userid that could run queries. What we know is that the userid we want to monitor start with the IT characters.

We have configured a THRESHOLD at the moment in COLLECT mode (in the final version we will change in STOP EXECUTION mode).

CREATE THRESHOLD QUERY_RUNNING_10SEC

    FOR DATABASE ACTIVITIES

    ENFORCEMENT DATABASE

    WHEN ACTIVITYTOTALTIME > 10 SECONDS

    COLLECT ACTIVITY DATA WITH DETAILS, SECTION AND VALUES

    CONTINUE

;

 

and two EVENT MONITORs to save the information in table.

CREATE EVENT MONITOR STMT_THRESH_VIOLATIONS

  FOR THRESHOLD VIOLATIONS

  WRITE TO TABLE

  AUTOSTART

;

 

CREATE EVENT MONITOR COLLECT_ACTIVITIES

   FOR ACTIVITIES

   WRITE TO TABLE

;

With this configuration we are able to identify all the SQL queries with ACTIVITYTOTALTIME > 10 SECONDS that are run from every userid.

From the documentation I understand that to monitor via threshold a specific list of userid I have to configure a custom WORKLOAD and then apply the ACTIVITYTOTALTIME threshold to the WORKLOAD. But we got a problem: it seems that only APPLNAME accepts wildcard in the application names like APPLNAME('DS_ConnMgt*','DSMAu*','DSMOQT','DSMRt*','DSSNAP*','UC_*') instead SESSION_USER no.

Has anyone experienced my same problem ? Our goal is to configure a THRESHOLD that monitor SESSION_USER('IT*') only.

Any suggestion is welcome.

Thanks in advance. Ciao.

Roberto.

------------------------------
Roberto Stradella
------------------------------