SEARCHDATABASE.COM | Database Administrator Tips November 5, 2003 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FROM OUR SPONSOR:
White Paper: How to Survive in a Multi-Database Environment http://searchDatabase.com/r/0,,20801,00.htm?track=NL-94&questsoftware =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: Find the space occupied by all tables and indexes - DB2: Introduction to data manipulation - Oracle: Create a DML script using a view - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Learning Guide: Solving common DB2 errors - Featured Topic: Normalization ___________________________________________________________________ ****************** Sponsored by Quest Software ******************** What applications and technologies do you need to be aware of in order to maintain your competitive edge? To help you administer more than one database simultaneously, Quest Software offers a new white paper: "The Heterogeneous DBA: Surviving in a Multi-database Environment." This white paper reviews common database terminology, discusses how each database handles space management, and offers helpful tips on administrative differences. http://searchDatabase.com/r/0,,20801,00.htm?track=NL-94&questsoftware ******************************************************************* ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FIND THE SPACE OCCUPIED BY ALL TABLES AND INDEXES | P. Mandayam Here's an undocumented method to find the space occupied by all tables and indexes in a SQL Server 2000 database. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci912794,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DATA MANIPULATION IN DB2 UDB | Dwaine Snow and Tom Phan This chapter from the new book "Advanced DBA Certification Guide and Reference for DB2 Universal Database v8" focuses on the creation, management, and monitoring of buffer pools in DB2 UDB Version 8 to provide optimal performance for the database. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci935161,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= CREATE A DML SCRIPT USING A VIEW | Satya Josyula Oracle developers and DBAs often use third-party utilities or complex packages to generate backup scripts for table data. Many of the available methods are limited or overly complex. With this view, you can have your own DML script for any table that does not contain LONG or LOB columns. >> CLICK for the tip... http://www.searchOracle.com/tip/1,289483,sid41_gci933201,00.html?track=NL-94 ___________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: FIND THE SIZE OF EVERY DATABASE | Madhusudan Naidu Gundapaneni http://www.searchDatabase.com/tip/1,289483,sid13_gci931564,00.html?track=NL-94 This handy script will find the size of every database in SQL Server 2000 without using the sp_spaceused function. CLUSTER INDEXES: UNDER THE HOOD | Alexander Kuznetsov http://www.searchDatabase.com/tip/1,289483,sid13_gci934007,00.html?track=NL-94 There is a widespread misconception that data rows are stored on the leaf level of a cluster index. With DB2, this is not true. Why should we care? In some cases, it certainly pays to understand what's going on under the hood. This is one of those cases, so let us discuss cluster indexes in more detail. COMPILE ALL INVALID OBJECTS | Daniel Lanz http://www.searchOracle.com/tip/1,289483,sid41_gci930536,00.html?track=NL-94 Even a simple change to an object in Oracle makes other objects invalid. It is urgent to recompile the objects in the right order because of their dependences of each other. This script will compile all invalid objects in the required order and list all objects that need a manual correction. SCRIPT TO KILL ALL DATABASE PROCESSES | Parthasarathy Mandayam http://www.searchDatabase.com/tip/1,289483,sid13_gci840255,00.html?track=NL-94 Here's a situation you SQL Server DBAs may face: you have to restore production copies of your database daily onto development servers. The problem is that the restore process requires exclusive access to the database. So, the author has come up with this nifty script to silently kill all users connected to the database. INTRODUCTION TO MONITORING DB2 V8 | Philip Gunning http://www.searchDatabase.com/tip/1,289483,sid13_gci931152,00.html?track=NL-94 Regardless of how well a RDBMS performs, the monitoring of SQL statements and resources is required to ensure continued good performance and to identify problem areas. DB2 v8 provides built-in monitoring capability through the use of snapshot monitoring and event monitoring. Here is a detailed introduction. RUN MULTIPLE SESSIONS OF DBV | Michael Vergara http://www.searchOracle.com/tip/1,289483,sid41_gci930274,00.html?track=NL-94 Ever had to do a lot of Oracle DBVERIFY commands? You build a script, or multiple scripts, and when they start running you wish you had more or less of them? Here's a UNIX script that will run a dynamically variable number of concurrent dbv commands. USEFUL SCRIPTS TO CLEAR OUT OLD PLAN HISTORY LOGS | Robert Hauck http://www.searchDatabase.com/tip/1,289483,sid13_gci911715,00.html?track=NL-94 When a plan's history gets long, it helps to be able to clear out any old ones that you don't need to see anymore. Use these scripts rather than deleting them one at a time via Enterprise Manager. LIMITING RESULT SETS WITH FETCH FIRST AND OPTIMIZE FOR by Alexander Kuznetsov http://www.searchDatabase.com/tip/1,289483,sid13_gci932208,00.html?track=NL-94 The FETCH FIRST and OPTIMIZE FOR clauses are best for limiting the amount of rows if the result set is big. Here's how to use them in DB2. Hundreds more free tech tips submitted by members: http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html?track=NL-94 Share your knowledge, submit a tip, win a prize: http://searchdatabase.techtarget.com/tipsSubmit/1,289485,sid13,00.html?track=NL-94 ___________________________________________________________________ IMPORTANT LINKS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= LEARNING GUIDE: SOLVING COMMON DB2 ERRORS | SearchDatabase.com When you get an error code in DB2, it's easy enough to look it up in the Messages and Codes manual. But knowing what is wrong doesn't always help you to solve the problem. Here, DB2 guru Craig Mullins helps SearchDatabase.com members solve some common SQL errors... >> CLICK here for the complete guide... http://www.searchdatabase.com/originalContent/0,289142,sid13_gci934419,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= NORMALIZATION | SearchDatabase.com Featured Topic Optimal normalization is the key to good database design. And good design is the key to optimal database performance, efficiency, and integrity. The tips and expert advice below will help you master this important skill. >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci879196,00.html?track=NL-94 >> CLICK for previous Featured Topics... http://searchdatabase.techtarget.com/featuredTopics/0,290043,sid13,00.html?track=NL-94 ___________________________________________________________________ *********************SEARCHDATABASE CONTACTS*********************** TIM DICHIARA, Senior Site Editor (mailto:[EMAIL PROTECTED]) ROBYN LORUSSO, Site Editor (mailto:[EMAIL PROTECTED]) SARA CUSHMAN, Assistant Editor (mailto:[EMAIL PROTECTED]) ELLEN O'BRIEN, News Editor (mailto:[EMAIL PROTECTED]) ROB WESTERVELT, News Writer (mailto:[EMAIL PROTECTED]) CLARK MOREY, Sales (mailto:[EMAIL PROTECTED]) ___________________________________________________________________ **********************ABOUT THIS NEWSLETTER************************ Created by TechTarget (http://www.techtarget.com?track=NL-94 TechTarget - The Most Targeted IT Media Copyright 2003, All Rights Reserved. To unsubscribe from 'DBA Tips,' simply reply to this e-mail with REMOVE (all caps) within the Body or Subject or go to http://searchDatabase.techtarget.com/register,?track=NL-94 log in to edit your profile, click on the link to Edit email subscriptions, and uncheck the box next to the newsletter you wish to unsubscribe from. When finished, click "Save Changes to My Profile."
