Executing a Succession of Database Statements 

Use

Before you execute a succession of database statements, you first explicitly open a database session. In this command, you can enter which user you want to open the database session with. If you do not specify a user, the system uses the data of the first DBM operator. Note that in this database session, you will only be able to execute 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 thus can no longer be rolled back with a ROLLBACK statement.

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

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 generated result set may exceed the size of the reply package (approx. 16 KB). In this case, the Database Manager indicates with the keyword CONTINUE in the output that there is more data that could be displayed. To display this additional data, use the DBM command db_fetch.

If you use db_executenice and want to move on to the subsequent data record, use the DBM command db_fetchnice.

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

It is also necessary to open database session explicitly to execute a single database statement if 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 necessary because displaying the results requires you to execute multiple database statements in the same database session.

To read the prerequisites, see Database Manager CLI, db_connect, db_execute, db_executenice, db_fetch, db_fetchnice, db_release

See also:

Glossary, AUTOCOMMIT Mode, ROLLBACK

Scenario 1

You want to create three new database users and execute the requisite 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. If you do not specify a user for this DBM command, the system uses the data of the first DBM user.

See also:

Concepts of the Database System, Users, Authentication and Authorizations

Reference Manual, CREATE USER Statement

Procedure

...

       1.      Call the Database Manager CLI in session mode, log on as operator OLEG with the password MONDAY, 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 instance.

Scenario 2

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

Procedure

...

       1.      Call the Database Manager CLI in session mode, log on as operator OLEG with the password MONDAY, 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 takes 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

After each execution of the DBM command db_fetchnice, the subsequent data record in the result set is displayed.