I'm setting new repository  for DMC 3.1.2 (totally new installation)
I received this error
Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements.
test connections work fine
I used this grants option before run the option of setting repository

GRANT CONNECT ON DATABASE TO USER MY_USER;
GRANT SQLADM ON DATABASE TO USER MY_USER;
GRANT EXECUTE ON PACKAGE NULLID.SYSSH200 TO USER MY_USER;
GRANT CREATETAB ON DATABASE TO USER MY_USER;
GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD TO USER MY_USER;
GRANT IMPLICIT_SCHEMA ON DATABASE TO USER MY_USER;
GRANT USE OF TABLESPACE USERSPACE1 TO USER MY_USER;
GRANT CREATEIN,ALTERIN,DROPIN ON SCHEMA SYSTOOLS TO USER MY_USER;
GRANT EXECUTE ON PROCEDURE SYSIBM.SQLCAMESSAGECCSID TO USER MY_USER;
CREATE BUFFERPOOL BP4CONSOLE PAGESIZE 32K;
CREATE TABLESPACE TS4CONSOLE PAGESIZE 32K BUFFERPOOL BP4CONSOLE;
GRANT USE OF TABLESPACE TS4CONSOLE TO USER MY_USER;
CREATE TEMPORARY TABLESPACE TS4CONSOLE_TEMP PAGESIZE 32K BUFFERPOOL BP4CONSOLE;


I replaced the real user with my_user

Ho i can fix this error?

Thanks in advance fro support

------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------

#Db2

i try to run in batch mode the create repository
I found a lot of these error

I replaced the real instance owner id with DB2_INSTANCE_OWNER

CREATE OR REPLACE VIEW BOTTLE_LOCK_WAIT AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLockWait") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOCK_WAIT". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_LOG_SPACE AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLogSpace") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOG_SPACE". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_MEMORY AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleMemory") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_MEMORY". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_NUM_LOCKS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleNumLocks") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_NUM_LOCKS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_QUERY_COST AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleQueryCost") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_QUERY_COST". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_MOD AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsMod") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_MOD". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_READ AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsRead") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_READ". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_RETURNED AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsReturned") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_RETURNED". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORT_OVERFLOWS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSortOverflows") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_SORT_OVERFLOWS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORTS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSorts") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object: "IBMCONSOLE.BOTTLE_SORTS".
SQLSTATE=42501

------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------

SOLVED! 
sqladm as user is not enough to create repository
Temporary dbadm assigned to user
I'm checking installation

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------

Original Message:
Sent: Thu June 25, 2020 03:53 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

i try to run in batch mode the create repository
I found a lot of these error

I replaced the real instance owner id with DB2_INSTANCE_OWNER

CREATE OR REPLACE VIEW BOTTLE_LOCK_WAIT AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLockWait") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOCK_WAIT". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_LOG_SPACE AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLogSpace") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOG_SPACE". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_MEMORY AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleMemory") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_MEMORY". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_NUM_LOCKS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleNumLocks") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_NUM_LOCKS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_QUERY_COST AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleQueryCost") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_QUERY_COST". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_MOD AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsMod") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_MOD". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_READ AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsRead") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_READ". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_RETURNED AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsReturned") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_RETURNED". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORT_OVERFLOWS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSortOverflows") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_SORT_OVERFLOWS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORTS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSorts") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object: "IBMCONSOLE.BOTTLE_SORTS".
SQLSTATE=42501

------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------

Sorry , another issue enabling 

Enable statistics event monitoring to collect query run-time statistics events

Fail to create tablespace due to: "MY_TECH_USER" does not have the privilege to perform operation "CREATE TABLESPACE".. SQLCODE=-552, SQLSTATE=42502, DRIVER=4.26.14

so do I have to create the objects manually?  (I found it on another discussion/post)

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;​

for V3.1.1 enabling collection use these statements
Create the event monitor prerequisite stored procedure.
--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE IBM_RTMON.EVMON_PREREQ()
SPECIFIC IBM_RTMON.EVMON_PREREQ
LANGUAGE SQL
BEGIN
DECLARE RESULT INTEGER;
DECLARE STMT VARCHAR(1000);
--Create a bufferpool with page size 32kb.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'CONSOLEPOOL';
IF (RESULT = 0) THEN
SET STMT = 'CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUMY_TECH_USERATIC PAGESIZE 32768';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a partition spannning all partitions.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.DBPARTITIONGROUPS WHERE DBPGNAME = 'CONSOLEGROUP';
IF (RESULT = 0) THEN
SET STMT = 'CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create the 32kb tablespace with max size 2G specified.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'TS4MONITOR';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUMY_TECH_USERATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 2G BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a 32kb pagesize temporary tablespace if there is not an existing one.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE DATATYPE = 'T' AND PAGESIZE = 32768;
IF (RESULT = 0) THEN
SET STMT = 'CREATE TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K MANAGED BY AUMY_TECH_USERATIC STORAGE EXTENTSIZE 4 BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Disable the legacy console workload DSM_WORKLOAD if it exists.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'DSM_WORKLOAD' AND ENABLED = 'Y';
IF (RESULT = 1) THEN
SET STMT = 'ALTER WORKLOAD DSM_WORKLOAD DISABLE';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--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.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'CONSOLE_WORKLOAD';
IF (RESULT = 0) THEN
SET STMT = 'CREATE WORKLOAD CONSOLE_WORKLOAD APPLNAME (''DSMAu*'',''DSMRt*'',''DS_ConnMgt*'',''DSSNAP*'',''DSMOQT'',''UC_*'')';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create SYSTOOLSPACE for Administrative Task Schedule(ATS).
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'SYSTOOLSPACE';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUMY_TECH_USERATIC STORAGE USING STOGROUP IBMSTOGROUP EXTENTSIZE 4';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
END@
--#SET TERMINATOR ;
--Apply the event monitor prerequisite stored procedure.
CALL IBM_RTMON.EVMON_PREREQ;
--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;
--Ensure you have ADMIN_TASK_ADD and ADMIN_TASK_UPDATE privileges to execute stored procedures(ATS).
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_ADD TO USER MY_TECH_USER;
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_UPDATE TO USER MY_TECH_USER;
grant use of tablespace TS4MONITOR TO USER MY_TECH_USER;

