Stored Procedure Compare
The stored procedure comparison task (spcompare) performs the comparison of parameters and result sets returned by two stored procedure of similar structure.
The comparison is executed for all output and input-output parameters as well as all result sets.
The result set comparison behavior can be altered setting the comparison options for all or specific resultsets returned by a stored procedure.
The stored procedure compare task is defined by the XML element spcompare whose attributes configure the execution parameters.
The following attributes the task:
| Attribute | Type | Purpose | Default |
|---|---|---|---|
| mode | string | Comparison mode. Refer to comparison mode for detailed description. | sequential |
| left | string | Name of the data source on the left side of the comparison | |
| right | string | Name of the data source on the right side of the comparison | |
| ignoretrailingnulls | bool | Ignores trailing NULL when comparing CHAR and VARCHAR columns |
Defined in Data comparison |
| ignoretrailingspaces | bool | Ignores trailing blanks when comparing CHAR and VARCHAR columns |
Defined in Data comparison |
| ignoreinbinaries | bool | Ignores trailing blanks and NULLs on FOR BIT DATA, BINARY and VARBINARY columns defined for CHAR and VARCHAR |
Defined in Data comparison |
| diffslimit | integer | Differences limit. When greater than zero, the comparison stops when the specified number of differences is detected. | Zero |
The following elements are used to configure the table comparison:
| Element | Purpose |
|---|---|
| left | Fully qualified name of the stored procedure on the left side |
| right | Fully qualified name of the stored procedure on the right side |
| parameters | Defines the input parameter values. If omitted, NULL value is passed. |
| resultsets | Defines the resultset comparison options. If no resultset element is specified, default comparison options are used. |
Important
Only for Oracle database, the parameters element is used to define ALL parameters (input, output, input-output).
Comparison Mode
The result set comparison can be run in the following modes:
- sequential: result set rows are compared in the order they are returned by the stored procedure and matched by columns as follows:
- When all columns match, rows are considered equal and next row is fetched from both side
- When all columns don’t match
- If row on left is greater than row on right, row on left is marked as missing on right and next row on right is fetch
- If row on left is less than row on right, row on right is marked as missing on left and next row on left is fetch
- parallel: result set rows are compared in the order they are returned by the stored procedure and matched by columns side by side as follows:
- All columns are compared and independently from the comparison results both rows from both sides are fetched after the comparison
Left and Right
The left and right elements are used to provide the fully qualified of the objects being compared.
The following attributes are used to configure left and right objects:
| Attribute | Type | Purpose | Default |
|---|---|---|---|
| bitdatacodepage | string | Defines the codepage to translate the binary data (i.e. CHAR FOR BIT DATA) from EBCDIC to ASCII. If not specified and no default bitdatacodepageis set for the datasource, no translation is performed (default). For the list of supported codepages, refer to Codepages. |
Example
<spcompare name="SPCMP01" left="QS" right="QS">
<left>dbo.sp01</left>
<right>dbo.sp01</right>
<parameters>
<parameter name="i10">i10</parameter>
<parameter name="i11">i11</parameter>
</parameters>
</spcompare>
Parameters
The parameters element is used to provide the value for input or input-output parameters to call the stored procedure.
Each parameter is defined by the parameter element. If a parameter is omitted, the stored procedure is invoked with the missing parameter value set to null.
Parameter
The parameter element defines the name, the value and, for Oracle only, the size of the character parameters.
The following attributes configure the parameter:
| Attribute | Type | Purpose | Default |
|---|---|---|---|
| name | string | Name of the parameter | |
| size | integer | Size, in characters, of the parameter (needed only for Oracle) |
The value provided to the stored procedure is determined by the syntax of the parameter element:
|Syntax |Meaning |Example
|Empty element |NULL value |<parameter name="A"/>
|Zero length value |Zero length string |<parameter name="A"></parameter>
|String |Not NULL Value |<parameter name="A">ACME</parameter>
If the length of the provided value exceeds the maximum size defined for the parameter, the value is automatically truncated to the maximum allowed size.
Important
DB2 and SQL Server require stored procedure character (CHAR, VARCHAR, etc.) parameters to be defined with a size attribute, therefore Ianus can inquire the RDBMS to determine automatically the size of character parameters. Oracle has no concept of parameters size for character types, making impossible for Ianus to obtain it automatically. Therefore, for Oracle, the size of all character parameters must be provided by the user. The size attribute is ignored for DB2 and SQL Server.
Example
<spcompare name="SPCMP02" left="QS" right="QS">
<left>dbo.sp01</left>
<right>dbo.sp01</right>
<parameters>
<parameter name="RS1">yes</parameter>
<parameter name="RS2">yes</parameter>
<parameter name="i10">i10</parameter>
<parameter name="i11">i11</parameter>
<parameter name="i20">i20</parameter>
<parameter name="i21">i21</parameter>
<parameter name="i22">binario al 22</parameter>
<parameter name="i30">14:00:00</parameter>
<parameter name="i31">1972-03-08</parameter>
<parameter name="i32">2015-12-31 12:00:00.000000</parameter>
<parameter name="i60">60.65</parameter>
<parameter name="i61">61</parameter>
<parameter name="i70">62.62</parameter>
<parameter name="i71">63</parameter>
<parameter name="i72">64</parameter>
<parameter name="i73">65</parameter>
<parameter name="i74">66</parameter>
</parameters>
</spcompare>
<spcompare name="SPCMP03" left="QS" right="QS">
<left>dbo.sp02a</left>
<right>dbo.sp02b</right>
<parameters>
<parameter name="IMODE">SAME</parameter>
</parameters>
</spcompare>
ResultSets
The resultsets element is used to provide the comparison options for the result sets produced by stored procedure.
Options for each result set is defined by a resultset element. If the resultset is identified by the id="*" it is treated as default for all resultsets.
Warning
Stored Procedures Comparison Task does not support Resultsets comparison with PostgreSQL. Stored Procedures comparison steps involving PostgreSQL database on either or both sides of the comparison, simply compare parameters and ignore any resultsets on both sides.
Resultset
The resultset element defines the comparison options for a specific resultset, as identified by the ID attribute.
If the ID is *, the options are treated as default for all resultset.
Sort options are defined by sort elements.
The following attributes configure the resultset:
| Attribute | Type | Purpose | Default |
|---|---|---|---|
| id | integer | Zero-based number of the resultset. If * it is treated as default for all resultsets. |
|
| ignore | bool | If set to true, the resultset is ignored in the comparison. | false |
Sort
The sort elements define the sort rules for the resultset.
The following attributes configure the sort options:
| Attribute | Type | Purpose | Default |
|---|---|---|---|
| side | string | Defines the side where the exclusion applies: left, right or both |
both |
The value of the sort element indicates the sort keys for the resultset, with the following notation:
`COLUMN_NAME (ASC|DESC) [, COLUMN_NAME (ASC|DESC)]+`
Using an asterisck (*) as value, indicates that the resulset is sorted by all columns.
Compare
The compare element is used to use a user provided column comparison class for the resultset column(s) matching all the provided criteria.
| Criteria | Description |
|---|---|
| leftname | the name of the column on the left side |
| rightname | the name of the column on the right side |
| lefttype | the SQL type on the left side |
| righttype | the SQL type on the right side |
Note
If no criteria is defined, all columns are matched.
The following attributes are used to configure the compare element:
| Attribute | Type | Purpose | Default |
|---|---|---|---|
| mode | string | Defines the custom comparison mode (plugin, layout) | plugin |
| leftname | string | Name of the column on the left side | |
| rightname | string | Name of the column on the right side | |
| lefttype | string | SQL type of the column on the left side | |
| righttype | string | SQL type of the column on the right side | |
| path | string | Indicates to load the class script from the specified file | |
| assembly | string | Indicates to load the class assembly from the specified DLL | |
| class | string | Indicates the name of the class to use | |
| plugin | string | Indicates to load the class from the plugin alias defined in the Ianus configuration file | |
| whitespaces | string | Defines the white spaces handling mode (asis, trim, ignore) | asis |
The user provided comparison plugin must implement the interface HPE.Ianus.Scripting.IColumnComparator. For a detailed description of the comparison class please refer to Column Comparator .
Example
<spcompare name="RSOPTS_KEYS" left="DB2" right="QS">
<left>ADMINISTRATOR.SPRSA</left>
<right>dbo.sprse</right>
<resultsets>
<resultset id="1">
<sort side="right">PK1 ASC, PK2 ASC</sort>
</resultset>
</resultsets>
</spcompare>
<spcompare name="RSOPTS_KEYSALL" left="DB2" right="QS">
<left>ADMINISTRATOR.SPRSA</left>
<right>dbo.sprse</right>
<resultsets>
<resultset id="*">
<sort>*</sort>
</resultset>
</resultsets>
</spcompare>
<spcompare name="RSOPTS_IGNORE" left="DB2" right="QS">
<left>ADMINISTRATOR.SPRSA</left>
<right>dbo.sprse</right>
<resultsets>
<resultset id="1" ignore="true"/>
</resultsets>
</spcompare>
<spcompare name="SPCMP1" left="QS" right="QS">
<left>dbo.usercmp1</left>
<right>dbo.usercmp2</right>
<resultsets>
<resultset id="1">
<compare lefttype="CHAR" plugin="StringCompareIC"/>
<compare lefttype="VARCHAR" plugin="StringCompareIC"/>
</resultset>
</resultsets>
</spcompare>
Transaction
The transaction element is used to execute the stored procedure(s) within a transaction. If no transaction element is provided, the stored procedures are executed without transaction.
The following attributes are used to configure left and right objects:
| Attribute | Type | Purpose | Default |
|---|---|---|---|
| side | string | Defines the side where the transaction is requested: left, right or both |
both |
| onclose | string | Defines the operation used to close the transaction: commit or rollback |
commit |
Example
<spcompare name="TRX3" left="QS" right="QS1">
<left>dbo.sp02a</left>
<right>dbo.sp02b</right>
<transaction side="left" onclose="commit"/>
<transaction side="right" onclose="rollback"/>
<parameters>
<parameter name="IMODE">SAME</parameter>
</parameters>
</spcompare>
Comparison Log
Ianus will report on both console and log file the result of the comparison of the resultset(s) first followed by the comparison of the OUT/INOUT parameters, indicating the differences, when detected.
For each difference detected, Ianus will log a message structured as follows:
- Difference indicator, a single character (
<,!or>) indicating:- When a row is on the left side only (
<) - When a row with the same PKs is on both sides but some columns are different
- When a row is on the right side only (
>)
- When a row is on the left side only (
- Primary key(s) content, encolsed into square brakets (
[and]), of the rows causing the difference - Names of the column(s) containing different data (after the
@character)
Example
[info] Sequential comparison started
[warn] < Resultset 1 / Row 1 @ PK1, C10, C11, C30, C40
[warn] < Resultset 1 / Row 3 @ C12
[warn] > Resultset 1 / Row 2 @ C10, C11, C12, PK4
[warn] > Resultset 1 / Row 6 @ PK2, C10, C11, PK3
[warn] > Resultset 1 / Row 8
[info] Compared 7 vs 8 rows, 5 differences detected
[error] dbo.sp02a has unmatched result set
[info] Comparing output parameters
[warn] OUT parameters PROCNAME mismatch:
[warn] < [SP02A ]
[warn] > [SP02B ]
[warn] OUT parameters ROWNUM mismatch:
[warn] < [ 7]
[warn] > [ 8]
[warn] OUT parameters TS mismatch:
[warn] < [2020-12-23 00:00:00.000000]
[warn] > [2020-12-24 00:00:00.000000]
[info] Compared 3 parameters
[warn] IanusTest/dbo.sp02a vs IanusTest/dbo.sp02b reported 10 differences
Note
Ianus will trace actual content of the columns causing the difference in the log file.
Status codes
| Status | Status code | Description |
|---|---|---|
| Ready | -1 | Task is initialized, but not yet started |
| Running | -2 | Task is running |
| Success | 0 | Task completed successfully |
| Warnings | 1 | Task completed with warnings |
| Errors | 2 | Task completed with errors |
| Aborted | 9 | Task cannot be executed |