- SAP Managed Tags:
The SQL Editor allows you to execute freely written SQL-statements. The SQL Editor supports only read statements. UPDATE, DELETE, INSERT statements or DDL (Data Definition Language) statements are not allowed.
This new option is available for SAP NetWeaver 7.0 Enhancement Package 2 and SAP NetWeaver 7.30 or higher if the monitored system has been configured to use the data collected by the SAP Database Performance Collector for IBM i (see SAP Note 2033326).
To start the SQL Editor, call transaction DBACOCKPIT and select the system you want to work with. In the navigation frame, choose Diagnostics > SQL Editor.
After you enter the SQL statement in the tab Input Query, press the pushbutton Execute to get the results. The results are displayed in the tab Result. If you want to execute another query, enter a new SQL statement in the tab Input Query and repeat the process.
The drop-down list History contains a history of the latest executed SQL statements. You can store a query for later use by pressing the pushbutton Save Query. You can load a previously saved query by pressing the pushbutton Load Query. The pushbutton Delete Query removes a previously saved query. The input field Rows allows you to limit the number of rows displayed in the tab Result after the result set has been retrieved from the database.
For security and data confidentiality reasons, the execution of syntactically and semantically valid queries can be restricted by the maintenance of the S_TABU_SQL authority object.
This is especially interesting if you have the requirement to restrict the access to objects inside the library associated with the monitored system.
For more information about the maintenance of the S_TABU_SQL authority object available, see SAP Note 1568173.
The S_TABU_SQL authority object is included in the role SAP_BC_S_DBCON_USER and SAP_BC_S_DBCON_ADMIN and in its default configuration it contains these values:
ACTVT = 33 (Read)
DBSID = *
TABLE = *
TABOWNER = *
This configuration allows read operations for all tables for the local system and all other remote systems.
The only restriction available is the one provided by the database authorities assigned to the connect user on the target Db2 for IBM i database.
Nevertheless, some libraries and the objects inside those libraries are not considered for the S_TABU_SQL authority check.
These objects are those located in the schemas: QSYS2, QSYS, SYSIBM, SYSIBMADM, SYSPROC, SYSTOOLS, SAPDB4M for non-switchable ASPs, SAPDB4M<nnn> for independent ASPs and R3SAP400, and the QAQQINI table inside the QUSRSYS library.
There is also a verification to avoid the execution of queries that read from objects not created within the monitored schema (it does not matter if the monitored schema is local or remote).
If you want to display the access path used to resolve the SQL statement, press the pushbutton Explain. The next screen Explain SQL Statement displays the access path as a tree. This tree based representation of the access plan is compatible in terms of the information provided with the browser-based EXPLAIN Access Plan screen.
You can access the details of a node by double-clicking on it. This action displays the detailed node information in the left pane of the screen. If the value in a cell of the table has been truncated, you can press the pushbutton Expand Value to display the complete information. The pushbutton Collapse Value reverts this action.
The detailed information of the first node contains an entry Original SQL Statement that displays the statement used for explain and contains the general information of the connected system.
If the node in the tree accesses a catalog object (i.e.: a table, an index or a table function), you can press the pushbutton Catalog Information to navigate to the corresponding option in the folder Diagnostics > System Catalog Views. You can easily identify this kind of nodes by the values in the columns Schema, Object Name and Number Of Entries. The Number Of Entries value is the number of rows in the table or view or the number of index entries. Based on the estimations provided by the database, the most expensive CPU-bound and the most expensive I/O-bound processing operations are marked in a different color (if one node has both most expensive values, only the largest value is marked).
You can access the browser-based EXPLAIN Access Plan screen by just pressing the pushbutton Graphical Explain. This action starts a browser where you can see a graph representation of the explain Access Plan.
Are you interested in this new option? Check SAP Note 2489321 – IBM i: Enhancements in the DBA Cockpit for Db2 on IBM i - SQL Editor to get information about the support package levels or correction instructions needed for the SQL Editor.
This new option is available for SAP NetWeaver 7.0 Enhancement Package 2 and SAP NetWeaver 7.30 or higher if the monitored system has been configured to use the data collected by the SAP Database Performance Collector for IBM i (see SAP Note 2033326).
To start the SQL Editor, call transaction DBACOCKPIT and select the system you want to work with. In the navigation frame, choose Diagnostics > SQL Editor.
After you enter the SQL statement in the tab Input Query, press the pushbutton Execute to get the results. The results are displayed in the tab Result. If you want to execute another query, enter a new SQL statement in the tab Input Query and repeat the process.
The drop-down list History contains a history of the latest executed SQL statements. You can store a query for later use by pressing the pushbutton Save Query. You can load a previously saved query by pressing the pushbutton Load Query. The pushbutton Delete Query removes a previously saved query. The input field Rows allows you to limit the number of rows displayed in the tab Result after the result set has been retrieved from the database.
For security and data confidentiality reasons, the execution of syntactically and semantically valid queries can be restricted by the maintenance of the S_TABU_SQL authority object.
This is especially interesting if you have the requirement to restrict the access to objects inside the library associated with the monitored system.
For more information about the maintenance of the S_TABU_SQL authority object available, see SAP Note 1568173.
The S_TABU_SQL authority object is included in the role SAP_BC_S_DBCON_USER and SAP_BC_S_DBCON_ADMIN and in its default configuration it contains these values:
ACTVT = 33 (Read)
DBSID = *
TABLE = *
TABOWNER = *
This configuration allows read operations for all tables for the local system and all other remote systems.
The only restriction available is the one provided by the database authorities assigned to the connect user on the target Db2 for IBM i database.
Nevertheless, some libraries and the objects inside those libraries are not considered for the S_TABU_SQL authority check.
These objects are those located in the schemas: QSYS2, QSYS, SYSIBM, SYSIBMADM, SYSPROC, SYSTOOLS, SAPDB4M for non-switchable ASPs, SAPDB4M<nnn> for independent ASPs and R3SAP400, and the QAQQINI table inside the QUSRSYS library.
There is also a verification to avoid the execution of queries that read from objects not created within the monitored schema (it does not matter if the monitored schema is local or remote).
If you want to display the access path used to resolve the SQL statement, press the pushbutton Explain. The next screen Explain SQL Statement displays the access path as a tree. This tree based representation of the access plan is compatible in terms of the information provided with the browser-based EXPLAIN Access Plan screen.
You can access the details of a node by double-clicking on it. This action displays the detailed node information in the left pane of the screen. If the value in a cell of the table has been truncated, you can press the pushbutton Expand Value to display the complete information. The pushbutton Collapse Value reverts this action.
The detailed information of the first node contains an entry Original SQL Statement that displays the statement used for explain and contains the general information of the connected system.
If the node in the tree accesses a catalog object (i.e.: a table, an index or a table function), you can press the pushbutton Catalog Information to navigate to the corresponding option in the folder Diagnostics > System Catalog Views. You can easily identify this kind of nodes by the values in the columns Schema, Object Name and Number Of Entries. The Number Of Entries value is the number of rows in the table or view or the number of index entries. Based on the estimations provided by the database, the most expensive CPU-bound and the most expensive I/O-bound processing operations are marked in a different color (if one node has both most expensive values, only the largest value is marked).
You can access the browser-based EXPLAIN Access Plan screen by just pressing the pushbutton Graphical Explain. This action starts a browser where you can see a graph representation of the explain Access Plan.
Are you interested in this new option? Check SAP Note 2489321 – IBM i: Enhancements in the DBA Cockpit for Db2 on IBM i - SQL Editor to get information about the support package levels or correction instructions needed for the SQL Editor.
2 Comments