are still valid for V3.1.2 or something is changed?

Thanks for support

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------

Original Message:
Sent: Thu June 25, 2020 04:37 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

SOLVED! 
sqladm as user is not enough to create repository
Temporary dbadm assigned to user
I'm checking installation

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------


Original Message:
Sent: Thu June 25, 2020 03:53 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

i try to run in batch mode the create repository
I found a lot of these error

I replaced the real instance owner id with DB2_INSTANCE_OWNER

CREATE OR REPLACE VIEW BOTTLE_LOCK_WAIT AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLockWait") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOCK_WAIT". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_LOG_SPACE AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLogSpace") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOG_SPACE". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_MEMORY AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleMemory") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_MEMORY". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_NUM_LOCKS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleNumLocks") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_NUM_LOCKS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_QUERY_COST AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleQueryCost") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_QUERY_COST". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_MOD AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsMod") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_MOD". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_READ AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsRead") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_READ". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_RETURNED AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsReturned") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_RETURNED". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORT_OVERFLOWS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSortOverflows") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_SORT_OVERFLOWS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORTS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSorts") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object: "IBMCONSOLE.BOTTLE_SORTS".
SQLSTATE=42501

------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY

Hello Roberto,

For DMC repository, the privilege requirement is described in the knowledge center.  You can find the details in this page.

The privilege requirement to configure the pre-requisite for event monitor on monitor database is currently not documented in our  knowledge center page  We will add them shortly after this post. 

To help answer your questions, the following privilege requirement is required to accomplish the task.
SYSCTRL or SYSADM privilege is required to create BUFFERPOOL and TABLESPACE

WLMADM or DBADM privilege is required to CREATE / ALTER WORKLOAD

ACCESSCTRL or SECADM privilege is required to GRANT EXECUTE on PROCEDURE

SYSADM privilege is required to db2set

------------------------------
Jason Sizto
------------------------------

Original Message:
Sent: Thu June 25, 2020 06:14 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

Sorry , another issue enabling 

Enable statistics event monitoring to collect query run-time statistics events

Fail to create tablespace due to: "MY_TECH_USER" does not have the privilege to perform operation "CREATE TABLESPACE".. SQLCODE=-552, SQLSTATE=42502, DRIVER=4.26.14

so do I have to create the objects manually?  (I found it on another discussion/post)

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;​

for V3.1.1 enabling collection use these statements
Create the event monitor prerequisite stored procedure.
--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE IBM_RTMON.EVMON_PREREQ()
SPECIFIC IBM_RTMON.EVMON_PREREQ
LANGUAGE SQL
BEGIN
DECLARE RESULT INTEGER;
DECLARE STMT VARCHAR(1000);
--Create a bufferpool with page size 32kb.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'CONSOLEPOOL';
IF (RESULT = 0) THEN
SET STMT = 'CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUMY_TECH_USERATIC PAGESIZE 32768';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a partition spannning all partitions.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.DBPARTITIONGROUPS WHERE DBPGNAME = 'CONSOLEGROUP';
IF (RESULT = 0) THEN
SET STMT = 'CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create the 32kb tablespace with max size 2G specified.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'TS4MONITOR';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUMY_TECH_USERATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 2G BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a 32kb pagesize temporary tablespace if there is not an existing one.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE DATATYPE = 'T' AND PAGESIZE = 32768;
IF (RESULT = 0) THEN
SET STMT = 'CREATE TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K MANAGED BY AUMY_TECH_USERATIC STORAGE EXTENTSIZE 4 BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Disable the legacy console workload DSM_WORKLOAD if it exists.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'DSM_WORKLOAD' AND ENABLED = 'Y';
IF (RESULT = 1) THEN
SET STMT = 'ALTER WORKLOAD DSM_WORKLOAD DISABLE';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--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.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'CONSOLE_WORKLOAD';
IF (RESULT = 0) THEN
SET STMT = 'CREATE WORKLOAD CONSOLE_WORKLOAD APPLNAME (''DSMAu*'',''DSMRt*'',''DS_ConnMgt*'',''DSSNAP*'',''DSMOQT'',''UC_*'')';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create SYSTOOLSPACE for Administrative Task Schedule(ATS).
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'SYSTOOLSPACE';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUMY_TECH_USERATIC STORAGE USING STOGROUP IBMSTOGROUP EXTENTSIZE 4';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
END@
--#SET TERMINATOR ;
--Apply the event monitor prerequisite stored procedure.
CALL IBM_RTMON.EVMON_PREREQ;
--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;
--Ensure you have ADMIN_TASK_ADD and ADMIN_TASK_UPDATE privileges to execute stored procedures(ATS).
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_ADD TO USER MY_TECH_USER;
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_UPDATE TO USER MY_TECH_USER;
grant use of tablespace TS4MONITOR TO USER MY_TECH_USER;

