Database Technologies...!

DB2 version - 10.1 OS version - AIX 6.1 After restoring db2 version 9.5 coldbackup on top of db2 v10.1 for upgrade, application users who logged in via 3rd party gui was getting the below error, DB2 SQL error: SQLCODE: -5193, SQLSTATE: 42524, SQLERRMC: null On the diag log bellow error was repeated when ever client was executing a query, FUNCTION: DB2 UDB, routine_infrastructure, sqlerCallDL, probe:5 RETCODE : ZRC=0x801A006D=-2145779603=SQLZ_CA_BUILT "SQLCA has already been built" when the user tries to query directly via the server it self, SQL5193N  The current session user does not have usage privilege on any enabled workloads.  SQLSTATE=42524 So although the first error was not very clear it was caused by not having workload privilege. By adding the user to a default workload as below, error can be resolved. bash-3.2$ db2 create role ROLE_REGULAR_USERS; DB20000I The SQL command completed successfully. bash-3.2$ db2 grant role ROLE_REGULAR_USERS t...

DB2 Version – 9.5 Fixpack 3 OS – AIX 5.3 SQL10004C An I/O error occurred while accessing the database directory. SQLSTATE=58031. This error was returned during the restoration of db backup on a new server. As per the docs and references this can be caused by several reasons including OS level permission, db and os level version etc.. But quick workaround that can be tested to resolve this error while performing a db2 restore is to try the restoration with different database directory without using the default one with the use "dbpath on" clause of db2 restore command.  By adding "dbpath on" to the restoration command as below, db2 "restore database CPSDB from '/data12/backup' dbpath on '/data12/cpsjun15' into cpsjun15 logtarget '/data12/cpsjun15/log' redirect"

DB2 v9.1 AIX 5.3 This post demonstrate step by step approach to restart a database which returns SQL1034C in DB2. Error → bash-4.2$ db2 connect to TESTDB SQL1034C The database is damaged. All applications processing the database have been stopped. SQLSTATE=58031 1. DB2 stop with force application all. b ash-4.2$ db2 force application all DB20000I The FORCE APPLICATION command completed successfully. DB21024I This command is asynchronous and may not be effective immediately. bash-4.2$ db2 list active databases SQL1611W No data was returned by Database System Monitor. bash-4.2$ db2stop 02/24/2015 01:38:16 0 0 SQL1025N The database manager was not stopped because databases are still active. SQL1025N The database manager was not stopped because databases are still active. 2. DB2 quiesce instance force connections bash-4.2$ db2 quiesce instance TINST1 immediate force connections SQ...

DB2 v9.1 fixpsck 10 - Single Instance on AIX Issue - Can not connect to db2 database directly from server it self. (Remote connection were allowed) Error returned was as below. Any db2 command failes with same error. Since the remote connection are allowed, and only local connections were refused it was obvious that this was not caused by network error/port mismatch etc.. So we were checking for any server changes made and found that the OS admin has changed the "/etc/service" file in the process of SHC gap closing. Cause - While the SHC OS admin has removed the db2 FCM (Fast communications manager) related entries from /etc/services. Which was causing the error. What is FCM - FCM is the mechanism used by DB2 to communicate within database partitions in a DPF environment. Solution - Add the FCM port entries in  "/etc/service". This entries should have port range defined as in the below example. ( cat /etc/services | grep -i db2 ) The difference bet...

In the previous part 1   we restored the database to different server. Now we will do the recovery of this database. Below is the command used for roll forwarding the database we restored in the part 1. This will put the database on consistent state using the logs that were included with the backup file from original host db2 “rollforward database ebppres to end of logs and complete overflow log path (‘/db2_main_data2/ebpplog’)”  “end of logs” – This indicates that we are doing a full recovery instead of Point in time recovery. If point in time recovery is needed “using local time” clause need to be used also specifying the time which the recovery will end. “complete” – This will stop the roll forward process and undo any uncompleted transactions to put the database on consistent(accessible) state. Here the use of “and” is to combine two commands. As an example above single command is same as issuing below two commands. db2 “rollforward database ebppres to en...

DB2 restore/recover database to different server Db2 version - DB2 v9.1.0.10 LUW, no DPF OS – AIX 5.3 Below  is the part 1 of 2 which descibes the process of restoring db2 full online database backup to different server with different mount point definition. So this will be a redirected restore with redefining database containers. BackupProcess Database name – ebpp db2 “backup database ebpp online to /rsair_dbbackup/bifinst1_backup/EBPP compress include logs”; LST -01 Restore Process  Assuming same instance level as the backup host is there on the restoration host, Step 1 – Initiate a redirect restore start Below is the command used.. Explanation follows, db2 terminate db2 "restore database ebpp dbpath on '/db2_main_temp1' into ebppres logtarget '/db2_main_data2/ebpplog' redirect" LST -02 dbpath will be the database path for newly created database. *** This need to be run from the directory which the ba...

This is to discuss the behavior of db2 when users (Who are granted database authorities) are dropped from OS level. Check the current users and their authorities granted.   Add a new user and grant some authorities then drop the user Now recheck the authorities still the user is defined in database and the authorities are there. In most cases this will not be an issue practically as default authentication can not happen since the user is dropped from OS level. Anyways you will have to revoke the authorities manually if you want to remove them. And if you ever needed to remove this type of entries from database (Happened to me.. Didn’t work saying “it will not affect anything L ”) below script will find those users and any objects that they have the ownership. On UNIX, DB2 => 9 . ~/.profile cat /etc/passwd | awk 'BEGIN { FS = ":" } ; { prin...

We were trying to restore a TSM backup of SAP with load options of DB2 restore. Command – db2 backup db ecp online load /usr/tivoli/tsm/tdp_r3/db264/libtdpdb264.a But we were repeatedly getting SQL2062N, SQL2062N An error occurred while accessing media "/usr/tivoli/tsm/tdp_r3/db264/libtdpdb264.a". Reason code: "11". dsmerror.log or db2diag log was not helping much. Then we were looking at - tdpdb2.<SID>.<nodename>.log ( http://publib.boulder.ibm.com/infocenter/tsminfo/v6/index.jsp?topic=%2Fcom.ibm.itsm.erp.doc%2Fr_dperp_d_prob_reasoncodes.html ) From that we could find the reason… It was because the ‘XINT_PROFILE’ environment variable was not set.. How to set XINT_PROFILE is described clearly in here, http://www-01.ibm.com/support/docview.wss?uid=swg21250331