SQWareRepository:db2

From Wiki_dbSQWare
Revision as of 23:18, 17 December 2012 by Expdbtools (talk | contribs) (Created page with '{{SQWareRepositoryPresSgbd|DB2|database|instances}} == Database content == {{SQWareRepositoryTables|dbsqware.tsqw_Repository dbsqware.tsqw_RepositoryOther |<nowiki> create table…')
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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 tsqw{{{4}}}_% or isqw{{{4}}}_% are specific for DB2.
Installation guide for SQWareRepository for DB2

Naming convention

All specific objects for DB2 are named with prefixes:

  • tsqw{{{4}}}_% : for tables
  • isqw{{{4}}}_% : for index
  • isqw{{{4}}}_%_u : for uniques
  • isqw{{{4}}}_%_pk : for primary key


Nuvola apps important.png
Warning:
Do not change the standard tables of the tool. For your custom objects, use another name convention than dbSQWare.

This will facilitate version upgrades.


Database content

=== dbsqware.tsqw_Repository dbsqware.tsqw_RepositoryOther 

===

This table contains : create table dbsqware.tsqw_Repository (
Desc of dbsqware.tsqw_Repository dbsqware.tsqw_RepositoryOther 

:


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;

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;

--alter table dbsqware.tsqw_RepositoryOther ALTER COLUMN env set DATA TYPE varchar(12)"

/* Jobs monitoring */


=== dbsqware.tsqw_JobsExec 

===

This table contains : create table dbsqware.tsqw_JobsExec
Desc of dbsqware.tsqw_JobsExec 

:


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;

/* to load log files in repository */


=== 

===

This table contains : alter table dbsqware.tsqw_JobsExec ADD COLUMN LOG_FILE CLOB(10M);
Desc of 

:


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);

/* vol fs */


=== dbsqware.tsqw_VolFS 

===

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

:


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;

/* threshold */


=== dbsqware.tsqw_VolFSThreshold dbsqware.tsqw_VolFSThresholdDef 

===

This table contains : create table dbsqware.tsqw_VolFSThreshold (
Desc of dbsqware.tsqw_VolFSThreshold dbsqware.tsqw_VolFSThresholdDef 

:


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 */


=== 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;
Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareRepository:db2&oldid=3543"