Information Management Upgrading to IDS version 10

Скачать презентацию Information Management Upgrading to IDS version 10

0ebfefe00442f0f2b039b02062cb148a.ppt

Количество слайдов: 52

Information Management Informix Dynamic Server 10. 0 Performance. Reliability. Scalability. Focus on OLTP and Embedded § IDS v 10 – Why upgrade? § Migration to v 10 § Migration impact on existing applications § Customer Migration Use Case discussion § Q&A 2

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 3

Information Management Why upgrade? IDS has been busy with v 9/v 10 detached idxs 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 Performance Enhancements § IDS 10. 0 provides the best performance of any IDS engine ever produced! § 15 - 20% Improvement over IDS 7. 31 4 As evidenced by internal TPC-C benchmarks 4 As evidenced by our key customers and partners § Fuzzy Checkpoints § Shared Statement Cache § New Buffer Management System § B-Tree Scanner § Configurable Page Size § Memory for Non-PDQ Queries § External Optimizer Directives § Dynamic OPTCOMPIND 5

Information Management High Availability § Restartable Fast Recovery § Improved Rollback Performance § Dynamic Log Creation § Improved Enterprise Replication (ER) § ER / HDR Interoperability § Table Level Restore § Online Index Drop and Rebuild § DRAUTO [out in v 9; back in v 10] § Resending Indexes in HDR § Alter of Replicated Tables § ER Templates § Online Resync of ER nodes 6

Information Management Scalable in all Dimensions Transactions: 1000’s of concurrent users Huge data volumes Deployment: distributed across 1000 s of sites Types of data: no limit to new data types § Big Chunks § Improved Support for Large Objects § SHMEM >4 G § Multiple Table Fragments in a Single DBspace § … 7

Information Management Security Enhancements § Encrypted Network Communication § Column Level Encryption § Secure environment check before startup § Trigger Introspection § Data. Blade/UDR registration restriction § PAM Authentication § Denial of Service Attacks 8

Information Management § § § § Redirected Restore Full use of Tapes Rename Chunks New Unix Bundle Installer No libraries in /usr/lib Order of install Add Chunks when first chunk is full § Onstat enhancments § Explain enhancements Lo D g H yna Ma Si DR m na Tr ng se ic ge M igg le tu Lo m D ore er Us p w ck en O efa V ing er it Al t R nta ult ER A Mo h E loc Tb ena pe R S LA de B ati R on ls m us ole ION RM pa e e In PR ce db o fo O Tb sp f ST rm G ls ace D at RA pa IO io M n ce M. . . an ag em en t Embeddability - Simplified Administration 9

Information Management Embeddability – Easier Application development Extensibility Enhanced SQL Support 4 Support for Long Identifiers 4 Updated Unicode Support 4 Sequences 4 Triggers on Select and Views 4 Order by not in select list 4 ANSI SQL-99 Joins 4 Describe input 4 Unions in Sub-queries 4 Names for Return Values 4 Multiple OUT parameters 4 Improved support for long character strings 4 Multi-nationalization 4 Collections 4 Table Functions 4 Virtual Table interface 4 User Defined Data Types 4 High-Performance Programming Support 4 Built-in functions for handling complex data Application Development Enhancements Ø CSDK 2. 90 Ø. NET, OLE DB, ODBC & MS Tooling Ø ESQL/C Enhancements Ø JDBC and WAS Integration 10

Information Management Upgrading to v 10 - Migration facts § Migrating from prior versions of Informix Dynamic Server v IDS 7. 24, 7. 31, 9. 21, 9. 3, 9. 4 upgrade directly to IDS v 10 v IDS 7. 22, and IDS 7. 20 require an interim upgrade to IDS 7. 31 v IDS 9. 14 requires an interim upgrade to IDS 9. 30 v Migrating from 32 -bit to 64 -bit uses the same inline conversion process v All other migrations will require other migration tools like dbexport/dbimport, HPL, etc including different operating systems 11

