atclause

NAME
SYNTAX
DESCRIPTION
BUGS
COPYRIGHT
SEE ALSO

NAME

atclause − provide a database for use in rwloadsim

SYNTAX

atclause ::=
  at identifier
| at default

DESCRIPTION

The atclause is used in several rwloadsim statements where database activity, e.g. execution of sql statement takes place. The purpose is to actively choose one of the declared databases. If you do not use an atclause, the database declared with the default attribute will be used.

To understand how the atclause is used, it is important to understand when connect and disconnect takes place. In the typical cases, the choice of database is determined at compile time before actual execution of code takes place. Most simulation runs will have one run command often with multiple threads that each at some point in time execute a control loop. Whenever a procedure with some SQL in it (typically inside a control loop) is executed, rwloadsim will ensure there is a database session upon start of the procedure, and potentially release it later upon exit. If the database is dedicated the database logon and logoff is already done (at the start/end of rwloadsim or a thread), so ensuring a database is effectively a no-op. In the other cases, an actual database session will be acquired, typically from a session pool or a DRCP pool, or through a complete logon

This mechanism is an important part of rwloadsim, and it is also what is behind the statistics gathering, where both the time to get a database and time to actually perform database work is registered. This mechanism applies in the following cases when an atclause is used outside a declared procedure or function, i.e. directly at the "main level" of your rwloadsim code:
procedurecall() at database;
sqlstatement at database;
execute at database; ... end;

When any of these is found directly in your main program, a database session will be acquired, the code will be executed and the session will be released. In case the named database is marked dedicated, a database session will already exist for the entire duration of execution of rwloadsim.

run
threads N at database
...
end;
end;

When threads are started and an explicit database is named, the entire thread will use that database. If the database is declared dedicated (or threads dedicated), an actual logon will be performed when the thread starts and logoff will be performed when it terminates. Otherwise (i.e. with any pooling), whenever the first procedure executing SQL (or having the statistics attribute) is called, that procedure will acquire and release a database session at start and exit. Note that any rollback or commit executed is always against the database session that is in use as a result of this mechanism; this implies two phase commit it not supported.

If you are using the at clause at any other place (typically inside a declared procedure), the behavior is somewhat different. In such cases, any existing database session will be "stacked", and a new database session will be acquired from a session pool as a completely new database connection for the duration of the SQL, procedure call or execution block. The session will be released or a disconnect will be done after finishing the SQL, procedure call or execution block. As sessions are acquired and released, only databases that have real client side pooling (session pool, reconnect or drcp) can be used. Note that only queries can be executed using this approach and that two-phase commit is not supported.

If you use at default inside a procedure, the effect is to use the database that was chosen at compile time for the top level procedure or thread being executed; this is not necessarily the database that has the default attribute. Take this example:

procedure copytodest()
  sql selsource select ... end;
  sql insdest insert ... end;


  for selsource at sourcedb loop
    insdest at default; # destdb would imply a new session!
  end loop;
  commit; # always against the effective database in use
end;
copytodest() at destdb;

When the procedure copytodest() is being called at the last line above, the named database (destdb) is being used during the call. Inside the procedure, a cursor loop is being executed selecting rows from the named database (sourcedb), and for each row returned, the SQL insdest is executed against the actual database session used by the procedure. This is the same database later used when commit is executed. Effectively, rows are copied from one database to another. Any array interface - for both the query and the insert - will be used. Note that two-phase commit is not supported, and that therefore commit and rollback cannot have an at-clause.

BUGS

The exact semantics of these "stacked at-clauses" is somewhat obscure and may change in a later release.

Getting two-phase-commit supported will be a major project and is very unlikely to happen.