(5 intermediate revisions by 2 users not shown) Line 1:
Line 1: {{SQWareRepositoryPresSgbd|Sybase|d'une database|dataservers}}
{{SQWareRepositoryPresSgbd|Sybase|database|dataservers|syb }}
== Contenu de la database ==
== Specific database content for Sybase ==
{{SQWareRepositoryTables|create table tsqw_Repository|
<nowiki>Repository</nowiki>|
<pre>
/* Repository */
create table tsqw_Repository (
DataServer varchar(30) not null,
Virt_Hostname varchar(64) not null,
Hostname varchar(64) not null,
Username varchar(60) not null,
Port integer not null,
Comments varchar(255),
Contact varchar(100),
Status varchar(10) default 'ON',
Client varchar(60) default 'N.A',
Upd_date smalldatetime default getdate(),
Env char(3)
)
lock datarows
go
create unique clustered index isqw_Repository_u on tsqw_Repository(DataServer)
with ignore_dup_key
go
grant select on tsqw_Repository to public
grant all on tsqw_Repository to indsyb_maj
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_RepositoryOther|
<nowiki>Repository autre (noeud passif de cluster par ex)</nowiki>|
<pre>
/* Repository autre (noeud passif de cluster par ex) */
create table tsqw_RepositoryOther (
DataServer varchar(30) not null,
Virt_Hostname varchar(64) not null,
Hostname varchar(64) not null,
Username varchar(60) not null,
Port integer not null,
Comments varchar(255),
Contact varchar(100),
Status varchar(10) default 'ON',
Client varchar(60) default 'N.A',
Upd_date smalldatetime default getdate(),
Env char(3)
)
lock datarows
go
create unique clustered index isqw_RepositoryOther_u on tsqw_RepositoryOther (DataServer)
with ignore_dup_key
go
grant select on tsqw_RepositoryOther to public
grant all on tsqw_RepositoryOther to indsyb_maj
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_JobsExec|
<nowiki>Jobs monitoring</nowiki>|
<pre>
/* Jobs monitoring */
create table tsqw_JobsExec (
DataServer varchar(30) not null,
Hostname varchar(30) not null,
Beginning datetime not null,
Script varchar(80) not null,
Parameters varchar(400) not null,
End_trt datetime null,
Duration char(8) null,
Status smallint null)
lock datarows
go
create unique clustered INDEX isqw_JobsExec_u ON tsqw_JobsExec(DataServer, Hostname, Beginning)
go
grant select on tsqw_JobsExec to public
go
grant all on tsqw_JobsExec to indsyb_maj
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_RepositoryDb|
<nowiki>Repository of databases</nowiki>|
<pre>
/* Repository of databases */
create table tsqw_RepositoryDb (
DataServer varchar(30) not null,
Database_name varchar(30) not null,
Comments varchar(255),
Contact varchar(100),
Upd_date smalldatetime default getdate()
)
lock datarows
go
create unique clustered index isqw_RepositoryDb_u on tsqw_RepositoryDb(DataServer)
with ignore_dup_key
go
grant select on tsqw_RepositoryDb to public
grant all on tsqw_RepositoryDb to indsyb_maj
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_VolDb|
<nowiki>Volumétrie des databases</nowiki>|
<pre>
/* Volumétrie des databases */
create table tsqw_VolDb (
Gather_date smalldatetime not null,
DataServer varchar(30) not null,
Database_name varchar(255) not null,
Size_db int,
Used int,
SizeLog int,
Owner varchar(30),
Options varchar(100)
)
lock datarows
go
create unique clustered index isqw_VolDb_u on tsqw_VolDb(DataServer, Database_name, Gather_date)
with ignore_dup_key
go
grant select on tsqw_VolDb to public
grant all on tsqw_VolDb to indsyb_maj
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_VolDbThreshold|
<nowiki>Définition des seuils dynamiques spécifiques pour les databases</nowiki>|
<pre>
/* Définition des seuils dynamiques spécifiques pour les databases */
create table tsqw_VolDbThreshold (
DataServer varchar(30) not null,
Database_name varchar(255) not null,
Threshold_c int,
Threshold_w int
)
lock datarows
go
create unique clustered index isqw_VolDbThreshold_u on tsqw_VolDbThreshold(DataServer, Database_name)
with ignore_dup_key
go
grant select on tsqw_VolDbThreshold to public
grant all on tsqw_VolDbThreshold to indsyb_maj
go
insert into tsqw_VolDbThreshold (DataServer,Database_name,Threshold_c,Threshold_w) values ('%', 'sybsystemprocs', 95, 90)
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_VolDbThresholdDef|
<nowiki>Définition des seuils dynamiques par défaut pour les databases</nowiki>|
<pre>
/* Définition des seuils dynamiques par défaut pour les databases */
create table tsqw_VolDbThresholdDef (
Size_db int,
Threshold_c int,
Threshold_w int
)
lock datarows
go
create unique clustered index isqw_VolDbThresholdDef_u on tsqw_VolDbThresholdDef(Size_db)
with ignore_dup_key
go
grant select on tsqw_VolDbThresholdDef to public
grant all on tsqw_VolDbThresholdDef to indsyb_maj
go
insert into tsqw_VolDbThresholdDef (Size_db,Threshold_c,Threshold_w) values (2048, 90, 80)
insert into tsqw_VolDbThresholdDef (Size_db,Threshold_c,Threshold_w) values (20480, 92, 85)
insert into tsqw_VolDbThresholdDef (Size_db,Threshold_c,Threshold_w) values (51200, 95, 90)
insert into tsqw_VolDbThresholdDef (Size_db,Threshold_c,Threshold_w) values (102400, 96, 94)
insert into tsqw_VolDbThresholdDef (Size_db,Threshold_c,Threshold_w) values (102400000, 98, 96)
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_GenInfos|
<nowiki>Infos générales sur le dataserver</nowiki>|
<pre>
/* Infos générales sur le dataserver */
create table tsqw_GenInfos (
Gather_date smalldatetime not null,
DataServer varchar(30) not null,
Size_ds int,
Used int,
Version varchar(35),
Unix varchar(20),
Memory int,
NbLogins int,
NbDb int,
NbEngines int,
NbMaxDevices int,
NbDevices int,
NbMaxLocks int,
NbMaxConnections int,
UnixVersion varchar(50)
)
lock datarows
go
create unique clustered index isqw_GenInfos_u on tsqw_GenInfos(DataServer, Gather_date)
with ignore_dup_key
go
grant select on tsqw_GenInfos to public
grant all on tsqw_GenInfos to indsyb_maj
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_GenHisto|
<nowiki>historique mensuel d'informations générales</nowiki>|
<pre>
/* historique mensuel d'informations générales */
create table tsqw_GenHisto (
Gather_date smalldatetime not null,
Month smalldatetime not null,
DataServer varchar(30) not null,
Size_ds int,
Used int,
Version varchar(35),
Maj_version varchar(5),
Client varchar(60) default 'N.A',
Env char(3)
)
lock datarows
go
create unique clustered index isqw_GenHisto_u on tsqw_GenHisto(DataServer, Month)
with ignore_dup_key
go
grant select on tsqw_GenHisto to public
grant all on tsqw_GenHisto to indsyb_maj
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_VolFS|
<nowiki>Volumétrie des FS</nowiki>|
<pre>
/* Volumétrie des FS */
create table tsqw_VolFS (
DataServer varchar(30) not null,
Gather_date smalldatetime not null,
FS varchar(80) not null,
Size_fs int,
Used int,
Free int,
Rate int,
Mount varchar(80)
)
lock datarows
go
create unique clustered index isqw_VolFS_u on tsqw_VolFS(DataServer, Mount, Gather_date)
with ignore_dup_key
go
grant select on tsqw_VolFS to public
grant all on tsqw_VolFS to indsyb_maj
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_VolFSThreshold|
<nowiki>Définition des seuils dynamiques spécifiques pour les FS</nowiki>|
<pre>
/* Définition des seuils dynamiques spécifiques pour les FS */
create table tsqw_VolFSThreshold (
DataServer varchar(30) not null,
Mount varchar(80) not null,
Threshold_c int,
Threshold_w int
)
lock datarows
go
create unique clustered index isqw_VolFSThreshold_u on tsqw_VolFSThreshold(DataServer, Mount)
with ignore_dup_key
go
grant select on tsqw_VolFSThreshold to public
grant all on tsqw_VolFSThreshold to indsyb_maj
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_VolFSThresholdDef|
<nowiki>Définition des seuils dynamiques par défaut pour les FS²</nowiki>|
<pre>
/* Définition des seuils dynamiques par défaut pour les FS */²
create table tsqw_VolFSThresholdDef (
Size_fs int,
Threshold_c int,
Threshold_w int
)
lock datarows
go
create unique clustered index isqw_VolFSThresholdDef_u on tsqw_VolFSThresholdDef(Size_fs)
with ignore_dup_key
go
grant select on tsqw_VolFSThresholdDef to public
grant all on tsqw_VolFSThresholdDef to indsyb_maj
go
insert into tsqw_VolFSThresholdDef (Size_fs,Threshold_c,Threshold_w) values (2048, 90, 80)
insert into tsqw_VolFSThresholdDef (Size_fs,Threshold_c,Threshold_w) values (10240, 92, 85)
insert into tsqw_VolFSThresholdDef (Size_fs,Threshold_c,Threshold_w) values (20480, 95, 90)
insert into tsqw_VolFSThresholdDef (Size_fs,Threshold_c,Threshold_w) values (102400000, 99, 97)
go
</pre>
}}
{{SQWareRepositoryTables|create table tsqw_VolBackups|
{{SQWareRepositoryTables|tsqwsyb_GenInfos |
<nowiki>Suivi des backups</nowiki>|
<nowiki>... </nowiki>|
<pre>
<pre>
/* Suivi des backups */
Field Type
create table tsqw_VolBackups (
gather_date datetime
DataServer varchar(30) not null,
dbalias varchar(80 )
Hostname varchar(30) not null,
size_ds decimal(12 ,2)
Database_name varchar(30) not null,
used decimal (12,2 )
Beginning smalldatetime not null,
version varchar(35 )
Bck_type varchar(50) not null,
unix varchar(20 )
End_trt smalldatetime not null,
memory int(11)
Duration char(8) not null,
nblogins int (11 )
Size_bck int default 0
nbdb int(11)
)
nbengines int(11 )
lock datarows
nbmaxdevices int(11)
go
nbdevices int(11)
nbmaxlocks int(11)
create unique clustered index isqw_VolBackups_u on tsqw_VolBackups(DataServer, Hostname, Database_name, Beginning)
nbmaxconnections int (11 )
with ignore_dup_key
unixversion varchar(250)
go
grant select on tsqw_VolBackups to public
grant all on tsqw_VolBackups to indsyb_maj
go
</pre>
</pre>
}}
}}
{{SQWareRepositoryTables|create table tsqw_VolBackupsExclude|
{{SQWareRepositoryTables|tsqwsyb_Parameters |
<nowiki>Exclusion pour les checks de backups</nowiki>|
<nowiki>... </nowiki>|
<pre>
<pre>
/* Exclusion pour les checks de backups */
Field Type
create table tsqw_VolBackupsExclude (
dbalias varchar(80 )
DataServer varchar(30) not null,
gather_date datetime
DbaName varchar(50) not null,
name varchar(255 )
Comments varchar(50) not null,
value varchar(255 )
Upd_date smalldatetime default getdate()
isdefault varchar (9 )
)
lock datarows
go
create unique clustered index isqw_VolBackupsExclude_u on tsqw_VolBackupsExclude(DataServer)
with ignore_dup_key
go
grant select on tsqw_VolBackupsExclude to public
grant all on tsqw_VolBackupsExclude to indsyb_maj
go
</pre>
</pre>
}}
}}
{{SQWareRepositoryTables|create table tsqw_VolTables|
{{SQWareRepositoryTables|tsqwsyb_VolDb |
<nowiki>Suivi des tables</nowiki>|
<nowiki>... </nowiki>|
<pre>
<pre>
/* Suivi des tables */
Field Type
create table tsqw_VolTables (
gather_date datetime
Gather_date smalldatetime not null,
dbalias varchar(80 )
DataServer varchar(30) not null,
database_name varchar(255)
Database_name varchar(255) not null,
size_db decimal (12,2 )
Owner varchar(50) not null,
used decimal (12 ,2 )
TableName varchar(255) not null,
sizelog decimal (12 ,2 )
Size_tb int
owner varchar(30)
)
options varchar(4000)
lock datarows
go
create unique clustered index isqw_VolTables_u on tsqw_VolTables(DataServer, Gather_date, Database_name, Owner, TableName)
with ignore_dup_key
go
grant select on tsqw_VolTables to public
grant all on tsqw_VolTables to indsyb_maj
go
</pre>
</pre>
}}
}}
{{SQWareRepositoryTables|create table tsqw_AlertFile|
{{SQWareRepositoryTables|tsqwsyb_VolTables |
<nowiki>Positionnement de l'error log</nowiki>|
<nowiki>... </nowiki>|
<pre>
<pre>
/* Positionnement de l'error log */
Field Type
create table tsqw_AlertFile (
dbalias varchar (80)
Gather_date smalldatetime not null,
gather_date datetime
DataServer varchar(30) not null,
database_name varchar(255 )
AlertFile varchar(255) not null
owner varchar(255)
)
tablename varchar(255 )
lock datarows
rows decimal(12,0)
go
reserved decimal(12,2)
data decimal(12,2)
create unique clustered index isqw_AlertFile_u on tsqw_AlertFile(DataServer, Gather_date)
index_size decimal (12 ,2 )
with ignore_dup_key
unused decimal(12,2)
go
grant select on tsqw_AlertFile to public
grant all on tsqw_AlertFile to indsyb_maj
go
</pre>
</pre>
}}
}}
Presentation It is the repository module, and stores rdbms indicators for all RDBMS. Installation guide for SQWareRepository for Sybase
Naming convention All specific objects for Sybase are named with prefixes:
tsqwsyb_% : for tables
isqwsyb_% : for index
isqwsyb_%_u : for uniques
isqwsyb_%_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 Sybase tsqwsyb_GenInfos This table contains :
...
Field Type
gather_date datetime
dbalias varchar(80)
size_ds decimal(12,2)
used decimal(12,2)
version varchar(35)
unix varchar(20)
memory int(11)
nblogins int(11)
nbdb int(11)
nbengines int(11)
nbmaxdevices int(11)
nbdevices int(11)
nbmaxlocks int(11)
nbmaxconnections int(11)
unixversion varchar(250)
tsqwsyb_Parameters This table contains :
...
Field Type
dbalias varchar(80)
gather_date datetime
name varchar(255)
value varchar(255)
isdefault varchar(9)
tsqwsyb_VolDb This table contains :
...
Field Type
gather_date datetime
dbalias varchar(80)
database_name varchar(255)
size_db decimal(12,2)
used decimal(12,2)
sizelog decimal(12,2)
owner varchar(30)
options varchar(4000)
tsqwsyb_VolTables This table contains :
...
Field Type
dbalias varchar(80)
gather_date datetime
database_name varchar(255)
owner varchar(255)
tablename varchar(255)
rows decimal(12,0)
reserved decimal(12,2)
data decimal(12,2)
index_size decimal(12,2)
unused decimal(12,2)
