IBM Software Group Washington Area Informix User Group

IBM Software Group Washington Area Informix User Group Forum 2004 The DB 2 DBA Checklist Dwaine R Snow, dsnow@ca. ibm. com DB 2 & Informix Product Manager

IBM Software Group | DB 2 Information Management Software Agenda • • Why Monitor? Monitoring tools available How to Monitor Daily Activities Weekly Activities Monthly Activities Summary

IBM Software Group | DB 2 Information Management Software Why Monitor? • Ensure no problems exist or are imminent – Detect issues before they cause problems – Watch for trends and handle them accordingly – Detect changes in • Database • Apps • Etc. • Maintain performance

IBM Software Group | DB 2 Information Management Software Monitoring Tools • Health Monitor / Health Center • Snapshots – Table Functions – Snapshot Monitors • • Event Monitors Memory Visualizer Administration Notification Log and DB 2 Diagnostic Log OS Tools – vmstat, iostat, ps, top, sar, etc.

IBM Software Group | DB 2 Information Management Software Monitoring Tools • Typically need to combine DB 2 and OS tools to get the complete picture – DB 2 tools usually do not give the complete picture – Make sure the DB 2 and OS info is captured at the same time • Cannot correlate information captured at different times

IBM Software Group | DB 2 Information Management Software How to Capture Snapshots • When monitoring the “system” – Take the "snapshots" during: • average workload • peak workload • Take the "snapshots" over a long enough period of time – not just over a one to two minute period • this does not give a realistic view • I would suggest taking the "snapshots" at intervals of 2 minutes (120 seconds) for at least 30 intervals. – i. e. vmstat 120 30 > vmstat. out

IBM Software Group | DB 2 Information Management Software Daily Activities • • Ensure the instance is running Ensure all databases are “connectable” and consistent Examine alert log / error logs Verify the backup(s) were successful Verify logs are being archived Verify available resources Look for objects needing REORG / RUNSTATS Study DB 2

IBM Software Group | DB 2 Information Management Software Ensure the Instance is running • Can be done in various ways – db 2 start will indicate if the instance is already running – ps –ef | grep db 2 sysc | grep <instance owner id> • will work on UNIX / Linux – Verify the DB 2 service is running on Windows

IBM Software Group | DB 2 Information Management Software Ensure databases are “connectable” • Can be done if various ways – Connect to each database – List applications – List active databases • For databases with no connections/activations – Get database configuration • Verify the database is consistent • Typically good practice to ACTIVATE all databases – Keeps the buffer pool primed

IBM Software Group | DB 2 Information Management Software Examine Logs • Ensure there are no new alerts – On UNIX/Linux check the Administration Notification Log for new messages – On Windows check the Windows Event Log • Check Diagnostic Log for new messages • Check Application logs, if they exist • Handle any error conditions immediately.

IBM Software Group | DB 2 Information Management Software Verify database backups were successful • list history backup all for <database name> – Repeat for all databases • Make sure backup image is not deleted – Is it on tape? • Make sure backup image is copied offsite • Nothing worse than crashing and having no “good” backup image

IBM Software Group | DB 2 Information Management Software Verify logs are being archived • Examine the userexit log – Make sure no errors have been encountered – Make sure logs were archived recently • Examine log path to ensure logs are being archived and cleaned up • The BLK_LOG_DSK_FUL database config parameter will ensure DB 2 will not come down if the log path becomes full

IBM Software Group | DB 2 Information Management Software Verify Available Resources • Verify free space in table spaces – get snapshot for tablespaces on <dbname> – list tablespaces show detail • Should be no table spaces > 90% full • Check for trends in table space usage – Keep table space info – Examine info from previous day(s) – Look for exceptional growth

IBM Software Group | DB 2 Information Management Software Verify Available Resources • Check for contention for CPU, memory, disk or network resources – vmstat • Sufficient free memory • Run queue small • CPU Usage < 80% – iostat • No disk with tm_acct > 40% • I/O evenly distributed • No iowait – netstat • No errors

