Transcript - SIR Database Software

SIR Database Management
A Refresher Course
for SIR DataBase Administrators
©Tom Shriver, DataVisor 2001, 2002
SIR Database Management
A Refresher Course
for SIR DataBase Administrators
©Tom Shriver, DataVisor 2001, 2002
SIR Database Management
A Refresher Course
for SIR DataBase Administrators
I.
II.
III.
IV.
V.
VI.
VII.
VIII.
IX.
SIR Files
Master
SIR Menu System
Batch Processing & Scheduled Events
Managing Schema Modifications
Backups
DB Integrity, Corruptions & Recovery
SIR Utilities for Database Management
SIR Software Upgrades & Local Testing
©Tom Shriver, DataVisor 2001
Typical SIR Software Files & Locations
Directories
c:\sir2002
c:\sir2002\help
c:\sir2002\tbbb
c:\sir2002\images
c:\sir2002\test
c:\sir2002\api
Important Files:
Executables:
sirproc.srp
sir.lcn
SIR executables and support files
SIR on-line help (html)
Tool Bar Button Binary (*.bmp picture) files
Picture files (*.bmp)
Example database COMPANY
Host Language application interface support
sir.exe; sirbatch.exe; sirmaster.exe;
sirsql.exe; sirgraph.exe; sirforms.exe;
*.dll files
System Procfile (SYSPROC) & the SIR Environment PQL
Contains Licensing Information
SIR Database Administration ©2001 Tom Shriver, DataVisor
4
SIR Database Files
 Typically
kept in a database specific folder
 A database has 4, 5 or 6 files
 Database Name plus numbered extension
c:\dv\company.sr1
c:\dv\company.sr2
c:\dv\company.sr3
c:\dv\company.sr4
c:\dv\company.sr5
c:\dv\company.sr6


Schema, rectype & variable specs
Schema, textual specs (e.g. labels)
Data
primary index & data
Procfile procedure definitions, PQL
Journal data & schema updates)
Alternate Indexes (SIR2002)
Database name here is COMPANY
SIR database Prefix is c:\dv\
SIR Database Administration ©2001 Tom Shriver, DataVisor
5
Data File (SIR3) Internal Usage

Low Level Storage Unit is a SIR Data Block



Contains B-Tree Index



Two Levels, Max 6 Levels
Data Record Search is b-tree search to data block and sequential
within data block
Contains Data Records



Will be multiples of Op System data block size
Will be large enough to hold 4 of largest database records
Records never span Data Blocks
Data Blocks are split leaving empty space:
the Loading Factor
Data Blocks Cached during use; User can control cache
SIR Database Administration ©2001 Tom Shriver, DataVisor
6
Journal File (SIR5) Internal Usage
 Journal
is a Sequential Binary File
 Contains





copies of Every DB Transaction
Data & Schema; not Procfile
Each Entry Stamped with Update Level
New Records: Record copied to Journal
Modified Rec: Old Rec & New Rec Copied (from-to)
Deleted Rec: Copy of Deleted Rec Journalled
 Journal
 View
Can Grow Much Faster than Database
with ITEMIZE FILE Utility
SIR Database Administration ©2001 Tom Shriver, DataVisor
7
Backup and Archive Files
 Database


& Application files backed up
Separate Device (tape, disk, computer)
Separate Facility (different building)
 Additional


Copies Locally
Depending on resources (available disk space)
Depending on criticality (time to recover from tape)
SIR Database Administration ©2001 Tom Shriver, DataVisor
8
Master:
How it Works
Part 1
SIR
Database
User A
Reads & Writes
User A
A SIR DataBase Allows
Multiple Readers and
a Single Writer
Users B & C
Read Only
User B
User C
Master:
How it Works
Part 2
SIR
Database
A SIR Database Under
Master Appears to Allow
Multiple Readers and Writers
Another SIR
Database
Master is the
Only Writer
(& Also Reads)
MASTER
Master may
Control Several
Databases
Users & Master
Exchange
Messages & Data
User A
User B
User C
Protecting & Controlling DataBase
with Master
 Don’t
allow Users “into” DataBase without
Master
 Once
in DataBase Don’t Allow Access to the Data
Entry System
 Reports



Don’t Need Master
Disconnect Master Before Running Query
Re-Connect After the Query
Can be Automated
SIR Database Administration ©2001 Tom Shriver, DataVisor
11
Master Administration: StartUp
from an OS command file (BAT, COM) on the
Server
 Start


Assign a Master Administration Password
Set Difference File Copy (DFC) Interval
 On
Windows NT set up as a System Service
SIR Database Administration ©2001 Tom Shriver, DataVisor
12
Master Administration: Monitoring Master
 From





Pause Master ( ctl-C )
List Users
( LIST )
Kill User
( KILL )
Copy Difference File ( COPY )
Resume Master ( Enter )
 From



