SQWareRepository:db2

Presentation

It is the repository module, and stores rdbms indicators for all RDBMS.
It is a MySQL database.
Objects prefix by tsqw_% or isqw_% are generic.
Objects prefix by tsqwdb2_% or isqwdb2_% are specific for DB2.
Installation guide for SQWareRepository for DB2

Naming convention

All specific objects for DB2 are named with prefixes:

• tsqwdb2_% : for tables
• isqwdb2_% : for index
• isqwdb2_%_u : for uniques
• isqwdb2_%_pk : for primary key

Database content

tsqw_Repository

This table contains : Instance repository
Desc of tsqw_Repository :

/* Instance repository */
create table dbsqware.tsqw_Repository (
 db2_sid varchar(80) not null,
 virt_host_name varchar (64) not null,
 host_name varchar (64) not null,
 username  varchar (60) not null,
 port integer not null,
 comments varchar(500),
 contact varchar(100),
 status varchar(10) default 'ON',
 client varchar (60) default 'N.A',
 upd_date timestamp,
 env  varchar(12)
)
;
alter table dbsqware.tsqw_Repository add constraint isqw_Repository_pk primary key (db2_sid);

grant select on dbsqware.tsqw_Repository to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_Repository to DBSQWARE_RMAJ;

=== RepositoryOther Instance repository Other === This table contains :

create table dbsqware.tsqw_RepositoryOther (
 db2_sid varchar(80) not null,
 virt_host_name varchar (64) not null,
 host_name varchar(64) not null,
 username  varchar(60) not null,
 port integer not null,
 comments varchar(500),
 contact varchar(100),
 status varchar(10) default 'XXX',
 client varchar (60) default 'N.A',
 upd_date timestamp,
 env  varchar(12)
)
;
grant select on dbsqware.tsqw_RepositoryOther to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_RepositoryOther to DBSQWARE_RMAJ;

Desc of RepositoryOther Instance repository Other :
{{{3}}}

=== tsqw_JobsExec Jobs monitoring === This table contains :

create table dbsqware.tsqw_JobsExec
(
 db2_sid varchar(80) not null,
 beginning timestamp not null,
 script char(80) not null,
 parameters varchar(400) not null,
 end timestamp,
 duration char(8),
 status integer
)
;
alter table dbsqware.tsqw_JobsExec add constraint isqw_JobsExec_pk primary key (db2_sid, beginning, script, parameters);

grant select on dbsqware.tsqw_JobsExec to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_JobsExec to DBSQWARE_RMAJ;

alter table dbsqware.tsqw_JobsExec ADD COLUMN LOG_FILE CLOB(10M);
alter table dbsqware.tsqw_JobsExec ADD COLUMN LOG_FILE CLOB(10M);

CREATE SEQUENCE dbsqware.SSQW_LOG_ID START WITH 100 INCREMENT BY 1 NO MAXVALUE NO CYCLE CACHE 5;
grant usage on sequence dbsqware.SSQW_LOG_ID to DBSQWARE_RMAJ;

alter table dbsqware.tsqw_JobsExec ADD COLUMN LOG_ID numeric(12) default -1;
create index dbsqware.isqw_JobsExec_LOG_ID on dbsqware.tsqw_JobsExec(LOG_ID);

Desc of tsqw_JobsExec Jobs monitoring :
{{{3}}}

=== tsqw_VolFS vol fs === This table contains :

create table dbsqware.tsqw_VolFS (
 db2_sid varchar(80) not null,
 gather_date date not null,
 host_name varchar(64) not null,
 filesystem varchar(200) not null,
 size_fs double not null,
 used double not null,
 free double not null,
 rate integer not null,
 mount varchar(200) not null
)
;
alter table dbsqware.tsqw_VolFS add constraint isqw_VolFS_pk primary key (db2_sid, gather_date,mount);

grant select on dbsqware.tsqw_VolFS to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_VolFS to DBSQWARE_RMAJ;

Desc of tsqw_VolFS vol fs :
{{{3}}}

=== tsqw_VolFSThreshold threshold === This table contains :

create table dbsqware.tsqw_VolFSThreshold (
 db2_sid varchar(80) not null,
 mount varchar (200) not null,
 threshold_c integer not null,
 threshold_w integer not null
)
;
alter table dbsqware.tsqw_VolFSThreshold add constraint isqw_VolFSThreshold_pk primary key (db2_sid, mount);

grant select on dbsqware.tsqw_VolFSThreshold to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_VolFSThreshold to DBSQWARE_RMAJ;

create table dbsqware.tsqw_VolFSThresholdDef (
 size_fs integer not null,
 threshold_c integer not null,
 threshold_w integer not null
)
;
alter table dbsqware.tsqw_VolFSThresholdDef add constraint isqw_VolFSThreshold_pk primary key (size_fs);

