CMS Snapshot Analysis - Run Analyzer - Information - AMT Saving debug mode - How to get the IN Tables from the End User

Keeping these information will speedup DATABASE team diagnostic on saving analysis phase in the following cases:

• Duplicated object GUID
• Performance issue during object comparison phase
• Crash during the Save step (comparing object on server, Merging object on server)
• Missing links between objects

Note: After completing the investigation, please ensure that saving debug mode is turned off by running the same script but replace the query's OPTION_VALUE as '0' and store procedure's 2nd parameter as 'OFF'.

1. Note that in order to get the IN Table quickly without having to run the saving step, we have to disable the Saving Step. Run the below script to bypass the Saving step. Once the output provided to support, do not forget to run the second script to enable the Saving step :
   
   

   -- ACTIVATE Saving Mode and Enable the ByPass for Saving Step (CSS)
    
   
   -- enable sav_in table process
   select DBG_SavingSetActivation ('AMT','ON');
   -- bypass the save store procedure: CACHE_PROCESSID and CACHE_FUSHDATA 
   insert into SYS_SITE_OPTIONS (option_name, option_value )
   values ('DATABASE_BYPASS_SAVING', '1');
   
   -- to activate again the normal saving process
   -- insert into SYS_SITE_OPTIONS (option_name, option_value )
   -- values ('DATABASE_BYPASS_SAVING', '0');
   
   CREATE OR REPLACE FUNCTION CACHE_PROCESSID (I_IDSESSION INT4, I_IDUSRPRO INT4) RETURNS int AS
   $body$
   DECLARE
    L_ERRORCODE int;
   BEGIN
    L_ERRORCODE := 0;
   --#@__KB_TIME__1__KB_TIME__@#
   perform cast_log ('start CACHE_PROCESSID '||cast(I_IDSESSION as varchar)||''||cast(I_IDUSRPRO as varchar) );
    
    select AMT_P_INIT (I_IDSESSION, I_IDUSRPRO) into L_ERRORCODE;
    --#@__KB_TIME__2__KB_TIME__@# 
    
    if exists (select 1 from SYS_SITE_OPTIONS
    where OPTION_NAME = 'DATABASE_BYPASS_SAVING'
    and OPTION_VALUE = '1') then
    perform cast_log ('start BYPASS CACHE_PROCESSID '||cast(I_IDSESSION as varchar)||''||cast(I_IDUSRPRO as varchar) );
    
    -- force to 0 to return immediatly in CACHE_FLUSHDATA
    insert into ANAATTR (SESSION_ID, ATTRNAM, INTVAL) values (I_IDSESSION, 'UPDATE', 0);
    select AMT_MAJ_STATUS (I_IDSESSION, 'TRANSLATED') into L_ERRORCODE;
    
    perform cast_log ('end BYPASS CACHE_PROCESSID '||cast(I_IDSESSION as varchar)||''||cast(I_IDUSRPRO as varchar) ); 
    else
    
    /* Process migration of GUID before all */ 
    select AMT_P_MIGRGUID(I_IDSESSION) into L_ERRORCODE; 
    --#@__KB_TIME__2__KB_TIME__@# 
    
    select AMT_P_SPLIT_IN (I_IDSESSION) into L_ERRORCODE;
    --#@__KB_TIME__2__KB_TIME__@# 
    
    select AMT_P_BUILD (I_IDSESSION) into L_ERRORCODE;
    
   --#@__KB_TIME__1__KB_TIME__@#
    end if;
    
    perform cast_log ('end CACHE_PROCESSID '||cast(I_IDSESSION as varchar)||''||cast(I_IDUSRPRO as varchar) );
    return L_ERRORCODE;
   END;
   $body$
   LANGUAGE 'plpgsql';
   
   

2. Run the analysis

3. Once the analysis is finished, and if you do not need anymore to lot the IN Table, you have to deactivate the Saving Mode and to disable the ByPass for Saving step (in CSS). You have to run the below queries :

   -- disable sav_in table process
   select DBG_SavingSetActivation ('AMT','OFF');
   
   -- Stop the by Saving step bypass
   
   CREATE OR REPLACE FUNCTION CACHE_PROCESSID (I_IDSESSION INT4, I_IDUSRPRO INT4) RETURNS int AS
   $body$
   DECLARE
    L_ERRORCODE int;
   BEGIN
    L_ERRORCODE := 0;
   --#@__KB_TIME__1__KB_TIME__@#
   perform cast_log ('start CACHE_PROCESSID '||cast(I_IDSESSION as varchar)||''||cast(I_IDUSRPRO as varchar) );
    select AMT_P_INIT (I_IDSESSION, I_IDUSRPRO) into L_ERRORCODE;
   --#@__KB_TIME__2__KB_TIME__@#
   
    /* Process migration of GUID before all */ 
    select AMT_P_MIGRGUID(I_IDSESSION) into L_ERRORCODE; 
   --#@__KB_TIME__2__KB_TIME__@#
   select AMT_P_SPLIT_IN (I_IDSESSION) into L_ERRORCODE;
   --#@__KB_TIME__2__KB_TIME__@#
   select AMT_P_BUILD (I_IDSESSION) into L_ERRORCODE;
   --#@__KB_TIME__1__KB_TIME__@#
    perform cast_log ('end CACHE_PROCESSID '||cast(I_IDSESSION as varchar)||''||cast(I_IDUSRPRO as varchar) );
    return L_ERRORCODE;
   END;
   $body$
   LANGUAGE 'plpgsql'
   /

Run this script on the KB:

Oracle

insert into SYS_SITE_OPTIONS (OPTION_NAME,OPTION_VALUE) values ('DATABASE_SAVING_DEBUG','1')
/
commit
/
begin
DBG_SavingSetActivation ('AMT','ON');
End;
/
commit
/

MS SQL Server

insert into SYS_SITE_OPTIONS (OPTION_NAME,OPTION_VALUE) values ('DATABASE_SAVING_DEBUG','1');
GO
EXEC DBG_SavingSetActivation 'AMT', 'ON';
GO

(2) Run the analysis as usual (or KB Update assistant), the debug data will be kept in KB

To make sure that the IN tables saving phase has succeeded, run the following SQL query on the KB:

select * from DBG_SavingUnit

If the query returns one row at least, then the saving phase has succeeded.

Clean up of IN tables in Cast Storage Service

If you want to perform a clean up of IN tables ,please use the following query:

select dbg_purgesavingunit('AMT')

This query will drop all AMT saved "sav_in_<table>".

Note:These tables should be dropped only when a backup has been taken.