
CREATE DOMAIN SQL_IDENTIFIER AS CHARACTER VARYING (128)
	CHARACTER SET SQL_TEXT;

CREATE DOMAIN CHARACTER_DATA AS CHARACTER VARYING (4096)
	CHARACTER SET SQL_TEXT;

CREATE DOMAIN CARDINAL_NUMBER AS INTEGER
	CONSTRAINT CARDINAL_NUMBER_DOMAIN_CHECK CHECK ( VALUE >= 0 );

CREATE TABLE INFORMATION_SCHEMA_CATALOG_NAME
(
	"CATALOG_NAME"	SQL_IDENTIFIER,

	CONSTRAINT INFORMATION_SCHEMA_CATALOG_NAME_PRIMARY_KEY
		PRIMARY KEY ( "CATALOG_NAME" )
);

INSERT INTO INFORMATION_SCHEMA_CATALOG_NAME VALUES(CURRENT_CATALOG);

CREATE VIEW CATALOGS
   AS SELECT CATALOG_PK, "CATALOG_NAME" FROM DEFINITION_SCHEMA.CATALOGS;

CREATE VIEW USERS
   AS SELECT USER_PK, USER_NAME, PASSWORD, DEFAULT_SCHEMA
	FROM DEFINITION_SCHEMA.USERS;

CREATE VIEW SCHEMATA
   AS SELECT SCHEMA_PK, SCHEMA_OWNER_PK, "SCHEMA_NAME", CATALOG_PK
	FROM DEFINITION_SCHEMA.SCHEMATA;

CREATE VIEW TABLES
   AS SELECT TABLE_PK, SCHEMA_PK, "TABLE_NAME", TABLE_TYPE
	FROM DEFINITION_SCHEMA.TABLES;

CREATE VIEW LS_INDEXES
   AS SELECT LS_INDEXES_PK, CATALOG_PK, SCHEMA_PK, INDEX_NAME,
             TABLE_PK, INDEX_DESCRIPTOR,
             INDEX_ARGS, INDEX_HANDLE,
             VERSION, FLAGS, COLLATION_PK
       FROM DEFINITION_SCHEMA.LS_INDEXES;

CREATE VIEW DATA_TYPE_DESCRIPTOR
   AS SELECT TABLE_OR_DOMAIN_PK, COLUMN_NAME_PK, DATA_TYPE, DATA_TYPE_CODE,
             CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH,
             CHARACTER_SET_PK, COLLATION_PK, NUMERIC_PRECISION,
             NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION,
             INTERVAL_FROM, INTERVAL_TO, DATETIME_PRECISION_LEADING,
             DATA_TYPE_DESCRIPTOR_PK
	FROM DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR;
             
CREATE VIEW DOMAINS
   AS SELECT DOMAIN_PK, SCHEMA_PK, "DOMAIN_NAME", IS_NULLABLE
	FROM DEFINITION_SCHEMA.DOMAINS;

CREATE VIEW VIEWS
   AS SELECT VIEW_PK, TABLE_PK, CHECK_OPTION, IS_UPDATABLE
	FROM DEFINITION_SCHEMA.VIEWS;

CREATE VIEW INDEXES
   AS SELECT INDEXES_PK, CATALOG_PK, SCHEMA_PK, INDEX_NAME, TABLE_PK,
             INDEX_COLUMN_COUNT, INDEX_COLUMNS, CONSTRAINT_NAME_PK, INDEX_TYPE
         FROM DEFINITION_SCHEMA.INDEXES;

CREATE VIEW INDEX_COLUMN_USAGE
   AS SELECT INDEX_COLUMN_USAGE_PK, INDEX_PK, TABLE_PK, COLUMN_PK, ORDINAL_POSITION
         FROM DEFINITION_SCHEMA.INDEX_COLUMN_USAGE;

CREATE VIEW TABLE_CACHE_SETTINGS
	( SCHEMA_PK,
	  TABLE_PK,
	  LOWER_LIMIT,
	  UPPER_LIMIT,
	  PERCENT,
	  PERSISTENT,
	  PRELOAD )
   AS SELECT SCHEMA_PK, TABLES_PK, LOWER_LIMIT, UPPER_LIMIT, PERCENT,
             PERSISTENT, PRELOAD
	FROM DEFINITION_SCHEMA.TABLE_CACHE_SETTINGS;