are still valid for V3.1.2 or something is changed?

Thanks for support

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------


Original Message:
Sent: Thu June 25, 2020 04:37 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

SOLVED! 
sqladm as user is not enough to create repository
Temporary dbadm assigned to user
I'm checking installation

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY


Original Message:
Sent: Thu June 25, 2020 03:53 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

i try to run in batch mode the create repository
I found a lot of these error

I replaced the real instance owner id with DB2_INSTANCE_OWNER

CREATE OR REPLACE VIEW BOTTLE_LOCK_WAIT AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLockWait") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOCK_WAIT". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_LOG_SPACE AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLogSpace") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOG_SPACE". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_MEMORY AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleMemory") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_MEMORY". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_NUM_LOCKS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleNumLocks") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_NUM_LOCKS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_QUERY_COST AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleQueryCost") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_QUERY_COST". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_MOD AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsMod") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_MOD". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_READ AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsRead") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_READ". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_RETURNED AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsReturned") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_RETURNED". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORT_OVERFLOWS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSortOverflows") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_SORT_OVERFLOWS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORTS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSorts") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object: "IBMCONSOLE.BOTTLE_SORTS".
SQLSTATE=42501

------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY

Hi Jason
thanks for your info..
Repository seems to be ok.

my problem now is to set

Enable statistics event monitoring to collect query run-time statistics events

I use a tech user with SQLADM auth
Referring to this note
https://www.ibm.com/support/knowledgecenter/en/SS5Q8A_3.1.x/com.ibm.datatools.dsweb.ots.security.doc/topics/Minimum_Database_Privileges_Required_on_LUW.html

Now I have no problem to use a different privileged user to create the environment , but I want that the tech user  used for monitoring have only SQLADM auth,

So I can create these object
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;​

before with my sysadm user,
then I can grant  (temporary)  my tech user (that i will use to monitor db)  to DBADM , configure this step
Enable statistics event monitoring to collect query run-time statistics events
and than I revoke DBADM grant to my tech user.

What to you think?

Thanks in advance

Rob







------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------

Original Message:
Sent: Thu June 25, 2020 04:47 PM
From: Jason Sizto
Subject: Error setting up db repository for DMC v3.1.2

Hello Roberto,

For DMC repository, the privilege requirement is described in the knowledge center.  You can find the details in this page.

The privilege requirement to configure the pre-requisite for event monitor on monitor database is currently not documented in our  knowledge center page  We will add them shortly after this post. 

To help answer your questions, the following privilege requirement is required to accomplish the task.
SYSCTRL or SYSADM privilege is required to create BUFFERPOOL and TABLESPACE

WLMADM or DBADM privilege is required to CREATE / ALTER WORKLOAD

ACCESSCTRL or SECADM privilege is required to GRANT EXECUTE on PROCEDURE

SYSADM privilege is required to db2set

------------------------------
Jason Sizto
------------------------------


Original Message:
Sent: Thu June 25, 2020 06:14 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

Sorry , another issue enabling 

Enable statistics event monitoring to collect query run-time statistics events

Fail to create tablespace due to: "MY_TECH_USER" does not have the privilege to perform operation "CREATE TABLESPACE".. SQLCODE=-552, SQLSTATE=42502, DRIVER=4.26.14

so do I have to create the objects manually?  (I found it on another discussion/post)

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;​

for V3.1.1 enabling collection use these statements
Create the event monitor prerequisite stored procedure.
--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE IBM_RTMON.EVMON_PREREQ()
SPECIFIC IBM_RTMON.EVMON_PREREQ
LANGUAGE SQL
BEGIN
DECLARE RESULT INTEGER;
DECLARE STMT VARCHAR(1000);
--Create a bufferpool with page size 32kb.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'CONSOLEPOOL';
IF (RESULT = 0) THEN
SET STMT = 'CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUMY_TECH_USERATIC PAGESIZE 32768';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a partition spannning all partitions.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.DBPARTITIONGROUPS WHERE DBPGNAME = 'CONSOLEGROUP';
IF (RESULT = 0) THEN
SET STMT = 'CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create the 32kb tablespace with max size 2G specified.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'TS4MONITOR';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUMY_TECH_USERATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 2G BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a 32kb pagesize temporary tablespace if there is not an existing one.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE DATATYPE = 'T' AND PAGESIZE = 32768;
IF (RESULT = 0) THEN
SET STMT = 'CREATE TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K MANAGED BY AUMY_TECH_USERATIC STORAGE EXTENTSIZE 4 BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Disable the legacy console workload DSM_WORKLOAD if it exists.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'DSM_WORKLOAD' AND ENABLED = 'Y';
IF (RESULT = 1) THEN
SET STMT = 'ALTER WORKLOAD DSM_WORKLOAD DISABLE';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--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.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'CONSOLE_WORKLOAD';
IF (RESULT = 0) THEN
SET STMT = 'CREATE WORKLOAD CONSOLE_WORKLOAD APPLNAME (''DSMAu*'',''DSMRt*'',''DS_ConnMgt*'',''DSSNAP*'',''DSMOQT'',''UC_*'')';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create SYSTOOLSPACE for Administrative Task Schedule(ATS).
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'SYSTOOLSPACE';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUMY_TECH_USERATIC STORAGE USING STOGROUP IBMSTOGROUP EXTENTSIZE 4';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
END@
--#SET TERMINATOR ;
--Apply the event monitor prerequisite stored procedure.
CALL IBM_RTMON.EVMON_PREREQ;
--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;
--Ensure you have ADMIN_TASK_ADD and ADMIN_TASK_UPDATE privileges to execute stored procedures(ATS).
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_ADD TO USER MY_TECH_USER;
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_UPDATE TO USER MY_TECH_USER;
grant use of tablespace TS4MONITOR TO USER MY_TECH_USER;

