Derby
  1. Derby
  2. DERBY-4783

Add all data in XPlain tables into the XML file generated by PlanExporter Tool

    Details

    • Type: Improvement Improvement
    • Status: In Progress
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Tools
    • Labels:
      None

      Description

      Rick has suggested this in Derby-4587. I think this way it allows a user to create his own style sheets and grab only the data he needed.

      1. derby-4783-a.diff
        41 kB
        Nirmal Fernando

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Promoted this improvement request from sub-task to improvement, so that the parent issue can be closed.

          Show
          Knut Anders Hatlen added a comment - Promoted this improvement request from sub-task to improvement, so that the parent issue can be closed.
          Hide
          Nirmal Fernando added a comment -

          Hi,

          I added all entries in Xplain tables to the PlanExporter's XML file. I used enums in order to make things efficient, so that made PlanExporter to only work in Java5 or higher.

          Thus this improvement needs documentation changes which will be covered under the sub task.

          I am glad if someone can review the patch and commit if no special comments!

          Thanks.

          Show
          Nirmal Fernando added a comment - Hi, I added all entries in Xplain tables to the PlanExporter's XML file. I used enums in order to make things efficient, so that made PlanExporter to only work in Java5 or higher. Thus this improvement needs documentation changes which will be covered under the sub task. I am glad if someone can review the patch and commit if no special comments! Thanks.
          Hide
          Bryan Pendleton added a comment -

          Hi Nirmal, thanks for continuing to work on the PlanExporter, it is much appreciated.

          It seems that each SELECT statement that we are issuing against the database is
          retrieving only a single field. I think it would be better if we could retrieve all the
          columns at once.

          That is, instead of issuing:

          select stmt_name from SYSXPLAIN_RESULTSETS where STMT_ID = ?
          select stmt_type from SYSXPLAIN_RESULTSETS where STMT_ID = ?
          select stmt_text from SYSXPLAIN_RESULTSETS where STMT_ID = ?

          and so forth, we should just do:

          select * from SYSXPLAIN_RESULTSETS where STMT_ID = ?

          and then extract each column from that single result by calling:

          rs.getString("stmt_name")
          rs.getString("stmt_type")
          rs.getString("stmt_text")

          etc.

          Show
          Bryan Pendleton added a comment - Hi Nirmal, thanks for continuing to work on the PlanExporter, it is much appreciated. It seems that each SELECT statement that we are issuing against the database is retrieving only a single field. I think it would be better if we could retrieve all the columns at once. That is, instead of issuing: select stmt_name from SYSXPLAIN_RESULTSETS where STMT_ID = ? select stmt_type from SYSXPLAIN_RESULTSETS where STMT_ID = ? select stmt_text from SYSXPLAIN_RESULTSETS where STMT_ID = ? and so forth, we should just do: select * from SYSXPLAIN_RESULTSETS where STMT_ID = ? and then extract each column from that single result by calling: rs.getString("stmt_name") rs.getString("stmt_type") rs.getString("stmt_text") etc.
          Hide
          Nirmal Fernando added a comment -

          Hi Bryan, it's my pleasure to work on Derby.

          The problem I faced was we are issuing a query like following:

          select 'no_opens="'|| TRIM(CHAR(NO_OPENS))|| '"' from SYSXPLAIN_RESULTSETS where STMT_ID = ?

          So, "select *" was not possible. Is there an alternative?

          Thanks!

          Show
          Nirmal Fernando added a comment - Hi Bryan, it's my pleasure to work on Derby. The problem I faced was we are issuing a query like following: select 'no_opens="'|| TRIM(CHAR(NO_OPENS))|| '"' from SYSXPLAIN_RESULTSETS where STMT_ID = ? So, "select *" was not possible. Is there an alternative? Thanks!
          Hide
          Bryan Pendleton added a comment -

          I think that the alternative is that we'd need to do this string processing in Java code,
          rather than in the SQL.

          But, in fact, I think this may not be very hard. For instance, I believe that

          rs.getString("no_opens")

          will already automatically do the equivalent of the "TRIM(CHAR(NO_OPENS))"
          processing. So all we're really talking about here is doing something like:

          "||"+rs.getString("no_opens") + "||"

          Although, it strikes me that we should eliminate the "||" processing as well, as
          I suspect these are just markers that will be used later in the code to parse
          the values apart, which doesn't seem like the right approach either. It seems
          like we should make the ResultSet object available to the bit of code which
          needs to fetch the value of the NO_OPENS column, so that code can just do
          the getInt() or getString() call itself, directly.

          I understand that this would mean a bit of refactoring of the current PlanExporter
          code, so we should study it some more.

          Show
          Bryan Pendleton added a comment - I think that the alternative is that we'd need to do this string processing in Java code, rather than in the SQL. But, in fact, I think this may not be very hard. For instance, I believe that rs.getString("no_opens") will already automatically do the equivalent of the "TRIM(CHAR(NO_OPENS))" processing. So all we're really talking about here is doing something like: "||"+rs.getString("no_opens") + "||" Although, it strikes me that we should eliminate the "||" processing as well, as I suspect these are just markers that will be used later in the code to parse the values apart, which doesn't seem like the right approach either. It seems like we should make the ResultSet object available to the bit of code which needs to fetch the value of the NO_OPENS column, so that code can just do the getInt() or getString() call itself, directly. I understand that this would mean a bit of refactoring of the current PlanExporter code, so we should study it some more.
          Hide
          Nirmal Fernando added a comment -

          I agree with Bryan, I think it's better to study more before refactoring!

          I unassigned my self from this issue for now!

          Thanks.

          Show
          Nirmal Fernando added a comment - I agree with Bryan, I think it's better to study more before refactoring! I unassigned my self from this issue for now! Thanks.

            People

            • Assignee:
              Unassigned
              Reporter:
              Nirmal Fernando
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development