-
Notifications
You must be signed in to change notification settings - Fork 18
/
atclause.html
200 lines (163 loc) · 7.09 KB
/
atclause.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
<!-- Creator : groff version 1.22.3 -->
<!-- CreationDate: Mon Mar 18 08:51:03 2024 -->
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta name="generator" content="groff -Thtml, see www.gnu.org">
<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
<meta name="Content-Style" content="text/css">
<style type="text/css">
p { margin-top: 0; margin-bottom: 0; vertical-align: top }
pre { margin-top: 0; margin-bottom: 0; vertical-align: top }
table { margin-top: 0; margin-bottom: 0; vertical-align: top }
h1 { text-align: center }
</style>
<title>atclause</title>
</head>
<body>
<h1 align="center">atclause</h1>
<a href="#NAME">NAME</a><br>
<a href="#SYNTAX">SYNTAX</a><br>
<a href="#DESCRIPTION">DESCRIPTION</a><br>
<a href="#BUGS">BUGS</a><br>
<a href="#COPYRIGHT">COPYRIGHT</a><br>
<a href="#SEE ALSO">SEE ALSO</a><br>
<hr>
<h2>NAME
<a name="NAME"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">atclause
− provide a database for use in rwloadsim</p>
<h2>SYNTAX
<a name="SYNTAX"></a>
</h2>
<pre style="margin-left:11%; margin-top: 1em">atclause ::=
<b> at</b> identifier
|<b> at default</b></pre>
<h2>DESCRIPTION
<a name="DESCRIPTION"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">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 <i>do not</i> use an atclause, the database declared
with the <b>default</b> attribute will be used.</p>
<p style="margin-left:11%; margin-top: 1em">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</p>
<p style="margin-left:11%; margin-top: 1em">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: <b><br>
procedurecall() at database; <br>
sqlstatement at database; <br>
execute at database; ... end;</b></p>
<p style="margin-left:22%;">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.</p>
<p style="margin-left:11%; margin-top: 1em"><b>run <br>
threads N at database <br>
... <br>
end; <br>
end;</b></p>
<p style="margin-left:22%; margin-top: 1em">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.</p>
<p style="margin-left:11%; margin-top: 1em">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.</p>
<p style="margin-left:11%; margin-top: 1em">If you use
<b>at default</b> 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:</p>
<pre style="margin-left:11%; margin-top: 1em">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;</pre>
<p style="margin-left:11%; margin-top: 1em">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 <i>cannot</i> have an
at-clause.</p>
<h2>BUGS
<a name="BUGS"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">The exact
semantics of these "stacked at-clauses" is
somewhat obscure and may change in a later release.</p>
<p style="margin-left:11%; margin-top: 1em">Getting
two-phase-commit supported will be a major project and is
very unlikely to happen.</p>
<h2>COPYRIGHT
<a name="COPYRIGHT"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">Copyright
© 2023 Oracle Corporation <br>
Licensed under the Universal Permissive License v 1.0 as
shown at https://oss.oracle.com/licenses/upl</p>
<h2>SEE ALSO
<a name="SEE ALSO"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em"><a href="databasedeclaration.html">databasedeclaration(1rwl)</a>,
<a href="statement.html">statement(1rwl)</a>, <a href="threadexecution.html">threadexecution(1rwl)</a></p>
<hr>
</body>
</html>