Master Window on Server
DataBase “Settings”, “Master Settings”
Connect / Disconnect
Monitor Users, DFC
Control Users, DFC
SIR Database Administration ©2001 Tom Shriver, DataVisor
13
Master Administration: Orderly Shut-Down
 Interactively



 In

Check Users, Make sure Everyone is Off
Force a Difference File Copy
Use “NoLogons” Exit if there are users
Batch
Use PQL Commands & Functions
o Monitor Users
o Remove Users


DBMS Command EXIT MASTER NOLOGONS
Explicit EXIT MASTER
SIR Database Administration ©2001 Tom Shriver, DataVisor
14
SIR Menu and Toolbar System
The Big Picture
 Covered
Separately at UK SIR Conference 2002
SIR Database Administration ©2001 Tom Shriver, DataVisor
15
Batch Processing

BAT Files Executed from Server

SIRBATCH Execution Statement Specificationss
o
o
o
o
o
Database name (db
Prefix
(p
Master
(mst
Proc to Run
(in
Output File
(out
= dbname )
= ‘folder_spec’)
= ‘master_name’)
= ‘file_name’)
= ‘file_name’)
SIR Database Administration ©2001 Tom Shriver, DataVisor
db directory
16
BAT File Examples
rem Run Daily CathLab Volume Report
cd \\ind1nt99\hbaseadm\cvl
\\ind1nt99\sir2000\sirbatch.exe in=cvl.pql out=cvl.out
- - - - - - - - - - - - - - - - - - - - - - - - - - -
rem Run DB Integrity Check
cd s:\hbaseadm\util
s:\sir2000\sirbatch.exe in=stopmst.pql out=stop.out
s:\sir2000\sirbatch.exe in=verify.pql out=verify.out
s:\sir2000\master.bat
SIR Database Administration ©2001 Tom Shriver, DataVisor
17
Scheduled Events
 Use


a Job Scheduler
Hourly, Daily, Weekly, Monthly, Selected Days
You set Time, Frequency, BAT File name to run
 Possibilities





Nightly Backup Routines
Interfaces
Daily Reports
Integrity Checks
Data Audits
SIR Database Administration ©2001 Tom Shriver, DataVisor
18
Managing the Database Schema
Schema Modifications
 Three



Types of Schema Modifications
Changes to Primary Key
Structural Change to Records
(add/delete variables, change data types)
Cosmetic Changes
(value labels, variable labels, BDI directives)
 Index
Mods Lock Rectype & Require Restrucure
 Record Structure Changes handled Dynamically
 Cosmetic Changes are Immediate
 Master Does not Handle all Changes Equally Well
SIR Database Administration ©2001 Tom Shriver, DataVisor
19
CODEBOOK LEVEL & UPDATE LEVEL
 Code
Book (Schema) Level
Incremented with each Schema Run

See current level with system global CBLEVEL
 Update


Data Update Run
Data records "stamped" with update level of last save
- see with RECLEVEL function in PQL
Schema Run
- see current update level with List Stats Utility
 Max

Level Incremented with each:
Update Levels (pre SIR2002): 32,767
Reset with Export/Import or Reload Option
SIR Database Administration ©2001 Tom Shriver, DataVisor
20
Update Level Usage by System
 Sequences


Identified by Update Level
SIR eventually matches data records to schema level
when it restructures records
 Executable



of Schema Mods Maintained
Procedures & Subroutines
Keep "Header" information
-- Record Types referenced in routine
-- Schema Level of those records
Automatically deleted if Schema mods affect routine
Header Info matched to schema every time used
SIR Database Administration ©2001 Tom Shriver, DataVisor
21
Handling Schema Modifications
 Keep


Schema Mod Frequency Down
Save up modifications & do several at once
Post a schedule for users
 Always
& Immediately Restructure with
UNLOAD / RELOAD
 Not absolutely required, but safe
 Minor problems exacerbated until restructure happens
SIR Database Administration ©2001 Tom Shriver, DataVisor
22
Database Indexes
 Primary

Index kept in the SR3 Data File
Composed of:
CASE ID
(if case structured database)
RECTYPE Number
KEYFIELD Values


Index size is fixed, usually as big as largest key
Data Records stored in this sorted order
 Secondary



Indexes ( SIR2002 ) kept in SR6 File
Keyed by any variables
Options for Unique / Non-Unique Indexes
Can be dropped and added
SIR Database Administration ©2001 Tom Shriver, DataVisor
23
Backups
 Regular

Make sure not ‘incrementals’ on db files
 Copies





to Other Directories on Server
For Quick Access in an Emergency
To Assure Successful System Backups
Minimize Database Downtime
 Journal

System “Tape Backups”
Files
Journal File is the SR5 File
Keep Nightly Copies, About a Weeks’ Worth
Delete Current Journal, Will Restart Automatically
SIR Database Administration ©2001 Tom Shriver, DataVisor
24
Backups:
UNLOAD FILE Utility
 The
