SQWareRepository:sybase

From Wiki_dbSQWare
Revision as of 23:39, 19 July 2010 by Exploit (talk | contribs) (Updated by Expdbtools)
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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 tsqw{{{4}}}_% or isqw{{{4}}}_% are specific for Sybase.
Installation guide for SQWareRepository for Sybase

Naming convention

All specific objects for Sybase are named with prefixes:

  • tsqw{{{4}}}_% : for tables
  • isqw{{{4}}}_% : for index
  • isqw{{{4}}}_%_u : for uniques
  • isqw{{{4}}}_%_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.


Contenu de la database

tsqw_Repository

This table contains : Repository
Desc of tsqw_Repository :

/* 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


tsqw_RepositoryOther

This table contains : Repository autre (noeud passif de cluster par ex)
Desc of tsqw_RepositoryOther :

/* 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


tsqw_JobsExec

This table contains : Jobs monitoring
Desc of tsqw_JobsExec :

/* 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


tsqw_RepositoryDb

This table contains : Repository of databases
Desc of tsqw_RepositoryDb :

/* 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


tsqw_VolDb

This table contains : Volumétrie des databases
Desc of tsqw_VolDb :

/* 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


tsqw_VolDbThreshold

This table contains : Définition des seuils dynamiques spécifiques pour les databases
Desc of tsqw_VolDbThreshold :

/* 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


tsqw_VolDbThresholdDef

This table contains : Définition des seuils dynamiques par défaut pour les databases
Desc of tsqw_VolDbThresholdDef :

/* 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


tsqw_GenInfos

This table contains : Infos générales sur le dataserver
Desc of tsqw_GenInfos :

/* 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


tsqw_GenHisto

This table contains : historique mensuel d'informations générales
Desc of tsqw_GenHisto :

/* 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


tsqw_VolFS

This table contains : Volumétrie des FS
Desc of tsqw_VolFS :

/* 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


tsqw_VolFSThreshold

This table contains : Définition des seuils dynamiques spécifiques pour les FS
Desc of tsqw_VolFSThreshold :

/* 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


tsqw_VolFSThresholdDef

This table contains : Définition des seuils dynamiques par défaut pour les FS²
Desc of tsqw_VolFSThresholdDef :

/* 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


tsqw_VolBackups

This table contains : Suivi des backups
Desc of tsqw_VolBackups :

/* Suivi des 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


tsqw_VolBackupsExclude

This table contains : Exclusion pour les checks de backups
Desc of tsqw_VolBackupsExclude :

/* Exclusion pour les checks de 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


tsqw_VolTables

This table contains : Suivi des tables
Desc of tsqw_VolTables :

/* Suivi des 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


tsqw_AlertFile

This table contains : Positionnement de l'error log
Desc of tsqw_AlertFile :

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