1 / 167

Moving from IBM IDS 7.x to IBM IDS 9.x

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. 12 + years of experience as DBA and engine admin mainly in retail environments

lefty
Download Presentation

Moving from IBM IDS 7.x to IBM IDS 9.x

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. Moving from IBM IDS 7.x to IBM IDS 9.x Carlton Doe Technical Sales Manager, Dallas, TX IBM

  2. ? 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:

  3. What I’m Going to CoverThe Basics • 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

  4. 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 • Enterprise Replication – A Snapshot • Java in the Engine

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

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

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

  8. 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!!

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

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

  11. Things to know before you move

  12. 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.**

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

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

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

  16. Things to Know • 9.2 / 9.3ER and HPL – does not support named and other complex datatypes, only distinct and opaque types Test dbexport / dbimport to see if more complex named types work • Shut down HADR / ER before upgrading

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

  18. Things to Know • 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!

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

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

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

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

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

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

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

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

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

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

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

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

  31. Dynamic Logical Logs

  32. 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)

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

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

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

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

  37. 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)

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

  39. Dynamic Logical Logs:DYNAMIC_LOGS parameter Values for the DYNAMIC_LOGS parameter: 2 (default) the server is authorized to add log files automatically to avoid long transaction hangs 1 if a log file is needed, the server waits for the admin to add one manually 0feature is turned off, mimics < 9.3 behavior

  40. Dynamic Logical Logs:DYNAMIC_LOGS parameter Why set DYNAMIC_LOGS=1? You want to make use of the feature in order to avoid long transaction hangs, but you don’t want to give up any control over log file location or size. Remember that once a log file is added to a non-critical Dbspace, that space becomes critical.

  41. Dynamic Logical Logs:DYNAMIC_LOGS parameter Why set DYNAMIC_LOGS=0? Hmm, tough question. We don’t know. Adding a log file to a Dbspace forces your next archive of that Dbspace (and the ROOT Dbspace) to be a level 0. If you’d rather risk a server hang than risk having to occasionally tweak your archive schedule, turn the feature off. If the server does hang due to a long transaction rollback, simply set DYNAMIC_LOGS to 1 or 2 and bounce the server. Now you may have to improvise with your archive schedule, but it’s better than prolonging a down system.

  42. Dynamic Logical Logs:DYNAMIC_LOGS parameter Dropping logical logs in 9.3 is similar to <9.3 • Never-used log files (A) drop immediately • Used log files are marked “Deleted”. Once all spaces have been archived they will automatically drop • If system has never been archived, the new drop restriction isn’t necessary, and is not applied • NEW -- Can be done while instance is on-line!!!

  43. Configurable Lock Mode

  44. Configurable Lock Mode:Introduction An extension of the "lock mode [ row | page ]" syntax in "create table" command Allows users / admins to set the lock mode on newly created tables without having to explicitly set it in the table creation statement. Uses either an environment variable or an $ONCONFIG parameter setting

  45. Configurable Lock Mode:How's it set? IFX_DEF_TABLE_LOCKMODE environment variable setenv IFX_DEF_TABLE_LOCKMODE row This variable can also be set in $HOME/.informix or $INFORMIXDIR/etc/informix.rc files DEF_TABLE_LOCKMODE $ONCONFIG parameter DEF_TABLE_LOCKMODE row This sets LOCK MODE to ROW for all tables for all sessions in the server

  46. Configurable Lock Mode:Examples No additional syntax: {TABLE "carlton".test_table row size = 155 number of columns = 3 index size = 0 } create table "carlton".test_table ( col1 integer, col2 char(50), col3 varchar(100) ) extent size 32 next size 32 lock mode page; TBLspace Report for my_nt_test:carlton.test_table Physical Address 200033 Creation date 09/09/2001 15:07:35 TBLspace Flags 901 Page Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 155

  47. Configurable Lock Mode:Examples Explicit "lock mode page" syntax with environmental variable set to "row": {TABLE "carlton".test_table_3 row size = 155 number of columns = 3 index size = 0 } create table "carlton".test_table_3 ( col1 integer, col2 char(50), col3 varchar(100) ) extent size 32 next size 32 lock mode page; TBLspace Report for my_nt_test:carlton.test_table_3 Physical Address 200245 Creation date 09/09/2001 15:18:09 TBLspace Flags 901 Page Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 155

  48. Bug in the code !! Configurable Lock Mode:Examples With environment variable set to "row" but no additional syntax in "create table" command: {TABLE "carlton".test_table_2 row size = 155 number of columns = 3 index size = 0 } create table "carlton".test_table_2 ( col1 integer, col2 char(50), col3 varchar(100) ) extent size 32 next size 32 lock mode page, row; TBLspace Report for my_nt_test:carlton.test_table_2 Physical Address 200034 Creation date 09/09/2001 15:18:09 TBLspace Flags 903 Page Locking Row Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 155

  49. SQL Statement Cache

  50. SQL Statement Cache: Introduction As implied, prepared and optimized SQL statements are stored in a section of the virtual portion of shared memory. Users executing identical DML SQL statements can pull the pre-parsed query plan and even query data structures from the cache More complex queries (multiple columns, many filters in the WHERE clause) benefit most from caching Turned off by default, the STMT_CACHE $ONCONFIG parameter and the “SET STATEMENT CACHE” SQL command control how the caching process works. Can be overridden with the “onmode -e” command

More Related