DBPedias

All your database knowledge are belong to you

DB2 zOS

SYSIBM.SYSROUTINES (V8)

Description: Contains a row for every routine. (A routine can be a user-defined function, cast function, or stored procedure.)

Tablespace: SYSOBJ

Indexes(Fields):
DSNOFX01(NAME.PARM_COUNT.ROUTINETYPE.PARM_SIGNATURE.SCHEMA.PARM1.PARM2.PARM3.PARM4.PARM5.PARM6.PARM7.PARM8.PARM9.PARM10.PARM11.PARM12.PARM13.
PARM14.PARM15.PARM16.PARM17.PARM18.PARM19.PARM20.PARM21.PARM22.PARM23.PARM24.PARM25.PARM26.PARM27.PARM28.PARM29.PARM30)
DSNOFX02(SCHEMA.SPECIFICNAME.)
ROUTINETYPE 

     DSNOFX03(NAME.SCHEMA.CAST_FUNCTION.PARM_COUNT.PARM_SIGNATURE.PARM1)
 DSNOFX04(ROUTINE_ID1)
 DSNOFX05(SOURCESCHEMA.SOURCESPECIFIC.ROUTINETYPE)
 DSNOFX06(SCHEMA.NAME.ROUTINETYPE.PARM_COUNT)
 DSNOFX07(NAME.PARM_COUNT.ROUTINETYPE.SCHEMA.PARM_SIGNATURE.PARM1.PARM2.PARM3.PARM4.PARM5.PARM6.PARM7.PARM8.PARM9.PARM10.PARM11.PARM12.PARM13.
 PARM14.PARM15.PARM16.PARM17.PARM18.PARM19.PARM20.PARM21.PARM22.PARM23.PARM24.PARM25.PARM26.PARM27.PARM28.PARM29.PARM30)
 DSNOFX08(JARSCHEMA.JAR_ID)
Column name Description Data type
SCHEMA Schema of the routine.
NOT NULL
OWNER Owner of the routine.
NOT NULL
NAME Name of the routine.
NOT NULL
ROUTINETYPE Type of routine: ; F
User-defined function or cast function
P
Stored procedure

NOT NULL
CREATEDBY Authorization ID under which the routine was created.
NOT NULL
SPECIFICNAME Specific name of the routine.
NOT NULL
ROUTINEID Internal identifier of the routine.
NOT NULL
RETURN_TYPE Internal identifier of the result data type of the function. The column contains a -2 if the function is a table function.
NOT NULL
ORIGIN Origin of the routine: ; E
External user-defined function (external table or external scalar) or stored procedure
Q
SQL function
S
System-generated function
U
Sourced on user-defined function or built-in function

NOT NULL
FUNCTION_TYPE Type of function: ; C
Aggregate function
S
Scalar function
T
Table function
blank
For a stored procedure (ROUTINETYPE = 'P')

NOT NULL
PARM_COUNT Number of parameters for the routine.
NOT NULL
LANGUAGE Implementation language of the routine: ; ASSEMBLE
C
COBOL
COMPJAVA
JAVA
PLI
REXX
SQL
blank
ROUTINETYPE = 'F' and ORIGIN is not 'E' or not 'Q'.

NOT NULL
COLLID Name of the package collection to be used when the routine is executed. A blank value indicates the package collection is the same as the package collection of the program that invoked the routine.
NOT NULL
SOURCESCHEMA If ORIGIN is 'U' and ROUTINETYPE is 'F', the schema of the source user-defined function ('SYSIBM' for a source built-in function). Otherwise, the value is blank.
NOT NULL
SOURCESPECIFIC If ORIGIN is 'U' and ROUTINETYPE is 'F', the specific name of the source user-defined function or source built-in function name. Otherwise, the value is blank.
NOT NULL
DETERMINISTIC The deterministic option of an external function or a stored procedure: ; N
Indeterminate (results may differ with a given set of input values).
Y
Deterministic (results are consistent).
blank
ROUTINETYPE='F' and ORIGIN is not 'E' or not 'Q' (the routine is a function, but not an external function or an SQL function).

