Background documentationdb_execute Locate this document in the navigation structure

 

With this DBM command you send an administration command or SQL statement to the database instance. However, for SQL statements in particular it is intended for use via a programming interface.

Unlike when using the DBM command db_executenice, you want the reply to transfer as many data records as possible. The quantity of data transferred is limited by the size of the reply package transferred by the DBM server.

With SELECT statements, the system displays the data records of the reply. If not all the data can be transferred because of the limited reply package size, you can use the db_fetch DBM command to transfer another reply package containing data records. However, this is only possible if you are still in the same database session.

If you specify other SQL statements, the execution of the statement is confirmed with an OK message.

When you execute db_execute, it implicitly opens a session with the database instance and then ends the session once the command has been executed. This database session is opened in AUTOCOMMIT mode, in other words, each SQL statement that is specified is automatically completed with a COMMIT statement.

Recommendation Recommendation

If a series of SQL statements is to be executed and the statements can only be executed by a particular database user, you first start an SQL session with the data of this database user (see: db_connect), execute all SQL statements, and then close either the database session (see: db_release) or the Database Manager CLI.

End of the recommendation.

For more information about SQL statements, see the SQL Reference Manual and SQL Tutorial.

Prerequisites

  • If you want to execute administration commands, you need the AccessUtility server authorization.

  • If you want to execute SQL statements, you need the AccessSQL server authorization.

Structure

db_execute [<user_type>] <statement>

Options

Option

Description

<user_type>

User type, possible values are:

DBM: the first DBM operator

DBA: database system administrator

SAP: special database user in connection with SAP applications

<statement>

Administration command or SQL statement

Result

OK

[END|CONTINUE]

[<record>

<record>

...]

Values for the Reply Fields

Value

Description

END

The complete reply was output.

CONTINUE

More data records are available but were not transferred due to the limited size of the reply package.

<record>

Result data of the SQL statements

The fields in a data record are separated by semicolons. Character strings are output in single quotation marks.

In the event of errors, see Reply Format.

Example

Using db_execute in Command Mode

Call the Database Manager CLI, log on as operator OLEG with the password MONDAY, connect to the database instance DEMODB.

Display the column names and comments of the table USERS in the system table COLUMNS in the schema DOMAIN:

>dbmcli -u OLEG,MONDAY -d DEMODB db_execute SELECT columnname, comment FROM domain.columns WHERE tablename = 'USERS'

OK

END

'OWNER';(LOB)

'GROUPNAME';(LOB)

'USERNAME';(LOB)

'USERMODE';(LOB)

'CONNECTMODE';(LOB)

'MAXTIMEOUT';(LOB)

'COSTWARNING';(LOB)

'COSTLIMIT';(LOB)

'DEFAULTCODE';(LOB)

'CREATEDATE';(LOB)

'CREATETIME';(LOB)

'ALTERDATE';(LOB)

'ALTERTIME';(LOB)

'PWCREADATE';(LOB)

'PWCREATIME';(LOB)

'SERVERDB';(LOB)

'SERVERNODE';(LOB)

'USER_ID';(LOB)

'ISREPLICATIONUSER';(LOB)

'COMMENT';(LOB)

Using db_execute in Session Mode
  1. Log on to the Database Manager CLI in session mode as operator OLEG with password MONDAY, connect to the database instance DEMODB:

    >dbmcli –u OLEG,MONDAY –d DEMODB

    dbmcli on DEMODB>

  2. Open a database session:

    dbmcli on DEMODB>db_connect

    OK

  3. Display the contents of the system table columns of the schema domain:

    dbmcli on DEMODB>db_execute SELECT * FROM domain.columns

    OK

    CONTINUE

    'DBADMIN';'DBADMIN';'ALLOCATORSTATISTIC';'ALLOCATOR';'OPT';'CHAR';'ASCII';40;(nu

    ll);'YES';'SEL+';(null);(null);(null);(null);1;(null);2;'20060406';'00145007';'2

    0060406';'00145007';'SYSTEM';(null)

    'DBADMIN';'DBADMIN';'ALLOCATORSTATISTIC';'USED_BYTES';'OPT';'FIXED';'';20;0;'YES

    ';'SEL+';(null);(null);(null);(null);2;(null);3;'20060406';'00145007';'20060406'

    ;'00145007';'SYSTEM';(null)

    'DBADMIN';'DBADMIN';'ALLOCATORSTATISTIC';'MAXUSED_BYTES';'OPT';'FIXED';'';20;0;'

    YES';'SEL+';(null);(null);(null);(null);3;(null);4;'20060406';'00145007';'200604

    06';'00145007';'SYSTEM';(null)

    'DBADMIN';'DBADMIN';'ALLOCATORSTATISTIC';'ALLOCATED_BYTES';'OPT';'FIXED';'';20;0

    ;'YES';'SEL+';(null);(null);(null);(null);4;(null);5;'20060406';'00145007';'2006

    0406';'00145007';'SYSTEM';(null)

    ...

    ...

  4. Transfer the next reply package:

    dbmcli on DEMODB>db_fetch

    OK

    CONTINUE

    'DBADMIN';'DBADMIN';'LOCKSTATISTICS';'REQTIMEOUT';'OPT';'CHAR';'UNICODE';10;(nul

    l);'YES';'SEL+';(null);(null);(null);(null);10;(null);11;'20060406';'00145007';'

    20060406';'00145007';'SYSTEM';(null)

    'DBADMIN';'DBADMIN';'LOCKSTATISTICS';'LASTWRITE';'OPT';'CHAR';'UNICODE';10;(null

    );'YES';'SEL+';(null);(null);(null);(null);11;(null);12;'20060406';'00145007';'2

    0060406';'00145007';'SYSTEM';(null)

    ...

    ...

  5. Transfer the next reply package:

    dbmcli on DEMODB>db_fetch

    OK

    CONTINUE

    ...

    ...

    ...

  6. Transfer the next reply package:

    dbmcli on DEMODB>db_fetch

    OK

    END

    ...

    ...

    'HOTEL';'MONA';'ROOM';'FREE';'OPT';'FIXED';'';3;0;'YES';'SEL+UPD+';(null);(null)

    ;(null);(null);3;(null);3;'20060406';'00145012';'20060406';'00145012';'TABLE';(n

    ull)

    'HOTEL';'MONA';'ROOM';'PRICE';'OPT';'FIXED';'';6;2;'YES';'SEL+UPD+';(null);(null

    );(null);(null);4;(null);4;'20060406';'00145012';'20060406';'00145012';'TABLE';(null)

  7. End the database session

    dbmcli on DEMODB>db_release

    OK

    dbmcli on DEMODB>

More Information

Database Manager CLI Tutorial, Operating Modes in the Database Manager CLI,

Executing Database Statements

Concepts of the Database System, SQL