- IBM DB2 9.7 Advanced Administration Cookbook
- Adrian Neagu Robert Pelletier
- 838字
- 2021-08-20 15:33:21
Altering databases
This topic involves making changes to a database's characteristics. We will see this command from the DB2 point of view and from an Oracle point of view. This may help clear misunderstandings from one environment to the other.
How to do it…
As understood in DB2, ALTER DATABASE
only has one function. Those of you coming from an Oracle DBA background should read on to From Oracle to DB2..., for further explanations.
The ALTER DATABASE
command lets you add or remove storage paths from the list of storage paths used for automatic storage table spaces.
Add storage path:
[db2inst1@nodedb21 ~]$ db2 "ALTER DATABASE NAV ADD STORAGE ON '/data1/db2'" DB20000I The SQL command completed successfully.
Remove storage path:
[db2inst1@nodedb21 ~]$ db2 "ALTER DATABASE NAV DROP STORAGE ON '/data1/db2'" DB20000I The SQL command completed successfully.
How it works…
Adding a storage path to a manual storage database makes it an automatic storage database. Existing manual storage table spaces are not converted to automatic storage table spaces.
- Regular and large table spaces:
Existing automatic storage table spaces will not use the new path in an initial phase.
- Temporary table spaces:
The database must be restarted to enable use of new storage paths for temporary table spaces.
From Oracle to DB2
For those of you that have more of an Oracle background, the ALTER DATABASE
is not the same. The following sections discuss Oracle ALTER DATABASE-related
tasks and how to carry them out in DB2.
Starting and stopping an instance (an instance = database) in Oracle is done with the ALTER DATABASE
command, which is NOT the case with DB2. Make sure you want to shut down the instance, not just a database. This will shut down all databases in the instance.
- Start instance:
[db2inst1@nodedb21 ~]$ db2start SQL1063N DB2START processing was successful. [db2inst1@nodedb21 ~]$
- Stop instance:
[db2inst1@nodedb21 ~]$ db2stop SQL1064N DB2STOP processing was successful.
In DB2, a START DBM/STOP DBM
is done at the instance level, so all databases from the instance are started/shut down.
Certain parameter changes necessitate rebouncing a database. Remember, in DB2 there may be more than one database per instance. If you need to start/stop a single database from an instance you use the activate/deactivate database command.
- Start database:
[db2inst1@nodedb21 ~]$ db2 connect reset DB20000I The SQL command completed successfully.[db2inst1@ nodedb21 ~]$ db2 activate database pos DB20000I The ACTIVATE DATABASE command completed successfully.
- Stop database:
Make sure there are no connections first:
[db2inst1@nodedb21 ~]$ db2 connect reset DB20000I The SQL command completed successfully.[db2inst1@ nodedb21 ~]$ db2 deactivate database pos DB20000I The DEACTIVATE DATABASE command completed successfully.
Adding or dropping containers (Oracle datafile) is done with DB2's ALTER TABLESPACE
command. However, you cannot rename a container.
db2 ALTER TABLESPACE NAV_TBLS DROP ( FILE '/data/db2/db2inst1/NODE0000/nav/nav_tbls.dbf' )
Log files are managed by the get/update database configuration command.
- Change archiving mode:
db2 update db cfg using logretain recovery deferred
- Change archive log destination:
db2 update db cfg using newlogpath /data/db2/NODE0000/pos/ db2logdir deferred
A backup should be made after changing the logging mode. Within the control center, you will be prompted to make a backup after changing the logging mode.
How it works...
For those having more of a DB2 background, Oracle's ALTER DATABASE
command will have a broader scope of activity than its DB2 counterpart.
There's more...
Other functionalities from Oracle's ALTER DATABASE
command are also specific to DB2 and will be covered in subsequent chapters.
There is no equivalent to Oracle's control files in DB2, so there is no replacement to manipulate those kinds of files in DB2.
You can also issue QUIESCE INSTANCE
and all databases will be in quiesced mode. Only users having SYSADM, SYSMAINT
, or SYSCTRL
authorization can access the databases and their objects. In the same fashion, UNQUIESCE INSTANCE
restores access at the instance level and all its databases to regular users.
However, you can use the QUIESCE DATABASE
command to quiesce a single database. The force connections option allows disconnecting users from the database. When the database is in a quiesced mode, you can still perform administrative tasks on it. You need to issue the UNQUIESCE DATABASE
command, which restores user access to the database.
DB2's RESTORE DATABASE
command is used to recover from a backup. It covers all the recovery aspects of Oracle's recover database
command.
HADR is DB2's counterpart of Oracle's Data Guard technology. It enables to have a secure copy of a database in a remote location, so in case a disaster occurs, the remote site can take over data processing for this database. It works in the same way with regards to sending transaction logs to a remote server and applying the logs on another database. It has its own set of commands and is not part of the alter database syntax.
- Spring 5.0 Microservices(Second Edition)
- 單片機(jī)C語(yǔ)言程序設(shè)計(jì)實(shí)訓(xùn)100例:基于STC8051+Proteus仿真與實(shí)戰(zhàn)
- 算法基礎(chǔ):打開(kāi)程序設(shè)計(jì)之門(mén)
- The DevOps 2.4 Toolkit
- 人人都懂設(shè)計(jì)模式:從生活中領(lǐng)悟設(shè)計(jì)模式(Python實(shí)現(xiàn))
- Hands-On Functional Programming with TypeScript
- Python Data Analysis Cookbook
- C#應(yīng)用程序設(shè)計(jì)教程
- INSTANT Yii 1.1 Application Development Starter
- 案例式C語(yǔ)言程序設(shè)計(jì)實(shí)驗(yàn)指導(dǎo)
- C陷阱與缺陷
- Android移動(dòng)應(yīng)用開(kāi)發(fā)項(xiàng)目教程
- Practical GIS
- Maven for Eclipse
- Python第三方庫(kù)開(kāi)發(fā)應(yīng)用實(shí)戰(zhàn)