SEARCHDATABASE.COM | Database Administrator Tips December 3, 2003 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FROM OUR SPONSOR:
White Paper: How to Survive in a Multi-Database Environment http://searchDatabase.com/r/0,,22025,00.htm?track=NL-94&questsoftware =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: Find the product of a field - DB2: Index covering and query performance - Oracle: Compile all invalid objects - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Ask the Experts: Your tough DBMS questions answered - Featured Topic: XML and the DBMS ___________________________________________________________________ ****************** 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,,22025,00.htm?track=NL-94&questsoftware ******************************************************************* ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FIND THE PRODUCT OF A FIELD | Vadivel Mohanakrishnan There isn't a prebuild function for finding the product of field or set of values in SQL Server. Here is a workaround using a combination of the SUM(), POWER() and LOG10() functions. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci937838,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= INDEX COVERING AND QUERY PERFORMANCE | Alexander Kuznetsov The DB2 rule of thumb "put the most selective column first in the index definition" is usually sound, but not always. Here is a situation when putting the most selective column last speeds up a query. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci939445,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= COMPILE ALL INVALID OBJECTS INCLUDING THOSE WITH SYNTAX ERRORS | by Rastislav Ciganek Here's how to compile all invalid objects in Oracle, including these with syntax errors. It can by used for specific schemas or for whole databases and has more features than utlrp.sql. >> CLICK for the tip... http://www.searchOracle.com/tip/1,289483,sid41_gci938288,00.html?track=NL-94 ___________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: MAINTAIN RUNNING NUMBERS | Vadivel Mohanakrishnan http://www.searchDatabase.com/tip/1,289483,sid13_gci937840,00.html?track=NL-94 Here is how to maintain a running number if you need to combine fields from different tables in SQL Server 7 and 2000. DEFINING BUSINESS INTELLIGENCE USERS | Mike Beire http://www.searchDatabase.com/tip/1,289483,sid13_gci938533,00.html?track=NL-94 Users of BI systems can be delineated by application requirements and by skill. Here is how to define who is using your BI systems -- a critical step in enuring the success of your implementation. GENERATE INDEX DDL AUTOMATICALLY | Eric Mao http://www.searchOracle.com/tip/1,289483,sid41_gci938291,00.html?track=NL-94 If you need to drop and recreate Oracle indexes, you may find this script useful. It will generate current index DDL for a specific schema, among other functions. BUILD THE RIGHT INDEX | Barrie Sosinsky http://www.searchDatabase.com/tip/1,289483,sid13_gci937802,00.html?track=NL-94 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. IMPROVING DB2 RECOVERY PERFORMANCE | Barrie Sosinsky http://www.searchDatabase.com/tip/1,289483,sid13_gci937773,00.html?track=NL-94 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. IDENTIFY TABLES HAVING FKS ON A GIVEN TABLE'S PK | Daniel Clamage http://www.searchOracle.com/tip/1,289483,sid41_gci935718,00.html?track=NL-94 DBAs frequently need to know what tables have foreign keys on a specified table's primary key. This Oracle stored procedure displays them. 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. 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: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= YOUR TOUGH DATABASE QUESTIONS ANSWERED | Ask the Experts SQL Server: Suppressing column headings in Query Analyzer http://www.searchdatabase.com/ateQuestionNResponse/0,289625,sid13_cid561707_tax285648,00.html?track=NL-94 SQL Server: Gap in integer sequence of primary key http://www.searchdatabase.com/ateQuestionNResponse/0,289625,sid13_cid561562_tax294863,00.html?track=NL-94 SQL Server: First execution of SP 10 seconds longer than rest http://www.searchdatabase.com/ateQuestionNResponse/0,289625,sid13_cid561703_tax285648,00.html?track=NL-94 SQL Server: Retrieving a deleted table http://www.searchdatabase.com/ateQuestionNResponse/0,289625,sid13_cid561705_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 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= XML and the DBMS | SearchDatabase.com Featured Topic XML began as a simple markup language for defining and sharing documents containing structured data. Then came query features and database integration. Our tips and advice will help you understand and use XML with your DBMS... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci761146,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."
