- IBM DB2 9.7 Advanced Application Developer Cookbook
- Sanjay Kumar Mohankumar Saraswatipura
- 521字
- 2021-08-20 15:42:19
Using the ALTER TABLE operation in a single transaction
When we perform the ALTER TABLE operations, such as dropping a column, in earlier versions of DB2, modifying the data types would force the database administrator to perform REORG on the table before any application would work on the table. Now in DB2 9.7, one can perform an unlimited number of table alterations within a single unit of work. This will allow a data modeler to manage the alteration of the column easily.
Getting ready
By default, autocommit is ON in DB2. That means each SQL statement we execute is a unit of work or a transaction. There are many ways to execute the unit of work; the simplest one is to turn off autocommit, or use db2 + c at the beginning, or use atomic procedures.
The different options available for the DB2 command can be listed using the following command:
C:\>db2 "? options" db2 [option ...] [db2-command | sql-statement | [? [phrase | message | sqlstate | class-code]]] option: -a, -c, -d, -e{c|s}, -finfile, -i, -lhistfile, -m, -n, -o, -p, -q, -rreport, -s, -t, -td;, -v, -w, -x, -zoutputfile. Option Description Default Setting ------ ---------------------------------------- --------------- -a Display SQLCA OFF -c Auto-commit ON
How to do it...
In this section, we'll see how multiple ALTER TABLE operations are allowed in three different units of work. This can be implemented in any language, but the user must have privileges to alter a table.
For demonstration purposes, we have created the EMPLOYEEE table, which is a replica of EMPLOYEE of the sample database without the dependent objects. If there are dependent objects, we may have to drop them and perform the ALTER TABLE statement.
- Set autocommit to
ON:This is the default behavior for the DB2 prompt, but it can be explicitly specified as follows:C:\>db2 +c
- Alter a table: Since autocommit is set to
ON, it means that every statement executed is a transaction in itself. We will alter the same table in different transactions.ALTER TABLE EMPLOYEEE DROP COLUMN SALARY ALTER TABLE EMPLOYEEE ALTER COLUMN EDLEVEL DROP NOT NULL ALTER TABLE EMPLOYEEE DROP COLUMN SEX - Reorganize the table: Since we have dropped some columns from the table, the table goes into the
REORG PENDINGstate. To bring the table out of theREORG PENDINGstate, we can use theREORGcommand as follows:
REORG TABLE EMPLOYEEE
The following screenshot shows the sample output for the preceding statements:
How it works…
When a table is altered with operations, such as dropping a column, altering a column data type, or altering the nullability feature of a column, the table may be placed in a REORG PENDING state. While the table is in the REORG state, no queries can be run until the table is brought online from the REORG PENDING state by executing the REORG command. Starting with DB2 9.7, one can perform an unlimited number of ALTER TABLE statements in a single transaction with a maximum of three transactions in a row before the need for table reorganization. This reduces the maintenance window requirement, in the case of a huge data warehouse environment.
- UNIX編程藝術
- Mastering Visual Studio 2017
- The DevOps 2.3 Toolkit
- 零基礎學Scratch少兒編程：小學課本中的Scratch創意編程
- Cassandra Data Modeling and Analysis
- 你不知道的JavaScript（中卷）
- Jenkins Continuous Integration Cookbook（Second Edition）
- MongoDB，Express，Angular，and Node.js Fundamentals
- Spring MVC+MyBatis開發從入門到項目實踐（超值版）
- OpenCV 3計算機視覺：Python語言實現（原書第2版）
- C++從入門到精通（第6版）
- 寫給青少年的人工智能（Python版·微課視頻版）
- 零基礎學編程系列（全5冊）
- Building Microservices with Go
- 少年小魚的魔法之旅：神奇的Python