WIXLIB

ASE Sybase NotesHandy tips for the busy DBALast updated: 05/04/2018DBXperts LtdGarrett Devinewww.dbxperts.co.uk

Version 1.2Page 205/04/2018Table of ContentsDocument Revision 1.2 . 3Introduction & Disclaimer . 3Mounting & Unmounting databases . 3Archive Databases . 3Create Archive Databases . 4WATCH-OUT . 4Syntax . 4HANDY SQL . 4Search for unique keys . 4Finding a which table contains a given column name . 5How to a get a list of triggers on the table . 5Listing Zombie processes. 5Identifying bottlenecks in Sybase . 5Licensing . 6Using multiple & sa tempdbs . 6Moving or reducing tempdb in Sybase . 7Mixed data and log devices removal . 8Moving disk devices . 9Copy statistics from one database to another . 10Fix Syslogs free space count errors in sybsecurity. 10Fix sp helpdb/sp helpsegment is returning negative numbers . 11Sybase Database ASE Server Startup & Stop script for linux RHEL for run level 3, 5: . 112www.ddsafe.co.uk

Version 1.2Page 305/04/2018Document Revision 1.2Introduction & DisclaimerThe notes contained in this document are intended as a fast find guide to using Sybase Adaptive ServerEnterprise and have been built up over my time using ASE in the real world. It is not intended to be acomplete exploration of all of ASE server’s abilities, nor do I claim that all the notes are without error. Ifyou find errors or would like to submit your own top tip for the next edition of this guide, then please emailus at info@ddsafe.co.ukMounting & Unmounting databasesSyntax:mount database all from manifest file [with listonly]Example:First, make sure mirroring of devices is disabledsp configure ‘disable disk mirroring’goIf not, set to ‘1’ and reboot ASEThe devices must be wholly used by the database in question. The simplest devices are those found onfilesystem, not RAW devices in Unix. To unmount a database to a ‘manifest file’USE mastergounmount database pubs2 to 'c:\sybase 15\pubs2.umount.manfest'gosp helpdb--check DB has disappeared.gomount database all from 'c:\sybase 15\pubs2.umount.manfest'goonline database prim dbgoIf you want to move the devices or database to another location, use the following ‘mount’ syntax.mount database all from 'c:\sybase 15\pubs2.umount.manfest'using 'C:\sybase new devs\pubs2 data01.dat' 'pubs2 data01',.any other devices.goNote:It is good practice to issue a “quiesce database” on the database, prior to unmounting it. This Suspendsand resumes updates to a specified list of databases.Can only mount/unmount if the set of databases that occupy those devices are isolated, self-contained onthose devices.Can unmount several databases at the same time to a single manifest file.Archive DatabasesDatabases must be dumped using the new syntax dump databases dbname to '/path/dbname 1.cmp’stripe on '/path/dbname 2.cmp’with compression ’1’go3www.ddsafe.co.uk

Version 1.2Page 405/04/2018Create Archive DatabasesFirst you need to create a small scratch database which will hold meta data for the archive database. Theonly interesting thing with it is that after you have built it you need to set the dboption scratch database totrue for it. It needs to be quite large. The example below is based on a 220Gb DB.create database scratch db on datadev1 5000log on logdev1 2000goexec sp dboption "scratch db", "scratch database", "true"exec sp dboption "scratch db", "trunc log on chkpt", "true"goWATCH-OUTIf you are loading from a compressed dump then you need to runsp configuration ‘compression memory size’, 64You may also need to increase your procedure cache. The scratch database needs to be around 5GBdata/2GB Log for the recovery of a 220Gb database. Make sure you change the ownership of the databaseto match the database to be restored prior to starting the load. This cannot be altered later and may beimportant for user access. One way is to give the DB owner sa role, create the DB, then revoke the sa role.The progress of the load is not shown in the backuplog. You can only tell if it is working by checking thephysical io from sysprocesses. The ASE errorlog will show you that the dump files have been initialisedas devices when you start the load.SyntaxThe syntax for creating the archive database is (as DB owner):create archive database archive db on datadev1 1000with scratch database scratch dbgoUSE archive dbgoEXEC sp changedbowner 'dbname'goYou can then load the dump into the archive database withLoad database archive db from "/dumps/dbname.dmp"GoOnline database archive dbgoThe database is now ready for browsing and you can also run dbcc checks etc against it.HANDY SQLSearch for unique keys--list tables with no unique indexselect convert(varchar(30), o.name)from sysobjects owhere o.type "U"and o.id not in (select i.idfrom sysobjects o, sysindexes iwhere o.type "U"and o.id i.idand i.status & 2 2 --2 if unique index)--list tables and primary keys4www.ddsafe.co.uk

