SQWareRepository:sybase: Difference between revisions

From Wiki_dbSQWare
Jump to navigation Jump to search
← Older edit
No edit summary
No edit summary
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
{{SQWareRepositoryPresSgbd|Sybase|database|dataservers|syb}}
{{SQWareRepositoryPresSgbd|Sybase|database|dataservers|syb}}
== Database content ==
== Specific database content for Sybase ==
{{SQWareRepositoryTables|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|tsqw_RepositoryOther|
<nowiki>Repository other (passive node for cluster for example)</nowiki>|
<pre>
/* Repository other (passive node for cluster for example) */
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|tsqwsyb_GenInfos|
}}
<nowiki>...</nowiki>|
 
{{SQWareRepositoryTables|tsqw_JobsExec|
<nowiki>Jobs monitoring</nowiki>|
<pre>
<pre>
/* Jobs monitoring */
Field Type
create table tsqw_JobsExec (
gather_date datetime
DataServer      varchar(30) not null,
dbalias varchar(80)
Hostname        varchar(30) not null,
size_ds decimal(12,2)
Beginning    datetime not null,
used decimal(12,2)
Script          varchar(80) not null,
version varchar(35)
Parameters      varchar(400) not null,
unix varchar(20)
End_trt datetime null,
memory int(11)
Duration char(8) null,
nblogins int(11)
Status          smallint null)
nbdb int(11)
lock datarows
nbengines int(11)
go
nbmaxdevices int(11)
 
nbdevices int(11)
create unique clustered INDEX isqw_JobsExec_u ON tsqw_JobsExec(DataServer, Hostname, Beginning)
nbmaxlocks int(11)
go
nbmaxconnections int(11)
 
unixversion varchar(250)
grant select on tsqw_JobsExec to public
go
grant all on tsqw_JobsExec to indsyb_maj
go
 
</pre>
</pre>
}}
}}


