ALTER DATABASE PARTITION GROUP DROP DBPARTITIONNUM Specifies the specific partition or partitions to drop from the database partition group. DBPARTITIONNUMS is a synonym for DBPARTITIONNUM. Any specified partition must already be defined in the database partition group(SQLSTATE 4272 db-partitions-clause Specifies the partition or partitions to be added or dropped db-partition-number1 Specify a specific partition number to db-partition-n1u/ 2 Specify a range of partition numbers. The value of db-partition-nmumber2 must be greater than or equal to the value of db-partition-numberl (SQLSTATE 428A9) LIKE DBPARTITIONNUM db-partition-mumber Specifies that the containers for the existing table spaces in the database partition group will be the same as the containers on the specified db-partition-number. The partition specified must be a partition that existed in the database partition group prior to this statement and is not included in a drop dbpartitionnum clause of the same statement WITHOUT TABLESPACES pecifies that the default table spaces are not created on the newly added partition or partitions. The ALTER TABLESPACE statement using the FOR DBPaRtitiONNUM clause must be used to define containers for use i with the table spaces that are defined on this database partition group. this option is not specified, the default containers are specified on newly added partitions for each table space defined on the database partition Rules: Each partition specified by number must be defined in the db2nodes. cfg file (SQLSTATE 42729) Each db-partition-number listed in the ON DBPARTITIONNUMS clause must be for a unique partition(SQLSTATE 427 A valid partition number is between 0 and 999 inclusive (SQLSTATE 42729) A partition cannot appear in both the ADD and DROP clauses (SQLSTATE 42728) There must be at least one partition remaining in the database partition group. The last partition cannot be dropped from a database partition group(SQLSTATE 428C0 If neither the LIKE dbpartitionnum clause nor the Without TABLESPACES clause is specified when adding a partition, the default is to use the lowest partition number of the existing partitions in the database 16 SQL Reference, Volume 2
DROP DBPARTITIONNUM Specifies the specific partition or partitions to drop from the database partition group. DBPARTITIONNUMS is a synonym for DBPARTITIONNUM. Any specified partition must already be defined in the database partition group (SQLSTATE 42729). db-partitions-clause Specifies the partition or partitions to be added or dropped. db-partition-number1 Specify a specific partition number. TO db-partition-number2 Specify a range of partition numbers. The value of db-partition-number2 must be greater than or equal to the value of db-partition-number1 (SQLSTATE 428A9). LIKE DBPARTITIONNUM db-partition-number Specifies that the containers for the existing table spaces in the database partition group will be the same as the containers on the specified db-partition-number. The partition specified must be a partition that existed in the database partition group prior to this statement and is not included in a DROP DBPARTITIONNUM clause of the same statement. WITHOUT TABLESPACES Specifies that the default table spaces are not created on the newly added partition or partitions. The ALTER TABLESPACE statement using the FOR DBPARTITIONNUM clause must be used to define containers for use with the table spaces that are defined on this database partition group. If this option is not specified, the default containers are specified on newly added partitions for each table space defined on the database partition group. Rules: v Each partition specified by number must be defined in the db2nodes.cfg file (SQLSTATE 42729). v Each db-partition-number listed in the ON DBPARTITIONNUMS clause must be for a unique partition (SQLSTATE 42728). v A valid partition number is between 0 and 999 inclusive (SQLSTATE 42729). v A partition cannot appear in both the ADD and DROP clauses (SQLSTATE 42728). v There must be at least one partition remaining in the database partition group. The last partition cannot be dropped from a database partition group (SQLSTATE 428C0). v If neither the LIKE DBPARTITIONNUM clause nor the WITHOUT TABLESPACES clause is specified when adding a partition, the default is to use the lowest partition number of the existing partitions in the database ALTER DATABASE PARTITION GROUP 16 SQL Reference, Volume 2
ALTER DATABASE PARTITION GROUP partition group(say it is 2)and proceed as if LIKE DBPARTITIONNUM 2 had been specified. For an existing partition to be used as the default it must have containers defined for all the table spaces in the database partition group(column IN_USE of SYSCAT. DBPARTITIONGROUPDEF is not T) Notes Compatibilities For compatibility with previous versions of DB2 NODE can be specified in place of DBPARTITIONNUM NODES can be specified in place of DBPARTITIONNUMS NODEGROUP can be specified in place of DATABASE PARTITION GROUP When a partition is added to a database partition group, a catalog entry is made for the partition(see SYSCAT. DBPARTITIONGROUPDEF) The partitioning map is changed immediately to include the new partition along with an indicator(IN_USE) that the partition is in the partitioning map if either no table spaces are defined in the database partition group or no tables are defined in the table spaces defined in the database partition group and the WITHOUT TABLESPACES clause was not specified The partitioning map is not changed and the indicator(IN_USE) is set to indicate that the partition is not included in the partitioning map if either tables exist in table spaces in the database partition group or table spaces exist in the database partition group and the WITHOUT TABLESPACES clause was specified To change the partitioning map, the REDISTRIBUTE DATABASE PARTITION GROUP command must be used. This redistributes any data, hanges the partitioning map, and changes the indicator. Table space containers need to be added before attempting to redistribute data if the WITHOUT TABLESPACES clause was specified When a partition is dropped from a database partition group, the catalog entry for the partition(see SYSCAT. DBPARTITIONGROUPDEF) is updated If there are no tables defined in the table spaces defined in the database partition group, the partitioning map is changed immediately to exclude th dropped partition and the entry for the partition in the database partition group is dropped If tables exist, the partitioning map is not changed and the indicator(IN_ USE) is set to indicate that the partition is waiting to be dropped. The REDISTRIBUTE DATABASE PARTITION GROUP command must be used to redistribute the data and drop the entry for the partition from the database partition group Chapter 1.Statements 17
partition group (say it is 2) and proceed as if LIKE DBPARTITIONNUM 2 had been specified. For an existing partition to be used as the default it must have containers defined for all the table spaces in the database partition group (column IN_USE of SYSCAT.DBPARTITIONGROUPDEF is not ’T’). Notes: v Compatibilities – For compatibility with previous versions of DB2: - NODE can be specified in place of DBPARTITIONNUM - NODES can be specified in place of DBPARTITIONNUMS - NODEGROUP can be specified in place of DATABASE PARTITION GROUP v When a partition is added to a database partition group, a catalog entry is made for the partition (see SYSCAT.DBPARTITIONGROUPDEF). The partitioning map is changed immediately to include the new partition along with an indicator (IN_USE) that the partition is in the partitioning map if either: – no table spaces are defined in the database partition group or – no tables are defined in the table spaces defined in the database partition group and the WITHOUT TABLESPACES clause was not specified. The partitioning map is not changed and the indicator (IN_USE) is set to indicate that the partition is not included in the partitioning map if either: – tables exist in table spaces in the database partition group or – table spaces exist in the database partition group and the WITHOUT TABLESPACES clause was specified. To change the partitioning map, the REDISTRIBUTE DATABASE PARTITION GROUP command must be used. This redistributes any data, changes the partitioning map, and changes the indicator. Table space containers need to be added before attempting to redistribute data if the WITHOUT TABLESPACES clause was specified. v When a partition is dropped from a database partition group, the catalog entry for the partition (see SYSCAT.DBPARTITIONGROUPDEF) is updated. If there are no tables defined in the table spaces defined in the database partition group, the partitioning map is changed immediately to exclude the dropped partition and the entry for the partition in the database partition group is dropped. If tables exist, the partitioning map is not changed and the indicator (IN_USE) is set to indicate that the partition is waiting to be dropped. The REDISTRIBUTE DATABASE PARTITION GROUP command must be used to redistribute the data and drop the entry for the partition from the database partition group. ALTER DATABASE PARTITION GROUP Chapter 1. Statements 17
ALTER DATABASE PARTITION GROUP Assume that you have a six-partition database that has the following partitions: 0, 1, 2, 5, 7, and 8. Two partitions are added to the system with partition numbers 3 and 6 Assume that you want to add partitions 3 and 6 to a database partition group called MAXGROUP and have table space containers like those on partition 2. The statement is as follows ALTER DATABASE PARTITION GROUP MAXGROUP ADD DBPARTITIONNUMS (3, 6)LIKE DBPARTITIONNUM 2 Assume that you want to drop partition 1 and add partition 6 to database partition group MEDGROUP. You will define the table space containers separately for partition 6 using ALTER TABLESPACE. The statement is as follows ALTER SE PARTITION GROUP MEDGROUP ITIONNUM (6)WITHOUT TABLESPACES DROP RTITIONNUM (1) Related concepts Data partitioning across multiple partitions" in the SQL Reference, Volume 1 18 SQL Reference, Volume 2
Example: Assume that you have a six-partition database that has the following partitions: 0, 1, 2, 5, 7, and 8. Two partitions are added to the system with partition numbers 3 and 6. v Assume that you want to add partitions 3 and 6 to a database partition group called MAXGROUP and have table space containers like those on partition 2. The statement is as follows: ALTER DATABASE PARTITION GROUP MAXGROUP ADD DBPARTITIONNUMS (3,6)LIKE DBPARTITIONNUM 2 v Assume that you want to drop partition 1 and add partition 6 to database partition group MEDGROUP. You will define the table space containers separately for partition 6 using ALTER TABLESPACE. The statement is as follows: ALTER DATABASE PARTITION GROUP MEDGROUP ADD DBPARTITIONNUM(6)WITHOUT TABLESPACES DROP DBPARTITIONNUM(1) Related concepts: v “Data partitioning across multiple partitions” in the SQL Reference, Volume 1 ALTER DATABASE PARTITION GROUP 18 SQL Reference, Volume 2
ALTER FUNCTION ALTER FUNCTION The ALTER FUNCTION statement modifies the properties of an existing Invocation: This statement can be embedded in an application pro tement that can be n or Issu the use of dynamic SQL statements. It is an executable st dynamically prepared only if DYNAMICRULES run behavior is in effect for the package(SQLSTATE 42509) Authorization The privileges held by the authorization id of the statement must include at least one of the following SYSADM or DBADM authority aLtERiN Privilege on the schema of the function Definer of the function as recorded in the definer column of SYSCAT. ROUTINES To alter the EXTERNAL NAME of a function, the privileges held by the authorization id of the statement must also include at least one of the following SYSADM or DBADM authority CREATE_EXTERNAL_ ROUTINE authority on the database To alter a function to be not fenced, the privileges held by the authorization ID of the statement must also include at least one of the following: SYSADM or DBADM authority CREATE_ NOT_ FENCED_ ROUTINE authority on the database To alter a function to be fenced, no additional authorities or privileges are required If the authorization ID has insufficient authority to perform the operation,an error(SQLSTaTE 42502)is raised Syntax: Chapter 1. Statements 19
ALTER FUNCTION The ALTER FUNCTION statement modifies the properties of an existing function. 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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509). Authorization: The privileges held by the authorization ID of the statement must include at least one of the following: v SYSADM or DBADM authority v ALTERIN privilege on the schema of the function v Definer of the function, as recorded in the DEFINER column of SYSCAT.ROUTINES To alter the EXTERNAL NAME of a function, the privileges held by the authorization ID of the statement must also include at least one of the following: v SYSADM or DBADM authority v CREATE_EXTERNAL_ROUTINE authority on the database To alter a function to be not fenced, the privileges held by the authorization ID of the statement must also include at least one of the following: v SYSADM or DBADM authority v CREATE_NOT_FENCED_ROUTINE authority on the database To alter a function to be fenced, no additional authorities or privileges are required. If the authorization ID has insufficient authority to perform the operation, an error (SQLSTATE 42502) is raised. Syntax: ALTER FUNCTION Chapter 1. Statements 19
ALTER FUNCTION bh--ALTER- function-designator EXTERNAL NAME string identifie FENCED I FENCED THREADSAFE T THREADSAF Description: function-designaton Uniquely identifies the function to be altered EXTERNAL NAME 'stringor identifie Identifies the name of the user-written code that implements the function This option can only be specified when altering external functions (SQLSTATE 42849) FENCED or NOT FENCED specifies whether the function is considered safe to run in the database manager operating environment's process or address space (NOT FENCED), or not(FENCED). Most functions have the option of running as FenCed or NOt FenceD If a function is altered to be FENCED, the database manager insulates its internal resources(for example, data buffers) from access by the function In general, a function running as FENCED will not perform as well as a similar one running as Not Fenced CAUTION Use of NoT FENCED for functions that were not adequately coded, reviewed, and tested can compromise the integrity of DB2. DB2 takes some precautions against many of the common types of inadvertent failures that might occur, but cannot guarantee complete integrity when NOT FENCED user-defined functions are used A function declared as NOT ThrEadsafe cannot be altered to be not FENCED(SQLSTATE 42613) If a function has any parameters defined AS LOCATOR, and was defined with the NO SQL option, the function cannot be altered to be FENCED (SQLSTATE 42613 This option cannot be altered for LANGUAGE OLE or OLEDB functions (SQLSTATE 42849) THREADSAFE or NOT THREADSAFE pecifies whether the function is considered safe to run in the same process as other routines(THREADSAFE), or not (NOT THREADSAFE) 20 SQL Reference, Volume 2
ALTER function-designator EXTERNAL NAME 'string' identifier FENCED NOT FENCED THREADSAFE NOT THREADSAFE Description: function-designator Uniquely identifies the function to be altered. EXTERNAL NAME ’string’ or identifier Identifies the name of the user-written code that implements the function. This option can only be specified when altering external functions (SQLSTATE 42849). FENCED or NOT FENCED Specifies whether the function is considered safe to run in the database manager operating environment’s process or address space (NOT FENCED), or not (FENCED). Most functions have the option of running as FENCED or NOT FENCED. If a function is altered to be FENCED, the database manager insulates its internal resources (for example, data buffers) from access by the function. In general, a function running as FENCED will not perform as well as a similar one running as NOT FENCED. CAUTION: Use of NOT FENCED for functions that were not adequately coded, reviewed, and tested can compromise the integrity of DB2. DB2 takes some precautions against many of the common types of inadvertent failures that might occur, but cannot guarantee complete integrity when NOT FENCED user-defined functions are used. A function declared as NOT THREADSAFE cannot be altered to be NOT FENCED (SQLSTATE 42613). If a function has any parameters defined AS LOCATOR, and was defined with the NO SQL option, the function cannot be altered to be FENCED (SQLSTATE 42613). This option cannot be altered for LANGUAGE OLE or OLEDB functions (SQLSTATE 42849). THREADSAFE or NOT THREADSAFE Specifies whether the function is considered safe to run in the same process as other routines (THREADSAFE), or not (NOT THREADSAFE). ALTER FUNCTION 20 SQL Reference, Volume 2