Original Message:
Sent: Thu May 16, 2024 04:55 AM
From: Roland Schock
Subject: DMC error in monitor.0
Hi Cintia,
sorry for the late response. I was too busy to allocate enough time to this issue.
The docs at https://www.ibm.com/docs/en/db2-data-mgr-console/3.1.x?topic=console-min-db-privileges-required-db2 are a bit shallow regarding the important difference of the "operational user" and the "data collection user". It would be great to make this important difference more visible in the DMC docs pages.
While trying to get the rights for the "data collection user" in the destination database, I stumbled over the requirement for SYSMAINT. It would be great, if the docs would explain detailed, what "few monitoring call" will be inoperative, and DMC should explicitly show on these panels "you must have SYSMAINT to see this panel", if the data collection user does not get the privileges.
Even if I crosscheck with https://www.ibm.com/docs/en/db2/11.5?topic=ownership-authorities-overview and https://www.ibm.com/docs/en/db2/11.5?topic=monitor-access-system-data-sysmon-authority I don't get an idea, what parts might be missing or what information DMC cannot show.
Needing SYSMAINT in a production environment is usually a killer argument against a monitoring solution for some customers.
And I did double check on the system, if my data collection user has required rights:
select substr(grantor,1,10) as GRANTOR,grantortype as GT, substr(grantee,1,20) as GRANTEE, GRANTEETYPE as GU, substr(schema,1,15) as SCHEMA,ROUTINETYPE as RT,EXECUTEAUTH as XA,count(*) from syscat.ROUTINEAUTH where grantee in ('MONDBUSR','PUBLIC') and schema='SYSPROC' group by GRANTOR,GRANTORTYPE,GRANTEE,GRANTEETYPE,SCHEMA,ROUTINETYPE,EXECUTEAUTH
GRANTOR GT GRANTEE GU SCHEMA RT XA 8
---------- -- -------------------- -- --------------- -- -- -----------
MONDBUSR U MONDBUSR U SYSPROC P Y 2
SYSIBM S PUBLIC G SYSPROC F G 170
SYSIBM S PUBLIC G SYSPROC F Y 17
SYSIBM S PUBLIC G SYSPROC P G 143
4 record(s) selected.
So it looks like public should be sufficient! [Ok, another issue, I have to work on! Its too much!]
I'm still checking, what privileges are missing. Maybe I can get temporarily SYSMAINT on a test machine to see the difference.
Cheers
------------------------------
Roland Schock
IBM Champion and IBM Gold Consultant
Original Message:
Sent: Thu May 02, 2024 10:33 AM
From: Cintia Ogura
Subject: DMC error in monitor.0
Hello Roland,
Can you please confirm if the user has the following privileges:
https://www.ibm.com/docs/en/db2-data-mgr-console/3.1.x?topic=console-min-db-privileges-required-db2
GRANT EXECUTE ON FUNCTION SYSPROC.* to user DMCUSR1;GRANT EXECUTE ON PROCEDURE SYSPROC.* to user DMCUSR1;
DMC uses stored procedures to run SQL statements.
That's why you need to have the privileges to execute SYSPROC routines.
I hope that helps!
------------------------------
Cintia Ogura
Original Message:
Sent: Thu May 02, 2024 10:17 AM
From: Roland Schock
Subject: DMC error in monitor.0
Hello,
I just noticed in monitor.0 log file the error below.
But after trying to execute the same statement (well, after some reformatting due to the format in the log file) on the destination machine, I could verify the named user has th according right on the database to monitor. Why do I get this error in monitor.0?
SQL: with unicode_fix(x) as (select null from sysibm.sysdummy1) select p.dbpartitionnum , u.utility_id , u.utility_type , (u.utility_start_time - HOUR(CURRENT TIMEZONE) HOURS - MINUTE(CURRENT TIMEZONE) MINUTES) as utility_start_time_timestamp , (p.progress_start_time - HOUR(CURRENT TIMEZONE) HOURS - MINUTE(CURRENT TIMEZONE) MINUTES) as progress_start_time_timestamp , varchar(substr2(u.utility_detail, 0, 2048)) as utility_description , null as reorg_end_timestamp , p.utility_state , u.utility_invoker_type , null as tabschema , null as tabname , null as data_partition_id , null as indschema , null as indname , null as reorg_type , u.utility_priority , null as reorg_phase , case when progress_total_units > 0 then (progress_completed_units * 100.0) / progress_total_units else null end as estimated_percent_complete , p.progress_completed_units , null as reorg_rowscompressed , null as reorg_rowsrejected , null as reorg_completion , p.progress_total_units , p.progress_description as progress_description , p.progress_work_metric , p.progress_seq_num --, u.progress_list_attr --, u.progress_list_cur_seq_num , u.object_type , u.object_schema , u.object_name , u.application_name , u.application_handle from table(mon_get_utility(-2)) u left outer join table(snap_get_util_progress(-2)) p on u.utility_id = p.utility_id and u.member = p.member -- where u.utility_dbname = current server where u.utility_type!='REORG' union all select r.dbpartitionnum , u.utility_id , 'REORG' as utility_type , (r.reorg_start - HOUR(CURRENT TIMEZONE) HOURS - MINUTE(CURRENT TIMEZONE) MINUTES) as utility_start_time_timestamp , (r.reorg_phase_start - HOUR(CURRENT TIMEZONE) HOURS - MINUTE(CURRENT TIMEZONE) MINUTES) as progress_start_time_timestamp , varchar(substr2(u.utility_detail, 0, 2048)) as utility_description , (r.reorg_end - HOUR(CURRENT TIMEZONE) HOURS - MINUTE(CURRENT TIMEZONE) MINUTES) as reorg_end_timestamp , r.reorg_status as utility_state , u.utility_invoker_type , r.tabschema as tabschema , r.tabname as tabname , r.data_partition_id , i.indschema as indschema , i.indname as indname , r.reorg_type , u.utility_priority , r.reorg_phase , case when r.reorg_max_counter > 0 then 100.0 * float(r.reorg_current_counter) / float(r.reorg_max_counter) when r.reorg_status = 'COMPLETED' then float(100) else float(0) end as estimated_percent_complete , null as progress_completed_units , r.reorg_rowscompressed , r.reorg_rowsrejected , case when r.reorg_end is null then null else r.reorg_completion end as reorg_completion , null as progress_total_units , null as progress_description , null as progress_work_metric , null as progress_seq_num --, null as progress_list_attr --, null as progress_list_cur_seq_num , u.object_type , u.object_schema , u.object_name , u.application_name , u.application_handle from table(snap_get_tab_reorg(null,-2)) r left outer join table(mon_get_utility(-2)) u on u.object_schema=r.tabschema and u.object_name=r.tabname and u.member = r.member and u.utility_type='REORG' left outer join syscat.indexes i on i.tabschema=r.tabschema and i.tabname=r.tabname and i.iid=r.reorg_index_id where r.reorg_end is null -- only get in-progress reorg
### Cause: com.ibm.db2.jcc.am.SqlException: The requested command or operation failed because the user ID does not have the authority to perform the requested command or operation. User ID: "MONDBUSR".. SQLCODE=-1092, SQLSTATE= , DRIVER=4.32.28
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: com.ibm.db2.jcc.am.SqlException: The requested command or operation failed because the user ID does not have the authority to perform the requested command or operation. User ID: "MONDBUSR".. SQLCODE=-1092, SQLSTATE= , DRIVER=4.32.28
### The error may exist in com/ibm/aps/tools/monitor/collector/mybatis/mapper/UtilitiesMapper.xml
### The error may involve defaultParameterMap
Kind regards
Roland Schock
------------------------------
Roland Schock
IBM Champion and IBM Gold Consultant
------------------------------