DB2AdministrationOverview

Webs: Faemalia -:- Greatprawn -:- Playground -:- Technical -:- Tweak
Technical Web Sections: Register -:- Users -:- Changes -:- Index -:- Search -:- Statistics

Table of Contents

DB2 Installation on Solaris

Setup

The shmsys:shminfo_shmmax parameters should be set to the suggested value in the above data, or 90% of the physical memory (in bytes), whichever is higher. For example, if you have 196 MB of physical memory in your system, set the shmsys:shminfo_shmmax parameter to 184968806 (196*1024*1024*0.9).

Install Software


Administration

Create Database Instance

Install Database Administration Server (DAS) Instance

Create Sample Database

Users

Connecting

SYSADM authority is controlled in the DBM CFG via the SYSADM_GROUP parameter. To update the config file:

db2 update dbm cfg using SYSADM_GROUP grp1

Killing Users

To list the current connections: 
db2 => list applications

Auth Id  Application    Appl.      Application Id                 DB       # of
         Name           Handle                                    Name    Agents
-------- -------------- ---------- ------------------------------ -------- -----
DB2INST1 db2bp          22         *LOCAL.db2inst1.020827203431   SAMPLE   1    
DB2INST1 db2bp          24         *LOCAL.db2inst1.020827203934   SAMPLE   1

To kill one of the connections: 

db2 => force application (24)
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

db2 => list applications

Auth Id  Application    Appl.      Application Id                 DB       # of
         Name           Handle                                    Name    Agents
-------- -------------- ---------- ------------------------------ -------- -----
DB2INST1 db2bp          22         *LOCAL.db2inst1.020827203431   SAMPLE   1

Establishing Schemas

To create an ecasino user with an ecasino password and ecasino schema name:

Using Schemas

Commands

Select Statements

Architecture, Definitions and Oracle Comparisons

Tuning

Import & Export

Backup & Recovery

Backup and Restore documentation and examples. The IBM tutorial is at: www7b.boulder.ibm.com/dmdd/library/techarticle/pworld/0112mccluney.html, which has been copied here on this wiki at DB2BackupRestoreRollforward. Enjoy.

Further reading after reviewing the above link:

Recoverable databases have either the logretain database configuration parameter set to "RECOVERY", the userexit database configuration parameter enabled, or both. Active logs are still available for crash recovery, but you also have the archived logs, which contain committed transaction data. Such a database can only be restored offline. It is restored to the state it was in when the backup image was taken. However, with rollforward recovery, you can roll the database forward (that is, past the time when the backup image was taken) by using the active and archived logs to either a specific point in time, or to the end of the active logs.

Recoverable database backup operations can be performed either offline or online (online meaning that other applications can connect to the database during the backup operation). Database restore and rollforward operations must always be performed offline. During an online backup operation, rollforward recovery ensures that all table changes are captured and reapplied if that backup is restored.

If you have a recoverable database, you can back up, restore, and roll individual table spaces forward, rather than the entire database. When you back up a table space online, it is still available for use, and simultaneous updates are recorded in the logs. When you perform an online restore or rollforward operation on a table space, the table space itself is not available for use until the operation completes, but users are not prevented from accessing tables in other table spaces.

To enable logging:

To see if a backup is corrupted:

Backup

Restore

To restore the database and not apply the logs use "without rolling forward" in the restore command. Logs are applied automatically.

You may restore a backup copy of a full database backup to an existing database. To restore to an existing database, you must have SYSADM, SYSCTRL, or SYSMAINT authority. The backup image may differ from the existing database in its alias name, its database name, or its database seed.

(Offline) Full Database Recovery

Situation: Logging IS NOT enabled and a version recovery is required.

(Online) Tablespace Recovery Using Full Database Backup

Situation: Logging IS enabled and a full recovery is required.

Performed an online full database backup:

Deleted everything from /db2inst1/NODE0000/SQL00001/SQLT0002.0, which is the USERSPACE1 tablespace directory. 

db2 => connect to sample

   Database Connection Information

 Database server        = DB2/SUN 7.2.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2 => select * from person

ID                   CARD_ID              GIVEN_NAME          
-------------------- -------------------- --------------------
SQL0290N  Table space access is not allowed.  SQLSTATE=55039

Since all of the tablespace files were deleted, no access is allowed to any object in the USERSPACE1 tablespace. I made sure the last full database online backup was the only backup file in db2inst1's home directory and executed the following to recover the database: 

[db2inst1@chubb ~]$ db2 restore db sample
SQL2539W  Warning!  Restoring to an existing database that is the
   same as the backup image database.  The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

db2 => connect to sample
SQL1117N  A connection to or activation of database "SAMPLE" cannot
  be made because of ROLL-FORWARD PENDING.  SQLSTATE=57019
db2 => rollforward db sample to end of logs   

                                 Rollforward Status

 Input database alias                   = sample
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0000005.LOG
 Log files processed                    = S0000001.LOG - S0000004.LOG
 Last committed transaction             = 2002-08-16-18.19.32.000000

DB20000I  The ROLLFORWARD command completed successfully.

db2 => connect to sample
SQL1117N  A connection to or activation of database "SAMPLE" cannot
  be made because of ROLL-FORWARD PENDING.  SQLSTATE=57019

db2 => rollforward db sample complete

                                 Rollforward Status

 Input database alias                   = sample
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000001.LOG - S0000004.LOG
 Last committed transaction             = 2002-08-16-18.19.32.000000

