Kintana Script : Update Environment Server Client Base Paths

Purpose

When you copy an environment, the Server and Client base paths on each application code record of the new environment need to be updated en-masse to match the new environment name.

Doing this by through the gui is too timed consuming and error prone.

Tested

With HP PPM (Project and Portfolio Management) Kintana v6.0.

Usage

Paste the following script into Toad or SQL Runner

-- List an environment's app codes and base paths
-- before and after update

select ee.environment_name,
       eea.app_code,
       eea.server_base_path,
       eea.client_base_path
from KENV_ENVIRONMENTS ee,
KENV_ENV_APPS eea
where ee.environment_id = eea.environment_id
  and ee.environment_name = 'copied_env_name'
order by ee.environment_name, eea.app_code

-- Replace server base path and client base path substrings
-- on the applications tab of the copied environment

update  KENV_ENV_APPS eea
set     eea.server_base_path = replace(eea.server_base_path,
                                       'oldenv_serverbasepath_substring',
                                       'newenv_serverbasepath_substring'),
        eea.client_base_path = replace(eea.client_base_path,
                                       'oldenv_clientbasepath_substring',
                                       'newenv_clientbasepath_substring')
where   -- eea.server_base_path like '%foo%' and
      exists (  select  null
                from    KENV_ENVIRONMENTS ee
                where   ee.environment_name = 'copied_env_name'
                and     ee.environment_id = eea.environment_id
             )
commit

Download

KintanaScripts.zip (58 Kb)