IBM Informix May 2006 Chat with the Lab

1. IBM Informix May 2006 Chat with the Lab IDS 10: Upgrading and New Features

2. Informix Dynamic Server 10.0 Performance. Reliability. Scalability. Topics • New features in 10.00.XC2 through XC5 • BAAN enhancements • Migrating to v10 • Q&A

3. Informix Dynamic Server 10.0 Performance. Reliability. Scalability. Migrating to v10 • Why Upgrade? • Migration matrix • Pre-Migration • Install and configuration • Validate your target environment • Perform conversion

4. onarchive gone btree scanners Why upgrade? IDS has been busy with v9/v10 buffer manager Config page size HDR/ER update statistics detached indexes Col Level Encryption large Chunk& files dynamic logging raw tables Fuzzy ckpts ER Resync optimizer directives PIT-TLR Shmem > 4 gig dynamic locking restartable fast recovery 9.4 9.2 9.3 10.0 IDS Versions – Key features by release

5. Migrating from prior versions of IDS

6. Upgrading to v10 - Conversion Steps Five easy steps to remember for data migration: • Get the source server ready by following pre-migration steps • Install the target database server and copy over relevant $INFORMIXDIR/etc configuration from source to target • Validate your environment settings are pointing at target • CONVERT by starting target server • Validate data integrity and take level 0 backup CONVERSION COMPLETE!

7. System Requirements • Requirements for a new Installation of IDS v10 • 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.

8. Space Required for Upgrade • Root Chunk (Chunk 0) should have at least 10% free. • 3000 Free pages of Logical Log Space to rebuild SysMaster • 2000 KB of Free Space per Database is need for each DB Space • Partition Headers should not be full • Use oncheck –me to compress extents • Determine how much space is needed by running: • DATABASE sysmaster; • SELECT partdbsnum(partnum) DBSpace_Num, • TRUNC(COUNT(*) * 2000) Free_Space_Needed • FROM sysdatabases • GROUP BY 1, ORDER BY 1; • SELECT dbsnum DBSpace_Num, sum(nfree) Spc_Avail • FROM syschunck • GROUP BY 1, ORDER BY 1;

9. 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 onconfig configuration 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.

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

11. v10 Installer – easier install with GUI/Silent support Installer can be invoked by: • installserver [preferred] Uses bundled JVM, if JAVA not found in the PATH • Installserver –javahome [for users that want to use a local java] • Java –cp IIF.jar run [for users who want to invoke directly – advanced] • Installserver –legacy [extracts a legacy distribution] Additionally LINUX also packages the following RPMs [since 9.40.UC5] Product.rpm GLS.rpm Message.rpm For enhanced usability, a wrapper script is provided that invokes the RPMs in the correct sequence

12. Copy & customize configuration to target

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

14. Perform Conversion • Start Dynamic Server 10.00 ** No “oninit –ivy” please - will initialize disk! • Monitor the online message log • Wait for “Conversion Completed Successfully” status ** Do not bounce engine before conversion is complete ** Install and configure any Datablade Modules prior to conversion

15. 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 pre-migration for before/after picture comparison

16. IDS 10.00 Migration: Using Replication Using Enterprise Replication to assist migration to 10.00 • Pros: • Allows mix of 7.3 and 10.00 versions • New transactions will queue up until the migrated server is available • Cons: • May get complicated to setup/administer • Table level replication only

17. New for 9.4 COLLATION CROSS FULL INSTEAD RESTART RIGHT New for v10 ACTIVE DIRECTIVES ENCRYPTION HINT INACTIVE INLINE INOUT LOAD New for 9.x CACHE COSTFUNC ITEM SELCONST INNER JOIN LEFT LOCKS RETAIN RAW STANDARD AVOID_EXECUTE USE_SUBQF AVOID_SUBQF Application Migration - Reserved Words • ONLINE • OPTCOMPIND • PARTITION • PASSWORD • SAVE • TABLE • TEMPLATE • TEST • TYPEID • TYPENAME • TYPEOF • WAIT • XADATASOURCE • XID

