SQWareRepository:db2: Difference between revisions

From Wiki_dbSQWare
Jump to navigation Jump to search
← Older editNewer edit →
No edit summary
Replaced content with '{{SQWareRepositoryPresSgbd|DB2|database|instances|db2}} == Specific database content for DB2 =='
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;
grant all on dbsqware.tsqw_VolFS to DBSQWARE_RMAJ;
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_VolFSThreshold
<nowiki>threshold</nowiki>|
<pre>
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 */
</pre>
}}
 
{{SQWareRepositoryTables|dbsqware.tsqw_VolDb
dbsqware.tsqw_VolInstance
dbsqware.tsqw_GenHisto
|<nowiki>
create table dbsqware.tsqw_VolDb (
</nowiki>|
<pre>
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*/
</pre>
}}
 
{{SQWareRepositoryTables|CREATE TABLE dbsqware.tsqw_AlertFile
|<nowiki>
CREATE TABLE dbsqware.tsqw_AlertFile  (
</nowiki>|
<pre>
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 */
</pre>
}}
 
{{SQWareRepositoryTables|dbsqware.tsqw_GenInfosLicense
|<nowiki>
create table dbsqware.tsqw_GenInfosLicense (
</nowiki>|
<pre>
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 */
</pre>
}}
 
{{SQWareRepositoryTables|dbsqware.tsqw_RepositoryVersion
|<nowiki>
create table dbsqware.tsqw_RepositoryVersion (
</nowiki>|
<pre>
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 */
</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>
}}

Revision as of 18:04, 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

Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareRepository:db2&oldid=7256"