SQWareRepository:sybase: Difference between revisions

From Wiki_dbSQWare
Jump to navigation Jump to search
← Older editNewer edit →
No edit summary
Replaced content with '{{SQWareRepositoryPresSgbd|Sybase|database|dataservers|syb}} == Specific database content for Sybase =='
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|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|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|tsqw_VolDb|
<nowiki>Volumetry of databases</nowiki>|
<pre>
/* Volumetry of 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|tsqw_VolDbThreshold|
<nowiki>Specifics threshold for databases</nowiki>|
<pre>
/* Specifics threshold for 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|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>
}}

Revision as of 19:03, 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

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