18. Application Migration and Client APIs ESQL/C • Simply upgrade to the latest version of CSDK and run the application. The new libraries will get loaded providing you enhanced functionality (like encryption). • In order to get new functionality like PAM, applications need to be modified ODBC/.NET/Java • Upgrade to a newer version of CSDK/JDBC driver and run the application 4GL (v 7.32.UC3 ) • c4gl upgrades required applications to be recompiled/relinked. The version 7.32.UC3 removes this requirement. Simply upgrade to this version and run your c4gl applications (supported 7.30.xc6 onwards) . P-code versions still require recompilation. • In order to get new functionality like DYNAMIC ARRAY, applications need to be changed

19. Informix Dynamic Server 10.0 Performance. Reliability. Scalability. Focus on OLTP and Embedded Technology Enhancements over IDS v7 • Improved performance • Higher Availability • Increased Scalability • Enhanced Security • Simplified Administration • Easier Application Development

20. Improved Performance Highlights • 8-15% Improvement over IDS 7.31 • Fuzzy Checkpoints • Helps solve long checkpoint problem in 7 • Shared Statement Cache • Statement cache is now global rather than per connection • New Buffer Management System • Combines IDS victim selection with DB2’s minimal I/O • B-Tree Scanner • Improves btree delete performance • Improved Replication Performance • Apply on Target 50% faster • Other Performance Improvements • 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

21. High Availability Highlights • Restartable Fast Recovery • Fast recovery possible even if recovery crashes • Improved Rollback Performance • Up to 50% faster for long rollbacks • Dynamic Log Creation • Eliminates server hangs due to long transactions • Improved Enterprise Replication (ER) • Improved performance • Quick queue recovery • Large transaction support • ER / HDR Interoperability • HDR systems can participate in ER systems too. • Other Improvements • Collection support, multiple smart blob stable queues (logged/unlogged)

22. Increased Scalability Highlights • Big Chunks • Maximum chunk size raised to 4TB • Maximum instance size is 128 PetaBytes • All utilities now handle large files • New Buffer Manager • Combines IDS and DB2 strengths • Other improvements • Improved support more CPUs. • Reduced use of latches • Spin Lock for temporary partition improved

23. Enhanced Security Highlights • Encrypted Network Communications • Communication between client/server and server/server now encrypted • Uses cryptographic libraries from OpenSSL • Optionally encrypts ER traffic • Useful for long haul networks, traffic sent over internet • Port can be configured for just ER traffic • No need for trusted hosts

24. Redirected Restore Recovery on non-identical systems Full use of Tapes Will write to end of tape Rename Chunks Rename during restore New Unix Bundle Installer No libraries in /usr/lib Order of Install Add Chunks when first chunk is full Now can add disk space if chunk 0 is completely full Onstat enhancements Can now display environment variables, list of all prepared statements in a session, Explain enhancements Explain turned on dynamically Log Management Logs added/dropped online Dynamic Lock Allocation Locks table shrinks/grows dynamically Simplified Administration Highlights

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

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

27. Application Development: Enhanced APIs • CSDK 2.90 • JDBC Enhancements plus WAS integrations • OLE DB Enhancements • ODBC Enhancements • ESQL/C Enhancements • Native .Net Provider

28. Informix Dynamic Server 10.0 Performance. Reliability. Scalability. New Features in 10.00.XC2 through 10.00XC5

29. New Features in 10.00.XC2 • First release of the IDS Express Edition • Restricted functionality in the Express Edition: • Limited to 2 CPUs • Memory limited to 4 GB • High-Availability Data Replication not available • Enterprise Replication not available • Parallel operations not supported • Parallel queries, backup/restore, High Performance Loader • Available on Windows and Linux (AMD,Intel) • No 64 bit support

30. New Features in 10.00.XC3 • ANSI-joins in distributed queries perform better • Tables now joined in the remote database rather than joining tables locally • Transaction support for XA-compliant external data sources • Conforms to X/Open XA interface standards for prepare/commit/rollback • MQ DataBlade module • Presents MQ data as a relational table • Table is read/write • New DBCREATE_PERMISSION configuration parameter to restrict the ability to create databases • If set only user Informix and the user specified can create databases • New secure default directory for the DUMPDIR configuration parameter • Previously DUMPDIR defaulted to /tmp, now it defaults to $INFORMIXDIR/tmp on UNIX and %INFORMIXDIR%\tmp on Windows

