SEARCHDATABASE.COM | Database Administrator Tips October 15, 2003 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FROM OUR SPONSOR:
Complimentary DB2 System Catalog Poster http://searchDatabase.com/r/0,,19747,00.htm?track=NL-94&questsoftware =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: Useful scripts to clear out old plan history logs - DB2: Limiting result sets with FETCH FIRST and OPTIMIZE FOR - Oracle: Performance tuning, step 3: Working the plan - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Free DB2, SQL Server, and Oracle book chapter downloads - Ask the experts: Your tough questions answered - Featured Topic: Beyond the big three ___________________________________________________________________ ****************** Sponsored by Quest Software ******************** Tired of having to reference cumbersome manuals every time you need to look up information about your DB2 catalogs? Get with the times and download Quest Software's free electronic desktop catalog poster of DB2 UDB V8. http://searchDatabase.com/r/0,,19747,00.htm?track=NL-94&questsoftware ******************************************************************* ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= USEFUL SCRIPTS TO CLEAR OUT OLD PLAN HISTORY LOGS | Robert Hauck 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. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci911715,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= LIMITING RESULT SETS WITH FETCH FIRST AND OPTIMIZE FOR by Alexander Kuznetsov 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. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci932208,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= PERFORMANCE TUNING, STEP 3: WORKING THE PLAN | Carol Francum 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. >> CLICK for the tip... http://searchoracle.techtarget.com/tip/1,289483,sid41_gci930520,00.html?track=NL-94 ___________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: 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. SPEED UP DATA TRANSPORT BETWEEN PRIMARY AND STANDBY DATABASE http://www.searchOracle.com/tip/1,289483,sid41_gci930276,00.html?track=NL-94 Ravinder Bhalla presents a quick tip to speed up data transport between primary and standby Oracle databases in a DataGuard environment. HOW SQL SERVER IS HACKED | David Litchfield http://www.searchDatabase.com/tip/1,289483,sid13_gci930221,00.html?track=NL-94 Database servers are a soft target for hackers even though they should be the most secure boxes within an organization's IT infrastructure. This chapter from David Litchfeld's new book "SQL Server Security" covers both software vulnerabilities, configuration issues, hacker tools, attacks that require and don't require authentication, and more. FORMATTING THE DATE WITH SQL | Ravinder Bahadur http://www.searchDatabase.com/tip/1,289483,sid13_gci920867,00.html?track=NL-94 Here is a simple way to provide a format for date fields in DB2. HIDDEN ORACLE, PART II: USING UNDOCUMENTED PARAMETERS http://www.searchOracle.com/tip/1,289483,sid41_gci928423,00.html?track=NL-94 In part 1 of this series, guru Don Burleson explored ways to extract undocumented packages, hints, utilities and executables from Oracle. Now Don is ready to explore some ways that the undocumented Oracle parameters are used to aid the Oracle professional in managing and tuning their systems. SPLIT DELIMITER-SEPARATED LISTS | Rajasekhar Sahitala http://www.searchDatabase.com/tip/1,289483,sid13_gci915927,00.html?track=NL-94 This SQL Server function is useful for splitting a string of values delimited by a special character; e.g., a comma or a space. DB2 DISCOVERY | Barrie Sosinsky http://www.searchDatabase.com/tip/1,289483,sid13_gci928300,00.html?track=NL-94 Here is a quick introduction to DB2 Discovery, which provides a more automated approach to problems associated with client/server communication. 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 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 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= YOUR TOUGH DATABASE QUESTIONS ANSWERED | Ask the Experts SQL: Syntax differences in MS Access http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid549813_tax285649,00.html?track=NL-94 SQL Server: Variables in WHERE statements of views? http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid549670_tax285648,00.html?track=NL-94 DB2: Problems with SELECT * after column added http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid547903_tax285651,00.html?track=NL-94 SQL: Summing values in discrete ranges http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid549665_tax285649,00.html?track=NL-94 SQL Server: Storing tables with same name in different schema http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid548257_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 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= BEYOND THE BIG THREE | SearchDatabase.com Featured Topic There is life beyond the big three DBMS vendors! Sybase, MySQL, and others have found significant niches in enterprise IT. Our collection of news, tips, and expert advice has all the info you need about the "best of the rest." >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci825686,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."