Information Management Converting to a Newer Database Server Source Database Server Target Database Server Reference Dynamic Server 9. 40 Dynamic Server 10. 00 Preparing for Migration and Converting to Dynamic Server 10. 0 Universal Server 9. 14 Dynamic Server 9. 30 The Version 9. 30 Migration Guide On. Line Dynamic Server 7. 23 Dynamic Server 7. 31 The Version 7. 31 Migration Guide Dynamic Server 9. 30 Dynamic Server 9. 20 or Dynamic Server 9. 21 Dynamic Server 7. 3 x Dynamic Server 7. 24 or Workgroup Edition 7. 24 On. Line Dynamic Server 7. 22 On. Line 5. 1 x 12

Information Management Migrating from prior versions of IDS 13

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! 14

Information Management Informix Dynamic Server 10. 0 Performance. Reliability. Scalability. Focus on OLTP and Embedded § Pre-Migration § Install and configuration § Validate your target environment § Perform conversion § Validate data integrity 15

Information Management Extensive Platform Support Intel EM 64 T/AMD 64 HP-UX on PA-RISC and Itanium. Tru 64 on Alpha IRIX Solaris on SPARC AIX on p. Series Linux on x. Series, z. Series, p. Series* and i. Series* Windows 2000, 2003 and XP on x 86 http: //www-306. ibm. com/software/data/informix/ids/requirements. html 16

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. 17

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; 18

Information Management Also plan for … § Networking issues 4 IP addresses (if using a new box) 4 Client connectivity § Application compatibility 4 3 rd Party Applications (Storage Managers, Monitoring Tools, Scripts) § Replication 4 Extra scripts to run. Refer to Migration Guide for details. § Testing plan or Staging environment 4 Try it out before you apply on your production system § Back-Out Plan 4 In case of emergency. Always good to prepare for contingencies 4 Save data that can help Tech Support diagnose issues that may arise 19

Information Management IDS 10. 00 Migration: Planning (cont. ) § Testing/Staging Plan 4 Test the actual migration plan for success, and to estimate time to complete 4 Test all aspects of your system including § Client access § Major application functions, Reporting, Key SQL Statements § Stored Procedures, Triggers, Distributed transactions § Backup and Restore § Simulated user load § For 9. x migrations – test any UDR’s and Data. Blade functions § Back-Out Plan 4 Consider steps required to back-out of the migration at each step in the process 4 Take a level-0 archive before migration starts 4 Make sure the archive is good - use archecker to check it. 4 Consider taking another form of backup – such as: § An offline OS level backup of the chunks § A dbexport of key databases (just in case) 20

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. 21

Information Management IDS 10. 00 Migration: Checklist § During times of peak usage, 4 online. log snippet, with some checkpoint durations in it. 4 onstat –a. F, -g all, -g stk all. § During times of peak usage, run the following onstats repetitively with r repeat option for a period of about 3 to 5 minutes: 4 onstat -u, to see the total number of sqlexecs used. 4 onstat -p, for read and write cache rates, to detect deadlocks and the number of sequential scans. 4 onstat -g nta, a consolidated output of -g ntu, ntt, ntm and ntd. 4 onstat -g nsc, -g nsd, -g nss for status of shared memory connections. 4 onstat -P, -g tpf, -g ppf 4 vmstat, iostat and sar, for cpu utilization. Run these repetitively for a period of about 3 to 5 minutes. Check man pages for usage. 4 timex of all regularly run queries. 22

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. 23

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 on LINUX also package 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 25

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 26

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 28

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 30

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 32

Information Management Migration of an IDS running on a different OS § Pre-migration Steps for the Source Database Server 4 Save copies of the current configuration files 4 Take a level 0 backup of the database server 4 Choose one of the following migration tools to unload the databases § Dbexport and dbimport § UNLOAD, dbschema and LOAD § UNLOAD, dbschema and dbload 4 Bring the source database server offline § Install and configure the Target database server § Use dbimport, LOAD or dbload to load the databases into the target database server. § Post-migration Steps for the Target Database Server 4 Take a level 0 backup of the database server 4 Run UPDATE STATISTICS 33

