Database problems

This information covers common problems that occur in the TADDM database.

Database deadlocks or storage errors occur while running a discovery

Problem

The discovery stops because database deadlocks occur or because storage retry exceptions are logged to the TADDM log files.

Solution

The database deadlocks are the root cause of storage retry exceptions.

While either of these types of errors can occur at any time, the errors are especially noticeable with a new database if database statistics have not been generated because of lack of data.

Database deadlocks

During discovery, TADDM runs parallel threads to improve throughput and performance. When the database is first created and still empty, TADDM assigns default values to the catalog statistics. Because of this method, some of the access paths that are used by the database manager can cause deadlocks to occur during a discovery run. To identify a deadlock, perform the following steps:

From a DB2® command window, run the following SQL statements:

db2 UPDATE MONITOR SWITCHES USING BUFFERPOOL ON
db2 UPDATE MONITOR SWITCHES USING LOCK ON
db2 UPDATE MONITOR SWITCHES USING SORT ON
db2 UPDATE MONITOR SWITCHES USING STATEMENT ON
db2 UPDATE MONITOR SWITCHES USING TABLE ON
db2 UPDATE MONITOR SWITCHES USING UOW ON
db2 UPDATE MONITOR SWITCHES USING TIMESTAMP ON

Also from a DB2 command window, run the following command:
```
db2 get monitor switches
```
All switches must be set to ON.
Run a discovery.
After the discovery completes, from the same DB2 command window as in the preceding steps, run the following command, where dbname is the name of your database:
```
db2 get snapshot for all on dbname > dbname-dbsnap.out
```
To see the number of deadlocks that are detected, look for the following words in the dbname-dbsnap.out file:
```
Deadlocks detected
```

For optimal performance and throughput, and to reduce the possibility of database deadlocks, use the following database commands to ensure that your database statistics are updated on a regular basis:

For DB2 databases:

runstats on table taddmuser.compsys and indexes all

For Oracle databases:

dbms_stats.gather_table_stats(ownname=> 'TADDMUSER', tabname=> 'COMPSYS', partname=> NULL);

Also, the gen_db_stats.jy script in the $COLLATION_HOME/bin directory outputs the database commands for either DB2 or Oracle databases to update the statistics on the TADDM tables. The following example shows how to use this script:

cd $COLLATION_HOME/bin
./gen_db_stats.jy >tmpdir/TADDM_table_stats.sql, where tmpdir is a directory where this file can be created. When this is complete, copy the file to the database server, and run the following command:
```
db2 -tvf tmpdir/TADDM_table_stats.sql
```

You can also use either the DB2 Control Center or the Oracle Enterprise Manager to update the database statistics, which is especially important after an initial discovery of your environment.

For more information about dwcount and topopumpcount, refer to the Tuning Discovery Performance paper that is available at http://www.ibm.com/software/brandcatalog/ismlibrary/. Search for Tuning Discovery Performance.

Storage errors

When using DB2 and running your first discovery against an empty database, you might see storage retry exceptions in the TADDM log files. These errors occur because no data exists in the database for calculating indexes to store the data efficiently. As data is added to the database and the database is properly tuned, these errors resolve and are no longer shown in the logs. You are not required to perform any action other than tuning the database after the first discovery. However, to mitigate the problem, you can perform the following steps:

In the $COLLATION_HOME/etc/collation.properties file, change the value of the com.collation.discover.observer.topopumpcount property from 16 to either 4 or 8.
Restart TADDM.
Run a discovery.
When discovery completes, run the RUNSTATS and the db2updatestats.sh programs, according to the following instructions:
1. cd $COLLATION_HOME/bin
2. ./gen_db_stats.jy >tmpdir/TADDM_table_stats.sql, where tmpdir is a directory where this file can be created. When this step is complete, copy the file to the database server, and run the following command:
```
db2 -tvf tmpdir/TADDM_table_stats.sql
```
3. cd $COLLATION_HOME/bin
4. ./db2updatestats.sh
In the $COLLATION_HOME/etc/collation.properties file, change the value of the com.collation.discover.observer.topopumpcount property back to 16 (or to another value that you prefer).
Restart TADDM.
Again run a discovery.

Database deadlocks occur while generating large business applications

Problem: Generation of very large business applications might acquire a large number of row locks, which might exceed the maximum lock number on the DB2 database. This leads to lock escalation, which might result in deadlocks.
Solution: Increase the maximum lock storage parameter by following the configuration instructions in the DB2 documentation.

Database deadlocks occur when the currently committed parameter is turned off

Problem: Lock timeouts and deadlocks might occur under the cursor stability (CS) isolation level with row-level locking.
Solution: Since version 9.7, there is currently committed semantics in DB2, thanks to which readers do not wait for writers to release row locks. Currently committed semantics is turned on by default for new databases. However, the database cur_commit parameter is disabled by default for the upgraded databases.; To avoid lock timeouts and deadlocks, turn on currently committed semantics by setting the cur_commit parameter to ON. For more information, see the Currently committed semantics improve concurrency topic in the DB2 documentation.

You are using an Oracle database, and the upgrade from a previous release of TADDM fails

Problem: You are using an Oracle database, and the upgrade from a previous release of TADDM fails. An ORA-01555 error during the upgrade indicates that the rollback segment in the Oracle database is too small and must be increased.
Solution: Ensure that the UNDO_RETENTION parameter is set to an appropriate value based on the size of the database. Refer to the Oracle documentation for information about how to calculate the appropriate value.

