Topic
  • 8 replies
  • Latest Post - ‏2013-01-30T18:31:54Z by SystemAdmin
SystemAdmin
SystemAdmin
1632 Posts

Pinned topic DS 3.2 with Query Workload Tuner: can't enable Tuning on DB2 9.5 database

‏2013-01-23T12:20:39Z |
Hi,

I can't enable Query Tuning against a DB2 9.5 database.

The erorr displayed is "The Status of the OT_WAPC tables could not be retrieved"

And it Shows an SQL error -104
jccTime:2013-01-23-12:56:54.601Thread:ModalContextStatement@746f6c52executeQuery (SELECT NAME, CREATOR, BASE_NAME, BASE_SCHEMA, TYPE, COLCOUNT FROM SYSIBM.SYSTABLES WHERE NAME IN () AND CREATOR='SYSTOOLS') called
jcc BEGIN TRACE_DIAGNOSTICS
jccThread:ModalContextSQLException@4f25835f java.sql.SQLException
jccThread:ModalContextSQLException@4f25835fSqlca@7321fa17 DB2 SQLCA from server
jccThread:ModalContextSQLException@4f25835fSqlca@7321fa17 SqlCode = -104
jccThread:ModalContextSQLException@4f25835fSqlca@7321fa17 SqlErrd = { -2145779603, 0, 0, 0, -705, 0 }
jccThread:ModalContextSQLException@4f25835fSqlca@7321fa17 SqlErrmc = );BLES WHERE NAME IN (;<value_expr_body>
jccThread:ModalContextSQLException@4f25835fSqlca@7321fa17 SqlErrmcTokens = { ), BLES WHERE NAME IN (, <value_expr_body> }
jccThread:ModalContextSQLException@4f25835fSqlca@7321fa17 SqlErrp = SQLNP021
jccThread:ModalContextSQLException@4f25835fSqlca@7321fa17 SqlState = 42601
jccThread:ModalContextSQLException@4f25835fSqlca@7321fa17 SqlWarn =
jccThread:ModalContextSQLException@4f25835f SQL state = 42601
jccThread:ModalContextSQLException@4f25835f Error code = -104
jccThread:ModalContextSQLException@4f25835f Tokens = );BLES WHERE NAME IN (;<value_expr_body>
jccThread:ModalContextSQLException@4f25835f Stack trace follows
com.ibm.db2.jcc.am.SqlSyntaxErrorException: );BLES WHERE NAME IN (;<value_expr_body>
at com.ibm.db2.jcc.am.bd.a(Unknown Source)
at com.ibm.db2.jcc.am.bd.a(Unknown Source)
at com.ibm.db2.jcc.am.bd.a(Unknown Source)
at com.ibm.db2.jcc.am.io.c(Unknown Source)
Workload Tuning is disabled...
The install package was from Passport Advantage.

This function has been working fine in 3.1.1 against the same database...
Regards,

Matthias
Updated on 2013-01-30T18:31:54Z at 2013-01-30T18:31:54Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: DS 3.2 with Query Workload Tuner: can't enable Tuning on DB2 9.5 database

    ‏2013-01-23T20:11:30Z  
    Hi,
    What is the DB2 minor version? Is it older than V9.5.6 ?

    • Xin
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: DS 3.2 with Query Workload Tuner: can't enable Tuning on DB2 9.5 database

    ‏2013-01-23T20:53:26Z  
    Hi,
    What is the DB2 minor version? Is it older than V9.5.6 ?

    • Xin
    Hi,

    unfortunately, yes.

    But some 10.1 test systems are up awaiting test from the development department.
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: DS 3.2 with Query Workload Tuner: can't enable Tuning on DB2 9.5 database

    ‏2013-01-23T21:52:02Z  
    Hi,

    unfortunately, yes.

    But some 10.1 test systems are up awaiting test from the development department.
    hi,
    Have you been able to do the configuration on the V10.1 system?
    We will fix the problem on V9.5 in future release.

    Xin
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: DS 3.2 with Query Workload Tuner: can't enable Tuning on DB2 9.5 database

    ‏2013-01-24T11:20:09Z  
    hi,
    Have you been able to do the configuration on the V10.1 system?
    We will fix the problem on V9.5 in future release.

    Xin
    Hi,

    yes, it works on 10.1 (I had dropped all objects from SYSTOOLS and Query Tuning configuration working fine.

    Unfortunately, I found a new problem:

    I can't grant a privilege for Workload tuning to a role called "Role-Developer-Schema" (SQL Error -104).
    It works without the dashes..
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: DS 3.2 with Query Workload Tuner: can't enable Tuning on DB2 9.5 database

    ‏2013-01-24T18:38:40Z  
    Hi,

    yes, it works on 10.1 (I had dropped all objects from SYSTOOLS and Query Tuning configuration working fine.

    Unfortunately, I found a new problem:

    I can't grant a privilege for Workload tuning to a role called "Role-Developer-Schema" (SQL Error -104).
    It works without the dashes..
    Hi,
    I tried the creating and granting statement in DB2 Command Line Processor and found the following:

    • I issued command: CREATE ROLE ROLE-DEVELOPER-SCHEMA; but failed with SQLCODE -104, because of the dash
    • I issued command: CREATE ROLE ROLE_DEVELOPER_SCHEMA; it succeeded, because of the underscore
    • Then, i issued command: GRANT SELECT ON SYSTOOLS.QT_WCC_WORKLOAD TO ROLE_DEVELOPER_SCHEMA; it succeeded.

    • I tied with double quote: CREATE ROLE "Role-Developer-Schema"; it succeeded.
    • However, the grant command: GRANT SELECT ON SYSTOOLS.QT_WCC_WORKLOAD TO "Role-Developer-Schema" failed with SQLCODE -567, which means the authorization name does not meet the identifier naming rules.

    So I think DB2 does not allow '-' in the identifier, while '_' is accepted.

    Could you please try Role_Developer_Schema, instead? Thanks!

    Xin
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: DS 3.2 with Query Workload Tuner: can't enable Tuning on DB2 9.5 database

    ‏2013-01-27T06:08:53Z  
    Hi,
    I tried the creating and granting statement in DB2 Command Line Processor and found the following:

    • I issued command: CREATE ROLE ROLE-DEVELOPER-SCHEMA; but failed with SQLCODE -104, because of the dash
    • I issued command: CREATE ROLE ROLE_DEVELOPER_SCHEMA; it succeeded, because of the underscore
    • Then, i issued command: GRANT SELECT ON SYSTOOLS.QT_WCC_WORKLOAD TO ROLE_DEVELOPER_SCHEMA; it succeeded.

    • I tied with double quote: CREATE ROLE "Role-Developer-Schema"; it succeeded.
    • However, the grant command: GRANT SELECT ON SYSTOOLS.QT_WCC_WORKLOAD TO "Role-Developer-Schema" failed with SQLCODE -567, which means the authorization name does not meet the identifier naming rules.

    So I think DB2 does not allow '-' in the identifier, while '_' is accepted.

    Could you please try Role_Developer_Schema, instead? Thanks!

    Xin
    Hi,

    Please tell db2 that you are granting to a role and it works. Use db2 'grant select on department to role "Role-Developer-Schema"' instead.
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: DS 3.2 with Query Workload Tuner: can't enable Tuning on DB2 9.5 database

    ‏2013-01-30T16:31:52Z  
    Hi,
    I tried the creating and granting statement in DB2 Command Line Processor and found the following:

    • I issued command: CREATE ROLE ROLE-DEVELOPER-SCHEMA; but failed with SQLCODE -104, because of the dash
    • I issued command: CREATE ROLE ROLE_DEVELOPER_SCHEMA; it succeeded, because of the underscore
    • Then, i issued command: GRANT SELECT ON SYSTOOLS.QT_WCC_WORKLOAD TO ROLE_DEVELOPER_SCHEMA; it succeeded.

    • I tied with double quote: CREATE ROLE "Role-Developer-Schema"; it succeeded.
    • However, the grant command: GRANT SELECT ON SYSTOOLS.QT_WCC_WORKLOAD TO "Role-Developer-Schema" failed with SQLCODE -567, which means the authorization name does not meet the identifier naming rules.

    So I think DB2 does not allow '-' in the identifier, while '_' is accepted.

    Could you please try Role_Developer_Schema, instead? Thanks!

    Xin
    Hi Xin,

    sorry about the delay.
    your GRANT is wrong:
    this working fine:

    GRANT SELECT ON TABLE SYSTOOLS.QT_WCC_WORKLOAD TO ROLE "Role-Developer-Schema";

    You miss "TO ROLE" in your command.
    We don't have any problems with a "-" in ROLE names.
    The "Manage Privilege" function simply forget to put an """ around the name.

    Matthias
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: DS 3.2 with Query Workload Tuner: can't enable Tuning on DB2 9.5 database

    ‏2013-01-30T18:31:54Z  
    Hi Xin,

    sorry about the delay.
    your GRANT is wrong:
    this working fine:

    GRANT SELECT ON TABLE SYSTOOLS.QT_WCC_WORKLOAD TO ROLE "Role-Developer-Schema";

    You miss "TO ROLE" in your command.
    We don't have any problems with a "-" in ROLE names.
    The "Manage Privilege" function simply forget to put an """ around the name.

    Matthias
    hi Matthias,
    Thanks for the reply!
    I apologize that I missed the "ROLE" keyword in my experiment.
    We found the issue and it is in our todo list for coming release.

    Regards,
    Xin