Upgrading API Manager from 3.1.0 to 3.2.0

The following information describes how to upgrade your API Manager server from APIM 3.1.0 to 3.2.0.

Note

Before you follow this section, see Upgrading Process for more information.

Attention

If you are using WSO2 Identity Server (WSO2 IS) as a Key Manager, first follow the instructions in Upgrading WSO2 IS as the Key Manager to 5.10.0.

If you are using PostgreSQL

The DB user needs to have superuser role to run the migration client and the relevant scripts

ALTER USER <user> WITH SUPERUSER;

If you are using Oracle

Please commit the changes after running the scripts given below

Follow the instructions below to upgrade your WSO2 API Manager server from WSO2 API-M 3.1.0 to 3.2.0.

Preparing for Migration

Disabling versioning in the registry configuration if it was enabled

If there are frequently updating registry properties, having the versioning enabled for registry resources in the registry can lead to unnecessary growth in the registry related tables in the database. To avoid this, versioning has been disabled by default from API Manager 3.0.0 onwards.

But, if registry versioning was enabled by you in WSO2 API-M 3.1.0 setup, it is required to turn off the registry versioning in the migrated 3.2.0 setup. Please follow the below steps to achieve this.

NOTE

Alternatively, it is possible to turn on registry versioning in API Manager 3.2.0 and continue. But this is highly NOT RECOMMENDED and these configurations should only be changed once.

Turning off registry versioning

Open the registry.xml file in the <OLD_API-M_HOME>/repository/conf directory. Check whether versioningProperties, versioningComments, versioningTags and versioningRatings configurations are true.

<staticConfiguration>
    <versioningProperties>true</versioningProperties>
    <versioningComments>true</versioningComments>
    <versioningTags>true</versioningTags>
    <versioningRatings>true</versioningRatings>
</staticConfiguration>

Warning

If the above configurations are already set as false you should not run the below scripts.

DB Scripts
-- Update the REG_PATH_ID column mapped with the REG_RESOURCE table --

UPDATE REG_RESOURCE_TAG SET REG_RESOURCE_TAG.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION);

UPDATE REG_RESOURCE_COMMENT SET REG_RESOURCE_COMMENT.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION);

UPDATE REG_RESOURCE_PROPERTY SET REG_RESOURCE_PROPERTY.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION);

UPDATE REG_RESOURCE_RATING SET REG_RESOURCE_RATING.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION);

-- Delete versioned tags, were the PATH_ID will be null for older versions --

delete from REG_RESOURCE_PROPERTY where REG_PATH_ID is NULL;

delete from REG_RESOURCE_RATING where REG_PATH_ID is NULL;

delete from REG_RESOURCE_TAG where REG_PATH_ID is NULL;

delete from REG_RESOURCE_COMMENT where REG_PATH_ID is NULL;

delete from REG_PROPERTY where REG_ID NOT IN (select REG_PROPERTY_ID from REG_RESOURCE_PROPERTY);

delete from REG_TAG where REG_ID NOT IN (select REG_TAG_ID from REG_RESOURCE_TAG);

delete from REG_COMMENT where REG_ID NOT IN (select REG_COMMENT_ID from REG_RESOURCE_COMMENT);

delete from REG_RATING where REG_ID NOT IN (select REG_RATING_ID from REG_RESOURCE_RATING);

-- Update the REG_PATH_NAME column mapped with the REG_RESOURCE table --

UPDATE REG_RESOURCE_TAG SET REG_RESOURCE_TAG.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION);

UPDATE REG_RESOURCE_PROPERTY SET REG_RESOURCE_PROPERTY.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION);

UPDATE REG_RESOURCE_COMMENT SET REG_RESOURCE_COMMENT.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION);

UPDATE REG_RESOURCE_RATING SET REG_RESOURCE_RATING.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION);           
-- Update the REG_PATH_ID column mapped with the REG_RESOURCE table --

UPDATE REG_RESOURCE_TAG SET REG_RESOURCE_TAG.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION)
/
UPDATE REG_RESOURCE_COMMENT SET REG_RESOURCE_COMMENT.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION)
/

UPDATE REG_RESOURCE_PROPERTY SET REG_RESOURCE_PROPERTY.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION)
/
UPDATE REG_RESOURCE_RATING SET REG_RESOURCE_RATING.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION)
/

-- Delete versioned tags, were the PATH_ID will be null for older versions --

delete from REG_RESOURCE_PROPERTY where REG_PATH_ID is NULL
/
delete from REG_RESOURCE_RATING where REG_PATH_ID is NULL
/
delete from REG_RESOURCE_TAG where REG_PATH_ID is NULL
/
delete from REG_RESOURCE_COMMENT where REG_PATH_ID is NULL
/
delete from REG_PROPERTY where REG_ID NOT IN (select REG_PROPERTY_ID from REG_RESOURCE_PROPERTY)
/
delete from REG_TAG where REG_ID NOT IN (select REG_TAG_ID from REG_RESOURCE_TAG)
/
delete from REG_COMMENT where REG_ID NOT IN (select REG_COMMENT_ID from REG_RESOURCE_COMMENT)
/
delete from REG_RATING where REG_ID NOT IN (select REG_RATING_ID from REG_RESOURCE_RATING)
/

-- Update the REG_PATH_NAME column mapped with the REG_RESOURCE table --

UPDATE REG_RESOURCE_TAG SET REG_RESOURCE_TAG.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION)
/
UPDATE REG_RESOURCE_PROPERTY SET REG_RESOURCE_PROPERTY.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION)
/
UPDATE REG_RESOURCE_COMMENT SET REG_RESOURCE_COMMENT.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION)
/
UPDATE REG_RESOURCE_RATING SET REG_RESOURCE_RATING.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION)
/
-- Update the REG_PATH_ID column mapped with the REG_RESOURCE table --
UPDATE REG_RESOURCE_TAG SET REG_PATH_ID=(SELECT REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION);

UPDATE REG_RESOURCE_COMMENT SET REG_PATH_ID=(SELECT REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION);

UPDATE REG_RESOURCE_PROPERTY SET REG_PATH_ID=(SELECT REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION);

UPDATE REG_RESOURCE_RATING SET REG_PATH_ID=(SELECT REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION);

-- Delete versioned tags, were the PATH_ID will be null for older versions --
delete from REG_RESOURCE_PROPERTY where REG_PATH_ID is NULL;

delete from REG_RESOURCE_RATING where REG_PATH_ID is NULL;

delete from REG_RESOURCE_TAG where REG_PATH_ID is NULL;

delete from REG_RESOURCE_COMMENT where REG_PATH_ID is NULL;

delete from REG_PROPERTY where REG_ID NOT IN (select REG_PROPERTY_ID from REG_RESOURCE_PROPERTY);

delete from REG_TAG where REG_ID NOT IN (select REG_TAG_ID from REG_RESOURCE_TAG);

delete from REG_COMMENT where REG_ID NOT IN (select REG_COMMENT_ID from REG_RESOURCE_COMMENT);

delete from REG_RATING where REG_ID NOT IN (select REG_RATING_ID from REG_RESOURCE_RATING);

-- Update the REG_PATH_NAME column mapped with the REG_RESOURCE table --
UPDATE REG_RESOURCE_TAG SET REG_RESOURCE_NAME=(SELECT REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION);

UPDATE REG_RESOURCE_PROPERTY SET REG_RESOURCE_NAME=(SELECT REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION);

UPDATE REG_RESOURCE_COMMENT SET REG_RESOURCE_NAME=(SELECT REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION);

UPDATE REG_RESOURCE_RATING SET REG_RESOURCE_NAME=(SELECT REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION);  
-- Update the REG_PATH_ID column mapped with the REG_RESOURCE table --

UPDATE REG_RESOURCE_TAG SET REG_RESOURCE_TAG.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION);

UPDATE REG_RESOURCE_COMMENT SET REG_RESOURCE_COMMENT.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION);

UPDATE REG_RESOURCE_PROPERTY SET REG_RESOURCE_PROPERTY.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION);

UPDATE REG_RESOURCE_RATING SET REG_RESOURCE_RATING.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION);


-- Delete versioned tags, were the PATH_ID will be null for older versions --

delete from REG_RESOURCE_PROPERTY where REG_PATH_ID is NULL;

delete from REG_RESOURCE_RATING where REG_PATH_ID is NULL;

delete from REG_RESOURCE_TAG where REG_PATH_ID is NULL;

delete from REG_RESOURCE_COMMENT where REG_PATH_ID is NULL;

delete from REG_PROPERTY where REG_ID NOT IN (select REG_PROPERTY_ID from REG_RESOURCE_PROPERTY);

delete from REG_TAG where REG_ID NOT IN (select REG_TAG_ID from REG_RESOURCE_TAG);

delete from REG_COMMENT where REG_ID NOT IN (select REG_COMMENT_ID from REG_RESOURCE_COMMENT);

delete from REG_RATING where REG_ID NOT IN (select REG_RATING_ID from REG_RESOURCE_RATING);

