1 / 181

Carlton Doe and his session – aka the warm-up band for Mark Scranton

Carlton Doe and his session – aka the warm-up band for Mark Scranton. Moving from IBM IDS 7.x to IBM IDS 9.x. Carlton Doe Technical Sales Manager, Dallas, TX IBM. ?. ISBN 0-13-605296-7. ISBN 0-13-080533-5. Who I Am.

eldora
Download Presentation

Carlton Doe and his session – aka the warm-up band for Mark Scranton

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Carlton Doe and his session –akathe warm-up band forMark Scranton

  2. Moving from IBM IDS 7.x to IBM IDS 9.x Carlton Doe Technical Sales Manager, Dallas, TX IBM

  3. ? ISBN 0-13-605296-7 ISBN 0-13-080533-5 Who I Am • 12 + years of experience as DBA and engine admin mainly in retail environments • Co-founded, presided over, and former member of the Board of Directors of the International Informix Users Group (IIUG) • Written two Informix Press books:

  4. What I’m Going to CoverThe Basics • IDS Futures • What is IBM IDS and Foundation? • Things to Know Before Moving • Reserved Words • ONCONFIG Parameters • How Do I Move? • Dynamic Logical Log Files • Configurable Lock Modes • SQL Statement Cache • Raw / Standard Tables • Changes to “Explain Mode” • Fuzzy Checkpoints • IBM Informix MaxConnect

  5. What I’m Going to CoverThe Good Stuff • Smart Large Objects and Dbspaces • Simple/Complex Datatypes and Casting • User Defined Routines and Functions • Collections • DataBlades and the Blade Manager • HDR / Enterprise Replication – A Snapshot • Java in the Engine

  6. IDS Futures

  7. 9.6 9.4 9.5 9.x 2003 2004 2005 2006 Projected IDS Roadmap IBM will deliver new releases of IDS about every 18 months • In Development • Features Frozen • Planned 2Q 2003 Delivery • In Planning • Accepting Feature Requests • Projected 4Q 2004 Delivery • In Concept • Projected 2Q 2006 Delivery

  8. IDS 9.40 Features Development is focused on providing a release of IDS 9 of interest to all IDS customers. OLTP Features are the #1 Priority • Scalability • Security • SQL Compatibility • Performance • Ease of Upgrade • And More!

  9. IDS 9.50 & 9.60 What can you expect down the road? • Further Integration with IBM & DM Products • Focus on Security • Focus on SQL Compatibility • Focus on Performance • Leverage the SMART Initiative • Support for the Latest Hardware • Platforms • Operating Systems • Structured Storage – NAS & SAN

  10. Platform Directions • Industry Consolidation, majority of IDS sales are on 5 platforms: • Tier 1 Operating Systems: AIX, Solaris, HPUX, Windows, Linux • Tier 1 Platforms: Sparc, Power, PA-RISC, Intel, IA64 Planned • Continuing support (Planned for IDS 9.40) for Tru 64, Irix, Caldera OpenUnix + Linux Kernel Personality (LKP) • Future is 64 bit. • Many vendors are slowly moving towards IA64 • Affected operating systems include: Windows, Linux, HPUX • Whenever possible move to 64 bit servers, rather than 32 bit • Most applications will run unchanged against a 64 bit server • Some will not be 64 bit clean -> thorough testing prior to deployment is recommended • Full exploitation of 64 bit server may require recompiling and/or modifying application

  11. Downlevel Operating System Versions You need to move to newer OS levels and/or 64 bit servers (recommended if available) • Solaris 2.5.1 • Solaris 2.7 (64 bit) • AIX 4.3.2 and below • HPUX 10.x • Tru 64 4.x • Dynix 4.4.x and below • Windows NT • Linux 2.2 Kernel • HPUX 11 32 bit • Caldera SCO OpenServer & UnixWare

  12. What’s the Difference Between IDS and Foundation?

  13. Parallelism built-in Parallel Data Query SQL92 Entry Level Enterprise Replication Many, many more... SQL 3 Support DataBlade Support DataBlade Developer Kit User Defined Routines User Defined Datatypes User Defined Indexing R-Tree Indexing Extended B-Tree Support Row Types Collections (sets, multiset, lists) Inheritance Polymorphism Partitioning with new data types What Is IBM IDS and Foundation? IDS - UDO 9.1x IBM Informix Dynamic Server 7.x

  14. IBM IDS 9.2 + What Is IDS and Foundation? IBM Informix Dynamic Server 7.x + IDS - UDO 9.1x =

  15. J/Foundation Web DataBlade Excalibur Text DataBlade Object Translator Office Connect IBM Informix Dynamic Server Then What Is Foundation?

  16. The New Face of the Foundation Family: • IBM IDS with J/Foundation • New product -- IDS with the Hot-Spot JVM • Internet Foundation – has been discontinued • Financial Foundation for Capital Markets • TimeSeries, NAG DataBlade Modules, TimeSeries Real Time Loader, Office Connect, Object Translator • Law Enforcement Foundation • Visionics, fingerprint, and other biometric-oriented DataBlade Modules IBM Informix Spatial DataBlade Module is freely available to IDS 9.3 customers!!

  17. Which Version Is Loaded?? (IBM IDS) Odra: onstat - Informix Dynamic Server Version 9.30.UC1G1 (Foundation) Ebro: onstat - Informix Dynamic Server Version 9.30.UC1G1 (MSGPATH) Wed Oct 4 07:49:52 2000 07:49:52 Booting Language <builtin> from module <> 07:49:52 Loading Module <BUILTINNULL> 07:49:57 Informix Dynamic Server Version 9.30.UC1G1 Software Serial Number AAB#J500705 07:50:12 Informix Dynamic Server Initialized -- Shared Memory Initialized 07:50:15 Physical Recovery Started 07:50:25 Physical Recovery Completed: 0 pages restored

  18. Which Version Is Loaded?? Ebro: cd /usr/informix/9_3/extend Ebro: ls -l drwxr-xr-x 4 informix informix 1024 Jul 19 08:08 ETX.1.30.UC5 drwxr-xr-x 4 informix informix 1024 Jul 19 08:07 TXT.1.10.UC5 drwxr-xr-x 4 informix informix 1024 Jul 19 09:02 VTS.1.20.UC1 drwxr-xr-x 2 informix informix 1024 Jul 19 07:42 ifxbuiltins.1.1 drwxr-xr-x 2 informix informix 1024 Jul 19 07:42 ifxmngr drwxr-xr-x 2 informix informix 1024 Jul 19 07:42 ifxrltree.2.00 drwxr-xr-x 4 informix informix 1024 Jul 19 07:49 krakatoa drwxr-xr-x 4 informix informix 1024 Jul 19 07:42 LLD.1.20.UC2 drwxr-xr-x 4 informix informix 1024 Aug 22 13:36 WEB.4.10.UC1

  19. Things to know before you move

  20. Things to Know • You can not directly move from < 7.x versions • IBM STRONGLY suggests that you first move to the latest available 7.x version then move to 9.x • While you may want to export/import your data, in-place upgrades have been quite successful -- just remember to wear your belt and suspenders • HP-UX 11.0 - RUN_AS_ROOT.server script is wrong Links created for one library goes into /usr/lib instead of /usr/lib/pa20_64; if left alone, engine will not come up (“library not found” error). A “defect” has been entered ** Check 9.3 version, may be corrected.**

  21. Things to Know • With IBM IDS, CPU and user-defined VPs are run as user “informix” while other VPs are still run as root. This prevents UDRs from having root access • If you have auto-start scripts, you should modify them so they “su” to “informix” before starting the engine • Install engine after client products for “finderr” to work. The order is: • tools • network • engine

  22. Things to Know • Precision change in float / smallfloat to decimal conversion smallfloat 8 -> 9 float 16 -> 17 • Default behavior of “create index” is now to create detached indexes. 7.x attached indexes are supported by the upgrade process • Use the “DEFAULT_ATTACH” variable to temporarily mimic 7.x behavior, it will be discontinued however in a future release • 9.4 Feature: LVARCHAR increases 2K to 31K Optional parameter added to lvarchar data-type declarations: LVARCHAR (n) where 0 < n < 32k-2. Current syntax still supported, but old 2k limit holds for that syntax.

  23. Things to Know • If using IBM Informix Enterprise Gateway with DRDA version 7.31.UC1 with IBM IDS 7.x, you must get the DRDA patch to avoid defect #132619 • HADR has changed. DRAUTO is no longer supported so after a HADR failure condition is declared, the secondary server goes to read-only mode • You must manually convert the secondary server to “standard” mode “onmode -d standard” • No changes to DRINTERVAL, DRTIMEOUT, DRLOSTFOUND

  24. Things to Know • 9.2 / 9.3 ER and HPL – does not support named and other complex datatypes, only distinct and opaque types • 9.4 feature: new functionality! see ER/HDR portion of the presentation. Test dbexport / dbimport to see if more complex named types work • Shut down HADR / ER before upgrading

  25. Things to Know • System catalog changes - columns added, moved, data types changed. New tables added and some dropped. Sysindexes and systables now a view rather than tables • Long identifiers!! The 8/18 rule is dead (the crowd cheers!!) user ids - 32 characters identifiers - 128 characters table name, server name, database name, column name, index name, synonym name, constraint name, procedure name, dbspace name, blobspace name, optical cluster name, trigger name, type name, routine language name, access method name, operator class name, trace message class name, trace message name, procedure variable names

  26. Things to Know • 9.4 Feature: File size limits for utilities have been increased from 2GB to 17 billion GB Utilities Affected: Oncheck, Onload, Onlog, Onmode, Onmonitor, Onparams, Onspaces, Onstat, Onunload, DB-Access, Dbexport, Dbimport, Dbload, Dbschema, Onpload, Ontape • Also updating Streams API for > 2GB support • 9.4 Feature: Chunk size limit changes from 2 GB to 4TB • 9.4 Feature: Maximum instance chunks changes to 32,767 Individual instance size now 128 petabytes

  27. 128 petabytes! Just howBIGis128 petabytes? • At a load rate of 10 gigabytes an hour it would take well over a 1,000 years to fill a 128 petabyte database. • At the current price for high-end structured storage it would cost $3-4 billion to buy 128 petabytes of storage. • The US Library of Congress contains an estimated 10 terabytes of printed material. 128 petabytes is equal to 12,800 Libraries of Congress.

  28. Things to Know • 9.4 Feature: # of columns in a Functional Index • Old Limit 16 columns • New Limit 341 Columns (Java, SPL) • New Limit 102 (C) • 9.4 Feature: DBServerAliases • Old Limit 10 • New Limit 32 • 9.4 Feature: Size of a Shared Memory Dump • Old Limit 2 GB limit • New Limit 17 Billion GB

  29. Things to Know • 9.4 Feature: Removed IDS libraries from /usr/lib • Each user connection uses about 5% more shared memory. Watch your shared memory allocations - both instance and operating system • This can be offset by using IBM Informix MaxConnect • Make sure you drop and rebuild your distributions after upgrading!

  30. Things to Know: New Reserved Words • CACHE • COSTFUNC • ITEM • SELCONST • INNER • JOIN • LEFT • LOCKS • RETAIN • RAW • STANDARD • AVOID_EXECUTE • USE_SUBQF • AVOID_SUBQF

  31. Things to Know: One Example of New SQL Functionality 9.4 Feature: ORDER BY values not in select list • Most databases implement the ability to order a list based on values not returned by the select. • Solution: Implement support for this SQL syntax: SELECT d.dept_num FROM dept d, employees e WHERE d.dept_num = e.dept_num GROUP BY d.dept_num ORDER BY avg(e.salary);

  32. ALLOW_NEWLINE BLOCKTIMEOUT DD_HASHMAX DD_HASHSIZE DS_HASHSIZE DS_POOLSIZE PC_HASHSIZE PC_POOLSIZE SBSPACENAME SBSPACETEMP SYSSBSPACENAME STMT_CACHE STMT_CACHE_SIZE STMT_CACHE_HITS STMT_CACHE_NOLIMIT STMT_CACHE_NUMPOOL VPCLASS JDKVERSION JVPHOME JVPLOGFILE JVPPROPFILE JVPJAVAVM JVPJAVAHOME JVPJAVALIB JVPCLASSPATH JVMTHREAD Things to Know:New ONCONFIG Parameters

  33. Things to Know:New ONCONFIG Parameters VPCLASS • Enables you to designate and create uniquely named, user-defined, or system classes of VPs. User-defined VPs (UDVPs) have the same functional power as CPU VPs • User-defined VPs should be created to execute user- defined routines and/or DataBlades • Some DataBlades (Verity, Excalibur Text) require their own VP as does the JVM • User-defined VPs can be added/dropped on the fly with the “onmode -p class_name” command

  34. Things to Know:New ONCONFIG Parameters VPCLASS Syntax tree: VPCLASS name,num=X,[max=X,aff=(X-Y),noyield,noage] • VP name is not case sensitive, options are order independent, no whitespace, 128 char max/entry • Must declare multiple JVPs to execute Java UDRs in parallel (Unix) • If using “noyield”, only declare “1” vp since the UDR will execute serially, causing others to queuefor their turn

  35. Things to Know:New ONCONFIG Parameters Use the VPCLASS parameter to replace the CPU and AIO ONCONFIG parameters VPCLASS cpu,num=3,max=10,noage • Comment out AFF_SPROC, AFF_NPROCS, NOAGE, NUMCPUVPS, SINGLECPUVP parameters VPCLASS aio,num=5,max=10 • Comment out NUMAIOVPS parameter

  36. Things to Know: Discontinued Support • ON-Archive. Use either the ontape or ON-Bar backup utility instead. • DB/Cockpit. Use ISA instead. • Informix-DBA. Use Server Studio Java Edition by AGS instead. • ISM graphical user interface. The command line provides the same functionality.

  37. Things to Know:How do I Move? 1. Stop database processing and force a change to a new logical log onmode -sy; onmode -l; onmode -c; onmode -ky; 2. Back up the instance(s) and critical configuration files in $INFORMIXDIR etc/ /aaodir/adtcfg$ONCONFIG (s)** /dbssodir/adtmasks ONCONFIG.stdsm_versions sqlhosts**tctermcaptermcap

  38. Things to Know:How do I Move? 3. Restart the instance(s) and put them into single-user mode to ensure all open transactions are properly rolled back oninit -sv 4. Verify data and index integrity with the oncheck utility (-cr, -ce -cc, -c [ I / D ] db_name) 5. Shut the instance(s) down again and create level 0 backup(s) and/or dbexports – make sure logical logs are backed up and clear!! 6. Install the new IBM IDS / Foundation software and change relevant scripts, global parameters, config files

  39. My Recommended Directory Structure 7_3 9_3 scripts disks (also tapes) isa logs config_files max_conn koetari -- symbolic links odra -- symbolic links /usr/informix Environment Variables: INFORMIXDIR ONCONFIG INFORMIXSQLHOSTS (current logs) /old_logs

  40. Things to Know:How do I Move? 7. Change ALARMPROGRAM to [ null | no_log.sh ] if using On-Bar or LTAPEDEV to /dev/null 8. Install any DataBlades 9. Restart each instance into quiescent mode and monitor the MSG_PATH file. Sysmaster and reserved pages conversion is automatic (see $INFORMIXDIR/etc/dummyupds7x.sql). When completed, a notice is written to MSG_PATH. If upgrading from 9.2 to 9.3, watch for sysutils and sysmaster db build successful messages in MSG_PATH before doing anything else! 10. Verify data integrity with oncheck commands

  41. Things to Know:How do I Move? 11. Execute an “update statistics low drop distributions” command on each database in the instance 12. Execute an “update statistics high” command on the sysmaster database 13. Execute an “update statistics medium distributions only” command on each database in the instance. 14. Change LTAPEDEV / ALARMPROGRAM back to its original value

  42. Things to Know:How do I Move? 15. Create a level 0 backup. 16. Perform system tests to check performance. Execute queries that will stress indexes to see if the optimizer properly uses them as well as their performance. 17. Let the users back in If there are any problems -- RESTORE from tape

  43. Dynamic Logical Logs

  44. Dynamic Logical Logs:Introduction New feature in IBM IDS 9.3 – automatically create, insert, and activate logical logs when needed!!! Does not eliminate long transactions, just the server hangs that can occur especially on startup. Required: • Ability to add log on the fly • Insert log immediately after current logical log • Bring the log into active mode without a backup of rootdbs / critical Dbspace(s)

  45. Dynamic Logical Logs:Introduction In the pre-9.3 engine: • Could only be added when in quiescent mode • Always took the first available slot in the list • Required a backup of the rootdbs (level 0) to become active

  46. Dynamic Logical Logs:Introduction In IBM IDS 9.3: • LOGSMAX disappears from $ONCONFIG • Logs can be added while instance is processing transactions if so configured • Logs can be added after current log to avoid running into log with "begin work" statement • Newly added logs are immediately available • Need to use DYNAMIC_LOGS $ONCONFIG parameter

  47. Dynamic Logical Logs:How do you add a log? onparams –a [ -d dbspace ] [ -s size ] [ -i ]

  48. Dynamic Logical Logs:How do you add a log?

  49. Dynamic Logical Logs:Server actions The server itself will attempt to add a log file if two conditions are true: • The next active log file contains an open transaction • DYNAMIC_LOGS is set to 2 (the default) The server checks for those conditions at two important points in the code: • Immediately after a log switch • Beginning of the last phase of logical recovery (Transaction Cleanup)

  50. Dynamic Logical Logs:Server Actions What Dbspaces are used for automatic log allocation? 1 The dbspace that contains the newest log files. (If this dbspace is full, the engine searches other dbspaces.) 2 Mirrored dbspace that contains log files (but excluding the root dbspace) 3 All dbspaces that already contain log files (excluding the root dbspace) 4 The dbspace that contains the physical log 5 The root dbspace 6 Any mirrored dbspace 7 Any dbspace

More Related