How to create the same database on other system

restoring a backup image in a different server

To create the same database on other system you need to specific the new devices, directories and files for the tablespaces containers . You can perform that using the redirect option on your restore command. Also, you have to move the logs from source server to the target server into the logpath. Let's see the concepts and steps below to do it :

Restore command

RESTORE DATABASE source-database-alias
[USER username [USING password]]
[TABLESPACE [(tblspace-name, ...)] [ONLINE]
| HISTORY FILE [ONLINE] | LOGS [ONLINE]]
[INCREMENTAL [AUTOMATIC | ABORT]]
[USE TSM | FROM dir/dev, ... ]
[TAKEN AT date-time]
[TO target-directory]
[INTO target-database-alias]
[LOGTARGET directory]
[NEWLOGPATH directory]
[WITH num-buff BUFFERS]
[BUFFER buffer-size]
[REPLACE EXISTING]
[REDIRECT]
[PARALLELISM n]
[WITHOUT ROLLING FORWARD]
[WITHOUT PROMPTING]
or
RESTORE DATABASE source-database-alias [CONTINUE | ABORT]

Restore Redirect

Definition of table space containers is kept during a backup

Used to redefine or redirect table space containers during a restore (usually used when restoring a backup image in a different server).

If containers are not available during a restore, new containers can be specified.

Redirected restore operations are performed by executing the RESTORE command with the REDIRECT option specified, followed by one or more SET TABLESPACE CONTAINERS commands, followed by the RESTORE command with the CONTINUE option specified.

The basic syntax for the SET TABLESPACE CONTAINERS command is:

SET TABLESPACE CONTAINERS FOR tablespace-id
USING ( {PATH 'container-string‘, ... |
{FILE | DEVICE} 'container-string' number-of-pages,
...})

Containers in the SET TABLESPACE CONTAINERS should have the same type of the containers defined on the source database. The container size on the target database can be smaller then on source, but the sum of the containers size should be at least equal to the table space high water mark on the source.

Example: Restore with redirect
```
db2 "RESTORE DATABASE SAMPLE FROM C:\BACKUPS TO D:\DB_DIR
INTO SAMPLE_2 REDIRECT"

db2 "SET TABLESPACE CONTAINERS FOR 0 USING (PATH
'D:\DB_DIR\SYSTEM')"
db2 "SET TABLESPACE CONTAINERS FOR 1 USING (PATH
'D:\DB_DIR\TEMP')"
db2 "SET TABLESPACE CONTAINERS FOR 2 USING (PATH
'D:\DB_DIR\USER')"

db2 "RESTORE DATABASE SAMPLE CONTINUE"
```

Assuming the backup was done online with log archiving enabled, there is one more thing to do:
Transfer your log files to the logpath in your new server.
Now you will perform a roll forward operation.

Roll Forward

Allows to return a database to the state it was in at a given point in time.

In order to perform a roll- forward recovery operation, the database must be recoverable
Invoked after a database or a table space restore.

You must have access to all archived log files that will be needed to perform the roll-forward recovery operation.

Once the logs needed are is in the current log path, transactions are reapplied.

Roll forward must be invoked after restoring a recoverable database from an online backup if the option without rolling forward were not specified in the restore command.

Before starting a roll forward, you should check the logs that you need using the command. For example:

db2 rollforward db sample query status

Then copy the logs from archive to the logpath or overflowlogpath, starting with the log showed in on “roll forward query status command”. Once the logs are in the logpath, run the roll forward command to restore your database on your new server.
To know what is the logpath you can execute the command below:

db2 get db cfg for <dbname>

Output:

Changed path to log files    (NEWLOGPATH) =
Path to log files                         = C:\DB2\NODE0000\SQL00002\SQLOGDIR\
Overflow log path       (OVERFLOWLOGPATH) =

EXAMPLES:

To to roll forward the database sample to the end of logs, and complete:

db2 rollforward db sample to end of logs and complete

After restoring the database, roll forward to a point in time, using local time

db2 rollforward db sample to 2006-05-15-17.21.56.245378 using local time and stop

Now, you have created the same database on other system!

How to grant privileges on DB2 9.7

Authorization

To grant ACCESSCTRL, DATAACCESS, DBADM, or SECADM authority, SECADM authority is required. To grant other authorities ACCESSCTRL or SECADM authority is required.

Syntax




>>-GRANT-------------------------------------------------------->

   .-,------------------------------------------------------------------.   
   V                                                                    |   