are still valid for V3.1.2 or something is changed?

Thanks for support

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY


Original Message:
Sent: Thu June 25, 2020 04:37 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

SOLVED! 
sqladm as user is not enough to create repository
Temporary dbadm assigned to user
I'm checking installation

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY


Original Message:
Sent: Thu June 25, 2020 03:53 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

i try to run in batch mode the create repository
I found a lot of these error

I replaced the real instance owner id with DB2_INSTANCE_OWNER

CREATE OR REPLACE VIEW BOTTLE_LOCK_WAIT AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLockWait") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOCK_WAIT". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_LOG_SPACE AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLogSpace") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOG_SPACE". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_MEMORY AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleMemory") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_MEMORY". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_NUM_LOCKS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleNumLocks") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_NUM_LOCKS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_QUERY_COST AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleQueryCost") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_QUERY_COST". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_MOD AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsMod") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_MOD". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_READ AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsRead") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_READ". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_RETURNED AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsReturned") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_RETURNED". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORT_OVERFLOWS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSortOverflows") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_SORT_OVERFLOWS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORTS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSorts") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object: "IBMCONSOLE.BOTTLE_SORTS".
SQLSTATE=42501

------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY

Sorry , just a further note:

this statement: 
CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS; (found in a previous post)
is really needed for non partitioned  Server/databases?

Thanks

Rob

------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------

Original Message:
Sent: Fri June 26, 2020 01:11 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

Hi Jason
thanks for your info..
Repository seems to be ok.

my problem now is to set

Enable statistics event monitoring to collect query run-time statistics events

I use a tech user with SQLADM auth
Referring to this note
https://www.ibm.com/support/knowledgecenter/en/SS5Q8A_3.1.x/com.ibm.datatools.dsweb.ots.security.doc/topics/Minimum_Database_Privileges_Required_on_LUW.html

Now I have no problem to use a different privileged user to create the environment , but I want that the tech user  used for monitoring have only SQLADM auth,

So I can create these object
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;​

before with my sysadm user,
then I can grant  (temporary)  my tech user (that i will use to monitor db)  to DBADM , configure this step
Enable statistics event monitoring to collect query run-time statistics events
and than I revoke DBADM grant to my tech user.

What to you think?

Thanks in advance

Rob







------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------


Original Message:
Sent: Thu June 25, 2020 04:47 PM
From: Jason Sizto
Subject: Error setting up db repository for DMC v3.1.2

Hello Roberto,

For DMC repository, the privilege requirement is described in the knowledge center.  You can find the details in this page.

The privilege requirement to configure the pre-requisite for event monitor on monitor database is currently not documented in our  knowledge center page  We will add them shortly after this post. 

To help answer your questions, the following privilege requirement is required to accomplish the task.
SYSCTRL or SYSADM privilege is required to create BUFFERPOOL and TABLESPACE

WLMADM or DBADM privilege is required to CREATE / ALTER WORKLOAD

ACCESSCTRL or SECADM privilege is required to GRANT EXECUTE on PROCEDURE

SYSADM privilege is required to db2set

------------------------------
Jason Sizto


Original Message:
Sent: Thu June 25, 2020 06:14 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

Sorry , another issue enabling 

Enable statistics event monitoring to collect query run-time statistics events

Fail to create tablespace due to: "MY_TECH_USER" does not have the privilege to perform operation "CREATE TABLESPACE".. SQLCODE=-552, SQLSTATE=42502, DRIVER=4.26.14

so do I have to create the objects manually?  (I found it on another discussion/post)

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;​

