| <<O>> Difference Topic DB2AdministrationOverview (r1.2 - 27 Apr 2003 - PhiloVivero) |
| ||||||||
| Line: 23 to 23 | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
| Changed: | ||||||||
| < < |
| |||||||
| > > |
| |||||||
| Changed: | ||||||||
| < < |
| |||||||
| > > |
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
| ||||||||
| Changed: | ||||||||
| < < |
| |||||||
| > > |
| |||||||
AdministrationCreate Database Instance | ||||||||
| Changed: | ||||||||
| < < |
| |||||||
| > > |
| |||||||
Install Database Administration Server (DAS) Instance | ||||||||
| Changed: | ||||||||
| < < |
| |||||||
| > > |
| |||||||
Create Sample Database | ||||||||
| <<O>> Difference Topic DB2AdministrationOverview (r1.1 - 06 Sep 2002 - PhiloVivero) |
| Line: 1 to 1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| Added: | ||||||||
| > > |
DB2 Installation on SolarisSetup
Install Software
AdministrationCreate Database Instance
Install Database Administration Server (DAS) Instance
Create Sample Database
UsersConnecting
Killing UsersTo 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 SchemasTo 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 & RecoveryBackup 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 restore the database and not apply the logs use "without rolling forward" in the restore command. Logs are applied automatically.
(Offline) Full Database RecoverySituation: Logging IS NOT enabled and a version recovery is required.
(Online) Tablespace Recovery Using Full Database BackupSituation: Logging IS enabled and a full recovery is required. Performed an online full database backup:
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=55039Since 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 BackupSituation: Logging IS enabled and a full recovery is required. Performed an online tablespace backup of the USERSPACE1 tablespace:
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=55039Since 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 ExamplesLOAD...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 Recoverydb2 rollforward db sample to 2002-08-18-14.21.56.245378 Troubleshooting ErrorsSQL1478W - Bufferpool ErrorSQL1478W 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 NYou'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 FilesCheck the db2alert.log and db2diag.log files when sever conditions occur.DB2 Connect Installation on WindowsInstall Software
| |||||||
| View -:- Diffs | r1.2 | > | r1.1 -:- More |
|
Revision r1.1 - 06 Sep 2002 - 00:19 - PhiloVivero Revision r1.2 - 27 Apr 2003 - 04:29 - PhiloVivero |
Copyright © 2002 by the Author of the article. All material on this collaboration tool is the property of the contributing authors. |