IBM Software Group | DB 2 Information Management Software Look for Objects needing REORG/RUNSTATS • Reorgchk – Examine for • Overflows • Non clustered indexes • Ensure current statistics exist on ALL objects – Select stats_time from syscat. tables – Select stats_time from syscat. indexes • Any new object(s) will need statistics

IBM Software Group | DB 2 Information Management Software Study DB 2 • Nothing is more valuable in the long run that the DBA be as widely experienced, and as widely read, as possible. • Readings should include DBA manuals, trade journals/magazines, news groups, mailing lists, web sites

IBM Software Group | DB 2 Information Management Software Weekly Activities • • • Look for new or changed objects, applications Look for objects that break rules Look for security policy violations Archive alert / error logs Check for updates for key DB software

IBM Software Group | DB 2 Information Management Software Look for new objects • • db 2 look – e list tables select tabname from syscat. tables select indname from syscat. indexes • get snapshot for tablespaces • list tablespaces • Verify against output from previous week

IBM Software Group | DB 2 Information Management Software Look for new/changed applications • list applications – Verify against output from previous week – Should see no new application names • get snapshot for dynamic sql – Verify against output from previous week – Should see no new stmts unless there are new applications • For any new applications and/or statements – Ensure proper indexes, current stats

IBM Software Group | DB 2 Information Management Software Look for objects that break rules • For each object-creation policy, i. e. – naming convention – storage parameters – etc. • Have an automated check to verify that the policy is being followed. • For example: – Tables should have indexes – Schemas should be identical between environments • Especially QA / Test and production.

IBM Software Group | DB 2 Information Management Software Look for Security Policy Violations • PUBLIC should NOT have connect privilege to any database • Ensure SYSADM group only assigned to 1 -2 people • Users should only have required privileges • Ensure Backup / Staging / Database directories have appropriate permissions

IBM Software Group | DB 2 Information Management Software Archive Alert / Error logs • ZIP / tar & compress – Alert log – Administration notification log – Diagnostic log – Dump / Trap files • Name the archive file with the date • Do this on server and client

IBM Software Group | DB 2 Information Management Software Check for Software Updates • Visit homepage of key vendors and look for relevant updates – DB 2 Fixpacks – OS Hot fixes – Vendor App fixes • Important to keep current

IBM Software Group | DB 2 Information Management Software Monthly Activities • • • Look for indicators of exceptional growth Project future performance based on above Look for OS issues Review configurations for tuning opportunities

IBM Software Group | DB 2 Information Management Software Look for Growth Indicators • get snapshot for tablespaces on <dbname> • list tablespaces show detail – Keep results – Compare with previous results – Look for table spaces with exceptional growth • Do the same for NPAGES for large tables

IBM Software Group | DB 2 Information Management Software Project Future Growth • Particularly important for DSS / Warehouse • Anticipate how much data will grow • Ensure there is enough space in existing – – Table spaces File systems Ensure staging space is adequate Ensure backup location has sufficient space • Anticipate need for more disk / memory / CPUs to maintain performance

IBM Software Group | DB 2 Information Management Software Project Performance • Based on projected growth – Will there be enough disk space – Will there be enough I/O bandwidth – Will there be enough memory – Will there be enough CPUs • Will new indexes be needed – Or changes to existing indexes – For OLTP, do not simply add indexes

IBM Software Group | DB 2 Information Management Software Look for OS Issues • Use OS tools to check for – Paging – Memory usage – I/O contention – Run Queue – Network usage – File system usage • Ensure no indicators of “poor health”

IBM Software Group | DB 2 Information Management Software Review Configurations • Examine – DBM Configuration – DB Configurations – DB 2 Registry Settings • Look for opportunities for improvement • Use Memory Visualizer to examine DB 2’s memory usage

IBM Software Group | DB 2 Information Management Software Summary • A DBAs job is never done • DB 2 is the BEST performing database in the world • But does require some baby-sitting • The Health Center can automate some of these tasks – But still requires DBA intervention – For now…