NOT NULL
EXTERNAL_ACTION The external action option of an external function or SQL function: ; N
Function has no side effects.
E
Function has external side effects so that the number of invocations is important.
blank
ORIGIN is not 'E' or 'Q' for the function (ROUTINETYPE='F'), or it is a stored procedure (ROUTINETYPE='P').

NOT NULL
NULL_CALL The CALLED ON NOT NULL INPUT option of an external function or stored procedure: ; N
The routine is not called if any parameter has a NULL value.
Y
The routine is called if any parameter has a NULL value.
blank
ROUTINETYPE='F' and ORIGIN is not 'E' (the routine is a function, but not an external function).

NOT NULL
CAST_FUNCTION Whether the routine is a cast function: ; N
The routine is not a cast function.
Y
The routine is a cast function.
blank
ORIGIN is not 'E' for the function (ROUTINETYPE='F'), or it is a stored procedure (ROUTINETYPE='P').

A cast function is generated by DB2 for a CREATE DISTINCT TYPE statement,


NOT NULL
SCRATCHPAD The SCRATCHPAD option of an external function: ; N
This function does not have a SCRATCHPAD.
Y
This function has a SCRATCHPAD.
blank
ORIGIN is not 'E' for the function (ROUTINETYPE='F'), or it is a stored procedure (ROUTINETYPE='P').

NOT NULL
SCRATCHPAD_LENGTH Length of the scratchpad if the ORIGIN is 'E' for the function (ROUTINETYPE='F') and NO SCRATCHPAD is not specified. Otherwise, the value is 0.
NOT NULL
FINAL_CALL The FINAL CALL option of an external function: ; N
A final call will not be made to the function.
Y
A final call will be made to the function.
blank
ORIGIN is not 'E' for the function (ROUTINETYPE='F'), or it is a stored procedure (ROUTINETYPE='P').

NOT NULL
PARALLEL The PARALLEL option of an external function: ; A
This function can be invoked by parallel tasks.
D
This function cannot be invoked by parallel tasks.
blank
ORIGIN is not 'E' for the function (ROUTINETYPE='F'), or it is a stored procedure (ROUTINETYPE='P').

NOT NULL
PARAMETER_STYLE The PARAMETER STYLE option of an external function or stored procedure: ; D
DB2SQL. All parameters are passed to the external function or stored procedure according to the DB2SQL standard convention.
G
GENERAL. All parameters are passed to the stored procedure according to the GENERAL standard convention.
N
GENERAL CALL WITH NULLS. All parameters are passed to the stored procedure according to the GENERAL WITH NULLS convention.
J
JAVA. All parameters are passed to the function or procedure according to the conventions for JAVA and SQLJ specifications.
blank
The column is blank if the ORIGIN is not 'E' or if LANGUAGE is SQL.

NOT NULL
FENCED ; Y
Indicates that this routine runs separately the DB2 address space. All user-defined routines run in the DB2 address space.
blank
ORIGIN is 'Q' .

NOT NULL
SQL_DATA_ACCESS The SQL statements that are allowed in an external function, SQL function, or stored procedure: ; C
CONTAINS SQL: Only SQL that does not read or modify data is allowed.
M
MODIFIES SQL DATA: All SQL is allowed, including SQL that reads or modifies data.
N
NO SQL: SQL is not allowed.
R
READS SQL DATA: Only SQL that reads data is allowed.
blank
Not applicable.

NOT NULL
DBINFO The DBINFO option of an external function or stored procedure: ; N
No, the DBINFO parameter will not be passed to the external function or stored procedure.
Y
Yes, the DBINFO parameter will be passed to the external function or stored procedure.
blank
ORIGIN is not 'E'.

NOT NULL
STAYRESIDENT The STAYRESIDENT option of the routine, which determines whether the routine is to be deleted from memory when the routine ends. ; N
The load module is to be deleted from memory after the routine terminates.
Y
The load module is to remain resident in memory after the routine terminates.
blank
ORIGIN is not 'E'.

