SEARCHDATABASE.COM | Database Administrator Tips November 19, 2003 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FROM OUR SPONSOR:
White Paper: How to Survive in a Multi-Database Environment http://searchDatabase.com/r/0,,21420,00.htm?track=NL-94&questsoftware =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: Build the right index - DB2: Improving DB2 recovery performance - Oracle: Identify tables having FKs on a given table's PK - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Featured Topic: Backup and recovery ___________________________________________________________________ ****************** 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. Download the white paper today at http://searchDatabase.com/r/0,,21420,00.htm?track=NL-94&questsoftware ******************************************************************* ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= BUILD THE RIGHT INDEX | Barrie Sosinsky Indexes are a central performance feature of any database but it's important to create the right kind of index. Here are a few quick index tips for SQL Server 2000. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci937802,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IMPROVING DB2 RECOVERY PERFORMANCE | Barrie Sosinsky By optimizing the use of your buffers and using PARALLELISM properly, you can reduce the time it takes to perform a restore operation in DB2. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci937773,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IDENTIFY TABLES HAVING FKS ON A GIVEN TABLE'S PK | Daniel Clamage DBAs frequently need to know what tables have foreign keys on a specified table's primary key. This Oracle stored procedure displays them. >> CLICK for the tip... http://www.searchOracle.com/tip/1,289483,sid41_gci935718,00.html?track=NL-94 ___________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: NUMBER OF BUSINESS DAYS BETWEEN TWO DATES | Murthy Chamarthi http://www.searchDatabase.com/tip/1,289483,sid13_gci867507,00.html?track=NL-94 This short SQL Server procedure is useful to get the number of working days between the two dates given. It can be used to find answers to many questions such as the number of working days of an employee in an organisation. DB2 EXCEPTION HANDLING | Paul Yip et al. http://www.searchDatabase.com/tip/1,289483,sid13_gci936196,00.html?track=NL-94 This chapter from the recent book "DB2 SQL Procedural Language for Linux, UNIX, and Windows" will describe the various types of DB2 condition handlers and techniques of how and when to use them. DISABLE/ENABLE FK CONSTRAINTS AGAINST A SPECIFIED PARENT TABLE http://www.searchOracle.com/tip/1,289483,sid41_gci935719,00.html?track=NL-94 Sometimes you want to truncate a table that has foreign keys in other tables pointing to the primary key of the table to be truncated. But you can't truncate a table that has foreign keys pointing to its primary key, even if the child tables are empty! This stored procedure from member Daniel Clamage simplifies the task of disabling/enabling foreign key constraints against the specified parent table. FIND THE SPACE OCCUPIED BY ALL TABLES AND INDEXES | Parthasarathy Mandayam http://www.searchDatabase.com/tip/1,289483,sid13_gci912794,00.html?track=NL-94 Here's an undocumented method to find the space occupied by all tables and indexes in a SQL Server 2000 database. DATA MANIPULATION IN DB2 UDB | Dwaine Snow and Tom Phan http://www.searchDatabase.com/tip/1,289483,sid13_gci935161,00.html?track=NL-94 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. CREATE A DML SCRIPT USING A VIEW | Satya Josyula http://www.searchOracle.com/tip/1,289483,sid41_gci933201,00.html?track=NL-94 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. 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. 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: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= BACKUP AND RECOVERY | SearchDatabase.com Featured Topic The most important function of a DBA is the backup and recovery of mission-critical data. Stay current with our storage tip collection, expert advice, and news analysis inside... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci831509,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]) GABRIELLE DERUSSY, 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."