SIR Recommended Backup Utility
 Creates
Sequential Database in a Single File
 Additional





Functionality:
Restructures DB Data after Schema Modifications
Releases Empty Data Blocks
Option to Reset Update Level
Can backup multiple databases to single file
RELOAD Procedure restores database in
Restructured form
SIR Database Administration ©2001 Tom Shriver, DataVisor
25
Backups: Database Integrity Checks
 Verify



Turn Master Off
Run VERIFY FILE
Turn Master back On
 Check

File Daily (Every Night)
Verify File Results Every Day
Could be shown to specific users automatically
SIR Database Administration ©2001 Tom Shriver, DataVisor
26
Backups: Verify File Utility & Options
VERIFY FILE
CIRKEY
RCF
CIRDATA
RECKEY
RECDATA
CHECK
COUNT
CCF
PATCH



| list case id values
| list CIR record counts
| list CIR variable values
| list record key field values
| list record variable values
| check all variable values vs. schema
| process a subset of cases
| clear corruption flag
| fix repairable corruptions
Rerun Verify File after PATCH
Listing Options Produce Huge Output
Use CCF with caution. It hides problems.
SIR Database Administration ©2001 Tom Shriver, DataVisor
27
Backups: Verify File Output
Data records on database
Cases on database
Index blocks read
Data blocks read
19614
53
10
618
Average index block loading
Average data block loading
Warning messages
Correctable errors
Non-correctable errors
0.74
0.81
0
0
0
Verification complete
with no errors
SIR Database Administration ©2001 Tom Shriver, DataVisor
28
Corrupt Database Recovery
 Methods

in Order of Preference
VERIFY FILE with PATCH

JOURNAL RESTORE with DB Backup
JOURNAL UPLOAD / DOWNLOAD

SIR FILE DUMP / Batch Data Input

Re-Enter Data

 Disaster
Planning: Create a Document
 Disaster
Practicum: Run a "Fire Drill"


Call Information Services for Backup Files
Recover with Journal Restore (can you recover 3 days)
SIR Database Administration ©2001 Tom Shriver, DataVisor
29
EXPORT Utility
 Creates
a textual set of SIR commands that when
run, re-builds the database



Schema Commands
(like WRITE SCHEMA)
Text Members of the Procfile (like PWRITE)
Export data is in a special text format
 Intended
to Move a Database between SIR
Platforms (different operating systems)
 Frequently
used as an alternate backup method
SIR Database Administration ©2001 Tom Shriver, DataVisor
30
IMPORT Procedure
 Several



Options
Run the Export File
Use Import Option on Database Menu
Batch Run
c:\sir2002\sirbatch.exe in=mydb.exp out=mydb.out
SIR Database Administration ©2001 Tom Shriver, DataVisor
31
SIR FILE DUMP &
Batch Data Input
 SIR

FILE DUMP
Creates formatted files per Schema specifications
o Must maintain Schema column specs
o Variables not assigned columns are not dumped
o Overlapping fields (including keys) may be overwritten


Specify Rectypes to Dump
Boolean, List, Count & Sample for Record Filtering
 Batch


Data Input
Inputs data from files formatted as per Schema
Control with Add / Replace Options
SIR Database Administration ©2001 Tom Shriver, DataVisor
32
SUBSET and MERGE
 SIR



Creates Sequential "Unload" type of file
Options to filter records to create subset
Options to rename records and variables
 SIR


SUBSET
MERGE
Merges SUBSET data into a different database
Not available through menu system
 Potential

Use
Recover specific data records from backups
SIR Database Administration ©2001 Tom Shriver, DataVisor
33
Database Content & Status
Reporting Utilities
SIR Utilities from Commands & Menu System
LIST STATS
SIR SCHEMA LIST
WRITE SCHEMA
ITEMIZE FILE
SIR FILE LIST
VERIFY FILE
Total Records, DB Settings, Rectype Summary Stats
Schema Description; Options for Level of Detail
Schema Command File
Sequential File Description (Unloads, Journals, Subsets)
Data Listed & Labeled
DB Integrity with Space Usage Statistics
Build Your Own Utility with PQL
PQL Functions Schema Functions (records, fields, keys, labels)
Tabfile Functions
System Functions
Environment Functions
Client Server (Master, ODBC, Application)
System Globals CBLEVEL, CPTIME, TERROR, SIRVER, MASTER
SIR Database Administration ©2001 Tom Shriver, DataVisor
34
SIR Software Upgrades &
Local Testing
 Major

Frequent Bug Fix Upgrades Available on Web-Site
 SIR
Welcomes Beta Testers
 When




New SIR Releases Every 18-24 Months
Putting up New Version of SIR
Install SIR in a new directory
Create a test version of your database
Export on Old SIR & Import with New Version
Recompile all Executables and Subroutines
Test Entire Application before Going Live
SIR Database Administration ©2001 Tom Shriver, DataVisor
35