asntdiff –f (input file) command option

With the asntdiff -f command option, you use an input file to specify information about any two tables that you want to compare, whether or not they are being replicated.

The input file contains SQL SELECT statements for the source and target tables that specify the rows that you want to compare. The standard asntdiff command compares tables that are involved in replication by using subscription information from the replication control tables.

The asntdiff -f option can compare any tables on z/OS®, Linux®, UNIX, or Windows. You can run asntdiff -f from a Linux, UNIX, or Windows command prompt, from z/OS as a batch job that uses JCL, or from z/OS under the UNIX System Services (USS) environment.

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.

Note: The asntrep command for repairing table differences does not support the input file option.

The format of the input file contents is as follows:


* Optional comment line
# Optional comment line
SOURCE_SERVER=server_name
SOURCE_SELECT="SQL_SELECT_STATEMENT"
TARGET_SERVER=server_name
TARGET_SELECT="SQL_SELECT_STATEMENT"
PARAMETER=value
...

Follow these guidelines:

Each parameter must follow the parameter=value format.
Multiple parameter-value pairs can be specified on a single line, separated by a blank. The parameter-value pairs also can be specified on a new line.
To preserve blanks, surround parameter values with double quotation marks ("). Double quotation marks are also required for the source and target SELECT statements.
If you want to preserve mixed case or blanks in the names of single Db2® objects (column or table names, DIFF_SCHEMA, DIFF_TABLESPACE) mask them with \" \", for example \"MY NAME\" or \"ColumnName\" or \"name\".
Comments must be prefixed with an asterisk (*) or pound sign (#). This line is ignored. Comments must be on their own line and cannot be added to a line that contains parameters.
Surround the DIFF_PATH and PWDFILE parameters with double quotation marks ("). A final path delimiter for DIFF_PATH is not required.

Syntax

Parameters

Table 1 defines the mandatory parameters to include in the input file for the asntdiff -f command.

For descriptions of optional parameters that you can include in the input file (and which are shared by the standard asntdiff command) see asntdiff: Comparing data in source and target tables (z/OS) or asntdiff: Comparing data in source and target tables (Linux, UNIX, Windows).

Table 1. asntdiff -f invocation parameter definitions for Linux, UNIX, Windows, and z/OS
Parameter	Definition
`input_filename`	Specifies the name of the file that contains the source and target database information and SELECT statements. Specify a directory path if the file is located somewhere other than the directory from which you run the asntdiff -f command. z/OS: You specify the input file in the JCL as follows: `//ASNTDIF1 EXEC PGM=ASNTDIFF,PARM='/-F' ... //SYSIN DD DISP=SHR,DSN=DPROPR.USER.LIXIAO.JCLLIB(INPUTFIL)` Where DPROPR.USER.LIXIAO.JCLLIB is the path of the input file INPUTFIL,
SOURCE_SERVER= `source_server_name`	Specifies the alias of the database where the source table exists.
TARGET_SERVER= `target_server_name`	Specifies the alias of the database where the target table exists.
SOURCE_SELECT= `source_select_statement` TARGET_SELECT= `target_select_statement`	Any valid SQL SELECT statement. The result sets from the SQL statement at each table must contain columns with matching data types and lengths. The asntdiff command describes the queries and compares the data from the two result sets. The command does not explicitly check the system catalog for type and length information. The SELECT can be an open select as in (), or a SELECT statement that contains column names, SQL expressions, and WHERE clauses that are permitted. Using an asterisk () retrieves all columns from the specified table. Db2 selects the columns in the order that the columns are declared in that table. Hidden columns are not included in the result of the SELECT * statement. An ORDER BY clause is mandatory. The clause must contain the numeric values of the positions of the columns in the SQL statement. The numeric value `n` identifies the `nth` column of the result table. Ensure that the column or columns in the ORDER BY clause reference a unique key or unique composite key. Otherwise the results are incorrect. An index on the columns in the ORDER BY clause might improve performance by eliminating the need for a sort. The entire statement must be enclosed in double quotes to mark the beginning and the end.

The following examples show the mandatory parameters, SQL statements, and optional parameters that you put in the input file.

Example 1 (z/OS)

This example shows the use of an open SELECT statement on Db2 for z/OS. Note the use of the \" to preserve mixed case in the table owner, and the use of optional parameters in the input file. Also note the use of the DB2_SUBSYSTEM parameter.

SOURCE_SERVER=STPLEX4A_DSN7
SOURCE_SELECT=”select * from CXAIMS.ALDEC order by 1”
TARGET_SERVER=STPLEX4A_DSN7
TARGET_SELECT=”select * from \"Cxaims\".TARG_ALDEC order by 1”
DIFF_DROP=Y
DB2_SUBSYSTEM=DSN7
MAXDIFF=10000
DEBUG=Y

Example 2 (z/OS)

This example demonstrates the use of SUBSTR and CAST functions in the SELECT statements.

SOURCE_SERVER=D7DP
SOURCE_SELECT=“select HIST_CHAR12,HIST_DATE,HIST_CHAR6,HIST_INT1,HIST_INT2,
HIST_INT3,SUBSTR(CHAR1,1,5) AS CHAR1,SUBSTR(CHAR2,1,10) AS CHAR2,HIST_INT3,
HIST_DEC1,HIST_DEC2,HIST_DEC3,CAST(INT1 AS SMALLINT) AS INT1
FROM BISVT.THIST17 ORDER BY 4”
TARGET_SERVER=STPLEX4A_DSN7
TARGET_SELECT=“select HIST_CHAR12,HIST_DATE,HIST_CHAR6,HIST_INT1,HIST_INT2,
HIST_INT3,CHAR1,CHAR2,HIST_INT3,HIST_DEC1,HIST_DEC2,HIST_DEC3,SML1
FROM BISVT.THIST17 ORDER BY 4”
DB2_SUBSYSTEM=DSN7
DIFF_DROP=Y
DEBUG=Y
MAXDIFF=10000

Example 3 (Windows)

This example compares the EMPLOYEE tables on SOURCEDB and TARGETDB and includes several optional parameters.


SOURCE_SERVER=SOURCEDB
SOURCE_SELECT=“select FIRSTNME, LASTNAME, substr(WORKDEPT,1,1)
as WORKDEPT, EMPNO from EMPLOYEE order by 4"
TARGET_SERVER=TARGETDB
TARGET_SELECT=“select FIRSTNME, LASTNAME, substr(WORKDEPT,1,1)
as WORKDEPT, EMPNO from EMPLOYEE order by 4"
DIFF_DROP=Y
DIFF =\"diffTable\"
DEBUG=Y
MAXDIFF=10000
PWDFILE=”asnpwd.aut”
DIFF_PATH=”C:\utils\”

Example 4 (Linux, UNIX)

This example compares the EMPLOYEE tables in a Linux or UNIX environment and uses a casting function.


SOURCE_SERVER=SOURCEDB
SOURCE_SELECT=“select EMPNO, FIRSTNME, LASTNAME, cast(SALARY as INT)
as SALARY from EMPLOYEE order by 1"
TARGET_SERVER=TARGETDB
TARGET_SELECT=“select EMPNO, FIRSTNME, LASTNAME, cast(SALARY as INT)
as SALARY from EMPLOYEE order by 1"
DIFF_DROP=Y
DIFF =\"diffTable\"
DEBUG=Y
MAXDIFF=10000
PWDFILE=”asnpwd.aut”       
DIFF_PATH=”home/laxmi/utils”