CREATE VIEW CONSTRAINT_NAMES
   AS SELECT CONSTRAINT_NAMES_PK, "CONSTRAINT_NAME", CONSTRAINT_KIND
	FROM DEFINITION_SCHEMA.CONSTRAINT_NAMES;

CREATE VIEW TABLE_CONSTRAINTS
   AS SELECT TABLE_PK, CONSTRAINT_TYPE, CONSTRAINT_NAME_PK,
             IS_DEFERRABLE, INITIALLY_DEFERRED
      FROM DEFINITION_SCHEMA.TABLE_CONSTRAINTS;

CREATE VIEW KEY_COLUMN_USAGE
   AS SELECT TABLE_PK, CONSTRAINT_NAME_PK, COLUMN_PK, ORDINAL_POSITION
        FROM DEFINITION_SCHEMA.KEY_COLUMN_USAGE;

CREATE VIEW REFERENTIAL_CONSTRAINTS
   AS SELECT CONSTRAINT_NAME_PK, UNIQUE_CONSTRAINT_NAME_PK, MATCH_OPTION,
             UPDATE_RULE, DELETE_RULE
        FROM DEFINITION_SCHEMA.REFERENTIAL_CONSTRAINTS;

CREATE VIEW COLUMNS
   AS SELECT COLUMN_PK, "COLUMN_NAME", ORDINAL_POSITION,
             TABLE_PK, IS_NULLABLE, COLUMN_DEFAULT, DOMAIN_PK
        FROM DEFINITION_SCHEMA.COLUMNS;

CREATE VIEW COLUMN_PRIVILEGES
   AS SELECT GRANTOR_PK, GRANTEE_PK, TABLE_PK, COLUMN_PK,
             PRIVILEGE_TYPE, IS_GRANTABLE
	FROM DEFINITION_SCHEMA.COLUMN_PRIVILEGES;

CREATE VIEW TABLE_PRIVILEGES
   AS SELECT GRANTOR_PK, GRANTEE_PK, TABLE_PK, PRIVILEGE_TYPE, IS_GRANTABLE
	FROM DEFINITION_SCHEMA.TABLE_PRIVILEGES;

CREATE VIEW PSM_ROUTINES
   AS SELECT SCHEMA_PK, SPECIFIC_NAME, ROUTINE_NAME, PARAMETER_COUNT,
             SQL_STMT, ROUTINE_KIND, RETURN_TYPE_PK, PSM_DEFINITION_PK
        FROM DEFINITION_SCHEMA.PSM_ROUTINES;

CREATE VIEW EXECUTE_PRIVILEGES
   AS SELECT GRANTOR_PK, GRANTEE_PK, SCHEMA_PK, ROUTINE_PK, IS_GRANTABLE
	FROM DEFINITION_SCHEMA.EXECUTE_PRIVILEGES;

CREATE VIEW GROUPS
   AS SELECT GROUP_PK, GROUP_NAME FROM DEFINITION_SCHEMA.GROUPS;

CREATE VIEW GROUP_USER_USAGE
   AS SELECT GROUP_PK, USER_PK FROM DEFINITION_SCHEMA.GROUP_USER_USAGE;

CREATE TRANSLATION TO_UPPER FOR SQL_TEXT TO SQL_TEXT
	FROM EXTERNAL('ToUpper.trans');

CREATE TRANSLATION TO_LOWER FOR SQL_TEXT TO SQL_TEXT
	FROM EXTERNAL('ToLower.trans');

CREATE TABLE EOMODELER_RELATIONS(
   "SCHEMA_NAME"	CHARACTER VARYING(256),
   RELATION_NAME	CHARACTER VARYING(256),
   JOIN_SEMANTIC	INTEGER, 
   TO_MANY		INTEGER, 
   SOURCE_TABLE		CHARACTER VARYING(256),
   SOURCE_COLUMNS	CHARACTER VARYING(256), 
   DESTINATION_TABLE	CHARACTER VARYING(256),
   DESTINATION_COLUMNS	CHARACTER VARYING(256)
);

CREATE TABLE BLACK_AND_WHITE_LIST(
   BLACK_AND_WHITE_LIST_PK	INTEGER DEFAULT UNIQUE PRIMARY KEY,
   IP_ADDR			CHAR(256),
   NETMASK			CHAR(256),
   WHITE_LISTED			CHAR(3),
   SECURE			CHAR(3)
);

