|
|
| 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>
| |
| − | }}
| |