NOT NULL
ASUTIME Number of CPU service units permitted for any single invocation of this routine. If ASUTIME is zero, the number of CPU service units is unlimited. The column is blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'. If a routine consumes more CPU service units than the ASUTIME value allows, DB2 cancels the routine.
NOT NULL
WLM_ENVIRONMENT Name of the WLM environment to be used to run this routine. The column is blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'. If the ROUTINETYPE = 'P', the value might be blank. Blank causes the stored procedure to be run in the DB2 stored procedure address space.
NOT NULL
WLM_ENV_FOR_NESTED For nested routine calls, indicates whether the address space of the calling stored procedure or user-defined function is used to run the nested stored procedure or user-defined function: ; N
The nested stored procedure or user-defined function runs in an address space other than the specified WLM environment if the calling stored procedure or user-defined function is not running in the specified WLM environment. 'WLM ENVIRONMENT name' was specified.
Y
The nested stored procedure or user-defined function runs in the environment used by the calling stored procedure or user-defined function. 'WLM ENVIRONMENT(name,*)' was specified.
blank
WLM_ENVIRONMENT is blank. The column is blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.

NOT NULL
PROGRAM_TYPE Indicates whether the routine runs as a Language Environment main routine or a subroutine: ; M
The routine runs as a main routine.
S
The routine runs as a subroutine.
blank
ORIGIN is not 'E'.

NOT NULL
EXTERNAL_SECURITY Specifies the authorization ID to be used if the routine accesses resources protected by an external security product: ; D
DB2 - The authorization ID associated with the WLM-established stored procedure address space.
U
USER - The authorization ID of the SQL user that invoked the routine.
C
DEFINER - The authorization ID of the owner of the routine.
blank
ORIGIN is not 'E'.

NOT NULL
COMMIT_ON_RETURN If ROUTINETYPE = 'P', whether the transaction is always to be committed immediately on successful return (non-negative SQLCODE) from this stored procedure: ; N
The unit of work is to continue.
Y
The unit of work is to be committed immediately.

If ROUTINETYPE = 'F', the value is blank.


NOT NULL
RESULT_SETS If ROUTINETYPE = 'P', the maximum number of ad hoc result sets that this stored procedure can return. If no ad hoc result sets exist or ROUTINETYPE = 'F', the value is zero.
NOT NULL
LOBCOLUMNS If ORIGIN = 'E' or 'Q', the number of LOB columns found in the parameter list for this user-defined function. If no LOB columns are found in the parameter list or ORIGIN is not 'E' or not 'Q', the value is 0.
NOT NULL
CREATEDTS Time when the CREATE statement was executed for this routine.
NOT NULL
ALTEREDTS Time when the last ALTER statement was executed for this routine.
NOT NULL
IBMREQD A value of Y indicates that the row came from the basic machine-readable material (MRM) tape.
NOT NULL
PARM1 Internal use only
NOT NULL
PARM2 Internal use only
NOT NULL
PARM3 Internal use only
NOT NULL
PARM4 Internal use only
NOT NULL
PARM5 Internal use only
NOT NULL
PARM6 Internal use only
NOT NULL
PARM7 Internal use only
NOT NULL
PARM8 Internal use only
NOT NULL
PARM9 Internal use only
NOT NULL
PARM10 Internal use only
NOT NULL
PARM11 Internal use only
NOT NULL
PARM12 Internal use only
NOT NULL
PARM13 Internal use only
NOT NULL
PARM14 Internal use only
NOT NULL
PARM15 Internal use only
NOT NULL
PARM16 Internal use only
NOT NULL
PARM17 Internal use only
NOT NULL
PARM18 Internal use only
NOT NULL
PARM19 Internal use only
NOT NULL
PARM20 Internal use only
NOT NULL
PARM21 Internal use only
NOT NULL
PARM22 Internal use only
NOT NULL
PARM23 Internal use only
NOT NULL
PARM24 Internal use only
NOT NULL
PARM25 Internal use only
NOT NULL
PARM26 Internal use only
NOT NULL
PARM27 Internal use only
NOT NULL
PARM28 Internal use only
NOT NULL
PARM29 Internal use only
NOT NULL
PARM30 Internal use only
NOT NULL
IOS_PER_INVOC Estimated number of I/Os that required to execute the routine. The value is -1 if the estimated number is not known.
NOT NULL WITH
DEFAULT -1
INSTS_PER_INVOC Estimated number of machine instructions that required to execute the routine. The value is -1 if the estimated number is not known.
NOT NULL WITH
DEFAULT -1
INITIAL_IOS Estimated number of I/O's that are performed the first time or the last time the routine is invoked. The value is -1 if the estimated number is not known.
NOT NULL WITH
DEFAULT -1
INITIAL_INSTS Estimated number of machine instructions that are performed the first time or the last time the routine is invoked. The value is -1 if the estimated number is not known.
NOT NULL WITH
DEFAULT -1
CARDINALITY The predicted cardinality of the routine, -1 to trigger DB2's use of the default value (10,000).
NOT NULL WITH
DEFAULT -1
RESULT_COLS For a table function, the number of columns in the result table. Otherwise, the value is 1.
NOT NULL
DEFAULT 1
EXTERNAL_NAME The path/module/function that DB2 should load to execute the routine. The column is blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.
NOT NULL
PARM_SIGNATURE Internal use only
NOT NULL WITH
DEFAULT
FOR BIT DATA
RUNOPTS The Language Environment run-time options to be used for this routine. An empty string indicates that the installation default Language Environment run-time options are to be used. The column is blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.
NOT NULL
REMARKS A character string provided by the user with the COMMENT statement.
NOT NULL
JAVA_SIGNATURE The signature of the jar file. ; blank
When PARAMETER STYLE is not JAVA. The column is also blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.

