中文

操作方法:检查压缩操作的状态

摘要

定期运行“压缩”是管理和维护健康的版本化地理数据库的必要操作。 该操作可移除某版本不再引用的状态,还可将增量表中的记录移动到对应的业务表中。 数据的编辑频率会影响“压缩”操作完成的持续时间。 在 10.3 和 10.2.1 实用程序以及 Telco Patch 1 中,新引入了一些压缩日志记录以对整个过程加以解释。

过程

要获取此新的压缩日志记录信息,请执行以下操作:

  1. 如果 COMPRESS_LOG 表不存在,请创建该表。 第一次执行“压缩”操作时,该表将自动创建。
  2. 创建一个压缩审核表,用于在 COMPRESS_LOG 表中的 COMPRESS_STATUS 列更改时插入记录。
  3. 在 COMPRESS_LOG 表上创建触发器以捕获对 COMPRESS_STATUS 列的更改,并将其写入压缩审核表。
    • 对于 Oracle 地理数据库:
      CREATE TABLE SDE.COMPRESS_LOG
         (    "SDE_ID" NUMBER(*,0) NOT NULL ENABLE,
              "SERVER_ID" NUMBER(*,0) NOT NULL ENABLE,
              "DIRECT_CONNECT" VARCHAR2(1) NOT NULL ENABLE,
              "COMPRESS_START" DATE NOT NULL ENABLE,
              "START_STATE_COUNT" NUMBER(*,0) NOT NULL ENABLE,
              "COMPRESS_END" DATE,
              "END_STATE_COUNT" NUMBER(*,0),
              "COMPRESS_STATUS" VARCHAR2(20)
         )
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
        STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE 
        DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE SDE;
      
      CREATE TABLE SDE.COMPRESS_LOG_TRACKER_TAB
      (SDE_ID NUMBER(*,0),
       SERVER_ID NUMBER(*,0),  
       LOG_DATE DATE,
       COMPRESS_END DATE,
       COMPRESS_STATUS VARCHAR2(20),
       START_STATE_COUNT NUMBER(*,0),
       END_STATE_COUNT NUMBER(*,0))
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
        STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE 
        DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE SDE;
      
      
      CREATE OR REPLACE TRIGGER SDE.COMPRESS_LOG_TRACKER_TRIG
      AFTER INSERT OR UPDATE 
         OF COMPRESS_STATUS
         ON SDE.COMPRESS_LOG
         REFERENCING NEW AS new
         FOR EACH ROW
      DECLARE
        BEGIN
         INSERT INTO SDE.COMPRESS_LOG_TRACKER_TAB (SDE_ID, SERVER_ID, LOG_DATE, COMPRESS_END, COMPRESS_STATUS, START_STATE_COUNT, END_STATE_COUNT)
         VALUES (:new.SDE_ID, :new.SERVER_ID, sysdate, :new.COMPRESS_END, :new.COMPRESS_STATUS, :new.START_STATE_COUNT, :new.END_STATE_COUNT);
        EXCEPTION
          WHEN OTHERS THEN
              RAISE;
      END;
      /
      
      查询压缩追踪表:
      
      SQL> select * from sde.compress_log_tracker_tab;
      
          SDE_ID  SERVER_ID LOG_DATE                 COMPRESS_END             COMPRESS_STATUS      START_STATE_COUNT END_STATE_COUNT
      ---------- ---------- ------------------------ ------------------------ -------------------- ----------------- ---------------
              51       5896 Tue 07-Jul-2015 10:55:16                          IN PROGRESS                         15
              51       5896 Tue 07-Jul-2015 10:55:16                          DELETE LEAF STATES                  15              15
              51       5896 Tue 07-Jul-2015 10:55:17                          TRIMMING                            15              13
              51       5896 Tue 07-Jul-2015 10:55:17                          MOVE TO BASE                        15              13
              51       5896 Tue 07-Jul-2015 10:55:17 Tue 07-Jul-2015 10:55:17 SUCCESS                             15               1
      
      5 rows selected.
    • 对于 SQL Server 地理数据库:
      CREATE TABLE [sde].[SDE_compress_log](
      	[compress_id] [int] IDENTITY(1,1) NOT NULL,
      	[sde_id] [int] NOT NULL,
      	[server_id] [int] NOT NULL,
      	[direct_connect] [varchar](1) NOT NULL,
      	[compress_start] [datetime] NOT NULL,
      	[start_state_count] [int] NOT NULL,
      	[compress_end] [datetime] NULL,
      	[end_state_count] [int] NULL,
      	[compress_status] [varchar](20) NULL,
       CONSTRAINT [compress_log_pk] PRIMARY KEY CLUSTERED 
      (
      	[compress_id] ASC
      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
      ) ON [PRIMARY]
      
      GO
      
      
      CREATE TABLE [sde].[SDE_COMPRESS_LOG_TRACKER_TAB]
      (
         [SDE_ID] int  NULL,
         [SERVER_ID] int  NULL,
         [LOG_DATE] datetime  NULL,
         [COMPRESS_END] [datetime],
         [COMPRESS_STATUS] varchar(20),
         [START_STATE_COUNT] [int],
         [END_STATE_COUNT] [int]
      )
      GO
      
      
      CREATE TRIGGER sde.SDE_COMPRESS_LOG_TRACKER_TRIG
         ON sde.SDE_COMPRESS_LOG
          AFTER INSERT, UPDATE
            AS 
               BEGIN
                  SET  NOCOUNT  ON
                  /* column variables declaration*/
                  DECLARE
                     @new$SDE_ID int, 
                     @new$SERVER_ID int, 
                     @new$LOG_DATE datetime,
                     @new$COMPRESS_END datetime,
                     @new$COMPRESS_STATUS varchar(20),
                     @new$START_STATE_COUNT int,
                     @new$END_STATE_COUNT int
                  DECLARE
                      ForEachInsertedRowTriggerCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
                        SELECT SDE_ID, SERVER_ID, sysdatetime(), COMPRESS_END, COMPRESS_STATUS, START_STATE_COUNT, END_STATE_COUNT
                        FROM inserted
                  OPEN ForEachInsertedRowTriggerCursor
                  FETCH ForEachInsertedRowTriggerCursor
                      INTO @new$SDE_ID, @new$SERVER_ID, @new$LOG_DATE, @new$COMPRESS_END, @new$COMPRESS_STATUS, @new$START_STATE_COUNT, @new$END_STATE_COUNT
                  WHILE @@fetch_status = 0           
                     BEGIN
                        /* trigger implementation: begin*/
                        BEGIN
                           IF (UPDATE(COMPRESS_STATUS))
                              BEGIN
      
                                  INSERT sde.SDE_COMPRESS_LOG_TRACKER_TAB(SDE_ID, SERVER_ID, LOG_DATE, COMPRESS_END, COMPRESS_STATUS, START_STATE_COUNT, END_STATE_COUNT)
                                  VALUES (@new$SDE_ID, @new$SERVER_ID, sysdatetime(), @new$COMPRESS_END, @new$COMPRESS_STATUS, @new$START_STATE_COUNT, @new$END_STATE_COUNT)
                              END
                        END
                        /* trigger implementation: end*/
                        FETCH ForEachInsertedRowTriggerCursor
                            INTO @new$SDE_ID, @new$SERVER_ID, @new$LOG_DATE, @new$COMPRESS_END, @new$COMPRESS_STATUS, @new$START_STATE_COUNT, @new$END_STATE_COUNT
                     END
                  CLOSE ForEachInsertedRowTriggerCursor
                  DEALLOCATE ForEachInsertedRowTriggerCursor
               END
      GO
      
      
      查询压缩追踪表:
      
      select * from sde.SDE_COMPRESS_LOG_TRACKER_TAB
      go
      
      SDE_ID      SERVER_ID   LOG_DATE                COMPRESS_END            COMPRESS_STATUS      START_STATE_COUNT END_STATE_COUNT
      
      ----------- ----------- ----------------------- ----------------------- -------------------- ----------------- ---------------
             3035        9016 2015-07-02 12:36:07.690                    NULL IN PROGRESS                       8154            NULL
             3035        9016 2015-07-02 12:36:07.720                    NULL DELETE LEAF STATES                8154            8154
             3035        9016 2015-07-02 12:52:03.113                    NULL TRIMMING                          8154            6308
             3035        9016 2015-07-02 12:53:44.770                   NULL MOVE TO BASE                      8154            6306
             3035        9016 2015-07-02 12:56:12.637 2015-07-02 12:56:12.623 SUCCESS                           8154            6094
      
      (5 rows affected)
      :
      如果 SQL Server 企业级地理数据库由 DBO 用户(而不是 SDE)维护,请更改表的所有者,并从触发 SDE 改为触发 DBO。
    • 每个 COMPRESS_STATUS 操作的说明:
      • IN PROGRESS - 压缩操作已经开始
      • DELETE LEAF STATES - 删除未包含在版本谱系内的所有状态
      • TRIMMING - 将状态的所有候选谱系折叠成一种状态
      • MOVE TO BASE - 将行从增量表移动到业务表(如果不被其他版本锁定)
      • SUCCESS - 压缩操作已成功完成

相关信息