DB2 related
The following notes are for myself only. Whoever happened to accidentally read these notes, please do not ask any question.
SQL2413N
Online backup is not allowed because the database is not recoverable or a backup pending condition is in effect.
Explanation
Online backup cannot be performed while the database is not logging for forward recovery as forward recovery will be required at restore time. Forward recovery is placed into effect by setting either the database configuration LOGARCHMETH1 or LOGARCHMETH2 and then performing an offline backup of the database.
User response
Execute an offline backup or reconfigure the database for roll-forward recovery and issue an offline backup so that subsequent online backups will be allowed.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The operation failed because the specified database cannot be
connected to in the mode requested. SQLSTATE=57019
Resolving the problem
The issue can be addressed by using the "restricted access" option in the QUIESCE INSTANCE command. This allows you to do an offline backup by quiesce the instance in restricted access mode, instead of quiescing the database individually. You can also start the DB2 instance in restricted access mode, with the db2start admin mode restricted access command.
These two methods are the recommended ways to quiesce the database before taking offline backups.
The following is an example of this method:
db2 quiesce instance restricted access immediate force connections
db2 deactivate db
db2 backup db
db2 activate db
db2 unquiesce instance
If you have many databases in your instance and you only want to do an offline backup of one database while the others remain online, you can see to use an alias for the applications connecting to the database. Before the backup, uncatalog that alias and catalog it in the database again with that alias after the offline backup is complete.
+++++++++++++++++++++
db2ilist -> to check instance
+++++++++++++++++++++
Basic Commands
Size of your database:
=>db2 'call get_dbsize_info(?,?,?,0)'
Version of DB2 you are running:
=>db2level
Stop the db2 instance
=>db2stop
Stop an instance that has current connections
=>db2 force applications all
=>db2 deactivate db
=>db2 terminate
=>db2stop force
=>ipclean
Start the db2 instance
=>db2start
Kill a hung instance (last resort)
=>db2_kill -all
List the databases in an instance
=>db2 list db directory
List the cataloged nodes
=>db2 list node directory
Show the database manager configuration settings
=>db2 get dbm cfg
Show the database level configuration settings
=>db2 get db cfg for
Activate a database
=>db2 activate db
Deactivate a database
=>db2 deactivate db
View the DB2 License
=>db2licm -l
To switch between partitions
=>db2 terminate; export DB2NODE=
+++++++++++++++++++++++++++++++++++++++++
Technote (FAQ)
Question
This technote describes how to use the GET_DBSIZE_INFO procedure to calculate the size and maximum capacity of a database in DB2® for Linux®, UNIX® and Windows®.
Answer
If you want to calculate the size and maximum capacity of your database, you can use the procedureGET_DBSIZE_INFO.
To execute this procedure you should follow these three steps:
Connect to the database for which you want to know the size and maximum capacity.
Execute the procedure GET_DBSIZE_INFO.
Close the connection.
For the second step, you should take into account the syntax of the procedure:
db2 call GET_DBSIZE_INFO (?,?,?, refresh-window )
where the first 3 parameters are output parameters, and the refresh-window is an input parameter of type INTEGER that specifies the number of minutes until the cached values for database size and capacity are to be refreshed.
Furthermore, to valid the above data, you can use db2pd -tablespaces output, by adding up all the Tablespace Statistics: UsedPgs for all the containers
Refer to the documentation links below for examples of the output from this procedure in DB2 UDB .
Related information
GET_DBSIZE_INFO procedure
+++++++++++++++++++++++++++++++++++++++++
Take an OFFLINE backup
db2 connect to
db2 quiesce database immediate force connections
db2 connect reset
db2 backup database to compress without prompting
after backup completes:
db2 connect to
db2 unquiesce database
db2 connect reset
Take an ONLINE Backup
db2 backup db to online compress
or use a background process on a linux/unix machine
nohup db2 backup db to online compress &
List recent backups and where they are stored
=>db2 list history backup all for
Check the integrity of a backup image
=>db2ckbkp
Restore from Incremental Backup Automatic
=>db2 restore db incremental automatic taken at
If you need to restore an incremental backup manually this command will give you the required previous backups.
=>db2ckrst -d -t
_______________________________________________
Command Description
db2cmd Open DB2 Command line Tool
db2cc Open DB2 Control Centre
db2start Start Database Normaly
db2stop Stop Database Normaly
db2stop force Stop Database forcely
db2 force application all Close all applications that uses DB2 Database.
db2level Display DB2 version and fix pack level
db6level Display DB2 Client Version
db2 terminate Close the database connection
db2_kill -all Kill a hanged instance
db2licm -l View license information
db2 connect to Establish connection to an instance
db2 list tablespaces show detail Displays table space information
db2 get dbm cfg Display configuration parameter of database manager.
db2 get db cfg for Display configuration parameter of an instance
db2 update dbm cfg using Change value of a database manager configuration parameter.
db2 update db cfg for using Change value of a instance configuration parameter.
db2 drop database Delete and instance
db2 activate db Activate Database
db2 deactivate Deactivate an active database
db2 rollforward db to end of logs Apply all pending logs
db2 rollforward db query status Display rollforward status
db2 backup database to “disk:\location” Take an offline backup to specified location
db2 list utilities show detail Display Database backup status
db2 restore db from “disk:\path” replace history file Restore database from a backup image
###########################################
Determine the maximum number of Logs file to be kept at anytime.
Number of primary log files (LOGPRIMARY) = 32
db2 get dbm cfg => to get DB Management Configuration
##########################################
To view the DB2 parameters that are associated with the DB2 transaction log and their values, run the following command:
db2 get database configuration for ect | egrep 'LOGFILSIZ|LOGPRIMARY|LOGSECOND|NEWLOGPATH|Path to logfiles'
Log file size (4KB)(LOGFILSIZ) = 2000
Number of primary log files(LOGPRIMARY) = 8
Number of secondary log files(LOGSECOND) = 3
Changed path to log files(NEWLOGPATH) =
The transaction log size is limited by the values of DB2 parameters LOGFILSIZ, LOGPRIMARY, and LOGSECOND. The log size is also affected by the disk space in the directory that is specified by the NEWLOGPATH DB2 parameter.
If the transaction log size exceeds the limit that is set, the transaction is backed out by using the information in the logs. If the transaction logs exceed the limit because of the lack of disk space, the database becomes corrupted and unusable. If the database becomes corrupted, it is possible to use the DB2 commands to recover the database. The database can also be restored from a backup or reloaded. If the database becomes corrupted, you can use the recovery commands in the sqllib/db2dump/db2diag.log file. The file is in the DB2 instance owner home directory.
By default, the DB2 transaction log file size (LOGFILSIZ) is defined to 2000 blocks of 4 KB or 8000 KB per log file. The number of primary logs files (LOGPRIMARY) is defined to 8 and the number of secondary log files (LOGSECOND) is 3. To increase the DB2 transaction log limits for millions of users, it is necessary to increase the size of the transaction logs (LOGFILSIZ) and increase the number of secondary files (LOGSECOND). You must increase the number of secondary files rather than the number of primary files. It is because the secondary files periodically get deleted when not in use.
The transaction log requirements are small for a directory server with a normal workload. It is observed that runtime directory operations increases the transaction log requirements for a short time.
The ldapadd or ldapmodify commands use some amount of transaction log space. It is because log space is required when you use a command to add a number of multivalued attributes to a single LDAP entry. For example, when you load many members into a group.
An ACL placed on a suffix object can result in the propagation of the ACL to every entry under that suffix. The directory server runs ACL propagation as one single committed DB2 transaction.
You can set the transaction log by running the following DB2 commands:
db2 update database configuration for LOGFILSIZE using 10000
db2 update database configuration for LOGPRIMARY using 2
db2 terminate
db2 force applications all
db2 connect to ecp
db2 get database configuration for ecp | egrep 'LOGFILSIZ|LOGPRIMARY|LOGSECOND|NEWLOGPATH|Path to log files'
SQL2413N
Online backup is not allowed because the database is not recoverable or a backup pending condition is in effect.
Explanation
Online backup cannot be performed while the database is not logging for forward recovery as forward recovery will be required at restore time. Forward recovery is placed into effect by setting either the database configuration LOGARCHMETH1 or LOGARCHMETH2 and then performing an offline backup of the database.
User response
Execute an offline backup or reconfigure the database for roll-forward recovery and issue an offline backup so that subsequent online backups will be allowed.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The operation failed because the specified database cannot be
connected to in the mode requested. SQLSTATE=57019
Resolving the problem
The issue can be addressed by using the "restricted access" option in the QUIESCE INSTANCE command. This allows you to do an offline backup by quiesce the instance in restricted access mode, instead of quiescing the database individually. You can also start the DB2 instance in restricted access mode, with the db2start admin mode restricted access command.
These two methods are the recommended ways to quiesce the database before taking offline backups.
The following is an example of this method:
db2 quiesce instance
db2 deactivate db
db2 backup db
db2 activate db
db2 unquiesce instance
If you have many databases in your instance and you only want to do an offline backup of one database while the others remain online, you can see to use an alias for the applications connecting to the database. Before the backup, uncatalog that alias and catalog it in the database again with that alias after the offline backup is complete.
+++++++++++++++++++++
db2ilist -> to check instance
+++++++++++++++++++++
Basic Commands
Size of your database:
=>db2 'call get_dbsize_info(?,?,?,0)'
Version of DB2 you are running:
=>db2level
Stop the db2 instance
=>db2stop
Stop an instance that has current connections
=>db2 force applications all
=>db2 deactivate db
=>db2 terminate
=>db2stop force
=>ipclean
Start the db2 instance
=>db2start
Kill a hung instance (last resort)
=>db2_kill -all
List the databases in an instance
=>db2 list db directory
List the cataloged nodes
=>db2 list node directory
Show the database manager configuration settings
=>db2 get dbm cfg
Show the database level configuration settings
=>db2 get db cfg for
Activate a database
=>db2 activate db
Deactivate a database
=>db2 deactivate db
View the DB2 License
=>db2licm -l
To switch between partitions
=>db2 terminate; export DB2NODE=
+++++++++++++++++++++++++++++++++++++++++
Technote (FAQ)
Question
This technote describes how to use the GET_DBSIZE_INFO procedure to calculate the size and maximum capacity of a database in DB2® for Linux®, UNIX® and Windows®.
Answer
If you want to calculate the size and maximum capacity of your database, you can use the procedureGET_DBSIZE_INFO.
To execute this procedure you should follow these three steps:
Connect to the database for which you want to know the size and maximum capacity.
Execute the procedure GET_DBSIZE_INFO.
Close the connection.
For the second step, you should take into account the syntax of the procedure:
db2 call GET_DBSIZE_INFO (?,?,?, refresh-window )
where the first 3 parameters are output parameters, and the refresh-window is an input parameter of type INTEGER that specifies the number of minutes until the cached values for database size and capacity are to be refreshed.
Furthermore, to valid the above data, you can use db2pd -tablespaces output, by adding up all the Tablespace Statistics: UsedPgs for all the containers
Refer to the documentation links below for examples of the output from this procedure in DB2 UDB .
Related information
GET_DBSIZE_INFO procedure
+++++++++++++++++++++++++++++++++++++++++
Take an OFFLINE backup
db2 connect to
db2 quiesce database immediate force connections
db2 connect reset
db2 backup database
after backup completes:
db2 connect to
db2 unquiesce database
db2 connect reset
Take an ONLINE Backup
db2 backup db
or use a background process on a linux/unix machine
nohup db2 backup db
List recent backups and where they are stored
=>db2 list history backup all for
Check the integrity of a backup image
=>db2ckbkp
Restore from Incremental Backup Automatic
=>db2 restore db
If you need to restore an incremental backup manually this command will give you the required previous backups.
=>db2ckrst -d
_______________________________________________
Command Description
db2cmd Open DB2 Command line Tool
db2cc Open DB2 Control Centre
db2start Start Database Normaly
db2stop Stop Database Normaly
db2stop force Stop Database forcely
db2 force application all Close all applications that uses DB2 Database.
db2level Display DB2 version and fix pack level
db6level Display DB2 Client Version
db2 terminate Close the database connection
db2_kill -all Kill a hanged instance
db2licm -l View license information
db2 connect to
db2 list tablespaces show detail Displays table space information
db2 get dbm cfg Display configuration parameter of database manager.
db2 get db cfg for
db2 update dbm cfg using
db2 update db cfg for
db2 drop database
db2 activate db
db2 deactivate
db2 rollforward db
db2 rollforward db
db2 backup database
db2 list utilities show detail Display Database backup status
db2 restore db
###########################################
Determine the maximum number of Logs file to be kept at anytime.
Number of primary log files (LOGPRIMARY) = 32
db2 get dbm cfg => to get DB Management Configuration
##########################################
To view the DB2 parameters that are associated with the DB2 transaction log and their values, run the following command:
db2 get database configuration for ect | egrep 'LOGFILSIZ|LOGPRIMARY|LOGSECOND|NEWLOGPATH|Path to logfiles'
Log file size (4KB)(LOGFILSIZ) = 2000
Number of primary log files(LOGPRIMARY) = 8
Number of secondary log files(LOGSECOND) = 3
Changed path to log files(NEWLOGPATH) =
The transaction log size is limited by the values of DB2 parameters LOGFILSIZ, LOGPRIMARY, and LOGSECOND. The log size is also affected by the disk space in the directory that is specified by the NEWLOGPATH DB2 parameter.
If the transaction log size exceeds the limit that is set, the transaction is backed out by using the information in the logs. If the transaction logs exceed the limit because of the lack of disk space, the database becomes corrupted and unusable. If the database becomes corrupted, it is possible to use the DB2 commands to recover the database. The database can also be restored from a backup or reloaded. If the database becomes corrupted, you can use the recovery commands in the sqllib/db2dump/db2diag.log file. The file is in the DB2 instance owner home directory.
By default, the DB2 transaction log file size (LOGFILSIZ) is defined to 2000 blocks of 4 KB or 8000 KB per log file. The number of primary logs files (LOGPRIMARY) is defined to 8 and the number of secondary log files (LOGSECOND) is 3. To increase the DB2 transaction log limits for millions of users, it is necessary to increase the size of the transaction logs (LOGFILSIZ) and increase the number of secondary files (LOGSECOND). You must increase the number of secondary files rather than the number of primary files. It is because the secondary files periodically get deleted when not in use.
The transaction log requirements are small for a directory server with a normal workload. It is observed that runtime directory operations increases the transaction log requirements for a short time.
The ldapadd or ldapmodify commands use some amount of transaction log space. It is because log space is required when you use a command to add a number of multivalued attributes to a single LDAP entry. For example, when you load many members into a group.
An ACL placed on a suffix object can result in the propagation of the ACL to every entry under that suffix. The directory server runs ACL propagation as one single committed DB2 transaction.
You can set the transaction log by running the following DB2 commands:
db2 update database configuration for LOGFILSIZE using 10000
db2 update database configuration for LOGPRIMARY using 2
db2 terminate
db2 force applications all
db2 connect to ecp
db2 get database configuration for ecp | egrep 'LOGFILSIZ|LOGPRIMARY|LOGSECOND|NEWLOGPATH|Path to log files'
Comments