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

摘要

定期运行“压缩”是管理和维护健康的版本化地理数据库的必要操作。 该操作可移除某版本不再引用的状态，还可将增量表中的记录移动到对应的业务表中。 数据的编辑频率会影响“压缩”操作完成的持续时间。 在 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 - 压缩操作已成功完成