capturing optimization level used for certain sql

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • db2admin
    #1

    capturing optimization level used for certain sql

    hello,

    where can i see what optimization level a certain sql has used ?
    is there any snapshot, event monitor, query patroller information
    which can be used to see that ?

    regards,
    db2admin
    Tags: None
    • stefan.albert
      #2
      Re: capturing optimization level used for certain sql

      you can use
      db2pd -db DBNAME -dynamic -full

      In the section "Dynamic SQL Environments:" is the second last col the
      optimization level.

      Use anchorID and StmtUID to join the informations of the (three)
      sections.


      On Oct 30, 3:42 pm, db2admin <jag...@gmail.c omwrote:
      hello,
      >
      where can i see what optimization level a certain sql has used ?
      is there any snapshot, event monitor, query patroller information
      which can be used to see that ?
      >
      regards,
      db2admin

        Comment

        • db2admin
          #3
          Re: capturing optimization level used for certain sql

          On Oct 31, 10:11 am, "stefan.alb ert" <stefan.alb...@ spb.dewrote:
          you can use
          db2pd -db DBNAME -dynamic -full
          >
          In the section "Dynamic SQL Environments:" is the second last col the
          optimization level.
          >
          Use anchorID and StmtUID to join the informations of the (three)
          sections.
          >
          On Oct 30, 3:42 pm, db2admin <jag...@gmail.c omwrote:
          >
          hello,
          >
          where can i see what optimization level a certain sql has used ?
          is there any snapshot, event monitor, query patroller information
          which can be used to see that ?
          >
          regards,
          db2admin
          thankyou
          i am wondering if all sqls get recorded in the output of this db2pd
          command. i asked my developer to run some application which use some
          SQLs using JDBC and after i ran this db2pd command. i was not able to
          see any of the SQL used by the application my developer ran.
          am i missing something ?

            Comment

            • Pierre StJ
              #4
              Re: capturing optimization level used for certain sql

              db2pd is an evolved snapshot command. It gives you what is there at
              that time.
              If the statement is gone and executed db2pd may not report on it if
              it is not in the cache anymore.

              If you need to catch only some statements you can read into the DB2
              Info. Center about event monitors.
              You can set one for stements and apply either applid or userid for
              filtering. Once the event monitor is started it will catch what you
              want and you can then analyze the output for your info.

              Regards, Pierre.
              On Oct 31, 2:00 pm, db2admin <jag...@gmail.c omwrote:
              On Oct 31, 10:11 am, "stefan.alb ert" <stefan.alb...@ spb.dewrote:
              >
              >
              >
              you can use
              db2pd -db DBNAME -dynamic -full
              >
              In the section "Dynamic SQL Environments:" is the second last col the
              optimization level.
              >
              Use anchorID and StmtUID to join the informations of the (three)
              sections.
              >
              On Oct 30, 3:42 pm, db2admin <jag...@gmail.c omwrote:
              >
              hello,
              >
              where can i see what optimization level a certain sql has used ?
              is there any snapshot, event monitor, query patroller information
              which can be used to see that ?
              >
              regards,
              db2admin
              >
              thankyou
              i am wondering if all sqls get recorded in the output of this db2pd
              command. i asked my developer to run some application which use some
              SQLs using JDBC and after i ran this db2pd command. i was not able to
              see any of the SQL used by the application my developer ran.
              am i missing something ?

                Comment

                • db2admin
                  #5
                  Re: capturing optimization level used for certain sql

                  On Nov 2, 5:51 pm, Pierre StJ <p.stjacq...@vi deotron.cawrote :
                  db2pd is an evolved snapshot command. It gives you what is there at
                  that time.
                  If the statement is gone and executed db2pd may not report on it  if
                  it is not in the cache anymore.
                  >
                  If you need to catch only some statements you can read into the DB2
                  Info. Center about event monitors.
                  You can set one for stements and apply either applid or userid for
                  filtering. Once the event monitor is started it will catch what you
                  want and you can then analyze the output for your info.
                  >
                  Regards, Pierre.
                  On Oct 31, 2:00 pm, db2admin <jag...@gmail.c omwrote:
                  >
                  On Oct 31, 10:11 am, "stefan.alb ert" <stefan.alb...@ spb.dewrote:
                  >
                  you can use
                  db2pd -db DBNAME -dynamic -full
                  >
                  In the section "Dynamic SQL Environments:" is the second last col the
                  optimization level.
                  >
                  Use anchorID and StmtUID to join the informations of the (three)
                  sections.
                  >
                  On Oct 30, 3:42 pm, db2admin <jag...@gmail.c omwrote:
                  >
                  hello,
                  >
                  where can i see what optimization level a certain sql has used ?
                  is there any snapshot, event monitor, query patroller information
                  which can be used to see that ?
                  >
                  regards,
                  db2admin
                  >
                  thankyou
                  i am wondering if all sqls get recorded in the output of this db2pd
                  command. i asked my developer to run some application which use some
                  SQLs using JDBC and after i ran this db2pd command. i was not able to
                  see any of the SQL used by the application my developer ran.
                  am i missing something ?
                  thankyou Pierre,

                  I thought about even monitor on statement but did not try yet because
                  i was in doubt if this will capture optimization level information
                  I guess i will try and find out

                    Comment

                    • stefan.albert
                      #6
                      Re: capturing optimization level used for certain sql

                      This information is not in the data captured with event monitor for
                      statement.
                      Also the snapshot does not give this information.
                      db2pd seems to be the only way...
                      >
                      I thought about even monitor on statement but did not try yet because
                      i was in doubt if this will capture optimization level information
                      I guess i will try and find out- Hide quoted text -
                      >

                        Comment

                        • Salvatore Vacca
                          #7
                          Re: capturing optimization level used for certain sql

                          On 5 Nov, 13:18, "stefan.alb ert" <stefan.alb...@ spb.dewrote:
                          This information is not in the data captured with event monitor for
                          statement.
                          Also the snapshot does not give this information.
                          db2pd seems to be the only way...
                          >
                          >
                          >
                          I thought about even monitor on statement but did not try yet because
                          i was in doubt if this will capture optimization level information
                          I guess i will try and find out- Hide quoted text -
                          Which DB2 release are you using?
                          Another way to capture and store optimization level used by queries is
                          the db2 audit feature; I can ensure you that on db2 v9.5 this
                          information is reported.
                          To know how to set db2 v9.5 audit , see my post on another forum:

                          http://www-128.ibm.com/developerworks/forums/thread.jspa?messageID=14158701&#14158701


                          Regards
                          Salvatore Vacca

                            Comment

                            • Salvatore Vacca
                              #8
                              Re: capturing optimization level used for certain sql

                              On 5 Nov, 13:18, "stefan.alb ert" <stefan.alb...@ spb.dewrote:
                              This information is not in the data captured with event monitor for
                              statement.
                              Also the snapshot does not give this information.
                              db2pd seems to be the only way...
                              >
                              >
                              >
                              I thought about even monitor on statement but did not try yet because
                              i was in doubt if this will capture optimization level information
                              I guess i will try and find out- Hide quoted text -

                              Which DB2 release are you using?
                              Another way to capture and store optimization level used by queries is
                              the db2 audit feature; I can ensure you that on db2 v9.5 this
                              information is reported.
                              To know how to set db2 v9.5 audit , see my post on another forum:

                              http://www-128.ibm.com/developerworks/forums/thread.jspa?messageID=14158701&#14158701


                              Regards
                              Salvatore Vacca

                                Comment

                                Previous template Next
                                Working...