The asntdiff command compares the columns in one table to their corresponding columns in another table and generates a list of differences between the two in the form of a DB2® table.
To use the compare utility, you run the asntdiff command and specify the name of a Q subscription (Q Replication) or subscription set member (SQL Replication) that contains the source and target tables that you want to compare. You can also use SQL statements in an input file to specify the tables to compare.
The following sections explain how to use the asntdiff command:
You can run the asntdiff command on Linux, UNIX, Windows, and z/OS® operating systems. The command compares tables on Linux, UNIX, Windows, z/OS, or System i® operating systems. The asntdiff command can be used with federated sources and targets if the corresponding columns in the two tables have the same data types.
The ASNTDIFF sample job in the SASNSAMP data set provides further information that is specific to the z/OS platform.
For Q Replication, the target must be a table and not a stored procedure. For SQL Replication, the target must be a user table, point-in-time table, replica table, or user-copy table.
When you run the command, you specify an SQL WHERE clause that uniquely identifies the Q subscription or subscription set member:
where="subname = 'my_qsub'"
where="set_name = 'my_set' and source_table='EMPLOYEE'"You might need to use more predicates in the WHERE clause to uniquely identify the subscription set member. For example, you might need to add the APPLY_QUAL, the SOURCE_OWNER, the TARGET_OWNER, or the TARGET_TABLE column from the IBMSNAP_SUBS_MEMBR table to the clause.
The best time to use the utility is when the source and target tables are stable. You might want to run the utility when the Q Capture and Q Apply programs or Capture and Apply programs are idle. For example, you could run the utility when the Q Capture program reached the end of the DB2 recovery log and all changes are applied at the target. If applications are still updating the source, the comparison might not be accurate.
If the replication programs are running, you might need to run the asntdiff command more than once to get a complete picture of evolving differences between the source and target tables.
The asntdiff command creates a difference table in the source database or subsystem to store differences that it finds.
The difference table is named schema.ASNTDIFF, where schema is the value specified in the DIFF_SCHEMA parameter. If the schema is not specified, it defaults to ASN. You can also use the DIFF parameter to specify a table name.
By default, the difference table is created in the default DB2 user table space. You can specify a different, existing table space by using the DIFF_TABLESPACE parameter.
The difference table has two or more columns. One column is named DIFF, with a blank space at the end on Linux, UNIX, and Windows. The value in the DIFF column is a character that indicates an insert, update, or delete operation followed by a numeric value that indicates which table contains a row with differences. The other columns contain the value of replication key columns. There is one row in the difference table for each unmatched row in the target table.
The difference table uses three identifiers that indicate the operation that is needed to change the target table so that it matches the source table:
A value of ? 1 indicates that there is an invalid character in one or more source columns.
A value of ? 2 indicates that there is an invalid character in one or more target columns.
Example:
The following list of values is returned by comparing an EMPLOYEE table at the source with a target copy of the same table. The key column for replication is the employee number, EMPNO:
DIFF EMPNO U 2 000010 I 2 000020 I 2 000040 D 2 000045 I 2 000050 D 2 000055
The first row in the example shows that a row with the key value 000010 exists at both the source and target tables, but at least one non-key column at the target has a different value. The next two rows show that rows with the key values 000020 and 000040 exist only at the source. The fourth row shows that a row with the key value 000045 exists only at the target.
The values ? 1 and ? 2 are not shown in the example.
The asntdiff utility supports multiple-byte character sets when the database is defined with SYSTEM or IDENTITY. However, the columns that are used as keys for replication at the source and target tables must use single-byte characters for the utility to compare the tables.
In a Linux, UNIX, or Windows database that uses Unicode, the characters in key data cannot be greater than the base U.S. English ASCII subset (first 256 ASCII characters) or the asntdiff utility cannot compare the tables.
You need to consider the data types of the tables that you are comparing when using asntdiff.
To compare columns with GRAPHIC data types, you must remove the blank padding in the data before you compare the source and target tables by using the DB2 scalar function rtrim(<column>. This function eliminates the code page differences for single-byte or double-byte spaces and ensures that the utility compares the GRAPHIC data in a consistent manner.
In some cases, differences between source and target tables are intentional, for example, if you use a search condition in Q Replication to filter which rows are replicated. The utility will not show differences between source and target tables that are a result of predicates or suppressed deletes.
The asntdiff -f (input file) option does not support SUPPRESS_DELETES because it bases the table comparison on a SQL SELECT statement rather than the Q subscription definition.
The asntdiff -f command option enables you to do differencing by using SQL SELECT statements that are read from an input file. This option provides greater flexibility to do differencing between two generic tables. The asntdiff -f option does not use replication definitions to determine which tables and rows to compare as the standard asntdiff command does.
The asntdiff -f option works for all tables on Linux, UNIX, Windows, and z/OS. For details on this option, see asntdiff –f (input file) command option.
In addition to the SELECT statements, the input file contains the source and target database information, the difference table information, and optional parameters that specify methods for processing the differences. You can use a password file that is created by the asnpwd command to specify a user ID and password for connecting to the source and target databases.
SOURCE_SELECT="select ID, XMLSERIALIZE(XMLColumn AS CLOB) AS XMLColumn from Table1 order by 1"
You can use the asntdiff RANGECOL parameter to compare only some of the rows in the two tables. This parameter specifies a range of rows from the source table that are bounded by two timestamps. You provide the name of a DATE, TIME, or TIMESTAMP column in the source table, and then use one of three different clauses for specifying the range. When you compare tables that are involved in peer-to-peer replication, you can use the IBM-generated IBMQREPVERTIME column for the source column in the range clause.
The RANGECOL parameter is not valid for the asntdiff -f (input file) option. You can use a SQL WHERE clause in the input file to achieve similar results.