Which one has better opportunities to test or DBA

Perhaps you know the situation where you want to quickly send a short SQL statement to your SAP system without having to invest a lot in ABAP programming. You may not have the ABAP knowledge to extract data from a table, but you are familiar with SQL. In these cases you should take a closer look at transaction DBACOCKPIT. In addition to many other functions for managing the database, this transaction provides you with an editor with which you can easily execute your SQL queries on your SAP system. With this method you will receive the result in the GUI shortly after sending the query.

Procedure for executing an SQL query

In order to be able to call the editor for the SQL queries in DBACOCKPIT, the following requirements exist for the user:

  • The user needs the appropriate rights to execute transactions SM49 and SM69.
  • The values ​​STOR and SMSS must be maintained in the authorization object S_ADMI_FCD.
  • The database connection must be maintained for the SQL queries. You can get the current status of a database connection by displaying the DBCON table.
  • Rights for calling up the table (s) to be queried must be assigned. You can find further details on this in the section “Further information on DBACOCKPIT” of this blog post.

After your user has the necessary rights for the SQL editor, proceed as follows:

  • Call the transaction “DBACOCKPIT”.
  • Open the following folder structure in the database administration: Performance -> Additional Functions -> SQL Command Editor
  • Enter your first query in the input query.
  • Then click on Execute or alternatively press F8

View of the DBA cockpit

Calling the input query and sending the first SQL query

View the results of a query to query the users of a system

 

Experience and alternatives

It should be mentioned at this point that it only makes sense to read the tables using the SELECT statement in order to get a quick view of the results. Using the DBACOCKPIT it is not possible to create entire table structures using Create Table. For such purposes, SAP provides other, better options. Another important point is that as soon as a user has the necessary authorizations to use the DBACOCKPIT transaction, he or she can potentially read the entire SAP system (with the appropriate authorizations for the tables). For example, the entire user table can be read out with a query. Therefore, the transaction should be treated with caution and should only be assigned to administrators.

In addition, it is possible to save SQL statements that have already been executed and thus to execute them again at any time in order to recognize changes in the result set without having to rewrite the SQL statement each time. The editor also allows you to start querying the SQL statements in the background. You get the result by calling transaction SM37, in which the result is output in a spool file.

The right authorization

The authorization to control the calls by the DBACOCKPIT is handled in a similar way to transaction SE16 / SE16N. When the table is called, the authorization object S_TABU_DIS or S_TABU_NAM is checked with a specific activity. It is only possible to access the tables or table authorization groups for which the corresponding values ​​have been assigned in the authorization objects mentioned. You can read more about the assignment of authorizations to individual tables here.

Finally

Have you had similar experiences with the DBACOCKPIT or do you know alternative ways of executing SQL queries on your system? I look forward to your feedback and cordially invite you to comment on this post.

Do you need support with the implementation? Our author is a consultant on this topic. Ask him about the RZ10.de partner product consultant for SAP Basis

This article is also available for download:

Download the article as a PDF document

Download the blog post here free of charge and without obligation as a PDF document.