INSERT INTO BLACK_AND_WHITE_LIST VALUES(0, '127.000.000.001', '255.255.255.255', 'YES', 'NO');
INSERT INTO BLACK_AND_WHITE_LIST VALUES(1, '000.000.000.000', '000.000.000.000', 'YES', 'YES');

CREATE PSEUDO TABLE ROOT_ROOT(
   VERSION		VARCHAR(32),
   "CATALOG_NAME"		VARCHAR(128),
   ROOT_TABLES		INT,
   ROOT_COLUMNS		INT,
   DB_PASSWORD		VARCHAR(128),
   FLAGS			INT,
   CREATION_TIME		BIT(64),
   PREVIOUS_START		BIT(64),
   MAX_FETCH_SIZE		INT,
   TRANSACTION_LOG_FLAGS	INT,
   TRANSACTION_LOG_LIMIT	INT,
   DB_ID			BIT(96)
);

CREATE PSEUDO TABLE ROOT_TABLES(
   ROOT_TABLE_PK		INT,
   ROOT_TABLE_NAME	VARCHAR(128)
);

CREATE PSEUDO TABLE ROOT_COLUMNS(
   ROOT_COLUMN_PK		INT,
   ROOT_COLUMN_POSITION	INT,
   ROOT_TABLE_FK		INT,
   ROOT_COLUMN_NAME	VARCHAR(64),
   ROOT_COLUMN_DATATYPE	INT,
   ROOT_COLUMN_SIZE	INT
);

CREATE PSEUDO TABLE DEVICES (
  "PK"       INTEGER,
  "NAME"     CHARACTER VARYING(128),
  "PATH"     CHARACTER VARYING(1024),
  "CAPACITY" LONGINT
);
GRANT SELECT ON DEVICES TO "_SYSTEM";

CREATE PSEUDO TABLE PARTITIONS (
   "PK"            INTEGER,
   "NAME"          CHARACTER VARYING(128),
   "DEVICE_PK"     INTEGER,
   "BASE_ADDRESS"  LONGINT, 
   "CAPACITY"      LONGINT,
   "BLOCKS_IN_USE" LONGINT,
   "HIGH_WATER"    LONGINT
);
GRANT SELECT ON PARTITIONS TO "_SYSTEM";

CREATE PSEUDO TABLE DISK_ZONES (
   "PK"           INTEGER,
   "NAME"         CHARACTER VARYING(128)
);
GRANT SELECT ON DISK_ZONES TO "_SYSTEM";

CREATE PSEUDO TABLE DISK_ZONE_PARTITIONS (
   "DISK_ZONE_PK"  INTEGER,
   "PARTITION_PK"  INTEGER,
   "POSITION"      INTEGER
);
GRANT SELECT ON DISK_ZONE_PARTITIONS TO "_SYSTEM";

CREATE PSEUDO TABLE DISK_ZONE_USAGE_TABLES(
   CATALOG_PK	INTEGER,
   SCHEMA_PK	INTEGER,
   TABLE_PK	INTEGER,
   "TABLE_NAME"	VARCHAR(256),
   DZ_DATA	INTEGER,
   DZ_VARCHAR	INTEGER,
   DZ_LOB	INTEGER,
   DZ_INDEX	INTEGER
);
GRANT SELECT ON DISK_ZONE_USAGE_TABLES TO "_SYSTEM";

CREATE PSEUDO TABLE TABLE_BASE_STATISTICS
(
   DATA_TABLE_PK	 INTEGER,
   
   OPEN_COUNT		 LONGINT,
   VERSION_COUNT	 LONGINT,
   
   "ROW_COUNT"           LONGINT,
   ROW_INSERT_COUNT      LONGINT,
   ROW_READ_COUNT        LONGINT,
   ROW_UPDATE_COUNT      LONGINT,
   ROW_DELETE_COUNT      LONGINT,
   ROW_HIT_COUNT         LONGINT,
   ROW_DROP_COUNT        LONGINT,
   
   STRING_COUNT          LONGINT,
   STRING_INSERT_COUNT   LONGINT,
   STRING_READ_COUNT     LONGINT,
   STRING_DELETE_COUNT   LONGINT,
   STRING_HIT_COUNT      LONGINT,
   STRING_DROP_COUNT     LONGINT,
   
   INDEX_COMPARE_COUNT	 LONGINT,
   INDEX_CREATE_COUNT	 LONGINT,
   INDEX_READ_COUNT	 LONGINT,
   INDEX_FILL_COUNT	 LONGINT
);
GRANT SELECT ON TABLE_BASE_STATISTICS TO "_SYSTEM";


