databasedeclaration − RWP*Load Simulator declare a database connection
databasedeclaration ::=
database ( identifier | default )
[ concatenation ]
{ databasespecification }
databasespecification ::=
username concatenation
| password concatenation
| connect concatenation
| default
| results
| drcp
| reconnect
| [ threads ] dedicated [ requestmark | statemark ]
| cursorcache expression
| sessionpool expression [ .. expression ] { sessionrelease } [ waitsession ]
| start procedurecall
| connectionclass concatenation
| connectionpool expression [ .. expression ] [ idlerelease ]
| connect connectionpool identifier
sessionrelease ::=
idlerelease
| countrelease
| lifetimerelease
idlerelease ::=
release expression
lifetimerelease ::=
release every expression
countrelease ::=
release count expression
waitsession ::=
wait expression
| wait expression then break
| wait expression then procedurecall
In the RWP*Load Simulator a database is a typically named variable with attributes telling how the actual database connection is established and used. An rwloadsim program can and often does have multiple databases used for different purposes. As an example, you have a database that uses a session pool to connect to your database under test as some application user, another database that uses a dedicated connection as a user with dba privileges used for queries against v$ views, and a third database that is your repository.
You use the database keyword followed by the name to begin a declaration of a named database. Attributes are typically constants, mostly strings, but can very well be expressions using public or private variables. In simple cases with only one database, you can replace the actual name by the keyword default, which is similar to using the -l option to rwloadsim.
All attributes are optional. If neither username nor password are provided, external authentication will be used, which typically means via a wallet. This cannot be done for databases of type drcp or connectionpool, and for sessionpool it implies the pool will be heterogeneous.
username string
Specifies the login name used to connect to the database. To login as one of sysdba, sysoper, sysasm, sysbackup, sysdg, syskm, sysrac provide the username as a string including "as" and the sys authentication wanted. As an example, the string "admin as sysdba" will be a sysdba connection using the username ’admin’. Only dedicated databases can use such sys authentication.
In stead of explicit password and connect, the username can be of the format also used at the command line for the -l option to rwloadsim, i.e. u/p@c. To use that approach with external authentication, use the syntax /@c.
If you provide a concatenation immediately after the name of database, that concatenation is taken as the username; effectively this implies you can omit the keyword username.
password string
Specifies the login password used to connect to the database. Alternatively, it can be provided using the u/p@c format for the username. If not provided, you will be prompted for it.
connect string
Specifies the connect string used to connect to the database. Alternatively, it can be provided as the part after @ using the u/p@c format for the username. The value is provided directly to e.g. the OCIServerAttach call and can therefore be of any format accepted such as short form URL style, "//host/service" or the name of an entry in the tnsnames.ora file. If not provided, the empty string is assumed, which will imply connection via either the TWO_TASK or the ORACLE_SID environment variable. For all practical purposes, this is similar to how sqlplus does it.
default
Make this the database used by default when no atclause is used. It is recommended that you only use this in simple cases where only one database is in use; if multiple databases are needed, you are advised always to explicitly choose them using an atclause.
results
Marks this database as the database used for the rwloadsim repository.
drcp
Use pooling against a DRCP or PRCP database using OCISessionPool. Note that rwloadsim does not verify that you actually use a connect string including either :pooled in URL style or (server=pooled).
reconnect
Only connect when an actual database session is required by your rwloadsim program. Note that this can imply a high number of database connections and dis-connections.
dedicated
threads dedicated
Use dedicated connections that are established once and kept logged on during the complete execution. Each thread will get their own such a database connection. If using the second syntax, the main thread will use the reconnect method.
cursorcache value
Set the value of the OCI_ATTR_STMTCACHESIZE; the default is 20.
sessionpool
value
sessionpool value .. value
Create a session pool, from which a session will be used when a procedure containing sql is being called. In the former case, the pool will have a fixed size, in the latter case, the size will be variable between the two values provided. OCISessionPool is used to implement this.
Note that this is the typical pooling method used by many popular application programming interface such as UCP for Java.
start procedurename ...
This attribute specifies a procedure to be called when rwloadsim establishes or first borrows a session, somewhat like a logon trigger. For sessionpool, the procedure is called the first time a newly created pooled session is borrowed. This first use of the first session in the pool is typically when the database is declared, while for other sessions in the pool the first use and therefore the call to the procedure will be when the session is used by threads.
For dedicated, threads dedicated and reconnect databases, the procedure is called when a session is established. In case of drcp the procedure is called every time a session is borrowed from the server side pool. For reconnect and drcp this means it may be called repeatedly.
The attribute cannot be used for a database declared as connectionpool, and it cannot be used for a database that specifies connect connectionpool.
The practical use is typically to perform some session initialization such as alter session set nls_date_format=... or other per-session settings. For sessionpool, after the procedure has been called, rwloadsim will internally tag the pooled session such that later use of the same session will not call the procedure again. It is not controlled that the actual result of calling the procedure is the same for every new session created by the pool, although such use will lead to inpredictable behavior.
The legacy procedure-call syntax using parentheses is not supported.
connectionpool
value
connectionpool lo .. hi
Create a connectionpool pool, that can subsequently be used as the connection for dedicated, threads dedicated or reconnect database. In itself, a connection pool cannot be used to execute any sql or database statements as it has no session associated with it; a session only gets created when the connection pool is used by an actual database. The pool itself can have a size or a range of sizes, and it is implemented using OCIConnectionPool.
Note that many popular application programming interface such as UCP for Java does not have the counterpart of an OCIConnectionPool.
release
value
release count value
release every value
For a pool, the first option sets the timeout value for idle sessions (OCI_ATTR_SPOOL_TIMEOUT) or connections (OCI_ATTR_CONN_TIMEOUT); the default is 60s. Sessions or connections in the pool that have been idle for more than the release value will disconnect unless the pool already has its minimum size.
For a session pool, you can also set the max usage count (OCI_ATTR_SPOOL_MAX_USE_SESSION) or the max lifetime (OCI_ATTR_SPOOL_MAX_LIFETIME_SESSION) using the either of the two last options. The default is 0 implying sessions will not have a max usage count or max lifetime.
For all three attributes, you may provide a double value, but since the value used in OCI is an integer, only the integer part of the value will be used.
wait
value
wait value then break
wait value then procedurename ...
By default, whenever a worker threads gets a session from a session pool, it will wait until an entry is available. If you provide wait and a value (in seconds), the worker thread will only wait for the specified time. If no then option is specified, either of ORA-24457 or ORA-24459 is returned, but rwloadsim will attempt continuing (as is the case after any oracle error), most likely causing further subsequent errors.
If then break is specified, the procedure that was causing the session get will not be executed; as this is typically done inside a control loop, the control loop will handle what to do next.
Finally, if then and a procedure call is specified, that procedure will be executed in stead of the procedure that was causing the session get. You can optionally have a set of parentheses around the argument list. The procedure called like this cannot perform any database calls (as it is called exactly when a session was unavailable), but it can have the statisticsonly attribute causing the count of failures in getting a session to be maintained in the repository. As for then break, a control loop will typically control what to do next.
The oraerror variable will contain either of the values 24457 or 24459, which you can use in the procedure to e.g. print a message.
connect connectionpool identifier
When creating a dedicated, threads dedicated or reconnect database, rather than using an ordinary connect string, use the named connection pool.
connectionclass string
For drcp and sessionpool this option specifies the connection class that will be set using OCI_ATTR_CONNECTION_CLASS. It is required if the actual connection is to DRCP or PRCP. If your database is declared as drcp, a default of "rwloadsim" will be used and this option can be used to overwrite this default. If your database is declared as sessionpool, there is no default and OCI_ATTR_CONNECTION_CLASS will not be set. Therefore, you must explicitly set connectionclass for databases declared as sessionpool if the actual database is pooled (i.e. DRCP or PRCP).
Dedicated database do not include session pooling and the connection will be established when the database is declared or when a thread is started. The database connection will be held until end of the program or thread.
If a (threads) dedicated database has been given the requestmark attribute, OCIRequestBegin() and OCIRequestEnd() will be called at the beginning and end of the first procedure containing SQL (or having explicit statistics). This can be used to test Application Continuity when a session pool is not in use.
If a (threads) dedicated database has been given the statemark attribute, the state of the session will change to OCI_SESSION_STATEFUL respectively OCI_SESSION_STATELESS at the beginning and end of the first procedure containing SQL (or having explicit statistics). This can improve session sharing with DRCP and PRCP.
Both features, requestmark and statemark, are to be considered experimental and may change in a future release.
For databases that include some type of pooling (sessionpool, reconnect or drcp), session acquire and release takes place at the beginning and end of the first procedure containing SQL, or the first procedure with an explicit statistics attribute.
If you have a default database declared as anything but dedicated, you need to wrap transactions in your main program with execute and end.
The RWP*Load Simulator uses several methods to prevent database connection storms from happening. A connection storm is characterized by an attempt to create many database connections almost simultaneously. If you are using variable sized session pools, are using the reconnect method or extensively use release database, the risk of running into connection storms will be considerably higher than with any other connection methods.
The first example shows the most simple database declaration that will be used by default:
database default "username/{password}@connectstring";
where the three parts are replaced by actual username, password and connect string, which can be an alias in tnsnames.ora or an URL style string such as //host/service.
The following example declares a database named "mypool" using a session pool sized between 3 and 10 and with a cursor cache of size 50:
database mypool username "username" password "{password}" connect "//server/service"
sessionpool 3..10 cursorcache 50;
To use this database that does not have the default attribute, you must explicitly use an at-clause.
If using or establishing a database connection fails with an error that is considered to be of "temporary" or "recoverable" nature, rwloadsim will attempt making the connection again. A few examples of such errors are ORA-00018, "maximum number of sessions exceeded" and ORA-03114 "not connected to ORACLE". The attempt at (re-)establishing the connection will be delayed by some seconds including a random part in order to prevent connection storms. Note that it is not guaranteed that a connection can be established. The full list of these errors is available in the routine rwldberror3 found in rwlerror.c.
Copyright
© 2023 Oracle Corporation
Licensed under the Universal Permissive License v 1.0 as
shown at https://oss.oracle.com/licenses/upl
declaration(1rwl), databasestatement(1rwl), statement(1rwl)