31. New Features in 10.00.XC3 - Continued • Table-level restore for smart large object columns • TLR now supports Smart Large Objects for restore from level-0 archive • AES cipher support for network encryption • Network encryption now supports aes, aes128, aes192, and aes256 keys • New Enterprise Replication commands to show statistics information • The “cdr stats rqm” command shows info about the reliable queue manager • 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 • Install now lets you install CSDK ver 2.90.XC3 simultaneously with 10 or separately • Enhanced Support for Retrieving Subsets of Query Results • New keyword FIRST only returns the first “N” rows of results • New keyword SKIP in projection list skips the first “N” rows of results • Keywords can be combined to skip N rows then return following first M rows

32. New Features in 10.00.XC3 - Continued • Ordering subsets of query results in collection-derived tables • ORDER BY clause now supported in collection subqueries • Data is sorted before FIRST/SKIP is applied • J/Foundation upgrade to JRE 1.4.2 • J/Foundation component now includes JRE 1.4.2 • New default directory for ADTPATH configuration parameter • Previously ADTPATH defaulted to /tmp, now it defaults to $INFORMIXDIR/aaodir on UNIX and %INFORMIXDIR%\aaodir on Windows • New UNSECURE_ONSTAT configuration parameter • 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/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls02.htm

33. New Features in 10.00.XC4 • TRUNCATE table support • This command allows all rows to be deleted from a table and its indexes very quickly • Enterprise Replication direct synchronization • The new “cdr sync replicate” and “cdr sync replicateset” commands perform synchronization between a reference server and one or more targets • These commands check consistency and optionally repair inconsistent rows • Enhanced support for IPv6 • OS support for Internet protocol version 6 (IPv6) is automatically checked for • Secure local connections • 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

34. New Features in 10.00.XC4 - Continued • Secure DataBlade module paths • The DB_LIBRARY_PATH config param defines paths which the database can load datablades • Parallel backup and restore is more efficient • The BAR_SORT_DBS env variable improves backup/restore when scope is not the whole system • Set to any value to enable to functionality • DB-Access stops a process after the first error • Invoked as “dbaccess –a” • Informix Interface for TSM supports HP-UX (Itanium) • Onbar can now be used with the Tivoli storage manager on HP-UX (Itanium) • New default value for IFX_EXTEND_ROLE configuration parameter • IFX_EXTEND_ROLE default changed from 0 to 1 • 1 means only those granted extend role can register external functions such as in datablades

35. New Features in 10.00.XC5 • Demo server now uses TCP instead of shared memory connections • Allows demo database to be connected to remotely • Install of demo server now checks uniqueness of SERVERNUM • 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 pSeries 64 bit, and Solaris 64 bit • JRE 1.4.2 upgrade for ISA and ISMP • Previous JRE has security hole • New Binary data type support - var binary and binary18 for Portal • Required by Websphere Portal

36. Informix Dynamic Server 10.0 Performance. Reliability. Scalability. BAAN Benchmark Performance Enhancements

37. BAAN Benchmark Performance Features in 10.00.XC5 • Improved internal latch performance • Avoids grabbing latches whenever possible • Don’t sequential scan user table on every prepare statement • Now keeps a cache of users with DBA privileges • Do not sort if only one row being returned • Improved ASF polling performance • Uses pollset OS interface (when available) rather than select() or poll() • Enable with FASTPOLL = 1 in config file • Not supported on AIX, DEC, and SGI • Avoid hash joins when using first rows optimizer directive • In this case nested loops will always be faster • May be able to avoid a sort this way

38. BAAN Benchmark Performance Features in 10.00.XC5 - Continued • Improved memory management when using lots of memory (> 20GB) • Searching the memory cache for a best fit block can be slow • This search can be disable by setting VP_MEMORY_CACHE_KB to 0 • Be sure (VP_MEMORY_CACHE_KB * num cpu vps) < (40% SHMTOTAL) • 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 • Uses IFX_SORT_POOLS • Now treats "col1>=X and col1 <= X" as "col1=X“ • This reduced query time on some queries from >50 sec. to about 10 sec.

39. BAAN Benchmark Performance Features in 10.00.XC5 - Continued • Start/Stop keys not set correctly for some query predicates • Composite indexes might perform slowly with range predicates • 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). • 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 • Previously if many prepared statements were open at commit time it would take a long time to commit.