for V3.1.1 enabling collection use these statements
Create the event monitor prerequisite stored procedure.
--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE IBM_RTMON.EVMON_PREREQ()
SPECIFIC IBM_RTMON.EVMON_PREREQ
LANGUAGE SQL
BEGIN
DECLARE RESULT INTEGER;
DECLARE STMT VARCHAR(1000);
--Create a bufferpool with page size 32kb.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'CONSOLEPOOL';
IF (RESULT = 0) THEN
SET STMT = 'CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUMY_TECH_USERATIC PAGESIZE 32768';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a partition spannning all partitions.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.DBPARTITIONGROUPS WHERE DBPGNAME = 'CONSOLEGROUP';
IF (RESULT = 0) THEN
SET STMT = 'CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create the 32kb tablespace with max size 2G specified.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'TS4MONITOR';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUMY_TECH_USERATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 2G BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a 32kb pagesize temporary tablespace if there is not an existing one.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE DATATYPE = 'T' AND PAGESIZE = 32768;
IF (RESULT = 0) THEN
SET STMT = 'CREATE TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K MANAGED BY AUMY_TECH_USERATIC STORAGE EXTENTSIZE 4 BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Disable the legacy console workload DSM_WORKLOAD if it exists.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'DSM_WORKLOAD' AND ENABLED = 'Y';
IF (RESULT = 1) THEN
SET STMT = 'ALTER WORKLOAD DSM_WORKLOAD DISABLE';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--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.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'CONSOLE_WORKLOAD';
IF (RESULT = 0) THEN
SET STMT = 'CREATE WORKLOAD CONSOLE_WORKLOAD APPLNAME (''DSMAu*'',''DSMRt*'',''DS_ConnMgt*'',''DSSNAP*'',''DSMOQT'',''UC_*'')';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create SYSTOOLSPACE for Administrative Task Schedule(ATS).
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'SYSTOOLSPACE';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUMY_TECH_USERATIC STORAGE USING STOGROUP IBMSTOGROUP EXTENTSIZE 4';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
END@
--#SET TERMINATOR ;
--Apply the event monitor prerequisite stored procedure.
CALL IBM_RTMON.EVMON_PREREQ;
--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;
--Ensure you have ADMIN_TASK_ADD and ADMIN_TASK_UPDATE privileges to execute stored procedures(ATS).
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_ADD TO USER MY_TECH_USER;
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_UPDATE TO USER MY_TECH_USER;
grant use of tablespace TS4MONITOR TO USER MY_TECH_USER;

are still valid for V3.1.2 or something is changed?

Thanks for support

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY


Original Message:
Sent: Thu June 25, 2020 04:37 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

SOLVED! 
sqladm as user is not enough to create repository
Temporary dbadm assigned to user
I'm checking installation

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY


Original Message:
Sent: Thu June 25, 2020 03:53 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

i try to run in batch mode the create repository
I found a lot of these error

I replaced the real instance owner id with DB2_INSTANCE_OWNER

CREATE OR REPLACE VIEW BOTTLE_LOCK_WAIT AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLockWait") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOCK_WAIT". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_LOG_SPACE AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLogSpace") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOG_SPACE". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_MEMORY AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleMemory") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_MEMORY". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_NUM_LOCKS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleNumLocks") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_NUM_LOCKS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_QUERY_COST AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleQueryCost") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_QUERY_COST". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_MOD AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsMod") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_MOD". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_READ AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsRead") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_READ". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_RETURNED AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsReturned") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_RETURNED". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORT_OVERFLOWS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSortOverflows") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_SORT_OVERFLOWS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORTS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSorts") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object: "IBMCONSOLE.BOTTLE_SORTS".
SQLSTATE=42501

------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY

Hi Roberto,

Thank you for sharing your usage.  I understand your concern in setting up the pre-req for event monitor monitoring having a higher privilege than the real time monitoring privilege requirement.

The page you referred above is the privilege requirements for real time monitoring privilege.  And we intend to document the privilege requirement for pre-req for event monitor monitoring in this page: https://www.ibm.com/support/knowledgecenter/en/SS5Q8A_3.1.x/com.ibm.datatools.dsweb.ots.monitor.doc/topics/mon_em_tablespaces.html.

The pre-req steps can be executed by another ID with higher privilege (e.g. sysctrl / sysadm) on the monitoring database, and this step is only needed once.

Once pre-req is done.  In the DMC connection profile for the monitoring database, you can set the "Data Collection" credential using an ID with real time monitoring privilege requirement described here.  

If you intended to use the DMC UI to setup pre-req for Event Monitor, your suggestion to temporarily elevate the privilege requirement for pre-req is needed.  We will clearly indicate the privilege requirement when using the DMC UI to configure Event Monitor Profile as well.  

Thank you for your suggestion.

Regarding the question below on why create DB PARTITION GROUP on non partitioned database.  I believe it is done for ease on implementation.  For me to get more context, can you refer me to the previous post you mentioned or reply to that post?  Thanks.

------------------------------
Jason Sizto
------------------------------

Original Message:
Sent: Fri June 26, 2020 01:11 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

Hi Jason
thanks for your info..
Repository seems to be ok.

my problem now is to set

Enable statistics event monitoring to collect query run-time statistics events

I use a tech user with SQLADM auth
Referring to this note
https://www.ibm.com/support/knowledgecenter/en/SS5Q8A_3.1.x/com.ibm.datatools.dsweb.ots.security.doc/topics/Minimum_Database_Privileges_Required_on_LUW.html

Now I have no problem to use a different privileged user to create the environment , but I want that the tech user  used for monitoring have only SQLADM auth,

So I can create these object
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;​

before with my sysadm user,
then I can grant  (temporary)  my tech user (that i will use to monitor db)  to DBADM , configure this step
Enable statistics event monitoring to collect query run-time statistics events
and than I revoke DBADM grant to my tech user.

What to you think?

Thanks in advance

Rob







------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------


Original Message:
Sent: Thu June 25, 2020 04:47 PM
From: Jason Sizto
Subject: Error setting up db repository for DMC v3.1.2

Hello Roberto,

For DMC repository, the privilege requirement is described in the knowledge center.  You can find the details in this page.

The privilege requirement to configure the pre-requisite for event monitor on monitor database is currently not documented in our  knowledge center page  We will add them shortly after this post. 

