Performance Monitor creates LOG$ tables as temporary

投稿者:: Quinn Wildman

概要: When you disconnect from your database with IBConsole any records saved in LOG$ tables with be deleted.

As of InterBase 7.5, InterBase Performance Monitor (included inside of IBConsole) does its logging to global temporary tables instead of standard tables. This has the undesired result of causing all logging to be lost once the transaction to the database is committed. Logging will be reverted to standard tables in a future version of InterBase Performance Monitor.

To workaround the problem, create the LOG$ tables before you turn on logging as follows:

CREATE TABLE LOG$ATTACHMENTS
(
  ID	INTEGER NOT NULL,
  TIME_STAMP	TIMESTAMP NOT NULL,
  SNAPSHOT_ID	INTEGER NOT NULL,
  TMP$ATTACHMENT_ID	INTEGER,
  TMP$DATABASE_ID	INTEGER,
  TMP$POOL_ID	INTEGER,
  TMP$POOL_MEMORY	INTEGER,
  TMP$STATEMENTS	SMALLINT,
  TMP$TRANSACTIONS	SMALLINT,
  TMP$TIMESTAMP	TIMESTAMP,
  TMP$QUANTUM	INTEGER,
  TMP$USER	CHAR(67) CHARACTER SET UNICODE_FSS,
  TMP$USER_IP_ADDR	CHAR(31),
  TMP$USER_HOST	CHAR(31),
  TMP$USER_PROCESS	INTEGER,
  TMP$STATE	CHAR(31),
  TMP$PRIORITY	CHAR(31),
  TMP$DBKEY_ID	INTEGER,
  TMP$ACTIVE_SORTS	SMALLINT,
  TMP$PAGE_READS	INTEGER,
  TMP$PAGE_WRITES	INTEGER,
  TMP$PAGE_FETCHES	INTEGER,
  TMP$PAGE_MARKS	INTEGER,
  TMP$RECORD_SELECTS	INTEGER,
  TMP$RECORD_INSERTS	INTEGER,
  TMP$RECORD_UPDATES	INTEGER,
  TMP$RECORD_DELETES	INTEGER,
  TMP$RECORD_PURGES	INTEGER,
  TMP$RECORD_EXPUNGES	INTEGER,
  TMP$RECORD_BACKOUTS	INTEGER,
 PRIMARY KEY (ID)
);

CREATE TABLE LOG$DATABASE
(
  ID	INTEGER NOT NULL,
  TIME_STAMP	TIMESTAMP NOT NULL,
  SNAPSHOT_ID	INTEGER NOT NULL,
  TMP$DATABASE_ID	INTEGER,
  TMP$DATABASE_PATH	VARCHAR(253),
  TMP$ATTACHMENTS	SMALLINT,
  TMP$STATEMENTS	SMALLINT,
  TMP$ALLOCATED_PAGES	INTEGER,
  TMP$POOLS	INTEGER,
  TMP$PROCEDURES	SMALLINT,
  TMP$RELATIONS	SMALLINT,
  TMP$TRIGGERS	SMALLINT,
  TMP$ACTIVE_THREADS	SMALLINT,
  TMP$SORT_MEMORY	INTEGER,
  TMP$CURRENT_MEMORY	INTEGER,
  TMP$MAXIMUM_MEMORY	INTEGER,
  TMP$PERMANENT_POOL_MEMORY	INTEGER,
  TMP$CACHE_POOL_MEMORY	INTEGER,
  TMP$TRANSACTIONS	SMALLINT,
  TMP$TRANSACTION_COMMITS	INTEGER,
  TMP$TRANSACTION_ROLLBACKS	INTEGER,
  TMP$TRANSACTION_PREPARES	INTEGER,
  TMP$TRANSACTION_DEADLOCKS	INTEGER,
  TMP$TRANSACTION_CONFLICTS	INTEGER,
  TMP$TRANSACTION_WAITS	INTEGER,
  TMP$NEXT_TRANSACTION	INTEGER,
  TMP$OLDEST_INTERESTING	INTEGER,
  TMP$OLDEST_ACTIVE	INTEGER,
  TMP$OLDEST_SNAPSHOT	INTEGER,
  TMP$CACHE_BUFFERS	INTEGER,
  TMP$CACHE_PRECEDENCE	INTEGER,
  TMP$CACHE_LATCH_WAITS	INTEGER,
  TMP$CACHE_FREE_WAITS	INTEGER,
  TMP$CACHE_FREE_WRITES	INTEGER,
  TMP$SWEEP_INTERVAL	INTEGER,
  TMP$SWEEP_ACTIVE	CHAR(1),
  TMP$SWEEP_RELATION	CHAR(67) CHARACTER SET UNICODE_FSS,
  TMP$SWEEP_RECORDS	INTEGER,
  TMP$PAGE_READS	INTEGER,
  TMP$PAGE_WRITES	INTEGER,
  TMP$PAGE_FETCHES	INTEGER,
  TMP$PAGE_MARKS	INTEGER,
  TMP$RECORD_SELECTS	INTEGER,
  TMP$RECORD_INSERTS	INTEGER,
  TMP$RECORD_UPDATES	INTEGER,
  TMP$RECORD_DELETES	INTEGER,
  TMP$RECORD_PURGES	INTEGER,
  TMP$RECORD_EXPUNGES	INTEGER,
  TMP$RECORD_BACKOUTS	INTEGER,
  TMP$STATE	CHAR(31),
 PRIMARY KEY (ID)
);