Version 1.2Page 505/04/2018select convert(varchar(30), object name(i.id)), convert(varchar(30),i.name)from sysobjects o, sysindexes iwhere o.type "U"and o.id i.idand i.status & 2 2Finding a which table contains a given column nameselect o.namefrom sysobjects o, syscolumns cwhere o.id c.idand c.name column name How to a get a list of triggers on the tableselect "table name" name,"insert trigger" convert(varchar, object name(instrig)),"update trigger" convert(varchar, object name(updtrig)),"delete trigger" convert(varchar, object name(deltrig))from sysobjectswhere type "U"--andname @table nameorder by nameListing Zombie processesselect spid from syslocks where spid not in (select spid from sysprocesses)Identifying bottlenecks in SybaseCapture showplan & query stats.set tracefile '/tmp/filename' for SPIDgosetsetsetsetgoshowplan onstatistics time onstatistics plancost onoption show abstract plan onWith the plancost info we should be able to identify any optimiser errors by comparing the estimated rowsand IO with the actual figures.Most likely, there will not be any and it will just be a case of ensuring thehigh IO / time sections of the SQL are using the correct indexes. If they are not (and update all stats doesnot fix it) then we add an index hint to force the issue (or tweak the abstract plan).5www.ddsafe.co.uk

Version 1.2Page 605/04/2018LicensingFrom version 12.5 ASE we need to use SYAM licensing. This is a bit of a pain in the back side. Youcan install 2-3 network licences. These are used by the servers to ‘check-out’ a license. Here we will lookat installing stand alone licences.Download the correct license from the Sybase site.For SBE edition, you need to create these licenses for each server.create these.It uses the mac address of the PC toCheck currently installed licence typesp lmconfig 'edition', 'SE' --Small Business Editionsp lmconfig 'license type', 'DH' -- Development licenseSet the variable SYBASE SAM CPUINFO in both user and system environment variablesSYBASE SAM CPUINFO CPUIDUsing multiple & sa tempdbsBind 'sa' to a tempdb--------------------CREATE TEMPORARY DATABASE tempdbsaON tempdb03 50,tempdb03 50gosp tempdb bind, lg, sa, DB, tempdbsagoExplanation:sp tempdb bind, objtype, objname, bindtype, bindobjobjtype object type (login name (or LG), application name (or AP))bindtype bind type (group (or GR), database (or DB))Testing:To check what you have bound the logins to the databasesp tempdb 'show', 'all'To check if login is using the tempdb, login with new session to serversp tempdb 'who', tempdbsaRound-robin tempdb haveing created multiple tempdb's, add them to the 'default' groupsp tempdb 'add', 'tempdb 2', 'default'gosp tempdb 'add', 'tempdb 3', 'default'gosp tempdb 'add', 'tempdb 4', 'default'go6www.ddsafe.co.uk

