Difference between revisions of "SQWareRepository:db2"

From Wiki_dbSQWare
Jump to: navigation, search
(→‎Database content)
 
(2 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
{{SQWareRepositoryPresSgbd|DB2|database|instances}}
+
{{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


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.


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"