SEARCHDATABASE.COM | Database Administrator Tips December 4, 2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FROM OUR SPONSOR:
Lumigent Entegra for Data Access Accountability http://searchDatabase.com/r/0,,8071,00.htm?lumigent =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - DB2: Generating reorg statements for all tables - SQL Server: Removing duplicate records from a table - Oracle: Use the name of current database in queries - Oracle: Trimming a string in versions before 8i - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Ask the Experts: Your tough database questions answered - Featured Topic: Stored procedure development ___________________________________________________________________ *********** Sponsored by Lumigent Technologies *********** Announcing Lumigent(R) Entegra(TM) - meet your data privacy and security requirements with a complete audit of database activity. Know "who's doing what to which data when". Entegra provides an audit trail of changes to data and to database structure and permissions, including data views (i.e. who viewed data). Entegra monitors and alerts on database activity for a complete record of data access, and data and database changes. FREE whitepaper -- "Data Access Accountability" http://searchDatabase.com/r/0,,8071,00.htm?lumigent ******************************************************************* ___________________________________________________________________ THIS WEEK'S TIPS: GENERATING REORG STATEMENTS FOR ALL TABLES | Prashant Dahalkar The author's production tables often need to be reorganized because they have been modified so many times that the data is fragmented and access performance is slow. In order to generate the reorg statements for all the tables at once -- as a batch process that will execute at night -- this command for DB2 UDB 7.1 is very useful. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci860001,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= REMOVING DUPLICATE RECORDS FROM A TABLE THAT HAS NO PRIMARY KEY By Prashant Dahalkar. Here is a SQL Server SELECT query that fetches only those records where the combination of two composite keys is unique. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci862833,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= USE THE NAME OF CURRENT DATABASE IN QUERIES | Sushil Srivastava Sometimes its quite essential to know and use the Oracle database name on which your SQL is being executed. You can do it simply by querying the GLOBAL_NAMES view in Oracle, like this: SELECT * FROM GLOBAL_NAMES. Simple! >> CLICK for the online version of the tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci858631,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= TRIMMING A STRING IN VERSIONS BEFORE 8I | Keshav Chennakeshav To trim a string in Oracle, LTRIM and RTRIM are available. Here is a utility function that combines the two. Note: this function is a standard feature of Oracle 8i and higher so it will be of use for those who use Oracle versions less than 8i. >> CLICK for the online version of the tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci859899,00.html ___________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: IDENTIFY REDUNDANT INDEXES | Sreedhar Bobbadi http://www.searchDatabase.com/tip/1,289483,sid13_gci859891,00.html Indexes are created to enhance performance, but often they are created with the same attributes, resulting in redundant indexes. Over a period of time the number of such indexes can grow, increasing the size of the DB2 catalog. The following SQL can help you to indentify all such indexes. It has been tested on DB2 UDB OS/390 version 7.0. USE A QUERY TO DOCUMENT YOUR DATABASE | Parthasarathy Mandayam http://www.searchDatabase.com/tip/1,289483,sid13_gci839384,00.html Did you know that you can use a query to document your SQL Server database? This simple query on the system tables will do just that. You can easily cut and paste the results into a Word document and improve the look and feel. Here's how. ACCESSING NON-ORACLE DATABASES WITH JDBC | Vishal Shah http://www.searchDatabase.com/tip/1,289483,sid13_gci865321,00.html The author needed to access data in a Progress database from within an Oracle stored procedure. To do so, he utilized Oracle's Java Stored Procedure functionality to make this possible. Here are the steps that can work with any DBMS. KEEPING OPTIMIZER STATISTICS UP TO DATE | David Gibbs http://www.searchDatabase.com/tip/1,289483,sid13_gci865222,00.html This Oracle script can be scheduled to run regularly to monitor table usage and keep statistics on schema objects current. 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 MESSAGES AND LINKS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ASK THE EXPERT | This week's tough database questions answered SQL Server: Two named instances versus two apps on one instance http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid505053_tax285648,00.html SQL: Boolean columns http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid505031_tax285649,00.html Oracle: TOP-N queries and function-based indexes http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid504157_tax289342,00.html DB2: Equivalent DB2 syntax for the Oracle DECODE function http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid503806_tax285651,00.html SQL Server: Getting I/O stats http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid505036_tax285648,00.html More expert answers here: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= STORED PROCEDURE DEVELOPMENT | SearchDatabase.com Featured Topic Stored procedures increase the performance, flexibility, and security of your DBMS. Here is a selection of our best tips and expert advice about coding effective Oracle, SQL Server, and DB2 stored procedures and triggers. >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci809177,00.html >> CLICK for previous Featured Topics... http://searchdatabase.techtarget.com/featuredTopics/0,290043,sid13,00.html ___________________________________________________________________ *********************SEARCHDATABASE CONTACTS*********************** TIP MAILBOX, (mailto:[EMAIL PROTECTED]) >> Submit your original tip here. TIM DICHIARA, Site Editor (mailto:[EMAIL PROTECTED]) >> Send your feedback and original articles. SARA CUSHMAN, Assistant Editor (mailto:[EMAIL PROTECTED]) >> Send your White Papers and favorite Web sites. ELLEN O'BRIEN, News Editor (mailto:[EMAIL PROTECTED]) >> Send your product announcements and poll ideas. TOM CLICK, Sales (mailto:[EMAIL PROTECTED]) >> Sponsor this or any other TechTarget newsletter. ___________________________________________________________________ **********************ABOUT THIS NEWSLETTER************************ Created by TechTarget (http://www.techtarget.com) TechTarget - The Most Targeted IT Media Copyright 2002, 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."
