Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.10.1.1
    • Component/s: Documentation
    • Labels:
      None

      Description

      We should document the procedure that will get added as part of DERBY-5578. The new procedure will invalidate all the stored prepared statements. When such an invalid stored prepared statement is executed next time, it will get recompiled at that point thus generating a new plan for it. This can be a very useful tool from support point of view, especially after upgrades.

      1. rrefsyscsinvalidatestoredstmts.html
        4 kB
        Kim Haase
      2. DERBY-5793.diff
        4 kB
        Kim Haase
      3. DERBY-5793.stat
        0.1 kB
        Kim Haase
      4. rrefsyscsinvalidatestoredstmts.html
        5 kB
        Kim Haase
      5. DERBY-5793-2.diff
        2 kB
        Kim Haase
      6. DERBY-5793-3.diff
        2 kB
        Kim Haase
      7. rrefsyscsinvalidatestoredstmts.html
        5 kB
        Kim Haase

        Activity

        Mamta A. Satoor created issue -
        Kim Haase made changes -
        Field Original Value New Value
        Assignee Kim Haase [ chaase3 ]
        Hide
        Kim Haase added a comment -

        I'm assuming this new procedure is for the release after 10.9 – is that correct?

        Also, what is the syntax? I am guessing it takes no arguments, but I had better make sure. Thanks!

        Show
        Kim Haase added a comment - I'm assuming this new procedure is for the release after 10.9 – is that correct? Also, what is the syntax? I am guessing it takes no arguments, but I had better make sure. Thanks!
        Mamta A. Satoor made changes -
        Assignee Kim Haase [ chaase3 ] Mamta A. Satoor [ mamtas ]
        Hide
        Mamta A. Satoor added a comment -

        Hi Kim, thanks as always for picking the doc part. You are right, the procedure takes no argument. The syntax is as follows
        CALL SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS()

        Show
        Mamta A. Satoor added a comment - Hi Kim, thanks as always for picking the doc part. You are right, the procedure takes no argument. The syntax is as follows CALL SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS()
        Mamta A. Satoor made changes -
        Summary Document new SYSCS_INVALIDATE_ALL_STATEMENTS procedure Document new SYSCS_INVALIDATE_STORED_STATEMENTS procedure
        Hide
        Kim Haase added a comment -

        Thanks for the info, Mamta. Did you mean to reassign the issue to yourself, or shall I pick it up again?

        Show
        Kim Haase added a comment - Thanks for the info, Mamta. Did you mean to reassign the issue to yourself, or shall I pick it up again?
        Mamta A. Satoor made changes -
        Assignee Mamta A. Satoor [ mamtas ] Kim Haase [ chaase3 ]
        Hide
        Mamta A. Satoor added a comment -

        sorry, I thought this was the code change issue. Assigned it to you Kim.

        Show
        Mamta A. Satoor added a comment - sorry, I thought this was the code change issue. Assigned it to you Kim.
        Hide
        Dag H. Wanvik added a comment - - edited

        This routine will only be runnable by the DBO by default, I expect? [Update, I see you discussed this on DERBY-5578]

        Show
        Dag H. Wanvik added a comment - - edited This routine will only be runnable by the DBO by default, I expect? [Update, I see you discussed this on DERBY-5578]
        Hide
        Mamta A. Satoor added a comment -

        Yes, it will only be runnable by DBO unless DBO grants execute permission to the users.

        Show
        Mamta A. Satoor added a comment - Yes, it will only be runnable by DBO unless DBO grants execute permission to the users.
        Hide
        Kim Haase added a comment -

        Attaching DERBY-5793.diff, DERBY-5793.stat, and rrefsyscsinvalidatestoredstmts.html, adding a new topic and modifying the map file:

        A src/ref/rrefsyscsinvalidatestoredstmts.dita
        M src/ref/refderby.ditamap

        Please let me know if any changes are needed. Thanks!

        Show
        Kim Haase added a comment - Attaching DERBY-5793 .diff, DERBY-5793 .stat, and rrefsyscsinvalidatestoredstmts.html, adding a new topic and modifying the map file: A src/ref/rrefsyscsinvalidatestoredstmts.dita M src/ref/refderby.ditamap Please let me know if any changes are needed. Thanks!
        Kim Haase made changes -
        Attachment rrefsyscsinvalidatestoredstmts.html [ 12536114 ]
        Attachment DERBY-5793.diff [ 12536115 ]
        Attachment DERBY-5793.stat [ 12536116 ]
        Kim Haase made changes -
        Issue & fix info Patch Available [ 10102 ]
        Mamta A. Satoor made changes -
        Assignee Kim Haase [ chaase3 ] Mamta A. Satoor [ mamtas ]
        Hide
        Mamta A. Satoor added a comment -

        Kim, thanks for the patch. The changes look good.

        Show
        Mamta A. Satoor added a comment - Kim, thanks for the patch. The changes look good.
        Hide
        Kim Haase added a comment -

        Thank you, Mamta. Did you mean to reassign it to yourself again? Or should I do the commit myself?

        Show
        Kim Haase added a comment - Thank you, Mamta. Did you mean to reassign it to yourself again? Or should I do the commit myself?
        Hide
        Mamta A. Satoor added a comment -

        sorry Kim, don't know how the ownership got changed to me.

        Show
        Mamta A. Satoor added a comment - sorry Kim, don't know how the ownership got changed to me.
        Mamta A. Satoor made changes -
        Assignee Mamta A. Satoor [ mamtas ] Kim Haase [ chaase3 ]
        Hide
        Kim Haase added a comment -

        Thanks very much, Mamta.

        Committed patch DERBY-5793.diff to documentation trunk at revision 1361347.

        Show
        Kim Haase added a comment - Thanks very much, Mamta. Committed patch DERBY-5793 .diff to documentation trunk at revision 1361347.
        Kim Haase made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Issue & fix info Patch Available [ 10102 ]
        Fix Version/s 10.10.0.0 [ 12321550 ]
        Resolution Fixed [ 1 ]
        Hide
        Kim Haase added a comment -

        Closing, since the changes have appeared in the Latest Alpha Manuals.

        Show
        Kim Haase added a comment - Closing, since the changes have appeared in the Latest Alpha Manuals.
        Kim Haase made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Hide
        Kim Haase added a comment -

        Reopening to add more content based on this information from Kathey Marsden:

        Run SYSCS_INVALIDATE_STORED_STATEMENTS whenever you think that your metadata queries or triggers are mis-behaving, for example if they throw a NoSuchMethod error on execution. Derby stores plans for triggers and metadata queries in the database. These should be invalidated automatically on upgrade and at other necessary times. Should you encounter an instance where they are not, you have found a bug that you should report, but one that you can likely work around by running SYSCS_INVALIDATE_STORED_STATEMENTS.

        Show
        Kim Haase added a comment - Reopening to add more content based on this information from Kathey Marsden: Run SYSCS_INVALIDATE_STORED_STATEMENTS whenever you think that your metadata queries or triggers are mis-behaving, for example if they throw a NoSuchMethod error on execution. Derby stores plans for triggers and metadata queries in the database. These should be invalidated automatically on upgrade and at other necessary times. Should you encounter an instance where they are not, you have found a bug that you should report, but one that you can likely work around by running SYSCS_INVALIDATE_STORED_STATEMENTS.
        Kim Haase made changes -
        Resolution Fixed [ 1 ]
        Status Closed [ 6 ] Reopened [ 4 ]
        Hide
        Kim Haase added a comment -

        Attaching DERBY-5793-2.diff and rrefsyscsinvalidatestoredstmts.html, with the information Kathey recommended. I hope it was correct to say that the error is likely to be NoSuchMethodError rather than NoSuchMethodException?

        Thanks for any comments.

        Show
        Kim Haase added a comment - Attaching DERBY-5793 -2.diff and rrefsyscsinvalidatestoredstmts.html, with the information Kathey recommended. I hope it was correct to say that the error is likely to be NoSuchMethodError rather than NoSuchMethodException? Thanks for any comments.
        Kim Haase made changes -
        Attachment rrefsyscsinvalidatestoredstmts.html [ 12550829 ]
        Attachment DERBY-5793-2.diff [ 12550830 ]
        Kim Haase made changes -
        Issue & fix info Patch Available [ 10102 ]
        Hide
        Rick Hillegas added a comment -

        The patch looks good to me. I think it wouldn't hurt to mention both NoSuchMethodError and NoSuchMethodException. Thanks.

        Show
        Rick Hillegas added a comment - The patch looks good to me. I think it wouldn't hurt to mention both NoSuchMethodError and NoSuchMethodException. Thanks.
        Hide
        Kim Haase added a comment -

        Thanks very much, Rick. I'm attaching a revised patch, DERBY-5793-3.diff, along with a new rrefsyscsinvalidatestoredstmts.html, that mentions both the error and the exception. I'll go ahead and commit this patch.

        Show
        Kim Haase added a comment - Thanks very much, Rick. I'm attaching a revised patch, DERBY-5793 -3.diff, along with a new rrefsyscsinvalidatestoredstmts.html, that mentions both the error and the exception. I'll go ahead and commit this patch.
        Kim Haase made changes -
        Attachment DERBY-5793-3.diff [ 12550961 ]
        Attachment rrefsyscsinvalidatestoredstmts.html [ 12550962 ]
        Hide
        Rick Hillegas added a comment -

        Thanks, Kim. +1

        Show
        Rick Hillegas added a comment - Thanks, Kim. +1
        Hide
        Kim Haase added a comment -

        Thanks, Rick.

        Committed patch DERBY-5793-3.diff to documentation trunk at revision 1402509.

        Show
        Kim Haase added a comment - Thanks, Rick. Committed patch DERBY-5793 -3.diff to documentation trunk at revision 1402509.
        Kim Haase made changes -
        Resolution Fixed [ 1 ]
        Status Reopened [ 4 ] Resolved [ 5 ]
        Issue & fix info Patch Available [ 10102 ]
        Hide
        Kim Haase added a comment -

        Closing again, since the new content has appeared in the Latest Alpha Manuals.

        Show
        Kim Haase added a comment - Closing again, since the new content has appeared in the Latest Alpha Manuals.
        Kim Haase made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Gavin made changes -
        Workflow jira [ 12670756 ] Default workflow, editable Closed status [ 12802078 ]

          People

          • Assignee:
            Kim Haase
            Reporter:
            Mamta A. Satoor
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development