-- Update the REG_PATH_NAME column mapped with the REG_RESOURCE table --

UPDATE REG_RESOURCE_TAG SET REG_RESOURCE_TAG.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION);

UPDATE REG_RESOURCE_PROPERTY SET REG_RESOURCE_PROPERTY.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION);

UPDATE REG_RESOURCE_COMMENT SET REG_RESOURCE_COMMENT.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION);

UPDATE REG_RESOURCE_RATING SET REG_RESOURCE_RATING.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION);
-- Update the REG_PATH_ID column mapped with the REG_RESOURCE table --
UPDATE REG_RESOURCE_TAG SET REG_RESOURCE_TAG.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION)
/
UPDATE REG_RESOURCE_COMMENT SET REG_RESOURCE_COMMENT.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION)
/
UPDATE REG_RESOURCE_PROPERTY SET REG_RESOURCE_PROPERTY.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION)
/
UPDATE REG_RESOURCE_RATING SET REG_RESOURCE_RATING.REG_PATH_ID=(SELECT REG_RESOURCE.REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION)
/

-- Delete versioned tags, were the PATH_ID will be null for older versions --
delete from REG_RESOURCE_PROPERTY where REG_PATH_ID is NULL
/
delete from REG_RESOURCE_RATING where REG_PATH_ID is NULL
/
delete from REG_RESOURCE_TAG where REG_PATH_ID is NULL
/
delete from REG_RESOURCE_COMMENT where REG_PATH_ID is NULL
/
delete from REG_PROPERTY where REG_ID NOT IN (select REG_PROPERTY_ID from REG_RESOURCE_PROPERTY)
/
delete from REG_TAG where REG_ID NOT IN (select REG_TAG_ID from REG_RESOURCE_TAG)
/
delete from REG_COMMENT where REG_ID NOT IN (select REG_COMMENT_ID from REG_RESOURCE_COMMENT)
/
delete from REG_RATING where REG_ID NOT IN (select REG_RATING_ID from REG_RESOURCE_RATING)
/

-- Update the REG_PATH_NAME column mapped with the REG_RESOURCE table --
UPDATE REG_RESOURCE_TAG SET REG_RESOURCE_TAG.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION)
/
UPDATE REG_RESOURCE_PROPERTY SET REG_RESOURCE_PROPERTY.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION)
/
UPDATE REG_RESOURCE_COMMENT SET REG_RESOURCE_COMMENT.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION)
/
UPDATE REG_RESOURCE_RATING SET REG_RESOURCE_RATING.REG_RESOURCE_NAME=(SELECT REG_RESOURCE.REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION)
/
-- Update the REG_PATH_ID column mapped with the REG_RESOURCE table --
UPDATE REG_RESOURCE_TAG SET REG_PATH_ID=(SELECT REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION);

UPDATE REG_RESOURCE_COMMENT SET REG_PATH_ID=(SELECT REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION);

UPDATE REG_RESOURCE_PROPERTY SET REG_PATH_ID=(SELECT REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION);

UPDATE REG_RESOURCE_RATING SET REG_PATH_ID=(SELECT REG_PATH_ID FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION);

-- Delete versioned tags, were the PATH_ID will be null for older versions --
delete from REG_RESOURCE_PROPERTY where REG_PATH_ID is NULL;

delete from REG_RESOURCE_RATING where REG_PATH_ID is NULL;

delete from REG_RESOURCE_TAG where REG_PATH_ID is NULL;

delete from REG_RESOURCE_COMMENT where REG_PATH_ID is NULL;

delete from REG_PROPERTY where REG_ID NOT IN (select REG_PROPERTY_ID from REG_RESOURCE_PROPERTY);

delete from REG_TAG where REG_ID NOT IN (select REG_TAG_ID from REG_RESOURCE_TAG);

delete from REG_COMMENT where REG_ID NOT IN (select REG_COMMENT_ID from REG_RESOURCE_COMMENT);

delete from REG_RATING where REG_ID NOT IN (select REG_RATING_ID from REG_RESOURCE_RATING);

-- Update the REG_PATH_NAME column mapped with the REG_RESOURCE table --
UPDATE REG_RESOURCE_TAG SET REG_RESOURCE_NAME=(SELECT REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_TAG.REG_VERSION);

UPDATE REG_RESOURCE_PROPERTY SET REG_RESOURCE_NAME=(SELECT REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_PROPERTY.REG_VERSION);

UPDATE REG_RESOURCE_COMMENT SET REG_RESOURCE_NAME=(SELECT REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_COMMENT.REG_VERSION);

UPDATE REG_RESOURCE_RATING SET REG_RESOURCE_NAME=(SELECT REG_NAME FROM REG_RESOURCE WHERE REG_RESOURCE.REG_VERSION=REG_RESOURCE_RATING.REG_VERSION);

Not recommended

If you decide to proceed with registry resource versioning enabled, Add the following configuration to the <NEW_API-M_HOME>/repository/conf/deployment.toml file of new WSO2 API Manager.

[registry.static_configuration]
enable=true

NOTE

Changing these configuration should only be done before the initial API-M Server startup. If changes are done after the initial startup, the registry resource created previously will not be available.

Step 1 - Migrate the API Manager configurations

Warning

Do not copy entire configuration files from the current version of WSO2 API Manager to the new one, as some configuration files may have changed. Instead, redo the configuration changes in the new configuration files.

Follow the instructions below to move all the existing API Manager configurations from the current environment to the new one. For more information refer Configuration Catalog

  1. Back up all databases in your API Manager instances along with the Synapse configurations of all the tenants and the super tenant.

    • The Synapse configurations of the super tenant are in the <OLD_API-M_HOME>/repository/deployment/server/synapse-configs/default directory.

    • The Synapse configurations of tenants are in the <OLD_API-M_HOME>/repository/tenants directory.

    • If you use a clustered/distributed API Manager setup , back up the available configurations in the API Gateway node.

  2. Download WSO2 API Manager 3.2.0.

  3. Open the <API-M_3.2.0_HOME>/repository/conf/deployment.toml file and provide the datasource configurations for the following databases.

    • User Store
    • Registry database/s
    • API Manager databases

    Note

    If you have used separate DBs for user management and registry in the previous version, you need to configure WSO2REG_DB and WSO2UM_DB databases separately in API-M 3.2.0 to avoid any issues.

    SHARED_DB should point to the previous API-M version's WSO2REG_DB. This example shows to configure MySQL database configurations.

    [database.apim_db]
    type = "mysql"
    url = "jdbc:mysql://localhost:3306/am_db"
    username = "username"
    password = "password"
    
    [database.shared_db]
    type = "mysql"
    url = "jdbc:mysql://localhost:3306/reg_db"
    username = "username"
    password = "password"

    Optionally add a new entry as below to the deployment.toml if you have configured a seperate user management database in the previous API-M version.

    [database.user]
    type = "mysql"
    url = "jdbc:mysql://localhost:3306/um_db"
    username = "username"
    password = "password"

    Note

    If you have configured WSO2CONFIG_DB in the previous API-M version, add a new entry to the <API-M_3.2.0_HOME>/repository/conf/deployment.toml as below.

    [database.config]
    type = "mysql"
    url = "jdbc:mysql://localhost:3306/config_db"
    username = "username"
    password = "password"

    If you are using another DB type

    If you are using another DB type other than H2 or MySQL or Oracle, when defining the DB related configurations in the deployment.toml file, you need to add the driver and validationQuery parameters additionally as given below.

    [database.apim_db]
    type = "mssql"
    url = "jdbc:sqlserver://localhost:1433;databaseName=mig_am_db;SendStringParametersAsUnicode=false"
    username = "username"
    password = "password"
    driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    validationQuery = "SELECT 1"
    [database.apim_db]
    type = "postgre"
    url = "jdbc:postgresql://localhost:5432/mig_am_db"
    username = "username"
    password = "password"
    driver = "org.postgresql.Driver"
    validationQuery = "SELECT 1"
    [database.apim_db]
    type = "oracle"
    url = "jdbc:oracle:thin:@localhost:1521/mig_am_db"
    username = "username"
    password = "password"
    driver = "oracle.jdbc.driver.OracleDriver"
    validationQuery = "SELECT 1 FROM DUAL"
    [database.apim_db]
    type = "db2"
    url = "jdbc:db2://localhost:50000/mig_am_db"
    username = "username"
    password = "password"
    driver = "com.ibm.db2.jcc.DB2Driver"
    validationQuery = "SELECT 1 FROM SYSIBM.SYSDUMMY1"
  4. If you have used separate DB for user management, you need to update <API-M_3.2.0_HOME>/repository/conf/deployment.toml file as follows, to point to the correct database for user management purposes.

    [realm_manager]
    data_source = "WSO2USER_DB"
  5. Copy the relevant JDBC driver to the <API-M_3.2.0_HOME>/repository/components/lib folder.

  6. Move all your Synapse configurations to API-M 3.2.0 pack.

    • Move your Synapse super tenant configurations. Copy the contents in the <OLD_API-M_HOME>/repository/deployment/server/synapse-configs/default directory and replace the contents in the <API-M_3.2.0_HOME>/repository/deployment/server/synapse-configs/default directory with the copied contents.
    • Move all your tenant Synapse configurations. Copy the contents in the <OLD_API-M_HOME>/repository/tenants directory and replace the contents in the <API-M_3.2.0_HOME>/repository/tenants directory with the copied contents.

    Warning

    When moving the Synapse configurations, do not replace the following set of files as they contain some modifications in API-M 3.2.0 version.

    • /proxy-services/WorkflowCallbackService.xml

    Attention

    If you are working with a clustered/distributed API Manager setup, follow this step on the Gateway node.

  7. Move all your Execution plans from <API-M_3.1.0_HOME>/repository/deployment/server/executionplans directory to <API-M_3.2.0_HOME>/repository/deployment/server/executionplans directory.

    Note

    If you are working with a clustered/distributed API Manager setup, follow this step on the Traffic Manager node.

  8. If you manually added any custom OSGI bundles to the <API-M_3.1.0_HOME>/repository/components/dropins directory, copy those to the <API-M_3.2.0_HOME>/repository/components/dropins directory.

  9. If you manually added any JAR files to the <API-M_3.1.0_HOME>/repository/components/lib directory, copy those and paste them in the <API-M_3.2.0_HOME>/repository/components/lib directory.

