Cannot generate APM TeamCenter API token

Document ID : KB000106748
Last Modified Date : 16/07/2018
Show Technical Document Details
Issue:
I cannot generate API security tokens in the Team Center
I can see this error in the Enterprise Manager logs
Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO apm_secure_store ( alias, cipher_text, created_date, last_read_date, user_id, client_id) VALUES (?, ?, ?, ?, ?, ?)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
Environment:
APM 10.7, upgrade from 10.5. Oracle database
Cause:
The apm_secure_store table was missing completely.
This was after an upgrade from 10.5 to 10.7 but the table is not created/altered in 10.7 so it would appear the table has been missing since installation.
Resolution:
You can find the scripts to create the table in the Enterprise Manager installation.
Under EM_HOME/install/oracle/database-scripts, you can find the script createtables-oracle-xx.x.x.x.sql, for example createtables-oracle-10.5.0.0.sql

 
CREATE TABLE apm_secure_store(
alias VARCHAR(256) NOT NULL,
cipher_text CLOB NOT NULL,
created_date TIMESTAMP NOT NULL,
last_read_date TIMESTAMP,
client_id VARCHAR(256) NOT NULL,
user_id VARCHAR(256),
PRIMARY KEY (alias, user_id)
);


 
Additional Information:
Details on creating/managing the database schema manually

https://docops.ca.com/ca-apm/10-7/en/installing/apm-installation/install-and-configure-the-apm-database#InstallandConfiguretheAPMDatabase-RunIndividualSQLScriptstoManagetheAPMDatabaseSchem