官术网_书友最值得收藏!

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.

Startup/shutdown instance

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.

  1. Start instance:
    [db2inst1@nodedb21 ~]$ db2start SQL1063N DB2START processing was successful. [db2inst1@nodedb21 ~]$
    
  2. 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.

Startup/shutdown database

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.

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

Database file containers

Adding or dropping containers (Oracle datafile) is done with DB2's ALTER TABLESPACE command. However, you cannot rename a container.

  1. Add container:
    db2 ALTER TABLESPACE NAV_TBLS ADD ( FILE '/data/db2/db2inst1/NODE0000/nav/nav_tbls_02.dbf' 2560 ) 
  2. Drop container:
db2 ALTER TABLESPACE NAV_TBLS DROP ( FILE '/data/db2/db2inst1/NODE0000/nav/nav_tbls.dbf' ) 

Log files

Log files are managed by the get/update database configuration command.

  1. Change archiving mode:
    db2 update db cfg using logretain recovery deferred 
  2. 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.

Control files

There is no equivalent to Oracle's control files in DB2, so there is no replacement to manipulate those kinds of files in DB2.

Quiesce instance/database

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.

Backup and recovery

DB2's RESTORE DATABASE command is used to recover from a backup. It covers all the recovery aspects of Oracle's recover database command.

Standby databases

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.

See also

主站蜘蛛池模板: 长葛市| 宜黄县| 浮梁县| 吴桥县| 土默特右旗| 十堰市| 乌拉特中旗| 乾安县| 神池县| SHOW| 凤翔县| 湛江市| 济源市| 临沂市| 临洮县| 南安市| 香港| 望谟县| 文成县| 林芝县| 始兴县| 额敏县| 乐清市| 南城县| 贺兰县| 伊金霍洛旗| 太湖县| 湖州市| 绥中县| 威海市| 垫江县| 福海县| 武安市| 星子县| 灵石县| 嘉兴市| 大化| 黄大仙区| 时尚| 桃园县| 普兰店市|