Difference between revisions of "SQWareRepository:sybase"

From Wiki_dbSQWare
Jump to: navigation, search
 
(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 18: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


Nuvola apps important.png
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"