SEARCHDATABASE.COM | Database Administrator Tips November 12, 2003 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FROM OUR SPONSOR:
FREE WHITE PAPER ON DATA RECOVERY BEST PRACTICES http://searchDatabase.com/r/0,,21145,00.htm?track=NL-94&lumigent =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: Number of business days between two dates - DB2: Exception handling - Oracle: Disable/enable FK constraints against a parent table - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Top 5: What's hot on SearchOracle.com - Featured Topic: T-SQL tips and scripts ___________________________________________________________________ ************** Sponsored by Lumigent Technologies ***************** Planning for data recovery is about more than just making sure your database is backed up. There are many things to consider. In his paper, Stephen Wynkoop, MVP and Founder SSWUG, discusses the best approaches to a solid data recovery solution. Download it today, compliments of Lumigent Technologies: http://searchDatabase.com/r/0,,21145,00.htm?track=NL-94&lumigent ******************************************************************* ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= NUMBER OF BUSINESS DAYS BETWEEN TWO DATES | Murthy Chamarthi 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. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci867507,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DB2 EXCEPTION HANDLING | Paul Yip et al. 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. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci936196,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DISABLE/ENABLE FK CONSTRAINTS AGAINST A SPECIFIED PARENT TABLE 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. >> CLICK for the tip... http://www.searchOracle.com/tip/1,289483,sid41_gci935719,00.html?track=NL-94 ___________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: 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. COMPILE ALL INVALID OBJECTS | Daniel Lanz http://www.searchOracle.com/tip/1,289483,sid41_gci930536,00.html?track=NL-94 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. 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. 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: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= SEARCHORACLE.COM TOP 5 | What's hot on SearchOracle.com Check out the most popular articles, tips, expert responses and white papers on SearchOracle.com for the last week or so: >> Oracle OCMs are a rare breed http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci935369,00.html?track=NL-94 >> Serious flaw found in Oracle 9i app server http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci935442,00.html?track=NL-94 >> Fast Guide: Solving common Oracle errors http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci933283,00.html?track=NL-94 >> Fast Guide: Becoming an Oracle Certified Professional http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci916587,00.html?track=NL-94 >> The Oracle Guru Exam #1: A compilation of quizzes 1-10 http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci931163,00.html?track=NL-94 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= NORMALIZATION | SearchDatabase.com Featured Topic Optimal normalization is the key to good database design. And good design is the key to optimal database performance, efficiency, and integrity. The tips and expert advice below will help you master this important skill. >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci879196,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]) 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 '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."