TADDM cannot connect to a DB2 database running on Red Hat Enterprise Linux version 6

Problem

If the DB2 database is installed on a server running Red Hat Enterprise Linux® version 6, the TADDM server might be unable to connect to the database. After three failed attempts, the following error message is displayed in the tomcat.log file (TADDM 7.3.0) or in the wlp.log (TADDM 7.3.0.1, and later):

ERROR jdo.JdoDbInit - 
java.sql.SQLException: [JdoDbInit.E.2] An error occurred attempting to
    connect to the  jdbc:db2://host.example.com:50000/taddm:deferPrepares=
    false; 
	database, db2inst1, com.ibm.db2.jcc.DB2Driver.
	at com.ibm.cdb.topomgr.jdo.JdoDbInit.getDbConnection(JdoDbInit.java:451)
	at com.ibm.cdb.topomgr.jdo.JdoDbInit.initDb(JdoDbInit.java:158)
	at com.ibm.cdb.topomgr.jdo.DbInit.start(DbInit.java:83)

Solution

This error is caused by the default password hashing algorithm used by Red Hat Enterprise Linux version 6, which is not compatible with versions of DB2 earlier than 9.7. To avoid the problem, follow these steps:

On the Linux DB2 server, change the system password hashing algorithm to MD5. (You can also use the SHA256 algorithm). Run this command to change the algorithm to MD5:
```
authconfig --passalgo=md5 --update
```
Change the passwords for the TADDM database users (typically db2inst1 and archuser). Changing the passwords forces reencryption with the new hashing algorithm.
Note: The TADDM database users are specified in the collation.properties file on the TADDM server:
```
com.collation.db.user=db2inst1
com.collation.db.archive.user=archuser
```
Verify that the passwords are encrypted using MD5 by viewing them in the /etc/shadow file. If the passwords are encrypted by using MD5, each is preceded by the string '$1$' (MD5). The following example shows that the db2inst1 password is encrypted using MD5:
```
db2inst1:$1$BuZ4l/S5$HjFa4JFtQQO5C4pFTxpes/:14193:0:99999:7:::
```
You can optionally change the system hashing algorithm back to the default SHA512. This change does not affect the passwords already encrypted using MD5. (However, if you are required to change the TADDM passwords in the future, you must change the algorithm again.) Run this command to change the algorithm back to SHA512:
```
authconfig --passalgo=sha512 --update
```

Error when installing the primary storage server if the DB2 database password contains special characters

Problem: When you specify the DB2 user password during the primary storage server installation, the installer cannot verify the password if it contains special characters.

Solution: You can continue with the installation without carrying out this verification.

Troubleshooting for exception: DB2 SQL error: SQLCODE: -964, SQLSTATE: 57011, SQLERRMC: null

Problem

An error similar to the following can occur during database update operations:

Exception: DB2 SQL error: SQLCODE: -964, 
SQLSTATE: 57011, SQLERRMC: null.

This error can occur because the transaction log space is depleted or because of a temporary increase in the number of active transactions.

Solution

If the transaction log space is depleted, increase the size of LOGPRIMARY on DB2. To do this, complete the following steps:

Establish the current size of LOGPRIMARY by running the following command:
```
db2 get db cfg for taddm | grep LOGP
```
Increase the size of LOGPRIMARY by running the following command:
```
db2 update db cfg for taddm using LOGPRIMARY new_value
```

Also, consider avoiding running multiple, data-intensive transactions simultaneously.

TADDM cannot connect to the database when Oracle Service Name is used

Problem

When instead of SID you use Oracle Service Name, TADDM is not able to connect to the database and DbInit fails.

Solution

If you want to use Oracle Service Name instead of SID, you must modify two properties in the collation.properties file:

Replace the com.collation.db.url=jdbc:oracle:thin:@<server>:<port>:<sid> property with the com.collation.db.url=jdbc:oracle:thin:@//<server>:<port>/<service_name> property.
Replace the com.collation.db.archive.url=jdbc:oracle:thin:@<server>:<port>:<sid> property with the com.collation.db.archive.url=jdbc:oracle:thin:@//<server>:<port>/<service_name> property.

'Not all return parameters registered' exception on the Oracle database

Problem

A storage error occurs on the Oracle database. The following exception is displayed:

java.sql.SQLException: Not all return parameters registered

Solution

Verify what version of JDBC drivers you have. If the drivers are in version 11.2.0.3, you must upgrade them to version 11.2.0.4. For more information, see Database server software requirements.

Business applications are not generated on the Oracle database

Problem

When you try to generate business applications on the Oracle database, the process silently fails. In the error logs, you can find a message that is similar to the following one:

ORA-00600, e.g. ERROR support.TransactionTemplate - Application exception overridden by rollback exception
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL
...
SQL state [60000]; error code [600]; ORA-00600: internal error code, arguments: [rworupo.1], [49410], [22], [], [], [], [], [], [], [], [], []
; nested exception is java.sql.SQLException: ORA-00600: internal error code, arguments: [rworupo.1], [49410], [22], [], [], [], [], [], [], [], [], []

Solution

Check whether you have a supported version of the Oracle database. Oracle Database 12c Release 1 Patch Set 12.1.0.2 is not supported because it contains an error in the XML engine which prevents TADDM from proper functioning. For more information, see Database server software requirements.