® Information Management IBM Informix May 2006 Chat with the Lab IDS 10: Upgrading and New Features IBM Software Group © 2005 IBM Corporation
Information Management Informix Dynamic Server 10. 0 Performance. Reliability. Scalability. Topics § New features in 10. 00. XC 2 through XC 5 § BAAN enhancements § Migrating to v 10 § Q&A 2
Information Management Informix Dynamic Server 10. 0 Performance. Reliability. Scalability. Migrating to v 10 § Why Upgrade? § Migration matrix § Pre-Migration § Install and configuration § Validate your target environment § Perform conversion 3
Information Management Why upgrade? IDS has been busy with v 9/v 10 detached indexes raw tables Fuzzy ckpts dynamic locking 9. 2 update statistics buffer manager onarchive gone 9. 3 Config page size Col Level Encryption large Chunk & files dynamic logging optimizer directives HDR/ER btree scanners ER Resync PIT-TLR restartable fast recovery 9. 4 Shmem > 4 gig 10. 0 IDS Versions – Key features by release 4
Information Management Migrating from prior versions of IDS 5
Information Management Upgrading to v 10 - Conversion Steps Five easy steps to remember for data migration: 1. Get the source server ready by following pre-migration steps 2. Install the target database server and copy over relevant $INFORMIXDIR/etc configuration from source to target 3. Validate your environment settings are pointing at target 4. CONVERT by starting target server 5. Validate data integrity and take level 0 backup CONVERSION COMPLETE! 6
Information Management System Requirements for a new Installation of IDS v 10 500 MB of disk space 256 MB of RAM Plan for: Adequate disk space to house schema Adequate disk space to house temporary tables Adequate CPU’s for application needs Plan for about 20% disk space overhead ** Lookup Installation guide for details IDS provides industrial-strength capabilities with a relatively small footprint, ensuring the best performance with less hardware. 7
Information Management Space Required for Upgrade § Root Chunk (Chunk 0) should have at least 10% free. § 3000 Free pages of Logical Log Space to rebuild Sys. Master § 2000 KB of Free Space per Database is need for each DB Space § Partition Headers should not be full 4 Use oncheck –me to compress extents § Determine how much space is needed by running: 4 DATABASE sysmaster; 4 4 SELECT partdbsnum(partnum) DBSpace_Num, TRUNC(COUNT(*) * 2000) Free_Space_Needed 4 FROM sysdatabases 4 GROUP BY 1, ORDER BY 1; 4 SELECT dbsnum DBSpace_Num, sum(nfree) Spc_Avail 4 FROM syschunck 4 GROUP BY 1, ORDER BY 1; 8
Information Management IDS 10. 00 Migration: Checklist § SQL query plans for all regularly used queries (especially complex queries) using SET EXPLAIN ON. § dbschema -d -hd for all critical tables. The output will have distribution information. § oncheck -pr output that dumps all the root reserved pages § Copy of onconfiguration file. Note: oncheck –pr will not dump all the configuration parameters so it’s better to have a copy of onconfig file. § A list of all the environment variables that are set using ‘env’ unix command. 9
Information Management Ensure you take care of … § Remove Outstanding In-Place Alters § Close All Transactions § Verify the integrity of the data with oncheck § Flush ER Queues with any transactions, if applicable § Active HDR servers – disable it, if applicable § Level-0 archive/backup before migration § UNIX/Linux – may require Kernel Parameters to be modified § Verify source server is offline ** Recommended documented steps to shutdown current server properly: Run onmode -sy, onmode -l, onmode -c and then onmode -kuy. 10
Information Management v 10 Installer – easier install with GUI/Silent support Installer can be invoked by: 1. installserver [preferred] Uses bundled JVM, if JAVA not found in the PATH 2. Installserver –javahome [for users that want to use a local java] 3. Java –cp IIF. jar run [for users who want to invoke directly – advanced] 4. Installserver –legacy [extracts a legacy distribution] Additionally LINUX also packages the following RPMs [since 9. 40. UC 5] Product. rpm GLS. rpm Message. rpm For enhanced usability, a wrapper script is provided that invokes the RPMs in the correct sequence 11
Information Management Copy & customize configuration to target Unix or Linux Windows $INFORMIXDIR/etc/$ONCONFIG %INFORMIXDIR%etc%ONCONFIG% $INFORMIXDIR/etc/onconfig. std %INFORMIXDIR%etconconfig. std $INFORMIXDIR/etc/oncfg* %INFORMIXDIR%etconcfg* $INFORMIXDIR/etc/sm_versions %INFORMIXDIR%etcsm_versions $INFORMIXDIR/aaodir/adtcfg %INFORMIXDIR%aaodiradtcfg. * $INFORMIXDIR/dbssodir/adtmasks %INFORMIXDIR%dbssodiradtmasks. * $INFORMIXDIR/etc/sqlhosts %INFORMIXDIR%etcixbar. servernum $INFORMIXDIR/etc/tctermcap $INFORMIXDIR/etc/ixbar. servernum 12
Information Management Set Environment variables of Target Verify that the following environment variables are set to the correct values to migrate to the Target server INFORMIXSERVER ONCONFIG PATH INFORMIXSQLHOSTS 13
Information Management Perform Conversion § Start Dynamic Server 10. 00 ** No “oninit –ivy” please - will initialize disk! § Monitor the online message log 4 Wait for “Conversion Completed Successfully” status ** Do not bounce engine before conversion is complete ** Install and configure any Datablade Modules prior to conversion 14
Information Management Post Migration § Update Statistics (Low to drop, High on sysmaster, normal on your databases) § Verify the Integrity of the Data [oncheck; archecker] § For ON-Bar, Rename the sm_versions. std File § Make an Initial Backup of Dynamic Server 10. 00 § Tune Dynamic Server 10. 00 for Performance § Enable HDR and ER, if applicable ** Rerun the pre-migration checklist described in premigration for before/after picture comparison 15
Information Management IDS 10. 00 Migration: Using Replication Using Enterprise Replication to assist migration to 10. 00 4 Pros: § Allows mix of 7. 3 and 10. 00 versions § New transactions will queue up until the migrated server is available 4 Cons: § May get complicated to setup/administer § Table level replication only 16
Information Management Application Migration - Reserved Words New for 9. x New for 9. 4 § CACHE § COSTFUNC § ITEM § SELCONST § INNER § JOIN § LEFT § LOCKS § RETAIN § RAW § STANDARD § AVOID_EXECUTE § USE_SUBQF § AVOID_SUBQF § COLLATION § ONLINE § CROSS § OPTCOMPIND § FULL § PARTITION § INSTEAD § PASSWORD § RESTART § SAVE § RIGHT § TABLE § TEMPLATE New for v 10 § § § § ACTIVE DIRECTIVES ENCRYPTION HINT INACTIVE INLINE INOUT LOAD § TEST § TYPEID § TYPENAME § TYPEOF § WAIT § XADATASOURCE § XID 17
Information Management Application Migration and Client APIs ESQL/C 4 Simply upgrade to the latest version of CSDK and run the application. The new libraries will get loaded providing you enhanced functionality (like encryption). 4 In order to get new functionality like PAM, applications need to be modified ODBC/. NET/Java 4 Upgrade to a newer version of CSDK/JDBC driver and run the application 4 GL (v 7. 32. UC 3 ) 4 c 4 gl upgrades required applications to be recompiled/relinked. The version 7. 32. UC 3 removes this requirement. Simply upgrade to this version and run your c 4 gl applications (supported 7. 30. xc 6 onwards). P-code versions still require recompilation. 4 In order to get new functionality like DYNAMIC ARRAY, applications need to be changed 18
Information Management Informix Dynamic Server 10. 0 Performance. Reliability. Scalability. Focus on OLTP and Embedded Technology Enhancements over IDS v 7 4 Improved performance 4 Higher Availability 4 Increased Scalability 4 Enhanced Security 4 Simplified Administration 4 Easier Application Development 19
Information Management Improved Performance Highlights § 8 -15% Improvement over IDS 7. 31 § Fuzzy Checkpoints 4 Helps solve long checkpoint problem in 7 § Shared Statement Cache 4 Statement cache is now global rather than per connection § New Buffer Management System 4 Combines IDS victim selection with DB 2’s minimal I/O § B-Tree Scanner 4 Improves btree delete performance § Improved Replication Performance 4 Apply on Target 50% faster § Other Performance Improvements 4 Query optimizer improvements for special cases, up to 50% increase in rollback speed, better hash join and aggregate performance, memory allocation algorithms are much faster, hold cursors can now execute in parallel 20
Information Management High Availability Highlights § Restartable Fast Recovery 4 Fast recovery possible even if recovery crashes § Improved Rollback Performance 4 Up to 50% faster for long rollbacks § Dynamic Log Creation 4 Eliminates server hangs due to long transactions § Improved Enterprise Replication (ER) 4 Improved performance 4 Quick queue recovery 4 Large transaction support § ER / HDR Interoperability 4 HDR systems can participate in ER systems too. § Other Improvements 4 Collection support, multiple smart blob stable queues (logged/unlogged) 21
Information Management Increased Scalability Highlights § Big Chunks 4 Maximum chunk size raised to 4 TB 4 Maximum instance size is 128 Peta. Bytes § All utilities now handle large files § New Buffer Manager 4 Combines IDS and DB 2 strengths § Other improvements 4 Improved support more CPUs. 4 Reduced use of latches 4 Spin Lock for temporary partition improved 22
Information Management Enhanced Security Highlights § Encrypted Network Communications 4 Communication between client/server and server/server now encrypted 4 Uses cryptographic libraries from Open. SSL § Optionally encrypts ER traffic 4 Useful for long haul networks, traffic sent over internet 4 Port can be configured for just ER traffic § No need for trusted hosts 23
Information Management Simplified Administration Highlights § Redirected Restore § § § 4 Recovery on non-identical systems Full use of Tapes 4 Will write to end of tape Rename Chunks 4 Rename during restore New Unix Bundle Installer No libraries in /usr/lib Order of Install Add Chunks when first chunk is full 4 Now can add disk space if chunk 0 is completely full § Onstat enhancements 4 Can now display environment variables, list of all prepared statements in a session, § Explain enhancements 4 Explain turned on dynamically § Log Management 4 Logs added/dropped online § Dynamic Lock Allocation 4 Locks table shrinks/grows dynamically 24
Information Management Application Development: Enhanced SQL Highlights § Support for Long Identifiers § Updated Unicode Support § Sequences § Triggers on Select and Views § Order by not in select list § ANSI SQL-99 Joins § Describe input § Unions in Sub-queries § Names for Return Values § Multiple OUT parameters § Improved support for long character strings § Multi-nationalization 25
Information Management Application Development: Extensibility Highlights § Collections § Table Functions § Virtual Table interface § User Defined Data Types § High-Performance Programming Support § Built-in functions for handling complex data 26
Information Management Application Development: Enhanced APIs § CSDK 2. 90 § JDBC Enhancements plus WAS integrations § OLE DB Enhancements § ODBC Enhancements § ESQL/C Enhancements § Native. Net Provider 27
Information Management Informix Dynamic Server 10. 0 Performance. Reliability. Scalability. New Features in 10. 00. XC 2 through 10. 00 XC 5 28
Information Management New Features in 10. 00. XC 2 § First release of the IDS Express Edition § Restricted functionality in the Express Edition: 4 Limited to 2 CPUs 4 Memory limited to 4 GB 4 High-Availability Data Replication not available 4 Enterprise Replication not available 4 Parallel operations not supported § Parallel queries, backup/restore, High Performance Loader 4 Available on Windows and Linux (AMD, Intel) 4 No 64 bit support 29
Information Management New Features in 10. 00. XC 3 § ANSI-joins in distributed queries perform better 4 Tables now joined in the remote database rather than joining tables locally § Transaction support for XA-compliant external data sources 4 Conforms to X/Open XA interface standards for prepare/commit/rollback § MQ Data. Blade module 4 Presents MQ data as a relational table 4 Table is read/write § New DBCREATE_PERMISSION configuration parameter to restrict the ability to create databases 4 If set only user Informix and the user specified can create databases § New secure default directory for the DUMPDIR configuration parameter 4 Previously DUMPDIR defaulted to /tmp, now it defaults to $INFORMIXDIR/tmp on UNIX and %INFORMIXDIR%tmp on Windows 30
Information Management New Features in 10. 00. XC 3 - Continued § Table-level restore for smart large object columns 4 TLR now supports Smart Large Objects for restore from level-0 archive § AES cipher support for network encryption 4 Network encryption now supports aes, aes 128, aes 192, and aes 256 keys § New Enterprise Replication commands to show statistics information 4 The “cdr stats rqm” command shows info about the reliable queue manager 4 The “cdr stats recv” command shows info about the receiver parallelism and latency statistics by source node § Client SDK included in Dynamic Server installation process 4 Install now lets you install CSDK ver 2. 90. XC 3 simultaneously with 10 or separately § Enhanced Support for Retrieving Subsets of Query Results 4 New keyword FIRST only returns the first “N” rows of results 4 New keyword SKIP in projection list skips the first “N” rows of results 4 Keywords can be combined to skip N rows then return following first M rows 31
Information Management New Features in 10. 00. XC 3 - Continued § Ordering subsets of query results in collection-derived tables 4 ORDER BY clause now supported in collection subqueries 4 Data is sorted before FIRST/SKIP is applied § J/Foundation upgrade to JRE 1. 4. 2 4 J/Foundation component now includes JRE 1. 4. 2 § New default directory for ADTPATH configuration parameter 4 Previously ADTPATH defaulted to /tmp, now it defaults to $INFORMIXDIR/aaodir on UNIX and %INFORMIXDIR%aaodir on Windows § New UNSECURE_ONSTAT configuration parameter 4 UNSECURE_ONSTAT can be set to allow users other than DBA to use onstat § Set to 1 to allow anyone to use onstat § If missing or value other than 1 only allow DBA’s to use onstat For more info see: http: //publib. boulder. ibm. com/infocenter/idshelp/v 10/index. jsp? topic=/com. ibm. sqls. doc/sqls 02. htm 32
Information Management New Features in 10. 00. XC 4 § TRUNCATE table support 4 This command allows all rows to be deleted from a table and its indexes very quickly § Enterprise Replication direct synchronization 4 The new “cdr sync replicate” and “cdr sync replicateset” commands perform synchronization between a reference server and one or more targets 4 These commands check consistency and optionally repair inconsistent rows § Enhanced support for IPv 6 4 OS support for Internet protocol version 6 (IPv 6) is automatically checked for § Secure local connections 4 The new SECURITY_LOCALCONNECTION config parameter makes connections more secure § Set to 0 for no extra security § Set to 1 to verify that user running program matches database user § Set to 2 the same as 1 but will also source IP is really coming from the indicated client program. Requires OS support for DOCTCP or IPCSTR 33
Information Management New Features in 10. 00. XC 4 - Continued § Secure Data. Blade module paths 4 The DB_LIBRARY_PATH config param defines paths which the database can load datablades § Parallel backup and restore is more efficient 4 The BAR_SORT_DBS env variable improves backup/restore when scope is not the whole system 4 Set to any value to enable to functionality § DB-Access stops a process after the first error 4 Invoked as “dbaccess –a” § Informix Interface for TSM supports HP-UX (Itanium) 4 Onbar can now be used with the Tivoli storage manager on HP-UX (Itanium) § New default value for IFX_EXTEND_ROLE configuration parameter 4 IFX_EXTEND_ROLE default changed from 0 to 1 § 1 means only those granted extend role can register external functions such as in datablades 34
Information Management New Features in 10. 00. XC 5 § Demo server now uses TCP instead of shared memory connections 4 Allows demo database to be connected to remotely § Install of demo server now checks uniqueness of SERVERNUM 4 Checks to see if any other demo instance has same servernum § If silent install changes the port number it will log the change in its log § MQ datablade 2. 0 now supported on Linux 32 bit, Linux p. Series 64 bit, and Solaris 64 bit § JRE 1. 4. 2 upgrade for ISA and ISMP 4 Previous JRE has security hole § New Binary data type support - var binary and binary 18 for Portal 4 Required by Websphere Portal 35
Information Management Informix Dynamic Server 10. 0 Performance. Reliability. Scalability. BAAN Benchmark Performance Enhancements 36
Information Management BAAN Benchmark Performance Features in 10. 00. XC 5 § Improved internal latch performance 4 Avoids grabbing latches whenever possible § Don’t sequential scan user table on every prepare statement 4 Now keeps a cache of users with DBA privileges § Do not sort if only one row being returned § Improved ASF polling performance 4 Uses pollset OS interface (when available) rather than select() or poll() 4 Enable with FASTPOLL = 1 in config file 4 Not supported on AIX, DEC, and SGI § Avoid hash joins when using first rows optimizer directive 4 In this case nested loops will always be faster 4 May be able to avoid a sort this way 37
Information Management BAAN Benchmark Performance Features in 10. 00. XC 5 - Continued § Improved memory management when using lots of memory (> 20 GB) 4 Searching the memory cache for a best fit block can be slow 4 This search can be disable by setting VP_MEMORY_CACHE_KB to 0 4 Be sure (VP_MEMORY_CACHE_KB * num cpu vps) < (40% SHMTOTAL) 4 This feature can be changed dynamically with “onmode -wm VP_MEMORY_CACHE_KB=<value>" § SINGLE_CPU_VP configuration performance improvement § Improved performance when searching bit maps 4 Uses IFX_SORT_POOLS § Now treats "col 1>=X and col 1 <= X" as "col 1=X“ 4 This reduced query time on some queries from >50 sec. to about 10 sec. 38
Information Management BAAN Benchmark Performance Features in 10. 00. XC 5 - Continued § Start/Stop keys not set correctly for some query predicates 4 Composite indexes might perform slowly with range predicates 4 For instance, given an index defined on columns (a, b, c, d), and the filters “a = 2 and b >=1 and b <= 7 and c >= 10 and c <= 20” previously we would only use filters on columns (a, b). Now we use (a, b, c). 4 A side effect is that explain output has changed § In the above case the output would change from saying “Key-First Filters” to saying “Index Key Filters”. § Improved performance when using many prepare statements 4 Previously if many prepared statements were open at commit time it would take a long time to commit. 39
Information Management 40
Information Management 41
Скачать презентацию Information Management IBM Informix May 2006 Chat
00a8e3d98b90688a598bfd02a0bc130a.ppt