Step 2 - Upgrade API Manager to 3.2.0

  1. Stop all WSO2 API Manager server instances that are running.

  2. Make sure you backed up all the databases and Synapse configurations as instructed in step 1 of the previous section.

  3. Upgrade the WSO2 API Manager database from version 3.1.0 to version 3.2.0 by executing the relevant database script, from the scripts that are provided below, on the WSO2AM_DB database.

    DB Scripts
     CREATE TABLE IF NOT EXISTS AM_KEY_MANAGER (
      UUID VARCHAR(50) NOT NULL,
      NAME VARCHAR(100) NOT NULL,
      DISPLAY_NAME VARCHAR(100) NULL,
      DESCRIPTION VARCHAR(256) NULL,
      TYPE VARCHAR(45) NULL,
      CONFIGURATION BLOB NULL,
      ENABLED BOOLEAN DEFAULT 1,
      TENANT_DOMAIN VARCHAR(100) NULL,
      PRIMARY KEY (UUID),
      UNIQUE (NAME,TENANT_DOMAIN)
      );
    
     CREATE TABLE IF NOT EXISTS AM_GW_PUBLISHED_API_DETAILS (
      API_ID varchar(255) NOT NULL,
      TENANT_DOMAIN varchar(255),
      API_PROVIDER varchar(255),
      API_NAME varchar(255),
      API_VERSION varchar(255),
      PRIMARY KEY (API_ID)
      );
    
     CREATE TABLE IF NOT EXISTS AM_GW_API_ARTIFACTS (
      API_ID varchar(255) NOT NULL,
      ARTIFACT blob,
      GATEWAY_INSTRUCTION varchar(20),
      GATEWAY_LABEL varchar(255),
      TIME_STAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (GATEWAY_LABEL, API_ID),
      FOREIGN KEY (API_ID) REFERENCES AM_GW_PUBLISHED_API_DETAILS(API_ID) ON UPDATE CASCADE ON DELETE NO ACTION
     );
    
    CREATE ALIAS IF NOT EXISTS DROP_FK AS $$ void executeSql(Connection conn, String sql)
    throws SQLException { conn.createStatement().executeUpdate(sql); } $$;
    
    call drop_fk('ALTER TABLE AM_APPLICATION_REGISTRATION DROP CONSTRAINT ' ||
    (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS
    WHERE TABLE_NAME = 'AM_APPLICATION_REGISTRATION' AND COLUMN_LIST  = 'SUBSCRIBER_ID,APP_ID,TOKEN_TYPE'));
    
    call drop_fk('ALTER TABLE AM_APPLICATION_KEY_MAPPING DROP CONSTRAINT ' ||
    (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS
    WHERE TABLE_NAME = 'AM_APPLICATION_KEY_MAPPING' AND COLUMN_LIST  = 'APPLICATION_ID,KEY_TYPE'));
    DROP ALIAS IF EXISTS DROP_FK;
    
    ALTER TABLE AM_APPLICATION_REGISTRATION ADD KEY_MANAGER VARCHAR(255) DEFAULT 'Resident Key Manager';
    ALTER TABLE AM_APPLICATION_REGISTRATION ADD UNIQUE (SUBSCRIBER_ID,APP_ID,TOKEN_TYPE,KEY_MANAGER);
    
    
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD UUID VARCHAR(512) NULL;
    UPDATE AM_APPLICATION_KEY_MAPPING SET UUID = random_uuid() WHERE UUID IS NULL;
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD KEY_MANAGER VARCHAR(512) NOT NULL DEFAULT 'Resident Key Manager';
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD APP_INFO BLOB;
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD PRIMARY KEY(APPLICATION_ID,KEY_TYPE,KEY_MANAGER);
    
    ALTER TABLE AM_WORKFLOWS ADD WF_METADATA BLOB NULL;
    ALTER TABLE AM_WORKFLOWS ADD WF_PROPERTIES BLOB NULL;
    
    ALTER TABLE AM_SUBSCRIPTION ADD TIER_ID_PENDING VARCHAR(50);
    
    ALTER TABLE AM_POLICY_SUBSCRIPTION ADD MAX_COMPLEXITY INT(11) NOT NULL DEFAULT 0;
    ALTER TABLE AM_POLICY_SUBSCRIPTION ADD MAX_DEPTH INT(11) NOT NULL DEFAULT 0;
    
    CREATE TABLE IF NOT EXISTS AM_API_RESOURCE_SCOPE_MAPPING (
        SCOPE_NAME VARCHAR(255) NOT NULL,
        URL_MAPPING_ID INTEGER NOT NULL,
        TENANT_ID INTEGER NOT NULL,
        FOREIGN KEY (URL_MAPPING_ID) REFERENCES   AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
        PRIMARY KEY(SCOPE_NAME, URL_MAPPING_ID)
    );
    
    
    CREATE TABLE IF NOT EXISTS AM_SHARED_SCOPE (
         NAME VARCHAR(255),
         UUID VARCHAR (256),
         TENANT_ID INTEGER,
         PRIMARY KEY (UUID)
    );
    
    ALTER TABLE IDN_OAUTH2_RESOURCE_SCOPE DROP PRIMARY KEY;
    
    DROP TABLE IF EXISTS AM_TENANT_THEMES;
    CREATE TABLE IF NOT EXISTS AM_TENANT_THEMES (
      TENANT_ID INTEGER NOT NULL,
      THEME BYTEA NOT NULL,
      PRIMARY KEY (TENANT_ID)
    );
    
    CREATE TABLE IF NOT EXISTS AM_GRAPHQL_COMPLEXITY (
        UUID VARCHAR(256),
        API_ID INTEGER NOT NULL,
        TYPE VARCHAR(256),
        FIELD VARCHAR(256),
        COMPLEXITY_VALUE INTEGER,
        FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE CASCADE,
        PRIMARY KEY(UUID),
        UNIQUE (API_ID,TYPE,FIELD)
    );
    
    UPDATE IDN_OAUTH_CONSUMER_APPS SET CALLBACK_URL="" WHERE CALLBACK_URL IS NULL;  
    
    CREATE TABLE IF NOT EXISTS AM_SCOPE (
        SCOPE_ID INTEGER NOT NULL AUTO_INCREMENT,
        NAME VARCHAR(255) NOT NULL,
        DISPLAY_NAME VARCHAR(255) NOT NULL,
        DESCRIPTION VARCHAR(512),
        TENANT_ID INTEGER NOT NULL DEFAULT -1,
        SCOPE_TYPE VARCHAR(255) NOT NULL,
        PRIMARY KEY (SCOPE_ID)
    );
    
    CREATE TABLE IF NOT EXISTS AM_SCOPE_BINDING (
        SCOPE_ID INTEGER NOT NULL,
        SCOPE_BINDING VARCHAR(255) NOT NULL,
        BINDING_TYPE VARCHAR(255) NOT NULL,
        FOREIGN KEY (SCOPE_ID) REFERENCES AM_SCOPE(SCOPE_ID) ON DELETE CASCADE
    );     
    
    DELETE FROM IDN_OAUTH2_SCOPE_BINDING WHERE SCOPE_BINDING IS NULL OR SCOPE_BINDING = '';   
    ALTER TABLE AM_WORKFLOWS
      ADD WF_METADATA BLOB DEFAULT NULL
      ADD WF_PROPERTIES BLOB DEFAULT NULL
    /
    
    CREATE TABLE AM_GW_PUBLISHED_API_DETAILS (
      API_ID varchar(255) NOT NULL,
      TENANT_DOMAIN varchar(255),
      API_PROVIDER varchar(255),
      API_NAME varchar(255),
      API_VERSION varchar(255),
      PRIMARY KEY (API_ID)
    ) /
    
    CREATE TABLE AM_GW_API_ARTIFACTS (
      API_ID varchar(255) NOT NULL,
      ARTIFACT blob,
      GATEWAY_INSTRUCTION varchar(20),
      GATEWAY_LABEL varchar(255) NOT NULL,
      TIME_STAMP TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
      PRIMARY KEY (GATEWAY_LABEL, API_ID),
      FOREIGN KEY (API_ID) REFERENCES AM_GW_PUBLISHED_API_DETAILS (API_ID) ON DELETE NO ACTION ON UPDATE RESTRICT
    ) /
    
    ALTER TABLE AM_SUBSCRIPTION ADD TIER_ID_PENDING VARCHAR(50) /
    
    ALTER TABLE AM_POLICY_SUBSCRIPTION
        ADD MAX_COMPLEXITY INT NOT NULL DEFAULT 0
        ADD MAX_DEPTH INT NOT NULL DEFAULT 0
    /
    
    CREATE TABLE IF NOT EXISTS AM_API_RESOURCE_SCOPE_MAPPING (
        SCOPE_NAME varchar(255) NOT NULL,
        URL_MAPPING_ID INTEGER NOT NULL,
        TENANT_ID INTEGER NOT NULL,
        FOREIGN KEY (URL_MAPPING_ID) REFERENCES   AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
        PRIMARY KEY(SCOPE_NAME, URL_MAPPING_ID)
    ) /
    
    
    CREATE TABLE IF NOT EXISTS AM_SHARED_SCOPE (
         NAME varchar(255),
         UUID varchar(256) NOT NULL,
         TENANT_ID INTEGER,
         PRIMARY KEY (UUID)
    ) /
    
    ALTER TABLE IDN_OAUTH2_RESOURCE_SCOPE DROP PRIMARY KEY /
    
    CREATE TABLE AM_KEY_MANAGER (
      UUID VARCHAR(50) NOT NULL,
      NAME VARCHAR(100) NOT NULL,
      DISPLAY_NAME VARCHAR(100) NULL,
      DESCRIPTION VARCHAR(256) NULL,
      TYPE VARCHAR(45) NULL,
      CONFIGURATION BLOB NULL,
      ENABLED SMALLINT DEFAULT 1,
      TENANT_DOMAIN VARCHAR(100) NOT NULL,
      PRIMARY KEY (UUID),
      UNIQUE (NAME,TENANT_DOMAIN)
    )
     /
    
     CREATE TABLE AM_TENANT_THEMES (
      TENANT_ID INTEGER NOT NULL,
      THEME BLOB NOT NULL,
      PRIMARY KEY (TENANT_ID)
    ) /
    
    CREATE TABLE AM_GRAPHQL_COMPLEXITY (
        UUID VARCHAR(256) NOT NULL,
        API_ID INTEGER NOT NULL,
        TYPE VARCHAR(256) NOT NULL,
        FIELD VARCHAR(256) NOT NULL,
        COMPLEXITY_VALUE INTEGER,
        FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
        PRIMARY KEY(UUID),
        UNIQUE (API_ID,TYPE,FIELD)
    )/
    
    UPDATE IDN_OAUTH_CONSUMER_APPS SET CALLBACK_URL='' WHERE CALLBACK_URL IS NULL /
    
    BEGIN
    DECLARE const_name VARCHAR(128);
    DECLARE STMT VARCHAR(200);
    select CONSTNAME into const_name from SYSCAT.TABCONST WHERE TABNAME='AM_APPLICATION_REGISTRATION' AND TYPE = 'U';
    SET STMT = 'ALTER TABLE AM_APPLICATION_REGISTRATION DROP UNIQUE ' ||  const_name;
    PREPARE S1 FROM STMT;
    EXECUTE S1;
    END
    /
    
    ALTER TABLE AM_APPLICATION_REGISTRATION ADD KEY_MANAGER VARCHAR(255) DEFAULT 'Resident Key Manager'/
    ALTER TABLE AM_APPLICATION_REGISTRATION ADD UNIQUE (SUBSCRIBER_ID,APP_ID,TOKEN_TYPE,KEY_MANAGER)/
    
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD UUID VARCHAR(50)/
    UPDATE AM_APPLICATION_KEY_MAPPING SET UUID = (VARCHAR(HEX(GENERATE_UNIQUE()))) WHERE UUID IS NULL;
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD KEY_MANAGER VARCHAR(50) NOT NULL DEFAULT 'Resident Key Manager'/
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD APP_INFO BLOB/
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD UNIQUE(APPLICATION_ID,KEY_TYPE,KEY_MANAGER)/
    ALTER TABLE AM_APPLICATION_KEY_MAPPING DROP PRIMARY KEY/
    
    CREATE TABLE AM_SCOPE (
        SCOPE_ID INTEGER NOT NULL,
        NAME VARCHAR(255) NOT NULL,
        DISPLAY_NAME VARCHAR(255) NOT NULL,
        DESCRIPTION VARCHAR(512),
        TENANT_ID INTEGER NOT NULL DEFAULT -1,
        SCOPE_TYPE VARCHAR(255) NOT NULL,
        PRIMARY KEY (SCOPE_ID)
    )/
    CREATE SEQUENCE AM_SCOPE_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
    /
    CREATE TRIGGER AM_SCOPE_TRIGGER NO CASCADE BEFORE INSERT ON AM_SCOPE REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
    
    BEGIN ATOMIC
    
        SET (NEW.SCOPE_ID)
        = (NEXTVAL FOR AM_SCOPE_SEQUENCE);
    
    END
    /
    CREATE TABLE AM_SCOPE_BINDING (
                SCOPE_ID INTEGER NOT NULL,
                SCOPE_BINDING VARCHAR(255) NOT NULL,
                BINDING_TYPE VARCHAR(255) NOT NULL,
                FOREIGN KEY (SCOPE_ID) REFERENCES AM_SCOPE(SCOPE_ID) ON DELETE CASCADE)
    /
    DELETE FROM IDN_OAUTH2_SCOPE_BINDING WHERE SCOPE_BINDING IS NULL OR SCOPE_BINDING = ''  / 
    ALTER TABLE AM_WORKFLOWS ADD
    WF_METADATA VARBINARY(MAX) NULL DEFAULT NULL,
    WF_PROPERTIES VARBINARY(MAX) NULL DEFAULT NULL
    ;
    
    IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_GW_PUBLISHED_API_DETAILS]') AND TYPE IN (N'U'))
    CREATE TABLE  AM_GW_PUBLISHED_API_DETAILS (
    API_ID varchar(255) NOT NULL,
    TENANT_DOMAIN varchar(255),
    API_PROVIDER varchar(255),
    API_NAME varchar(255),
    API_VERSION varchar(255),
    PRIMARY KEY (API_ID)
    );
    
    IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_GW_API_ARTIFACTS]') AND TYPE IN (N'U'))
    CREATE TABLE  AM_GW_API_ARTIFACTS (
    API_ID varchar(255) NOT NULL,
    ARTIFACT VARBINARY(MAX),
    GATEWAY_INSTRUCTION varchar(20),
    GATEWAY_LABEL varchar(255),
    TIMESTAMP DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (GATEWAY_LABEL, API_ID),
    FOREIGN KEY (API_ID) REFERENCES AM_GW_PUBLISHED_API_DETAILS(API_ID) ON UPDATE CASCADE ON DELETE NO ACTION
    );
    
    GO
    CREATE TRIGGER dbo.TIMESTAMP ON dbo.AM_GW_API_ARTIFACTS
    AFTER INSERT, UPDATE
    AS
    UPDATE f set TIMESTAMP=GETDATE()
    FROM
    dbo.[AM_GW_API_ARTIFACTS] AS f
    INNER JOIN inserted
    AS i
    ON f.TIMESTAMP = i.TIMESTAMP;
    GO
    
    ALTER TABLE AM_SUBSCRIPTION ADD TIER_ID_PENDING VARCHAR(50);
    
    ALTER TABLE AM_POLICY_SUBSCRIPTION ADD
    MAX_COMPLEXITY INTEGER NOT NULL DEFAULT 0,
    MAX_DEPTH INTEGER NOT NULL DEFAULT 0
    ;
    
    IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_RESOURCE_SCOPE_MAPPING]') AND TYPE IN (N'U'))
    CREATE TABLE AM_API_RESOURCE_SCOPE_MAPPING (
        SCOPE_NAME VARCHAR(255) NOT NULL,
        URL_MAPPING_ID INTEGER NOT NULL,
        TENANT_ID INTEGER NOT NULL,
        FOREIGN KEY (URL_MAPPING_ID) REFERENCES   AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
        PRIMARY KEY(SCOPE_NAME, URL_MAPPING_ID)
    );
    
    IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_SHARED_SCOPE]') AND TYPE IN (N'U'))
    CREATE TABLE AM_SHARED_SCOPE (
        NAME VARCHAR(255),
        UUID VARCHAR (256),
        TENANT_ID INTEGER,
        PRIMARY KEY (UUID)
    );
    
    DECLARE @SQL VARCHAR(4000);
    SET @SQL = 'ALTER TABLE |TABLE_NAME| DROP CONSTRAINT |CONSTRAINT_NAME|';
    
    SET @SQL = REPLACE(@SQL, '|CONSTRAINT_NAME|',( SELECT name FROM sysobjects WHERE xtype = 'PK' AND parent_obj = OBJECT_ID('IDN_OAUTH2_RESOURCE_SCOPE')));
    SET @SQL = REPLACE(@SQL,'|TABLE_NAME|','IDN_OAUTH2_RESOURCE_SCOPE');
    EXEC (@SQL);
    
    IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_KEY_MANAGER]') AND TYPE IN (N'U'))
    CREATE TABLE AM_KEY_MANAGER (
    UUID VARCHAR(50) NOT NULL,
    NAME VARCHAR(100) NULL,
    DISPLAY_NAME VARCHAR(100) NULL,
    DESCRIPTION VARCHAR(256) NULL,
    TYPE VARCHAR(45) NULL,
    CONFIGURATION VARBINARY(MAX) NULL,
    ENABLED BIT DEFAULT 1,
    TENANT_DOMAIN VARCHAR(100) NULL,
    PRIMARY KEY (UUID),
    UNIQUE (NAME,TENANT_DOMAIN)
    );
    
    IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_TENANT_THEMES]') AND TYPE IN (N'U'))
    CREATE TABLE AM_TENANT_THEMES (
    TENANT_ID INTEGER NOT NULL,
    THEME VARBINARY(MAX) NOT NULL,
    PRIMARY KEY (TENANT_ID)
    );
    
    IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_GRAPHQL_COMPLEXITY]') AND TYPE IN (N'U'))
    CREATE TABLE AM_GRAPHQL_COMPLEXITY (
        UUID VARCHAR(256),
        API_ID INTEGER NOT NULL,
        TYPE VARCHAR(256),
        FIELD VARCHAR(256),
        COMPLEXITY_VALUE INTEGER,
        FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE CASCADE,
        PRIMARY KEY(UUID),
        UNIQUE (API_ID,TYPE,FIELD)
    );
    
    UPDATE IDN_OAUTH_CONSUMER_APPS SET CALLBACK_URL='' WHERE CALLBACK_URL IS NULL;
    
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD UUID VARCHAR(50);
    GO
    UPDATE AM_APPLICATION_KEY_MAPPING SET UUID = NEWID() WHERE UUID IS NULL;
    GO
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD KEY_MANAGER VARCHAR(50) NOT NULL DEFAULT 'Resident Key Manager';
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD APP_INFO VARBINARY(MAX);
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD CONSTRAINT app_key_unique_cns UNIQUE (APPLICATION_ID,KEY_TYPE,KEY_MANAGER);
    DECLARE @ap_keymap as VARCHAR(8000);
    SET @ap_keymap = (SELECT name from sys.objects where parent_object_id=object_id('AM_APPLICATION_KEY_MAPPING') AND type='PK');
    EXEC('ALTER TABLE AM_APPLICATION_KEY_MAPPING
    drop CONSTRAINT ' + @ap_keymap);
    
    DECLARE @am_appreg as VARCHAR(8000);
    SET @am_appreg = (SELECT name from sys.objects where parent_object_id=object_id('AM_APPLICATION_REGISTRATION') AND type='UQ');
    EXEC('ALTER TABLE AM_APPLICATION_REGISTRATION
    drop CONSTRAINT ' + @am_appreg);
    
    ALTER TABLE AM_APPLICATION_REGISTRATION ADD KEY_MANAGER VARCHAR(255) DEFAULT 'Resident Key Manager';
    ALTER TABLE AM_APPLICATION_REGISTRATION ADD UNIQUE (SUBSCRIBER_ID,APP_ID,TOKEN_TYPE,KEY_MANAGER); 
    
    IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_SCOPE]') AND TYPE IN (N'U'))
    CREATE TABLE AM_SCOPE (
    SCOPE_ID INTEGER IDENTITY,
    NAME VARCHAR(255) NOT NULL,
    DISPLAY_NAME VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(512),
    TENANT_ID INTEGER NOT NULL DEFAULT -1,
    SCOPE_TYPE VARCHAR(255) NOT NULL,
    PRIMARY KEY (SCOPE_ID)
    );
    
    IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_SCOPE_BINDING]') AND TYPE IN (N'U'))
    CREATE TABLE AM_SCOPE_BINDING (
    SCOPE_ID INTEGER NOT NULL,
    SCOPE_BINDING VARCHAR(255) NOT NULL,
    BINDING_TYPE VARCHAR(255) NOT NULL,
    FOREIGN KEY (SCOPE_ID) REFERENCES AM_SCOPE(SCOPE_ID) ON DELETE CASCADE
    );        
    
    DELETE FROM IDN_OAUTH2_SCOPE_BINDING WHERE SCOPE_BINDING IS NULL OR SCOPE_BINDING = '';
     CREATE TABLE IF NOT EXISTS AM_KEY_MANAGER (
      UUID VARCHAR(50) NOT NULL,
      NAME VARCHAR(100) NOT NULL,
      DISPLAY_NAME VARCHAR(100) NULL,
      DESCRIPTION VARCHAR(256) NULL,
      TYPE VARCHAR(45) NULL,
      CONFIGURATION BLOB NULL,
      ENABLED BOOLEAN DEFAULT 1,
      TENANT_DOMAIN VARCHAR(100) NULL,
      PRIMARY KEY (UUID),
      UNIQUE (NAME,TENANT_DOMAIN)
      );
    
     CREATE TABLE IF NOT EXISTS AM_GW_PUBLISHED_API_DETAILS (
      API_ID varchar(255) NOT NULL,
      TENANT_DOMAIN varchar(255),
      API_PROVIDER varchar(255),
      API_NAME varchar(255),
      API_VERSION varchar(255),
      PRIMARY KEY (API_ID)
     ) ENGINE=InnoDB;
    
     CREATE TABLE IF NOT EXISTS AM_GW_API_ARTIFACTS (
      API_ID varchar(255) NOT NULL,
      ARTIFACT blob,
      GATEWAY_INSTRUCTION varchar(20),
      GATEWAY_LABEL varchar(255),
      TIME_STAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (GATEWAY_LABEL, API_ID),
      FOREIGN KEY (API_ID) REFERENCES AM_GW_PUBLISHED_API_DETAILS(API_ID) ON UPDATE CASCADE ON DELETE NO ACTION
     ) ENGINE=InnoDB;
    
    SELECT CONCAT("ALTER TABLE AM_APPLICATION_REGISTRATION DROP INDEX ",constraint_name)
    INTO @sqlst
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = database() AND TABLE_NAME = "AM_APPLICATION_REGISTRATION"
    AND constraint_type='UNIQUE';
    
    ALTER TABLE AM_APPLICATION_REGISTRATION ADD KEY_MANAGER VARCHAR(255) DEFAULT 'Resident Key Manager';
    ALTER TABLE AM_APPLICATION_REGISTRATION ADD UNIQUE (SUBSCRIBER_ID,APP_ID,TOKEN_TYPE,KEY_MANAGER);
    
    PREPARE stmt FROM @sqlst;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET @sqlst = NULL;
    
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD UUID VARCHAR(50);
    UPDATE AM_APPLICATION_KEY_MAPPING SET UUID = UUID() WHERE UUID IS NULL;
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD KEY_MANAGER VARCHAR(50) NOT NULL DEFAULT 'Resident Key Manager';
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD APP_INFO BLOB;
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD CONSTRAINT UNIQUE(APPLICATION_ID,KEY_TYPE,KEY_MANAGER);
    ALTER TABLE AM_APPLICATION_KEY_MAPPING DROP PRIMARY KEY;
    
    ALTER TABLE AM_WORKFLOWS ADD WF_METADATA BLOB NULL DEFAULT NULL;
    ALTER TABLE AM_WORKFLOWS ADD WF_PROPERTIES BLOB NULL DEFAULT NULL;
    
    ALTER TABLE AM_SUBSCRIPTION ADD TIER_ID_PENDING VARCHAR(50);
    
    ALTER TABLE AM_POLICY_SUBSCRIPTION ADD MAX_COMPLEXITY INT(11) NOT NULL DEFAULT 0;
    ALTER TABLE AM_POLICY_SUBSCRIPTION ADD MAX_DEPTH INT(11) NOT NULL DEFAULT 0;
    
    CREATE TABLE IF NOT EXISTS AM_API_RESOURCE_SCOPE_MAPPING (
        SCOPE_NAME VARCHAR(255) NOT NULL,
        URL_MAPPING_ID INTEGER NOT NULL,
        TENANT_ID INTEGER NOT NULL,
        FOREIGN KEY (URL_MAPPING_ID) REFERENCES   AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
        PRIMARY KEY(SCOPE_NAME, URL_MAPPING_ID)
    );
    
    
    CREATE TABLE IF NOT EXISTS AM_SHARED_SCOPE (
         NAME VARCHAR(255),
         UUID VARCHAR (256),
         TENANT_ID INTEGER,
         PRIMARY KEY (UUID)
    );
    
    ALTER TABLE IDN_OAUTH2_RESOURCE_SCOPE DROP PRIMARY KEY;
    
    CREATE TABLE IF NOT EXISTS AM_TENANT_THEMES (
      TENANT_ID INTEGER NOT NULL,
      THEME MEDIUMBLOB NOT NULL,
      PRIMARY KEY (TENANT_ID)
    ) ENGINE=InnoDB;
    
    CREATE TABLE IF NOT EXISTS AM_GRAPHQL_COMPLEXITY (
        UUID VARCHAR(256),
        API_ID INTEGER NOT NULL,
        TYPE VARCHAR(256),
        FIELD VARCHAR(256),
        COMPLEXITY_VALUE INTEGER,
        FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE CASCADE,
        PRIMARY KEY(UUID),
        UNIQUE (API_ID,TYPE,FIELD)
    )ENGINE INNODB;
    
    UPDATE IDN_OAUTH_CONSUMER_APPS SET CALLBACK_URL="" WHERE CALLBACK_URL IS NULL;
    
    CREATE TABLE IF NOT EXISTS AM_SCOPE (
        SCOPE_ID INTEGER NOT NULL AUTO_INCREMENT,
        NAME VARCHAR(255) NOT NULL,
        DISPLAY_NAME VARCHAR(255) NOT NULL,
        DESCRIPTION VARCHAR(512),
        TENANT_ID INTEGER NOT NULL DEFAULT -1,
        SCOPE_TYPE VARCHAR(255) NOT NULL,
        PRIMARY KEY (SCOPE_ID)
    )ENGINE INNODB;
    
    CREATE TABLE IF NOT EXISTS AM_SCOPE_BINDING (
        SCOPE_ID INTEGER NOT NULL,
        SCOPE_BINDING VARCHAR(255) NOT NULL,
        BINDING_TYPE VARCHAR(255) NOT NULL,
        FOREIGN KEY (SCOPE_ID) REFERENCES AM_SCOPE (SCOPE_ID) ON DELETE CASCADE
    )ENGINE INNODB;
    
    DELETE FROM IDN_OAUTH2_SCOPE_BINDING WHERE SCOPE_BINDING IS NULL OR SCOPE_BINDING = '';        
    ALTER TABLE AM_WORKFLOWS ADD (
      WF_METADATA BLOB DEFAULT NULL NULL,
      WF_PROPERTIES BLOB DEFAULT NULL NULL
    )
    /
    
    CREATE TABLE AM_GW_PUBLISHED_API_DETAILS (
      API_ID varchar(255) NOT NULL,
      TENANT_DOMAIN varchar(255),
      API_PROVIDER varchar(255),
      API_NAME varchar(255),
      API_VERSION varchar(255),
      PRIMARY KEY (API_ID)
    )
    /
    
    CREATE TABLE AM_GW_API_ARTIFACTS (
      API_ID varchar(255) NOT NULL,
      ARTIFACT blob,
      GATEWAY_INSTRUCTION varchar(20),
      GATEWAY_LABEL varchar(255),
      TIME_STAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (GATEWAY_LABEL, API_ID),
      FOREIGN KEY (API_ID) REFERENCES AM_GW_PUBLISHED_API_DETAILS(API_ID) ON DELETE CASCADE
    )
    /
    
    CREATE OR REPLACE TRIGGER update_timestamp
        BEFORE INSERT OR UPDATE ON AM_GW_API_ARTIFACTS
        FOR EACH ROW
    BEGIN
        :NEW.TIME_STAMP := systimestamp;
    END;
    /
    
    ALTER TABLE AM_SUBSCRIPTION ADD TIER_ID_PENDING VARCHAR2(50)
    /
    
    ALTER TABLE AM_POLICY_SUBSCRIPTION ADD (
        MAX_COMPLEXITY INTEGER DEFAULT 0 NOT NULL,
        MAX_DEPTH INTEGER DEFAULT 0 NOT NULL
    )
    /
    
    CREATE TABLE AM_API_RESOURCE_SCOPE_MAPPING (
        SCOPE_NAME VARCHAR(255) NOT NULL,
        URL_MAPPING_ID INTEGER NOT NULL,
        TENANT_ID INTEGER NOT NULL,
        FOREIGN KEY (URL_MAPPING_ID) REFERENCES AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
        PRIMARY KEY(SCOPE_NAME, URL_MAPPING_ID)
    )
    /
    
    CREATE TABLE AM_SHARED_SCOPE (
         NAME VARCHAR(255),
         UUID VARCHAR (256),
         TENANT_ID INTEGER,
         PRIMARY KEY (UUID)
    )
    /
    
    ALTER TABLE IDN_OAUTH2_RESOURCE_SCOPE DROP PRIMARY KEY
    /
    
    CREATE TABLE AM_KEY_MANAGER (
      UUID VARCHAR(50) NOT NULL,
      NAME VARCHAR(100) NULL,
      DISPLAY_NAME VARCHAR(100) NULL,
      DESCRIPTION VARCHAR(256) NULL,
      TYPE VARCHAR(45) NULL,
      CONFIGURATION BLOB NULL,
      ENABLED CHAR(1) DEFAULT 1,
      TENANT_DOMAIN VARCHAR(100) NULL,
      PRIMARY KEY (UUID),
      UNIQUE (NAME,TENANT_DOMAIN)
    )
    /
    
    CREATE TABLE AM_TENANT_THEMES (
      TENANT_ID INTEGER NOT NULL,
      THEME BLOB NOT NULL,
      PRIMARY KEY (TENANT_ID)
    )
    /
    
    CREATE TABLE AM_GRAPHQL_COMPLEXITY (
        UUID VARCHAR(256),
        API_ID INTEGER NOT NULL,
        TYPE VARCHAR(256),
        FIELD VARCHAR(256),
        COMPLEXITY_VALUE INTEGER,
        FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
        PRIMARY KEY(UUID),
        UNIQUE (API_ID,TYPE,FIELD)
    )
    /
    
    UPDATE IDN_OAUTH_CONSUMER_APPS SET CALLBACK_URL='' WHERE CALLBACK_URL IS NULL
    /
    
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD UUID VARCHAR(50)
    /
    UPDATE AM_APPLICATION_KEY_MAPPING SET UUID = SYS_GUID() WHERE UUID IS NULL
    /
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD KEY_MANAGER VARCHAR(50) DEFAULT 'Resident Key Manager' NOT NULL
    /
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD APP_INFO BLOB
    /
    ALTER TABLE AM_APPLICATION_KEY_MAPPING ADD UNIQUE(APPLICATION_ID,KEY_TYPE,KEY_MANAGER)
    /
    ALTER TABLE AM_APPLICATION_KEY_MAPPING DROP PRIMARY KEY
    /
    ALTER TABLE AM_APPLICATION_REGISTRATION ADD KEY_MANAGER VARCHAR2(255) DEFAULT 'Resident Key Manager' NOT NULL
    /
    ALTER TABLE AM_APPLICATION_REGISTRATION ADD UNIQUE(SUBSCRIBER_ID,APP_ID,TOKEN_TYPE,KEY_MANAGER)
    /
    CREATE TABLE AM_SCOPE (
        SCOPE_ID INTEGER NOT NULL,
        NAME VARCHAR2(255) NOT NULL,
        DISPLAY_NAME VARCHAR2(255) NOT NULL,
        DESCRIPTION VARCHAR2(512),
        TENANT_ID INTEGER DEFAULT -1 NOT NULL,
        SCOPE_TYPE VARCHAR2(255) NOT NULL,
        PRIMARY KEY (SCOPE_ID))
    /
    CREATE SEQUENCE AM_SCOPE_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
    /
    CREATE OR REPLACE TRIGGER AM_SCOPE_TRIGGER
        BEFORE INSERT
        ON AM_SCOPE
        REFERENCING NEW AS NEW
        FOR EACH ROW
        BEGIN
            SELECT AM_SCOPE_SEQUENCE.nextval INTO :NEW.SCOPE_ID FROM dual;
        END;
    /
    CREATE TABLE AM_SCOPE_BINDING (
        SCOPE_ID INTEGER NOT NULL,
        SCOPE_BINDING VARCHAR2(255) NOT NULL,
        BINDING_TYPE VARCHAR2(255) NOT NULL,
        FOREIGN KEY (SCOPE_ID) REFERENCES AM_SCOPE(SCOPE_ID) ON DELETE CASCADE)
    /
    DELETE FROM IDN_OAUTH2_SCOPE_BINDING WHERE SCOPE_BINDING IS NULL
    /        
    DROP TABLE IF EXISTS AM_KEY_MANAGER;
    CREATE TABLE  IF NOT EXISTS AM_KEY_MANAGER (
      UUID VARCHAR(50) NOT NULL,
      NAME VARCHAR(100) NULL,
      DISPLAY_NAME VARCHAR(100) NULL,
      DESCRIPTION VARCHAR(256) NULL,
      TYPE VARCHAR(45) NULL,
      CONFIGURATION BYTEA NULL,
      ENABLED BOOLEAN DEFAULT '1',
      TENANT_DOMAIN VARCHAR(100) NULL,
      PRIMARY KEY (UUID),
      UNIQUE (NAME,TENANT_DOMAIN)
    );
    
    DROP TABLE IF EXISTS AM_GW_PUBLISHED_API_DETAILS;
    CREATE TABLE IF NOT EXISTS AM_GW_PUBLISHED_API_DETAILS (
      API_ID varchar(255) NOT NULL,
      TENANT_DOMAIN varchar(255),
      API_PROVIDER varchar(255),
      API_NAME varchar(255),
      API_VERSION varchar(255),
      PRIMARY KEY (API_ID)
    );
    
    DROP TABLE IF EXISTS AM_GW_API_ARTIFACTS;
    CREATE TABLE IF NOT EXISTS AM_GW_API_ARTIFACTS (
      API_ID varchar(255) NOT NULL,
      ARTIFACT BYTEA,
      GATEWAY_INSTRUCTION varchar(20),
      GATEWAY_LABEL varchar(255),
      TIME_STAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (GATEWAY_LABEL, API_ID),
      FOREIGN KEY (API_ID) REFERENCES AM_GW_PUBLISHED_API_DETAILS(API_ID) ON UPDATE CASCADE ON DELETE NO ACTION
    );
    
    CREATE OR REPLACE FUNCTION update_modified_column()
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.TIME_STAMP= now();
        RETURN NEW;
    END;
    $$ language 'plpgsql';
    
    CREATE TRIGGER TIME_STAMP AFTER UPDATE ON AM_GW_API_ARTIFACTS FOR EACH ROW EXECUTE PROCEDURE  update_modified_column();
    
    DO $$ DECLARE con_name varchar(200);
    BEGIN
    SELECT 'ALTER TABLE AM_APPLICATION_REGISTRATION DROP CONSTRAINT ' || tc .constraint_name || ';' INTO con_name
    FROM information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
    WHERE constraint_type = 'UNIQUE' AND tc.table_name = 'am_application_registration' AND kcu.column_name = 'token_type';
    
    EXECUTE con_name;
    END $$;
    
    ALTER TABLE AM_APPLICATION_REGISTRATION
        ADD KEY_MANAGER VARCHAR(255) DEFAULT 'Resident Key Manager',
        ADD UNIQUE (SUBSCRIBER_ID,APP_ID,TOKEN_TYPE,KEY_MANAGER);
    
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
    ALTER TABLE AM_APPLICATION_KEY_MAPPING
        ADD UUID VARCHAR(50) NOT NULL DEFAULT uuid_generate_v1(),
        ADD KEY_MANAGER VARCHAR(50) NOT NULL DEFAULT 'Resident Key Manager',
        ADD APP_INFO BYTEA NULL,
        ADD CONSTRAINT application_key_unique UNIQUE(APPLICATION_ID,KEY_TYPE,KEY_MANAGER);
    
    DO $$ DECLARE con_name varchar(200);
    BEGIN
    SELECT 'ALTER TABLE AM_APPLICATION_KEY_MAPPING DROP CONSTRAINT ' || tc .constraint_name || ';' INTO con_name
    FROM information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
    WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name = 'am_application_key_mapping';
    EXECUTE con_name;
    END $$;
    
    ALTER TABLE AM_WORKFLOWS
        ADD WF_METADATA BYTEA NULL,
        ADD WF_PROPERTIES BYTEA NULL;
    
    ALTER TABLE AM_SUBSCRIPTION ADD TIER_ID_PENDING VARCHAR(50);
    
    ALTER TABLE AM_POLICY_SUBSCRIPTION
        ADD MAX_COMPLEXITY INTEGER NOT NULL DEFAULT 0,
        ADD MAX_DEPTH INTEGER NOT NULL DEFAULT 0;
    
    CREATE TABLE IF NOT EXISTS AM_API_RESOURCE_SCOPE_MAPPING (
        SCOPE_NAME VARCHAR(255) NOT NULL,
        URL_MAPPING_ID INTEGER NOT NULL,
        TENANT_ID INTEGER NOT NULL,
        FOREIGN KEY (URL_MAPPING_ID) REFERENCES   AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
        PRIMARY KEY(SCOPE_NAME, URL_MAPPING_ID)
    );
    
    CREATE TABLE IF NOT EXISTS AM_SHARED_SCOPE (
         NAME VARCHAR(255),
         UUID VARCHAR (256),
         TENANT_ID INTEGER,
         PRIMARY KEY (UUID)
    );
    
    DO $$ DECLARE con_name varchar(200);
    BEGIN SELECT 'ALTER TABLE IDN_OAUTH2_RESOURCE_SCOPE DROP CONSTRAINT ' || tc .constraint_name || ';' INTO con_name
    FROM information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
    WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name = 'idn_oauth2_resource_scope';
    EXECUTE con_name;
    END $$;
    
    DROP TABLE IF EXISTS AM_TENANT_THEMES;
    CREATE TABLE IF NOT EXISTS AM_TENANT_THEMES (
      TENANT_ID INTEGER NOT NULL,
      THEME BYTEA NOT NULL,
      PRIMARY KEY (TENANT_ID)
    );
    
    CREATE TABLE IF NOT EXISTS AM_GRAPHQL_COMPLEXITY (
        UUID VARCHAR(256),
        API_ID INTEGER NOT NULL,
        TYPE VARCHAR(256),
        FIELD VARCHAR(256),
        COMPLEXITY_VALUE INTEGER,
        FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE CASCADE,
        PRIMARY KEY(UUID),
        UNIQUE (API_ID,TYPE,FIELD)
    );
    
    UPDATE IDN_OAUTH_CONSUMER_APPS SET CALLBACK_URL='' WHERE CALLBACK_URL IS NULL;
    
    DROP TABLE IF EXISTS AM_SCOPE;
    DROP SEQUENCE IF EXISTS AM_SCOPE_PK_SEQ;
    CREATE SEQUENCE AM_SCOPE_PK_SEQ;
    CREATE TABLE IF NOT EXISTS AM_SCOPE (
                SCOPE_ID INTEGER DEFAULT NEXTVAL('AM_SCOPE_PK_SEQ'),
                NAME VARCHAR(255) NOT NULL,
                DISPLAY_NAME VARCHAR(255) NOT NULL,
                DESCRIPTION VARCHAR(512),
                TENANT_ID INTEGER NOT NULL DEFAULT -1,
                SCOPE_TYPE VARCHAR(255) NOT NULL,
                PRIMARY KEY (SCOPE_ID)
    );
    
    DROP TABLE IF EXISTS AM_SCOPE_BINDING;
    CREATE TABLE IF NOT EXISTS AM_SCOPE_BINDING (
                SCOPE_ID INTEGER NOT NULL,
                SCOPE_BINDING VARCHAR(255) NOT NULL,
                BINDING_TYPE VARCHAR(255) NOT NULL,
                FOREIGN KEY (SCOPE_ID) REFERENCES AM_SCOPE(SCOPE_ID) ON DELETE CASCADE
    );   
    
    DELETE FROM IDN_OAUTH2_SCOPE_BINDING WHERE SCOPE_BINDING IS NULL OR SCOPE_BINDING = '';
  4. Copy the keystores (i.e., client-truststore.jks, wso2cabon.jks and any other custom JKS) used in the previous version and replace the existing keystores in the <API-M_3.2.0_HOME>/repository/resources/security directory.

    If you have enabled Secure Vault

    If you have enabled secure vault in the previous API-M version, you need to add the property values again according to the new config modal and run the script as below. Please refer Encrypting Passwords in Configuration files for more details.

    ./ciphertool.sh -Dconfigure
    ./ciphertool.bat -Dconfigure
  5. Migrate the API Manager artifacts.

    You have to run the following migration client to update the registry artifacts.

    1. Download and extract the migration-resources.zip. Copy the extracted migration-resources to the <API-M_3.2.0_HOME> folder.

    2. Download and copy the API Manager Migration Client to the <API-M_3.2.0_HOME>/repository/components/dropins folder.

    3. Start the API-M server as follows.

      sh wso2server.sh -DmigrateFromVersion=3.1.0
      wso2server.bat -DmigrateFromVersion=3.1.0
    4. Shutdown the API-M server.

    5. Remove the org.wso2.carbon.apimgt.migrate.client-3.2.0-1.jar file, which is in the <API-M_3.2.0_HOME>/repository/components/dropins directory.

    6. Remove the migration-resources directory, which is in the <API-M_3.2.0_HOME> directory.

  6. Re-index the artifacts in the registry.

    1. Run the reg-index.sql script against the SHARED_DB database.

      Note

      Please note that depending on the number of records in the REG_LOG table, this script will take a considerable amount of time to finish. Do not stop the execution of script until it is completed.

    2. Add the tenantloader-1.0.jar to <API-M_3.2.0_HOME>/repository/components/dropins directory.

      Attention

      If you are working with a clustered/distributed API Manager setup, follow this step on the Store and Publisher nodes.

      Note

      You need to do this step, if you have multiple tenants only.

    3. Add the following configuration in to <API-M_3.2.0_HOME>/repository/conf/deployment.toml file.

      [indexing]
      re_indexing = 1

      Info

      If you use a clustered/distributed API Manager setup, do the above change in deployment.toml of Publisher and Devportal nodes

    4. If the <API-M_3.2.0_HOME>/solr directory exists, take a backup and thereafter delete it.

    5. Start the WSO2 API-M server.

    6. Stop the WSO2 API-M server and remove the tenantloader-1.0.jar from the <API-M_3.2.0_HOME>/repository/components/dropins directory.

