Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Course Guide Informix 12.10 Database Administration Course code IX223 ERC 1.0 ®

Course Guide Informix 12.10 Database Administration Course code IX223 ERC 1.0 ® IBM Training Preface © Copyright IBM Corp. 2001, 2017 P-2 Course materials may not be reproduced in whole or in part without the prior written permission of IBM. September, 2017 NOTICES This information was developed for products and services offered in the USA. IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative for information on the products and services currently available in your area. Any reference to an IBM product, program, or service is not intended to state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or service that does not infringe any IBM intellectual property right may be used instead. However, it is the user's responsibility to evaluate and verify the operation of any non-IBM product, program, or service. IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not grant you any license to these patents. You can send license inquiries, in writing, to: IBM Director of Licensing IBM Corporation North Castle Drive, MD-NC119 Armonk, NY 10504-1785 United States of America The following paragraph does not apply to the United Kingdom or any other country where such provisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in certain transactions, therefore, this statement may not apply to you. This information could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein; these changes will be incorporated in new editions of the publication. IBM may make improvements and/or changes in the product(s) and/or the program(s) described in this publication at any time without notice. Any references in this information to non-IBM websites are provided for convenience only and do not in any manner serve as an endorsement of those websites. The materials at those websites are not part of the materials for this IBM product and use of those websites is at your own risk. IBM may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you. Information concerning non-IBM products was obtained from the suppliers of those products, their published announcements or other publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products. This information contains examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples include the names of individuals, companies, brands, and products. All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental. TRADEMARKS IBM, the IBM logo, ibm.com, and Informix are trademarks or registered trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the web at “Copyright and trademark information” at www.ibm.com/legal/copytrade.shtml. Adobe, and the Adobe logo are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States, and/or other countries. Microsoft, Windows, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both. Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both. UNIX is a registered trademark of The Open Group in the United States and other countries. PuTTY is copyright 1997-2017 by Simon Tatham. VMware is a trademark of VMware, Inc © Copyright International Business Machines Corporation 2017. This document may not be reproduced in whole or in part without the prior written permission of IBM. US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. Preface © Copyright IBM Corp. 2001, 2017 P-3 Course materials may not be reproduced in whole or in part without the prior written permission of IBM. Contents Preface ................................................................................................................. P-1 Contents ............................................................................................................. P-3 Course overview ............................................................................................... P-15 Document conventions ..................................................................................... P-17 Exercises .......................................................................................................... P-18 Additional training resources ............................................................................ P-19 IBM product help .............................................................................................. P-20 Creating databases and tables ............................................................ 1-1 Unit objectives .................................................................................................... 1-3 Prerequisites ...................................................................................................... 1-4 Before you create a database............................................................................. 1-5 Database names ................................................................................................ 1-6 Database logging ............................................................................................... 1-7 No logging .......................................................................................................... 1-8 NO LOGGING still has logging ......................................................................... 1-10 Unbuffered logging ........................................................................................... 1-11 Buffered logging ............................................................................................... 1-12 MODE ANSI databases .................................................................................... 1-13 The database dbspace ..................................................................................... 1-15 Creating a database ......................................................................................... 1-16 Creating a table ................................................................................................ 1-17 Select a valid table name ................................................................................. 1-18 Extents ............................................................................................................. 1-20 Estimating row and extent sizes ....................................................................... 1-22 Managing extents ............................................................................................. 1-24 Table lock modes ............................................................................................. 1-26 Tables and dbspaces ....................................................................................... 1-28 Creating a table ................................................................................................ 1-29 Creating a table: Simple large objects .............................................................. 1-30 Creating a table: Smart large objects ................................................................ 1-31 Creating a temporary table ............................................................................... 1-32 DBCENTURY ................................................................................................... 1-34 The DBSCHEMA utility ..................................................................................... 1-35 Using ONCHECK and ONSTAT ....................................................................... 1-37 Preface © Copyright IBM Corp. 2001, 2017 P-4 Course materials may not be reproduced in whole or in part without the prior written permission of IBM. Sysmaster table: sysdatabases ........................................................................ 1-38 System Catalog table: systables ....................................................................... 1-39 System Catalog table: syscolumns ................................................................... 1-40 Exercise 1: Create databases and tables ......................................................... 1-41 Unit summary ................................................................................................... 1-70 Altering and dropping databases and tables ...................................... 2-1 Unit objectives .................................................................................................... 2-3 Altering a table ................................................................................................... 2-4 Fast ALTER ........................................................................................................ 2-5 In-place ALTER .................................................................................................. 2-6 Slow ALTER ....................................................................................................... 2-8 Slow ALTER process ......................................................................................... 2-9 Data space reclamation: CLUSTER index ........................................................ 2-10 Data space reclamation: TRUNCATE ............................................................... 2-12 Renaming columns, tables, and databases ...................................................... 2-13 Converting simple objects to smart objects ...................................................... 2-15 Dropping tables and databases ........................................................................ 2-16 Exercise 2: Alter and drop databases and tables .............................................. 2-17 Unit summary ................................................................................................... 2-27 Creating, altering, and dropping indexes ............................................ 3-1 Unit objectives .................................................................................................... 3-3 B+ tree index structure ....................................................................................... 3-4 B+ tree splits ...................................................................................................... 3-6 Indexes: Unique and duplicate ........................................................................... 3-7 Composite index ................................................................................................. 3-8 Using composite indexes .................................................................................... 3-9 Cluster indexes ................................................................................................. 3-10 The CREATE INDEX statement ....................................................................... 3-11 Detached indexes ............................................................................................. 3-13 Index fill factor .................................................................................................. 3-14 Altering, dropping, and renaming indexes ........................................................ 3-15 SYSINDICES and SYSINDEXES system catalogs ........................................... 3-16 Forest of trees index ......................................................................................... 3-17 Comparing B+ tree and forest of trees indexes ................................................ 3-18 Exercise 3: Create, alter, and drop indexes ...................................................... 3-19 Unit summary ................................................................................................... 3-27 Preface © Copyright IBM Corp. 2001, 2017 P-5 Course materials may not be reproduced in whole or in part without the prior written permission of IBM. Managing and maintaining indexes ..................................................... 4-1 Unit objectives .................................................................................................... 4-3 Benefits of indexing ............................................................................................ 4-4 Costs of indexing ................................................................................................ 4-5 B+ tree maintenance .......................................................................................... 4-7 Indexing guidelines ............................................................................................. 4-9 Index join columns ............................................................................................ 4-10 Index filter columns .......................................................................................... 4-11 Index columns involved in sorting ..................................................................... 4-12 Avoid highly duplicate indexes .......................................................................... 4-13 Volatile tables ................................................................................................... 4-14 Keeping key size small ..................................................................................... 4-15 Composite indexes ........................................................................................... 4-16 Clustered indexes ............................................................................................. 4-17 Drop versus disable indexes............................................................................. 4-18 Parallel index builds ......................................................................................... 4-19 Calculating index size ....................................................................................... 4-20 Exercise 4: Managing and maintaining indexes ................................................ 4-23 Unit summary ................................................................................................... 4-51 Table and index partitioning ................................................................ 5-1 Unit objectives .................................................................................................... 5-3 What is fragmentation? ....................................................................................... 5-4 Fragments and extents ....................................................................................... 5-5 Advantages of fragmentation .............................................................................. 5-6 Parallel scans and fragmentation ....................................................................... 5-8 Parallel scans (PDQ queries) ............................................................................. 5-9 DSS queries ..................................................................................................... 5-10 Balanced I/O and fragmentation ....................................................................... 5-11 OLTP queries ................................................................................................... 5-12 Types of distribution schemes .......................................................................... 5-13 Round robin fragmentation ............................................................................... 5-15 Round robin for smart large objects .................................................................. 5-17 Expression-based fragmentation ...................................................................... 5-18 Using PARTITIONING ...................................................................................... 5-19 Logical and relational operators ........................................................................ 5-20 Fragmentation by expression guidelines .......................................................... 5-22 Using hash functions ........................................................................................ 5-24 Fragmentation based on a list .......................................................................... 5-26 Fragmentation based on an interval ................................................................. 5-27 Fragmented/partitioned indexes ....................................................................... 5-29 Preface © Copyright IBM Corp. 2001, 2017 P-6 Course materials may not be reproduced in whole or in part without the prior written permission of IBM. CREATE INDEX statement .............................................................................. 5-31 ROWIDS .......................................................................................................... 5-32 Selecting a fragmentation strategy ................................................................... 5-33 Fragmentation of temporary tables ................................................................... 5-35 Creating fragmented temporary tables ............................................................. 5-36 Fragmenting an index ....................................................................................... 5-37 System Catalog: sysfragments ......................................................................... 5-38 Optional discussion: Case study ....................................................................... 5-39 Exercise 5: Table and index partitioning ........................................................... 5-41 Unit summary ................................................................................................... 5-58 Maintaining table and index partitioning ............................................. 6-1 Unit objectives .................................................................................................... 6-3 The ALTER FRAGMENT statement ................................................................... 6-4 Initializing a new fragmentation strategy ............................................................. 6-5 Adding an additional fragment ............................................................................ 6-6 Dropping a fragment ........................................................................................... 6-7 Modifying an existing fragment ........................................................................... 6-8 Attaching and detaching fragments .................................................................... 6-9 How is uploads/s1/ ix223course-guide.pdf