Log Data Filter Options

The log data filter options for the DML activity function let you specify filters to include or exclude specific data from the analysis. These filters provide comprehensive control over the contents of your report, SQL statements, or load file. You can create filters for various object types (databases, tables, and so on). You can then link those filters together using a join operator. You can further refine your filters by creating data or selection queries that contain SELECT SQL statements. In most instances, you can select the objects from a list or can enter the object names manually. In some instance, you can only enter the object names manually.
caladb219
The log data filter options for the DML activity function let you specify filters to include or exclude specific data from the analysis. These filters provide comprehensive control over the contents of your report, SQL statements, or load file. You can create filters for various object types (databases, tables, and so on). You can then link those filters together using a join operator. You can further refine your filters by creating data or selection queries that contain SELECT SQL statements. In most instances, you can select the objects from a list or can enter the object names manually. In some instance, you can only enter the object names manually.
Table filters take precedence over database filters. If you create a filter to include a specific table, the table is included even if the database containing that table is excluded.
If you specified more than one filter on the DML Activity Report Options panel, a Filter Specifications panel appears for each filter.
Contents:
Select Objects From a List
You can create a filter to specify which objects to include or exclude from analysis. You can specify these objects by selecting them from a list, or by entering the object names manually. The following instructions describe how to select objects from a list.
Follow these steps:
  1. Type
    I
    (Include) or
    X
    (Exclude) in the appropriate filter field on the DML Activity Report Options panel. A separate filter field is provided for each object type (table, plan, authorization ID, connection ID, and so on).
    Press Enter.
    The Filter Specifications panel for the selected object type appears.
  2. Use the "Enter selection criteria..." fields to enter your selection criteria. Mask characters are allowed.
    The following mask characters are allowed for object selection:
    • Asterisk (*)–Includes all items.
    • Per cent sign (%)--Represents any string of zero or more characters in the item name.
    • Underscore (_)--Represents any single character.
    Press Enter.
    The panel displays the objects that match your criteria.
  3. Type
    S
    (Select) in the SEL field next to each item to include or exclude from the filter. Press Enter.
    A message informs you that your selections have been queued.
  4. (Optional) Type
    S
    (Shrink) on the command line and press Enter.
    The Selected Object Queue panel appears, showing the objects that are currently selected.
    You can alter or remove objects from the list shown in this panel. Type
    S
    in the SEL (Select) field to select an object. Delete the S to remove an object.
  5. Press PF3 (End).
    The Filter Specifications panel reappears.
  6. Press PF3 (End).
    The Report Options panel reappears. You can now select the rest of your options.
Enter Table Names Manually
You can create a filter to specify which tables to include or exclude from analysis. The following instructions describe how to enter table names manually.
Follow these steps:
  1. Type
    I
    (Include) or
    X
    (Exclude) in the appropriate filter field on the DML Activity Report Options panel. A separate filter field is provided for each object type (table, plan, authorization ID, connection ID, and so on).
    Press Enter.
    The Filter Specifications panel for the selected object type appears.
  2. Use the "Enter the desired names..." fields to enter the object names.
    • Mask characters are allowed.
      The following mask characters are allowed for object selection:
      • Asterisk (*)–Includes all items.
      • Per cent sign (%)--Represents any string of zero or more characters in the item name.
      • Underscore (_)--Represents any single character.
    • The Cmd (Command) field lets you add empty lines to the filter list as needed. It also lets you use the following commands:
      • B (Browse)
        Accesses the
        CA RC/Update™ for DB2 for z/OS
        table browse facility so that you can browse the table contents. A
        CA RC/Update™ for DB2 for z/OS
        license is required.
      • FB (Fast Browse)
        Accesses the
        CA RC/Update™ for DB2 for z/OS
        table browse facility so that you can browse the table contents. This option is slightly faster than the Browse option because it bypasses the Browse Options panel. A
        CA RC/Update™ for DB2 for z/OS
        license is required.
    • You can use the following fields to define a data query:
      • Where
        Lets you define a data query to refine your filter further.
        Note:
        Queries can be defined only for INCLUDE filter types. Additionally, specify a table name and creator. If you enter a masking character, it is interpreted as a literal part of the table or creator name.
      • Appl
        Specifies whether to apply the query to the
        after-image
        or
        before-image
        of the data, or to both.
    Press Enter.
    Your selections on this panel dictate which panel appears next:
    • If the Data Query Edit panel appears, go to Step 3.
    • If the Query List panel appears, go to Step 4.
    • If a
      CA RC/Update™ for DB2 for z/OS
      panel appears, go to Step 5.
    • If the Cmd field is blank, a new line is inserted. You can enter another table name, creator, or both. Go to Step 6.
  3. Create (or edit) a data query.
    Press PF3 (End) to return to the Table Filter Specifications panel.
    Your selections on the Table Filter Specifications panel dictate which panel appears next.
    • If the Query List panel appears, go to Step 3.
    • If a
      CA RC/Update™ for DB2 for z/OS
      panel appears, go to Step 4.
    • If the Cmd field is blank, a new line is inserted. You can enter another table name, creator, or both. Go to Step 5.
  4. Select an existing query by typing
    S
    next to it.
    You can also use the Query List panel to create and delete queries. For more information, see Query Lists in the
    CA Database Management Solutions for DB2 for z/OS
    documentation.
    Press PF3 (End).
    Your selections on the Table Filter Specifications panel dictate which panel appears next.
    • If a
      CA RC/Update™ for DB2 for z/OS
      panel appears, go to Step 4.
    • If the Cmd field is blank, a new line is inserted. You can enter another table name, creator, or both. Go to Step 5.
  5. Browse the contents and then press PF3 (End) repeatedly until the Table Filter Specifications panel reappears.
    For more information about the browse functions, see the
    CA RC/Update™ for DB2 for z/OS
    documentation.
    .
  6. Repeat the preceding steps as necessary to define additional filters.
  7. Press PF3 (End).
    The Report Options panel reappears. You can now select the rest of your options. Your table filters remain in effect until you return to the Main Menu.