Step 3 - Optionally, migrate the configurations for WSO2 API-M Analytics

Warning

This step is only required if you have WSO2 API-M-Analytics configured in your current deployment.

Follow the steps below to migrate APIM Analytics 3.1.0 to APIM Analytics 3.2.0

Step 3.1 - Migrating the Analytics Database

The schema for table APIMALLALERT is changed in analytics version 3.2. So it is recommended to drop the above table prior to the migration so that it will be recreated at the server startup using the new script. If you think that you require the already available data in the above table you can take a backup of it. But the above table is just used to maintain a summary of all types of alerts in a single place. As these alerts are persisted individually as well in tables specific for the type of the alert, you will not loose any data related to alerts by dropping this table.

DB Scripts

DROP TABLE APIMALLALERT;

The GRAPHQL operations stored in analytics database are persisted in a sorted way from 3.2 onwards due to a performance improvement. If you use GRAPHQL APIs please follow the instruction below to sort the already existing data on above columns

  1. Configure the datasource used for WSO2 API Manager Analytics in the <API-M_3.2.0_HOME>/repository/conf/deployment.toml file of WSO2 API Manager.

    The following in an example of how the configurations should be defined when using MySQL.

    • The datasource points to the analytics datasource configured in the version 3.1

      [[datasource]]
      id = "APIM_ANALYTICS_DB"
      type = "mysql"
      url = "jdbc:mysql://localhost:3306/analytics_db"
      driver = "com.mysql.jdbc.Driver"
      validationQuery = "SELECT 1"
      username = "username"
      password = "password"
      pool_options.defaultAutoCommit = true

    Note

    When performing Analytics migration in WSO2 API-M Analytics 3.2.0, you need to set the defaultAutoCommit value to true in the APIM_ANALYTICS_DB

    If you are using another DB type

    If you are using another DB type other than H2 or MySQL or Oracle, when defining the DB related configurations in the deployment.toml file for API Manager database, you need to add the driver and validationQuery parameters optionally. For example MSSQL database configuration is as follows for the API Manager database.

    [database.apim_db]
    type = "mssql"
    url = "jdbc:sqlserver://localhost:1433;databaseName=mig_am_db;SendStringParametersAsUnicode=false"
    username = "username"
    password = "password"
    driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    validationQuery = "SELECT 1"
  2. Download and copy the org.wso2.carbon.apimgt.migrate.client-3.2.0-1.jar to the <API-M_3.2.0_HOME>/repository/components/dropins folder.

  3. Copy the relevant JDBC driver to the <API-M_3.2.0_HOME>/repository/components/lib folder.

  4. Make sure that WSO2 API-M Analytics is disabled in the <API-M_3.2.0_HOME>/repository/conf/ deployment.toml file.

    [apim.analytics]
    enable = false
  5. After setting the above configurations in place, start up the WSO2 API-M 3.2.0 server with the following command, if you had enabled Geo Location Based Statistics in the old version setup.

    sh wso2server.sh -DmigrateStats=true -DmigrateFromVersion=3.1.0
    wso2server.bat -DmigrateStats=true -DmigrateFromVersion=3.1.0

    Note: If cross tenant API subscriptions exist, the migration will be aborted. To ignore this, Please set the flag ignoreCrossTenantSubscriptions to true as below.

    sh wso2server.sh -DignoreCrossTenantSubscriptions=true -DmigrateFromVersion=3.1.0
    wso2server.bat -DignoreCrossTenantSubscriptions=true -DmigrateFromVersion=3.1.0