DB20000I  The ROLLFORWARD command completed successfully.

db2 => select * from person

ID                   CARD_ID              GIVEN_NAME          
-------------------- -------------------- --------------------
Mike                 W                    Fennel              
Graham               W                    Bartlett            
David                M                    Jackson             
Gene                 G                    Parker              
Dave                 K                    Cane                                

  5 record(s) selected.

(Online) Tablespace Recovery Using Tablespace Backup

Situation: Logging IS enabled and a full recovery is required.

Performed an online tablespace backup of the USERSPACE1 tablespace:

Deleted everything from /db2inst1/NODE0000/SQL00001/SQLT0002.0, which is the USERSPACE1 tablespace directory. 

db2 => connect to sample

   Database Connection Information

 Database server        = DB2/SUN 7.2.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2 => select * from person

ID                   CARD_ID              GIVEN_NAME          
-------------------- -------------------- --------------------
SQL0290N  Table space access is not allowed.  SQLSTATE=55039

Since all of the tablespace files were deleted, no access is allowed to any table in the USERSPACE1 tablespace. I made sure the last online tablespace backup of the USERSPACE1 tablespace was the only backup file in db2inst1's home directory and executed the following: 

[db2inst1@chubb ~]$ db2 restore db sample tablespace online
DB20000I  The RESTORE DATABASE command completed successfully.

[db2inst1@chubb ~]$ db2 rollforward db sample to end of logs tablespace online             

                                 Rollforward Status

 Input database alias                   = sample
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               = S0000007.LOG
 Log files processed                    =  -
 Last committed transaction             = 2002-08-16-21.44.37.000000

DB20000I  The ROLLFORWARD command completed successfully.

db2 => select * from person

ID                   CARD_ID              GIVEN_NAME          
-------------------- -------------------- --------------------
Mike                 W                    Fennel              
Graham               W                    Bartlett            
David                M                    Jackson             
Gene                 G                    Parker              
Dave                 K                    Cane                                

  5 record(s) selected.

----------------------------------

Other Examples

LOAD...COPY TO...

Incremental Delta (differential)

Situation: Logging IS enabled and a full recovery is required.

NOTE: For SMS table spaces, the granularity of this tracking is at the table space level. For DMS table spaces, the granularity is at the extent level for data and index pages, and at the table space level for other page types.

To enable incremental or incremental delta online backups you must first enable modification tracking: 

db2 => connect to sample
SQL1224N  A database agent could not be started to service a request,
  or was terminated as a result of a database system shutdown or a force
  command.  
SQLSTATE=55032

[db2inst1@chubb ~/bkpdir]$ db2 restore db sample incremental automatic
  taken at 20020822104024
SQL2539W  Warning!  Restoring to an existing database that is the same
  as the backup image database.  The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

[db2inst1@chubb ~/bkpdir]$ db2 rollforward db sample to end of logs

                                 Rollforward Status

 Input database alias                   = sample
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0000025.LOG
 Log files processed                    = S0000022.LOG - S0000024.LOG
 Last committed transaction             = 2002-08-22-17.45.27.000000

DB20000I  The ROLLFORWARD command completed successfully.

[db2inst1@chubb ~/bkpdir]$ db2 rollforward db sample complete

                                 Rollforward Status

 Input database alias                   = sample
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000022.LOG - S0000024.LOG
 Last committed transaction             = 2002-08-22-17.45.27.000000

DB20000I  The ROLLFORWARD command completed successfully.

db2 => connect to sample

   Database Connection Information

 Database server        = DB2/SUN 7.2.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2 => select * from person

ID                   CARD_ID              GIVEN_NAME          
-------------------- -------------------- --------------------
1                    123ds                Graham              
2                    32d                  Mike                
3                    adsf                 sad                 
4                    234                  erg                 
5                    324                  324d                

  5 record(s) selected.

May have also used:

Point in Time Recovery 

db2 rollforward db sample to 2002-08-18-14.21.56.245378

Troubleshooting Errors

SQL1478W - Bufferpool Error

SQL1478W The database has been started but only one buffer pool has been activated. SQLSTATE=01626

The cause of this error is that there is not enough memory to allocate to this buffer. 

db2 => select * from syscat.bufferpools

BPNAME       BUFFERPOOLID NGNAME NPAGES PAGESIZE ESTORE
------------ ------------ ------ ------ -------- ------
IBMDEFAULTBP            1 -       77406     4096 N

You'll notice there is a large buffer pool called IBMDEFAULTBP that is 77,406 pages large. This is (77406 pages * 4096 bytes) = 317054976 bytes. Approximately 300 MB of memory is being requested for the buffer pool.

Buffer pool sizes: (range per OS)
64-bit UNIX: 2 pages - 2,147,483,647 pages
32-bit UNIX: 2 pages - 524,288 pages
32-bit WONDOWS: 2 pages - 524,288 pages

To fix the error you need to alter the buffer pool size: alter bufferpool ibmdefaultbp size 2000

Stop and start DB2 and the error should not appear. To confirm the change query syscat.bufferpools again and you should see: 

BPNAME       BUFFERPOOLID NGNAME NPAGES PAGESIZE ESTORE
------------ ------------ ------ ------ -------- ------
IBMDEFAULTBP            1 -        2000     4096 N

Alert Files

Check the db2alert.log and db2diag.log files when sever conditions occur.

DB2 Connect Installation on Windows

Install Software


Edit -:- Attach -:- Ref-By -:- Printable -:- More