SEARCHDATABASE.COM | Database Administrator Tips August 28, 2003 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FROM OUR SPONSOR:
Free database white papers http://searchdatabase.techtarget.com/whitepapers3/0,,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: Check the isolation level of any SPID - DB2: Using the Run Statistics utility - Oracle: Insert statement generator - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Ask the experts: NESTED GROUP BYS - Learning Guide: DB2 basics - Featured Topic: Performance tuning ___________________________________________________________________ *********************** Free white papers ************************* Download the key papers you need today to make better-informed decisions. Targeted categories include: Data analysis and Business Intelligence; Data Security, Data Storage, Database Web Integration, and more. Click here to access the free SearchDatabase.com white papers section http://searchdatabase.techtarget.com/whitepapers3/0,,sid13,00.html ******************************************************************* ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= CHECK THE ISOLATION LEVEL OF ANY SQL SERVER SPID | Paul Wehland How do you check the isolation level of other users (i.e., other SPIDs)? DBCC USEROPTIONS cannot do it. The way aound this is to use the undocumented DBCC PSS command.... >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci920866,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= USING THE RUN STATISTICS UTILITY | Barrie Sosinsky A short overview of DB2's Run Statistics utility, which is used to collect information about the organization of your table structures. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci921669,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= INSERT STATEMENT GENERATOR | Ravi Nookala This Oracle script generates INSERT statements from an existing table that can be used as a backup or source script for future data loads. >> CLICK for the full tip... http://www.searchOracle.com/tip/1,289483,sid41_gci920868,00.html >> NOTE: All Oracle-related tech tips have moved! Click here: http://www.SearchOracle.com/tips ___________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: DATA MIGRATION SCRIPT FOR STATIC TABLES | Rajasekhar Sahitala http://www.searchDatabase.com/tip/1,289483,sid13_gci915924,00.html Suppose that you have an application in which you have a table containing static data and you need this data whenever you recreate the database. Using this SQL Server script, you can create a batch of insert statements that you can keep in your database creation script and start using the database after the database creation without reloading any data. USE THE FIREHOSE CURSOR | Barrie Sosinsky http://www.searchDatabase.com/tip/1,289483,sid13_gci911716,00.html SQL Server supports several types of cursors but one read-only, non-scrollable cursor that is specially optimized for fast performance and worthy of note is the firehose cursor. Here's a short introduction. A SIMPLE WAY TO REORGANIZE A TABLE AND RELEASE THE UNUSED SPACE http://www.searchOracle.com/tip/1,289483,sid41_gci918141,00.html This tip from member Debajit Mishra describes a simple solution to better manage the table space and performance during ETL operations in Oracle 9i. RESEEDING IDENTITIES IN SQL SERVER | Baya Pavliashvili http://www.searchDatabase.com/tip/1,289483,sid13_gci913732,00.html Reseeding identities in IDENTITY columns are commonly used as primary keys for SQL Server tables. Here are some tips about reseeding identities and some pitfalls to avoid. JDBC: DATABASE PROGRAMMING WITH J2EE | Art Taylor http://www.searchDatabase.com/tip/1,289483,sid13_gci917202,00.html As a registered member of SearchDatabase.com, you're entitled to a complimentary copy of Chapter 1 from "JDBC: Database programming with J2EE" written by Art Taylor and published by Prentice Hall PTR. This chapter focuses on JDBC design, how Java works with relational databases, the JDBC API and more. HOW TO USE RESUMABLE SPACE ALLOCATION IN ORACLE 9I | Sanjay Gupta http://www.searchOracle.com/tip/1,289483,sid41_gci915006,00.html This tip discuss how to use a new feature in Oracle 9i called Resuamable Space Allocation. Before 9i, it was not possible to suspend a session when some space-related problem occured, so as a result, whole transactions got rolled back and you had to start your work all over again. GENERATING A DATABASE POPULATION ROUTINE | Baya Pavliashvili http://www.searchDatabase.com/tip/1,289483,sid13_gci913717,00.html Here is a SQL Server script that will populate your data model with data and stress test it prior to making it available to users. Hundreds more free tech tips submitted by members: http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html Share your knowledge, submit a tip, win a prize: http://searchdatabase.techtarget.com/tipsSubmit/1,289485,sid13,00.html ___________________________________________________________________ IMPORTANT LINKS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= NESTED GROUP BYS | Ask the Experts Dear Ask the Expert: I've got a query that consolidates a group of rows and gives me a column SUM... How can I have this query return just the MAX amount out of the SUM(NETINTLCNT) values grouped by UNIV_CUST_ID and VEH_CTR_CD? Can I have multiple GROUP BYs in a query, or a subselect GROUP BY? >> CLICK to read SQL expert Rudy Limeback's response... http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid546962_tax285649,00.html >> CLICK for other database expert answers... http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DB2 BASICS | Learning Guide DB2 took top honors in some DBMS market share reports this year, which means many companies may have already, or will soon be, switching over to IBM's brand of data management. With that in mind, we created this learning guide, highlighting basic DB2 information that can help Oracle, SQL Server and other database pros get up to speed on the new system. http://www.searchdatabase.com/originalContent/0,289142,sid13_gci917394,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= PERFORMANCE TUNING | SearchDatabase.com Featured Topic A slow DBMS is not only annoying to users, it can also affect your company's bottom line. The collection of Oracle, SQL Server, DB2, & SQL tuning advice below will help you achieve maximal speed and reliability for your system. >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci842456,00.html >> CLICK for previous Featured Topics... http://searchdatabase.techtarget.com/featuredTopics/0,290043,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ___________________________________________________________________ *********************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 MORLEY, Sales (mailto:[EMAIL PROTECTED]) ___________________________________________________________________ **********************ABOUT THIS NEWSLETTER************************ Created by TechTarget (http://www.techtarget.com) TechTarget - The Most Targeted IT Media Copyright 2003, All Rights Reserved. ___________________________________________________________________ ::::::::::::::::::::: ABOUT THIS NEWSLETTER :::::::::::::::::::::: This newsletter is published by TechTarget, the most targeted IT media. http://www.techtarget.com Copyright 2003 TechTarget. 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, 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."
