Purpose of Query
This page helps you to update the service ID for a schema (Local, Central, MNGT) from the database. This will avoid same Service ID for 2 schemas on the same DB server.
Applicable CAST Version
Release | Yes/No |
---|---|
8.3.x | |
8.2.x | |
8.1.x | |
8.0.x |
Applicable RDBMS
RDBMS | Yes/No |
---|---|
Oracle Server | |
Microsoft SQL Server | |
CSS2 |
Query for CSS
set search_path = <Schema Name>; DO $$ DECLARE rec record; curschema varchar; oldsiteid integer; qry1 varchar; newsiteid INTEGER; BEGIN DROP TABLE IF EXISTS tmp_otherschema_sys_site; CREATE TEMP TABLE tmp_otherschema_sys_site(site_id INTEGER NOT NULL); SELECT current_schema() into curschema; SELECT site_id FROM sys_site into oldsiteid; --RAISE NOTICE 'Old site id %',oldsiteid; FOR rec in SELECT table_schema FROM information_schema.tables WHERE table_name = 'sys_site' and table_schema <> curschema LOOP SELECT format('SELECT site_id FROM %s.sys_site', quote_ident(rec.table_schema)) into qry1; Execute 'INSERT INTO tmp_otherschema_sys_site (site_id) ' || qry1; END LOOP; IF EXISTS(SELECT 1 FROM tmp_otherschema_sys_site WHERE site_id = oldsiteid) THEN SELECT (CASE WHEN MIN(site_id) > 1 then MIN(site_id)-1 ELSE MAX(site_id)+1 END)::INTEGER into newsiteid FROM tmp_otherschema_sys_site; --RAISE NOTICE 'New site id %',newsiteid; UPDATE sys_site SET site_id = newsiteid WHERE site_id = oldsiteid; END IF; DROP TABLE IF EXISTS tmp_otherschema_sys_site; RETURN; END$$;
Query result example
Query result interpretation
The query will check the entire DB to see if there are any duplicated and then update the site_id accordingly.
Ticket reference
AIPCORE-2383