SQWareRepository:db2: Difference between revisions

From Wiki_dbSQWare
Jump to navigation Jump to search
Created page with '{{SQWareRepositoryPresSgbd|DB2|database|instances}} == Database content == {{SQWareRepositoryTables|dbsqware.tsqw_Repository dbsqware.tsqw_RepositoryOther |<nowiki> create table…'
 
No edit summary
 
(3 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|dbsqware.tsqw_Repository
{{SQWareRepositoryTables|tsqwdb2_Parameters|
dbsqware.tsqw_RepositoryOther
<nowiki>...</nowiki>|
|<nowiki>
create table dbsqware.tsqw_Repository (
</nowiki>|
<pre>
<pre>
create table dbsqware.tsqw_Repository (
Field Type
db2_sid varchar(80) not null,
dbalias varchar(80)
virt_host_name varchar (64) not null,
gather_date date
host_name varchar (64) not null,
name varchar(255)
username  varchar (60) not null,
value varchar(255)
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 */
</pre>
}}
 
{{SQWareRepositoryTables|dbsqware.tsqw_JobsExec
|<nowiki>
create table dbsqware.tsqw_JobsExec
</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;
 
/* to load log files in repository */
</pre>
}}
 
{{SQWareRepositoryTables|
|<nowiki>
alter table dbsqware.tsqw_JobsExec ADD COLUMN LOG_FILE CLOB(10M);
</nowiki>|
<pre>
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 */
</pre>
}}
 
{{SQWareRepositoryTables|dbsqware.tsqw_VolFS
|<nowiki>
create table dbsqware.tsqw_VolFS (
</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;
 
/* threshold */
</pre>
</pre>
}}
}}


{{SQWareRepositoryTables|dbsqware.tsqw_VolFSThreshold
{{SQWareRepositoryTables|tsqwdb2_VolDb|
dbsqware.tsqw_VolFSThresholdDef
<nowiki>...</nowiki>|
|<nowiki>
create table dbsqware.tsqw_VolFSThreshold (
</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,
size_db decimal(12,2)
threshold_w integer not null
max_size_db decimal(12,2)
)
;
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_VolSchema|
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,
table_schema varchar(64)
size_db double,
size_sch decimal(12,2)
max_size_db double
data_length decimal(12,2)
)
index_length decimal(12,2)
;
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>
</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"