SEARCHDATABASE.COM | Database Administrator Tips October 29, 2003 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FROM OUR SPONSOR:
How does your salary compare to those of your peers? http://searchdatabase.com/r/0,,18964,00.htm?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: Find the size of every database - DB2: Cluster indexes - Oracle: Compile all invalid objects - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Free DB2, SQL Server, and Oracle book chapter downloads - Ask the experts: Migrating from DB2 to SQL Server - Featured Topic: Locking down your DBMS ___________________________________________________________________ *************** SearchDatabase.com Career Center ****************** How does your salary compare to those of your peers? Visit SearchDatabase.com's new Career Center powered by Dice and take our salary survey. You'll find training, certification and resume resources along with over 27,000 IT job openings to search through. Register now and receive e-mails when a listing matches your search criteria, have your profile automatically sent to Dice.com member companies, access a host of career resources and tips and much more! http://searchdatabase.com/r/0,,18964,00.htm?track=NL-94 ******************************************************************* ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FIND THE SIZE OF EVERY DATABASE | Madhusudan Naidu Gundapaneni This handy script will find the size of every database in SQL Server 2000 without using the sp_spaceused function. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci931564,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= CLUSTER INDEXES: UNDER THE HOOD | Alexander Kuznetsov 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. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci934007,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= COMPILE ALL INVALID OBJECTS | Daniel Lanz 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. >> CLICK for the tip... http://www.searchOracle.com/tip/1,289483,sid41_gci930536,00.html?track=NL-94 ___________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: 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. PERFORMANCE TUNING, STEP 3: WORKING THE PLAN | Carol Francum http://searchoracle.techtarget.com/tip/1,289483,sid41_gci930520,00.html?track=NL-94 In step 1 we identified the elements of a basics of an Oracle performance tuning plan. Then, in step 2, we identified the current state for subsequent stages and determined the relative priorities which need to be met in terms of user requirements and management requirements. The next step in a tuning program is to assess the details of the system, in terms of specific architecture, processes and code. PROBLEM-SOLVING BLOCKING SITUATIONS | Robert Hauck http://www.searchDatabase.com/tip/1,289483,sid13_gci912793,00.html?track=NL-94 This procedure for SQL Server 2000 is intended to help with problem-solving blocking situations. The author developed it to contact users when their client software holds locks that are holding up other users. It works faster than viewing the blocking status through Enterprise Manager. LITTLE BY LITTLE, STEP BY STEP: SPLITTING DB2 TRANSACTIONS http://www.searchDatabase.com/tip/1,289483,sid13_gci931229,00.html?track=NL-94 Sometimes you may need to insert, update or delete a lot of rows. Doing that in one SQL statement may be unwise. Here is how to split a large transaction into several smaller ones 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: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FREE DB2, SQL SERVER, AND ORACLE BOOK CHAPTER DOWNLOADS SearchDatabase.com presents a small but growing collection of free excerpts from the latest books written by highly-regarded DBMS gurus, free for the taking. If you're looking for quality technical SQL Server, DB2 and Oracle information from the top authors in the data management field, you've come to the right place. CLICK here for the complete list... http://www.searchDatabase.com/originalContent/0,289142,sid13_gci931678,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= MIGRATING FROM DB2 TO SQL SERVER | Ask the Expert Can you suggest any tools or techniques for migrating from DB2 to SQL Server? >> CLICK for expert Mike Hotek's answer... http://www.searchdatabase.com/ateQuestionNResponse/0,289625,sid13_cid556532_tax285648,00.html?track=NL-94 >> CLICK for other database expert answers... http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html?track=NL-94 >> CLICK for Oracle-related expert answers... http://searchoracle.techtarget.com/ateExperts/0,289622,sid41,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= LOCKING DOWN YOUR DBMS | SearchDatabase.com Featured Topic The need for data security may be obvious, but the path getting there certainly isn't. Our collection of tips & advice will help you lock down your Oracle, DB2, or SQL Server DBMS. >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci863539,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]) TOM CLICK, Sales (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."