Information Management Choosing a Migration method § Migration on Same Operation System 4 Use Direct Conversion/Reversion (when allowed between the IDS versions) § Migration on Different Operation Systems 4 Dbexport and dbimport § Use these utilities to move an entire database 4 UNLOAD, dbload and dbschema § To move selected columns and tables, use UNLOAD statement. Use dbload to change the data format 4 UNLOAD and LOAD statements § To move selected columns and tables, use UNLOAD statement. Use LOAD when you do NOT want to change the data format 4 onload and onunload utilities § To unload data in page-sized chunks use onunload utility § Use onload to move data to an identical database server on computer of same type 4 High Performance Loader (HPL) § To move selected columns and tables or an entire database, use HPL § Consulting provides automated migration services using HPL 34

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 35

Information Management Reverting from Dynamic Server v 10 § Determine Whether Reversion Is Possible 4 Does the schema file contain SQL statements or Features like long identifiers that the earlier database server does not support? 4 Have any new SPL routines been created in IDS v 10, or have you imported existing routines using dbimport? 4 Have tables or indexes using expression fragmentation had expressions changed or new fragments added? 4 Have any new objects and extensions that the old database server does not support been created. Eg: Stprocs(), UDRs, UDTs, etc. , 4 Have any new check constraints or triggers been created? 4 … [refer to Migration Guide for details] § Pre-Reversion Steps 4 4 4 4 Check and Configure available space Save copies of the current configuration files Verify the integrity of data using oncheck Take a level 0 backup of the database server Remove pending in-place alters Remove usage of any new feature adoption Remove Blade. Manager extensions If necessary, disable HDR 36

Information Management Reverting from Dynamic Server v 10 (Contd. ) Run the reversion utility onmode -b 4 Check for the "Reversion Complete" message in the online message log Post-Reversion Steps 4 Modify the configuration parameters and reset necessary env variables 4 Reset environment variables 4 If necessary, remove any Communications Support Module settings 4 Run Update statistics 4 Use oncheck to verify the integrity of data. 4 Take a level 0 backup of the database server 4 Return the target database server to online mode 37

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 38

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 39

Information Management “To sum it all up, IDS 10. 0 is a DBA’s dream come true. ” Gary Ben-Israel CIO, National Institute for Testing and Evaluation Jerusalem, Israel “Companies that need their data and transaction resources to be available 24 x 7 will be pleased with the release of IDS 10. 0. With this new release, organizations can tune their database and manage index issues online, without interrupting service. . ” Gerd Kaluzinski Senior Systems Engineer BYTEC Gmb. H “… Table level restore enables us to implement a BCP plan without the need for duplicate servers. ” David A. Link IS Manager, West Corporation 40

Information Management Informix Dynamic Server 10. 0 Performance. Reliability. Scalability. Focus on OLTP and Embedded Customer v 10 MIGRATION USE CASE Discussion BYTEC Gmb. H - Hermann-Metzger-Strasse 7 - 88045 Friedrichshafen 16/03/2018 gerd. kaluzinski@bytec. de 41

Information Management BYTEC – Customer profiles § Here the most recent migrations from our customers: [earlier at v 7] 9. 21. UC 3 -> 9. 21. UC 4 -> 9. 30. FC 3 -> 9. 21. FC 2 -> 10. 00. UC 3 on Linux Red. Hat Advanced Server 3 (soon 4) 9. 40. UC 6 on Suse 9. 40. FC 6 on Solaris 8 9. 40. FC 6 on HP-UX 11 i § All systems running 95% OLTP § IDS instance size between 40 GB and 200 GB with ~150 - 650 Connections to the database § One is a system running with Baa. N. This one has 40'000 tables. The other ones are in the range from 500 to 3000 tables § The number of dbspaces goes from 8 to 24 & the number of chunks goes from 12 to 42. All instances had upto 10 databases. BYTEC Gmb. H - Hermann-Metzger-Strasse 7 - 88045 Friedrichshafen 42

