SEARCHDATABASE.COM | Database Administrator Tips October 22, 2003 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FROM OUR SPONSOR:
New Data Profiling and Data Quality Web Seminar from DataFlux http://searchDatabase.com/r/0,,20167,00.htm?track=NL-94&dataflux =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: Script to kill all database processes - DB2: Introduction to monitoring DB2 v8 - Oracle: Run multiple sessions of DBV - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Free DB2, SQL Server, and Oracle book chapter downloads - Ask the experts: Subquery or join? - Featured Topic: Ask the experts ___________________________________________________________________ ************* Sponsored by DataFlux (A SAS Company) *************** Join DataFlux as they discuss the importance of data profiling and data quality at this special web seminar on October 23rd at 1:00 p.m. EDT. Learn how a strategy using both elements can enhance the data that drives your CRM, ERP and data warehousing initiatives. Register today for Data Profiling and Data Quality: The Blueprint for Effective Data Management! http://searchDatabase.com/r/0,,20167,00.htm?track=NL-94&dataflux ******************************************************************* ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= SCRIPT TO KILL ALL DATABASE PROCESSES | Parthasarathy Mandayam 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. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci840255,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= INTRODUCTION TO MONITORING DB2 V8 | Philip Gunning 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. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci931152,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= RUN MULTIPLE SESSIONS OF DBV | Michael Vergara 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. >> CLICK for the tip... http://www.searchOracle.com/tip/1,289483,sid41_gci930274,00.html?track=NL-94 ___________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: 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. 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. 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 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= SUBQUERY OR JOIN? | Ask the Expert Some tasks can be performed in two ways, both by joins and subqueries. In what situations should we opt for subqueries? >> CLICK for expert Rudy Limeback's answer... http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid555368_tax285649,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 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ASK THE EXPERTS | SearchDatabase.com Featured Topic Our panel of industry gurus has answered over 2,000 of your tough Oracle, SQL Server, DB2, data warehousing, & SQL questions. Browse the knowledgebase or submit your own... >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci843571,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 'DB2 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."
