SQWareRepository:sybase

From Wiki_dbSQWare
Revision as of 16:02, 8 August 2015 by Expdbtools (talk | contribs)
Jump to: navigation, search

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.


Database content

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 other (passive node for cluster for example)
Desc of tsqw_RepositoryOther :

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


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 : Volumetry of databases
Desc of tsqw_VolDb :

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


tsqw_VolDbThreshold

This table contains : Specifics threshold for databases
Desc of tsqw_VolDbThreshold :

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


tsqw_VolDbThresholdDef

This table contains : Default threshold for databases
Desc of tsqw_VolDbThresholdDef :

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


tsqw_GenInfos

This table contains : General information of dataserver
Desc of tsqw_GenInfos :

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


tsqw_GenHisto

This table contains : Monthly general informations
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 : Volumetry of FS
Desc of tsqw_VolFS :

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


tsqw_VolFSThreshold

This table contains : Specific dynamic threshold of FS
Desc of tsqw_VolFSThreshold :

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


tsqw_VolFSThresholdDef

This table contains : Default dynamic threshold of FS
Desc of tsqw_VolFSThresholdDef :

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


tsqw_VolBackups

This table contains : Monitoring backups
Desc of tsqw_VolBackups :

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


tsqw_VolBackupsExclude

This table contains : Exclude checks backups
Desc of tsqw_VolBackupsExclude :

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


tsqw_VolTables

This table contains : Volumetry of tables
Desc of tsqw_VolTables :

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


tsqw_AlertFile

This table contains : Localisation of 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


tsqw_VolBackupsExcludeGlobalDb

This table contains : Global exclusion of databases for check backup db
Desc of tsqw_VolBackupsExcludeGlobalDb :

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


tsqw_VolBackupsExcludeDb

This table contains : Specific exclusion of databases for check backup db
Desc of tsqw_VolBackupsExcludeDb :

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