Filter Data by Log Statement
You can create a filter to specify which types of log statements (inserts, deletes, updates, or utilities) to include or exclude from analysis.
Follow these steps:
  1. Type
    I
    (Include) or
    X
    (Exclude) in the Statement Type field on the DML Activity Report Options panel and press Enter.
    The Statement Filter Specifications panel appears.
  2. Type
    S
    (Select) next to the statement types to include in the filter. Remove the S from the statement types to exclude from the filter. Press Enter.
  3. Press PF3 (End).
    The Report Options panel reappears. You can now select the rest of your options.
Link Filters Using the Join Operator
When you create multiple filters to include or exclude specific data from analysis, you can use a join operator (AND or OR) to link the filters together. If you specify AND, data is included or excluded only if
all
filter specifications are met. If you specify OR, data is included or excluded for each filter specification that is met. CA Log Analyzer™ for DB2 for z/OS joins the Include filters first and then the Exclude filters.
Note:
If a log entry satisfies both the Include and Exclude filters, the Exclude filter takes precedence.
To link filters using the join operator, type
AND
or
OR
in the Join Operator field.
CA Log Analyzer™ for DB2 for z/OS links the filters at run time.
Refine a Filter by Using a Query
When you create a data filter, you can further refine the filter by creating a data query or a selection query.
CA Log Analyzer™ for DB2 for z/OS
lets you define a query by using the Extended Query Facility (EQF). You can use all standard SELECT clauses. When the query executes,
CA Log Analyzer™ for DB2 for z/OS
evaluates the before- and after-images of a row to determine whether they meet the query criteria. If either image meets the criteria, that log record is selected. If
CA Log Analyzer™ for DB2 for z/OS
cannot determine whether a row meets the criteria, it discards that record. Records can be discarded when the tables were created with DATA CAPTURE NONE and the Image Copy Level of Detail has not been chosen. Records can also be discarded when you are using the Image Copy Level of Detail and suitable image copies do not exist.
You can use the Miscellaneous Options portion of the DML Activity Report Options panel to request a report of discarded log records.
A data query applies only to table filters. This query lets you define a SELECT SQL statement that is applied against the log data to determine which rows to include in the analysis.
  • A data query is tied to a particular table as opposed to a function (like selection queries). A data query is not based on any pre-existing internal SELECT statements. You can specify the columns, column order, WHERE conditions, and row order.
    A data query can also be used as a model to create other queries by saving it under another name.
  • Selection queries let you add conditions to an existing internal query. After you have added the conditions to the query, you can name and save the query for later reuse. The saved query is considered a selection query because it controls the order and selection of rows. A selection query does not let you control the columns selected.
Follow these steps:
  1. Create a filter using the DML Activity Report Options panel.
    The Filter Specifications panel appears.
  2. Select the objects to include or exclude from the analysis.
    The selected objects appear on the Filter Specifications panel.
  3. Type
    Y
    in the Where field.
    The Where field under "Enter selection criteria..." lets you create a selection query.
    The Where field under "Enter the desired table names..." lets you create a data query. Data queries are valid only for table filters.
    Press Enter.
    The SQL Selection Panel or the Data Query Edit panel appears.
  4. Create your query. Press PF1 to view the online help for these panels.
SQL Notes for Data Queries
CA Log Analyzer™ for DB2 for z/OS
fully supports the following predicates:
  • BASIC
  • BETWEEN
  • IN
  • LIKE
  • NULL
The AND, OR, and NOT logical operators are also fully supported. You can control the order of precedence by using parentheses. If you omit the parentheses, the following order of precedence is used:
  • NOT is applied before AND
  • AND is applied before OR
CA Log Analyzer™ for DB2 for z/OS
does not
support the following SQL constructs:
  • Subselects
  • Column functions
  • Joins
  • Labeled durations
  • Unions
  • Host variables
  • Special registers
  • GROUP BY clause
  • Scalar functions
  • HAVING clause
  • Concatenation or arithmetic operators
  • ORDER BY clause
How to Provide Your Own SELECT SQL Statements for Data Queries
The SELECT SQL statements can also be passed to the product in a file.
To provide your own SELECT SQL statements for data queries, follow these guidelines:
  • The DDNAME must be LADSQLIN.
  • The SQL data set must have an LRECL of 80.
  • The SQL contained in the file must be in a format suitable for use with SPUFI.
  • The SELECT statements must appear in columns 1 - 72 and each statement must be terminated with a semicolon (;).
  • The format of the UPDATE function and predicate is as follows:
    UPDATE(column-name)
The following illustration is a sample SQL statement, as viewed from the Data Query panel, illustrating the use of the UPDATE function and predicate:
PTSQLDSP SQL Statement Display
Command ==>
Name ==> PXXXXXX Share => N Default => N
Description => SAMPLE QUERY
Current SQL Statement:
SELECT EMPNO, LASTNAME, PHONENO, HIREDATE, SEX, BIRTHDATE, SALARY, BONUS,
COMM, UPDATE(WORKDEPT)
FROM USER02.EMP
WHERE UPDATE(JOB)