CREATE PSEUDO PROCEDURE CLEAR_TABLE_STATISTICS ();
GRANT execute ON procedure CLEAR_TABLE_STATISTICS TO "_SYSTEM";

CREATE VIEW TABLE_STATISTICS
 ( 
   "SCHEMA_NAME",
   "TABLE_NAME",
    OPEN_COUNT,
    VERSION_COUNT,
    "ROW_COUNT",
    ROW_INSERT_COUNT,
    ROW_READ_COUNT,
    ROW_UPDATE_COUNT,
    ROW_DELETE_COUNT,
    ROW_HIT_COUNT,
    ROW_DROP_COUNT,
    
    STRING_COUNT,
    STRING_INSERT_COUNT,
    STRING_READ_COUNT,
    STRING_DELETE_COUNT,
    STRING_HIT_COUNT,
    STRING_DROP_COUNT,
    
    INDEX_COMPARE_COUNT,
    INDEX_CREATE_COUNT,
    INDEX_READ_COUNT,
    INDEX_FILL_COUNT 
 ) AS 
SELECT 
    T2."SCHEMA_NAME",
    T1."TABLE_NAME",
    T0.OPEN_COUNT,
    T0.VERSION_COUNT,
    T0."ROW_COUNT",
    T0.ROW_INSERT_COUNT,
    T0.ROW_READ_COUNT,
    T0.ROW_UPDATE_COUNT,
    T0.ROW_DELETE_COUNT,
    T0.ROW_HIT_COUNT,
    T0.ROW_DROP_COUNT,
    T0.STRING_COUNT,
    T0.STRING_INSERT_COUNT,
    T0.STRING_READ_COUNT,
    T0.STRING_DELETE_COUNT,
    T0.STRING_HIT_COUNT,
    T0.STRING_DROP_COUNT,
    T0.INDEX_COMPARE_COUNT,
    T0.INDEX_CREATE_COUNT,
    T0.INDEX_READ_COUNT,
    T0.INDEX_FILL_COUNT  
FROM 
    INFORMATION_SCHEMA.TABLE_BASE_STATISTICS T0,
    DEFINITION_SCHEMA.TABLES T1,
    DEFINITION_SCHEMA.SCHEMATA T2
WHERE
   T1.DATA_TABLE_PK = T0.DATA_TABLE_PK AND
   T2.SCHEMA_PK     = T1.SCHEMA_PK;

CREATE TABLE TABLE_STATISTICS_HISTORY
(
   STAMP		       timestamp(3) default cast(current_timestamp(3) as timestamp(3)),
   DATA_TABLE_PK	 INTEGER,
   
   OPEN_COUNT		 LONGINT,
   VERSION_COUNT	 LONGINT,
   
   "ROW_COUNT"           LONGINT,
   ROW_INSERT_COUNT      LONGINT,
   ROW_READ_COUNT        LONGINT,
   ROW_UPDATE_COUNT      LONGINT,
   ROW_DELETE_COUNT      LONGINT,
   ROW_HIT_COUNT         LONGINT,
   ROW_DROP_COUNT        LONGINT,
   
   STRING_COUNT          LONGINT,
   STRING_INSERT_COUNT   LONGINT,
   STRING_READ_COUNT     LONGINT,
   STRING_DELETE_COUNT   LONGINT,
   STRING_HIT_COUNT      LONGINT,
   STRING_DROP_COUNT     LONGINT,
   
   INDEX_COMPARE_COUNT	 LONGINT,
   INDEX_CREATE_COUNT	 LONGINT,
   INDEX_READ_COUNT	 LONGINT,
   INDEX_FILL_COUNT	 LONGINT,
   primary key (STAMP,DATA_TABLE_PK)
);

GRANT SELECT ON TABLE_STATISTICS_HISTORY TO "_SYSTEM";
GRANT INSERT ON TABLE_STATISTICS_HISTORY TO "_SYSTEM";
GRANT DELETE ON TABLE_STATISTICS_HISTORY TO "_SYSTEM";

