|
|
| 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>
| |
| − | }}
| |