Information Management BYTEC – Customer profiles (contd. . ) § The In. Place migration took up to 12 hours including oncheck after migration on the big systems. We had up to 2 hours without access, after this we allowed read-only until the checks have passed. § Prepare for: Pseudo updates to 9. 40 take a lot of LOCKS and you can run into OVERLOCKS. Physical log must be big enough and/or PLOG_OVERFLOW_PATH set. If physlog overflow occurs during migration you have to start again. Some new reserved words have been introduced (ref, status, . . . ) and so you have to check your application on a test server against this. § Benefits: Response times in application have been reduced without changing the application. A backup concept, where every night a dbexport blocked the database was changed by using the new feature "table level restore". ** Reversion was not tested on these systems as there was no requirement BYTEC Gmb. H - Hermann-Metzger-Strasse 7 - 88045 Friedrichshafen 43

Information Management BYTEC’s Migration Checklist 1. Preparation Shutdown User Connections (Dataconsistency) Create and Check last Backup (ontape, dbexport) Increase Parameter LOCKS up to 1'000 Increase Physical Log used for conversion and set Environment PLOG_OVERFLOW_PATH oncheck -c. DI (each database) oncheck -cr / oncheck -c. R oncheck -cc Check installed products § (Checks can be made the days before migration) BYTEC Gmb. H - Hermann-Metzger-Strasse 7 - 88045 Friedrichshafen 44

Information Management BYTEC’s Migration Checklist 2. First Steps Update statistics Low drop Distributions (each database) Remove CDR (cdr delete server) Unregister Datablades from all databases Deactivate your RC-Scripts Make sure all logs have been backed up (no U-----) Change LTAPEDEV in ONCONFIG to /dev/null Shutdown using onmode -ucky Save a copy of „onconfig“ and „sqlhosts“ Backup INFORMIXDIR (for example using tar) BYTEC Gmb. H - Hermann-Metzger-Strasse 7 - 88045 Friedrichshafen 45

Information Management BYTEC’s Migration Checklist 3. Install new software Check permissions (INFORMIXDIR) Install the new Products (Tools-Engine-SDK) Modify UNIX-Kernel (see Release Notes) Check KAIO-settings (see Release Notes) (for example on HP /etc/privgroup) Make changes in onconfig restore sqlhosts BYTEC Gmb. H - Hermann-Metzger-Strasse 7 - 88045 Friedrichshafen 46

Information Management BYTEC’s Migration Checklist 4. Start new server oninit -v to see all steps Check online. log to see Conversion and Messages Execute ONMODE -BC 1 and ONMODE -BC 2 (Big. Chunks) oncheck -cr / oncheck -cc oncheck -c. I (each database) oncheck -c. D (each database) update statistics high (on leading indexcolumns) BYTEC Gmb. H - Hermann-Metzger-Strasse 7 - 88045 Friedrichshafen 47

Information Management BYTEC’s Migration Checklist 5. Backup and Documentation Take the first backup of the new version Initiate CDR Register Datablades Activate RC-Scripts Make a snapshot of your installation (onstat -a) BYTEC Gmb. H - Hermann-Metzger-Strasse 7 - 88045 Friedrichshafen 48

Information Management Review of Migrations Following the checklist all Migrations went without problems within the calculated time Among these Migrations have been Hospitals, where the complete Migration with more than 50 Chunks could be made within 2 hours of downtime. Other customers situated in Metal Industries, Fashion manufacturers as well as Public Sites (Libraries and Universities) have been migrated In. Place. BYTEC Gmb. H - Hermann-Metzger-Strasse 7 - 88045 Friedrichshafen 49

Information Management Review of Migrations Here one example of Configuration, which has been migrated. You see all kind of Dbspaces used at customersite. Medical images are stored in Smartblobs, Doctor-Letters are in Blobspaces: Dbspaces address number fags fchunk nchunks flags name 892 ba 7 d 8 1 0 x 40001 1 1 N B rootdbs 89 f 18 ad 0 2 0 x 40001 2 25 N B datadbs 89 f 18 c 20 3 0 x 40011 12 20 N BB blobdbs 89 f 18 d 70 4 0 x 42001 22 1 N TB tempdbs 89 f 18 ec 0 5 0 x 40001 24 3 N B logdbs 89 f 19018 6 0 x 42001 23 1 N TB tempdbs 2 b 3 b 7 aea 8 7 0 x 48001 52 12 N SB sbspace BYTEC Gmb. H - Hermann-Metzger-Strasse 7 - 88045 Friedrichshafen 50

Information Management 51

Information Management 52