databasedeclaration − RWP*Load Simulator declare a database connection
databasedeclaration ::=
database identifier [ concatenation ]
databasespecification { databasespecification }
databasespecification ::=
username concatenation
| password concatenation
| connect concatenation
| default
| results
| drcp
| reconnect
| [ threads ] dedicated [ requestmark | statemark ]
| cursorcache expression
| sessionpool expression [ .. expression ] [ releasepool ] [ waitsession ]
| connectionclass concatenation
| connectionpool expression [ .. expression ] [ releasepool ]
| connect connectionpool identifier
releasepool ::=
release expression
waitsession ::=
wait expression
| wait expression then break
| wait expression then identifier ( [ concatenation { , concatenation } ] )
In the RWP*Load Simulator a database is a 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 start a database declaration. Attributes are typically constants, mostly strings, but can very well be expressions using public or private variables.
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.
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
For a pool, set 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.
wait
value
wait value then break
wait value then identifier( ...
)
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 with a potentially empty argument list is specified, that procedure will be executed in stead of the procedure that was causing the session get. 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.
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)