You should use Execute dynamic SQL in batch if you want to execute
the generated SQL in batch mode, as opposed to submitting it through the Db2 SPUFI
utility.
The SQLOUT DD file generated during the detail report
run is used as input to this batch process. The SQL is executed dynamically.
If you requested redo SQL during the detail report run, the execution
of the generated SQL will redo those changes.
If you requested undo SQL during the detail report run, the execution
of the generated SQL will undo those changes.
This process is very similar to running your SQL through
SPUFI, except it executes in batch mode. The SQL should not be modified
in any way prior to execution without complete knowledge of the results
of such actions. As with any SQL execution, errors can occur. Restart
capabilities are built into this process.
From the Db2 Log Analysis Tool Selection menu, select option 5,
Execute dynamic SQL in batch.
The Execute dynamic SQL in batch:
Fast Apply panel appears.
Figure 1. Execute
dynamic SQL in batch: Fast Apply panel (top half of panel)
V3.6.0 -------- Execute dynamic SQL in batch: Fast Apply --------- SC01/SS1A
COMMAND ===>
More: +
*Action.............. E (E - Edit, S - Submit)
Job Identifier...... (8 character maximum)
*Db2 subsystem name.. SS1A (Db2 SSID)
LE runtime loadlibs.
*Restart............. I (A - Auto, I - Ignore, S - Startover,
N - New, M - Number)
Restart Name........ (not used if Restart=I)
Restart SQL number.. (only used if Restart=M)
*Commit Frequency.... 1
*Commit Type......... T (T - Transaction, S - Statement)
*Loglevel............ 1 (1-9 only)
*Bypass Multi-Row Inserts. (Y - Off, N - On)
*Table filters............ N (Y/N)
*ON SQLERROR.............. S (S - Stop, W - Warn,
I - Ignore, R - Rollback)
*ON SQLWARN.......... I (S - Stop, W - Warn,
I - Ignore, R - Rollback)
*ON Multiple rows.... I (S - Stop, W - Warn,
I - Ignore, R - Rollback)
Figure 2. Execute dynamic SQL in batch: Fast
Apply panel (bottom half of panel)
Execute dynamic SQL in batch: Fast Apply panel fields
Action
Type E or S.
E
Indicates that the temporary JCL created as a result of the data
entered on the panel will be displayed in edit mode.
S
Indicates the temporary JCL will be submitted automatically.
Job identifier
This is the same job identifier as used in general and detail
reports. It is an eight-character maximum field that is used to identify
the data set names for the different runs of reports. It must be a
valid node in a data set name.
Db2 subsystem name
Enter a valid subsystem ID (SSID) identifying the Db2 subsystem against which the batch SQL will
run. The Log Analysis Tool plans must be bound at this SSID. It does not
have to be the same SSID against which the SQL was generated. In other words, you can be performing
data replication, but the tables named in the SQL must exist at this SSID.
LE runtime loadlibs
(Optional) Enter the fully qualified name (no
quotes) of your Language Environment (LE) runtime load libraries.
Put a single load library on each line. These libraries will likely
be named CEE, or something similar, though your site has complete
control over the naming convention of these libraries. These libraries
are used by the C program handling the fast apply SQL process. Contact
your systems programming staff if you do not know the names.
Restart
Enter one of the values:
A
AUTO mode. The restart program determines if there is a row in
the restart table for this process and proceeds as follows:
If there is not a row in the restart table, it creates one and
starts processing SQLIN from the start of the file.
If there is a row in the restart table and the row indicates that
the last run was a success, the restart program starts processing
SQLIN from the start of the file.
If there is a row in the restart table and the row indicates that
the last run failed, the restart program starts processing SQLIN at
the statement following the last successful commit.
I
IGNORE mode. No restart is possible, and the restart table and
process name are ignored.
S
STARTOVER mode. The restart program determines if there is a row
in the restart table for this process name and proceeds as follows:
If there is not a row, the restart program creates one and starts
processing SQLIN from the start of the file.
If there is a row, the restart program resets the row and starts
processing SQLIN from the start of the file.
N
NEW mode. The restart program assumes that this is a totally new
process, and creates a new entry for the process name in the restart
table. If the process name already exists, the program terminates.
M
NUMBER mode. The restart program starts processing SQL at the
specified SQL statement number.
Restart name
This is the process name used to associate different runs of the
restart program. It must be provided if restart is desired. Use an
eight- character unique name to identify the run. If you re-use this
name, be sure that the previous run (with the same restart name) ran
successfully, or else the SQL will be started at the point of failure
from the previous run. If you are not sure of the results of the previous
run, either change the restart name or set Restart field to S,
so it is treated as a new run from the beginning of the SQL.
Restart SQL number
This is the SQL statement number to start processing from if you
set Restart=M. It is otherwise ignored.
Commit Frequency
Enter a value from 1 to 99999. This controls how frequently commits
are performed. It is used in conjunction with the Commit Type parameter.
Commit Type
Enter one of the values described following:
T
Transaction. This refers to the COMMIT statements in the SQL file.
If the Commit Frequency is 1 and the Commit Type is Transaction,
a commit is performed for every commit statement found in the SQL
file.
If the Commit Frequency is 10 and the Commit Type is Transaction,
a commit is performed for every 10 commit statements found in the
SQL file.
S
Statement. This refers to the executed Fast Apply SQL statements, not the actual SQL statements
in the SQL file.
If the Commit Frequency is 1 and the Commit Type is Statement, a commit is performed after each
executed Fast Apply statement which might include multiple, combined SQL statements from the SQL
file.
If the Commit Frequency is 10 and the Commit Type is Statement, a commit is performed after
every 10 executed Fast Apply statements, each of which might include multiple, combined SQL
statements from the SQL file.
With this option, the actual commit statements in the SQL file are ignored.
When there are more than two inserts per commit in one statement, Fast Apply
switches on multiple-row insert with the attributes NOT ATOMIC CONTINUE ON
SQLEXCEPTION by default. To switch off the multi-row insert, add ,MRIOFF
to the PARM=SSID parameter in the JCL EXEC statement.
Note: Adding the MRIOFF
parameter helps prevent issues that might be caused by multi-row insert, but requires more CPU
usage.
Loglevel
Enter a value from 1 to 9. This controls the level of messages
written to the ALAPRINT DD data set. Leave as the default (1) unless
directed otherwise for problem analysis.
Bypass Multi-Row Inserts
The multi-row Inserts are used by Fast Apply to improve performance. This option prevents
the use of multi-row inserts when Fast Apply runs. Enter 'Y' to bypass Multi-Row Inserts
processing.
Table filters
Table filters allow for tables found in the SQL to be excluded
and ignored or for execution of SQL only for specific tables. Enter
a Y if you want to use table filters.
Restriction: If
you are converting a table name from its value in the SQL file to
something different and specify the table on the filter panel, you
must specify the target name.
On xxxxx fields
For all of the ON xxxxx
fields listed here, these values and rules apply:
S
STOP after an event. It will rollback to the previous commit point,
and stop all processing.
W
Issue a WARNING after an event, but continue processing.
I
IGNORE an event, and continue processing.
R
ROLLBACK after an event. It will rollback to a previous commit
point, but then continue processing with the next SQL statement.
The precedence rules for the ON xxxxx fields
are as follows:
ON SQLCODEs setting
ON SQLERROR setting
ON Multiple rows setting
ON SQLWARN setting
ON SQLERROR
Enter one of the described values. The event in this case is any
SQL error return code.
ON SQLWARN
Enter one of the described values. The event in this case is any
SQL warning return code.
ON Multiple rows
Enter one of the described values. The event in this case is any
SQL statement which causes modifications to more than one row.
ON SQLCODEs
Use this line to enter up to 10 different SQL codes for which
you want to control the specific action to take. For example, if you
want SQL code +100 to be ignored, you can specify that here. The format
of this input is shown here, and must be copied exactly. If you need
more than 10 SQL codes to handle specifically, edit the JCL in DD
ALACFG ensuring that you follow that format.
The format for each entry is: <sign><code>,<action>;
The <sign> can be + or -. It must be present.
The <code> can be any 3 digit SQL condition
code (warning or error).
The <action> is as described (S/W/I/R), and
the event is the listed SQL condition code.
Important: These
codes are not intended to have any meaning or usefulness for your
specifications; they are examples only.
Execute dynamic SQL
in batch: Fast Apply table filters
When you specify Y in the
Table filters option on the "Execute dynamic SQL in batch: Fast Apply"
panel, you are identifying that you want either the SQL to be excluded
and ignored for specific tables or for the SQL to be run only for
specific tables.
When you type Y in the Table
filters field and press Enter, the "Fast Apply
Table Filters" panel is displayed: Figure 3. Execute
dynamic SQL in batch: Fast Apply Table Filters panel (top half of
panel)
V3.6.0 ---------------- Fast Apply Table Filters ----------------- SC01/SS1A
COMMAND ===>
Hit 'Enter' to save current filters; 'PF3' to bypass use of these filters
*Exclude tables ..... N (Y/N)
*Table Owner *Table Name
------------------------------------ ----------------------------------------
____________________________________ ________________________________________
Fast Apply Table Filters fields
Exclude Tables
Identify whether you want to exclude the tables that you are specifying
on the panel. If you specify Y to exclude
tables, any table specified is ignored when processing the SQL file.
All other tables are included and processed. If you specify N to
not exclude tables, only the specified table is processed. All other
tables are excluded and ignored.