To help answer your questions, the following privilege requirement is required to accomplish the task.
SYSCTRL or SYSADM privilege is required to create BUFFERPOOL and TABLESPACE

WLMADM or DBADM privilege is required to CREATE / ALTER WORKLOAD

ACCESSCTRL or SECADM privilege is required to GRANT EXECUTE on PROCEDURE

SYSADM privilege is required to db2set

------------------------------
Jason Sizto


Original Message:
Sent: Thu June 25, 2020 06:14 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

Sorry , another issue enabling 

Enable statistics event monitoring to collect query run-time statistics events

Fail to create tablespace due to: "MY_TECH_USER" does not have the privilege to perform operation "CREATE TABLESPACE".. SQLCODE=-552, SQLSTATE=42502, DRIVER=4.26.14

so do I have to create the objects manually?  (I found it on another discussion/post)

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;​

for V3.1.1 enabling collection use these statements
Create the event monitor prerequisite stored procedure.
--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE IBM_RTMON.EVMON_PREREQ()
SPECIFIC IBM_RTMON.EVMON_PREREQ
LANGUAGE SQL
BEGIN
DECLARE RESULT INTEGER;
DECLARE STMT VARCHAR(1000);
--Create a bufferpool with page size 32kb.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'CONSOLEPOOL';
IF (RESULT = 0) THEN
SET STMT = 'CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUMY_TECH_USERATIC PAGESIZE 32768';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a partition spannning all partitions.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.DBPARTITIONGROUPS WHERE DBPGNAME = 'CONSOLEGROUP';
IF (RESULT = 0) THEN
SET STMT = 'CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create the 32kb tablespace with max size 2G specified.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'TS4MONITOR';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUMY_TECH_USERATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 2G BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a 32kb pagesize temporary tablespace if there is not an existing one.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE DATATYPE = 'T' AND PAGESIZE = 32768;
IF (RESULT = 0) THEN
SET STMT = 'CREATE TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K MANAGED BY AUMY_TECH_USERATIC STORAGE EXTENTSIZE 4 BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Disable the legacy console workload DSM_WORKLOAD if it exists.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'DSM_WORKLOAD' AND ENABLED = 'Y';
IF (RESULT = 1) THEN
SET STMT = 'ALTER WORKLOAD DSM_WORKLOAD DISABLE';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--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.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'CONSOLE_WORKLOAD';
IF (RESULT = 0) THEN
SET STMT = 'CREATE WORKLOAD CONSOLE_WORKLOAD APPLNAME (''DSMAu*'',''DSMRt*'',''DS_ConnMgt*'',''DSSNAP*'',''DSMOQT'',''UC_*'')';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create SYSTOOLSPACE for Administrative Task Schedule(ATS).
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'SYSTOOLSPACE';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUMY_TECH_USERATIC STORAGE USING STOGROUP IBMSTOGROUP EXTENTSIZE 4';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
END@
--#SET TERMINATOR ;
--Apply the event monitor prerequisite stored procedure.
CALL IBM_RTMON.EVMON_PREREQ;
--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;
--Ensure you have ADMIN_TASK_ADD and ADMIN_TASK_UPDATE privileges to execute stored procedures(ATS).
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_ADD TO USER MY_TECH_USER;
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_UPDATE TO USER MY_TECH_USER;
grant use of tablespace TS4MONITOR TO USER MY_TECH_USER;

are still valid for V3.1.2 or something is changed?

Thanks for support

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY


Original Message:
Sent: Thu June 25, 2020 04:37 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

SOLVED! 
sqladm as user is not enough to create repository
Temporary dbadm assigned to user
I'm checking installation

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY


Original Message:
Sent: Thu June 25, 2020 03:53 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

i try to run in batch mode the create repository
I found a lot of these error

I replaced the real instance owner id with DB2_INSTANCE_OWNER

CREATE OR REPLACE VIEW BOTTLE_LOCK_WAIT AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLockWait") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOCK_WAIT". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_LOG_SPACE AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLogSpace") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOG_SPACE". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_MEMORY AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleMemory") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_MEMORY". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_NUM_LOCKS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleNumLocks") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_NUM_LOCKS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_QUERY_COST AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleQueryCost") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_QUERY_COST". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_MOD AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsMod") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_MOD". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_READ AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsRead") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_READ". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_RETURNED AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsReturned") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_RETURNED". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORT_OVERFLOWS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSortOverflows") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_SORT_OVERFLOWS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORTS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSorts") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object: "IBMCONSOLE.BOTTLE_SORTS".
SQLSTATE=42501

------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY

Hi Jason
thanks for you info , 

i will check all ,, 

about previous post with this statement

CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS;

here:

https://community.ibm.com/community/user/hybriddatamanagement/communities/community-home/digestviewer/viewthread?GroupId=4189&MessageKey=8540c4a3-b7c2-452f-9d96-efc5c5c525bd&CommunityKey=e1f1cc2c-065f-4152-bef7-3641a384c9e1&tab=digestviewer&ReturnUrl=%2fcommunity%2fuser%2fhybriddatamanagement%2fcommunities%2fcommunity-home%2fdigestviewer%3fcommunitykey%3de1f1cc2c-065f-4152-bef7-3641a384c9e1%26tab%3ddigestviewer

Thanks again

Rob

------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY
------------------------------