{{SQWareRepositoryTables|tsqw_RepositoryDb|
{{SQWareRepositoryTables|tsqwsyb_Parameters|
<nowiki>Repository of databases</nowiki>|
<nowiki>...</nowiki>|
<pre>
<pre>
/* Repository of databases */
Field Type
create table tsqw_RepositoryDb  (
dbalias varchar(80)
  DataServer            varchar(30) not null,
gather_date datetime
  Database_name        varchar(30) not null,
name varchar(255)
  Comments              varchar(255),
value varchar(255)
  Contact              varchar(100),
isdefault varchar(9)
  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>
</pre>
}}
}}


{{SQWareRepositoryTables|tsqw_VolDb|
{{SQWareRepositoryTables|tsqwsyb_VolDb|
<nowiki>Volumetry of databases</nowiki>|
<nowiki>...</nowiki>|
<pre>
<pre>
/* Volumetry of databases */
Field Type
create table tsqw_VolDb (
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)
Size_db int,
used decimal(12,2)
Used int,
sizelog decimal(12,2)
SizeLog int,
owner varchar(30)
Owner varchar(30),
options varchar(4000)
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>
</pre>
}}
}}


{{SQWareRepositoryTables|tsqw_VolDbThreshold|
{{SQWareRepositoryTables|tsqwsyb_VolTables|
<nowiki>Specifics threshold for databases</nowiki>|
<nowiki>...</nowiki>|
<pre>
<pre>
/* Specifics threshold for databases */
Field Type
create table tsqw_VolDbThreshold (
dbalias varchar(80)
DataServer              varchar(30)    not null,
gather_date datetime
Database_name          varchar(255)    not null,
database_name varchar(255)
Threshold_c            int,
owner varchar(255)
Threshold_w            int
tablename varchar(255)
 
rows decimal(12,0)
)
reserved decimal(12,2)
lock datarows
data decimal(12,2)
go
index_size decimal(12,2)
create unique clustered index isqw_VolDbThreshold_u on tsqw_VolDbThreshold(DataServer, Database_name)
unused decimal(12,2)
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|tsqw_VolDbThresholdDef|
<nowiki>Default threshold for databases</nowiki>|
<pre>
/* Default threshold for 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|tsqw_GenInfos|
<nowiki>General information of dataserver</nowiki>|
<pre>
/* General information of 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|tsqw_GenHisto|
<nowiki>Monthly general informations</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|tsqw_VolFS|
<nowiki>Volumetry of FS</nowiki>|
<pre>
/* Volumetry of 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|tsqw_VolFSThreshold|
<nowiki>Specific dynamic threshold of FS</nowiki>|
<pre>
/* Specific dynamic threshold of 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|tsqw_VolFSThresholdDef|
<nowiki>Default dynamic threshold of FS</nowiki>|
<pre>
/* Default dynamic threshold of 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|tsqw_VolBackups|
<nowiki>Monitoring backups</nowiki>|
<pre>
/* Monitoring backups */
create table tsqw_VolBackups (
  DataServer    varchar(30)    not null,
  Hostname      varchar(30)    not null,
  Database_name varchar(30)    not null,
  Beginning smalldatetime  not null,
  Bck_type varchar(50)    not null,
  End_trt      smalldatetime  not null,
  Duration char(8)        not null,
  Size_bck int default 0
)
lock datarows
go
 
create unique clustered index isqw_VolBackups_u on tsqw_VolBackups(DataServer, Hostname, Database_name, Beginning)
with ignore_dup_key
go
 
grant select on tsqw_VolBackups to public
grant all on tsqw_VolBackups to indsyb_maj
go
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_VolBackupsExclude|
<nowiki>Exclude checks backups</nowiki>|
<pre>
/* Exclude checks backups */
create table tsqw_VolBackupsExclude (
  DataServer    varchar(30)    not null,
  DbaName      varchar(50)    not null,
  Comments      varchar(50)    not null,
  Upd_date      smalldatetime  default getdate()
)
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>
}}
 
{{SQWareRepositoryTables|tsqw_VolTables|
<nowiki>Volumetry of tables</nowiki>|
<pre>
/* Volumetry of tables */
create table tsqw_VolTables (
Gather_date smalldatetime  not null,
DataServer varchar(30)    not null,
Database_name varchar(255)   not null,
Owner varchar(50)    not null,
TableName varchar(255)   not null,
Size_tb int
)
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>
}}
 
{{SQWareRepositoryTables|tsqw_AlertFile|
<nowiki>Localisation of error log</nowiki>|
<pre>
/* Positionnement de l'error log */
create table tsqw_AlertFile (
Gather_date smalldatetime  not null,
DataServer varchar(30)    not null,
AlertFile varchar(255)    not null
)
lock datarows
go
 
create unique clustered index isqw_AlertFile_u on tsqw_AlertFile(DataServer, Gather_date)
with ignore_dup_key
go
 
grant select on tsqw_AlertFile to public
grant all on tsqw_AlertFile to indsyb_maj
go
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_VolBackupsExcludeGlobalDb|
<nowiki>Global exclusion of databases for check backup db</nowiki>|
<pre>
/* Global exclusion of databases for check backup db */
create table tsqw_VolBackupsExcludeGlobalDb (
  Database_name varchar(30)    not null,
  DbaName      varchar(50)    not null,
  Comments      varchar(50)    not null,
  Upd_date      smalldatetime  default getdate()
)
lock datarows
go
 
create unique clustered index isqw_VolBackupsExcludeGlobalDb_u on tsqw_VolBackupsExcludeGlobalDb(Database_name)
with ignore_dup_key
go
 
grant select on tsqw_VolBackupsExcludeGlobalDb to public
grant all on tsqw_VolBackupsExcludeGlobalDb to indsyb_maj
go
 
insert into tsqw_VolBackupsExcludeGlobalDb values ('dbccdb','MP','Init edt',getdate())
insert into tsqw_VolBackupsExcludeGlobalDb values ('sybsecurity','MP','Init edt',getdate())
insert into tsqw_VolBackupsExcludeGlobalDb values ('sybsystemdb','MP','Init edt',getdate())
insert into tsqw_VolBackupsExcludeGlobalDb values ('model','MP','Init edt',getdate())
insert into tsqw_VolBackupsExcludeGlobalDb values ('audsa','MP','Init edt',getdate())
insert into tsqw_VolBackupsExcludeGlobalDb values ('tempdb%','MP','Init edt',getdate())
go
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_VolBackupsExcludeDb|
<nowiki>Specific exclusion of databases for check backup db</nowiki>|
<pre>
/* Specific exclusion of databases for check backup db */
create table tsqw_VolBackupsExcludeDb (
  DataServer    varchar(30)    not null,
  Database_name varchar(30)     not null,
  DbaName      varchar(50)    not null,
  Comments      varchar(50)     not null,
  Upd_date      smalldatetime  default getdate()
)
lock datarows
go
 
create unique clustered index isqw_VolBackupsExcludeDb_u on tsqw_VolBackupsExcludeDb(DataServer,Database_name)
with ignore_dup_key
go
 
grant select on tsqw_VolBackupsExcludeDb to public
grant all on tsqw_VolBackupsExcludeDb to indsyb_maj
go
</pre>
</pre>
}}
}}

Latest revision as of 19:17, 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 tsqwsyb_% or isqwsyb_% are specific for Sybase.
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 : ...
Desc of tsqwsyb_GenInfos :

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 : ...
Desc of tsqwsyb_Parameters :

Field Type
dbalias varchar(80)
gather_date datetime
name varchar(255)
value varchar(255)
isdefault varchar(9)


tsqwsyb_VolDb

This table contains : ...
Desc of tsqwsyb_VolDb :

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 : ...
Desc of tsqwsyb_VolTables :

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)
Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareRepository:sybase&oldid=7269"