Db2

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

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!

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

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

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!

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