CREATE  TABLE LOG$HEAPS
( 
  ID INTEGER NOT NULL,
  TIME_STAMP	TIMESTAMP NOT NULL,
  SNAPSHOT_ID	INTEGER NOT NULL,
  TMP$HEAP_TYPE    CHAR(31),
  TMP$HEX_ADDRESS    CHAR(31),
  TMP$ADDRESS    NUMERIC(15,0),
  TMP$FREE_MEMORY    INTEGER,
 PRIMARY KEY (ID)
);

CREATE TABLE LOG$POOLS
(
  ID	INTEGER NOT NULL,
  TIME_STAMP	TIMESTAMP NOT NULL,
  SNAPSHOT_ID	INTEGER NOT NULL,
  TMP$POOL_ID	INTEGER,
  TMP$TYPE	CHAR(31),
  TMP$POOL_MEMORY	INTEGER,
  TMP$FREE_MEMORY	INTEGER,
  TMP$EXTEND_MEMORY	INTEGER,
  TMP$FREE_STACK_NODES	SMALLINT,
  TMP$FREE_BITMAP_BUCKETS	SMALLINT,
  TMP$FREE_BITMAP_SEGMENTS	INTEGER,
 PRIMARY KEY (ID)
);

CREATE TABLE LOG$PROCEDURES
(
  ID	INTEGER NOT NULL,
  TIME_STAMP	TIMESTAMP NOT NULL,
  SNAPSHOT_ID	INTEGER NOT NULL,
  TMP$PROCEDURE_ID	INTEGER,
  TMP$DATABASE_ID	INTEGER,
  TMP$PROCEDURE_NAME	CHAR(67) CHARACTER SET UNICODE_FSS,
  TMP$POOL_ID	INTEGER,
  TMP$POOL_MEMORY	INTEGER,
  TMP$CLONE	SMALLINT,
  TMP$TIMESTAMP	TIMESTAMP,
  TMP$USE_COUNT	SMALLINT,
  TMP$QUANTUM	INTEGER,
  TMP$INVOCATIONS	INTEGER,
  TMP$PAGE_READS	INTEGER,
  TMP$PAGE_WRITES	INTEGER,
  TMP$PAGE_FETCHES	INTEGER,
  TMP$PAGE_MARKS	INTEGER,
  TMP$RECORD_SELECTS	INTEGER,
  TMP$RECORD_INSERTS	INTEGER,
  TMP$RECORD_UPDATES	INTEGER,
  TMP$RECORD_DELETES	INTEGER,
  TMP$RECORD_PURGES	INTEGER,
  TMP$RECORD_EXPUNGES	INTEGER,
  TMP$RECORD_BACKOUTS	INTEGER,
 PRIMARY KEY (ID)
);

CREATE TABLE LOG$RELATIONS
(
  ID	INTEGER NOT NULL,
  TIME_STAMP	TIMESTAMP NOT NULL,
  SNAPSHOT_ID	INTEGER NOT NULL,
  TMP$RELATION_ID	SMALLINT,
  TMP$DATABASE_ID	INTEGER,
  TMP$RELATION_NAME	CHAR(67) CHARACTER SET UNICODE_FSS,
  TMP$USE_COUNT	SMALLINT,
  TMP$SWEEP_COUNT	SMALLINT,
  TMP$SCAN_COUNT	INTEGER,
  TMP$FORMATS	SMALLINT,
  TMP$POINTER_PAGES	INTEGER,
  TMP$DATA_PAGES	INTEGER,
  TMP$GARBAGE_COLLECT_PAGES	INTEGER,
  TMP$PAGE_READS	INTEGER,
  TMP$PAGE_WRITES	INTEGER,
  TMP$PAGE_FETCHES	INTEGER,
  TMP$PAGE_MARKS	INTEGER,
  TMP$RECORD_IDX_SELECTS	INTEGER,
  TMP$RECORD_SEQ_SELECTS	INTEGER,
  TMP$RECORD_INSERTS	INTEGER,
  TMP$RECORD_UPDATES	INTEGER,
  TMP$RECORD_DELETES	INTEGER,
  TMP$RECORD_PURGES	INTEGER,
  TMP$RECORD_EXPUNGES	INTEGER,
  TMP$RECORD_BACKOUTS	INTEGER,
 PRIMARY KEY (ID)
);