NOT NULL WITH
DEFAULT
CLASS The class name contained in the jar file. ; blank
When PARAMETER STYLE is not JAVA. The column is also blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.

NOT NULL WITH
DEFAULT
JARSCHEMA The schema of the jar file. ; blank
When PARAMETER STYLE is not JAVA. The column is also blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.

NOT NULL WITH
DEFAULT
JAR_ID The name of the jar file. ; blank
When PARAMETER STYLE is not JAVA. The column is also blank if ROUTINETYPE = 'F' and ORIGIN is not 'E'.

NOT NULL WITH
DEFAULT
SPECIAL_REGS The SPECIAL REGISTER option for a routine. ; I
INHERIT SPECIAL REGISTERS
D
DEFAULT SPECIAL REGISTERS
blank
ROUTINETYPE = 'F' and ORIGIN is not 'E' or not 'Q'.

NOT NULL WITH
DEFAULT 'I'
NUM_DEP_MQTS Number of dependent materialized query tables. The value is 0 if the row does not describe a user-defined table function, or if no materialized query tables are defined on the table function.
NOT NULL WITH
DEFAULT
MAX_FAILURE Allowable 
     failures for this routine (0-32767). If zero
     is specified, the routine will never be
     stopped. If no value is specified for this
     routine, the default will be -1 to indicate that the DB2 installation
     parameter (STORMXAB) will be used.

NOT NULL WITH
DEFAULT -1
PARAMETER_CCSID A CCSID that specifies how character, graphic, date, time, and timestamp data types for system generated parameters to the routine such as message tokens and DBINFO should be passed. The value is dependent on the encoding scheme specified implicitly or explicitly for the PARAMETER CCSID clause defined at the system for that encoding scheme. The following list describes the CCSID for each encoding scheme: ; ASCII
If mixed data is allowed, this CCSID is for mixed ASCII data, SBCS data uses the corresponding SBCS CCSID, and graphic data uses the corresponding DBCS CCSID. Otherwise, this CCSID is for SBCS ASCII data.
EBCDIC
If mixed data is allowed, this CCSID is for mixed EBCDIC data, SBCS data uses the corresponding SBCS CCSID, and graphic data uses the corresponding DBCS CCSID. Otherwise, this is the CCSID for SBCS EBCDIC data.
UNICODE
This CCSID is for mixed data (1208).


A value of zero means that the CCSIDs used are those CCSIDs for the encoding scheme of other string or datetime parameters in the parameter list or RETURNS clause CCSID clauses, or the value in the DEF ENCODING SCHEME on installation panel DSNTIPF.


NOT NULL WITH
DEFAULT

Adapted from material found in the IBM DB2 Universal Database for z/OS V8 SQL Reference (SC18-7426-02).

Retrieved from "http://dbpedias.com/w/index.php?title=DB2_zOS:SYSIBM.SYSROUTINES_(V8)&oldid=22022"