Defining an output SQL file
Before you begin
Ensure that you understand the concepts presented in the following sections:
To define an SQL file as output
From the Actions category on the Main Menu, select Generate REDO SQL and press Enter.For REDO SQL or UNDO SQL, BMC AMI Log Master displays a panel to confirm whether you want a Backout Integrity report in addition to the generated SQL. Choose whether to include the report, and then press Enter.
BMC AMI Log Master displays the task dialog panel for the type of SQL that you selected.
- Specify a filter as instructed in Defining-a-filter-in-a-log-scan-step.To generate REDO SQL, ensure that your filter refers to at least one specific Db2 object (such as a table name or a column name). This action ensures that you define (either directly or indirectly) a set of table spaces for which BMC AMI Log Master generates the REDO SQL statements.
- Specify a time frame as instructed in Defining-a-time-frame.
- (REDO SQL file only) To generate a REDO SQL file, you must define a REDO recovery point:
- On the Generate REDO SQL panel, select Specify Run Time Options and press Enter.BMC AMI Log Master displays the Run Time Options panel.
- In the Specify REDO Recovery Point field, type E and press Enter.BMC AMI Log Master displays the REDO Recovery Point panel.
- Specify the point of recovery as a specific RBA/LRSN or a log mark.When you select the Last Common Quiesce option, BMC AMI Log Master searches the SYSIBM.SYSCOPY table until it finds a quiesce with the same RBA or LRSN for all table spaces requested. BMC AMI Log Master designates this RBA or LRSN as the proposed recovery point.
- Press F3 twice to return to the Generate REDO SQL panel.
Select Define Report and File Outputs and press Enter.BMC AMI Log Master displays the Report and File Outputs panel, as shown in the following figure. The figure shows the REDO SQL defaults, which are a Backout Integrity report and a REDO SQL file.
OUTPUTS Report and File Outputs
Command ===> Scroll ===> HALF
SSID : DB2A
Work ID . . : USER2.$$WORKID0006
Description : USER2 2022-01-18 21.42.36 REDO
Enter an action code. Then press Enter.
I=Insert D=Delete E=Edit
ACT TYPE DESCRIPTION
_ Report BACKOUT INTEGRITY
Sysout: Class(*)
DDNAME: BIREPORT
_ SQL File Terse
Redo File: &SYSUID..D&DATE..T&TIME..REDO.SQL
Include Trigger
Include ROLLBACK No
******************************** End of List *******************************- In the ACT column beside the SQL File entry, type E.BMC AMI Log Master displays the SQL Output panel.
In the Redo SQL field, type E and press Enter.BMC AMI Log Master displays the REDO SQL Output panel, as shown in the following figure:
.----------------------------------------------------------------------------.
| REDO SQL Output |
| SQL Output Destination . . . . . . : |
| &SYSUID..D&DATE..T&TIME..REDO.SQL |
| SQL Output Template Destination . . : |
| &SYSUID..D&DATE..T&TIME..REDO.TEMPLATE |
| |
| Options . . . . . . . . . _ 1. Edit SQL Output Dataset |
| 2. Edit SQL Template Dataset |
| 3. Remove SQL Template Dataset |
| F1=Help F3=Exit F12=Cancel |
----------------------------------------------------------------------------.Select Edit SQL Output Dataset and press Enter.BMC AMI Log Master displays the Output Dataset Information panel, as shown in the following figure:
| ======================== Output Dataset Information ======================== |
| Redo SQL Output File DDNAME |
| Dataset Name (PDS Member Allowed) . . . (Optional) |
| ABC.ACCT.REDO.SQL |
| Disposition. . . . . . N (N=New, M=Mod, O=Old, S=Shr) |
| |
| RECFM . . . . . . . . _____ (FB=Fixed Block,VB=Variable Block) |
| Unit . . . . . . . . . SYSDA |
| Allocation Type. . . . C (C=Cyls, T=Trks) |
| Space. . . . . . . . . 15 , 5 (Primary, Secondary) |
| Release. . . . . . . . Y (Y=Yes, N=No) |
| Max Volumes. . . . . . ___ |
| Expiration Date. . . . ________ (yyyy/ddd or 99365, 99366, 98000, 99000) |
| Retention Period . . . 30__ |
| Model DSCB . . . . . . ____________________________________________ |
| Data Compaction . . . _ (Y=Yes, N=No For TAPE Only) |
| Data Class . . . . . . ________ (For SMS Only) |
| Management Class . . . ________ (For SMS Only) |
| Storage Class. . . . . ________ (For SMS Only) |
| Volser(s) . . . . . . ______ , ______ , ______ ,______ , ______ , ______ |
'------------------------------------------------------------------------------'- Specify the data set name and attributes as required, and then press F3 to return to the Output Dataset Information panel.
(optional) Select Edit SQL Template Dataset and press Enter.We recommend that you generate the optional SQL template data set if you use the High-speed Apply Engine to execute your generated SQL statements.
BMC AMI Log Master displays the Output Dataset Information panel, including a default name for the SQL template data set.
- Specify the SQL template data set and attributes as required, and then press F3 twice to return to the SQL Output panel.
- (optional) On the SQL Output panel, change the default values for SQL output options.
To adjust other options, type E in the SQL Options field and press Enter.BMC AMI Log Master displays the SQL Options panel, as shown in the following figure:
SQL Options
Include Comments . . . . . . . . . . N (Y=Yes, N=No)
Commit Frequency . . . . . . . . . . 1 (Number of Units, 0=All)
Units . . . . . . . . . . . . T (T=Transactions, S=Statement
Update All Columns . . . . . . . . . N (Y=Yes, N=No)
Decimal Point. . . . . . . . . . . . _ (P=Period, C=Comma)
Generate SQL Where Clause Using. . . A (P=Pri. Key, K=Key & Chgd Cols)
A=All Cols, U=Use Overrides)
Exclude INSERT Column Names. . . . . N (Y=Yes, N=No)
Set SQLID. . . . . . . . . , . ________ (ID for SET SQLID statement)- Specify options for your output SQL file as required.For more information about these options, press F1 to access the online Help.
- Press F3 until you return to the Report and File Outputs panel.
- Review the selections that you made for the SQL File, and then press F3 to return to the Generate REDO SQL panel.