create view
   statistic_history
as select 
   t3.stamp as stamp0,
   t4.stamp as stamp1,    
   T2."SCHEMA_NAME",
   T1."TABLE_NAME",
   t4.OPEN_COUNT          - t3.OPEN_COUNT          as OPEN_COUNT,      
   t4.VERSION_COUNT       - t3.VERSION_COUNT       as VERSION_COUNT,
   t4."ROW_COUNT",
   t4.ROW_INSERT_COUNT    - t3.ROW_INSERT_COUNT    as ROW_INSERT_COUNT,
   t4.ROW_READ_COUNT      - t3.ROW_READ_COUNT      as ROW_READ_COUNT,
   t4.ROW_UPDATE_COUNT    - t3.ROW_UPDATE_COUNT    as ROW_UPDATE_COUNT,
   t4.ROW_DELETE_COUNT    - t3.ROW_DELETE_COUNT    as ROW_DELETE_COUNT, 
   t4.ROW_HIT_COUNT       - t3.ROW_HIT_COUNT       as ROW_HIT_COUNT,
   t4.ROW_DROP_COUNT      - t3.ROW_DROP_COUNT      as ROW_DROP_COUNT,
   t4.STRING_COUNT        - t3.STRING_COUNT        as STRING_COUNT,
   t4.STRING_INSERT_COUNT - t3.STRING_INSERT_COUNT as STRING_INSERT_COUNT,
   t4.STRING_READ_COUNT   - t3.STRING_READ_COUNT   as STRING_READ_COUNT,
   t4.STRING_DELETE_COUNT - t3.STRING_DELETE_COUNT as STRING_DELETE_COUNT,
   t4.STRING_HIT_COUNT    - t3.STRING_HIT_COUNT    as STRING_HIT_COUNT,
   t4.STRING_DROP_COUNT   - t3.STRING_DROP_COUNT   as STRING_DROP_COUNT,
   t4.INDEX_COMPARE_COUNT - t3.INDEX_COMPARE_COUNT as INDEX_COMPARE_COUNT,
   t4.INDEX_CREATE_COUNT  - t3.INDEX_CREATE_COUNT  as INDEX_CREATE_COUNT,
   t4.INDEX_READ_COUNT    - t3.INDEX_READ_COUNT    as INDEX_READ_COUNT,
   t4.INDEX_FILL_COUNT    - t3.INDEX_FILL_COUNT    as INDEX_FILL_COUNT
from
   DEFINITION_SCHEMA.TABLES T1,
   DEFINITION_SCHEMA.SCHEMATA T2,
   information_schema.TABLE_STATISTICS_HISTORY T3,
   information_schema.TABLE_STATISTICS_HISTORY T4
where
   T3.DATA_TABLE_PK = T1.DATA_TABLE_PK AND
   T4.DATA_TABLE_PK = T1.DATA_TABLE_PK AND
   T2.SCHEMA_PK     = T1.SCHEMA_PK;
GRANT all privileges ON statistic_history TO "_SYSTEM";

create procedure table_statistics()
begin
   declare stamp1 timestamp;
   declare stamp0 timestamp;

   set :stamp0 = (select max(stamp) from information_schema.TABLE_STATISTICS_HISTORY);
   set :stamp1 = cast(current_timestamp(3) as timestamp(3));

   insert into 
      information_schema.TABLE_STATISTICS_HISTORY 
   select 
      :stamp1, 
      DATA_TABLE_PK,
      OPEN_COUNT,	
      VERSION_COUNT,
      "ROW_COUNT",       
      ROW_INSERT_COUNT,   
      ROW_READ_COUNT,     
      ROW_UPDATE_COUNT,   
      ROW_DELETE_COUNT,   
      ROW_HIT_COUNT,      
      ROW_DROP_COUNT,     
      STRING_COUNT,       
      STRING_INSERT_COUNT,
      STRING_READ_COUNT,  
      STRING_DELETE_COUNT,
      STRING_HIT_COUNT,   
      STRING_DROP_COUNT,  
      INDEX_COMPARE_COUNT,
      INDEX_CREATE_COUNT,
      INDEX_READ_COUNT,
      INDEX_FILL_COUNT
   from
      information_schema.TABLE_BASE_STATISTICS;
   if :stamp0 is not null
   then
      select * from statistic_history where stamp0 = :stamp0 and stamp1 = :stamp1;
   end if;