>----+-ACCESSCTRL-----------------------------------------------------+-+-->
     +-BINDADD--------------------------------------------------------+     
     +-CONNECT--------------------------------------------------------+     
     +-CREATETAB------------------------------------------------------+     
     +-CREATE_EXTERNAL_ROUTINE----------------------------------------+     
     +-CREATE_NOT_FENCED_ROUTINE--------------------------------------+     
     +-DATAACCESS-----------------------------------------------------+     
     |           .-WITH DATAACCESS----.     .-WITH ACCESSCTRL----.    |     
     +-DBADM--•--+--------------------+--•--+--------------------+--•-+     
     |           '-WITHOUT DATAACCESS-'     '-WITHOUT ACCESSCTRL-'    |     
     +-EXPLAIN--------------------------------------------------------+     
     +-IMPLICIT_SCHEMA------------------------------------------------+     
     +-LOAD-----------------------------------------------------------+     
     +-QUIESCE_CONNECT------------------------------------------------+     
     +-SECADM---------------------------------------------------------+     
     +-SQLADM---------------------------------------------------------+     
     '-WLMADM---------------------------------------------------------'     

                    .-,---------------------------------.   
                    V                                   |   
>--ON DATABASE--TO----+-+-------+--authorization-name-+-+------><
                      | +-USER--+                     |     
                      | +-GROUP-+                     |     
                      | '-ROLE--'                     |     
                      '-PUBLIC------------------------'

Description

ACCESSCTRL

Grants the access control authority. The ACCESSCTRL authority allows the holder to:

Grant and revoke the following database authorities: BINDADD, CONNECT, CREATETAB, CREATE_EXTERNAL_ROUTINE, CREATE_NOT_FENCED_ROUTINE, EXPLAIN, IMPLICIT_SCHEMA, LOAD, QUIESE_CONNECT, SQLADM, WLMADM
Grant and revoke all object level privileges

The ACCESSCTRL authority cannot be granted to PUBLIC (SQLSTATE 42508).

BINDADD

Grants the authority to create packages. The creator of a package automatically has the CONTROL privilege on that package and retains this privilege even if the BINDADD authority is subsequently revoked.

CONNECT

Grants the authority to access the database.

CREATETAB

Grants the authority to create base tables. The creator of a base table automatically has the CONTROL privilege on that table. The creator retains this privilege even if the CREATETAB authority is subsequently revoked.

There is no explicit authority required for view creation. A view can be created at any time if the authorization ID of the statement used to create the view has either CONTROL or SELECT privilege on each base table of the view.

CREATE_EXTERNAL_ROUTINE

Grants the authority to register external routines. Care must be taken that routines so registered will not have adverse side effects. (For more information, see the description of the THREADSAFE clause on the CREATE or ALTER routine statements.)

Once an external routine has been registered, it continues to exist, even if CREATE_EXTERNAL_ROUTINE is subsequently revoked.

CREATE_NOT_FENCED_ROUTINE

Grants the authority to register routines that execute in the database manager's process. Care must be taken that routines so registered will not have adverse side effects. (For more information, see the description of the FENCED clause on the CREATE or ALTER routine statements.)

Once a routine has been registered as not fenced, it continues to run in this manner, even if CREATE_NOT_FENCED_ROUTINE is subsequently revoked.

CREATE_EXTERNAL_ROUTINE is automatically granted to an authorization-name that is granted CREATE_NOT_FENCED_ROUTINE authority.

DATAACCESS

Grants the authority to access data. The DATAACCESS authority allows the holder to:

Select, insert, update, delete, and load data
Execute any package
Execute any routine (except audit routines)

The DATAACCESS authority cannot be granted to PUBLIC (SQLSTATE 42508).

DBADM

Grants the database administrator authority. A database administrator holds nearly all privileges on nearly all objects in the database. The only exceptions are those privileges that are part of the access control, data access, and security administrator authorities. DBADM cannot be granted to PUBLIC.

EXPLAIN

Grants the authority to explain statements. The EXPLAIN authority allows the holder to explain, prepare, and describe dynamic and static SQL statements without requiring access to data.

IMPLICIT_SCHEMA

Grants the authority to implicitly create a schema.

LOAD

Grants the authority to load in this database. This authority gives a user the right to use the LOAD utility in this database. DATAACCESS and DBADM also have this authority by default. However, if a user only has LOAD authority (not DATAACCESS), the user is also required to have table-level privileges. In addition to LOAD privilege, the user is required to have:

