ALTER FUNCTION (compiled SQL scalar)
The ALTER FUNCTION (compiled SQL scalar) statement changes the description of a user-defined compiled SQL scalar function at the current server. The function options, parameter names, and routine body can be changed and additional versions of the procedure can be defined and maintained using the ALTER FUNCTION statement.
Invocation
This
statement can only be dynamically prepared and the DYNAMICRULES run
behavior must be specified implicitly or explicitly.
Authorization
- Ownership of the function
- The ALTERIN privilege on the schema
- SYSADM authority
- SYSCTRL authority
- System
DBADM
If
the authorization ID that is used to alter the function has installation
SYSADM authority, the function is identified as system-defined function
when the function definition is reevaluated.
Additional privileges might be required in the following situations:
- If SQL-routine-body is specified, the privilege set must include the privileges that are required to execute the statements in SQL-routine-body.
- If a user-defined type is referenced (i.e. as the data type of
an SQL variable in the body of the function), the privilege set
must include at least one of the following:
- Ownership of the distinct type
- The USAGE privilege on the user-defined type
- SYSADM authority
- If
the function uses a table as a parameter, the privilege set must also
include at least one of the following:
- Ownership of the table
- The SELECT privilege on the table
- SYSADM authority
- If you specify the WLM ENVIRONMENT FOR DEBUG MODE clause, RACF® or an external security product is invoked to check the required authority for defining programs in the WLM environment. If the WLM environment access is protected in RACF, the privilege set must include the required authority.
- When replacing an SQL scalar function, the privilege set must include the required authorization to add a new package or a new version of an existing package depending on the value of the BIND NEW PACKAGE field on installation panel DSNTIPP, or the privilege set must include SYSADM or SYSCTRL authority.
Additional authorization might be required on the SYSDUMMYx tables depending on the content of the function definition. For details, see SYSDUMMYx tables.
At
least one of the following privileges is required if the SECURED option
is specified or if the function is currently secured and the NOT SECURED
option is specified:- SECADM authority
- CREATE_SECURE_OBJECT privilege
At least one of those privileges is also required if the function is currently secure and the ALTER ACTIVE VERSION, ALTER VERSION routine-version-id, ADD VERSION, or REPLACE clause is specified.
- The privilege set includes SYSADM authority
- The privilege set includes SYSCTRL authority
- The SQL authorization ID of the process has the ALTERIN privilege on the schema
Syntax
>>-ALTER -| function-designator |-------------------------------> .-ALTER-. .-ACTIVE VERSION--------------. >--+-+-------+--+-----------------------------+--| option-list |---------+->< | +-ALL VERSIONS----------------+ | | '-VERSION--routine-version-id-' | | .-ACTIVE VERSION--------------. | +-REPLACE--+-----------------------------+--| routine-specification |-+ | '-VERSION--routine-version-id-' | +-ADD VERSION--routine-version-id--| routine-specification |----------+ +-ACTIVATE VERSION--routine-version-id--------------------------------+ | .-ACTIVE VERSION--------------. | +-REGENERATE--+-----------------------------+-------------------------+ | '-VERSION--routine-version-id-' | '-DROP VERSION--routine-version-id------------------------------------'
function-designator:
>>-+-FUNCTION--function-name--+-------------------------+-+---->< | | .-,-------------. | | | | V | | | | '-(----+-----------+-+--)-' | | '-data-type-' | | | | | '-SPECIFIC FUNCTION--specific-name---------------------'
routine-specification:
>>-(--+-----------------------------------+--)------------------> | .-,-----------------------------. | | V | | '---parameter-name--| data-type |-+-' .---------------------. V | >--RETURNS--| data-type2 |----+-----------------+-+-------------> '-| option-list |-' >--| SQL-routine-body |----------------------------------------><
data-type, data-type2:
>>-+-| built-in-type |--+-------------------------------------->< '-distinct-type-name-'
built-in-type:
>>-+-+-SMALLINT----+-------------------------------------------------------------------------------------------------+->< | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)--------------------. | +-+-DECIMAL-+--+--------------------------+-----------------------------------------------------------------------+ | +-DEC-----+ '-(integer-+-----------+-)-' | | '-NUMERIC-' '-, integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+---------------------------------------------------------------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(34)-. | +-DECFLOAT--+------+----------------------------------------------------------------------------------------------+ | '-(16)-' | | .-(1)-------. | +-+-+-+-CHARACTER-+--+-----------+----------+----+--------------------+--+----------------------+---------------+-+ | | | '-CHAR------' '-(integer)-' | '-CCSID--+-ASCII---+-' '-FOR--+-SBCS--+--DATA-' | | | | '-+-+-CHARACTER-+--VARYING-+--(integer)-' +-EBCDIC--+ +-MIXED-+ | | | | | '-CHAR------' | '-UNICODE-' '-BIT---' | | | | '-VARCHAR----------------' | | | | .-(1M)-------------. | | | '-+-+-CHARACTER-+--LARGE OBJECT-+--+------------------+----+--------------------+--+----------------------+---' | | | '-CHAR------' | '-(integer-+---+-)-' '-CCSID--+-ASCII---+-' '-FOR--+-SBCS--+--DATA-' | | '-CLOB------------------------' +-K-+ +-EBCDIC--+ '-MIXED-' | | +-M-+ '-UNICODE-' | | '-G-' | | .-(1)-------. | +-+-GRAPHIC--+-----------+-------+--+--------------------+--------------------------------------------------------+ | | '-(integer)-' | '-CCSID--+-ASCII---+-' | | +-VARGRAPHIC--(--integer--)----+ +-EBCDIC--+ | | | .-(1M)-------------. | '-UNICODE-' | | '-DBCLOB--+------------------+-' | | '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-BINARY--+-----------+-------------------------+---------------------------------------------------------------+ | | '-(integer)-' | | | +-+-BINARY VARYING-+-(integer)------------------+ | | | '-VARBINARY------' | | | | .-(1M)-------------. | | | '-+-BINARY LARGE OBJECT-+--+------------------+-' | | '-BLOB----------------' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE------------------------------------------------+---------------------------------------------------------+ | +-TIME------------------------------------------------+ | | | .-(--6--)-------. .-WITHOUT TIME ZONE-. | | | '-TIMESTAMP--+---------------+--+-------------------+-' | | '-(--integer--)-' '-WITH TIME ZONE----' | +-ROWID-----------------------------------------------------------------------------------------------------------+ '-XML-------------------------------------------------------------------------------------------------------------'
SQL-routine-body:
>>-SQL-control-statement---------------------------------------><
option-list: (Specify options in any order. Specify at least one option. Do not specify the same option more than one time.)
(1) >>--------------------------------------------------------------> .-NOT DETERMINISTIC-. .-EXTERNAL ACTION----. .-READS SQL DATA----. >--+-------------------+--+--------------------+--+-------------------+--> '-DETERMINISTIC-----' '-NO EXTERNAL ACTION-' +-CONTAINS SQL------+ '-MODIFIES SQL DATA-' .-CALLED ON NULL INPUT-------. .-STATIC DISPATCH-. >--+----------------------------+--+-----------------+--+-------------------+--> '-RETURNS NULL ON NULL INPUT-' +-ALLOW PARALLEL----+ '-DISALLOW PARALLEL-' >--+---------------------+--+------------------------+--+-----------------------------------+--> +-DISALLOW DEBUG MODE-+ '-QUALIFIER--schema-name-' '-PACKAGE OWNER--authorization-name-' +-ALLOW DEBUG MODE----+ '-DISABLE DEBUG MODE--' .-ASUTIME NO LIMIT-------. .-INHERIT SPECIAL REGISTERS-. >--+------------------------+--+---------------------------+----> '-ASUTIME LIMIT--integer-' '-DEFAULT SPECIAL REGISTERS-' .-CURRENT DATA NO--. .-DEGREE 1---. >--+--------------------------------------+--+------------------+--+------------+--> '-WLM ENVIRONMENT FOR DEBUG MODE--name-' '-CURRENT DATA YES-' '-DEGREE ANY-' >--+-------------------------------------------------------+----> +-CONCURRENT ACCESS RESOLUTION--USE CURRENTLY COMMITTED-+ '-CONCURRENT ACCESS RESOLUTION--WAIT FOR OUTCOME--------' .-DYNAMICRULES RUN--------. >--+-------------------------+----------------------------------> +-DYNAMICRULES BIND-------+ +-DYNAMICRULES DEFINEBIND-+ +-DYNAMICRULES DEFINERUN--+ +-DYNAMICRULES INVOKEBIND-+ '-DYNAMICRULES INVOKERUN--' .-WITHOUT EXPLAIN-. .-WITHOUT IMMEDIATE WRITE-. >--+-------------------------------------+--+-----------------+--+-------------------------+--> +-APPLICATION ENCODING SCHEME ASCII---+ '-WITH EXPLAIN----' '-WITH IMMEDIATE WRITE----' +-APPLICATION ENCODING SCHEME EBCDIC--+ '-APPLICATION ENCODING SCHEME UNICODE-' .-ISOLATION LEVEL CS-. .-OPTHINT--''--------------. >--+--------------------+--+--------------------------+--+------------------------------------+--> +-ISOLATION LEVEL RS-+ '-OPTHINT--string-constant-' | .-,--------------------. | +-ISOLATION LEVEL RR-+ | V | | '-ISOLATION LEVEL UR-' '-SQL PATH----+-schema-name------+-+-' +-SYSTEM PATH------+ '-+-SESSION USER-+-' '-USER---------' >--+-----------------------------------------+------------------> +-QUERY ACCELERATION NONE-----------------+ +-QUERY ACCELERATION ENABLE---------------+ +-QUERY ACCELERATION ENABLE WITH FAILBACK-+ +-QUERY ACCELERATION ELIGIBLE-------------+ '-QUERY ACCELERATION ALL------------------' >--+-----------------------+------------------------------------> +-GET_ACCEL_ARCHIVE NO--+ '-GET_ACCEL_ARCHIVE YES-' .-REOPT NONE---. .-VALIDATE RUN--. >--+--------------+--+---------------+--+------------------------------+--+-------------------+--> +-REOPT ALWAYS-+ '-VALIDATE BIND-' +-ROUNDING DEC_ROUND_CEILING---+ +-DATE FORMAT ISO---+ '-REOPT ONCE---' +-ROUNDING DEC_ROUND_DOWN------+ +-DATE FORMAT EUR---+ +-ROUNDING DEC_ROUND_FLOOR-----+ +-DATE FORMAT USA---+ +-ROUNDING DEC_ROUND_HALF_DOWN-+ +-DATE FORMAT JIS---+ +-ROUNDING DEC_ROUND_HALF_EVEN-+ '-DATE FORMAT LOCAL-' +-ROUNDING DEC_ROUND_HALF_UP---+ '-ROUNDING DEC_ROUND_UP--------' .-FOR UPDATE CLAUSE REQUIRED-. >--+---------------+--+----------------------------+--+-------------------+--+-SECURED-----+->< +-DECIMAL(15)---+ '-FOR UPDATE CLAUSE OPTIONAL-' +-TIME FORMAT ISO---+ '-NOT SECURED-' +-DECIMAL(31)---+ +-TIME FORMAT EUR---+ +-DECIMAL(15,s)-+ +-TIME FORMAT USA---+ '-DECIMAL(31,s)-' +-TIME FORMAT JIS---+ '-TIME FORMAT LOCAL-'
- Only LANGUAGE SQL, NOT DETERMINISTIC, DETERMINISTIC, EXTERNAL ACTION, NO EXTERNAL ACTION, CONTAINS SQL, READS SQL DATA, STATIC DISPATCH, and CALLED ON NULL INPUT can be specified for an inline SQL scalar function.
Description
One of the following three clauses identifies the function to be changed.
- FUNCTION function-name
- Identifies the SQL function by its function name.
The identified function must be an SQL scalar function. There must be exactly one function with function-name in the schema. The function can have any number of input parameters.1 If the schema does not contain a function with function-name or contains more than one function with this name, an error occurs.
- FUNCTION function-name (parameter-type,...)
- Identifies the SQL function by its function signature, which uniquely
identifies the function.
- function-name
- Gives the function name of the SQL function.
If function-name() is specified, the function that is identified must have zero parameters.
- (parameter-type,...)
- Specifies the number of input parameters of the function
and the name and data type of each parameter.
If the function was defined with a table parameter (the LIKE TABLE name AS LOCATOR clause was specified in the CREATE FUNCTION statement to indicate that one of the input parameters is a transition table), the function signature cannot be used to uniquely identify the function. Instead, use one of the other syntax variations to identify the function with its function name, if unique, or its specific name.
- (data-type,...)
- Identifies the number of input parameters of the function and
the data type of each parameter. The data type of each parameter must
match the data type that was specified in the CREATE FUNCTION statement
for the parameter in the corresponding position. The number of data
types and the logical concatenation of the data types are used to
uniquely identify the function. Therefore, you cannot change the number
of parameters or the data types of the parameters.
For data types that have a length, precision, or scale attribute, you can use a set of empty parentheses, specify a value, or accept the default values:
- Empty parentheses indicate that DB2 is
to ignore the attribute when determining whether the data types match.
For example, DEC() will be considered a match for a parameter of a function defined with a data type of DEC(7,2). Similarly DECFLOAT() will be considered a match for DECFLOAT(16) or DECFLOAT(34).
FLOAT cannot be specified with empty parentheses because its parameter value indicates different data types (REAL or DOUBLE).
- If you use a specific value for a length, precision, or scale
attribute, the value must exactly match the value that was specified
(implicitly or explicitly) in the CREATE FUNCTION statement.
The specific value for FLOAT(n) does not have to exactly match the defined value of the source function because 1<=n<= 21 indicates REAL and 22<=n<=53 indicates DOUBLE. Matching is based on whether the data type is REAL or DOUBLE.
- If length, precision, or scale is not explicitly specified and empty parentheses are not specified, the default length of the data type is implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.
For data types with a subtype or encoding scheme attribute, specifying the FOR subtype DATA clause or the CCSID clause is optional. Omission of either clause indicates that DB2 is to ignore the attribute when determining whether the data types match. If you specify either clause, it must match the value that was implicitly or explicitly specified in the CREATE FUNCTION statement.
See CREATE FUNCTION for more information on the specification of the parameter list.
- Empty parentheses indicate that DB2 is
to ignore the attribute when determining whether the data types match.
A function with the function signature must exist in the explicitly or implicitly specified schema.
- SPECIFIC FUNCTION specific-name
- Identifies a particular user-defined function by its specific
name. The name is implicitly or explicitly qualified with a
schema name. A compiled SQL scalar function with the specific
name must exist in the schema. If the specific name is not qualified,
it is implicitly qualified with a schema name as described in the
description for FUNCTION function-name.
- ALTER ACTIVE VERSION, ALL VERSIONS,
or VERSION routine-version-id
- Specifies that a version of the function is to be changed. When
you change a function using ALTER option-list,
any option that is not explicitly specified will use the existing
value from the version of the function that is being changed.
- ACTIVE VERSION, ALL VERSION or, VERSION routine-version-id
- Identifies the version of the function that is to be changed.
- ACTIVE VERSION
- Specifies that the currently active version of the function is
to be changed, replaced, or regenerated. If the function is secure,
the changed, replaced, or regenerated version remains secure.
ACTIVE VERSION is the default.
- ALL VERSIONS
- Specifies that all of the versions of the function are to be changed. SECURED and NOT SECURED are the only options that can be changed when ALL VERSIONS is specified.
- VERSION routine-version-id
- Identifies the version of the function that is to be changed, replaced, or regenerated. routine-version-id is the version identifier that is assigned when the version of the function is defined. routine-version-id must identify a version of the specified function that exists at the current server. If the function is secure, the changed, replaced, or regenerated version remains secure.
- REPLACE ACTIVE VERSION or VERSION routine-version-id
- Specifies that a version of the function is to be replaced.
Binding the replaced version of the function might result in a new access path even if the routine body is not being changed.
When you replace a function, the data types, CCSID specifications, and character data attributes (FOR BIT/SBCS/MIXED DATA) of the parameters must be the same as the attributes of the corresponding parameters for the currently active version of the function. For options that are not explicitly specified, the system default values for those options are used, even if those options were explicitly specified for the version of the function that is being replaced. This is not the case for versions of the function that specified DISABLE DEBUG MODE. If DISABLE DEBUG MODE is specified for a version of a function, it cannot be changed by using the REPLACE clause. When a function definition is replaced, any existing comments in the catalog for that definition of the function are removed.
- ACTIVE VERSION or VERSION routine-version-id
- Identifies the version of the function that is to be replaced.
- ACTIVE VERSION
- Specifies that the currently active version of the function is
to be changed, replaced, or regenerated. If the function is secure,
the changed, replaced, or regenerated version remains secure.
ACTIVE VERSION is the default.
- VERSION routine-version-id
- Identifies the version of the function that is to be changed, replaced, or regenerated. routine-version-id is the version identifier that is assigned when the version of the function is defined. routine-version-id must identify a version of the specified function that exists at the current server. If the function is secure, the changed, replaced, or regenerated version remains secure.
- ADD VERSION routine-version-id
- Specifies that a new version of the function is to be created. routine-version-id is
the version identifier for the new version of the function. routine-version-id must
not identify a version of the specified function that already exists
at the current server.
When a new version of a function is created, the comment that is recorded in the catalog for the new version will be the same as the comment that is in the catalog for the currently active version.
When you add a new version of a function, the data types, CCSID specifications, and character data attributes (FOR BIT/SBCS/MIXED DATA) of the parameters must be the same as the attributes of the corresponding parameters for the currently active version of the function. The parameter names can differ from the other versions of the function. For options that are not explicitly specified, the system default values will be used.
If the function is secure, the new version is considered secure.
- ACTIVATE VERSION routine-version-id
- Specifies the version of the function that is to be the currently
active version. routine-version-id is the
version identifier that is assigned when the version of the function
is defined. The version that is specified with routine-version-id is
the version that will be invoked by a function invocation. routine-version-id must
identify a version of the function that exists at the current server.
- REGENERATE ACTIVE VERSION or VERSION routine-version-id
- Specifies that a version of the function is to be regenerated. When DB2 maintenance is applied that changes how an SQL function is generated, the function might need to be regenerated to process the changes from applying the maintenance.
REGENERATE automatically rebinds, at the current server, the package for the SQL control statements for the function and rebinds the package for the SQL statements that are included in the body of the function.
REGENERATE is different than the REBIND PACKAGE command. REBIND PACKAGE rebinds the SQL statements (usually to generate better access paths for those statement) but the SQL control statements in the function definition are not rebound.
When a function definition is regenerated, any existing comments in the catalog for that definition of the function are not removed.
- ACTIVE VERSION or VERSION routine-version-id
- Identifies the version of the function that is to be regenerated.
- ACTIVE VERSION
- Specifies that the currently active version of the function is
to be changed, replaced, or regenerated. If the function is secure,
the changed, replaced, or regenerated version remains secure.
ACTIVE VERSION is the default.
- VERSION routine-version-id
- Identifies the version of the function that is to be changed, replaced, or regenerated. routine-version-id is the version identifier that is assigned when the version of the function is defined. routine-version-id must identify a version of the specified function that exists at the current server. If the function is secure, the changed, replaced, or regenerated version remains secure.
- DROP VERSION routine-version-id
- Drops the version of the function that is identified with routine-version-id. routine-version-id is
the version identifier that is assigned when the version is defined. routine-version-id must
identify a version of the function that exists at the current server
and must not identify the currently active version of the function.
Only the identified version of the function is dropped.
When only a single version of the function exists at the current server, use the DROP FUNCTION statement to drop the function.
- RETURNS
- Identifies the output of the function.
- data-type2
- Specifies the data type of the output. The data type must match
the data type that was specified in the RETURNS clause of the CREATE
FUNCTION statement.
- NOT DETERMINISTIC or DETERMINISTIC
- Specifies
whether the function returns the same results each time that the function
is invoked with the same input arguments.
- NOT DETERMINISTIC
- The function might not return the same result each
time that the function is invoked with the same input arguments. The
function depends on some state values that affect the results. DB2 uses this information to disable
the merging of views and table expressions when processing SELECT
or SQL data change statements that refer to this function. An example
of a function that is not deterministic is one that generates random
numbers.
NOT DETERMINISTIC must be specified explicitly or implicitly if the function program accesses a special register or invokes another function that is not deterministic.
- DETERMINISTIC
- The function always returns the same result each time that the function is invoked with the same input arguments. An example of a deterministic function is a function that calculates the square root of the input. DB2 uses this information to enable the merging of views and table expressions for SELECT or SQL data change statements that refer to this function. If applicable, specify DETERMINISTIC to prevent non-optimal access paths from being chosen for SQL statements that refer to this function.
DB2 does not verify that the function program is consistent with the specification of DETERMINISTIC or NOT DETERMINISTIC.
- EXTERNAL ACTION or NO EXTERNAL ACTION
- Specifies
whether the function takes an action that changes the state of an
object that DB2 does not manage.
An example of an external action is sending a message or writing a
record to a file.
- EXTERNAL ACTION
- The function can take an action that changes the state of an object
that DB2 does not manage.
Some SQL statements that invoke functions with external actions can result in incorrect results if parallel tasks execute the function. For example, if the function sends a note for each initial call to it, one note is sent for each parallel task instead of once for the function.
If you specify EXTERNAL ACTION, DB2:
- Materializes the views and table expressions in SELECT or SQL data change statements that refer to the function. This materialization can adversely affect the access paths that are chosen for the SQL statements that refer to this function. Do not specify EXTERNAL ACTION if the function does not have an external action.
- Does not move the function from one task control block (TCB) to another between FETCH operations.
- Does not allow another function or stored procedure to use the TCB until the cursor is closed. This is also applicable for cursors declared WITH HOLD.
The only changes to resources made outside of DB2 that are under the control of commit and rollback operations are those changes made under RRS control.
EXTERNAL ACTION must be specified implicitly or explicitly specified if the SQL routine body invokes a function that is defined with EXTERNAL ACTION.
- NO EXTERNAL ACTION
- The function does not take any action that changes the state of an object that DB2 does not manage. DB2 uses this information to enable the merging of views and table expressions for SELECT or SQL data change statements that refer to this function. If applicable, specify NO EXTERNAL ACTION to prevent non-optimal access paths from being chosen for SQL statements that refer to this function.
DB2 does not verify that the function program is consistent with the specification of EXTERNAL ACTION or NO EXTERNAL ACTION.
- MODIFIES SQL DATA, READS SQL DATA, or CONTAINS
SQL
- Specifies the classification of SQL statements and nested routines that this routine can execute
or invoke. The database manager verifies that the SQL statements issued by the function, and all
routines locally invoked by the routine, are consistent with this specification; the verification is
not performed when nested remote routines are invoked. For the classification of each statement, see
SQL statement data access classification for routines.
- MODIFIES SQL DATA
- Specifies that the function can execute any SQL statement except the statements that are not
supported in functions. Do
not specify MODIFIES SQL DATA when ALLOW PARALLEL is in effect.
- READS SQL DATA
- Specifies that the function can execute statements with a data access classification of READS
SQL DATA, CONTAINS SQL, or NO SQL. The function cannot execute SQL statements that modify
data.
READS SQL DATA is the default.
- CONTAINS SQL
- Specifies that the function can execute only SQL statements with a data access classification of CONTAINS SQL or NO SQL. The function cannot execute SQL statements the read or modify data.
- CALLED ON NULL INPUT or RETURNS
NULL ON NULL INPUT
- Specifies whether the function is invoked if any of the input
arguments is null at execution time.
- CALLED ON NULL INPUT
- Specifies that the function is to be invoked, if any, or all,
argument values are null. This specification means that the body of
the function must be coded to test for null argument values.
CALLED ON NULL INPUT is the default.
- RETURNS NULL ON NULL INPUT
- Specifies that the function is not invoked and returns the null value if any of the input arguments is null.
- STATIC DISPATCH
- At
function resolution time, DB2 chooses
a function based on the static (or declared) types of the function
parameters.
STATIC DISPATCH is the default.
- ALLOW PARALLEL or DISALLOW PARALLEL
- Specifies if the function can be run in parallel. The default
is DISALLOW PARALLEL, if you specify one or more of the following
clauses:
- NOT DETERMINISTIC
- EXTERNAL ACTION
- MODIFIES SQL DATA
Otherwise, ALLOW PARALLEL is the default.
- ALLOW PARALLEL
- Specifies that the function can be run in parallel.
- DISALLOW PARALLEL
- Specifies that the function cannot be run in parallel.
- ALLOW DEBUG MODE, DISALLOW
DEBUG MODE, or DISABLE DEBUG MODE
- Specifies
whether this version of the routine can be run in debugging mode.
The default is determined using the value of the CURRENT DEBUG MODE
special register.
- ALLOW DEBUG MODE
- Specifies that this version of the routine can be run in debugging mode. When this version of the routine is invoked and debugging is attempted, a WLM environment must be available.
- DISALLOW DEBUG MODE
- Specifies that this version of the routine cannot be run in debugging
mode.
You can use an ALTER statement to change this option to ALLOW DEBUG MODE for this initial version of the routine.
- DISABLE DEBUG MODE
- Specifies that this version of the routine can never be run in
debugging mode.
This version of the routine cannot be changed to specify ALLOW DEBUG MODE or DISALLOW DEBUG MODE after this version of the routine has been created or altered to use DISABLE DEBUG MODE. To change this option, drop the routine and create it again using the option that you want. An alternative to dropping and recreating the routine is to create a version of the routine that uses the option that you want and making that version the active version.
When DISABLE DEBUG MODE is in effect, the WLM ENVIRONMENT FOR DEBUG MODE is ignored.
- QUALIFIER schema-name
- Specifies the implicit qualifier
that is used for unqualified object names that are referenced in the
procedure body. See Unqualified alias, index, JAR file, sequence, table, trigger, and view names for
information about how the default for this option is determined.
- PACKAGE OWNER authorization-name
- Specifies the owner of
the package that is associated with the version of the routine. The
SQL authorization ID of the process is the default value.
The authorization ID must have the privileges that are required to execute the SQL statements that are contained in the routine body and must contain the necessary bind privileges. The value of PACKAGE OWNER is subject to translation when it is sent to a remote system.
If the privilege set lacks SYSADM or SYSCTRL authority, authorization-name must be the same as one of the authorization IDs of the process or the authorization ID of the process. If the privilege set includes SYSADM or SYSCTRL authority, authorization-name can be any authorization ID that contains the necessary bind privileges.
- ASUTIME
- Specifies the total amount of processor time, in CPU service units,
that a single invocation of a routine can run. The value is unrelated to the ASUTIME column of the
resource limit specification table.
When you are debugging a routine, setting a limit can be helpful in case the routine gets caught in a loop. For information on service units, see z/OS MVS Initialization and Tuning Guide.
- NO LIMIT
- Specifies that there is no limit on the service units.
NO LIMIT is the default.
- LIMIT integer
- The limit on the
number of CPU service units is a positive integer in the range of 1 to 2
147 483 647. If the procedure uses more service units than the specified value, DB2 cancels the procedure. The CPU cycles that are consumed by parallel tasks
in a procedure do not contribute towards the specified ASUTIME LIMIT.
- INHERIT SPECIAL REGISTERS or DEFAULT
SPECIAL REGISTERS
- Specifies how special registers are set on entry to the routine.
- INHERIT SPECIAL REGISTERS
- Specifies that the
values of special registers are inherited, according to the rules
that are listed in the table for characteristics of special registers
in a routine in Table 1.
INHERIT SPECIAL REGISTERS is the default.
- DEFAULT SPECIAL REGISTERS
- Specifies that special registers are initialized to the default values, as indicated by the rules in the table for characteristics of special registers in a routine in Table 1.
- WLM ENVIRONMENT FOR DEBUG MODE name
- Specifies
the WLM (workload manager) application environment that is used by DB2 when debugging the routine.
The name of the WLM environment is an SQL
identifier.
If you do not specify WLM ENVIRONMENT FOR DEBUG MODE, DB2 uses the default WLM-established stored procedure address space specified at installation time.
To define a routine that is to run in a specified WLM application environment, you must have the appropriate authority for the WLM application environment. For an example of a RACF command that provides this authorization, see Running stored procedures.
The WLM ENVIRONMENT FOR DEBUG MODE value is ignored when DISABLE DEBUG MODE is in effect.
- CURRENT DATA YES or CURRENT DATA NO
- Specifies whether to require
data currency for read-only and ambiguous cursors when the isolation
level of cursor stability is in effect. CURRENT DATA also determines
whether block fetch can be used for distributed, ambiguous cursors.
- CURRENT DATA YES
- Specifies that data currency is required for read-only and ambiguous cursors. DB2 acquired page or row locks to ensure data currency. Block fetch is ignored for distributed, ambiguous cursors.
- CURRENT DATA NO
- Specifies that data currency is not required for read-only and
ambiguous cursors. Block fetch is allowed for distributed, ambiguous
cursors. Use of CURRENT DATA NO is
not recommended if the routine attempts to dynamically prepare and
execute a DELETE WHERE CURRENT OF statement against an ambiguous cursor
after that cursor is opened. You receive a negative SQLCODE if your
routine attempts to use a DELETE WHERE CURRENT OF statement for any
of the following cursors:
- A cursor that is using block fetch
- A cursor that is using query parallelism
- A cursor that is positioned on a row that is modified by this or another application process
CURRENT DATA NO is the default.
- DEGREE
- Specifies whether to attempt to run
a query using parallel processing to maximize performance.
- 1
- Specifies that parallel processing should not be used.
1 is the default.
- ANY
- Specifies that parallel processing can be used.
- CONCURRENT ACCESS RESOLUTION
- Specifies
the whether processing uses only committed data or whether it will
wait for commit or rollback of data that is in the process of being
updated.
- WAIT FOR OUTCOME
- Specifies that processing will wait for the commit or rollback of data that is in the process of being updated.
- USE CURRENTLY COMMITTED
- Specifies that processing use the currently committed version
of the data when data that is in the process of being updated is encountered. USE
CURRENTLY COMMITTED is applicable on scans that access
tables that are defined in universal table spaces with row or page
level lock size.
When there is lock contention between a read transaction and an insert transaction, USE CURRENTLY COMMITTED is applicable to scans with isolation level CS or RS. Applicable scans include intent read scans for read-only and ambiguous queries and for updatable cursors. USE CURRENTLY COMMITTED is also applicable to scans initiated from WHERE predicates of UPDATE or DELETE statements and the subselect of INSERT statements.
When there is lock contention is between a read transaction and a delete transaction, USE CURRENTLY COMMITTED is applicable to scans with isolation level CS and when CURRENT DATA NO is specified.
- DYNAMICRULES
- Specifies the values that
apply, at run time, for the following dynamic SQL attributes:
- The authorization ID that is used to check authorization
- The qualifier that is used for unqualified objects
- The source for application programming options that DB2 uses to parse and semantically verify dynamic SQL statements
DYNAMICRULES also specifies whether dynamic SQL statements can include GRANT, REVOKE, ALTER, CREATE, DROP, and RENAME statements.
In addition to the value of the DYNAMICRULES clause, the run time environment of a routine controls how dynamic SQL statements behave at run time. The combination of the DYNAMICRULES value and the run time environment determines the value for the dynamic SQL attributes. That set of attribute values is called the dynamic SQL statement behavior. The following values can be specified:- RUN
- Specifies that dynamic SQL statements are to be processed using
run behavior.
RUN is the default.
- BIND
- Specifies that dynamic SQL statements are to be processed using bind behavior.
- DEFINEBIND
- Specifies that dynamic SQL statements are to be processed using either define behavior or bind behavior.
- DEFINERUN
- Specifies that dynamic SQL statements are to be processed using either define behavior or run behavior.
- INVOKEBIND
- Specifies that dynamic SQL statements are to be processed using either invoke behavior or bind behavior.
- INVOKERUN
- Specifies that dynamic SQL statements are to be processed using either invoke behavior or run behavior.
- APPLICATION ENCODING SCHEME
- Specifies the default encoding scheme for SQL variables in static
SQL statements in the routine body. The value is used for defining an SQL variable in a compound
statement if the CCSID clause is not specified as part of the data type, and the PARAMETER CCSID
routine option is not specified.
- ASCII
- Specifies that the data is encoded using the ASCII CCSIDs of the server.
- EBCDIC
- Specifies that the data is encoded using the EBCDIC CCSIDs of the server.
- UNICODE
- Specifies that the data is encoded using the Unicode CCSIDs of the server.
See the ENCODING bind option in ENCODING bind option for information about how the default for this option is determined.
- WITH EXPLAIN or WITHOUT EXPLAIN
- Specifies
whether information will be provided about how SQL statements in the
routine will execute.
- WITHOUT EXPLAIN
- Specifies that information will not be provided about how SQL
statements in the routine will execute.
You can get EXPLAIN output for a statement that is embedded in a routine that is specified using WITHOUT EXPLAIN by embedding the SQL statement EXPLAIN in the routine body. Otherwise, the value of the EXPLAIN option applies to all explainable SQL statements in the routine body, and to the fullselect portion of any DECLARE CURSOR statements.
WITHOUT EXPLAIN is the default.
- WITH EXPLAIN
- Specifies that information will be provided about how SQL statements in the routine will
execute. Information is inserted into the table owner.PLAN_TABLE.
owner is the authorization ID of the owner of the routine. Alternatively, the
authorization ID of the owner of the routine can have an alias as
owner.PLAN_TABLE that points to the base table, PLAN_TABLE.
owner must also have the appropriate SELECT and INSERT privileges on that table.
WITH EXPLAIN does not obtain information for statements that access remote objects. PLAN_TABLE must
have a base table and can have multiple aliases with the same table name, PLAN_TABLE, but have
different schema qualifiers. It cannot be a view or a synonym and should exist before the version is
added or replaced. In all inserts to owner.PLAN_TABLE, the value of QUERYNO is
the statement number that is assigned by DB2.
The WITH EXPLAIN option also populates two optional tables if they exist: DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE. DSN_STATEMNT_TABLE contains an estimate of the processing cost for an SQL statement. See DB2 Application Programming and SQL Guide for more information. DSN_FUNCTION_TABLE contains information about function resolution. See DB2 Application Programming and SQL Guide for more information.
For a description of the tables that are populated by the WITH EXPLAIN option, see EXPLAIN.
- WITH IMMEDIATE WRITE or WITHOUT IMMEDIATE WRITE
- Specifies whether immediate writes are to
be done for updates that are made to group buffer pool dependent page sets or partitions. This
option is only applicable for data sharing environments. The IMMEDWRITE subsystem parameter has no
affect of this option. IMMEDWRITE bind option shows the implied hierarchy of the
IMMEDWRITE bind option (which is similar to this routine option) as it affects run time.
- WITHOUT IMMEDIATE WRITE
- Specifies that normal write activity is performed. Updated pages that are group buffer pool
dependent are written at or before phase one of commit or at the end of abort for transactions that
have been rolled back.
WITHOUT IMMEDIATE WRITE is the default.
- WITH IMMEDIATE WRITE
- Specifies that updated pages that are group buffer pool dependent are immediately written as soon as the buffer update completes. Updated pages are written immediately even if the buffer is updated during forward progress or during the rollback of a transaction. WITH IMMEDIATE WRITE might impact performance.
- ISOLATION LEVEL RR, RS, CS, or UR
- Specifies how far to isolate the routine from the effects of other
running applications. For information about isolation levels, see Managing DB2 Performance.
- RR
- Specifies repeatable read.
- RS
- Specifies read stability.
- CS
- Specifies cursor stability. CS is the default.
- UR
- Specifies uncommitted read.
- OPTHINT string-constant
- Specifies whether query
optimization hints are used for static SQL statements that are contained within the body of the
routine.
string-constant is a character string of up to 128 bytes in length, which is used by the DB2 subsystem when searching the PLAN_TABLE for rows to use as input. The default value is an empty string, which indicates that the DB2 subsystem does not use optimization hints for static SQL statements.
Optimization hints are only used if optimization hints are enabled for your system. See DB2 Installation Guide for information about enabling optimization hints.
- SQL PATH
- Specifies
the SQL path that the DB2 subsystem
uses to resolve unqualified user-defined types, functions, and procedure
names (in CALL statements) in the body of the routine. The default
value is "SYSIBM", "SYSFUN", "SYSPROC",
and the value of the QUALIFIER option, which is the qualifier for
the trigger that is the target of the statement. The maximum length
of the SQL path is 2048 bytes. DB2 calculates
the length by taking each schema-name that
is specified and removing any trailing blanks from it, adding two
delimiters around it, and adding one comma after each schema name
except for the last name. The length of the resulting string cannot
exceed 2048 bytes.
- schema-name
- Identifies a schema. DB2 does not verify that the schema exists when the ALTER statement is processed. The same schema name should not appear more than one time in the list of schema names.
- SYSTEM PATH
- Specifies the schema names "SYSIBM", "SYSFUN", "SYSPROC".
- SESSION_USER or USER
- Specifies the value of the SESSION_USER (or USER) special register. At the time the ALTER statement is processed, the actual length is included in the total length of the list of schema names that is specified for the SQL PATH option.
- REOPT
- Specifies if DB2 will determine the access path at run time
by using the values of SQL variables or SQL parameters, parameter
markers, and special registers.
- NONE
- Specifies that DB2 does
not determine the access path at run time by using the values of SQL
variables or SQL parameters, parameter markers, and special registers.
NONE is the default.
- ALWAYS
- Specifies that DB2 always determines the access path at run time each time an SQL statement is run. Do not specify REOPT ALWAYS with the WITH KEEP DYNAMIC or NODEFER PREPARE clauses.
- ONCE
- Specifies that DB2 determine the access path for any dynamic SQL statements only once, at the first time the statement is opened. This access path is used until the prepared statement is invalidated or removed from the dynamic statement cache and need to be prepared again.
- QUERY ACCELERATION
- Specifies whether a static SQL query is bound for acceleration,
and if so, with what behavior.
- NONE
- Specifies that no static SQL query in the application is bound for acceleration or will be accelerated when the application is run.
- ENABLE
- Specifies that a static SQL query is bound for acceleration if
it satisfies the acceleration criteria, including the cost and heuristics
criteria. The query is routed to an accelerator when the application
runs. Otherwise, if the static query does not satisfy the acceleration
criteria, the query is bound for execution in DB2.
If an error condition, such as one of the following examples, occurs while executing the accelerated static query when the application is run, DB2 fails the static query and returns a negative SQL code to the application:
- A failure occurs while running the static query on the accelerator.
- The accelerator returns an error for the query.
- The accelerator is not started and DB2 cannot route the static query to the accelerator for execution.
- ENABLE WITH FAILBACK
- Results in the same behavior as ENABLE, except if one of the error conditions occurs on the first OPEN of the accelerated static query when the application is run. In this case, instead of failing the static query and returning a negative SQL code to the application, DB2 performs a temporary statement-level incremental bind of the query and runs the query in DB2. The application does not see the acceleration failure. Failback to DB2 is not possible after the application does a successful OPEN for the query on the accelerator.
- ELIGIBLE
- Specifies that a static SQL query is bound for acceleration if
the query meets the basic acceleration criteria, regardless of the
cost or heuristics criteria. The query is routed to the accelerator
when the application runs.
Like the behavior for ENABLE, if an error condition occurs while executing the accelerated static query when the application is run, DB2 fails the static query and returns a negative SQL code to the application.
- ALL
- Specifies that all of the static SQL queries in the application
are to be bound for acceleration and routed to the accelerator when
the application runs. If DB2 determines
that a static query cannot be bound to run on the accelerator and
the query references a user base table or view, the BIND or REBIND
PACKAGE operation fails with an error message for that query. (A failure
exception is made for declared global temporary tables (DGTTs) and
created global temporary tables and (CGTTs) because these tables cannot
be accelerated.)
Like the behavior for ENABLE, if an error condition occurs while executing the accelerated static query when the application is run, DB2 fails the static query and returns a negative SQL code to the application.
This bind option does not apply to a fullselect or WITH common-table-expression that is specified in a RETURN statement for the routine, or in a SET host-variable-assignment that is used in the routine. The queries that are specified in these cases cannot be accelerated.
- GET_ACCEL_ARCHIVE
- Specifies whether a static SQL query that is bound for acceleration
retrieves archived data on the accelerator, instead of active data.
- NO
- Specifies that no static SQL query is bound to retrieve archived
data from the accelerator. If the static query also is not bound for
acceleration, the query is bound to run in DB2.
If the static query is bound for acceleration because the QUERYACCELERATION bind option was specified, the query is routed to the accelerator when the application runs; however, the query does not retrieve any archived data.
- YES
- Specifies that if all of the following criteria are met, the query
is bound for acceleration and retrieves the archived data on the accelerator
when the application runs:
- The QUERYACCELERATION bind option is also specified.
- The static SQL query references an accelerated table that has partitioned data archived on an accelerator.
- The static query satisfies the acceleration criteria that is specified by the QUERYACCELERATION bind option.
If the static query does not satisfy the acceleration criteria that is specified by the QUERYACCELERATION bind option, the BIND or REBIND PACKAGE operation fails with an error message for that query.
This bind option does not apply to a fullselect or WITH common-table-expression that is specified in a RETURN statement for the routine, or in a SET host-variable-assignment that is used in the routine. The queries that are specified in these cases cannot be accelerated.
- VALIDATE RUN or VALIDATE
BIND
- Specifies whether to recheck, at
run time, errors of the type "OBJECT NOT FOUND" and "NOT AUTHORIZED"
that are found during bind or rebind. The option has no effect if
all objects and needed privileges exist.
- VALIDATE RUN
- Specifies that if needed objects or privileges do not exist when
the CREATE statement is processed, warning messages are returned,
but the CREATE statement succeeds. The DB2 subsystem
rechecks for the objects and privileges at run time for those SQL
statements that failed the checks during processing of the CREATE
statement. The authorization checks the use of the authorization ID
of the owner of the routine.
VALIDATE RUN is the default.
- VALIDATE BIND
- Specifies that if needed objects or privileges do not exist at the time the CREATE statement is processed, an error is issued and the CREATE statement fails.
- ROUNDING
- Specifies the rounding mode for manipulation
of DECFLOAT data. The default value is taken from the DEFAULT DECIMAL
FLOATING POINT ROUNDING MODE in DECP.
- DEC_ROUND_CEILING
- Specifies numbers are rounded towards positive infinity.
- DEC_ROUND_DOWN
- Specifies numbers are rounded towards 0 (truncation).
- DEC_ROUND_FLOOR
- Specifies numbers are rounded towards negative infinity.
- DEC_ROUND_HALF_DOWN
- Specifies numbers are rounded to nearest; if equidistant, round down.
- DEC_ROUND_HALF_EVEN
- Specifies numbers are rounded to nearest; if equidistant, round so that the final digit is even.
- DEC_ROUND_HALF_UP
- Specifies numbers are rounded to nearest; if equidistant, round up.
- DEC_ROUND_UP
- Specifies numbers are rounded away from 0.
- DATE FORMAT ISO, EUR, USA, JIS,
or LOCAL
- Specifies the date format
for result values that are string representations of date or time
values. See String representations of datetime values for
more information.
The default format is specified in the DATE FORMAT field of installation panel DSNTIP4 of the system where the routine is defined. You cannot use the LOCAL option unless you have a date exit routine.
- DECIMAL(15), DECIMAL(31), DECIMAL(15,s),
or DECIMAL(31,s)
- Specifies the maximum precision that
is to be used for decimal arithmetic operations. See Arithmetic with two decimal operands for
more information. The default format is specified in the DECIMAL ARITHMETIC
field of installation panel DSNTIPF of the system where the routine
is defined. If the form pp.s is
specified, s must be a number between 1
and 9. s represents the minimum scale that
is to be used for division.
- FOR UPDATE CLAUSE OPTIONAL or FOR UPDATE CLAUSE REQUIRED
- Specifies whether the FOR UPDATE clause is
required for a DECLARE CURSOR statement if the cursor is to be used to perform positioned updates.
- FOR UPDATE CLAUSE REQUIRED
- Specifies that a FOR UPDATE clause must be specified as part of the cursor definition if the
cursor will be used to make positioned updates.
FOR UPDATE CLAUSE REQUIRED is the default.
- FOR UPDATE CLAUSE OPTIONAL
- Specifies that the FOR UPDATE clause does not need to be specified in order for a cursor to be used for positioned updates. The routine body can include positioned UPDATE statements that update columns that the user is authorized to update.
- TIME FORMAT ISO, EUR, USA, JIS,
or LOCAL
- Specifies the time format
for result values that are string representations of date or time
values. See String representations of datetime values for
more information.
The default format is specified in the TIME FORMAT field of installation panel DSNTIP4 of the system where the routine is defined. You cannot use the LOCAL option unless you have a date exit routine.
- SECURED or NOT SECURED
- Specifies if the function is considered secure. When the option
is specified with the ALL VERSIONS clause,
it applies to all existing versions and to any future versions of
the function. When it is specified with other clauses such as ADD
VERSION, or REPLACE, the value
must be the same as the value that is in effect for the function that
is being changed.
- SECURED
- Specifies that the function is considered secure.
- NOT SECURED
- Specifies that the function is considered not secure. NOT SECURED
must not be specified when a row permission or a column mask depends
on the function.
When the function is invoked, the arguments of the function must not reference a column for which a column mask is enabled when the table is using active column access control.
- SQL-routine-body
- Specifies
a single SQL control statement, including a compound-statement.
See SQL control statements for SQL routines for more information
about defining SQL functions.
A call to a procedure that issues a COMMIT, ROLLBACK, CONNECT, RELEASE, or SET CONNECTION statement is not allowed in a function.
If the SQL-routine-body is a compound statement, it must contain at least one RETURN statement and a RETURN statement must be executed when the function is invoked.
An ALTER FUNCTION (compiled SQL scalar) statement or an ALTER PROCEDURE (SQL native) statement with an ADD VERSION or REPLACE clause is not allowed in an SQL-routine-body.
Notes
- The order of options and the RETURNS clause
- The RETURNS clause must precede the options-list.
If the body of the function contains only a RETURN-statement, RETURN-statement must
be specified after the RETURNS clause and the options-list in
the routine body.
- ALTER FUNCTION for in use functions:
- ALTER FUNCTION will be locked out from making changes if the function
is in use. For example, if a query that is currently running is referencing
an SQL scalar function named 'fn1' (routine-version-id
is 'v1'), an ALTER FUNCTION fn1 ACTIVATE VERSION v2 statement will
wait for the query that is currently running to complete before making
'v2' the active version for function 'fn1'. This wait for completion
behavior happens even if the query invokes the function multiple times
for processing multiple rows or if the query contains multiple references
to the function that is being changed.
- Considerations for changing a version of a function:
- To change a version of a function, the environment settings that
are in effect when the ALTER FUNCTION statement is issued must be
the same as the environment settings that are in effect when the version
of the function is first created using the CREATE FUNCTION or ALTER
FUNCTION statement if one of the following options is specified:
- QUALIFIER
- PACKAGE OWNER
- WLM ENVIRONMENT FOR DEBUG MODE
- OPTHINT
- SQL PATH
- DECIMAL (if the value includes a comma)
- Considerations for catalog comments for a routine definition:
- When a function definition is replaced, any existing comment in
the catalog for the definition is removed. However, when a function
definition is regenerated, any existing comment in the catalog for
the definition is retained.
- Identifier resolution:
- See SQL control statements for SQL routines for
information on how names are resolved to columns, SQL variables, or
SQL parameters within an SQL routine.
If duplicate names are used for columns and SQL variables and parameters, qualify the duplicate names by using the table designator for columns, the routine name for parameters, and the label name for SQL variables.
- Characteristics of the package that is generated for a function:
- The package that is associated with a version of a function is
named as follows:
- location is set to the value of the CURRENT SERVER special register
- collection-id (schema) for the package is the same as the schema qualifier of the function
- package-id is the same as the specific name of the function
- version-id is the same as the version identifier for the version of the function
The package is generated using the bind options that correspond to the implicitly or explicitly specified function options. In addition to the corresponding bind options, the package is generated using the following bind options:- FLAG(I)
- SQLERROR(NOPACKAGE)
- ENABLE(*)
- Correspondence of function options to bind command options:
- The following table lists options for CREATE FUNCTION and ALTER
FUNCTION and the corresponding bind command option. See BIND and REBIND options for packages and plans for information about the
BIND command options.
Table 1. Correspondence of function options to bind options CREATE FUNCTION or ALTER FUNCTION option bind command option APPLICATION ENCODING SCHEME ENCODING(ASCII), ENCODING(EBCDIC), ENCODING(UNICODE) CURRENT DATA NO CURRENTDATA(NO) CURRENT DATA YES CURRENTDATA(YES) DYNAMICRULES DYNAMICRULES(RUN), DYNAMICRULES(BIND), DYNAMICRULES(DEFINEBIND), DYNAMICRULES(DEFINERUN), DYNAMICRULES(INVOKEBIND), DYNAMICRULES(INVOKERUN) ISOLATION LEVEL ISOLATION(RR), ISOLATION(RS), ISOLATION(CS), ISOLATION(UR) OPTHINT OPTHINT PACKAGE OWNER OWNER QUALIFIER QUALIFIER REOPT ALWAYS REOPT(ALWAYS) REOPT NONE REOPT(NONE) REOPT ONCE REOPT(ONCE) ROUNDING DEC_ROUND_CEILING ROUNDING(CEILING) ROUNDING DEC_ROUND_DOWN ROUNDING(DOWN) ROUNDING DEC_ROUND_FLOOR ROUNDING(FLOOR) ROUNDING DEC_ROUND_HALF_DOWN ROUNDING(HALFDOWN) ROUNDING DEC_ROUND_HALF_EVEN ROUNDING(HALFEVEN) ROUNDING DEC_ROUND_HALF_UP ROUNDING(HALFUP) ROUNDING DEC_ROUND_UP ROUNDING(UP) SQL PATH PATH VALIDATE BIND VALIDATE(BIND) VALIDATE RUN VALIDATE(RUN) WITH EXPLAIN EXPLAIN(YES) WITHOUT EXPLAIN EXPLAIN(NO) WITH IMMEDIATE WRITE IMMEDWRITE(YES) WITHOUT IMMEDIATE WRITE IMMEDWRITE(NO) - Invalidation of packages:
- When a version of an SQL function is altered to change any option
that is specified for the active version, all packages that refer
to that function are marked invalid. In addition, when certain attributes
of an SQL function are changed, the body of the function might be
rebound or regenerated. The following table summarizes when implicit
rebind and regeneration occurs when specific options are changed.
A value of 'Y' in a row indicates that a rebind or regeneration occurs
if the option is changed for a version of the function. A value of
'N' in a row indicates that a rebind or regeneration does not occur.
Table 2. CREATE FUNCTION and ALTER FUNCTION options that result in rebind or regeneration of the function when changed CREATE FUNCTION or ALTER FUNCTION option Change requires rebind of invoking application Change results in implicit rebind of non-control statements in the body of the function Change results in implicit regeneration of the entire body of the function ALLOW DEBUG MODE, DISALLOW DEBUG MODE, or DISABLE DEBUG MODE Y 1 2 Y 1 Y APPLICATION ENCODING SCHEME Y Y Y ASUTIME Y N N CURRENT DATA N Y N DATE FORMAT Y Y Y DECIMAL Y Y Y DYNAMICRULES N Y N FOR UPDATE CLAUSE OPTIONAL or FOR UPDATE CLAUSE REQUIRED Y Y Y INHERIT SPECIAL REGISTERS or DEFAULT SPECIAL REGISTERS Y N N ISOLATION LEVEL N Y N MODIFIES SQL DATA, READS SQL DATA, or CONTAINS SQL Y Y Y NOT DETERMINISTIC or DETERMINISTIC N N N OPTHINT N Y N PACKAGE OWNER N Y N QUALIFIER N Y N REOPT N Y N ROUNDING Y Y Y SQL PATH N Y N TIME FORMAT Y Y Y VALIDATE RUN or VALIDATE BIND N Y N WITH EXPLAIN or WITHOUT EXPLAIN N Y N WITH IMMEDIATE WRITE or WITHOUT IMMEDIATE WRITE N Y N WLM ENVIRONMENT FOR DEBUG MODE Y N N Note:- 1 The function package is rebound or regenerated if a value of ALLOW DEBUG MODE is changed to DISALLOW DEBUG MODE
- 2 Invoking applications are invalidated if a value of DISALLOW DEBUG MODE is changed to DISABLE DEBUG MODE
- Considerations for SQL processor programs:
- SQL processor programs (such as SPUFI, the command line processor, and DSNTEP2) might not correctly parse SQL statements in the routine body that are ended with semicolons. These processor programs accept multiple SQL statements as input when each statement is separated with a terminator character. Processor programs that use a semicolon as the SQL statement terminator might truncate an ALTER FUNCTION statement with embedded semicolons and pass only a portion of the statement to DB2. Therefore, you might need to change to SQL terminator character for these processor programs.
- Considerations for the SYSENVIRONMENTS catalog table:
- An ALTER statement that specifies new environment settings will
result in a new row being added to the SYSENVIRONMENTS catalog table.
The new row will be added even if an error is subsequently encountered
during processing of the statement. Thus, a new SYSENVIRONMENT row
might be added to the table even for an ALTER statement that fails.
- Dependent objects:
- An SQL routine is dependent on objects that are referenced in
the routine body.
- Altering a function from NOT SECURED to SECURED:
- Typically, the security administrator will examine the data that
is accessed by a function, ensure that it is secure, and grant the
CREATE_SECURE_OBJECT privilege to the user that requires privileges
to change the user-defined function to be secured. After the function
is changed to SECURED, the security administrator will revoke the
CREATE_SECURE_OBJECT privilege from the owner of the function.
The function is considered secure after the ALTER FUNCTION statement is executed. DB2 treats the SECURED attribute as an assertion that declares that the security administrator has established an audit procedure for all changes to the user-defined function. DB2 assumes that such a control audit procedure is in place for all subsequent ALTER FUNCTION statements or changes to external packages.
Packages and statements in the dynamic statement cache that reference the function are invalidated.
- Altering a function from SECURED to NOT SECURED:
- Packages and statements in the dynamic statement cache that reference
the function are invalidated when the function is changed from SECURED
to NOT SECURED. An function that is not secured might negatively impact
performance if that function accesses data in a table that is using
row access control or column access control. To minimize the performance
impact, either change the function to use the SECURED option or deactivate
row access control or column access control for the table that the
function is accessing.
- Invoking other user-defined functions in a secure function:
- When a secure user-defined function is referenced in an SQL data
change statement that references a table that is using row access
control or column access control, and if the secure user-defined function
invokes other user-defined functions, the nested user-defined functions
are not validated as secure. If those nested functions can access
sensitive data, the security administrator needs to ensure that those
functions are allowed to access sensitive data and should ensure that
a change control audit procedure has been established for all changes
to those functions.
- The SECURE column in the DSN_FUNCTION_TABLE EXPLAIN table:
- The SECURE column in the DSN_FUNCTION_TABLE EXPLAIN table indicates
if a user-defined function is considered secure.
- Deploying a compiled SQL function:
- When a BIND DEPLOY command is issued to deploy a compiled SQL
function to a target location, the SECURED and NOT SECURED options
are included in the deployment process.
When deploying a compiled SQL function, if a function with the same target name does not exist at the target location, the deployed function is created as a new function at the target location with the same SECURED or NOT SECURED option that is specified (or the default of NOT SECURED is used) in the source function of the deployment.
When deploying a compiled SQL function, if a function with the same target name already exists at the target location, the deployed function is either added as a new version of the function or is used to replace an existing version of the function. The SECURED or NOT SECURED option of the deployed function must be the same as that of the existing function at the target location
- Compatibilities:
- For compatibility with the CREATE FUNCTION (SQL scalar) statement,
the following clause can be specified, but will be ignored:
- LANGUAGE SQL
- Optional syntax:
- To provide compatibility with the syntax of the CREATE FUNCTION
statement, the following options can also be specified:
- SPECIFIC
- PARAMETER CCSID
However, if these options are specified, the value for the option must be the same as the value that is already in effect for the function.
- Alternative syntax and synonyms:
- To provide compatibility with previous releases of DB2 or other
products in the DB2 family, DB2 supports
the following keywords:
- VARIANT as a synonym for NOT DETERMINISTIC
- NOT VARIANT as a synonym for DETERMINISTIC
- NULL CALL as a synonym for CALLED ON NULL INPUT
- NOT NULL CALL as a synonym for RETURNS
NULL ON NULL INPUT
- TIMEZONE
can be specified as an alternative to TIME ZONE
Examples
ALTER FUNCTION MY_UDF1
DETERMINISTIC; ALTER FUNCTION REVERSE
ALTER ACTIVE VERSION
NOT DETERMINISTIC
ALLOW DEBUG MODE; ALTER FUNCTION REVERSE(INSTR VARCHAR(4000))
REPLACE VERSION V2 (INSTR VARCHAR(4000))
RETURNS VARCHAR(4000)
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN
DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT '';
DECLARE LEN INT;
IF INSTR IS NULL THEN
RETURN NULL;
END IF;
SET RESTSTR = INSTR;
SET LEN = LENGTH(INSTR);
WHILE LEN > 0 DO
SET (REVSTR, RESTSTR, LEN) = (SUBSTR(RESTSTR, 1, 1) CONCAT
REVSTR, SUBSTR(RESTSTR, 2, LEN - 1), LEN - 1);
END WHILE;
RETURN REVSTR;
END ALTER FUNCTION REVERSE(INSTR VARCHAR(4000))
ADD VERSION V3 (INSTR VARCHAR(4000))
RETURNS VARCHAR(4000)
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN
DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT '';
DECLARE LEN INT;
IF INSTR IS NULL THEN
RETURN NULL;
END IF;
SET (RESRSTR, LEN) = (INSTR, LENGTH(INSTR));
WHILE LEN > 0 DO
SET (REVSTR, RESTSTR, LEN) = (SUBSTR(RESTSTR, 1, 1) CONCAT
REVSTR, SUBSTR(RESTSTR, 2, LEN - 1), LEN - 1);
END WHILE;
RETURN REVSTR;
END ALTER FUNCTION REVERSE(INSTR VARCHAR(4000))
ACTIVATE VERSION V3; ALTER FUNCTION REVERSE(INSTR VARCHAR(4000))
REGENERATE ACTIVE VERSION;