Step 3.2 - Configure WSO2 API-M Analytics 3.2.0

Note

  • In API-M 3.0.0, when working with API-M Analytics, only the worker profile has been used by default and dashboard profile is used only when there are custom dashboards.
  • Now with API-M 3.1.0, both the worker and dashboard profiles are being used. The default Store and Publisher dashboards are now being moved to the Analytics dashboard server side and they have been removed from the API-M side.
  • The same set of DBs will be used in the Analytics side and additionally you need to share the WSO2AM_DB with the dashboard server node.

Follow the instructions below to configure WSO2 API Manager Analytics for the WSO2 API-M Analytics migration in order to migrate the statistics related data.

  1. Download WSO2 API Manager Analytics 3.2.0.

  2. Configure the following 2 datasources in the <API-M_ANALYTICS_3.2.0_HOME>/conf/dashboard/deployment.yaml file by pointing to the old WSO2AM_DB and APIM_ANALYTICS_DB.

    #Data source for APIM Analytics
    - name: APIM_ANALYTICS_DB
        description: Datasource used for APIM Analytics
        jndiConfig:
        name: jdbc/APIM_ANALYTICS_DB
        definition:
        type: RDBMS
        configuration:
            jdbcUrl: 'jdbc:mysql://localhost:3306/analytics_db'
            username: root
            password: root
            driverClassName: com.mysql.jdbc.Driver
            maxPoolSize: 50
            idleTimeout: 60000
            connectionTestQuery: SELECT 1
            validationTimeout: 30000
            isAutoCommit: false
    
    #Main datasource used in API Manager
    - name: AM_DB
        description: Main datasource used by API Manager
        jndiConfig:
        name: jdbc/AM_DB
        definition:
        type: RDBMS
        configuration:
            jdbcUrl: "jdbc:mysql://localhost:3306/am_db"
            username: root
            password: root
            driverClassName: com.mysql.jdbc.Driver
            maxPoolSize: 10
            idleTimeout: 60000
            connectionTestQuery: SELECT 1
            validationTimeout: 30000
            isAutoCommit: false
  3. Configure the following datasource in the <API-M_ANALYTICS_3.2.0_HOME>/conf/worker/deployment.yaml file by pointing to the old APIM_ANALYTICS_DB.

    #Data source for APIM Analytics
    - name: APIM_ANALYTICS_DB
      description: "The datasource used for APIM statistics aggregated data."
      jndiConfig:
        name: jdbc/APIM_ANALYTICS_DB
      definition:
        type: RDBMS
        configuration:
          jdbcUrl: 'jdbc:mysql://localhost:3306/analytics_db'
          username: root
          password: root
          driverClassName: com.mysql.jdbc.Driver
          maxPoolSize: 50
          idleTimeout: 60000
          connectionTestQuery: SELECT 1
          validationTimeout: 30000
          isAutoCommit: false
  4. Copy the relevant JDBC driver OSGI bundle to the <APIM_ANALYTICS_3.2.0_HOME>/lib folder.

    To convert the jar files to OSGi bundles, follow the steps given below.

    1. Download the non-OSGi jar for the required third party product, and save it in a preferred directory in your machine.
    2. Go to the <API-M_ANALYTICS_HOME>/bin directory. Run the command given below, to generate the converted file in the <API-M_ANALYTICS_HOME>/lib directory.
    ./jartobundle.sh <PATH_TO_NON-OSGi_JAR> ../lib
  5. Start the Worker and Dashboard profiles as below by navigating to <API-M_ANALYTICS_3.2.0_HOME>/bin location.

    sh worker.sh
    sh dashboard.sh