Version 1.2Page 705/04/2018Moving or reducing tempdb in Sybase1. restart ASE server in single user mode (using -m switch)2. backup master database and BCP out r.syscharsets3. Do the work!1 use master2 go1 sp configure "allow updates", 12 go1 begin transaction2 go1 select * from sysusages2 where dbid db id('tempdb')3 go--Set the first 2MB of tempdb back to data and log in case they were separated:1 update sysusages2 set segmap 7 where dbid db id('tempdb')3 and lstart 04 go--Should look a bit like this.dbidsegmaplstartsizevstartpadunreservedpgs crdate------ ----------- ----------- ----------- ----------- ------ ------------- -------------------------27010248196 NULL423Dec28 2006 3:43PM27102451200033554432 NULL510000Feb12 2007 9:57AM2751302451200050331648 NULL510000Feb12 2007 9:57AM----Delete all other rows belonging to tempdb from sysusages.--The number of rows affected should be one less than the number of rows affected by the previous selectcommand.1 delete sysusages where dbid db id('tempdb')2 and lstart ! 03 go--Verify that tempdb has one entry that looks like this:1 select * from sysusages2 where dbid db -------270102425647www.ddsafe.co.uk

Version 1.2Page 805/04/2018--If you see a problem1 rollback transaction2 go--else1 commit transaction2 go1 sp configure "allow updates", 02 go--immediately issue a checkpoint and shut down Adaptive Server:--You must shut down Adaptive Server before altering the size of tempdb again.--Extend database as usual.Note on removing multiple tempdb'ssp tempdb 'show', 'all'gosp tempdb 'remove', 'tempdb 1', 'default'goThen restart ASE.drop database tempdb 1goMixed data and log devices removal1.dump tran mxgfxo dev 02 db with truncate onlygo2.dump database mxgfxo dev 02 db to'compress::1::/home/sybase/dump01/ACC51 ASE/load/mxgfxo dev 02 db prefix 1.cmp'stripe on 'compress::1::/home/sybase/dump01/ACC51 ASE/load/mxgfxo dev 02 db prefix 2.cmp'stripe on 'compress::1::/home/sybase/dump01/ACC51 ASE/load/mxgfxo dev 02 db prefix 3.cmp'stripe on 'compress::1::/home/sybase/dump01/ACC51 ASE/load/mxgfxo dev 02 db prefix 4.cmp'stripe on 'compress::1::/home/sybase/dump01/ACC51 ASE/load/mxgfxo dev 02 db prefix 5.cmp'stripe on 'compress::1::/home/sybase/dump01/ACC51 ASE/load/mxgfxo dev 02 db prefix 6.cmp'goGet DDL of database using DBAArtisan3. backup systables /bin/bcp systables.ksh ACC51 ASE4. select * from sysusages where dbid db id('mxgfxo dev 02 db')5. Check if at least one of the segmap 4 and is not mixed with any of the data device of this database. Thiscan be varified with the following commandselect a.segmap, b.namefrom sysusages a, sysdevices bwhere dbid db id('mxgfxo dev 02 db')and a.vstart between b.low and b.highgoOtherwise alter database dbname log on new log device size Check sysusages again to makesure segmap 4 for this6. set segmap to 'data' for mixed devicessp configure 'allow updates', 1go8www.ddsafe.co.uk

Version 1.2Page 905/04/2018update sysusages set segmap 3 where dbid 14 and segmap 7gosp configure 'allow updates', 0go7. Shutdown/restart server.8. create a dummy table and insert 1000 rows. This will make new page allocation for syslogs move to thenew logonly device.create table dummy table ( xxx char(255) )goinsert dummy table select "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"go 10009. Dump tran with truncate only.dump tran dbname with truncate onlygo10. Shutdown and restart server and insert 1000 rows to create few logs. insert dummy table select"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"go 100011. dump tran dbname to "device name"godump database mxgfxo dev 02 db to'compress::1::/home/sybase/dump01/ACC51 ASE/load/mxgfxo dev 02 db fixed 1.cmp'stri

Copy statistics from one database to another. 10 Fix Syslogs free space count errors in sybsecurity. 10 Fix sp_helpdb/sp_helpsegment is returning negative numbers . 11 Sybase Database ASE Server Startup & Stop script for linux RHEL for run level 3, 5: . 11. Version 1.2 Page 3 05/04/2018 3 www.ddsafe.co.uk Document Revision 1.2 Introduction & Disclaimer The notes contained in .