CHECK_SYSROUTINE procedure
The CHECK_SYSROUTINE procedure compares entries in the QSYS2.SYSROUTINES table between two systems.
Authorization: See Note below.
The schema is SYSTOOLS.
- remote-rdb-name
- A character string containing the name of the remote database.
- schema-name
- A character string containing the name of the schema to compare.
- avoid-result-set
- An integer value that indicates whether a result set should be returned. The default is 0.
- 1
- No result set is returned.
- 0
- Result set is returned.
The result set that is returned or the table that is created contains the following columns:
| Column Name | System Column Name | Data Type | Description |
|---|---|---|---|
| SERVER_NAME | SRVRNAME | VARCHAR(18) | Name of server where the request was run. |
| ROUTINE_CREATED | RTNCREATE | TIMESTAMP | The timestamp when the routine was created. |
| ROUTINE_DEFINER | DEFINER | VARCHAR(128) | Name of the user that defined the routine. |
| LAST_ALTERED | ALTEREDTS | TIMESTAMP | Timestamp when routine was last altered. |
| SPECIFIC_SCHEMA | SPECSCHEMA | VARCHAR(128) | Schema name of the routine instance. |
| SPECIFIC_NAME | SPECNAME | VARCHAR(128) | Specific name of the routine instance. |
| ROUTINE_SCHEMA | RTNSCHEMA | VARCHAR(128) | Name of the schema that contains the routine. |
| ROUTINE_NAME | RTNNAME | VARCHAR(128) | Name of the routine. |
| ROUTINE_TYPE | RTNTYPE | VARCHAR(9) | Type of the routine. |
| ROUTINE_BODY | BODY | VARCHAR(8) | Type of the routine body. |
| EXTERNAL_NAME | EXTNAME | VARCHAR(279) | External program name for routine. |
| IN_PARMS | IN_PARMS | SMALLINT | Identifies the number of input parameters. |
| OUT_PARMS | OUT_PARMS | SMALLINT | Identifies the number of output parameters. |
| INOUT_PARMS | INOUT_PARM | SMALLINT | Identifies the number of input/output parameters. |
| SQL_DATA_ACCESS | DATAACCESS | VARCHAR(8) | Identifies whether a routine contains SQL and whether it reads or modifies data. |
| PARM_SIGNATURE | SIGNATURE | VARCHAR(2048) | The signature of the routine. |
Note
This procedure is provided in the SYSTOOLS schema as an example of how to compare catalog tables between systems using an SQL procedure. Similar to other Db2® for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar procedures, or to create a customized version within a user-specified schema.
Services provided in SYSTOOLS have authorization requirements that are determined by the interfaces used to implement the service. To understand the authority requirements, extract the SQL for the service and examine the implementation.
Example
Compare the current system to a remote system to find which routines differ, when they were created, and who created them.
CALL SYSTOOLS.CHECK_SYSROUTINE('LP01UT18', 'CORPDB_EX')