Hi Joachim
sorry it was an error when I masked some info
But when I executed the script TOM was replaced by right user,, (separatley)
I first executed this part (below) and after I run the pre-req sql
not a full script , so all worked fine
i do not receive any errors
thanks for you note and sorry for mismatch
Rob
GRANT CONNECT ON DATABASE TO USER MY_TECH_USER#
GRANT EXECUTE ON PACKAGE NULLID.SYSSH200 TO USER MY_TECH_USER#
GRANT CREATETAB ON DATABASE TO USER MY_TECH_USER#
GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD TO USER MY_TECH_USER#
GRANT IMPLICIT_SCHEMA ON DATABASE TO USER MY_TECH_USER#
GRANT USE OF TABLESPACE USERSPACE1 TO USER MY_TECH_USER#
GRANT CREATEIN,ALTERIN,DROPIN ON SCHEMA SYSTOOLS TO USER MY_TECH_USER#
GRANT EXECUTE ON PROCEDURE SYSIBM.SQLCAMESSAGECCSID TO USER MY_TECH_USER#
CREATE BUFFERPOOL BP4CONSOLE PAGESIZE 32K#
CREATE TABLESPACE TS4CONSOLE PAGESIZE 32K BUFFERPOOL BP4CONSOLE#
GRANT USE OF TABLESPACE TS4CONSOLE TO USER MY_TECH_USER#
CREATE TEMPORARY TABLESPACE TS4CONSOLE_TEMP PAGESIZE 32K BUFFERPOO
------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.comRome - ITALY
------------------------------
Original Message:
Sent: Fri May 15, 2020 04:16 AM
From: Joachim Müller
Subject: Database responsiveness monitoring
Hello Rob,
in your script dmcddl.txt you have replaced all TOM with MY_TECH_USER
But with that all AUTOMATIC were replaced too.
This changed all AU(TOM)ATIC to AUMY_TECH_USERATIC in the procedure IBM_RTMON.EVMON_PREREQ().
Unless you changed the script afterwards.
Best regards,
Joachim
------------------------------
Joachim Müller
Original Message:
Sent: Fri May 15, 2020 03:15 AM
From: Roberto Mandolini
Subject: Database responsiveness monitoring
Hi all
This is my report from 10.5.x (I masked some info)
I have this message
running CALL IBM_RTMON.EVMON_PREREQ;
I run the attached pre-req script whitout problem , I taked also some info from this
The only error that I can see is this
i replaced real tech user with fake MY_TECH_USER ( this user have SQLADM auth)
## 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: MY_TECH_USER".. SQLCODE=-1092, SQLSTATE= , DRIVER=4.26.14
### The error may exist in com/ibm/aps/tools/monitor/collector/mybatis/mapper/EventMonitorStaticsMapper.xml
### The error may involve com.ibm.aps.tools.monitor.collector.mybatis.mapper.EventMonitorStaticsMapper.queryFixedSize-Inline
### The error occurred while setting parameters
### SQL: select tbsp_type,tbsp_auto_resize_enabled,tbsp_max_size from sysibmadm.tbsp_utilization where tbsp_name = 'TS4MONITOR' fetch first 1 rows only
### 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: MY_TECH_USER".. SQLCODE=-1092, SQLSTATE= , DRIVER=4.26.14
Conclusion:
all seems to be fine ,, but I was not able to have Database responsiveness monitoring
Best regards
Rob
------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
Original Message:
Sent: Thu May 14, 2020 05:34 AM
From: XIAO MIN ZHAO
Subject: Database responsiveness monitoring
Hi Adrian,
Yes, there are lots of improvements for DMC event monitoring in next release. Thanks for feedback!
Hi Scott and Toby, thanks for your comments!
------------------------------
XIAO MIN ZHAO
Original Message:
Sent: Thu May 14, 2020 05:23 AM
From: Adrian Osterwalder
Subject: Database responsiveness monitoring
Ok, I understand... then is the check, that DMC does before activating the database responsiveness feature, not OK for DB2 10.5. Therefore DMC should eather the user notify about this feature is not supported for 10.5 databases or modify this check since we do not use pureScale. Maybe this would be considered in next version.
------------------------------
Adrian Osterwalder
Original Message:
Sent: Tue May 12, 2020 01:55 PM
From: TOBY HAYNES
Subject: Database responsiveness monitoring
In Db2 v10.5, db2_member() is specific to Db2 pureScale installations
The DB2_MEMBER and DB2_CF administrative views and DB2_GET_INSTANCE_INFO table function return information about the members and CFs of a DB2® pureScale® instance, including state information, where applicable.
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0056718.html
In Db2 v11.1, db2_member ()is extended to support other instance types:
<main role="main" aria-label="">
The DB2_MEMBER and DB2_CF administrative views and DB2_GET_INSTANCE_INFO table function return information about database members of single and multi-partitioned instances and the members and CFs of a Db2® pureScale® instance, including state information where applicable.
</main>
------------------------------
TOBY HAYNES
Original Message:
Sent: Tue May 12, 2020 09:47 AM
From: Scott Walkty
Subject: Database responsiveness monitoring
Hi Xiao Min,
From the documentation, sysibadm.db2_member should be supported on that level. I'm not personally aware of any issues with that function. If it is returning no rows, I would suggest opening a ticket with support to investigate.
Regards,
Scott
------------------------------
Scott Walkty
Original Message:
Sent: Mon May 11, 2020 11:22 PM
From: XIAO MIN ZHAO
Subject: Database responsiveness monitoring
For the sql select * from sysibmadm.db2_member returns no data on some db2 versions, does @Scott Walkty have any comments? Thanks Scott in advance!
------------------------------
XIAO MIN ZHAO
Original Message:
Sent: Mon May 11, 2020 03:24 AM
From: Adrian Osterwalder
Subject: Database responsiveness monitoring
Hi Kai,
Thank you for your reply. I can not agree with you that this release is a DEV build. It is the officially released formal 10.5 FP5 release (https://www.ibm.com/support/fixcentral/swg/doSelectFixes?options.selectedFixes=DB2-ntx64-universal_fixpack-10.5.500.107-FP005&continue=1). We have the same behavior on a DB2 10.5 server with FP7 installed (10.5.700.375). Right before, I installed the actual fix pack FP11 and the issue is the same. select * from sysibmadm.db2_member returns no lines and now I'm pretty sure that's not possible to get database responsiveness to work with a DB2 10.5.
------------------------------
Adrian Osterwalder
Original Message:
Sent: Sun May 10, 2020 11:21 PM
From: KAI DING
Subject: Database responsiveness monitoring
Hi Adrian,
As select * from sysibmadm.db2_member; returns nothing on the Db2 v10.5.500.107 build, I believe it's a dev Db2 build, not a formal one. Please use a formal Db2 build which could ensure Db2 quality.
From DMC perspective, the formal support Db2 version is V11.1 and above. Refer to:
https://www.ibm.com/software/reports/compatibility/clarity-reports/report/html/softwareReqsForProduct?deliverableId=0962CE30D85911E99EAFC519926DF897&duComponentIds=Server_3440B8C0AD6011E4BF417B0BC8E5108A
For monitoring, the supported version is V10.5.4 and above. As we can see, `v10.5.500.107` is satisfied, so there is no error/warning. But since it's a dev Db2 build, it's not working well. Again, please use formal Db2 version to avoid the confusion.
Hope these would be helpful. Thanks!
------------------------------
KAI DING
Original Message:
Sent: Fri May 08, 2020 02:33 AM
From: Adrian Osterwalder
Subject: Database responsiveness monitoring
Hi Xiao Min
Thank you very much for the information's. It helped me to get a deeper understanding of the issue. I've checked the two statements. The statement select count(id) from sysibmadm.db2_member; returns 0 (view is empty). But the bufferpool, the partition group and the tablespace where already there and therefore select count(member) from table(mon_get_tablespace('${tsName}', -2)); returns 1.
Yesterday evening, I upgraded a DEV system from DB2 10.5 to 11.5 because I saw the requirements for the DMC, to check, if the DB2 version is the reason for the issue. And the result of the test was, that the responsiveness widget works fine on 11.5 but not on DB2 v10.5.500.107. And now, because you gave me the statements, I ran select * from sysibmadm.db2_member; against both, the 10.5 and the 11.5 instances.
While on 11.5 the result looks like...
| ID | HOME_HOST | CURRENT_HOST | STATE | ALERT |
|---|
| 0 | cxw-tst25 | | | |
... the result on 10.5 returns nothing. Is that a bug from DB2 or a change between 10.5 to 11.5?
So in the end I can understand that 10.5 isn't officially supported by DMC 3.1.x and I didn't check that prior to the installation. But I cannot understand why there isn't a version check in DMC and an appropriate info anywhere in the UI.
------------------------------
Adrian Osterwalder
Original Message:
Sent: Thu May 07, 2020 10:57 PM
From: XIAO MIN ZHAO
Subject: Database responsiveness monitoring
Hi Adrian,
Database responsiveness depends on a statistics event monitor to collect data. But the statistics event monitor failed to be created as the prerequisites failed. "isDPFSpanAllMem":false caused the failure. The tablespace used to create event monitor is required to span all member so that it can collect all events running on the whole database members. The following 2 sqls are used by console to validate whether a tablespace is span all member: The 2 count numbers should match.
select count(id) from sysibmadm.db2_member;select count(member) from table(mon_get_tablespace('${tsName}', -2));Would you please check whether the tablespace you used for event monitor match the condition? You can try these following statements to recreate the objects. (TS4MONITOR is the default tablespace name for event monitors).
CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUTOMATIC PAGESIZE 32768;CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS;CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 500M BUFFERPOOL CONSOLEPOOL;
Hope these would be helpful. Thanks!
------------------------------
XIAO MIN ZHAO
Original Message:
Sent: Tue May 05, 2020 04:11 PM
From: Adrian Osterwalder
Subject: Database responsiveness monitoring
Hi
I'm completely new with DMC. I've installed version 3.1.1 for the first time on a new installed windows server. But I don't get the DB responsiveness monitoring to work.
In the target database, the schema and the stored procedures were created. But nothing else... so there are no tables and therefore the ATS tasks leading to an error. The EVMON_PREREQ stored procedure runs without any issue and the other statements for the workload also.
While reading in other threads in this forum, I've seen that the "/dbapi/v4/metrics/evmon/validate/HistogramsData?end=1588708105251&start=1588704505251" should return true for all "is..." values, but I see that "isDPFSpanAllMem" returns false for all of my databases.
full response:
{ "prerequisiteCheck": { "allSatisfied":false, "isTbspExist":true, "isPageSizeValid":true, "isTempTbspPagesizeValid":true, "isDPFSpanAllMem":false, "isFixedSizeValid":true, "isAtsEnabled":true, "isSystoolspaceExists":true, "hasExecProcPrivileges":true, "hasCreateTablePrivileges":true, "hasCreateEvmonPrivileges":true }, "isMonitorProfileEnabled":true, "isAtsUsedInMonitorProfile":true}
Maybe someone could tell me what's going wrong... I've spent several hours to find out what the issue could be.
------------------------------
Adrian Osterwalder
------------------------------
#Db2