grant select on dbsqware.tsqw_VolFSThresholdDef to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_VolFSThresholdDef to DBSQWARE_RMAJ;

insert into dbsqware.tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (2048, 90, 80);
insert into dbsqware.tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (10240, 92, 85);
insert into dbsqware.tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (20480, 95, 90);
insert into dbsqware.tsqw_VolFSThresholdDef (size_fs,threshold_c,threshold_w) values (102400000, 99, 97);

/* vol databases */

Desc of tsqw_VolFSThreshold threshold :
{{{3}}}

=== dbsqware.tsqw_VolDb dbsqware.tsqw_VolInstance dbsqware.tsqw_GenHisto

===

This table contains : create table dbsqware.tsqw_VolDb (
Desc of dbsqware.tsqw_VolDb dbsqware.tsqw_VolInstance dbsqware.tsqw_GenHisto

:

create table dbsqware.tsqw_VolDb (
 db2_sid varchar(80) not null,
 database_name varchar(80) not null,
 gather_date date not null,
 size_db double,
 max_size_db double
)
;
alter table dbsqware.tsqw_VolDb add constraint isqw_VolDb_pk primary key (db2_sid, database_name, gather_date);

grant select on dbsqware.tsqw_VolDb to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_VolDb to DBSQWARE_RMAJ;

create table dbsqware.tsqw_VolInstance (
 db2_sid varchar(80) not null,
 gather_date date not null,
 size_instance double,
 maj_version integer,
 version varchar(30)
)
;
alter table dbsqware.tsqw_VolInstance add constraint isqw_VolInstance_pk primary key (db2_sid, gather_date);

grant select on dbsqware.tsqw_VolInstance to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_VolInstance to DBSQWARE_RMAJ;

create table dbsqware.tsqw_GenHisto (
 db2_sid varchar(80) not null,
 month date not null,
 gather_date date not null,
 size_instance double,
 maj_version integer,
 version varchar(30),
 client varchar (60),
 env  varchar(12)
)
;
alter table dbsqware.tsqw_GenHisto add constraint isqw_GenHisto_pk primary key (db2_sid, month);

grant select on dbsqware.tsqw_GenHisto to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_GenHisto to DBSQWARE_RMAJ;

/* Alert log location*/

=== CREATE TABLE dbsqware.tsqw_AlertFile

===

This table contains : CREATE TABLE dbsqware.tsqw_AlertFile (
Desc of CREATE TABLE dbsqware.tsqw_AlertFile

:

CREATE TABLE dbsqware.tsqw_AlertFile  (
 db2_sid varchar(80) not null,
 gather_date date not null,
 alert_file  varchar(800)
)
;
alter table dbsqware.tsqw_AlertFile add constraint isqw_AlertFile_pk primary key (db2_sid);

grant select on dbsqware.tsqw_AlertFile to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_AlertFile to DBSQWARE_RMAJ;

/* for licence infos */

=== dbsqware.tsqw_GenInfosLicense

===

This table contains : create table dbsqware.tsqw_GenInfosLicense (
Desc of dbsqware.tsqw_GenInfosLicense

:

create table dbsqware.tsqw_GenInfosLicense (
 db2_sid varchar(80) not null,
 gather_date date not null,
 host_name varchar(64) not null,
 cpu_count integer,
 port_string varchar(255),
 banner varchar(255),
 os_type varchar(255)
)
;
alter table dbsqware.tsqw_GenInfosLicense add constraint isqw_GenInfosLicense_pk primary key (db2_sid);

grant select on dbsqware.tsqw_GenInfosLicense to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_GenInfosLicense to DBSQWARE_RMAJ;

/* for version of SQWareRepository */

=== dbsqware.tsqw_RepositoryVersion

===

This table contains : create table dbsqware.tsqw_RepositoryVersion (
Desc of dbsqware.tsqw_RepositoryVersion

:

create table dbsqware.tsqw_RepositoryVersion (
        upd_date timestamp not null,
        action varchar(32) not null,
        version  varchar(17) not null,
        comments varchar(255)
)
;
alter table dbsqware.tsqw_RepositoryVersion add constraint isqw_RepositoryVersion_pk primary key (upd_date,action);

grant select on dbsqware.tsqw_RepositoryVersion to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_RepositoryVersion to DBSQWARE_RMAJ;

/* for exclude check indicators */

=== dbsqware.tsqw_RepositoryExclude

===

This table contains : create table dbsqware.tsqw_RepositoryExclude (
Desc of dbsqware.tsqw_RepositoryExclude

:

create table dbsqware.tsqw_RepositoryExclude (
 db2_sid varchar(80) not null
)
;
alter table dbsqware.tsqw_RepositoryExclude add constraint isqw_RepositoryExclude_pk primary key (db2_sid);

grant select on dbsqware.tsqw_RepositoryExclude to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_RepositoryExclude to DBSQWARE_RMAJ;

/* for instance parameters */

=== dbsqware.tsqw_Parameters

===

This table contains : create table dbsqware.tsqw_Parameters (
Desc of dbsqware.tsqw_Parameters

:

create table dbsqware.tsqw_Parameters (
 db2_sid varchar(80) not null,
 gather_date date not null,
 name varchar(255) not null,
 value varchar(255)
)
;
alter table dbsqware.tsqw_Parameters add constraint isqw_Parameters_pk primary key (db2_sid,gather_date,name);

grant select on dbsqware.tsqw_Parameters to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_Parameters to DBSQWARE_RMAJ;

/* for obsolescence management */

=== dbsqware.tsqw_EndLife

===

This table contains : create table dbsqware.tsqw_EndLife (
Desc of dbsqware.tsqw_EndLife

:

create table dbsqware.tsqw_EndLife  (
  maj_version integer not null,
  ReleaseDate date not null,
  Premier date not null,
  Extended date not null
)
;
alter table dbsqware.tsqw_EndLife add constraint isqw_EndLife_pk primary key (maj_version);

grant select on dbsqware.tsqw_EndLife to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_EndLife to DBSQWARE_RMAJ;

insert into dbsqware.tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values ('101', date('2012-04-30'), date('2017-04-30'), date('2020-04-30'));
insert into dbsqware.tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values ('98', date('2010-04-30'), date('2015-04-30'), date('2018-04-30'));
insert into dbsqware.tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values ('97', date('2009-09-30'), date('2014-09-30'), date('2017-09-30'));
insert into dbsqware.tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values ('95', date('2007-10-31'), date('2014-04-30'), date('2017-04-30'));
insert into dbsqware.tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values ('91', date('2006-07-30'), date('2012-04-30'), date('2015-04-30'));
insert into dbsqware.tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values ('82', date('1900-01-01'), date('2009-04-30'), date('2012-04-30'));
insert into dbsqware.tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values ('81', date('1900-01-01'), date('2009-04-30'), date('2012-04-30'));
insert into dbsqware.tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values ('72', date('1900-01-01'), date('2004-09-30'), date('2004-09-30'));
insert into dbsqware.tsqw_EndLife (maj_version, ReleaseDate, Premier, Extended) values ('71', date('1900-01-01'), date('2003-06-30'), date('2003-06-30'));

/* vol tablespaces */

=== dbsqware.tsqw_VolTbs

===

This table contains : create table dbsqware.tsqw_VolTbs (
Desc of dbsqware.tsqw_VolTbs

:

create table dbsqware.tsqw_VolTbs (
 db2_sid varchar(80) not null,
 database_name varchar(80) not null,
 tablespace_name   varchar(80) not null,
 gather_date date not null,
 size_tbs double,
 used double,
 free double
)
;
alter table dbsqware.tsqw_VolTbs add constraint isqw_VolDb_pk primary key (db2_sid,database_name,tablespace_name,gather_date);

grant select on dbsqware.tsqw_VolTbs to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_VolTbs to DBSQWARE_RMAJ;


/* for db parameters */

=== dbsqware.tsqw_ParametersDb

===

This table contains : create table dbsqware.tsqw_ParametersDb (
Desc of dbsqware.tsqw_ParametersDb

:

create table dbsqware.tsqw_ParametersDb (
 db2_sid varchar(80) not null,
 database_name varchar(80) not null,
 gather_date date not null,
 name varchar(255) not null,
 value varchar(255)
)
;
alter table dbsqware.tsqw_ParametersDb add constraint isqw_Parameters_pk primary key (db2_sid,database_name,gather_date,name);

grant select on dbsqware.tsqw_ParametersDb to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_ParametersDb to DBSQWARE_RMAJ;

/* for table size */

===

===

This table contains : dbaname varchar(50) not null,
Desc of

:

  dbaname       varchar(50)     not null,
  comments      varchar(50)     not null,
  upd_date      timestamp
)
;
alter table dbsqware.tsqw_VolBackupsExcludeDb add constraint isqw_VolBackupsExcludeDb_pk primary key (db2_sid,database_name);

grant select on dbsqware.tsqw_VolBackupsExcludeDb to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_VolBackupsExcludeDb to DBSQWARE_RMAJ;

insert into dbsqware.tsqw_RepositoryVersion (upd_date, action, version, comments) values ( current date, 'Full install', '2.0.12', 'Install full repo in 2.0.12 version');

select 'End of full install 2.0.12'
from sysibm.sysdummy1;