Original Message:
Sent: Fri June 26, 2020 03:08 PM
From: Jason Sizto
Subject: Error setting up db repository for DMC v3.1.2

Hi Roberto,

Thank you for sharing your usage.  I understand your concern in setting up the pre-req for event monitor monitoring having a higher privilege than the real time monitoring privilege requirement.

The page you referred above is the privilege requirements for real time monitoring privilege.  And we intend to document the privilege requirement for pre-req for event monitor monitoring in this page: https://www.ibm.com/support/knowledgecenter/en/SS5Q8A_3.1.x/com.ibm.datatools.dsweb.ots.monitor.doc/topics/mon_em_tablespaces.html.

The pre-req steps can be executed by another ID with higher privilege (e.g. sysctrl / sysadm) on the monitoring database, and this step is only needed once.

Once pre-req is done.  In the DMC connection profile for the monitoring database, you can set the "Data Collection" credential using an ID with real time monitoring privilege requirement described here.  

If you intended to use the DMC UI to setup pre-req for Event Monitor, your suggestion to temporarily elevate the privilege requirement for pre-req is needed.  We will clearly indicate the privilege requirement when using the DMC UI to configure Event Monitor Profile as well.  

Thank you for your suggestion.

Regarding the question below on why create DB PARTITION GROUP on non partitioned database.  I believe it is done for ease on implementation.  For me to get more context, can you refer me to the previous post you mentioned or reply to that post?  Thanks.

------------------------------
Jason Sizto
------------------------------


Original Message:
Sent: Fri June 26, 2020 01:11 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

Hi Jason
thanks for your info..
Repository seems to be ok.

my problem now is to set

Enable statistics event monitoring to collect query run-time statistics events

I use a tech user with SQLADM auth
Referring to this note
https://www.ibm.com/support/knowledgecenter/en/SS5Q8A_3.1.x/com.ibm.datatools.dsweb.ots.security.doc/topics/Minimum_Database_Privileges_Required_on_LUW.html

Now I have no problem to use a different privileged user to create the environment , but I want that the tech user  used for monitoring have only SQLADM auth,

So I can create these object
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;​

before with my sysadm user,
then I can grant  (temporary)  my tech user (that i will use to monitor db)  to DBADM , configure this step
Enable statistics event monitoring to collect query run-time statistics events
and than I revoke DBADM grant to my tech user.

What to you think?

Thanks in advance

Rob







------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY


Original Message:
Sent: Thu June 25, 2020 04:47 PM
From: Jason Sizto
Subject: Error setting up db repository for DMC v3.1.2

Hello Roberto,

For DMC repository, the privilege requirement is described in the knowledge center.  You can find the details in this page.

The privilege requirement to configure the pre-requisite for event monitor on monitor database is currently not documented in our  knowledge center page  We will add them shortly after this post. 

To help answer your questions, the following privilege requirement is required to accomplish the task.
SYSCTRL or SYSADM privilege is required to create BUFFERPOOL and TABLESPACE

WLMADM or DBADM privilege is required to CREATE / ALTER WORKLOAD

ACCESSCTRL or SECADM privilege is required to GRANT EXECUTE on PROCEDURE

SYSADM privilege is required to db2set

------------------------------
Jason Sizto


Original Message:
Sent: Thu June 25, 2020 06:14 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

Sorry , another issue enabling 

Enable statistics event monitoring to collect query run-time statistics events

Fail to create tablespace due to: "MY_TECH_USER" does not have the privilege to perform operation "CREATE TABLESPACE".. SQLCODE=-552, SQLSTATE=42502, DRIVER=4.26.14

so do I have to create the objects manually?  (I found it on another discussion/post)

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;​

for V3.1.1 enabling collection use these statements
Create the event monitor prerequisite stored procedure.
--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE IBM_RTMON.EVMON_PREREQ()
SPECIFIC IBM_RTMON.EVMON_PREREQ
LANGUAGE SQL
BEGIN
DECLARE RESULT INTEGER;
DECLARE STMT VARCHAR(1000);
--Create a bufferpool with page size 32kb.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.BUFFERPOOLS WHERE BPNAME = 'CONSOLEPOOL';
IF (RESULT = 0) THEN
SET STMT = 'CREATE BUFFERPOOL CONSOLEPOOL ALL DBPARTITIONNUMS SIZE 1000 AUMY_TECH_USERATIC PAGESIZE 32768';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a partition spannning all partitions.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.DBPARTITIONGROUPS WHERE DBPGNAME = 'CONSOLEGROUP';
IF (RESULT = 0) THEN
SET STMT = 'CREATE DATABASE PARTITION GROUP CONSOLEGROUP ON ALL DBPARTITIONNUMS';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create the 32kb tablespace with max size 2G specified.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'TS4MONITOR';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE TS4MONITOR IN CONSOLEGROUP PAGESIZE 32768 MANAGED BY AUMY_TECH_USERATIC STORAGE AUTORESIZE YES INITIALSIZE 100M MAXSIZE 2G BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create a 32kb pagesize temporary tablespace if there is not an existing one.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE DATATYPE = 'T' AND PAGESIZE = 32768;
IF (RESULT = 0) THEN
SET STMT = 'CREATE TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K MANAGED BY AUMY_TECH_USERATIC STORAGE EXTENTSIZE 4 BUFFERPOOL CONSOLEPOOL';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Disable the legacy console workload DSM_WORKLOAD if it exists.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'DSM_WORKLOAD' AND ENABLED = 'Y';
IF (RESULT = 1) THEN
SET STMT = 'ALTER WORKLOAD DSM_WORKLOAD DISABLE';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--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.
SELECT COUNT(*) INTO RESULT FROM SYSCAT.WORKLOADS WHERE WORKLOADNAME = 'CONSOLE_WORKLOAD';
IF (RESULT = 0) THEN
SET STMT = 'CREATE WORKLOAD CONSOLE_WORKLOAD APPLNAME (''DSMAu*'',''DSMRt*'',''DS_ConnMgt*'',''DSSNAP*'',''DSMOQT'',''UC_*'')';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
--Create SYSTOOLSPACE for Administrative Task Schedule(ATS).
SELECT COUNT(*) INTO RESULT FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'SYSTOOLSPACE';
IF (RESULT = 0) THEN
SET STMT = 'CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUMY_TECH_USERATIC STORAGE USING STOGROUP IBMSTOGROUP EXTENTSIZE 4';
EXECUTE IMMEDIATE STMT;
END IF;
COMMIT;
END@
--#SET TERMINATOR ;
--Apply the event monitor prerequisite stored procedure.
CALL IBM_RTMON.EVMON_PREREQ;
--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;
--Ensure you have ADMIN_TASK_ADD and ADMIN_TASK_UPDATE privileges to execute stored procedures(ATS).
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_ADD TO USER MY_TECH_USER;
GRANT EXECUTE ON PROCEDURE SYSPROC.ADMIN_TASK_UPDATE TO USER MY_TECH_USER;
grant use of tablespace TS4MONITOR TO USER MY_TECH_USER;