Note

If you have developed any custom dashboards in API-M 3.0.0 Analytics using Stream Processor, you will be able to use the same in API-M Anaytics 3.1.0 as well. If you require any guidance regarding this, you can contact WSO2 Support.

Step 3.3 - Configure WSO2 API-M 3.2.0 for Analytics

Follow the instructions below to configure WSO2 API Manager for the WSO2 API-M Analytics migration in order to migrate the statistics related data.

  1. Configure the following datasources in the <API-M_3.2.0_HOME>/repository/conf/deployment.toml file.

    The following is an example of how the configurations should be defined when using MySQL.

    This datasource points to the previous API-M version's WSO2AM_DB datasource.

    [database.apim_db]
    type = "mysql"
    url = "jdbc:mysql://localhost:3306/am_db"
    username = "username"
    password = "password"
  2. Enable analytics in WSO2 API-M by setting the following configuration to true in the <API-M_3.2.0_HOME>/repository/conf/deployment.toml file.

    [apim.analytics]
    enable = true

Step 4 - Restart the WSO2 API-M 3.2.0 server

  1. Restart the WSO2 API-M server.

    sh wso2server.sh
    wso2server.bat

    If you have enabled Analytics

    After starting the WSO2 API-M server and the WSO2 API-M Analytics 3.2.0 server from worker and dashboard profiles, the dashboards can be accessed via https://<dashboard-server-host-name>:9643/analytics-dashboard link.

    Warning

    Make sure you have started the API-M server node before accessing the Dashboard profile as the authentication happens via the API-M's authentication admin service.

This concludes the upgrade process.

Tip

The migration client that you use in this guide automatically migrates your tenants, workflows, external user stores, etc. to the upgraded environment. Therefore, there is no need to migrate them manually.

Top