INSERT privilege on the table for LOAD with mode INSERT, TERMINATE (to terminate a previous LOAD INSERT), or RESTART (to restart a previous LOAD INSERT)
INSERT and DELETE privilege on the table for LOAD with mode REPLACE, TERMINATE (to terminate a previous LOAD REPLACE), or RESTART (to restart a previous LOAD REPLACE)
INSERT privilege on the exception table, if such a table is used as part of LOAD

QUIESCE_CONNECT

Grants the authority to access the database while it is quiesced.

SECADM

Grants the security administrator authority. The authority allows the holder to:

Create and drop security objects such as audit policies, roles, security labels, security label components, security policies, and trusted contexts
Grant and revoke authorities, exemptions, privileges, roles, and security labels
Grant and revoke the SETSESSIONUSER privilege
Execute TRANSFER OWNERSHIP on objects owned by others

The SECADM authority cannot be granted to PUBLIC (SQLSTATE 42508).

SQLADM

Grants the authority to manage SQL statement execution. The SQLADM authority allows the holder to:

Create, drop, flush, and set event monitors
Explain, prepare, and describe dynamic and static SQL statements without requiring access to data
Flush optimization profile cache
Flush package cache
Execute the runstats utility

WLMADM

Grants the authority to manage workloads. The WLMADM authority allows the holder to:

Create, drop, and alter service classes, work action sets, work class sets, or workloads.

TO

Specifies to whom the authorities are granted.

USER: Specifies that the authorization-name identifies a user.
GROUP: Specifies that the authorization-name identifies a group name.
ROLE: Specifies that the authorization-name identifies a role name. The role name must exist at the current server (SQLSTATE 42704).
authorization-name,...: Lists the authorization IDs of one or more users, groups, or roles.
The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).
PUBLIC: Grants the authorities to a set of users (authorization IDs). For more information, see "Authorization, privileges and object ownership".

Rules

For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
- If the security plug-in in effect for the instance cannot determine the status of the authorization-name, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined as ROLE in the database, and as either GROUP or USER according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined according to the security plug-in in effect as both USER and GROUP, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined according to the security plug-in in effect as USER only, or if it is undefined, USER is assumed.
- If the authorization-name is defined according to the security plug-in in effect as GROUP only, GROUP is assumed.
- If the authorization-name is defined in the database as ROLE only, ROLE is assumed.

Notes

ACCESSCTRL, DATAACCESS, DBADM, or SECADM authorities cannot be granted to the special group PUBLIC. Therefore, granting ACCESSCTRL, DBADM, DATAACCESS, or SECADM authority to a role role-name fails if role-name is granted to PUBLIC either directly or indirectly (SQLSTATE 42508).
- Role role-name is granted directly to PUBLIC if the following statement has been issued:
```
   GRANT ROLE role-name TO PUBLIC
```
- Role role-name is granted indirectly to PUBLIC if the following statements have been issued:
```
   GRANT ROLE role-name TO ROLE role-name2
   GRANT ROLE role-name2 TO PUBLIC
```
Syntax alternatives: The following are supported for compatibility with previous versions of DB2® and with other database products.
- CREATE_NOT_FENCED can be specified in place of CREATE_NOT_FENCED_ROUTINE
- SYSTEM can be specified in place of DATABASE
Privileges granted to a group: A privilege that is granted to a group is not used for authorization checking on:
- Static DML statements in a package
- A base table while processing a CREATE VIEW statement
- A base table while processing a CREATE TABLE statement for a materialized query table
- Create SQL routine
- Create trigger

Examples

Example 1: Give the users WINKEN, BLINKEN, and NOD the authority to connect to the database.

   GRANT CONNECT ON DATABASE TO USER WINKEN, USER BLINKEN, USER NOD

Example 2: Grant BINDADD authority on the database to a group named D024. There is both a group and a user called D024 in the system.

   GRANT BINDADD ON DATABASE TO GROUP D024

Observe that, the GROUP keyword must be specified; otherwise, an error will occur since both a user and a group named D024 exist. Any member of the D024 group will be allowed to bind packages in the database, but the D024 user will not be allowed (unless this user is also a member of the group D024, had been granted BINDADD authority previously, or BINDADD authority had been granted to another group of which D024 was a member).

Example 3: Give user Walid security administrator authority.

   GRANT SECADM ON DATABASE TO USER Walid

SOURCE: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.sql.ref.doc/doc/r0000958.html

DB2 Tricks

Pages

Sunday, 1 June 2014