版次注意事項


|38.5 Chapter 6. SQL Statements

|38.5.1 Update of the Partitioning Key Now Supported

|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. |

|38.5.1.1 Statement: ALTER TABLE

|Rules |

|38.5.1.2 Statement: CREATE TABLE

|Rules |

|38.5.1.3 Statement: DECLARE GLOBAL TEMPORARY TABLE PARTITIONING KEY (column-name,...)

|Note:
The partitioning key columns cannot be updated (SQLSTATE 42997). |

|38.5.1.4 Statement: UPDATE

|Footnotes |

|38.5.2 Larger Index Keys for Unicode Databases

|38.5.2.1 ALTER TABLE

|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.

|38.5.2.2 CREATE INDEX

|Indexes can be defined on variable length columns whose length is greater |than 255 bytes if the registry variable DB2_INDEX_2BYTEVARLEN is ON.

|38.5.2.3 CREATE TABLE

|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.

|38.5.3 ALTER SEQUENCE

|ALTER SEQUENCE

| |

|The ALTER SEQUENCE statement modifies the attributes of a sequence by: |

|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: |

|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 |

|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
| 

|38.5.4 ALTER TABLE

|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:

  1. |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. |
|
|

|38.5.5 Compound SQL (Embedded)

|A prepared COMMIT statement is not allowed in an ATOMIC compound SQL statement.

|38.5.6 Compound Statement (Dynamic)

|Compound Statement (Dynamic)

| | | | | | |

|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:

  1. |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: |

|The SQL statements that can be issued are: |

|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 |

|38.5.7 CREATE FUNCTION (Source or Template)

|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). |

|38.5.8 CREATE FUNCTION (SQL Scalar, Table or Row)

|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:

  1. |NULL CALL may be specified in place of CALLED ON NULL INPUT

  2. |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. |

|38.5.9 CREATE METHOD

|The syntax diagram changes to:

|CREATE METHOD

|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: |

|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. |

|38.5.10 CREATE SEQUENCE

|CREATE SEQUENCE

| |

|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: |

|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:

  1. |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 |

|範例

|Example 1: Create a sequence called org_seq:

|   CREATE SEQUENCE org_seq 
|      START WITH 1 
|      INCREMENT BY 1 
|      NO MAXVALUE 
|      NO CYCLE 
|      CACHE 24

|38.5.11 CREATE TRIGGER

|CREATE TRIGGER

|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:

  1. |OLD and NEW may only be specified once each.

  2. |OLD_TABLE and NEW_TABLE may only be specified once each and only |for AFTER triggers.

  3. |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): |

|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: |

|38.5.12 CREATE WRAPPER

|Linux uses libraries called LIBDRDA.SO and LIBSQLNET.SO, not LIBDRDA.A and LIBSQLNET.A.

|38.5.13 DECLARE CURSOR

| |

|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".

|38.5.14 DELETE

|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. |

|38.5.15 DROP

|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:
| |

|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 "-"

|38.5.16 GRANT (Sequence Privileges)

|GRANT (Sequence Privileges)

| | | | |

|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: |

|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
|    

|38.5.17 INSERT

|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. |

|38.5.18 SELECT INTO

|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. |

|38.5.19 SET ENCRYPTION PASSWORD

|SET ENCRYPTION PASSWORD

| |

|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 |

|Version |

|Example

|Example 1:  The following statement sets |the ENCRYPTION PASSWORD.

|   SET ENCRYPTION PASSWORD = 'bubbalu'

|38.5.20 SET transition-variable

|This section changes to the following:

|SET Variable

| | |

|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: |

|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: |

|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:

  1. |The number of expressions, NULLs and DEFAULTs must match the number |of target-variables.

  2. |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 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 |

|Version |

|The examples for this statement stay the same.

|38.5.21 UPDATE

|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.


[ 頁面頂端 | 前一頁 | 下一頁 | 目錄 | 索引 ]