Database maintenance script

Database maintenance script This script performs DBCCs, transaction dumps, or database dumps for a specified database. #!/usr/bin/ksh #------------------------------------- # syb_maint # # Sybase database maintenance: perform DBCCs / log backups / db backups # # Parms: database, dump dir, mode (dump | tran_only | dbcc) # # Step 1: DBCCs (dbcc mode only) # Step 2: Backup # # Output is routed to backup.log & dbcc.log #------------------------------------- if test $# -lt 3 then echo " " echo "usage:" echo "------" echo "syb_maint <dbname> <dump path> <mode (tran_only,dump,dbcc)>" echo " " echo " " exit fi if test ! -d $2 ; then echo " " echo " Invalid path: " echo $2 echo " " exit fi if test ! -f /usr2/dumps/scripts/contact.txt ; then echo " contact.txt file not found " exit fi contact=`cat /usr2/dumps/scripts/contact.txt` logfile1=/usr2/dumps/cronlogs/syb_maint/dbcc.log logfile2=/usr2/dumps/cronlogs/syb_maint/backup.log if test -f /tmp/syb_stop ; then echo " ***** db stop detected ***** " >> $logfile1 exit fi echo "=============================" > /dev/null echo $1 > /dev/null echo "=============================" > /dev/null if test "$3" = "dbcc" ; then echo "Running dbcc step ..." > /dev/null eval /apps/sybase/bin/isql -Sserver -I/apps/sybase/interfaces -Ujsmith -Pyankees << finis >> $logfile1 print '***** DBCC $1 **************************************' go use master go sp_dboption $1, "single user", true go use $1 go dbcc checkdb ($1,skip_ncindex) go dbcc checkcatalog go dbcc checkalloc go checkpoint go use master go sp_dboption $1, "single user", false go quit finis # check output if egrep "error|corrupt" $logfile1 | egrep -v "printed|TABLE|Checking" > /dev/null then echo "*** Errors found in DBCC log file." rmail $contact@focal.com << endmsg *** Errors found in DBCC log file . endmsg fi fi echo "Running dump step ..." > /dev/null eval /apps/sybase/bin/isql -Sserver -I/apps/sybase/interfaces -Ujsmith -Pyankees << finis2 >> $logfile2 print '***** DUMP $1 **************************************' go use master go exec sp_syb_maint $1, '$2', '$3' go quit finis2 if grep "error|corrupt" $logfile2 > /dev/null then echo "*** Errors found in backup log file" rmail $contact@mycompany.com << endmsg2 *** Errors found in backup log file . endmsg2 fi echo "Sybase maintenance complete" > /dev/null Dealing with a Corrupted Database    Hardware failures can result in databases that are corrupt and will not open upon restart of the server. In some cases the database is marked suspect, and then cannot be opened. The best way to deal with a database in this state is to nuke it and reload it from a backup. Here's a code snippet which will force the drop to occur, when drop database fails. /* note: X=the dbid of the database (from sysdatabases) */ use master go sp_configure "allow updates",1 go begin tran go update sysdatabases set status = 320 where dbid = X go /* always make sure the status has been changed to 320 */ select dbid, status from sysdatabases where dbid = X go commit tran go sp_configure 'allow updates', 0 go checkpoint go /* recycle the server */ dbcc dbrepair (database_name, dropdb) go /* now, recycle the server and rebuild the database */ Drop an alias, with objects linked to login       sp_configure 'allow updates',1 go begin tran go update sysobjects set loginame = 'jsmith' where loginame = 'rjones' go commit tran go sp_configure 'allow updates',0 go sp_dropalias 'rjones' go Sybase ASE 15 Enhancements Table Structure Enhancements Partitioned tables: Tables can be partitioned by range, value, or round-robin (Semantic Partitioning) What is a partition? A partition is a segment of a table, usually with rows sharing a common value (e.g. period_id); Partitions allow for fast deletion of data simple bulk replacement of data ultra high volume OLTP, via round-robin distribution better query performance through partition elimination (homing in on a single partition) super-large tables (hundreds of gigabytes) that are manageable All ASE 15 tables are partitioned (most tables will have a single partition) Computed columns materialized (stored, calc'd upon datachange) or non-materialized (calc'd upon retrival) computed columns can be indexed (function-based index) Statistics Update statistics is not necessary after index rebuild sp_recompile is not necessary after index rebuild Encrypted columns : meets US Government encryption standards Object names (tables, etc) can be up to 255 characters long Temp table (#temp) improvements, including naming Object level recovery: allows single objects to be recovered from dump files Query engine enhancements Joins / searches now optimized for queries with calculated columns e.g. select account_id from account_header where floor(gic_subindustry/100) = 451020 Joins / searches now optimized for queries with mismatched data types e.g. -- join an integer with a real/float select t1.account_id from account_header t1, account_attribute t2 where t1.acct_id = t2.svalue and t2.cat_cd = 'BC' Hash joins * i/o is dramatically reduced for non-index type queries, dramatically improving performance. e.g. -- join 2 tables, against non-indexed or computed columns select t2.sales_nm, count(1) from account_header t1, sales_historical_data t2 where convert( integer, ( left(convert(varchar(10),account_id),5) ) ) = t2.sales_id ** ASE 15 is up to 20 times faster than ASE 12 for these types of queries Work table optimization, hash-based distinct e.g. select count(distinct round(svalue) ) from account_attribute ** ASE 15 is up to 20 times faster than ASE 12 for these types of queries Database Engines: Status and Settings       Setting and Displaying Database Engines Two configuration parameters control the number of dataserver processes (engines) which are active in the server: "max online engines" and "number of engines at startup". Most shops with 4-cpu servers will set these to 3. If the number of engines at startup is lower than the max online engines, how do you tell the number of engines which are active? There are two ways: 1) select * from master..sysengines 2) at the UNIX prompt, use the ps -ef command to list all running processes. Note the dataserver processes, each is an engine. How to start/stop an engine? -- start engine #4 sp_engine 'online',4 go -- stop engine #4 sp_engine 'offline',4 go Removing the Replication Marker or Transaction Log Marker       When loading a database from a database which was replicated, it is necessary to remove the replication transaction log marker - otherwise a good chunk of the transaction log will go to waste (and may cause log-full situations 1> dbcc gettrunc 2> go secondary trunc page secondary trunc state dbrepstat generation id database id database name ltl version -------------------- --------------------- --------- ------------- ----------- ------------------------------ ----------- 45626 1 167 0 16 database702 400 1> dbcc settrunc(ltm,ignore) 2> go secondary trunc page secondary trunc state dbrepstat generation id database id database name ltl version -------------------- --------------------- --------- ------------- ----------- ------------------------------ ---------- 45626 0 166 0 16 database702 400 Sybase ASE: Managing Identity Columns     Using an identity column is a handy way to have IDs generated automatically - it is widely thought that this type of ID was difficult to control (gaps in IDs for example) .. indeed you can control the sequence via the sp_chgattribute procedure. See below for a complete example.    3> create table zz_test (  4>    rec_id  integer identity not null,  5>    rec_desc  varchar(20) not null  )  insert into zz_test (rec_desc) values ('yellow')  (1 row affected)  1> insert into zz_test (rec_desc) values ('orange')  (1 row affected)  1> insert into zz_test (rec_desc) values ('green')  (1 row affected) select * from zz_test   rec_id      rec_desc   ----------- --------------------             1 yellow             2 orange             3 green    (3 rows affected)  1>  2>  3> truncate table zz_test  1>  2> sp_chgattribute zz_test, "identity_burn_max", 0, "1"  DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.  'identity_burn_max' attribute of object 'zz_test' changed to 1.  (return status = 0)  1>  2> insert into zz_test (rec_desc) values ('blue')  (1 row affected)  1> insert into zz_test (rec_desc) values ('red')  (1 row affected)  1> insert into zz_test (rec_desc) values ('pink')  (1 row affected) select * from zz_test   rec_id      rec_desc   ----------- --------------------             2 blue             3 red             4 pink  (3 rows affected)