How to Drop and Recreate TIBCO Data Virtualization Repository with existing metadata?

How to Drop and Recreate TIBCO Data Virtualization Repository with existing metadata?

book

Article ID: KB0070830

calendar_today

Updated On:

Products

TIBCO Data Virtualization

Description

This article outlines the steps to perform to drop and Recreate TIBCO Data Virtualization PostgreSQL repository with existing metadata

Few scenarios where you may want to drop and recreate the TDV repository are as below:
1. One or more repository tables are "bloated" to an unreasonable size, and manually executing the command "VACUUM FULL ANALYZE VERBOSE" does not shrink the tables. This issue was resolved in 7.0.8.00.03. 
2. Newly created resource_ids are negative because the repository ids are greater than around 2 billion (the maximum size of a Java integer). This was resolved in 7.0.7.

Issue/Introduction

This article outlines the steps to perform to drop and Recreate TIBCO Data Virtualization Postgres Repository with existing metadata

Resolution

For both issues, if you can't upgrade immediately, recreating the repository can be a temporary workaround.

Note:
When you do move to a newer version, reinstalling TDV instead of upgrading (i.e. applying a patch) will resolve the issue with no need to recreate the repository.
If you upgrade by applying a patch, you will need to recreate the repository to remove extraneous rows and shrink the tables or "reset" the resource IDs.

Perform the following steps to Drop and recreate the TDV Repository with existing metadata:
1) Take a backup of the existing metadata by keeping a copy of TDV Server install directory. 
        a) If you don't already have recent backup containing all the metadata but you want to save, take a full server backup from Studio. (Administration tab -> Full Server Backup)
        b) Verify the backup is valid
2) If node is part of a cluster, perform the below steps:
        a) Remove from the cluster using Web Manager > Cluster Management
        b) Add it back to the cluster (this will automatically drop the current schema and import the metadata from the timekeeper)
        c) Check the metadata of the node in Studio to verify that it matches the timekeeper
        d) Repeat with every node whose repository needs to be cleaned
3) If the node IS NOT part of a cluster, perform the below steps:
        a) Stop TDV server
        b) Drop the schema using repo_util.bat or repo_util.sh.

  Example for reference:
(i) List files under <TDV_INSTALL>\conf\repository\postgresql beginning with 'composite_clean'.
dir D:\TIBCO_DV\conf\repository\postgresql\composite_clean*
(ii) If the list contains files named composite_clean-nnnnnnnnn.sql ('nnnnnnnnn' represents a version number), run repo_util.bat with the arguments -dropSchema -schemaDropScript <highest versioned file>
For instance if the list is:
        -  composite_clean-080200001.sql
        -  composite_clean-080401001.sql
        -  composite_clean.sql     
Then composite_clean-080401001.sql is the highest versioned file, and the command is:
repo_util.bat -dropSchema -schemaDropScript D:\TIBCO_DV\conf\repository\postgresql\composite_clean-080401001.sql
(iii) If the list does not contain any versioned files (i.e. only composite_clean.sql is present), the schemaDropScript argument is not required and the command is:
repo_util.bat -dropSchema 
  Output:
register basic services
   Connecting to database...
   
   You are about to permanently delete all metadata in the server's
   repository database. All server resources including data sources, views,
   scripts, and definitions will be deleted. This action cannot be undone.
   Do you want to proceed? [y,n]: y
   Dropping schema...
   The Composite server repository schema has been dropped.
 c) Start TDV server.
      cs_server.log should contain below line after startup:
INFO [main] 2019-09-11 12:23:48.364 -0700 NamespaceModule - Server started with an empty database.  Creating the initial system database entries.
 d) Import the full server backup.