end;
GRANT execute ON procedure table_statistics TO "_SYSTEM";


CREATE PSEUDO TABLE IO_STATISTICS
(
    blockSize INTEGER,           -- 0
    allocatedBlocks LONGINT,     -- 1
    freeBlocks LONGINT,          -- 2
    
--    descrReads LONGINT,
--    descrWrites LONGINT,
--    descrCacheHits LONGINT,
--    descrCachePages LONGINT,
--    descrCachePageCount LONGINT,
--    descrCacheSize LONGINT,
    
--    dataReadsCount LONGINT,
--    dataReadsBlocks LONGINT,
--    dataWritesCount LONGINT,
--    dataWritesBlocks LONGINT,
    
    synchCount LONGINT,          -- 3

    rawDriver BOOLEAN,           -- 4
 
    byteSize LONGINT,            -- 5
    pageCount LONGINT,           -- 6
    defragPages LONGINT,         -- 7
    pagesInCache LONGINT,        -- 8
    freePages LONGINT,           -- 9
    freePageAllocs LONGINT,      -- 10
    freePageStalls LONGINT,      -- 11

    pagesPendingI LONGINT,       -- 12
    pendingSyncs LONGINT,        -- 13
    pendingOs LONGINT,           -- 14
    readCount LONGINT,           -- 15
    readBlocks LONGINT,          -- 16
    readHits LONGINT,            -- 17
    readStalls LONGINT,          -- 18
    readWaits LONGINT,           -- 19

    unbufferedReadWaits LONGINT, -- 20
    cacheDrops LONGINT,          -- 21
    writeCount LONGINT,          -- 22
    writeBlocks LONGINT,         -- 23
    writeStalls LONGINT,         -- 24
    deviceReadCount LONGINT,     -- 25
    deviceReadBlocks LONGINT,    -- 26
    deviceWriteCount LONGINT,    -- 27
    deviceWriteBlocks LONGINT,   -- 28
    
    commitGroupSize INT,         -- 29
    commitGroupCount INT,        -- 30
    rollbackTransactions LONGINT,-- 31
    commitTransactions LONGINT,  -- 32
    degradedTransactions LONGINT,-- 33
    commitCount LONGINT,         -- 34
    rollbackCount LONGINT,       -- 35
    flushCount LONGINT           -- 36
         
);

GRANT SELECT ON IO_STATISTICS TO "_SYSTEM";

create view commit_statistics as select commitGroupSize, commitGroupCount, rollbackTransactions, commitTransactions, degradedTransactions, commitCount, rollbackCount, flushCount from IO_STATISTICS;
GRANT  SELECt ON commit_statistics TO "_SYSTEM";

CREATE COLLATION CASE_INSENSITIVE
   FOR SQL_TEXT
   FROM EXTERNAL('CaseInsensitive.coll1');
GRANT USAGE ON COLLATION CASE_INSENSITIVE TO PUBLIC;

CREATE INDEX ON EOMODELER_RELATIONS("SCHEMA_NAME", SOURCE_TABLE);

GRANT SELECT, UPDATE, INSERT, DELETE ON EOMODELER_RELATIONS TO PUBLIC;
GRANT SELECT ON INFORMATION_SCHEMA_CATALOG_NAME TO PUBLIC;

GRANT SELECT ON BLACK_AND_WHITE_LIST TO PUBLIC;
GRANT SELECT, UPDATE, INSERT, DELETE ON BLACK_AND_WHITE_LIST TO "_SYSTEM";

GRANT SELECT ON ROOT_ROOT    TO "_SYSTEM";
GRANT SELECT ON ROOT_TABLES  TO "_SYSTEM";
GRANT SELECT ON ROOT_COLUMNS TO "_SYSTEM";

GRANT DELETE ON INFORMATION_SCHEMA_CATALOG_NAME TO "_SYSTEM";

CREATE PSEUDO TABLE FRAGMENTATION
(
   DATA_TABLE_PK         INTEGER,
   FILE_VERSION          LONGINT,
   FRAGMENTATION_VERSION LONGINT,
   BLOCKS                LONGINT,
   SEEK                  LONGINT
);
GRANT SELECT ON FRAGMENTATION TO "_SYSTEM";

