SQWareRepository:db2: Difference between revisions

From Wiki_dbSQWare
Jump to navigation Jump to search
← Older edit
No edit summary
No edit summary
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
{{SQWareRepositoryPresSgbd|DB2|database|instances|db2}}
{{SQWareRepositoryPresSgbd|DB2|database|instances|db2}}
== Database content ==
== Specific database content for DB2 ==
{{SQWareRepositoryTables|tsqw_Repository|
<nowiki>Instance repository</nowiki>|
<pre>
/* 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;
 
</pre>
}}
 
{{SQWareRepositoryTables|RepositoryOther
<nowiki>Instance repository Other</nowiki>|
<pre>
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;
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_JobsExec
<nowiki>Jobs monitoring</nowiki>|
<pre>
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);
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_VolFS
<nowiki>vol fs</nowiki>|
<pre>
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;
{{SQWareRepositoryTables|tsqwdb2_Parameters|
grant all on dbsqware.tsqw_VolFS to DBSQWARE_RMAJ;
<nowiki>...</nowiki>|
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_VolFSThreshold
<nowiki>threshold</nowiki>|
<pre>
<pre>
create table dbsqware.tsqw_VolFSThreshold (
Field Type
db2_sid varchar(80) not null,
dbalias varchar(80)
mount varchar (200) not null,
gather_date date
threshold_c integer not null,
name varchar(255)
threshold_w integer not null
value varchar(255)
)
;
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 */
</pre>
</pre>
}}
}}


{{SQWareRepositoryTables|dbsqware.tsqw_VolDb
{{SQWareRepositoryTables|tsqwdb2_VolDb|
dbsqware.tsqw_VolInstance
<nowiki>...</nowiki>|
dbsqware.tsqw_GenHisto
|<nowiki>
create table dbsqware.tsqw_VolDb (
</nowiki>|
<pre>
<pre>
create table dbsqware.tsqw_VolDb (
Field Type
db2_sid varchar(80) not null,
dbalias varchar(80)
database_name varchar(80) not null,
gather_date date
gather_date date not null,
size_db decimal(12,2)
size_db double,
max_size_db decimal(12,2)
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*/
</pre>
</pre>
}}
}}


{{SQWareRepositoryTables|CREATE TABLE dbsqware.tsqw_AlertFile
{{SQWareRepositoryTables|tsqwdb2_VolSchema|
|<nowiki>
<nowiki>...</nowiki>|
CREATE TABLE dbsqware.tsqw_AlertFile  (
</nowiki>|
<pre>
<pre>
CREATE TABLE dbsqware.tsqw_AlertFile  (
Field Type
db2_sid varchar(80) not null,
dbalias varchar(80)
gather_date date not null,
gather_date date
alert_file  varchar(800)
table_schema varchar(64)
)
size_sch decimal(12,2)
;
data_length decimal(12,2)
alter table dbsqware.tsqw_AlertFile add constraint isqw_AlertFile_pk primary key (db2_sid);
index_length decimal(12,2)
 
grant select on dbsqware.tsqw_AlertFile to DBSQWARE_RLEC;
grant all on dbsqware.tsqw_AlertFile to DBSQWARE_RMAJ;
 
/* for licence infos */
</pre>
</pre>
}}
}}


{{SQWareRepositoryTables|dbsqware.tsqw_GenInfosLicense
{{SQWareRepositoryTables|tsqwdb2_VolTables|
|<nowiki>
<nowiki>...</nowiki>|
create table dbsqware.tsqw_GenInfosLicense (
</nowiki>|
<pre>
<pre>
create table dbsqware.tsqw_GenInfosLicense (
Field Type
db2_sid varchar(80) not null,
dbalias varchar(80)
gather_date date not null,
gather_date date
host_name varchar(64) not null,
table_schema varchar(64)
cpu_count integer,
table_name varchar(64)
port_string varchar(255),
data_length decimal(12,2)
banner varchar(255),
index_length decimal(12,2)
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 */
</pre>
</pre>
}}
}}


{{SQWareRepositoryTables|dbsqware.tsqw_RepositoryVersion
{{SQWareRepositoryTables|tsqwdb2_VolTbs|
|<nowiki>
<nowiki>...</nowiki>|
create table dbsqware.tsqw_RepositoryVersion (
</nowiki>|
<pre>
<pre>
create table dbsqware.tsqw_RepositoryVersion (
Field Type
        upd_date timestamp not null,
dbalias varchar(80)
        action varchar(32) not null,
tablespace_name varchar(80)
        version  varchar(17) not null,
gather_date date
        comments varchar(255)
size_tbs decimal(12,2)
)
used decimal(12,2)
;
free decimal(12,2)
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 */
</pre>
}}
 
{{SQWareRepositoryTables|dbsqware.tsqw_RepositoryExclude
|<nowiki>
create table dbsqware.tsqw_RepositoryExclude (
</nowiki>|
<pre>
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 */
</pre>
}}
 
{{SQWareRepositoryTables|dbsqware.tsqw_Parameters
|<nowiki>
create table dbsqware.tsqw_Parameters (
</nowiki>|
<pre>
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 */
</pre>
}}
 
{{SQWareRepositoryTables|dbsqware.tsqw_EndLife
|<nowiki>
create table dbsqware.tsqw_EndLife  (
</nowiki>|
<pre>
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 */
</pre>
}}
 
{{SQWareRepositoryTables|dbsqware.tsqw_VolTbs
|<nowiki>
create table dbsqware.tsqw_VolTbs (
</nowiki>|
<pre>
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 */
</pre>
}}
 
{{SQWareRepositoryTables|dbsqware.tsqw_ParametersDb
|<nowiki>
create table dbsqware.tsqw_ParametersDb (
</nowiki>|
<pre>
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 */
</pre>
}}
 
{{SQWareRepositoryTables|
|<nowiki>
  dbaname      varchar(50)    not null,
</nowiki>|
<pre>
  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;
</pre>
</pre>
}}
}}

Latest revision as of 19:08, 8 August 2015

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


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.


Specific database content for DB2

tsqwdb2_Parameters

This table contains : ...
Desc of tsqwdb2_Parameters :

Field Type
dbalias varchar(80)
gather_date date
name varchar(255)
value varchar(255)


tsqwdb2_VolDb

This table contains : ...
Desc of tsqwdb2_VolDb :

Field Type
dbalias varchar(80)
gather_date date
size_db decimal(12,2)
max_size_db decimal(12,2)


tsqwdb2_VolSchema

This table contains : ...
Desc of tsqwdb2_VolSchema :

Field Type
dbalias varchar(80)
gather_date date
table_schema varchar(64)
size_sch decimal(12,2)
data_length decimal(12,2)
index_length decimal(12,2)


tsqwdb2_VolTables

This table contains : ...
Desc of tsqwdb2_VolTables :

Field Type
dbalias varchar(80)
gather_date date
table_schema varchar(64)
table_name varchar(64)
data_length decimal(12,2)
index_length decimal(12,2)


tsqwdb2_VolTbs

This table contains : ...
Desc of tsqwdb2_VolTbs :

Field Type
dbalias varchar(80)
tablespace_name varchar(80)
gather_date date
size_tbs decimal(12,2)
used decimal(12,2)
free decimal(12,2)
Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareRepository:db2&oldid=7262"