CREATE TABLE LOG$STATEMENTS
(
  ID	INTEGER NOT NULL,
  TIME_STAMP	TIMESTAMP NOT NULL,
  SNAPSHOT_ID	INTEGER NOT NULL,
  TMP$STATEMENT_ID	INTEGER,
  TMP$ATTACHMENT_ID	INTEGER,
  TMP$TRANSACTION_ID	INTEGER,
  TMP$SQL	VARCHAR(4094),
  TMP$POOL_ID	INTEGER,
  TMP$POOL_MEMORY	INTEGER,
  TMP$CLONE	SMALLINT,
  TMP$TIMESTAMP	TIMESTAMP,
  TMP$QUANTUM	INTEGER,
  TMP$INVOCATIONS	INTEGER,
  TMP$STATE	CHAR(31),
  TMP$PRIORITY	CHAR(31),
  TMP$PAGE_READS	INTEGER,
  TMP$PAGE_WRITES	INTEGER,
  TMP$PAGE_FETCHES	INTEGER,
  TMP$PAGE_MARKS	INTEGER,
  TMP$RECORD_SELECTS	INTEGER,
  TMP$RECORD_INSERTS	INTEGER,
  TMP$RECORD_UPDATES	INTEGER,
  TMP$RECORD_DELETES	INTEGER,
  TMP$RECORD_PURGES	INTEGER,
  TMP$RECORD_EXPUNGES	INTEGER,
  TMP$RECORD_BACKOUTS	INTEGER,
 PRIMARY KEY (ID)
);

CREATE TABLE LOG$TRANSACTIONS
(
  ID	INTEGER NOT NULL,
  TIME_STAMP	TIMESTAMP NOT NULL,
  SNAPSHOT_ID	INTEGER NOT NULL,
  TMP$TRANSACTION_ID	INTEGER,
  TMP$ATTACHMENT_ID	INTEGER,
  TMP$POOL_ID	INTEGER,
  TMP$POOL_MEMORY	INTEGER,
  TMP$TIMESTAMP	TIMESTAMP,
  TMP$SNAPSHOT	INTEGER,
  TMP$QUANTUM	INTEGER,
  TMP$SAVEPOINTS	INTEGER,
  TMP$READONLY	CHAR(1),
  TMP$WRITE	CHAR(1),
  TMP$NOWAIT	CHAR(1),
  TMP$COMMIT_RETAINING	CHAR(1),
  TMP$STATE	CHAR(31),
  TMP$PRIORITY	CHAR(31),
  TMP$TYPE	CHAR(31),
  TMP$PAGE_READS	INTEGER,
  TMP$PAGE_WRITES	INTEGER,
  TMP$PAGE_FETCHES	INTEGER,
  TMP$PAGE_MARKS	INTEGER,
  TMP$RECORD_SELECTS	INTEGER,
  TMP$RECORD_INSERTS	INTEGER,
  TMP$RECORD_UPDATES	INTEGER,
  TMP$RECORD_DELETES	INTEGER,
  TMP$RECORD_PURGES	INTEGER,
  TMP$RECORD_EXPUNGES	INTEGER,
  TMP$RECORD_BACKOUTS	INTEGER,
 PRIMARY KEY (ID)
);

CREATE TABLE LOG$TRIGGERS
(
  ID	INTEGER NOT NULL,
  TIME_STAMP	TIMESTAMP NOT NULL,
  SNAPSHOT_ID	INTEGER NOT NULL,
  TMP$TRIGGER_ID	INTEGER,
  TMP$DATABASE_ID	INTEGER,
  TMP$RELATION_NAME	CHAR(67) CHARACTER SET UNICODE_FSS,
  TMP$TRIGGER_NAME	CHAR(67) CHARACTER SET UNICODE_FSS,
  TMP$TRIGGER_TYPE	SMALLINT,
  TMP$TRIGGER_SEQUENCE	SMALLINT,
  TMP$TRIGGER_ORDER	CHAR(31),
  TMP$TRIGGER_OPERATION	CHAR(31),
  TMP$POOL_ID	INTEGER,
  TMP$POOL_MEMORY	INTEGER,
  TMP$CLONE	SMALLINT,
  TMP$TIMESTAMP	TIMESTAMP,
  TMP$QUANTUM	INTEGER,
  TMP$INVOCATIONS	INTEGER,
  TMP$PAGE_READS	INTEGER,
  TMP$PAGE_WRITES	INTEGER,
  TMP$PAGE_FETCHES	INTEGER,
  TMP$PAGE_MARKS	INTEGER,
  TMP$RECORD_SELECTS	INTEGER,
  TMP$RECORD_INSERTS	INTEGER,
  TMP$RECORD_UPDATES	INTEGER,
  TMP$RECORD_DELETES	INTEGER,
  TMP$RECORD_PURGES	INTEGER,
  TMP$RECORD_EXPUNGES	INTEGER,
  TMP$RECORD_BACKOUTS	INTEGER,
 PRIMARY KEY (ID)
);


次からのサーバー応答:: ETNASC01