create pseudo procedure defragment ( :seek_percent integer );
GRANT execute ON procedure defragment TO "_system";

COMMIT;

alter table DEFINITION_SCHEMA."CLUSTERS" set preserve true;
alter table DEFINITION_SCHEMA."CATALOGS" set preserve true;
alter table DEFINITION_SCHEMA."USERS" set preserve true;
alter table DEFINITION_SCHEMA."SCHEMATA" set preserve true;
alter table DEFINITION_SCHEMA."TABLES" set preserve true;
alter table DEFINITION_SCHEMA."DATA_TYPE_DESCRIPTOR" set preserve true;
alter table DEFINITION_SCHEMA."DOMAINS" set preserve true;
alter table DEFINITION_SCHEMA."DOMAIN_CONSTRAINTS" set preserve true;
alter table DEFINITION_SCHEMA."VIEWS" set preserve true;
alter table DEFINITION_SCHEMA."COLUMNS" set preserve true;
alter table DEFINITION_SCHEMA."VIEW_TABLE_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."VIEW_COLUMN_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."VIEW_OBJECT_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."CONSTRAINT_NAMES" set preserve true;
alter table DEFINITION_SCHEMA."TABLE_CONSTRAINTS" set preserve true;
alter table DEFINITION_SCHEMA."KEY_COLUMN_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."REFERENTIAL_CONSTRAINTS" set preserve true;
alter table DEFINITION_SCHEMA."CHECK_CONSTRAINTS" set preserve true;
alter table DEFINITION_SCHEMA."CHECK_TABLE_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."CHECK_COLUMN_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."CHECK_OBJECT_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."ASSERTIONS" set preserve true;
alter table DEFINITION_SCHEMA."TABLE_PRIVILEGES" set preserve true;
alter table DEFINITION_SCHEMA."COLUMN_PRIVILEGES" set preserve true;
alter table DEFINITION_SCHEMA."USAGE_PRIVILEGES" set preserve true;
alter table DEFINITION_SCHEMA."CHARACTER_SETS" set preserve true;
alter table DEFINITION_SCHEMA."COLLATIONS" set preserve true;
alter table DEFINITION_SCHEMA."TRANSLATIONS" set preserve true;
alter table DEFINITION_SCHEMA."SQL_LANGUAGES" set preserve true;
alter table DEFINITION_SCHEMA."INDEXES" set preserve true;
alter table DEFINITION_SCHEMA."INDEX_COLUMN_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."TEMP_TABLES" set preserve true;
alter table DEFINITION_SCHEMA."TABLE_CACHE_SETTINGS" set preserve true;
--alter table DEFINITION_SCHEMA."WHISKEY_LIST" set preserve true;
alter table DEFINITION_SCHEMA."GROUPS" set preserve true;
alter table DEFINITION_SCHEMA."GROUP_USER_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."ROW_PRIVILEGE_DEFAULTS" set preserve true;
alter table DEFINITION_SCHEMA."PSM_ROUTINES" set preserve true;
alter table DEFINITION_SCHEMA."PSM_PARAMETERS" set preserve true;
alter table DEFINITION_SCHEMA."PSM_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."PSM_DEFAULT_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."PSM_CHECK_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."EXECUTE_PRIVILEGES" set preserve true;
alter table DEFINITION_SCHEMA."LS_INDEXES" set preserve true;
alter table DEFINITION_SCHEMA."TRIGGERS" set preserve true;
alter table DEFINITION_SCHEMA."TRIGGERED_UPDATE_COLUMNS" set preserve true;	
alter table DEFINITION_SCHEMA."TRIGGER_TABLE_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."TRIGGER_COLUMN_USAGE" set preserve true;
alter table DEFINITION_SCHEMA."TRIGGER_OBJECT_USAGE" set preserve true;

alter table INFORMATION_SCHEMA."BLACK_AND_WHITE_LIST" set preserve true;

COMMIT;

create pseudo procedure database_id( inout :db_id bit(96));
create function database_id () returns bit(96)
begin
   declare dbid bit(96) default x'123456789012345678901234';
   call database_id(:dbid);
   return :dbid;
end;
GRANT execute ON procedure database_id TO PUBLIC;
GRANT execute ON function  database_id TO PUBLIC;
COMMIT;