are still valid for V3.1.2 or something is changed?

Thanks for support

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY


Original Message:
Sent: Thu June 25, 2020 04:37 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

SOLVED! 
sqladm as user is not enough to create repository
Temporary dbadm assigned to user
I'm checking installation

Rob


------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY


Original Message:
Sent: Thu June 25, 2020 03:53 AM
From: Roberto Mandolini
Subject: Error setting up db repository for DMC v3.1.2

i try to run in batch mode the create repository
I found a lot of these error

I replaced the real instance owner id with DB2_INSTANCE_OWNER

CREATE OR REPLACE VIEW BOTTLE_LOCK_WAIT AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLockWait") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOCK_WAIT". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_LOG_SPACE AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleLogSpace") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_LOG_SPACE". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_MEMORY AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleMemory") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_MEMORY". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_NUM_LOCKS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleNumLocks") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_NUM_LOCKS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_QUERY_COST AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleQueryCost") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_QUERY_COST". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_MOD AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsMod") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_MOD". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_READ AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsRead") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_READ". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_ROWS_RETURNED AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleRowsReturned") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_ROWS_RETURNED". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORT_OVERFLOWS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSortOverflows") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object:
"IBMCONSOLE.BOTTLE_SORT_OVERFLOWS". SQLSTATE=42501

CREATE OR REPLACE VIEW BOTTLE_SORTS AS WITH ALL_DELTA AS ( SELECT LAG(DBCONN_INT) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_DBCONN_INT, DBCONN_INT, LAG(COLLECTED) OVER (ORDER BY DBCONN_INT, COLLECTED) AS PREV_COLLECTED, COLLECTED, APPLICATION_HANDLE, UOW_ID, RESOURCE_VALUE, AVG_WO_MAX, APPLICATION_NAME, SESSION_AUTH_ID, WORKLOAD_NAME FROM "bottleSorts") SELECT AD.DBCONN_INT, DB.DBCONN_ID AS DBCONN_ID, AD.PREV_COLLECTED, AD.COLLECTED, (CAST(((( JULIAN_DAY(AD.COLLECTED) - JULIAN_DAY(AD.PREV_COLLECTED)) * BIGINT(86400)) + (( EXTRACT(HOUR FROM AD.COLLECTED) - EXTRACT(HOUR FROM AD.PREV_COLLECTED)) * 60 * BIGINT(60)) + (( EXTRACT(MINUTE FROM AD.COLLECTED) - EXTRACT(MINUTE FROM AD.PREV_COLLECTED)) * BIGINT(60)) ) AS BIGINT) * 1000) + CAST((( EXTRACT(SECOND FROM AD.COLLECTED) - EXTRACT(SECOND FROM AD.PREV_COLLECTED)) * BIGINT(1000)) AS BIGINT) AS DELTA_MSEC, AD.APPLICATION_HANDLE, AD.UOW_ID, AD.RESOURCE_VALUE, AD.AVG_WO_MAX, AD.APPLICATION_NAME, AD.SESSION_AUTH_ID, AD.WORKLOAD_NAME FROM ALL_DELTA AD JOIN RTMON_MAP_DBCONN DB ON AD.DBCONN_INT = DB.DBCONN_INT WHERE AD.PREV_DBCONN_INT = AD.DBCONN_INT
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N The statement failed because the authorization ID does not have the
required authorization or privilege to perform the operation. Authorization
ID: "DB2_INSTANCE_OWNER". Operation: "REPLACE VIEW". Object: "IBMCONSOLE.BOTTLE_SORTS".
SQLSTATE=42501

------------------------------
Roberto Mandolini
Value Transformation Services an IBM subsidiary
Roberto.Mandolini_V-TServices@it.ibm.com
Rome - ITALY