Topic
  • 5 replies
  • Latest Post - ‏2013-03-27T20:28:10Z by SystemAdmin
SystemAdmin
SystemAdmin
1632 Posts

Pinned topic Query Tuning with DS 3.1.1 - DB2OE IS NOT VALID AUTH ID

‏2013-03-26T16:59:47Z |
Hello,

One of my colleagues reported that she is facing an issue with Query Tuning and it appears like a different SQLID is set by Data Studio during index analysis and that sounds to be weird to me. SQLID and Schema are specified when a new project is created and authorized for use. It does not make sense why it is being changed under the covers. Did anyone else encountered this issue before? Are there any workaround ?

Error Message is copied below.

Exception Message:
During index analysis, an unexpected SQL error occurred with SQLCODE: -553 and SQLSTATE: 42503. Fix the problem and then run the Workload Index Advisor for workload Dummy_Workload_for_IA again.
The SQL statement failed.

Explanation:
The SQL statement resulted in an error with SQLCODE: -553 and SQLSTATE: 42503.

User response:
The DB2 information center contains more information about the SQLCODE and how to resolve the error.
DB2OE SPECIFIED IS NOT ONE OF THE VALID AUTHORIZATION IDS FOR REQUESTED OPERATION. SQLCODE=-553, SQLSTATE=42503, DRIVER=3.63.108
Updated on 2013-03-27T20:28:10Z at 2013-03-27T20:28:10Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Query Tuning with DS 3.1.1 - DB2OE IS NOT VALID AUTH ID

    ‏2013-03-26T17:56:49Z  
    HI,
    My name is Xin Wu. I am a developer of InfoSphere Optim Query Workload Tuner.

    Please try the following setting in the Global Preference.
    1. Go to Windows -> Preferences
    2. In the Preferences dialog, expand "Data Management"->"Query Tuner" node on the navigation panel on the left
    3. On the right panel, you will see "General Options" group in the middle.
    4. UNCHECK the option "Enable internal processes to use secondary ... "
    5. Click OK to save the setting.
    6. Re-run the function you tried before.

    The attached is the screen shot of this setting. If the issue still exists, please open a PMR for us to better assist you.

    Thank you for using our product!
    Best regards,
    Xin Wu
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Query Tuning with DS 3.1.1 - DB2OE IS NOT VALID AUTH ID

    ‏2013-03-26T18:18:00Z  
    HI,
    My name is Xin Wu. I am a developer of InfoSphere Optim Query Workload Tuner.

    Please try the following setting in the Global Preference.
    1. Go to Windows -> Preferences
    2. In the Preferences dialog, expand "Data Management"->"Query Tuner" node on the navigation panel on the left
    3. On the right panel, you will see "General Options" group in the middle.
    4. UNCHECK the option "Enable internal processes to use secondary ... "
    5. Click OK to save the setting.
    6. Re-run the function you tried before.

    The attached is the screen shot of this setting. If the issue still exists, please open a PMR for us to better assist you.

    Thank you for using our product!
    Best regards,
    Xin Wu
    Thank you so much !! It worked like a charm ;>)
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Query Tuning with DS 3.1.1 - DB2OE IS NOT VALID AUTH ID

    ‏2013-03-27T20:10:15Z  
    HI,
    My name is Xin Wu. I am a developer of InfoSphere Optim Query Workload Tuner.

    Please try the following setting in the Global Preference.
    1. Go to Windows -> Preferences
    2. In the Preferences dialog, expand "Data Management"->"Query Tuner" node on the navigation panel on the left
    3. On the right panel, you will see "General Options" group in the middle.
    4. UNCHECK the option "Enable internal processes to use secondary ... "
    5. Click OK to save the setting.
    6. Re-run the function you tried before.

    The attached is the screen shot of this setting. If the issue still exists, please open a PMR for us to better assist you.

    Thank you for using our product!
    Best regards,
    Xin Wu
    Xin,

    Unfortunately, UNCHECK the option "Enable internal processes to use secondary ..' causes problem which I explained in another thread "Query Tuning with DS 3.1.1 - Workload-based Index Advisor not enabled". Here is the situation I have:

    1) We have a common set of explain tables under a particular schema (say XXX and it is a secondary authorization id) and Current SQLID is set to XXX prior starting query tuning. If I uncheck above mentioned option, DS tries to use my primary authorization as SQLID for processsing and fails identifying the explain tables.

    2) If I have it checked, DS use CURRENT SQLID as DB2OE and defeats the purpose. It does not help even if I had an alias on the tables under SQLID XXX

    7584 IA for ZOS Thread 3/27/13 1:12:53 PM EDT Data Access Trace http://com.ibm.datatools.dsoe.common.da.SQLExecutorFactory newDynamicSQLExecutor(Connection conn) Got new dynamic SQLExecutor: com.ibm.datatools.dsoe.common.da.DynamicSQLExecutorImpl@9c009c

    7584 IA for ZOS Thread 3/27/13 1:12:53 PM EDT Data Access Entry http://com.ibm.datatools.dsoe.common.da.DynamicSQLExecutorImpl executeUpdate() Starts to execute the SQL statement:
    SET CURRENT SQLID = 'DB2OE'

    7584 IA for ZOS Thread 3/27/13 1:12:54 PM EDT Data Access Exit http://com.ibm.datatools.dsoe.common.da.DynamicSQLExecutorImpl executeUpdate() Succeeds to execute the SQL statement.SET CURRENT SQLID = 'DB2OE'

    Thanks
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Query Tuning with DS 3.1.1 - DB2OE IS NOT VALID AUTH ID

    ‏2013-03-27T20:10:25Z  
    Another issue was encountered with the solution provided
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Query Tuning with DS 3.1.1 - DB2OE IS NOT VALID AUTH ID

    ‏2013-03-27T20:28:10Z  
    Another issue was encountered with the solution provided
    Hi,
    Would you please open a PMR, so we can address this issue properly with detailed analysis? After opening the PMR, send me an email including the PMR number, so that we can take it quickly. My email is xinwu@us.ibm.com.

    Thank you again for your patience and we will do our best to address your problem.

    Best regards,
    Xin Wu