Procedure documentationExecuting a Succession of Database Statements Locate this document in the navigation structure

 

Before you can execute a succession of database statements, you must explicitly open a database session.

In the command for opening the database session, you can specify the user you want the database session to be opened with. If you do not specify a user, the system uses the data of the first DBM operator.

Note that within a database session, you can execute only those database statements for which the user used to open the database session is authorized.

The database session is always opened in AUTOCOMMIT mode. This means that executed SQL statements are automatically closed with a COMMIT statement and therefore cannot be rolled back with a ROLLBACK statement.

Within the database session, you use the DBM command db_execute or db_executenice to transfer a database statement to a database.

Which of these commands is preferable in a given situation depends on whether you want to display the largest possible result set of the database statement or display the result record-by-record.

If you use db_execute, the result set may exceed the size of the reply package (approx. 16 KB). In this case, Database Manager indicates with the keyword CONTINUE that there is more data. To display this additional data, use the DBM command db_fetch.

If you use db_executenice, each time you want to display the next data record, you must use the DBM command db_fetchnice.

When all the result data has been displayed, close the database session or Database Manager CLI, if applicable.

You must also explicitly open a database session if you want to execute a single database statement and you expect a result set that exceeds the size of the reply package, or if you want to display the result set record-by-record. This is because displaying the results requires that you execute multiple database statements in the same database session.

Prerequisites

You can find the prerequisites under Database Manager CLI,

db_connect

db_execute

db_executenice

db_fetch

db_fetchnice

db_release

Procedure

Scenario 1

You want to create three new database users and execute the required SQL statements in immediate succession. To do this, you have to open a database session.

Only a database system administrator is authorized to create new database users. You must therefore open the database session with the data of the database system administrator.

Procedure

  1. Call Database Manager CLI in session mode, log on as operator OLEG with the password MONDAY, and connect to the database instance DEMODB:

    >dbmcli –u OLEG,MONDAY –d DEMODB

    dbmcli on demodb>

  2. Start a database session as database administrator DBADMIN with password SECRET:

    dbmcli on demodb>db_connect DBADMIN,SECRET

    OK

  3. Execute the SQL statement to create user MONA with password RED and the properties DBA and NOT EXCLUSIVE:

    dbmcli on demodb>db_execute CREATE USER mona PASSWORD red DBA NOT EXCLUSIVE

    OK

  4. Execute the SQL statement to create user DAVID with password BLUE and the property RESOURCE:

    dbmcli on demodb>db_execute CREATE USER david PASSWORD blue RESOURCE

    OK

  5. Execute the SQL statement to create user BORIS with password PINK and the property STANDARD:

    dbmcli on demodb>db_execute CREATE USER boris PASSWORD pink STANDARD

    OK

  6. Close the database session:

    dbmcli on demodb>db_release

    OK

Result

Database users MONA, DAVID, and BORIS have been created with the specified properties and can now log on to the database.

Scenario 2

You want to execute an SQL statement to display the contents of table users. The data of the result set should be displayed record-by-record.

Procedure

  1. Call the Database Manager CLI in session mode, log on as operator OLEG with the password MONDAY, and connect to the database instance DEMODB:

    >dbmcli -u OLEG,MONDAY -d DEMODB

    dbmcli on demodb>

  2. Start a database session (if no user is specified, the system uses the data of the first DBM operator):

    dbmcli on demodb>db_connect

    OK

  3. Execute the SQL statement for record-by-record display of the contents of table users (user name, connection mode and user ID):

    dbmcli on demodb>db_executenice SELECT username,connectmode,user_id FROM users

    OK

    CONTINUE

    USERNAME = 'DBM'

    CONNECTMODE = 'MULTIPLE'

    USER_ID = 0

    ---

  4. Display the next data record:

    dbmcli on demodb>db_fetchnice

    OK

    CONTINUE

    USERNAME = 'DBADMIN'

    CONNECTMODE = 'MULTIPLE'

    USER_ID = 10

    ---

  5. Display the next data record:

    dbmcli on demodb>db_fetchnice

    OK

    USERNAME = 'MONA'

    CONNECTMODE = 'MULTIPLE'

    USER_ID = 27

Result

Each time the DBM command db_fetchnice is executed, the next data record in the result set is displayed.

More Information

Database Administration, Managig Users

SQL Reference Manual, CREATE USER Statement

Glossary, AUTOCOMMIT Mode, ROLLBACK