Space announcements

   

This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

ORDER BY definition

This topic describes the ORDER BY definition syntax of the LOGSCAN statement.

By default, Log Master sorts reports based on certain criteria (for example, entries in the summary report are often sorted in order based on table name). To change the default sort criteria, use the ORDER BY definition. You can choose a maximum of three sort criteria. If you need to specify more than three sort criteria, use customized templates instead of standard reports.

The ORDER BY definition is not valid for a Backout Integrity report or a standard default Quiet Point report. Different types of reports have different sets of ordering defaults. Several reports have restrictions on the columns or fields that you can use for ordering. For more information about a particular report’s defaults or restrictions, see Reference of default Log Master reports.

You can also specify sort criteria by using a report template. If you use a report template to generate a report, you cannot specify an ORDER BY definition for the same report. Log Master provides additional sort capabilities in report templates that are not available for the standard default reports.

ORDER BY definition syntax diagram

OptionDescription

orderingField

Specifies a sort criteria used to sort Log Master reports. The available sort criteria are as follows:

ValueDescription
ACTIVITY DESC

Sorts reports based on activity, in descending order. Activity in this context represents the number of database changes (insert, update, delete, or exchange actions) that have occurred against a given Db2 object.

ACTIVITY TYPE

Sorts reports based on the type of Db2 catalog activity. This criteria is valid only for a Catalog Activity report. You can also think of this syntax as sorting the report based on a type of data definition language (DDL) statement. For example, the report can group together all log records that reflect DROP statements, or ALTER statements.

AUTH ID

Sorts reports based on authorization ID. An authorization ID is a character string used to connect to Db2; it frequently represents an individual, a work group, or a function.

COLUMN NAME

Sorts reports based on the column name. The following conditions apply:

  • The column name must be the last option in the order-by list.

  • Either the column name or column number must be included in the order-by list, but not both.

  • Column names are not qualified with the table creator and table name. Consequently, if you need to report on multiple tables, the order-by list should include Table Name, even though the table name is not required.

    Note

    This criteria is valid only for a Column Update Frequency report.

COLUMN NUMBER

Sorts reports based on the column number. The following conditions apply:

  • The column number must be the last option in the order-by list.

  • Either the column name or column number must be included in the order-by list, but not both.

  • Column numbers are not qualified with the table creator and table name. Consequently, if you need to report on multiple tables, the order-by list should include Table Name, even though the table name is not required.

    Note

    This criteria is valid only for a Column Update Frequency report.

COMMAND

Sorts reports based on a Db2 command name or a synonym that Db2 supports, such as START DATABASE (-STA DB). This criteria is valid only for a Commands report.

CONNECTION ID

Sorts reports based on connection ID. A connection ID is a string identifier associated with a specific address space connection.

CONNECTION TYPE

Sorts reports based on connection type. A connection type is a string constant representing the manner in which a unit of recovery thread is attached to Db2. For a list of possible connection type values, see CONNECTION TYPE.

CORRELATION ID

Sorts reports based on correlation ID. A correlation ID is a unique string identifier for a specific Db2 thread.

CREATE RBA

Sorts reports based on the RBA/LRSN value when a Db2 object was created. Use this value to sort reports that Log Master produces when it runs in overtime mode. For example, in overtime mode two different instances of a table can have the same name (and possibly the same DBID or OBID). By using this value, you can separate the data from each instance of the table.

You must specify TABLE NAME or OBID as a sort criteria before you specify CREATE RBA.

DATABASE NAME

Sorts reports based on database names.

DBID

Sorts reports based on database ID (DBID). A DBID is an internal identifier assigned to a database by Db2.

DSNUM

Sorts reports based on data set number. The data set number is the ordinal number of a given data set within a table space (usually a partitioned table space).

MEMBER ID

Sorts reports based on the member ID of a given member of a data sharing group. Use this keyword only with reports generated in a data sharing environment.

MEMBER NAME

Sorts reports based on the name of a given member of a data sharing group. Use this keyword only with reports generated in a data sharing environment.

OBID

Sorts reports based on object ID (OBID). An OBID is the internal identifier that Db2 assigns to an object.

OBJECT TYPE

Sorts reports based on the type of Db2 object. This criteria is valid only for a Catalog Activity report. For example, the report can group together all log records that affect tables or all log records that affect indexes. Besides the commonly used objects, such as tables and columns, the object type includes other objects defined in the Db2 catalog, such as plans, packages, collections, and buffer pools.

PLAN NAME

Sorts reports based on plan name. A plan name is a character string representing an application plan, the control structure used by Db2 to process SQL statements.

PRIMARY KEY

Sorts reports based on the primary key of a table. The primary key of a table is a column or set of columns within a table, defined so that there is one unique primary key value for each row in the table. If you specify PRIMARY KEY, but a table has no primary key defined, Log Master selects a key to use. For more information, see QUALIFY.

PSID

Sorts reports based on page set ID (PSID). A PSID is an internal page set identifier assigned to a given table space by Db2.

SSID

Sorts reports based on the subsystem identifier (SSID) of a given Db2 subsystem. Use this keyword only with reports generated in a data sharing environment.

STATUS

Sorts reports based on status change type.

For a description of the statuses that Log Master detects and reports on, see STATUS.

Note

This criteria is valid only for Status Changes reports.

For detailed descriptions of a status change, see the relevant IBM Db2 product information.

TABLE NAME

Sorts reports based on the names of tables selected in your WHERE clause or filter.

TABLESPACE NAME

Sorts reports based on the names of table spaces selected in your WHERE clause or filter.

UNIT OF RECOVERY

Sorts reports based on unit of recovery identifier (URID). A URID is a hexadecimal value assigned by Db2 to a recoverable sequence of operations within Db2.

ValueDescription
WITH ALL FIELDS

Use this keyword to include all of the URID-related fields in a generated Summary report. Log Master obtains these fields from the 'Begin UR' log record of each unit of recovery and they include values such as authorization ID or correlation ID. By default, Log Master includes only a subset of the URID-related fields.

This keyword is valid only for Summary reports and only when the format of the report is CSV (comma separated value) or SDF (standard definition format).

UTILITY

Sorts reports based on Db2 utility name.

For a description of the utilities that Log Master detects and reports on, see UTILITY.

Note

This criteria is valid only for Status Changes reports.

For detailed descriptions of a Db2 utility, see the relevant IBM Db2 product information.



Was this page helpful? Yes No Submitting... Thank you

Comments