appmap tables are missing in the CEMDB postgres database for Introscope 10.1

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• INTROSCOPE

Issue/Introduction

Issue:
  Upgraded APM from v9.6x or v9.7x to v10.1 after this the MOM EM log is showing following error messages:

[ERROR] [Thread-44] [Manager.AppMap]
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT data FROM appmap_model_vertices WHERE update_time > ?]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "appmap_model_vertices" does not exist

Position: 18
atorg.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:772)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192)
at com.wily.introscope.appmap.em.model.dao.impl.gmt.GMTNamedParameterJdbcTemplate.query(GMTNamedParameterJdbcTemplate.java:106)
at com.wily.introscope.appmap.em.model.dao.impl.OntologyOperationsDaoImpl.getMergedVertices(OntologyOperationsDaoImpl.java:102)
at sun.reflect.GeneratedMethodAccessor553.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:201)
at com.sun.proxy.$Proxy133.getMergedVertices(Unknown Source)
at com.wily.introscope.appmap.em.ontology.OntologyModelBean.postConstruct(OntologyModelBean.java:82)
at com.wily.introscope.appmap.config.AppMapPostConstructExecutor.initializeAll(AppMapPostConstructExecutor.java:113)
at com.wily.introscope.appmap.config.AppMapPostConstructExecutor.access$1(AppMapPostConstructExecutor.java:100)
at com.wily.introscope.appmap.config.AppMapPostConstructExecutor$1.run(AppMapPostConstructExecutor.java:153)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "appmap_model_vertices" does not exist

  Also, Team Center is not able to mount graphs and starts to throw:

    - "Error retrieving permissions. Status code: 503"
    - "Error reading data from polling URL: ../apm/appmap/private/permission. Retrying in 60000ms."

  It occurs when CEMDB Schema is not matching with the actual Introscope release, if you are not receiving org.springframework.jdbc.BadSqlGrammarException error, please refer to below document to address 503 error:

  http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec1881846.aspx

 

Cause:
  This issue occurs because the difference between 9.6x/9.7x, 10.0 and 10.1 DB schema. When you upgrade the database some tables may missing and causing this error.

 

Workaround:

  Here is the procedure that we need to take. Do this during maintenance hours.

 

    1 - Make a backup of CEMDB postgres database.
    2 - Stop all EMs connected to the CEMDB.
    3 - Open the pgadmin and run the commands in given order one by one in attached files.
         First: create_appmap_tables.txt create_appmap_tablesPART1.txt
         Second: create_appmap_tables.txt create_appmap_tablesPART2.sql
         Third: GRANT-SQL-commands.txt GRANT-SQL-commands_10.1.sql (note you need to replace the <wilydb> user with your admin user for CEMDB)
    4 - Make sure all commands complete successfully, let us know.
    5 - Start all EMs connected to the CEMDB.
    6 - If failure occurs, restore the CEMDB backup and start all EMs connected to the CEMDB.
    7 - Report a issue to CA support with details about the failure.

 

Additional Information:  

 

  Please note that this Knowledge Document only applies when upgrading to Introscope 10.1, if you are upgrading to 10.0 please refer to this other Document:

  http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec1665089.aspx?intcmp=searchresultclick&resultnum=73

 

  The reason to have two knowledge documents addressing this is the structural difference between 10.0 Schema and 10.1 Schema.

 

Environment

Release: CEMUGD00200-10.1-Introscope to CA Application-Performance Management-Upgrade Mai
Component: