What’s New in IDS 9.3??

1. What’s New in IDS 9.3?? Carlton Doe System Engineering, District Manager IBM

2. What I’m Going to Cover • Misc Stuff • Dynamic Logical Log Files • Configurable Lock Modes • SQL Statement Cache • Raw / Standard Tables • Changes to “Explain Mode” • Smart Large Objects and Dbspaces • Enterprise Replication – A Snapshot • Java in the Engine

3. Misc. Stuff

4. The New Face of the Foundation Family: • IBM IDS with J/Foundation • New product -- IDS with the Hot-Spot JVM • Internet Foundation – New Version coming soon! • Web, Text DataBlade Modules, Office Connect, Object Translator • 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!!

5. New Reserved Words • CACHE • COSTFUNC • ITEM • SELCONST • INNER • JOIN • LEFT • LOCKS • RETAIN • RAW • STANDARD • AVOID_EXECUTE • USE_SUBQF • AVOID_SUBQF

6. 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 New ONCONFIG Parameters

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

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

9. Maximum Number of Sessions • Overall maximum - The maximum number of simultaneous sessions since the server was brought up for the first time. • Current maximum - The maximum number of simultaneous sessions since the server was last booted. A message indicating the value is written to the log right after each checkpoint message. • Both these values will also be made visible to the ISA tool through SMI tables.

10. Additional SQL Syntax Revoke as User • Grant and Revoke allow the owner of various databaseobjects to assign privileges or revoke privileges from other users. • Grant supports following syntax "Grant .... TO <USER1> AS <USER2>". • This feature will add corresponding syntax and functionality to Revoke. • "Revoke .... FROM <USER1> AS <USER2>“ This enables USER2 to revoke that privilege.

11. Additional SQL Syntax Optional From in Delete Clause • Enable users to omit the ‘FROM’ word in ‘DELETE ‘ statement. • Both the following statements work the same way: DELETE FROM tablename WHERE expression; DELETE tablename WHERE expression; • The goal of this feature is to simplify the usage of DELETE statement to support third party software

12. New Onstat Option onstat -g stm <session id> • This option lists all the prepared SQL statements for a session and the amount of memory used. • Example: $ onstat -g stm 8 Informix Dynamic Server Version 9.30.xx --On-Line-- 1024 Kbytes session 8 --------------------------------------------------- sdblock heapsz statement ('*' = Open cursor) a97b018 10424 * select * from tb a97b110 8368 insert into tb values (?) a97b208 5120 <SPL statement>

13. Dynamic Logical Logs

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

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

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

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

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

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

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

21. 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 0 feature is turned off, mimics < 9.3 behavior

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

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

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

25. Configurable Lock Mode

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

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

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

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

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

31. SQL Statement Cache

32. SQL Statement Cache:New $ONCONFIG parameters • STMT_CACHE_HITS Number of statement hits before entered into the cache Values: • 0 – statement immediately inserted • N – first time statement used, key-only entry made in cache, from 2 to N counter incremented. After N uses, full statement entered into cache Want to set to "1" (one) to prevent ad-hoc statements from clogging cache

33. SQL Statement Cache:New $ONCONFIG parameters • STMT_CACHE_NOLIMIT Controls insertion of qualified statements into cache after its size is greater than the STMT_CACHE_SIZE value. While statement cache can grow, this acts as a governor to stop unrestrained growth Values: • 0 – no new statements are inserted after STMT_CACHE_SIZE reached • 1 – unrestricted growth of cache is allowed

34. SQL Statement Cache: New $ONCONFIG parameters • STMT_CACHE_NUMPOOL Specifies number of pools to be allocated to the SQL Statement Cache Values: • 1 (default) to 256 As statement cache increases, number of pools supporting the cache may become a bottleneck. Use the "onstat –g ssc pool" and "onstat –g spi" commands to monitor usage and spin waits. See pages 4-49 – 4-52 in the Performance Guide for more information.

35. Raw / Standard Tables

36. Raw / Standard Tables Raw tables have been in XPS for quite some time and are used in conjunction with its SQL-based HPL or massive static data manipulation Intended for initial loading and data validation Similar behavioral attributes regardless of database logging mode, i.e., don't try to use them in transactions.

