CA Chorus Software Manager (CA CSM) MSMTC Tomcat task failed to start with the following errors:
MSM0008E - System startup failed - please see error output for further information. Fatal error that has stopped the startup was: StatementCallback; uncategorized SQLException for SQL .delete from component_fmid.; SQL state .HY000.; errorcode .0.; ca.datacom.db.DBSQLException: ca.datacom.db.DBSQLException: NO ROW FOUND
in Session(executeImmediate)in Session(executeImmediate); nested exception is java.sql.SQLException: ca.datacom.db.
.DBSQLException: ca.datacom.db.DBSQLException: NO ROW FOUND in Session(executeImmediate)in Session(executeImmediate).
MSM0010E - CA CSM startup failed.
CSM 6.1 with Datacom 14.0 components
The underlying problem was inaccurate record count in the CXX which could have been caused by the CSM Multi-user (MSMMUF) crash before CXX updates were completed. This could cause bad record count results to the SQL COUNT(*) issued by the CSM application.
Run CA Datacom batch DBUTLTY with SYSIN "RETIX KEYNAME=*SETR,DBID=4000".
RETIX with KEYNAME=*SETR recounts the records in the area and updates the statistics in the CXX. In general, *SETR is used only to ensure the counts in the CXX are accurate and is not intended for normal use. Note: assure that Datacom 14.0 PTF RO72828 has been applied for use of RETIX.
Alternately, if Support suggests it, a new Multi-user startup option SQL_COUNT_OPT_OFF YES could be considered so that COUNT(*) reads the table to get the number of records instead from the CXX.
SELECT COUNT(*) FROM TABLE does not physically count the rows in the table but uses the row counter that is stored in the CXX. The CSM application runs a DELETE FROM TABLE when the COUNT(*) returns a non-zero number. In the reported case, there were no rows in the table but the CXX row counter was not zero. CSM expects the DELETE to get a zero SQLCODE but there were no rows so the delete resulted in SQLCODE +100 return code. Then, because of the non-zero SQLCODE on the DELETE, CSM stopped the Datacom Server (MSMDBSR) startup which in turn caused the MSMTC startup to fail.
After correcting the CXX row count by running DBUTLTY RETIX with KEYID=*SETR, the row counter in the CXX provided accurate results to the CSM application.
For information on RETIX with *SETR, refer to the CA Datacom/DB DBUTLTY Reference Guide for z/OS
For information on "SQL_COUNT_OPT_OFF", refer to RI78556. Also, assure that Datacom 14.0 PTFs RO78642 and RO78675 are applied.