Db2

  • 1.  Error setting up db repository for DMC v3.1.2

    Posted Thu June 25, 2020 03:20 AM
    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


  • 2.  RE: Error setting up db repository for DMC v3.1.2

    Posted Thu June 25, 2020 03:53 AM
    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
    ------------------------------



  • 3.  RE: Error setting up db repository for DMC v3.1.2

    Posted Thu June 25, 2020 04:37 AM
    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
    ------------------------------



  • 4.  RE: Error setting up db repository for DMC v3.1.2

    Posted Thu June 25, 2020 06:14 AM
    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
    ------------------------------



  • 5.  RE: Error setting up db repository for DMC v3.1.2

    Posted Thu June 25, 2020 04:47 PM
    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
    ------------------------------



  • 6.  RE: Error setting up db repository for DMC v3.1.2

    Posted Fri June 26, 2020 01:12 AM
    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
    ------------------------------



  • 7.  RE: Error setting up db repository for DMC v3.1.2

    Posted Fri June 26, 2020 02:03 AM
    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
    ------------------------------



  • 8.  RE: Error setting up db repository for DMC v3.1.2

    Posted Fri June 26, 2020 03:09 PM
    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
    ------------------------------



  • 9.  RE: Error setting up db repository for DMC v3.1.2

    Posted Mon June 29, 2020 07:18 AM
    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
    ------------------------------