37. Raw / Standard TablesRaw table attributes • nonlogged permanent table, behaves like table in a nonlogging database • uses light appends, adds rows quickly to end of each table fragments • updates, inserts, and deletes are supported but not logged • Can not support indexes, referential constraints, or rollback • Can only restore from last physical backup if not updated since that backup, no MiT restore • Fast recovery rolls back incomplete transactions on STANDARD tables but not on RAW tables • Wicked fast to use if done properly!!! 

38. Raw / Standard TablesSyntax create raw table xyz ( yada, yada ) in etc., etc. fragment by whatever; alter table xyz type [ raw | standard ]; • When altered for massive manipulation, make sure you backup the table after converting it back from raw mode!!

39. Changes to "explain mode"

40. Explain Mode Can now get sqexplain.out information without having to execute the DML statement!!!! Two ways to activate this feature: • per statement with a directive select --+explain avoid_execute * from xyz AVOID_EXECUTE directive works in conjunction with EXPLAIN directive. If EXPLAIN directives is omitted, i.e. if only AVOID_EXECUTE directive is used,the query will not be executed and no explain output will be generated.

41. Explain Mode • Set as part of a statement block Set explain on avoid_execute; After executing this statement, the server writes to the sqexplain.out file without executing any statements until SET EXPLAIN ON/OFF is executed.

42. Large Objects and SBSpaces

43. Size and location of the metadata area is configurable at sbspace and/or chunk creation Metadata is always logged regardless of database or sbspace logging mode header (reserved) pages User Data metadata area Large Objects and SBSpaces Smart BLOBs are stored in “smart” BLOBSpaces (sbspace) which contain meta-data as well as user data

44. Large Objects and SBSpaces onspaces -S name -g pageunit -p pathname -o offset -s size [ -m pathnameoffset ] -Ms mdsize -Mo mdoffset -Df default list-t -Mo mdoffset offset, in kbs, into the disk partition where metadata will be stored -Ms mdsize the size, in kbs, of the metadata area for the initial chunk, remainder is user data space -- not recommended though possible. Each SLO needs ~500 bytes of metadata space -Df default list default specifications for objects stored in the sbspace. Comma separated, in double quotes (“ “) • -t new option in 9.3 for temporary sbspaces to store temporary smart LOBs

45. Large Objects and SBSpaces Temporary SBSpaces: • Like temporary DBSpaces -- no logging whatsoever occurs • Set with the SBSPACETEMP $ONCONFIG parameter Temporary smart LOBs: • To create a temp smartblob, set the LO_CREATE_TEMP flag in the ifx_lo_specset_flags or mi_lo_specset_flags function. Use mi_lo_copy or ifx_lo_copy to create a permanent smart large object from a temporary smart large object. • If you put a temporary smartblob in a permanent sbspace it gets deleted at the end of session

46. header (reserved) pages User Data metadata area Large Objects and SBSpaces Metadata stealing: • 40% of user data area reserved for either meta or user data, is allocated as needed to either portion (10% increments) • If metadata area is filling, messages in MSG_PATH • If metadata fills, and reserve is empty, no more S-BLOBs can be inserted even if user data area has free space • Must add more metadata space

47. Large Objects and SBSpaces To add a chunk, you can specify whether the chunk will contain only user data, metadata, or both: onspaces -a sbspace -p pathname -o offset -s size[-Ms mdsize -Mo mdoffset | -U ][ -m pathname offset ] To drop the sbspace: onspaces -d sbspacename -f (force)

48. Enterprise Replication – A Snapshot of New Features

49. ER – A SnapshotNew Stuff • DataSync performance improvements • Spooling improvements • Serial column primary key support • Changed columns sent with update, partial row update • Smart LOBs support • No raw table support • Distinct / opaque type support but not complex

50. ER – A SnapshotNew Stuff DataSync performance improvements include: • Support of intra-replicate parallelism • Parallel apply with ordered commits • Operations for a given row are serialized • Necessary to prevent ‘delete before insert’ • Some tables are always serialized • Page level locking or small tables • Out-of-order commits • Allows commit order on target to be different than it was on source • No prior cross replicate relationships • No prior referential constraint relationships