- 浏览: 435989 次
- 性别:
- 来自: 南京
文章分类
最新评论
-
pulsar_lxl:
请注明转载原文:http://hllvm.group.itey ...
JVM研究 -
goycejin:
感谢楼主,我也要及笔记了,这记性
Failed to execute goal org.apache.maven.plugins:maven-javadoc-plugin:2.8.1:jar -
lianglong2000:
我理解是 java -server 类名 是运行的jdk中的j ...
jdk,jre你真的懂吗? -
yanqlv:
有个问题,既然windows目录(Windows/system ...
jdk,jre你真的懂吗? -
yanqlv:
系统化的描述也挺重要,架构模式>设计模式,架构模式≈设计 ...
MVC是设计模式么?是框架么?
ALTER TABLE操作在日常开发中很常见,下面是摘自DB2官网关于ALTER TABLE操作的一段话
Perhaps the most important thing to realize when running an ALTER TABLE statement containing a REORG-recommended operation is that once the ALTER TABLE statement has executed, the table will be placed in the Reorg Pending state. This means that the table is inaccessible for almost all operations until you perform a REORG. See the ALTER TABLE statement in the SQL Referencefor the complete list of ALTER TABLE operations, some of which are also called REORG-recommended operations.
简单地说就是运行ALTER TABLE时要注意当前运行的语句是否需要执行REORG操作,对于这样的ALTER TABLE语句,如果不执行REORG操作的话,基本上目标表就不再可用。至于什么样的语句需要REORG,什么样的不需要,看SQL Reference去!下面是一个具体的例子演示:
CREATE TABLE my_test AS (
SELECT id,
...
sla_priority1_time,
sla_priority2_time,
sla_priority3_time,
sla_priority4_time,
CAST(NULL AS DECIMAL(11, 2)) AS approvedDouAmount,
CAST(NULL AS DECIMAL(4)) AS year
FROM fin_attributes
)
WITH NO DATA;
ALTER TABLE my_test ALTER COLUMN id SET GENERATED ALWAYS AS IDENTITY;
ALTER TABLE my_test ADD COLUMN datetime TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP;
ALTER TABLE my_test ALTER COLUMN sla_priority1_time DROP NOT NULL;
ALTER TABLE my_test ALTER COLUMN sla_priority2_time DROP NOT NULL;
ALTER TABLE my_test ALTER COLUMN sla_priority3_time DROP NOT NULL;
REORG TABLE my_test;
ALTER TABLE my_test ALTER COLUMN sla_priority4_time DROP NOT NULL;
REORG TABLE my_test;
INSERT INTO my_test (
...
sla_priority1_time,
sla_priority2_time,
sla_priority3_time,
sla_priority4_time,
approvedDouAmount,
year
)
SELECT ...
sla_priority1_time,
sla_priority2_time,
sla_priority3_time,
sla_priority4_time,
NVL(pg.approvedDouAmount, 0),
YEAR(NOW())
FROM fin_attributes f, projgrp pg
WHERE f.projgrp_id = pg.id
AND f.project_id IS NULL
AND f.fin_projgrp_id IS NULL
UNION
SELECT ...
sla_priority1_time,
sla_priority2_time,
sla_priority3_time,
sla_priority4_time,
NVL(p.approvedDouAmount, 0),
YEAR(NOW())
FROM fin_attributes f, project p
WHERE f.project_id = p.id
AND f.projgrp_id IS NULL;
代码有四种颜色,绿色代表不需要执行REORG的语句,红色代表需要执行REORG的语句,蓝色是REORG语句。从代码上可以看出,红色高亮语句虽然要求使用REORG,但不及时运行REORG还可以让后续的几个语句继续执行。原因是DB2允许最多三条语句处于Reorg Pending状态,假如去除第一个REORG,语句“ALTER TABLE my_test ALTER COLUMN sla_priority4_time DROP NOT NULL;”就会执行失败。
结论:如果不确定那个是需要REORG哪个是不需要REORG,索性都用上REORG;虽然在允许有三条语句处于Reorg Pending状态,但最好每条ALTER TABLE对应一个REORG,因为处于Reorg Pending状态的表有可能会阻碍后续操作。
PS:REORG TABLE本身是DB2的command,不是正常的SQL语句(Statement)。如果在非命令行环境中想使用REORG的话,可以像下面那样调用存储过程间接执行REORG操作,执行前确保你所使用的帐号有调用这个存储过程的权限:
CALL SYSPROC.ADMIN_CMD('reorg table my_test')
最后来一段用Java动态执行SQL语句或DB2命令的代码。
下面是ibm相关网站的一篇文章:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0023297.htm
Using the ALTER TABLE statement to alter columns of a table
Before creating your database, you need to consider the type and organization of the data that you want to store in it. You need to plan what kind of data your business might need to use and how your business will use that data. However, things change. Despite good planning, there might be new requirements that necessitate changes to the tables in the database. You can use the ALTER TABLE statement to alter the row format of table data: dropping columns, changing the types of columns, and certain other column attributes.
Restrictions on table access after ALTER TABLE statements containing REORG-recommended operations
It is important that you plan the implementation of the table alterations well. Perhaps the most important thing to realize when running an ALTER TABLE statement containing a REORG-recommended operation is that once the ALTER TABLE statement has executed, the table will be placed in the Reorg Pending state. This means that the table is inaccessible for almost all operations until you perform a REORG. See the ALTER TABLE statement in the SQL Reference for the complete list of ALTER TABLE operations, some of which are also called REORG-recommended operations.
After an ALTER TABLE statement containing REORG-recommended operations, you can execute only the following statements on a table:
REORG TABLE
DROP TABLE
ALTER TABLE
RENAME TABLE
To allow data recovery in case of a REORG failure, table data might be read using scan-based read only statements, that is, using TABLE SCAN statements. In addition, index-based table access is not allowed. If a table scan-based access is used instead of index-based access, you can also issue a SELECT statement from the table.
The following ALTER TABLE statements require row data validation, and are not allowed following a REORG-recommended ALTER. However, you can execute most of the other ALTER TABLE statements. The ALTER TABLE statements that you cannot use are those that require scanning of column data to verify the validity of the alteration operations. Specifically, this means that you cannot execute the following statements on a table:
ADD UNIQUE CONSTRAINT
ADD CHECK CONSTRAINT
ADD REFERENTIAL CONSTRAINT
ALTER COLUMN SET NOT NULL
ALTER TABLE ADD REFERENTIAL CONSTRAINT
ALTER TABLE ADD CONSTRAINT
ALTER TABLE ADD UNIQUE CONSTRAINT
Examples of ALTER TABLE statements containing REORG-recommended operations
In addition to placing restrictions on table access after an ALTER TABLE statement containing REORG-recommended operations, the DB2® database manager allows you to specify only three REORG-recommended operations before you perform a classic REORG and before additional REORG-recommended operations will succeed. For this reason, you should code each ALTER TABLE statement containing REORG-recommended operations to change the attributes of as many columns as possible. For example, if you specify the following sequence of ALTER TABLE statements containing only one REORG-recommended operation in each, you will be unable to specify any subsequent ALTER TABLE statements that would require a new row format until you perform a classic REORG:
ALTER TABLE foo DROP COLUMN C1
ALTER TABLE foo DROP COLUMN C2
ALTER TABLE foo DROP COLUMN C3
You could, however, replace the three ALTER TABLE statements with a single one:
ALTER TABLE foo DROP COLUMN C1 DROP COLUMN C2 DROP COLUMN C3
Since you can alter only one attribute per column in a single SQL statement--for example, type or nullability—it is possible that changing a column to a new format could require the use of more than one ALTER TABLE statement containing REORG-recommended operations. In such a case, it is important that the order of alterations not allow one alteration to preclude another due to the Reorg Pending state. This means that you should perform operations requiring table data access using the first ALTER TABLE statement containing REORG-recommended operations. For example, if column C1 is an integer and is NULLABLE and you want to change this column to be a NOT NULLABLE BIGINT, the following sequence will fail:
ALTER TABLE bar ALTER COLUMN C1 SET DATA TYPE BIGINT
ALTER TABLE bar ALTER COLUMN C1 SET NOT NULL
The reason for the failure is that the second ALTER TABLE statement requires a scan of the column C1 to see whether any rows contain the value NULL. Since the table is placed in Reorg Pending state after the first statement, the scan for the second statement cannot be performed.
However, the following sequence will succeed because the first statement does not access the data and does not put the table in Reorg Pending state:
ALTER TABLE bar ALTER COLUMN C1 SET NOT NULL
ALTER TABLE bar ALTER COLUMN C1 SET DATA TYPE BIGINT
You can perform many operations that alter a table that do not constitute REORG-recommended operations regardless of the number of REORG-recommended operations that you have specified. These include:
ADD COLUMN
ALTER COLUMN DEFAULT VALUE
RENAME TABLE
ALTER COLUMN SET DATA TYPE VARCHAR/VARGRAPHIC/CLOB/BLOB/DBCLOB
Concurrency during ALTER TABLE execution
Any ALTER TABLE statement requires exclusive access to a table, as it modifies in-memory structures. For certain statement options in particular, ALTER TYPE and DROP COLUMN—rows in table catalogs will be locked exclusively for UPDATE or DELETE. For this reason, once the ALTER TABLE statement completes, it is important that the unit of work containing the statement be committed or rolled back as soon as possible.
ALTER TABLE authority considerations
After using an ALTER TABLE statement containing REORG-recommended operations, you must use the classic REORG TABLE statement to make the table accessible again. Having ALTER authority on the table does not necessarily mean that you have the authority to use the REORG TABLE statement; you must have REORG authority.
CASCADE versus RESTRICT semantics when dropping columns
When dropping a column, DB2 must ensure that any database objects that are dependent on that column—for example, views, triggers, and indexes—are also updated. Two options are available when you specify an ALTER TABLE DROP COLUMN statement: CASCADE and RESTRICT. These options affect how dependent database objects are updated.
CASCADE
CASCADE, the default, automatically handles the dropping of database objects that are dependent on the column being dropped. You should use CASCADE only when you feel confident that you understand the full impact of such an operation. If you do not understand the database object dependencies well, using the CASCADE option might result in performance degradation as a result of implicitly dropping an index. Other side effects could include DML failures on views that were marked inoperative or data integrity issues stemming from inoperative triggers. The following objects are implicitly dropped when you use CASCADE:
Identity attributes
SQL routines
Indexes
Unique constraints
Triggers
Foreign key constraints
Primary key constraints (this will also cause the implicit deletion of any dependent foreign key constraints)
Check constraints
Generated column data
Views
Packages
RESTRICT
RESTRICT causes the ALTER TABLE statement to fail if any database object other than a package is found to have a dependency on the column being dropped. Often, it is difficult to generate the complete list of dependent objects for a particular column, but it might be desirable to evaluate each object to decide whether a replacement index should be created following a DROP COLUMN operation. In cases such as this, you might want to specify the RESTRICT option and remove or plan for the re-creation of each affected object. The object type and name first detected with a column-level dependency are returned as part of the error message if the ALTER TABLE statement fails.
Perhaps the most important thing to realize when running an ALTER TABLE statement containing a REORG-recommended operation is that once the ALTER TABLE statement has executed, the table will be placed in the Reorg Pending state. This means that the table is inaccessible for almost all operations until you perform a REORG. See the ALTER TABLE statement in the SQL Referencefor the complete list of ALTER TABLE operations, some of which are also called REORG-recommended operations.
简单地说就是运行ALTER TABLE时要注意当前运行的语句是否需要执行REORG操作,对于这样的ALTER TABLE语句,如果不执行REORG操作的话,基本上目标表就不再可用。至于什么样的语句需要REORG,什么样的不需要,看SQL Reference去!下面是一个具体的例子演示:
CREATE TABLE my_test AS (
SELECT id,
...
sla_priority1_time,
sla_priority2_time,
sla_priority3_time,
sla_priority4_time,
CAST(NULL AS DECIMAL(11, 2)) AS approvedDouAmount,
CAST(NULL AS DECIMAL(4)) AS year
FROM fin_attributes
)
WITH NO DATA;
ALTER TABLE my_test ALTER COLUMN id SET GENERATED ALWAYS AS IDENTITY;
ALTER TABLE my_test ADD COLUMN datetime TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP;
ALTER TABLE my_test ALTER COLUMN sla_priority1_time DROP NOT NULL;
ALTER TABLE my_test ALTER COLUMN sla_priority2_time DROP NOT NULL;
ALTER TABLE my_test ALTER COLUMN sla_priority3_time DROP NOT NULL;
REORG TABLE my_test;
ALTER TABLE my_test ALTER COLUMN sla_priority4_time DROP NOT NULL;
REORG TABLE my_test;
INSERT INTO my_test (
...
sla_priority1_time,
sla_priority2_time,
sla_priority3_time,
sla_priority4_time,
approvedDouAmount,
year
)
SELECT ...
sla_priority1_time,
sla_priority2_time,
sla_priority3_time,
sla_priority4_time,
NVL(pg.approvedDouAmount, 0),
YEAR(NOW())
FROM fin_attributes f, projgrp pg
WHERE f.projgrp_id = pg.id
AND f.project_id IS NULL
AND f.fin_projgrp_id IS NULL
UNION
SELECT ...
sla_priority1_time,
sla_priority2_time,
sla_priority3_time,
sla_priority4_time,
NVL(p.approvedDouAmount, 0),
YEAR(NOW())
FROM fin_attributes f, project p
WHERE f.project_id = p.id
AND f.projgrp_id IS NULL;
代码有四种颜色,绿色代表不需要执行REORG的语句,红色代表需要执行REORG的语句,蓝色是REORG语句。从代码上可以看出,红色高亮语句虽然要求使用REORG,但不及时运行REORG还可以让后续的几个语句继续执行。原因是DB2允许最多三条语句处于Reorg Pending状态,假如去除第一个REORG,语句“ALTER TABLE my_test ALTER COLUMN sla_priority4_time DROP NOT NULL;”就会执行失败。
结论:如果不确定那个是需要REORG哪个是不需要REORG,索性都用上REORG;虽然在允许有三条语句处于Reorg Pending状态,但最好每条ALTER TABLE对应一个REORG,因为处于Reorg Pending状态的表有可能会阻碍后续操作。
PS:REORG TABLE本身是DB2的command,不是正常的SQL语句(Statement)。如果在非命令行环境中想使用REORG的话,可以像下面那样调用存储过程间接执行REORG操作,执行前确保你所使用的帐号有调用这个存储过程的权限:
CALL SYSPROC.ADMIN_CMD('reorg table my_test')
最后来一段用Java动态执行SQL语句或DB2命令的代码。
//*************************************************************************** // Licensed Materials - Property of IBM // // Governed under the terms of the International // License Agreement for Non-Warranted Sample Code. // // (C) COPYRIGHT International Business Machines Corp. 1997 - 2006 // All Rights Reserved. // // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. //*************************************************************************** // // SOURCE FILE NAME: TbOnlineInx.java // // SAMPLE: How to create and reorg indexes on a table // // SQL STATEMENTS USED: // INCLUDE // CREATE INDEX // DROP INDEX // REORG // LOCK // // JAVA 2 CLASSES USED: // Statement // ResultSet // File // FileWriter // Process // BufferedReader // InputStreamReader // // Classes used from Util.java are: // Db // JdbcException // // OUTPUT FILE: TbOnlineInx.out (available in the online documentation) // Output will vary depending on the JDBC driver connectivity used. //*************************************************************************** // // For more information on the sample programs, see the README file. // // For information on developing Java applications see the Developing Java Applications book. // // For information on using SQL statements, see the SQL Reference. // // For the latest information on programming, compiling, and running DB2 // applications, visit the DB2 Information Center at // http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp //**************************************************************************/ import java.sql.*; import java.lang.*; import java.io.*; public class TbOnlineInx { public static void main(String argv[]) { try { Db db = new Db(argv); System.out.println(); System.out.println( "THIS SAMPLE SHOWS HOW TO CREATE AND REORG ONLINE INDEXES/n" + "ON TABLES."); // connect to the 'sample' database db.connect(); // create online index on a table createIndex(db.con); // reorg online index on a table reorgIndex(db.con); // drop online index created dropIndex(db.con); // disconnect from the 'sample' database db.disconnect(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e); jdbcExc.handle(); } } // main // How to create an index on a table with different levels // of access to the table like read-write, read-only, no access static void createIndex(Connection conn) throws Exception { System.out.print( "/n-----------------------------------------------------------" + "/nUSE THE SQL STATEMENT/n" + " CREATE INDEX/n" + "TO CREATE AN INDEX/n"); // create an online index with read-write access to the table System.out.print( "/nTo create an index on a table allowing read-write access/n" + "to the table, use the following SQL command:/n/n" + " CREATE INDEX index1 ON employee (lastname ASC)/n"); Statement stmt = conn.createStatement(); stmt.executeUpdate("CREATE INDEX index1 ON employee (lastname ASC)"); conn.commit(); dropIndex(conn); // create an index on a table while allowing only read access to it System.out.println( "/nTo create an index on a table allowing only read access/n" + "to the table, use the following two SQL commands:/n/n" + " LOCK TABLE employee IN SHARE MODE/n" + " CREATE INDEX index1 ON employee (lastname ASC)"); stmt.executeUpdate("LOCK TABLE employee IN SHARE MODE"); conn.commit(); stmt.executeUpdate("CREATE INDEX index1 ON employee (lastname ASC)"); conn.commit(); dropIndex(conn); // create an online index allowing no access to the table System.out.println( "/nTo create an index on a table allowing no access to the /n" + "table (only uncommitted readers allowed), use the /n" + "following two SQL statements:/n/n" + " LOCK TABLE employee IN EXCLUSIVE MODE/n" + " CREATE INDEX index1 ON employee (lastname ASC)"); stmt.executeUpdate("LOCK TABLE employee IN EXCLUSIVE MODE"); conn.commit(); stmt.executeUpdate("CREATE INDEX index1 ON employee (lastname ASC)"); conn.commit(); stmt.close(); } // createIndex // Create 3 CLP files for REORG command with write, read and no access, // respectively. static void createFiles(Connection conn) throws Exception { // get fully qualified name of the table String tableName = "EMPLOYEE"; String schemaName = getSchemaName(conn, tableName); String fullTableName = schemaName + "." + tableName; // reorg command has to be executed with three different options, namely, // 'with write access', 'with read access' and 'with no access' String[] fileNames = { "ReorgCmdAllowWrite.db2", "ReorgCmdAllowRead.db2", "ReorgCmdAllowNone.db2" }; String[] options = { " WRITE ACCESS", " READ ACCESS", " NO ACCESS" }; for (int i = 0; i < 3; i++) { // create a CLP file with the REORG command and execute the file File outputFile = new File(fileNames[i]); FileWriter out = new FileWriter(outputFile); out.write("CONNECT TO SAMPLE;/n"); out.write("REORG INDEXES ALL FOR TABLE " + fullTableName + " ALLOW" + options[i] + ";/n"); out.write("CONNECT RESET;"); out.close(); // on exit, delete the temporary files created outputFile.deleteOnExit(); } } //createFiles // How to reorg an index on a table with different levels of // access to the table like read-write, read-only, no access static void reorgIndex(Connection conn) { System.out.print( "/n-----------------------------------------------------------/n" + "/nUSE THE SQL STATEMENT:/n"+ " REORG/n" + "TO REORGANIZE A TABLE./n"); String[] fileNames = { "ReorgCmdAllowWrite.db2", "ReorgCmdAllowRead.db2", "ReorgCmdAllowNone.db2" }; String[] options = { " write access", " read access", " no access" }; try { // create 3 files with REORG commands createFiles(conn); for (int i = 0; i < 3; i++) { System.out.println( "/nReorganize the indexes on a table allowing" + options[i] + "/n-----------------------------------------------------------"); String s = null; String execCmd = "db2 -tvf " + fileNames[i]; // execute the command to run the CLP file Process p = Runtime.getRuntime().exec(execCmd); // open streams for the process's input and error BufferedReader stdInput = new BufferedReader(new InputStreamReader(p.getInputStream())); BufferedReader stdError = new BufferedReader(new InputStreamReader(p.getErrorStream())); // read the output from the command and set the output variable with // the value while ((s = stdInput.readLine()) != null) { System.out.println(s); } // read any errors from the attempted command and set the error // variable with the value while ((s = stdError.readLine()) != null) { System.out.println(s); } p.destroy(); } // for } // try catch (IOException e) { e.printStackTrace(); System.exit(-1); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e); jdbcExc.handle(); } } // reorgIndex // How to drop the index on a table static void dropIndex(Connection conn) { System.out.println( "/nUSE THE SQL STATEMENT/n" + " DROP/n" + "TO DROP AN INDEX:"); try { // drop the indexes System.out.println( " Execute the statement/n" + " DROP INDEX index1/n" + "/n-----------------------------------------------------------"); Statement stmt = conn.createStatement(); stmt.executeUpdate("DROP INDEX index1"); conn.commit(); stmt.close(); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e); jdbcExc.handle(); } } // dropIndex // function to get the schema name for a particular table static String getSchemaName(Connection conn, String tableName) throws Exception { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT tabschema "+ " FROM syscat.tables "+ " WHERE tabname = '"+ tableName + "'"); boolean result = rs.next(); String schemaName = rs.getString("tabschema"); rs.close(); stmt.close(); // remove the trailing white space characters from schemaName before // returning it to the calling function return schemaName.trim(); } // getSchemaName } // TbOnlineInx
下面是ibm相关网站的一篇文章:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0023297.htm
Using the ALTER TABLE statement to alter columns of a table
Before creating your database, you need to consider the type and organization of the data that you want to store in it. You need to plan what kind of data your business might need to use and how your business will use that data. However, things change. Despite good planning, there might be new requirements that necessitate changes to the tables in the database. You can use the ALTER TABLE statement to alter the row format of table data: dropping columns, changing the types of columns, and certain other column attributes.
Restrictions on table access after ALTER TABLE statements containing REORG-recommended operations
It is important that you plan the implementation of the table alterations well. Perhaps the most important thing to realize when running an ALTER TABLE statement containing a REORG-recommended operation is that once the ALTER TABLE statement has executed, the table will be placed in the Reorg Pending state. This means that the table is inaccessible for almost all operations until you perform a REORG. See the ALTER TABLE statement in the SQL Reference for the complete list of ALTER TABLE operations, some of which are also called REORG-recommended operations.
After an ALTER TABLE statement containing REORG-recommended operations, you can execute only the following statements on a table:
REORG TABLE
DROP TABLE
ALTER TABLE
RENAME TABLE
To allow data recovery in case of a REORG failure, table data might be read using scan-based read only statements, that is, using TABLE SCAN statements. In addition, index-based table access is not allowed. If a table scan-based access is used instead of index-based access, you can also issue a SELECT statement from the table.
The following ALTER TABLE statements require row data validation, and are not allowed following a REORG-recommended ALTER. However, you can execute most of the other ALTER TABLE statements. The ALTER TABLE statements that you cannot use are those that require scanning of column data to verify the validity of the alteration operations. Specifically, this means that you cannot execute the following statements on a table:
ADD UNIQUE CONSTRAINT
ADD CHECK CONSTRAINT
ADD REFERENTIAL CONSTRAINT
ALTER COLUMN SET NOT NULL
ALTER TABLE ADD REFERENTIAL CONSTRAINT
ALTER TABLE ADD CONSTRAINT
ALTER TABLE ADD UNIQUE CONSTRAINT
Examples of ALTER TABLE statements containing REORG-recommended operations
In addition to placing restrictions on table access after an ALTER TABLE statement containing REORG-recommended operations, the DB2® database manager allows you to specify only three REORG-recommended operations before you perform a classic REORG and before additional REORG-recommended operations will succeed. For this reason, you should code each ALTER TABLE statement containing REORG-recommended operations to change the attributes of as many columns as possible. For example, if you specify the following sequence of ALTER TABLE statements containing only one REORG-recommended operation in each, you will be unable to specify any subsequent ALTER TABLE statements that would require a new row format until you perform a classic REORG:
ALTER TABLE foo DROP COLUMN C1
ALTER TABLE foo DROP COLUMN C2
ALTER TABLE foo DROP COLUMN C3
You could, however, replace the three ALTER TABLE statements with a single one:
ALTER TABLE foo DROP COLUMN C1 DROP COLUMN C2 DROP COLUMN C3
Since you can alter only one attribute per column in a single SQL statement--for example, type or nullability—it is possible that changing a column to a new format could require the use of more than one ALTER TABLE statement containing REORG-recommended operations. In such a case, it is important that the order of alterations not allow one alteration to preclude another due to the Reorg Pending state. This means that you should perform operations requiring table data access using the first ALTER TABLE statement containing REORG-recommended operations. For example, if column C1 is an integer and is NULLABLE and you want to change this column to be a NOT NULLABLE BIGINT, the following sequence will fail:
ALTER TABLE bar ALTER COLUMN C1 SET DATA TYPE BIGINT
ALTER TABLE bar ALTER COLUMN C1 SET NOT NULL
The reason for the failure is that the second ALTER TABLE statement requires a scan of the column C1 to see whether any rows contain the value NULL. Since the table is placed in Reorg Pending state after the first statement, the scan for the second statement cannot be performed.
However, the following sequence will succeed because the first statement does not access the data and does not put the table in Reorg Pending state:
ALTER TABLE bar ALTER COLUMN C1 SET NOT NULL
ALTER TABLE bar ALTER COLUMN C1 SET DATA TYPE BIGINT
You can perform many operations that alter a table that do not constitute REORG-recommended operations regardless of the number of REORG-recommended operations that you have specified. These include:
ADD COLUMN
ALTER COLUMN DEFAULT VALUE
RENAME TABLE
ALTER COLUMN SET DATA TYPE VARCHAR/VARGRAPHIC/CLOB/BLOB/DBCLOB
Concurrency during ALTER TABLE execution
Any ALTER TABLE statement requires exclusive access to a table, as it modifies in-memory structures. For certain statement options in particular, ALTER TYPE and DROP COLUMN—rows in table catalogs will be locked exclusively for UPDATE or DELETE. For this reason, once the ALTER TABLE statement completes, it is important that the unit of work containing the statement be committed or rolled back as soon as possible.
ALTER TABLE authority considerations
After using an ALTER TABLE statement containing REORG-recommended operations, you must use the classic REORG TABLE statement to make the table accessible again. Having ALTER authority on the table does not necessarily mean that you have the authority to use the REORG TABLE statement; you must have REORG authority.
CASCADE versus RESTRICT semantics when dropping columns
When dropping a column, DB2 must ensure that any database objects that are dependent on that column—for example, views, triggers, and indexes—are also updated. Two options are available when you specify an ALTER TABLE DROP COLUMN statement: CASCADE and RESTRICT. These options affect how dependent database objects are updated.
CASCADE
CASCADE, the default, automatically handles the dropping of database objects that are dependent on the column being dropped. You should use CASCADE only when you feel confident that you understand the full impact of such an operation. If you do not understand the database object dependencies well, using the CASCADE option might result in performance degradation as a result of implicitly dropping an index. Other side effects could include DML failures on views that were marked inoperative or data integrity issues stemming from inoperative triggers. The following objects are implicitly dropped when you use CASCADE:
Identity attributes
SQL routines
Indexes
Unique constraints
Triggers
Foreign key constraints
Primary key constraints (this will also cause the implicit deletion of any dependent foreign key constraints)
Check constraints
Generated column data
Views
Packages
RESTRICT
RESTRICT causes the ALTER TABLE statement to fail if any database object other than a package is found to have a dependency on the column being dropped. Often, it is difficult to generate the complete list of dependent objects for a particular column, but it might be desirable to evaluate each object to decide whether a replacement index should be created following a DROP COLUMN operation. In cases such as this, you might want to specify the RESTRICT option and remove or plan for the re-creation of each affected object. The object type and name first detected with a column-level dependency are returned as part of the error message if the ALTER TABLE statement fails.
发表评论
-
数据库事务隔离级别
2015-05-20 19:34 924数据库事务的隔离级别有4个,由低到高依次为Read unco ... -
数据库(Database)
2014-01-10 21:23 868一、数据库(Database) 数据库是一个数据的 ... -
oracle执行计划
2013-10-24 15:59 12151.什么是执行计划 所谓执行计划,顾名思义,就是对 ... -
如何查询DB2的表和表结构
2013-03-20 16:58 2055select * from sysibm.systables ... -
Group by与having理解
2012-07-17 12:57 1071注意:select 后的字段,必须要么包含在group by中 ... -
DB2错误信息(按sqlcode排序)
2012-07-04 15:45 1341sqlcode sqlstate 说明 000 0 ... -
JDBC驱动程序的四种类型
2012-06-06 22:07 13201、JDBC框架 JDBC(Java DataBase Co ...
相关推荐
- 更新数据:`UPDATE`语句在两个系统中基本一致,但Oracle允许在单个`UPDATE`语句中更新多个表,DB2通常限制为单表更新。 - 删除数据:`DELETE FROM`在两者中也是相同的,但Oracle的`TRUNCATE TABLE`用于快速删除...
在DB2数据库系统中,有时我们需要更改现有表中列的数据类型以适应新的业务需求或提高数据处理效率。本文将详细介绍如何在DB2中修改表的数据类型,并通过一个具体的例子来说明整个过程。 #### 标题与描述中的知识点...
- **命令**:`db2 reorg table tb1` - **说明**:对tb1表进行重组,提高性能。 - **重组检查** - **命令**:`db2 reorgchk` - **说明**:检查表是否需要重组。 #### 五、缓冲池管理 - **查看缓冲池** - **...
#### 二十三、为什么在ODS中大部分表不需要建索引? 在ODS中,由于数据主要用来支持报表和分析,通常查询模式固定,因此很多表不需要建立索引。 **需要建立索引的情况:** 1. 经常作为查询条件的字段。 2. 高频...
- **UPDATE不能用一个表中的记录为条件修改另一个表中的记录**:DB2不支持跨表更新操作。 - **如果显示调用存储过程时传NULL值要注意**:在调用存储过程时传递NULL值可能会导致意外行为。 #### 四、DB2编程性能注意...
尽管文档中提到CURSOR不能定义为WITH UR,但在实际操作中,DB2允许使用`WITH UR`关键字与CURSOR结合。 #### CURSOR ORDER BY 以后不能FOR UPDATE DB2中确实存在这样的限制:带有`ORDER BY`子句的CURSOR不能使用`FOR...
### DB2培训总结知识点 #### 一、DB2概述 **DB2**是IBM公司研发的一款关系型数据库管理系统,...以上总结了DB2的关键特性和历史发展,以及日常管理和维护中常见的操作指令,为DB2的学习者和使用者提供了全面的视角。
5. **UPDATE不能用一个表中的记录为条件修改另一个表中的记录:** 更新操作通常只能在一个表上进行,跨表更新需要使用更复杂的逻辑。 #### 四、DB2编程性能注意事项 1. **大数据的导表的使用:** 使用`EXPORT`、`LOAD...
在DB2中执行存储在文件中的脚本通常需要使用`db2 -tf`命令,其中`-t`表示脚本是文本格式,`-f`后跟脚本文件名。 ##### 3.2 建存储过程时CREATE后一定不要用TAB键 在编写存储过程时,使用`CREATE OR REPLACE ...
- **UPDATE不能用一个表中的记录为条件修改另一个表中的记录**:在DB2中,这样的操作是不允许的。 - **如果显示调用存储过程时传NULL值要注意**:处理NULL值时需要特别注意,确保不会导致错误。 #### 四、DB2编程...
+39501628 - 设置了无效的优化提示,原因代码指定了为什么,忽略优化提示** - **描述**:使用的优化提示无效,查询优化器将忽略这些提示。 - **处理方法**:检查优化提示的有效性和适用性。 **25. +40201521 - ...
5. **UPDATE不能用一个表中的记录为条件修改另一个表中的记录:** 在DB2中,不能直接使用一个表中的记录来更新另一个表中的数据。 6. **如果显示调用存储过程时传NULL值要注意:** 在调用存储过程时传递NULL值可能会...
- 建表后调整:`ALTERTABLE tablename COMPRESSYES;` - **查询压缩属性**:可以通过查询系统表`systables`的`COMPRESSION`字段来查看表是否可被压缩(`R`表示可压缩)。 #### 二、更新表统计信息(RUNSTATS) - **...
虽然视图通常是只读的,但 DB2 提供了一个名为 IMPORT 的工具,它允许在某些情况下将数据导入到视图中。需要注意的是,并非所有视图都支持导入操作。 ##### 9. 成功恢复表空间后的状态(State After Successful ...
- **说明**:设置了无效的优化提示,原因代码指定了为什么,忽略优化提示。 - **处理方法**:根据原因代码调整优化提示,或考虑禁用提示。 24. **SQLCODE +402 (SQLSTATE 01521)** - **说明**:未知的位置。 - ...