use ##database
go
drop table     archivedetail
go
drop table     archivesummary
go
drop table     umonhistory
go
drop table     umonhistsummary
go

create table archivesummary
   (end_time      smalldatetime     not null,
    start_time    smalldatetime     not null,
    cpu_min       int               not null,
    cpu_max       int               not null,
    total_read    int               not null,
    total_write   int               not null,
    log_write     int               not null,
    xacts         int               not null,
    ddlks         int               not null,
    dcache        double precision  not null,
    pcache        double precision  not null,
    auxi1         int               not null,
    auxi2         int               not null,
    auxdp1        double precision  not null,
    auxdp2        double precision  not null,

   primary key clustered  (end_time))
go
sp_setpglockpromote 'table', archivesummary, 2, 2, 1
go

create table archivedetail
   (end_time      smalldatetime     not null,
    group_name    varchar (25)      not null,
    field_name    varchar (35)      not null,
    field_id      smallint          not null,
    value         int               not null,

   foreign key (end_time)  references archivesummary (end_time))
go
create clustered index C on archivedetail (end_time, group_name, field_name, field_id)
go
sp_setpglockpromote 'table', archivedetail, 2, 2, 1
go

create table umonhistsummary
   (seq           tinyint           not null,
    start_time    datetime          not null,
    end_time      datetime          not null,
    cpu_min       int               not null,
    cpu_max       int               not null,
    total_read    int               not null,
    total_write   int               not null,
    log_write     int               not null,
    xacts         int               not null,
    ddlks         int               not null,
    dcache        double precision  not null,
    pcache        double precision  not null,
    auxi1         int               not null,
    auxi2         int               not null,
    auxdp1        double precision  not null,
    auxdp2        double precision  not null,

   primary key clustered  (seq))
go
sp_setpglockpromote 'table', umonhistsummary, 2, 2, 1
go

create table umonhistory
   (seq           tinyint           not null,
    group_name    varchar (25)      not null,
    field_name    varchar (35)      not null,
    field_id      smallint          not null,
    value         int               not null,

   foreign key (seq)  references umonhistsummary (seq))
go
sp_setpglockpromote 'table', umonhistory, 2, 2, 1
go

insert into umonhistsummary values ( 1, getdate (), getdate (),  1,  1,  11, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values ( 2, getdate (), getdate (),  2,  2,  22, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values ( 3, getdate (), getdate (),  3,  3,  13, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values ( 4, getdate (), getdate (),  4,  4,  14, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values ( 5, getdate (), getdate (),  5,  5,  15, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values ( 6, getdate (), getdate (),  6,  6,  16, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values ( 7, getdate (), getdate (),  7,  7,  17, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values ( 8, getdate (), getdate (),  8,  8,  18, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values ( 9, getdate (), getdate (),  9,  9,  19, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (10, getdate (), getdate (), 10, 10, 110, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (11, getdate (), getdate (), 11, 11, 111, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (12, getdate (), getdate (), 12, 12, 112, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (13, getdate (), getdate (), 13, 13, 113, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (14, getdate (), getdate (), 14, 14, 114, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (15, getdate (), getdate (), 15, 15, 115, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (16, getdate (), getdate (), 16, 16, 116, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (17, getdate (), getdate (), 17, 17, 117, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (18, getdate (), getdate (), 18, 18, 118, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (19, getdate (), getdate (), 19, 19, 119, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (20, getdate (), getdate (), 20, 20, 120, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (21, getdate (), getdate (), 21, 21, 121, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (22, getdate (), getdate (), 22, 22, 122, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (23, getdate (), getdate (), 23, 23, 123, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (24, getdate (), getdate (), 24, 24, 124, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (25, getdate (), getdate (), 25, 25, 125, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (26, getdate (), getdate (), 26, 26, 126, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (27, getdate (), getdate (), 27, 27, 127, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (28, getdate (), getdate (), 28, 28, 128, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (29, getdate (), getdate (), 29, 29, 129, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (30, getdate (), getdate (), 30, 30, 130, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (31, getdate (), getdate (), 31, 31, 131, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (32, getdate (), getdate (), 32, 32, 132, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (33, getdate (), getdate (), 33, 33, 133, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (34, getdate (), getdate (), 34, 34, 134, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (35, getdate (), getdate (), 35, 35, 135, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (36, getdate (), getdate (), 36, 36, 136, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (37, getdate (), getdate (), 37, 37, 137, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (38, getdate (), getdate (), 38, 38, 138, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (39, getdate (), getdate (), 39, 39, 139, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (40, getdate (), getdate (), 40, 40, 140, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (41, getdate (), getdate (), 41, 41, 141, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (42, getdate (), getdate (), 42, 42, 142, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (43, getdate (), getdate (), 43, 43, 143, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (44, getdate (), getdate (), 44, 44, 144, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (45, getdate (), getdate (), 45, 45, 145, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (46, getdate (), getdate (), 46, 46, 146, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (47, getdate (), getdate (), 47, 47, 147, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (48, getdate (), getdate (), 48, 48, 148, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (49, getdate (), getdate (), 49, 49, 149, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (50, getdate (), getdate (), 50, 50, 150, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (51, getdate (), getdate (), 51, 51, 151, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (52, getdate (), getdate (), 52, 52, 152, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (53, getdate (), getdate (), 53, 53, 153, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (54, getdate (), getdate (), 54, 54, 154, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (55, getdate (), getdate (), 55, 55, 155, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (56, getdate (), getdate (), 56, 56, 156, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (57, getdate (), getdate (), 57, 57, 157, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (58, getdate (), getdate (), 58, 58, 158, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (59, getdate (), getdate (), 59, 59, 159, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
insert into umonhistsummary values (60, getdate (), getdate (), 60, 60, 160, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
go
create clustered index  C on umonhistory (seq, group_name, field_name, field_id)
go
