Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
8 replies Latest Post - ‏2013-01-30T18:31:54Z by SystemAdmin
SystemAdmin
SystemAdmin
1632 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

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

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

    • Xin
    • SystemAdmin
      SystemAdmin
      1632 Posts
      ACCEPTED ANSWER

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

      ‏2013-01-23T20:53:26Z  in response to SystemAdmin
      Hi,

      unfortunately, yes.

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

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

        ‏2013-01-23T21:52:02Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

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

          ‏2013-01-24T11:20:09Z  in response to SystemAdmin
          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
            ACCEPTED ANSWER

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

            ‏2013-01-24T18:38:40Z  in response to SystemAdmin
            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
              ACCEPTED ANSWER

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

              ‏2013-01-27T06:08:53Z  in response to SystemAdmin
              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
              ACCEPTED ANSWER

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

              ‏2013-01-30T16:31:52Z  in response to SystemAdmin
              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
                ACCEPTED ANSWER

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

                ‏2013-01-30T18:31:54Z  in response to SystemAdmin
                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