版次注意事項
|Update the partitioning key is now supported. The following text from various
|statements in Chapter 6 should be deleted only
|if the DB2_UPDATE_PART_KEY=ON:
- |Note:
- If DB2_UPDATE_PART_KEY=OFF,
|then the restrictions still apply.
|
|Rules
|
- |A partitioning key column of a table cannot be updated (SQLSTATE 42997).
- |A nullable column of a partitioning key cannot be included as a foreign
|key column when the relationship is defined with ON DELETE SET NULL (SQLSTATE
|42997).
|
|Rules
|
- |A partitioning key column of a table cannot be updated (SQLSTATE 42997).
- |A nullable column of a partitioning key cannot be included as a foreign
|key column when the relationship is defined with ON DELETE SET NULL (SQLSTATE
|42997).
|
- |Note:
- The partitioning key columns cannot be updated (SQLSTATE 42997).
|
|Footnotes
|
- |108 A column of a partitioning key is not updatable (SQLSTATE 42997).
|The row of data must be deleted and inserted to change columns in a partitioning
|key.
|
|The length of variable length columns that are part of any index, including
|primary and unique keys, defined when the registry variable DB2_INDEX_2BYTEVARLEN
|was on, can be altered to a length greater than 255 bytes. The fact that a
|variable length column is involved in a foreign key will no longer prevent
|the length of that column from being altered to larger than 255 bytes, regardless
|of the registry variable setting. However, data with length greater than 255
|cannot be inserted into the table unless the column in the corresponding primary
|key has length greater than 255 bytes, which is only possible if the primary
|key was created with the registry variable ON.
|Indexes can be defined on variable length columns whose length is greater
|than 255 bytes if the registry variable DB2_INDEX_2BYTEVARLEN is ON.
|Primary and unique keys with variable keyparts can have a size greater
|than 255 if the registry variable DB2_INDEX_2BYTEVARLEN is ON. Foreign keys
|can be defined on variable length columns whose length is greater than 255
|bytes.
|
|
|The ALTER SEQUENCE statement modifies the attributes of a sequence by:
|
- |Restarting the sequence
- |Changing the increment between future sequence values
- |Setting new minimum or maximum values
- |Changing the number of cached sequence numbers
- |Changing whether the sequence can cycle or not
- |Changing whether sequence numbers must be generated in order of request
|
|Invocation
|This statement can be embedded in an application program or issued through
|the use of dynamic SQL statements. It is an executable statement that can
|be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies,
|the statement cannot be dynamically prepared (SQLSTATE 42509).
|Authorization
|The privileges held by the authorization ID of the statement must include
|at least one of the following:
|
- |Definer of the sequence
- |The ALTERIN privilege for the schema implicitly or explicitly specified
- |SYSADM or DBADM authority
|
|Syntax
|>>-ALTER SEQUENCE--sequence-name-------------------------------->
|
| .-------------------------------------------.
| V |
|>-------+-RESTART--+-------------------------+-+--+------------><
| | '-WITH--numeric-constant--' |
| +-INCREMENT BY--numeric-constant-------+
| +-+-MINVALUE--numeric-constant--+------+
| | '-NO MINVALUE-----------------' |
| +-+-MAXVALUE--numeric-constant--+------+
| | '-NO MAXVALUE-----------------' |
| +-+-CYCLE----+-------------------------+
| | '-NO CYCLE-' |
| +-+-CACHE--integer-constant--+---------+
| | '-NO CACHE-----------------' |
| '-+-ORDER----+-------------------------'
| '-NO ORDER-'
|
|Descritpion
|
- |sequence-name
- |Identifies the particular sequence. The combination of name, and the
|implicit or explicit schema name must identify an existing sequence at the
|current server. If no sequence by this name exists in the explicitly or implicitly
|specified schema, an error (SQLSTATE 42704) is issued.
- |RESTART
- |Restarts the sequence. If numeric-constant is not specified,
|the sequence is restarted at the value specified implicitly or explicitly
|as the starting value on the CREATE SEQUENCE statement that originally created
|the sequence.
|
- |WITH numeric-constant
- |Restarts the sequence with the specified value. This value can be any
|positive or negative value that could be assigned to a column of the data
|type associated with the sequence (SQLSTATE 42820) as long as there are no
|non-zero digits to the right of the decimal point (SQLSTATE 42894).
|
- |INCREMENT BY
- |Specifies the interval between consecutive values of the sequence.
|This value can be any positive or negative value that could be assigned
|to a column of the data type associated with the sequence (SQLSTATE 42820),
|and does not exceed the value of a large integer constant (SQLSTATE 42815),
|without non-zero digits existing to the right of the decimal point (SQLSTATE
|428FA).
|If this value is negative, then the sequence of values
|descends. If this value is positive, then the sequence of values ascends.
|If this value is 0 or greater than the range defined by MINVALUE and
|MAXVALUE, only one value will be generated, but the sequence is treated as
|an ascending sequence otherwise.
- |MINVALUE or NO MINVALUE
- |Specifies the minimum value at which a descending sequence either cycles
|or stops generating values, or an ascending sequence cycles to after
|reaching the maximum value.
|
- |MINVALUE numeric-constant
- |Specifies the numeric constant that is the minimum value. This value
|can be any positive or negative value that could be assigned to a
|column of the data type associated with the sequence (SQLSTATE 42820),
|without non-zero digits existing to the right of the decimal point
|(SQLSTATE 428FA), but the value must be less than or equal to the
|maximum value (SQLSTATE 42815).
- |NO MINVALUE
- |For an ascending sequence, the value is the START WITH value, or 1
|if START WITH is not specified. For a descending sequence, the value
|is the minimum value of the data type associated with the sequence.
|This is the default.
|
- |MAXVALUE or NO MAXVALUE
- |Specifies the maximum value at which an ascending sequence either
|cycles or stops generating values, or a descending sequence cycles to after
|reaching the minimum value.
|
- |MAXVALUE numeric-constant
- |Specifies the numeric constant that is the maximum value. This value
|can be any positive or negative value that could be assigned to a
|column of the data type associated with the sequence (SQLSTATE 428FA),
|without non-zero digits existing to the right of the decimal point
|(SQLSTATE 428FA), but the value must be greater than or equal to the
|minimum value (SQLSTATE 42815).
- |NO MAXVALUE
- |For an ascending sequence, the value is the maximum value of the
|data type associated with the sequence. For a descending sequence, the
|value is the START WITH value, or -1 if START WITH is not specified.
|This is the default.
|
- |CYCLE or NOCYCLE
- |Specifies whether the sequence should continue to generate values after
|reaching either its maximum or minimum value. The boundary of the sequence
|can be reached either with the next value landing exactly on the boundary
|condition, or by overshooting it in which case the next value would be
|determined from wrapping around to the START WITH value if cycles were
|permitted.
|
- |CYCLE
- |Specifies that values continue to be generated for this sequence
|after the maximum or minimum value has been reached. If this option is
|used, after an ascending sequence reaches its maximum value, it generates
|its minimum value; or after a descending sequence reaches its minimum
|value, it generates its maximum value. The maximum and minimum values
|for the sequence determine the range that is used for cycling.
|When CYCLE is in effect, then duplicate values can be generated for
|the sequence.
- |NO CYCLE
- |Specifies that values will not be generated for the sequence once
|the maximum or minimum value for the sequence has been reached. This
|is the default.
|
- |CACHE or NO CACHE
- |Specifies whether to keep some preallocated values in memory for faster
|access. This is a performance and tuning option.
|
- |CACHE integer-constant
- |Specifies the maximum number of sequence values that are preallocated
|and kept in memory. Preallocating and storing values in the cache reduces
|synchronous I/O to the log when values are generated for the sequence.
|In the event of a system failure, all cached sequence values that
|have not been used in committed statements are lost (that is, they
|will never be used). The value specified for the CACHE option is
|the maximum number of sequence values that could be lost in case
|of system failure.
|The minimum value is 2 (SQLSTATE
|42815). The default value is CACHE 20.
- |NO CACHE
- |Specifies that values of the sequence are not to be preallocated. It
|ensures that there is not a loss of values in the case of a system
|failure, shutdown or database deactivation. When this option is specified,
|the values of the sequence are not stored in the cache. In this case,
|every request for a new value for the sequence results in synchronous
|I/O to the log.
|
- |NO ORDER or ORDER
- |Specifies whether the sequence numbers must be generated in order of
|request.
|
- |ORDER
- |Specifies that the sequence numbers are generated in order of
|request.
- |NO ORDER
- |Specifies that the sequence numbers do not need to be generated in
|order of request. This is the default.
|
|
|After restarting a sequence or changing to CYCLE, it is possible for sequence
|numbers to be duplicate values of ones generated by the sequence previously.
|Version
|
- |Only future sequence numbers are affected by the ALTER SEQUENCE statement.
- |The data type of a sequence cannot be changed. Instead, drop and recreate
|the sequence specifying the desired data type for the new sequence.
- |All cached values are lost when a sequence is altered.
|
|Examples
|Example 1: A possible reason for specifying RESTART
|without a numeric value would be to reset the sequence to the START WITH value.
|In this example, the goal is to generate the numbers from 1 up to the number
|of rows in the table and then inserting the numbers into a column added to
|the table using temporary tables. Another use would be to get results back
|where all the resulting rows are numbered:
| ALTER SEQUENCE org_seq
| RESTART
| SELECT NEXTVAL for org_seq, org.*
| FROM org
|
|Changes to syntax fragments:
|
|column-alteration
|
||--column-name-------------------------------------------------->
|
|>-----+-SET--+-DATA TYPE--+-VARCHAR-----------+---(--integer--)--+-------+>
| | | +-CHARACTER VARYING-+ | |
| | | '-CHAR VARYING------' | |
| | '-EXPRESSION AS--(--generation-expression--)--------' |
| +-ADD SCOPE--+-typed-table-name-+----------------------------------+
| | '-typed-view-name--' |
| '-+-| identity-alteration |--------------------------------------+-'
| '-SET GENERATED--+-ALWAYS-----+---+--------------------------+-'
| '-BY DEFAULT-' '-| identity-alteration |--'
|
|>---------------------------------------------------------------|
|
|identity-alteration
|
||---+-RESTART--+--------------------------+-+-------------------|
| | '-WITH--numeric-constant---' |
| +-SET INCREMENT BY--numeric-constant----+
| | (1) |
| +-SET--+-NO MINVALUE-----------------+--+
| | '-MINVALUE--numeric-constant--' |
| +-SET--+-NO MAXVALUE-----------------+--+
| | '-MAXVALUE--numeric-constant--' |
| +-SET--+-CYCLE----+---------------------+
| | '-NO CYCLE-' |
| +-SET--+-NO CACHE-----------------+-----+
| | '-CACHE--integer-constant--' |
| '-SET--+-NO ORDER-+---------------------'
| '-ORDER----'
|
|Note:
- |These parameters can be specified without spaces: NOMINVALUE, NOMAXVALUE,
|NOCYCLE, NOCACHE, and NOORDER. These single word versions are all acceptable
|alternatives to the two word versions.
|
|Add the following parameters:
|
- |SET GENERATED
- |Specifies whether values are to be generated for the column always or
|only when a default value is needed.
|
- |ALWAYS
- |A value will always be generated for the column when a row is inserted
|or updated in the table. The column must already be defined as a generated
|column (SQLSTATE 42837).
- |BY DEFAULT
- |The value will be generated for the column when a row is inserted
|into the table, unless a value is specified. The column must already be
|defined as a generated column (SQLSTATE 42837).
- |RESTART or RESTART WITH numeric-constant
- |Resets the state of the sequence associated with the identity column.
|If WITH numeric-constant is not specified, then the sequence
|for the identity column is restarted at the value that was specified,
|either implicitly or explicitly, as the starting value when the identity
|column was originally created. The numeric-constant is an exact
|numeric constant that can be any positive or negative value that could
|be assigned to this column (SQLSTATE 42820) as long as there are no non-zero
|digits to the right of the decimal point (SQLSTATE 42894). The column
|must already be defined with the IDENTITY attribute (SQLSTATE 42837).
|The numeric-constant will be used as the next value for the column.
- |SET INCREMENT BY numeric-constant
- |Specifies the interval between consecutive values of the identity column.
|The column must already be defined with the IDENTITY attribute (SQLSTATE
|42837). This value is any positive or negative value that could be assigned
|to this column (SQLSTATE 42820), and does not exceed the value of a large
|integer constant (SQLSTATE 42815), as long as there are no non-zero digits
|to the right of the decimal point (SQLSTATE 42894).
|If this value
|is negative, then the sequence of values for this identity column descends.
|If this value is positive, then the sequence of values for this identity
|column ascends. If this value is 0, or is greater than the range defined
|by MINVALUE and MAXVALUE, then DB2 will only generate one value, but
|the sequence is treated as an ascending sequence otherwise.
- |SET MINVALUE numeric-constant or NO MINVALUE
- |Specifies the minimum value at which a descending identity column either
|cycles or stops generating values, or the value to which an ascending identity
|column cycles to after reaching the maximum value. The column must already
|be defined with the IDENTITY attribute (SQLSTATE 42837).
|
- |MINVALUE numeric-constant
- |Specifies the minimum numeric constant value . This value can be
|any positive or negative value that could be assigned to this column (SQLSTATE
|42820), without non-zero digits existing to the right of the decimal
|point (SQLSTATE 42894), but the value must be less than the maximum
|value (SQLSTATE 42815).
- |NO MINVALUE
- |For an ascending sequence, the value is the START WITH value, or 1 if
|START WITH is not specified. For a descending sequence, the value is
|the minimum value of the data type of the column.
|
- |SET MAXVALUE numeric-constant or NO MAXVALUE
- |Specifies the maximum value at which an ascending identity column either
|cycles or stops generating values, or the value to which a descending
|identity column cycles to after reaching the minimum value. The column
|must already be defined with the IDENTITY attribute (SQLSTATE 42837).
|
- |MAXVALUE numeric-constant
- |Specifies the numeric constant that is the maximum value. This value
|can be any positive or negative value that could be assigned to this
|column (SQLSTATE 42820), without non-zero digits existing to the right
|of the decimal point (SQLSTATE 42894), but the value must be greater
|than the minimum value (SQLSTATE 42815).
- |NO MAXVALUE
- |For an ascending sequence, the value is the maximum value of the data
|type of the column. For a descending sequence, the value is the START
|WITH value, or -1 if START WITH is not specified.
|
- |SET CYCLE or NO CYCLE
- |Specifies whether this identity column should continue to generate values
|after generating either the maximum or minimum value. The column must
|already be defined with the IDENTITY attribute (SQLSTATE 42837).
|
- |CYCLE
- |Specifies that values continue to be generated for this column after
|the maximum or minimum value has been reached. If this option is used,
|then after an ascending identity column reaches the maximum value,
|it generates its minimum value; or after a descending sequence reaches
|the minimum value, it generates its maximum value. The maximum and
|minimum values for the identity column determine the range that is
|used for cycling.
|When CYCLE is in effect, then duplicate values
|can be generated for an identity column. Although not required, if
|unique values are desired, a single-column unique index defined using
|the identity column will ensure uniqueness. If a unique index exists
|on such an identity column and a non-unique value is generated, then
|an error occurs (SQLSTATE 23505).
- |NO CYCLE
- |Specifies that values will not be generated for the identity column
|once the maximum or minimum value has been reached.
|
- |SET CACHE integer-constant or NO CACHE
- |Specifies whether to keep some preallocated values in memory for faster
|access. This is a performance and tuning option. The column must already
|be defined with the IDENTITY attribute (SQLSTATE 42837).
|
- |CACHE integer-constant
- |Specifies how many values of the identity sequence are preallocated
|and kept in memory. When values are generated for the identity column,
|preallocating and storing values in the cache reduces synchronous I/O
|to the log.
|If a new value is needed for the identity column and
|there are no unused values available in the cache, then the allocation
|of the value requires waiting for I/O to the log. However, when a
|new value is needed for the identity column and there is an unused
|value in the cache, the allocation of that identity value can happen
|more quickly by avoiding the I/O to the log.
|When
|a database manager is stopped (database deactivation, system failure, or
|shutdown, for example), all cached sequence values that have not been
|used in committed statements are lost (that is, they will never be
|used). The value specified for the CACHE option is the maximum number
|of values for the identity column that could be lost in case of system
|failure.
|The minimum value is 2 (SQLSTATE 42615).
- |NO CACHE
- |Specifies that values for the identity column are not to be preallocated.
|When this option is specified, the values of the identity column
|are not stored in the cache. In this case, every request for a new
|identity value results in synchronous I/O to the log.
|
- |SET ORDER or NO ORDER
- |Specifies whether the identity column values must be generated in order
|of request. The column must already be defined with the IDENTITY attribute
|(SQLSTATE 42837).
|
- |ORDER
- |Specifies that the identity column values are generated in order of
|request.
- |NO ORDER
- |Specifies that the identity column values do not need to be generated
|in order of request.
|
|
|
|A prepared COMMIT statement is not allowed in an ATOMIC compound SQL statement.
|
|
|
|
|
|
|
|A compound statement groups other statements together into an executable
|block. You can declare SQL variables within a dynamically prepared atomic
|compound statement.
|Invocation
|This statement can be embedded in a trigger, SQL Function, or SQL Method,
|or issued through the use of dynamic SQL statements. It is an executable
|statement that can be dynamically prepared.
|Authorization
|No privileges are required to invoke a dynamic compound statement. However,
|the authorization ID of the compound statement must hold the necessary privileges
|to invoke the SQL statements embedded in the compound statement.
|Syntax
|dynamic-compound-statement
|
|>>-+--------------+--BEGIN ATOMIC------------------------------->
| | (1) |
| '-label:-------'
|
|>-----+-----------------------------------------------+--------->
| | .-----------------------------------------. |
| | V | |
| '-----+-| SQL-variable-declaration |-+---;---+--'
| '-| condition-declaration |----'
|
| .-,-----------------------------.
| V |
|>--------SQL-procedure-statement--;---+---END--+--------+------><
| '-label--'
|
|SQL-variable-declaration
|
| .-,--------------------.
| V |
||---DECLARE-------SQL-variable-name---+--data-type-------------->
|
| .-DEFAULT NULL-------------.
|>-----+--------------------------+------------------------------|
| '-DEFAULT--default-values--'
|
|condition-declaration
|
||---DECLARE--condition-name--CONDITION--FOR--------------------->
|
| .-VALUE-.
| .-SQLSTATE--+-------+---.
|>----+-----------------------+---string-constant----------------|
|
|Note:
- |A label can only be specified when the statement is in a function,
|method, or trigger definition.
|
|Description
|
- |label
- |Defines the label for the code block. If the beginning label is specified,
|it can be used to qualify SQL variables declared in the dynamic compound
|statement and can also be specified on a LEAVE statement. If the ending
|label is specified, it must be the same as the beginning label.
- |ATOMIC
- |ATOMIC indicates that, if an error occurs in the compound statement,
|all SQL statements in the compound statement will be rolled back and any
|remaining SQL statements in the compound statement are not processed.
- |SQL-procedure-statement
- |The following list of SQL-control-statements can be used within
|the dynamic compound statement:
|
- |FOR Statement
- |GET DIAGNOSTICS Statement
- |IF Statement
- |ITERATE Statement
- |LEAVE Statement
- |SIGNAL Statement
- |WHILE Statement
|
|The SQL statements that can be issued are:
|
- |fullselect
|6
- |Searched UPDATE
- |Searched DELETE
- |INSERT
- |SET variable statement
|
- |SQL-variable-declaration
- |Declares a variable that is local to the dynamic compound statement.
|
- |SQL-variable-name
- |Defines the name of a local variable. DB2 converts all SQL variable
|names to uppercase. The name cannot:
|
- |Be the same as another SQL variable within the same compound statement.
- |Be the same as a parameter name.
- |Be the same as column names.
|
|If an SQL statement contains an identifier with the same name
|as an SQL variable and a column reference, DB2 interprets the identifier
|as a column.
- |data-type
- |Specifies the data type of the variable.
- |DEFAULT default-values or NULL
- |Defines the default for the SQL variable. The variable is initialized
|when the dynamic compound statement is called. If a default value is
|not specified, the variable is initialized to NULL.
|
- |condition-declaration
- |Declares a condition name and corresponding SQLSTATE value.
|
- |condition-name
- |Specifies the name of the condition. The condition name must
|be unique within the procedure body and can be referenced only within
|the compound statement in which it is declared.
- |FOR SQLSTATE string-constant
- |Specifies the SQLSTATE associated with the condition. The string-constant must be specified as five characters enclosed
|in single quotes, and cannot be '00000'.
|
|
|Version
|
- |Dynamic compound statements are compiled by DB2 as one single statement.
|This statement is effective for short scripts involving little control
|flow logic but significant data flow. For larger constructs with nested
|complex control flow, a better choice is to use SQL procedures for details
|on using SQL procedures.
|
|The syntax diagram changes to the following
|>>-CREATE FUNCTION--function-name------------------------------->
|
|>----(--+------------------------------------------+---)---*---->
| | .-,----------------------------------. |
| | V | |
| '----+-----------------+---data-type1---+--'
| '-parameter-name--'
|
|>----RETURNS--data-type2---*----+--------------------------+---->
| '-SPECIFIC--specific-name--'
|
|>----*---------------------------------------------------------->
|
|>-----+-SOURCE--+-function-name--------------------------------+------------------+>
| | +-SPECIFIC--specific-name----------------------+ |
| | '-function-name--(--+-------------------+---)--' |
| | | .-,-----------. | |
| | | V | | |
| | '----data-type---+--' |
| | .-NOT DETERMINISTIC--. .-EXTERNAL ACTION----. |
| '-AS TEMPLATE--*----+--------------------+--*----+--------------------+--*--'
| '-DETERMINISTIC------' '-NO EXTERNAL ACTION-'
|
|>----*---------------------------------------------------------><
|
|Add the following to the "Description" section:
|
- |DETERMINISTIC or NOT DETERMINISTIC
- |This optional clause specifies whether the function always
|returns the same results for given argument values (DETERMINISTIC) or whether
|the function depends on some state values that affect the results (NOT DETERMINISTIC).
|That is, a DETERMINISTIC function must always return the same table from successive
|invocations with identical inputs. Optimizations taking advantage of the
|fact that identical inputs always produce the same results are prevented by
|specifying NOT DETERMINISTIC.
|NOT DETERMINISTIC must be explicitly or implicitly
|specified if the body of the function accesses a special register or calls
|another non-deterministic function (SQLSTATE 428C2).
- |NO EXTERNAL ACTION or EXTERNAL ACTION
- |This optional clause specifies whether or not the function takes some
|action that changes the state of an object not managed by the database manager.
|By specifying NO EXTERNAL ACTION, the system can use certain optimizations
|that assume functions have no external impacts.
|EXTERNAL ACTION must be
|explicitly or implicitly specified if the body of the function calls another
|function that has an external action (SQLSTATE 428C2).
|
|The syntax diagram changes to:
|>>-CREATE FUNCTION--function-name------------------------------->
|
|>----(--+------------------------------------+---)---*---------->
| | .-,----------------------------. |
| | V | |
| '----parameter-name--data-type1---+--'
|
|>----RETURNS--+-data-type2--------------------+--*-------------->
| '--+-ROW---+---| column-list |--'
| '-TABLE-'
|
| .-LANGUAGE SQL--.
|>-----+--------------------------+--*----+---------------+--*--->
| '-SPECIFIC--specific-name--'
|
| .-NOT DETERMINISTIC--. .-EXTERNAL ACTION----.
|>-----+--------------------+--*----+--------------------+--*---->
| '-DETERMINISTIC------' '-NO EXTERNAL ACTION-'
|
| .-READS SQL DATA--. .-STATIC DISPATCH--.
|>-----+-----------------+--*----+------------------+--*--------->
| '-CONTAINS SQL----'
|
| (1)
| .-CALLED ON NULL INPUT-------.
|>-----+----------------------------+--*------------------------->
|
|>-----+-----------------------------------------------------+--->
| | (2) |
| '-PREDICATES--(--| predicate-specification |--)-------'
|
|>----| SQL-function-body |-------------------------------------><
|
|column-list
|
| .-,--------------------------.
| V |
||---(-----column-name--data-type3---+---)-----------------------|
|
|SQL-function-body
|
||---+-RETURN Statement-----------+------------------------------|
| '-dynamic-compound-statement-'
|
|Note:
- |NULL CALL may be specified in place of CALLED ON NULL INPUT
- |Valid only if RETURNS specifies a scalar result (data-type2)
|
|Change the following parameters:
|
- |LANGUAGE SQL
- |Specifies that the function is written using SQL.
|
|This parameter section replaces the "RETURN expression, NULL, WITH common-table-expression,
|fullselect" parameter section.
|
- |SQL-function-body
- |Specifies the body of the function. Parameter names can be referenced
|in the SQL-function-body. Parameter names may be qualified with the function
|name to avoid ambiguous references.
|If the SQL-function-body is a dynamic
|compound statement, it must contain at least one RETURN statement and a RETURN
|statement must be executed when the function is called (SQLSTATE 42632).
|If the function is a table or row function, then it can contain only one
|RETURN statement which must be the last statement in the dynamic compound
|(SQLSTATE 429BD).
|For additional details, see Compound Statement (Dynamic)
|and RETURN.
|
|The syntax diagram changes to:
|Syntax
|>>-CREATE------------------------------------------------------->
|
|>-----+-METHOD--+-method-name----------+---FOR--type-name--+---->
| | '-| method-signature |-' |
| '-SPECIFIC METHOD--specific-name---------------------'
|
|>-----+-*----EXTERNAL--+-----------------------+--*----+------------------------------+--*--+>
| | '-NAME--+-'string'---+--' '-TRANSFORM GROUP--group-name--' |
| | '-identifier-' |
| '-| SQL-method-body |-----------------------------------------------------------------'
|
|>--------------------------------------------------------------><
|
|method-signature
|
||---method-name--(--+---------------------------------------------------------+---)-->
| | .-,--------------------------------------------------. |
| | V | |
| '----+-----------------+---data-type1--+-------------+--+-'
| '-parameter-name--' '-AS LOCATOR--'
|
|>----+------------------------------------------------------------------+->
| '-RETURNS--+-data-type2--+-------------+------------------------+--'
| | '-AS LOCATOR--' |
| '-data-type3--CAST FROM--data-type4--+-------------+-'
| '-AS LOCATOR--'
|
|>---------------------------------------------------------------|
|
|SQL-method-body
|
||---+-RETURN Statement-----------+------------------------------|
| '-dynamic-compound-statement-'
|
|The following parameters replace the "RETURN scalar-expression or NULL"
|section:
|
- |SQL-method-body
- |The SQL-method-body defines the how the method is implemented if the
|method specification in CREATE TYPE is LANGUAGE SQL.
|The SQL-method-body
|must comply with the following parts of the method specification:
|
- |DETERMINISTIC or NOT DETERMINISTIC (SQLSTATE 428C2)
- |EXTERNAL ACTION or NO EXTERNAL ACTION (SQLSTATE 428C2)
- |CONTAINS SQL or READS SQL DATA (SQLSTATE 42985)
|
|Parameter names can be referenced in the SQL-method-body. The
|subject of the method is passed to the method implementation as an implicit
|first parameter named SELF.
|For additional details, see Compound Statement (Dynamic)
|and RETURN.
|
|
|
|The CREATE SEQUENCE statement creates a sequence at the application
|server.
|Invocation
|This statement can be embedded in an application program or issued through
|the use of dynamic SQL statements. It is an executable statement that can
|be dynamically prepared. However, if the bind option DYNAMICRULES BIND
|applies, the statement cannot be dynamically prepared (SQLSTATE 42509).
|Authorization
|The privileges held by the authorization ID of the statement must include
|at least one of the following:
|
- |CREATEIN privilege for the implicitly or explicitly specified schema
- |SYSADM or DBADM authority
|
|Syntax
| .-AS INTEGER-----.
|>>-CREATE SEQUENCE--sequence-name---*----+----------------+--*-->
| '-AS--data-type--'
|
|>-----+-------------------------------+--*---------------------->
| '-START WITH--numeric-constant--'
|
| .-INCREMENT BY 1------------------.
|>-----+---------------------------------+--*-------------------->
| '-INCREMENT BY--numeric-constant--'
|
| (1)
| .-NO MINVALUE-----------------.
|>-----+-----------------------------+--*------------------------>
| '-MINVALUE--numeric-constant--'
|
| .-NO MAXVALUE-----------------. .-NO CYCLE--.
|>-----+-----------------------------+--*----+-----------+--*---->
| '-MAXVALUE--numeric-constant--' '-CYCLE-----'
|
| .-CACHE 20-----------------. .-NO ORDER--.
|>-----+--------------------------+--*----+-----------+--*------><
| +-CACHE--integer-constant--+ '-ORDER-----'
| '-NO CACHE-----------------'
|
|Note:
- |These parameters can be specified without spaces: NOMINVALUE, NOMAXVALUE,
|NOCYCLE, NOCACHE, and NOORDER. These single word versions are all acceptable
|alternatives to the two word versions.
|
|Description
|
- |sequence-name
- |Names the sequence. The combination of name, and the implicit or
|explicit schema name must not identify an existing sequence at the current
|server (SQLSTATE 42710).
|The unqualified form of sequence-name
|is an SQL identifier. The qualified form is a qualifier followed by
|a period and an SQL identifier. The qualifier is a schema name.
|If the sequence name is explicitly qualified with a schema
|name, the schema name cannot begin with 'SYS' or an error (SQLSTATE
|42939) is raised.
- |AS data-type
- |Specifies the data type to be used for the sequence value. The data
|type can be any exact numeric type (SMALLINT, INTEGER, BIGINT or DECIMAL)
|with a scale of zero or a user-defined distinct type for which the source
|type is an exact numeric type with a scale of zero (SQLSTATE 42815).
|The default is INTEGER.
- |START WITH numeric-constant
- |Specifies the first value for the sequence. This value can be any
|positive or negative value that could be assigned to a column of the data
|type associated with the sequence (SQLSTATE 42820), without non-zero
|digits existing to the right of the decimal point (SQLSTATE 428FA). The
|default is MINVALUE for ascending sequences and MAXVALUE for descending
|sequences.
|This value is not necessarily the value that a sequence
|would cycle to after reaching the maximum or minimum value of the sequence.
|The START WITH clause can be used to start a sequence outside the range
|that is used for cycles. The range used for cycles is defined by MINVALUE
|and MAXVALUE.
- |INCREMENT BY numeric-constant
- |Specifies the interval between consecutive values of the sequence.
|This value can be any positive or negative value that could be assigned
|to a column of the data type associated with the sequence (SQLSTATE 42820),
|and does not exceed the value of a large integer constant (SQLSTATE 42815),
|without non-zero digits existing to the right of the decimal point (SQLSTATE
|428FA).
|If this value is negative, then the sequence of values
|descends. If this value is positive, then the sequence of values ascends.
|If this value is 0 or greater than the range defined by MINVALUE and
|MAXVALUE, only one value will be generated, but the sequence is treated as
|an ascending sequence otherwise. The default is 1.
- |MINVALUE or NO MINVALUE
- |Specifies the minimum value at which a descending sequence either cycles
|or stops generating values, or an ascending sequence cycles to after
|reaching the maximum value.
|
- |MINVALUE numeric-constant
- |Specifies the numeric constant that is the minimum value. This value
|can be any positive or negative value that could be assigned to a
|column of the data type associated with the sequence (SQLSTATE 42820),
|without non-zero digits existing to the right of the decimal point
|(SQLSTATE 428FA), but the value must be less than or equal to the
|maximum value (SQLSTATE 42815).
- |NO MINVALUE
- |For an ascending sequence, the value is the START WITH value, or 1
|if START WITH is not specified. For a descending sequence, the value
|is the minimum value of the data type associated with the sequence.
|This is the default.
|
- |MAXVALUE or NO MAXVALUE
- |Specifies the maximum value at which an ascending sequence either
|cycles or stops generating values, or a descending sequence cycles to after
|reaching the minimum value.
|
- |MAXVALUE numeric-constant
- |Specifies the numeric constant that is the maximum value. This value
|can be any positive or negative value that could be assigned to a
|column of the data type associated with the sequence (SQLSTATE 428FA),
|without non-zero digits existing to the right of the decimal point
|(SQLSTATE 428FA), but the value must be greater than or equal to the
|minimum value (SQLSTATE 42815).
- |NO MAXVALUE
- |For an ascending sequence, the value is the maximum value of the
|data type associated with the sequence. For a descending sequence, the
|value is the START WITH value, or -1 if START WITH is not specified.
|This is the default.
|
- |CYCLE or NO CYCLE
- |Specifies whether the sequence should continue to generate values after
|reaching either its maximum or minimum value. The boundary of the sequence
|can be reached either with the next value landing exactly on the boundary
|condition, or by overshooting it.
|
- |CYCLE
- |Specifies that values continue to be generated for this sequence
|after the maximum or minimum value has been reached. If this option is
|used, after an ascending sequence reaches its maximum value it generates
|its minimum value; after a descending sequence reaches its minimum
|value it generates its maximum value. The maximum and minimum values
|for the sequence determine the range that is used for cycling.
|When CYCLE is in effect, then duplicate values can be generated for
|the sequence.
- |NO CYCLE
- |Specifies that values will not be generated for the sequence once
|the maximum or minimum value for the sequence has been reached. This
|is the default.
|
- |CACHE or NO CACHE
- |Specifies whether to keep some preallocated values in memory for faster
|access. This is a performance and tuning option.
|
- |CACHE integer-constant
- |Specifies the maximum number of sequence values that are preallocated
|and kept in memory. Preallocating and storing values in the cache reduces
|synchronous I/O to the log when values are generated for the sequence.
|In the event of a system failure, all cached sequence values that
|have not been used in committed statements are lost (that is, they
|will never be used). The value specified for the CACHE option is
|the maximum number of sequence values that could be lost in case
|of system failure.
|The minimum value is 2 (SQLSTATE
|42815). The default value is CACHE 20.
- |NO CACHE
- |Specifies that values of the sequence are not to be preallocated. It
|ensures that there is not a loss of values in the case of a system
|failure, shutdown or database deactivation. When this option is specified,
|the values of the sequence are not stored in the cache. In this case,
|every request for a new value for the sequence results in synchronous
|I/O to the log.
|
- |NO ORDER or ORDER
- |Specifies whether the sequence numbers must be generated in order of
|request.
|
- |ORDER
- |Specifies that the sequence numbers are generated in order of
|request.
- |NO ORDER
- |Specifies that the sequence numbers do not need to be generated in
|order of request. This is the default.
|
|
|Version
|
- |It is possible to define a constant sequence, that is, one that would
|always return a constant value. This could be done by specifying the same
|value for MINVALUE or MAXVALUE, or by specifying an INCREMENT value of
|zero. In either case, in order to allow for NEXTVAL to generate the same
|value more than once, CYCLE must be specified. A constant sequence can
|be used as a numeric global variable. ALTER SEQUENCE can be used to adjust
|the values that will be generated for a constant sequence.
- |A sequence can be cycled manually, by using the ALTER SEQUENCE statement.
|If NO CYCLE is implicitly or explicitly specified, the sequence can be
|restarted or extended using the ALTER SEQUENCE statement to cause values
|to continue to be generated once the maximum or minimum value for the
|sequence has been reached.
- |Caching sequence numbers implies that a range of sequence numbers can
|be kept in memory for fast access. When an application accesses a sequence
|that can allocate the next sequence number from the cache, the sequence
|number allocation can happen quickly. However, if an application accesses
|a sequence that cannot allocate the next sequence number from the cache,
|the sequence number allocation may require having to wait for I/O operations
|to persistent storage. The choice of the value for CACHE should be done
|keeping in mind the performance and application requirements tradeoffs.
- |The owner has the ALTER and USAGE privileges on the new sequence. Only
|the USAGE privilege can be granted by the owner and only to PUBLIC.
- |The following syntax is also supported: NOMINVALUE, NOMAXVALUE, NOCYCLE,
|NOCACHE, and NOORDER.
|
|範例
|Example 1: Create a sequence called org_seq:
| CREATE SEQUENCE org_seq
| START WITH 1
| INCREMENT BY 1
| NO MAXVALUE
| NO CYCLE
| CACHE 24
|Syntax
|>>-CREATE TRIGGER--trigger-name----+-NO CASCADE BEFORE-+-------->
| '-AFTER-------------'
|
|>-----+-INSERT-----------------------------+--ON--table-name---->
| +-DELETE-----------------------------+
| '-UPDATE--+------------------------+-'
| | .-,--------------. |
| | V | |
| '-OF----column-name---+--'
|
|>-----+----------------------------------------------------------------------+>
| | .----------------------------------------------------. |
| | V (1) (2) .-AS-. | |
| '-REFERENCING-------------------+-OLD--+----+--correlation-name--+--+--'
| | .-AS-. |
| +-NEW-+----+--correlation-name---+
| | .-AS-. |
| +-OLD_TABLE-+----+--identifier---+
| | .-AS-. |
| '-NEW_TABLE-+----+--identifier---'
|
|>-----+-FOR EACH ROW---------------+--MODE DB2SQL--------------->
| | (3) |
| '--------FOR EACH STATEMENT--'
|
|>-----| triggered-action |-------------------------------------><
|
|triggered-action
|
||--+-------------------------------+--SQL-procedure-statement---|
| '-WHEN--(--search-condition--)--'
|
|Note:
- |OLD and NEW may only be specified once each.
- |OLD_TABLE and NEW_TABLE may only be specified once each and only
|for AFTER triggers.
- |FOR EACH STATEMENT may not be specified for BEFORE triggers.
|
|Replace the description of "triggered-action" with the following:
|
- |triggered-action
- |Specifies the action to be performed when a trigger is activated. A
|triggered-action is composed of an SQL-procedure-statement and an
|optional condition for the execution of the SQL-procedure-statement.
- |WHEN (search-condition)
- |Specifies a condition that is true, false, or unknown. The search-condition provides a capability to determine whether or not a certain triggered
|action should be executed.
|The associated action is performed only if the
|specified search condition evaluates as true. If the WHEN clause is omitted,
|the associated SQL-procedure statement is always performed.
- |SQL-procedure-statement
- |The SQL-procedure-statement can contain a dynamic compound
|statement or any of the SQL control statements listed in Compound Statement (Dynamic).
|If the trigger is a BEFORE trigger, then an SQL-procedure-statement
|can also include a fullselect
| or a SET variable statement (SQLSTATE 42987).
|If the trigger is an AFTER trigger, then an SQL-procedure-statement
|can also include one of the following (SQLSTATE 42987):
|
- |an INSERT SQL statement
- |a searched UPDATE SQL statement
- |a searched DELETE SQL statement
- |a SET variable statement
- |a fullselect 7
|
|The SQL-procedure-statement cannot reference an undefined
|transition variable (SQLSTATE 42703) or a declared temporary table (SQLSTATE
|42995).
|The SQL-procedure-statement in a BEFORE trigger cannot
|reference a summary table defined with REFRESH IMMEDIATE (SQLSTATE 42997).
|The SQL-procedure-statement in a BEFORE trigger cannot reference
|a generated column, other than the identity column, in the new transition
|variable (SQLSTATE 42989).
|
|The Notes section changes to the following:
|
- |The result of a fullselect specified in the SQL-procedure-statement is not available inside or outside of the trigger.
- |Inoperative triggers:
|An inoperative trigger is a trigger that is no longer available and is therefore
|never activated.
|
|
|A trigger becomes inoperative if:
|
- |A privilege that the creator of the trigger is required to have for the
|trigger to execute is revoked.
- |An object such as a table, view or alias, upon which the triggered action
|is dependent, is dropped.
- |A view, upon which the triggered action is dependent, becomes inoperative.
- |An alias that is the subject table of the trigger is dropped.
|
|In practical terms, an inoperative trigger is one in which a trigger
|definition has been dropped as a result of cascading rules for DROP or REVOKE
|statements. For example, when an view is dropped, any trigger with an SQL-procedure-statement defined using that view is made inoperative.
|When a trigger is made inoperative, all packages with statements performing
|operations that were activating the trigger will be marked invalid. When
|the package is rebound (explicitly or implicitly) the inoperative
|trigger is completely ignored. Similarly, applications with dynamic SQL
|statements performing operations that were activating the trigger will also
|completely ignore any inoperative triggers.
|The trigger name can still
|be specified in the DROP TRIGGER and COMMENT ON TRIGGER statements.
|An inoperative trigger may be recreated by issuing a CREATE TRIGGER statement
|using the definition text of the inoperative trigger. This trigger definition
|text is stored in the TEXT column of SYSCAT.TRIGGERS. Note that there is no
|need to explicitly drop the inoperative trigger in order to recreate it.
|Issuing a CREATE TRIGGER statement with the same trigger-name as an inoperative trigger will cause that inoperative trigger to be
|replaced with a warning (SQLSTATE 01595).
|Inoperative triggers are indicated
|by an X in the VALID column of the SYSCAT.TRIGGERS catalog view.
- |Errors executing triggers:
|Errors that occur during the execution of triggered SQL statements
|are returned using SQLSTATE 09000 unless the error is considered severe.
|
|
|If the error is severe, the severe error SQLSTATE is returned. The SQLERRMC
|field of the SQLCA for non-severe error will include the trigger name, SQLCODE,
|SQLSTATE and as many tokens as will fit from the tokens of the failure.
|The SQL-procedure-statement could include a SIGNAL SQLSTATE statement
|or contain a RAISE_ERROR function. In both these cases, the SQLSTATE returned
|is the one specified in the SIGNAL SQLSTATE statement or the RAISE_ERROR condition.
|
|Linux uses libraries called LIBDRDA.SO and LIBSQLNET.SO, not LIBDRDA.A and LIBSQLNET.A.
|
|
|Within the "DECLARE CURSOR" statement, near the end of the Notes section
|the following sentence should be changed from:
|An ambiguous cursor is considered read-only if the BLOCKING bind option
|is ALL, otherwise it is considered deletable.
|to:
|An ambiguous cursor is considered read-only if the BLOCKING bind option
|is ALL; otherwise, it is considered updatable.
|The change is from the word "deletable" to the word "updatable".
|The searched DELETE syntax diagram changes to the following:
|>>-DELETE FROM----+-table-name-------------------+-------------->
| +-view-name--------------------+
| '-ONLY--(--+-table-name-+---)--'
| '-view-name--'
|
|>-----+---------------------------+----------------------------->
| | .-AS-. |
| '-+----+--correlation-name--'
|
|>-----+--------------------------+---+---------------+---------><
| '-WHERE--search-condition--' '-WITH--+-RR-+--'
| +-RS-+
| +-CS-+
| '-UR-'
|
|Positioned DELETE:
|>>-DELETE FROM----+-table-name-------------------+-------------->
| +-view-name--------------------+
| '-ONLY--(--+-table-name-+---)--'
| '-view-name--'
|
|>----WHERE CURRENT OF--cursor-name-----------------------------><
|
|Add the following to the description section:
|
- |WITH
-
|
| Specifies the isolation level used when locating the
|rows to be deleted.
|
- |RR
- |Repeatable Read
- |RS
- |Read Stability
- |CS
- |Cursor Stability
- |UR
- |Uncommitted Read
|
|The default isolation level of the statement is the isolation
|level of the package in which the statement is bound.
|
|Add the following option:
|>>-SEQUENCE--sequence-name--RESTRICT---------------------------><
|
|Add the following parameters:
|
- |SEQUENCE sequence-name RESTRICT
-
|Identifies the particular sequence that is to be dropped. The sequence-name, along with the implicit or explicit schema name, must
|identify an existing sequence at the current server. If no sequence by this
|name exists in the explicitly or implicitly specified schema, an error (SQLSTATE
|42704) is raised.
|The RESTRICT keyword enforces the rule that the
|sequence is not dropped if the definition of a table column refers to the
|sequence (through an IDENTITY column).
|
- |Note:
-
|
- |System created sequences for IDENTITY columns cannot be dropped using
|the DROP sequence command.
- |When a sequence is dropped, all privileges on the sequence are also dropped.
|
|
|The table showing the dependencies that objects have on each other (Table
|27) needs to be updated as follows:
|New row: DROP SEQUENCE The entry at the intersection of the new row "DROP
|SEQUENCE" and the column "PACKAGE" will be an "A". The rest of the entries
|in this new row will be "-"
|
|
|
|
|
|This form of the GRANT statement grants privileges on a user-defined sequence.
|Invocation
|This statement can be embedded in an application program or issued through
|the use of dynamic SQL statements. It is an executable statement that can
|be dynamically prepared. However, if the bind option DYNAMICRULES BIND
|applies, the statement cannot be dynamically prepared (SQLSTATE 42509).
|Authorization
|The privileges held by the authorization ID of the statement must include
|at least one of the following:
|
- |Owner of the sequence
- |SYSADM or DBADM authority
|
|Syntax
|>>-GRANT--USAGE--ON SEQUENCE--sequence-name--TO PUBLIC---------><
|
|Description
|
- |USAGE
- |Grants the USAGE privilege for a sequence.
- |ON SEQUENCE sequence-name
- |Identifies the sequence on which the USAGE privilege is to be granted.
|The sequence-name, including the implicit or explicit schema
|qualifier, must uniquely identify an existing sequence at the current
|server. If no sequence by this name exists in the specified schema, an
|error (SQLSTATE 42704) is raised.
- |TO PUBLIC
- |Grants the USAGE privilege to all users.
|
|Examples
|Example 1: Grant any user the privilege on
|a sequence called MYNUM
| GRANT USAGE ON SEQUENCE MYNUM TO PUBLIC
|
|Syntax diagram changes to:
|>>-INSERT INTO----+-table-name-+-------------------------------->
| '-view-name--'
|
|>-----+----------------------------+---------------------------->
| | .-,--------------. |
| | V | |
| '-(-----column-name---+---)--'
|
| .-,------------------------------------.
| V |
|>-----+-VALUES------+-+-expression-+----------------+--+--------+>
| | | +-NULL-------+ | |
| | | '-DEFAULT----' | |
| | | .-,-----------------. | |
| | | V | | |
| | '-(------+-expression-+--+---)--' |
| | +-NULL-------+ |
| | '-DEFAULT----' |
| '-+---------------------------------------+---fullselect--'
| | .-,--------------------------. |
| | V | |
| '-WITH-----common-table-expression---+--'
|
|>-----+---------------+----------------------------------------><
| '-WITH--+-RR-+--'
| +-RS-+
| +-CS-+
| '-UR-'
|
|Add the following to the description section:
|
- |WITH
-
|
| Specifies the isolation level at which the fullselect
|is executed.
|
- |RR
- |Repeatable Read
- |RS
- |Read Stability
- |CS
- |Cursor Stability
- |UR
- |Uncommitted Read
|
|The default isolation level of the statement is the isolation
|level of the package in which the statement is bound.
|
|The syntax diagram changes to:
| .-,----------------.
| V |
|>>-select-clause--INTO-------host-variable---+--from-clause----->
|
|>----+--------------+--+-----------------+--+---------------+--->
| '-where-clause-' '-group-by-clause-' '-having-clause-'
|
|>-----+---------------+----------------------------------------><
| '-WITH--+-RR-+--'
| +-RS-+
| +-CS-+
| '-UR-'
|
|Add the following to the description section:
|
- |WITH
-
|
| Specifies the isolation level at which the
|SELECT INTO statement is executed.
|
- |RR
- |Repeatable Read
- |RS
- |Read Stability
- |CS
- |Cursor Stability
- |UR
- |Uncommitted Read
|
|The default isolation level of the statement is the isolation level
|of the package in which the statement is bound.
|
|
|
|The SET ENCRYPTION PASSWORD statement sets the password that will be
|used by the encryption and decryption functions. The password is not tied
|to DB2 authentication, and is used for data encryption only.
|This statement is not under transaction control.
|Invoction
|The statement can be embedded in an application program or issued interactively.
|It is an executable statement that can be dynamically prepared.
|Authorization
|No authorization is required to execute this statement.
|Syntax
| .-=-.
|>>-SET--ENCRYPTION PASSWORD--+---+--+-host-variable---+--------><
| '-string-constant-'
|
|Description
|The ENCRYPTION PASSWORD can be used by the ENCRYPT, DECRYPT_BIN, and DECRYPT_CHAR
|built-in functions for password based encryption. The length must be between
|6 and 127 inclusive. All characters must be specified in the exact case intended
|as there is no conversion to uppercase characters.
|
- |host-variable
- |A variable of type CHAR or VARCHAR. The length of the contents of the
|host-variable must be between 6 and 127 inclusive (SQLSTATE 428FC). It cannot
|be set to null. All characters must be specified in the exact case intended
|as there is no conversion to uppercase characters.
- |string-constant
- |A character string constant. The length must be between 6 and 127 inclusive
|(SQLSTATE 428FC).
|
|Rule
|
- |The initial ENCRYPTION PASSWORD value is the empty string ('').
- |The host-variable or string-constant is transmitted to the database server
|using normal DB2 mechanisms.
|
|Version
|
|Example
|Example 1: The following statement sets
|the ENCRYPTION PASSWORD.
| SET ENCRYPTION PASSWORD = 'bubbalu'
|This section changes to the following:
|
|
|
|The SET Variable statement assigns values to local variables or to new
|transition variables. It is under transaction control.
|Invocation
|This statement can only be used as an SQL statement in either a dynamic
|compound statement, trigger, SQL function or SQL method.
|Authorization
|To reference a transition variable, the privileges held by the authorization
|ID of the trigger creator must include at least one of the following:
|
- |UPDATE of the columns referenced on the left hand side of the assignment
|and SELECT for any columns referenced on the right hand side.
- |CONTROL privilege on the table (subject table of the trigger)
- |SYSADM or DBADM authority.
|
|To execute this statement with a row-fullselect
|as the right hand side of the assignment, the privileges held by the
|authorization ID of either the trigger definer or the dynamic compound
|statement owner must also include at least one of the following, for each
|table or view referenced:
|
- |SELECT privilege
- |CONTROL privilege
- |SYSADM or DBADM.
|
|Syntax
|>>-SET---------------------------------------------------------->
|
| .-,---------------------------------------------------------------------------------.
| V |
|>--------+-| target-variable |--=--+-expression-+--------------------------------------+--+>
| | +-NULL-------+ |
| | '-DEFAULT----' |
| | .-,----------------------. .-,--------------------. |
| | V | V (1) | |
| '-(-----| target-variable |---+---)--=--(--+----+-expression------+--+-+---)--'
| | +-NULL------------+ |
| | '-DEFAULT---------' |
| | (2) |
| '-row-fullselect------------'
|
|>--------------------------------------------------------------><
|
|target-variable
|
||---+-SQL-variable-name--------+---+--------------------------+-|
| '-transition-variable-name-' | .--------------------. |
| | V | |
| '----..attribute-name---+--'
|
|Note:
- |The number of expressions, NULLs and DEFAULTs must match the number
|of target-variables.
- |The number of columns in the select list must match the number of target-variables.
|
|Description
|
- |target-variable
- |Identifies the target variable of the assignment. A target-variable representing the same variable must not be specified more than once
|(SQLSTATE 42701).
|
- |SQL-variable-name
- |Identifies the SQL variable that is the assignment target. SQL
|variables must be declared before they are used. SQL variables can be
|defined in a dynamic compound statement.
- |transition-variable-name
- |Identifies the column to be updated in the transition row. A transition-variable-name must identify a column in the subject
|table of a trigger, optionally qualified by a correlation name that
|identifies the new value (SQLSTATE 42703).
- |..attribute name
- |Specifies the attribute of a structured type that is set (referred to
|as an attribute assignment). The SQL-variable-nameor transition-variable-name
|specified must be defined with a user-defined structured type (SQLSTATE
|428DP). The attribute-name must be an attribute
|of the structured type (SQLSTATE 42703). An assignment that does not
|involve the ..attribute name clause
|is referred to as a conventional assignment.
|
- |expression
- |Indicates the new value of the target-variable. The expression
|is any expression of the type described in Chapter 2 of the SQL Reference.
|The expression can not include a column function except when it occurs
|within a scalar fullselect (SQLSTATE 42903). In the context of a CREATE
|TRIGGER statement, an expression may contain references
|to OLD and NEW transition variables and must be qualified by the correlation-name to specify which transition variable (SQLSTATE 42702).
- |NULL
- |Specifies the null value and can only be specified for nullable columns
|(SQLSTATE 23502). NULL cannot be the value in an attribute assignment
|(SQLSTATE 429B9), unless it was specifically cast to the data type of
|the attribute.
- |DEFAULT
- |Specifies that the default value should be used.
|If target-variable is a column, the value inserted depends on how the column was defined
|in the table.
|
- |If the column was defined using the WITH DEFAULT clause, then the value
|is set to the default defined for the column.
- |If the column was defined using the IDENTITY clause, the value is generated
|by the database manager.
- |If the column was defined without specifying the WITH DEFAULT clause,
|the IDENTITY clause, or the NOT NULL clause, then the value is NULL.
- |If the column was defined using the NOT NULL clause and the IDENTITY clause
|is not used, or the WITH DEFAULT clause was not used or DEFAULT NULL
|was used, the DEFAULT keyword cannot be specified for that column (SQLSTATE
|23502).
|
|If target-variable is an SQL variable, then the value
|inserted is the default as specified or implied in the variable declaration.
- |row-fullselect
- |A fullselect that returns a single row with the number of columns corresponding
|to the number of target-variables specified for assignment. The values
|are assigned to each corresponding target-variable. If the result of the
|row-fullselect is no rows, then null values are assigned. In the context
|of a CREATE TRIGGER statement, a row-fullselect
|may contain references to OLD and NEW transition variables which must
|be qualified by their correlation-name to specify
|which transition variable to use (SQLSTATE 42702). An error is returned if
|there is more than one row in the result (SQLSTATE 21000).
|
|Rule
|
- |The number of values to be assigned from expressions, NULLs and DEFAULTs
|or the row-fullselect must match the number of target-variables
|specified for assignment (SQLSTATE 42802).
- |A SET Variable statement cannot assign an SQL variable and a transition
|variable in one statement (SQLSTATE 42997).
- |Values are assigned to target-variables under the assignment rules described
|in Chapter 2 of the SQL Reference.
|If the statement is used in a BEFORE
|UPDATE trigger, and the registry variable DB2_UPDATE_PART_KEY=OFF, then
|a transition-variable specified as target-variable cannot
|be a partitioning key column (SQLSTATE 42997).
|
|Version
|
|The examples for this statement stay the same.
|The searched UPDATE syntax diagram is changed to:
|>>-UPDATE----+-table-name-------------------+------------------->
| +-view-name--------------------+
| '-ONLY--(--+-table-name-+---)--'
| '-view-name--'
|
|>-----+---------------------------+----------------------------->
| | .-AS-. |
| '-+----+--correlation-name--'
|
|>-----SET--| assignment-clause |-------------------------------->
|
|>-----+--------------------------+---+---------------+---------><
| '-WHERE--search-condition--' '-WITH--+-RR-+--'
| +-RS-+
| +-CS-+
| '-UR-'
|
|Add the following to the description section:
|
- |WITH
-
|
| Specifies the isolation level at which the UPDATE statement
|is executed.
|
- |RR
- |Repeatable Read
- |RS
- |Read Stability
- |CS
- |Cursor Stability
- |UR
- |Uncommitted Read
|
|The default isolation level of the statement is the isolation level
|of the package in which the statement is bound.
|
Footnotes:
- 6
-
A common-table-expression may precede the fullselect
- 7
-
A common-table-expression may
precede a fullselect.
- 8
-
There is no
casting of the previous value to the source type prior to the computation.
[ 頁面頂端 | 前一頁 | 下一頁 | 目錄 | 索引 ]