Key Features in IDS Version 10 00

® Key Features in IDS Version 10. 00 Compared with IDS 9. 40 Jerry Keesee, Director of the Informix Lab Jonathan Leffler STSM, Informix Database Engineering Information Management Division IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 Version 2 © 2005 IBM Corporation

Agenda § IDS Version 10. 00 Features Summary § Performance § Autonomics, Ease of Administration § Security § Availability, ER § Application Development, Standards 2 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Announcing the Availability of the V 10 Release § Strong IBM Information Management commitment to the IDS product line. § Most industry proven, reliable, and high performing OLTP RDBMS for Open Systems. § Significantly raises the bar for autonomics, embeddability, availability and low-cost operations. § Most accessible platforms on the market with Unix, Windows and Linux. § Highest quality and robustness suitable for missioncritical, bet your business operations for SMB and enterprises world-wide. § Generally available since end of February 2005. 3 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

IDS: A Tradition of Innovation Smart Restore (TLPIT) Online Create/Drop Index 2004 Distributed High Availability (ER+HDR) Configurable Page Size 4 GL/EGL VLDB – Large Chunks Java in the Server 2000 Data. Blades Smart Large Objects (Blobs/Clobs) Enterprise Replication DSA Architecture 1994 Online Backup 4 Object Relational Capabilities 1998 High Availability Data Replication Data Fragmentation Parallel Data Query Distributed Query (ISTAR) IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

“To sum it all up, IDS v 10. 0 is a DBA’s dream come true. ” Gary Ben-Israel CIO, National Institute for Testing and Evaluation Jerusalem, Israel Beta Customer 12/04 5 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

IBM Informix Dynamic Server Roadmap IDS v 10 IDS v 9. 40 1 Q 2003 § Backup & Restore § HDR & ER coexistence § Remove size restrictions (Large File / Chunks) § Security Sys. (LDAP / Encryption § Monitoring, Utilities § Performance 2003 6 2004 IDS v. Next 1 Q 2005 § 4 GL/Web. Sphere EGL §. Net Native Provider § JDBC 3. 0 Compliance § Column Level Encryption § Default Roles § Table Level PIT Restore § Configurable Page Size § ER Alter, DRAUTO, Resync 2005 2006 2 H 2006 § Smooth Upgrade of HDR § Online table re-org § Dynamic Reconfiguration § Auto Update Stats § Auto Disk Mgmt § RAS § Autonomics 2007 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 IDS v. Next+ 1 H 2008 § DB 2 Interoperability § Market § Technology § Customer Requests 2008 © 2005 IBM Corporation

IDS 10. 00: Summary of Ease of Administration ü Automated Re-Sync of Tables in ER Key Features ü BAR Ease of Use Enhancements Safety / High Availability / Reliability ü Table Level Point in Time Restore ü Faster Restart ü Query Plan and Tracing Tool Enhancements ü HDR Ease of Use Enhancements Partner Enhancements ü Configurable Page Size ü Larger Index Keys ü Create / Drop Index Online ü Single or Privileged User Admin Mode ü Rename DBSpace ü Default Roles Security ü Column Level Encryption ü LDAP Support for Windows 7 ü ER Templates / Ease Setup in ISA ü External Optimizer Directives ü Dynamically Set OPTCOMPIND ü ON-Tape Without Involving Backup Media App Development / SWG Integration ü 4 GL / EGL Merge with Web. Sphere ü. Net Native Provider ü Visual Studio Integration ü JDBC 3. 0 Standards Compliance ü Cross-DB Support for Built-In UDTs ü DRDA in ESQL/C & Server ü WORF and TDPI Bundled Performance & Infrastructure ü Query Processing and Load Performance Improvement ü Shared Memory Segments larger than 4 GB IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Topics: Performance Enhancements hi Configurable page sizes External optimizer directives Engine Impact Memory to non-PDQ queries low 8 Dynamic OPTCOMPIND Multiple fragments in single dbspace Number of times requested by users IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 hi © 2005 IBM Corporation

Performance: Configurable Page Size Reasons for Configurable Page Size § Space efficiency – Larger pages up to 16 K bytes contiguous space § Increased maximum key size – Longer keys up to 3 K bytes § Access efficiency - fewer I/O operations for data and indices 9 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: Configurable Page Size § Space efficiency – larger pages up to 16 K bytes contiguous space most any page type page header A row size of 1200 bytes: row 1 – 1 row fits on a 2 k page (6 k every 3 rows). – 3 rows fit on a 4 k page, a savings of 33% row 2 For thirty 1200 -byte rows: – A 2 k page size requires 60 k. – A 4 k page size would require only 40 k. – A 6 k page size requires just 36 k, a 40% savings 2048 (28) -----2020 10 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 slot table © 2005 IBM Corporation

Performance: Configurable Page Size § Increased maximum key size - longer keys up to 3 K bytes index page header • Placing more keys on a page, we support longer keys without drastically increasing index level depth. row 1 row 2 • The pre-10. 0 key size limit was also a roadblock to utilizing the UNICODE character set, which caused some key values to exceed the max length. key value 11 rowid slot table del flag IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: Configurable Page Size § Access efficiency – Fewer I/O operations for data and indices. • Long rows put into a single page benefits data access times by decreasing the number of pages read per row. • Pages large enough to fit “oversize” rows eliminate the overhead of access time for remainders pages. • More items put on a larger index page and a decreased number of levels in a btree index reduce index traversal costs. • For DSS environments, using larger data pages that maximize disk scan rates may improve table scan performance. 12 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: Configurable Page Size § Larger page sizes need larger rows. § Otherwise, you waste space: Page Row Wasted Size KB Size Space 2 3 235 4 11 243 § Applies to indexes as well as data 6 19 251 – If the key size is too small, you waste space in the large pages. 8 28 4 10 36 12 § RS = ((PS × 1024 – 28) ÷ 255) – 4 12 44 20 14 52 28 16 60 36 – Still 255 rows per page. § PS × 1024 > (RS + 4) × 255 + 28 slot table entry 13 page overhead IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: Configurable Page Size § Create new dbspaces with non-default page size – onspaces –c –d dbspace_8 k –p /informix/dev/sys_13. dbspace_8 k. c 0 –o 0 –s 2048000 –k 8 § Note: all critical dbspaces must use basic page size. – rootdbs – dbspaces containing logical logs – dbspace containing physical log § If no buffer pool of this page size exists – A new buffer pool is created – Using default buffer pool configuration. 14 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: BUFFERPOOL configuration § Old, familiar parameters are technically obsolete: – BUFFERS, LRU_MIN_DIRTY, LRU_MAX_DIRTY § Use new, repeatable BUFFERPOOL parameter: – BUFFERPOOL • • • size=2 K buffers=3000000 lrus=128 lru_min_dirty=0. 01 lru_max_dirty=0. 05 – Separated by commas, all on one line. • BUFFERPOOL size=2 K, buffers=3000000, lrus=128, lru_min_dirty=0. 01, lru_max_dirty=0. 05 – Also ‘default’ instead of ‘size=nk’ 15 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: BUFFERPOOL configuration § Each page size has its own buffer pool. § Different buffer pool settings for each BUFFERPOOL. § If you add a new dbspace with a non-default page size – and no buffer pool exists for that page size § IDS allocates a new buffer pool using the default settings. § If there is no BUFFERPOOL default in $ONCONFIG, – Values from onconfig. std are used instead. • And there are defaults for the default buffer pool. § BUFFERPOOL entries in ONCONFIG are allocated – Even if no dbspace currently uses that page size. 16 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: BUFFERPOOL configuration § Consider using (say) 12 K pages for temporary dbspaces – Configure the 12 K buffer pool for temporary tables: • With high LRU_MIN_DIRTY, LRU_MAX_DIRTY • You don’t need temporary data written to disk § Consider using (say) 16 K pages for the major table – It gets its own buffer pool all to itself. § Beware being too clever! – IDS does a good job balancing the use of buffers. 17 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: Non-PDQ queries – a problem… PDQPRIORIT Y set? yes calculate memory for query will sort fit in memory? no yes allocate 128 K of memory no sort to disk done sort in memory 18 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: Memory Allocation for non-PDQ Queries § Feature was first available in 9. 40. x. C 4. § You can specify how much memory is allocated to non-PDQ queries. – The default of 128 K can be insufficient for queries that specify ORDER BY, GROUP BY, hash joins, or other memory-intensive options. § Use the new configuration parameter, DS_NONPDQ_QUERY_MEM, to specify more memory than the 128 K that is allocated to non-PDQ queries by default. § The onstat, onmode, and onmonitor utilities support this configuration parameter. 19 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: External Optimizer Directives § Suppose you purchase an application – But you do not get the source code for it. § Suppose some of its queries work slowly – Because of a peculiarity in the optimizer, statistics, … § Suppose that an optimizer hint can fix the problem. § Before version 10. 00, there was no way to add a hint. – Unless you used I-Spy. § With version 10. 00, you can store external directives. 20 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: External Optimizer Directives § New SQL statement, SAVE EXTERNAL DIRECTIVES – Creates and registers external optimizer directives. – Stored in the new sysdirectives system catalog table. § At run-time, external directives have to be enabled: – Per session by the new environment variable: • export IFX_EXTDIRECTIVES=1 – Enabled for client unless server disables it – 0 => disabled for this client regardless of server setting. – Or per instance by the new $ONCONFIG parameter: • EXT_DIRECTIVES 1 # Enable external directives – Enabled for client if explicitly requested – 2 => enabled unless explicitly disabled by client. – 0 => disabled for all users regardless of client setting. 21 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Multiple Fragments of a Table in One Dbspace § Suppose you wanted to fragment a table with: – One fragment a quarter for two years of data. – Eight fragments in total. § You needed at least 8 dbspaces for this: – Each fragment had to go into a separate dbspace. § With version 9. 40 and large chunks, – It is better to have one chunk per disk drive. – But you only have one 80 GB drive. 22 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Multiple Fragments of a Table in One Dbspace § You can create partitions within a dbspace that can each support a table fragment. – Reduces the total number of dbspaces needed for a fragmented table. § Storing multiple table fragments in a single dbspace improves query performance over storing each fragmented expression in a different dbspace. § This feature improves performance and simplifies management of dbspaces 23 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Multiple Fragments of a Table in One Dbspace § CREATE TABLE Example 1 (…table definition…) FRAGMENT BY EXPRESSION PARTITION p 1_ex 1 (…) IN dbspace 1, PARTITION p 2_ex 1 (…) IN dbspace 1, PARTITION p 3_ex 1 (…) IN dbspace 1, REMAINDER PARTITION pr_ex 1 IN dbspace 1; § Upwardly compatible: – Old scheme had each partition anonymous in a separate dbspace. – New scheme allocates partition name same as dbspace name for upgraded fragmented tables. 24 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: OPTCOMPIND A Review response time OPTCOMPIND Setting think/optimization time query/execution time good indexes; healthy stats use index(es) 0 bad indexes; stale stats 1 if RR then 0; else 2 2 use lowest cost* healthy stats * cost = I/O + (cpu *0. 03) 25 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Performance: Dynamic OPTCOMPIND § You can use SET ENVIRONMENT OPTCOMPIND to set OPTCOMPIND environment variable dynamically for the current session. § The value that you enter using this statement takes precedence over the current setting specified in the ONCONFIG file. § The default setting of the OPTCOMPIND environment variable is restored when your current session terminates. § No other user sessions are affected by SET ENVIRONMENT OPTCOMPIND statements that you execute. 26 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Topics: Administration Enhancements hi ON-Tape using standard i/o Shared memory segments bigger than 4 GB Engine Impact True single user mode Default roles More comprehensive version information low 27 Renaming dbspaces Better event alarms information Viewing ON-Bar logical log info HDR setup with EBR Tablespace tablespace management Enhanced ONBar debugging Number of times requested by users IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 hi © 2005 IBM Corporation

ON-Tape Using Standard I/O § ON-Tape can use standard I/O channels – Instead of a tape device or disk file. – Set TAPEDEV, LTAPEDEV to STDIO. § Now ontape can use pipes for archives and restores. – Process the data with other programs, – Without first saving the data in files or tape devices. – For example: • Use compression to save media space. • Use cloning to duplicate the archive for safety. • Use encryption to prevent casual snooping on archives. – But compress before encrypting when saving. – Watch the key management! – Set up HDR more quickly: • Restore the data directly to the secondary server. • Skip the step of saving the data to file or tape. 28 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Shared Memory Segments Bigger Than 4 GB § On 64 -bit platforms – only. § Shared memory segments can be as large as: – Your operating system platform allows, – Which is controlled by the SHMMAX kernel parameter. § Important for systems with large main memories. – Do not want 256 shared memory segments • On a machine with 1 TB main memory for use by IDS. 29 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Administration: Renaming Dbspaces § Existing customers who wish to reorganize their data are moving their data to a new dbspace, then reloading the data back into the original dbspace to regain the original dbspace name. – The last reload step could be avoided by using a rename dbspace option. – Helps in recycling dbspaces. § Time consuming operations such as reorganizing the data in an existing dbspace can benefit from this feature. § The rename dbspace operation only changes the dbspace name: – It does not reorganize data 30 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Administration: Renaming Dbspaces § Feature was added to IDS 9. 40. UC 3, with limitations: – Cannot rename: • blobspaces, sbspaces, temporary, or external spaces. – Cannot rename dbspaces referenced by: • • DBSPACETEMP, CDR_DBSPACE SBSPACENAME, SBSPACETEMP SYSSBSPACENEAME, CDR_QHDR_DBSPACE CDR_QDATA_SBSPACE, – Cannot rename dbspaces if you are using HDR. – Cannot rename dbspaces when ER is active. 31 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Administration: Renaming Dbspaces § In IDS version 10. 00, some restrictions remain: – Rename can not be done on critical spaces • Root dbspace • Dbspace containing physical log or logical logs. – A dbspace with down chunks can not be renamed. – ON-Monitor cannot rename spaces. § Take a Level 0 archive of the renamed space and root dbspace after renaming. – Otherwise, recovery from archive uses the old dbspace name. § Rename on HDR primary propagates to secondary. 32 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Administration: Default Roles § Suppose you manage your database permissions using roles. § You use an application with no support for roles. § You don’t have the source code, so you can’t fix it. § Prior to IDS version 10. 00, you were stuck. § With IDS version 10. 00, you can create a role – And assign that as the default role to • Individual users • Or to PUBLIC. 33 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Administration: Default Roles § GRANT DEFAULT ROLE z_role TO zaphod; § Zaphod is granted permission to use z_role. – If he does not already have that permission. § When Zaphod connects to the database, – The current role is automatically set to z_role. § The role can be changed after connecting: – SET ROLE NONE; – SET ROLE totherone; – SET ROLE DEFAULT; § It does not limit the user – If the users knows what they’re doing. 34 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Administration : Managing the Tblspace § The DBSA adds a chunk temporarily to a dbspace – To create a large table in the dbspace. § During processing the tblspace extends – Into the newly added chunk. § The DBSA drops the large table – And now wants to drop the chunk. § IDS won’t let that happen! 35 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Administration : Managing the Tblspace § Tblspace == partition § TBLspace – Special table that tracks other tables within a dbspace. – Every dbspace has its own tblspace • That tracks tables (partitions) within its own dbspace. – There is only one tblspace per dbspace. • But as with any tblspace it can have multiple extents. – Partnum is 0 x. DDD 00001, where DDD = dbspace number. 36 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Resolution Options Prior to IDS 10. 00 § Drop Dbspace – This causes the customer to have to unload the entire dbspace. – And drop all tables in the dbspace. – Then drop and recreate the dbspace. – The rebuild all the tables in the recreated dbspace. – This creates an outage! § Dial-in To Drop Chunk – Another solution was for Advanced Support dial in and drop the chunk. – They would also clean up the tblspace. – This caused down time and is risky (usually avoided). 37 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

First and Next Extent Sizes – Root Dbspace § Root dbspace (oninit –i) – Two new ONCONFIG parameters to configure root dbspace – TBLTBLFIRST • Specifies the size of the first extent of the tblspace – TBLTBLNEXT • Specifies the next extent size of the tblspace – If these parameters are not present, the defaults will be used. – All sizes are in KB, and must be a multiple of the basic page size. 38 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

First and Next Extent Sizes – Non-root Dbspaces § Non-root dbspace created with onspaces – When creating a new dbspace with onspaces you will now be allowed to specify the first and next extent sizes for the tblspace. • -ef –en – If these options are not used the defaults will be used. § Example onspaces –c –d dbs 1 –p /spare 2/dbs 1. 1 –o 0 –s 10000 –ef 150 –en 150 39 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Administration : Single User Mode § “Single-User Mode” feature allows the IBM Informix Dynamic Server to be put into a maintenance mode, allowing only the user ‘informix’ to connect the server. § It is intended to be viewed as a mode intermediate between Quiescent mode and Online mode. § It allows a DBA to have the server in a fully functional mode where any required maintenance may be performed. § It is intended to be used by DBA’s to perform any SQL/DDL maintenance while preventing normal users from connecting. § ISA also supports the Single-User Mode functionality. 40 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Topics : Security Enhancements hi Column-Level Encryption Engine Impact Trigger introspection PAM authentication Secure environment check before startup Restrict who can create external UDRs Restrict who can create databases low 41 Number of times requested by users IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 hi © 2005 IBM Corporation

Security: PAM Authentication § PAM – Pluggable Authentication Modules – It is a standardized system for allowing the OSA (Operating System Admininistrator) to configure how authentication is done. – Allows OSA to configure authentication methods. – Available on Linux, AIX, Solaris, HP-UX and others. – configured at the Operating System level. – APIs to write shared object (. so) at wws. sun. com/software/solaris/pam – PAM supports challenge-response protocols: • In response to initial authentication request, • PAM issues a challenge, • And waits for response from application. /etc/pam/conf login auth required /usr/lib/security/pam_unix. so. 1 configuration file 42 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 /usr/lib/security PAM Modules © 2005 IBM Corporation

Trigger Introspection § In IDS before version 10. 00, – Use triggers to audit modify activity on tables. – But each table needs its own logging routines • Because the structure of each table is usually different. § In IDS version 9. 40. x. C 4 or later, – You can create a single introspective routine in C: • To audit many different tables. • Obtain information about the triggered action: – Triggers, triggering tables, views, statements, – And the values of rows involved in the trigger actions. – Not for the faint of heart. • You create and install a new shared object. 43 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Restricting Registration of Data. Blade Modules § In IDS before version 10. 00, – Any DBA or RESOURCE level user can create UDRs • Including EXTERNAL ones which load a shared library. – Any of those users could subvert the system security. § In IDS version 10. 00, – The DBSA can control who creates EXTERNAL UDRs. – Set IFX_ EXTEND_ROLE 1 in ONCONFIG file • Default is backwards compatible 0 or ‘off’ – insecure. – Grant EXTEND role to selected users • In each database where they must create the UDRs. • GRANT EXTEND TO udrdef 1; – A DBA cannot grant the EXTEND role. 44 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Restricting Database Creation § Since IDS 9. 30, ONCONFIG file can contain – DBCREATE_PERMISSION – Lists the user names permitted to create databases. § Strongly recommended: – Set it to a conservative value such as: • DBCREATE_PERMISSION dba 1, dba 2, informix – Only dba 1, dba 2 and informix can create databases. – Suggestion – don’t set it to just informix • Unless informix already owns all your databases, • Which isn’t the best idea, but isn’t fatal either. 45 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Security: Column Level Encryption § IDS version 10. 00 adds new SQL statement: – SET ENCRYPTION PASSWORD ‘password’; § And new encryption and decryption functions: – ENCRYPT_AES, ENCRYPT_TDES – DECRYPT_CHAR, DECRYPT_BINARY – GETHINT § Together, these permit applications to encrypt data in the columns. § Discussed in the March ‘Chat With The Lab’ – Not repeated here. 46 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Security: Secure Environment Check § Server utilities check that the environment is secure – Before doing anything dangerous. – In versions 7. 31. UD 7, 9. 30. UC 8, 9. 40. UC 3, and later. § Public write permission is forbidden – On both directories and files which are checked. § For each key directory: – $INFORMIXDIR itself, and – Sub-directories bin, lib, etc, msg, gls, aaodir, dbssodir: • Check that the directory exists. • It is owned by user informix and the correct group. 47 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Security: Secure Environment Check § The permissions on the ONCONFIG file are correct. – The file must belong to the DBSA group. – Usually, the ONCONFIG file is owned by user informix. § The permissions on the sqlhosts file are correct. – Normally, the sqlhosts file is $INFORMIXDIR/etc/sqlhosts. – The owner should be user informix. – The group should be either informix or the DBSA group. § Configuration filenames shorter than 256 characters. – $INFORMIXDIR/etc/onconfig. std – $INFORMIXDIR/etc/$ONCONFIG – Hence, INFORMIXDIR shorter than 238 characters, • Less if $ONCONFIG is longer than 12 characters. 48 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Topics : Availability Enhancements hi Table Level Restore Engine Impact Online Index Drop & Rebuild low 49 Faster Recovery with Fuzzy Checkpoints Number of times requested by users IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 hi © 2005 IBM Corporation

Availability: Recovering Quickly with Fuzzy Checkpoints § Two new configuration parameters (FAST_RESTART_PHYSLOG and FAST_RESTART_CKPT_FUZZYLOG) § reduces the time required for engine recovery. This supports high availability by improving recovery performance when using fuzzy checkpoints. 50 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Availability: Index Changes § CREATE INDEX and DROP INDEX now supports DDL operations that apply no exclusive lock to the table on which the specified index is defined. – If you use this syntax to create an index on a table that other users are accessing, the index is not available until no user is updating the table. – After you issue the new syntax to drop an index, no one can reference the index, but concurrent DML operations can use the index until they terminate. – Dropping the index is deferred until no user is using the index. – This feature maintains the availability of the table within a production environment after an existing index has ceased to be efficient. 51 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Availability: Point-in-Time Table Restore § Purpose: to provide the customer with the ability to easily extract a set of tables, a table or a portion of a table from a level 0 archive to a user specified point in time. § The extracted data can be placed in an external table or on a table on the server of the user’s choice regardless of server version or machine type as long as the database server is listed in the sqlhost file. 52 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Benefits to the DBA § SQL Driven Distributed Restore (SDDR) § Extract a table or set of tables § Data may be placed in the same version database or on a different database version with a different machine architecture § You may apply a filter to the retrieved data § Retrieve just a subset of the columns § Repartitioning of the data 53 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Topics : Application Development/Standards Enhancements hi Support of Java Eclipse Framework Engine Impact JDBC 3. 0 Spec Support Full Support of. NET Server Studio Java Edition 4. 1 included ESQL/C to DB 2 Support low 54 Number of times requested by users IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 hi © 2005 IBM Corporation

Applications : JDBC 3. 0 Support Version 3. 0 of the IBM Informix JDBC Driver supports the following features in compliance with the Sun Microsystems JDBC 3. 0 specification: § Internally update BLOB and CLOB data types using all methods introduced in the JDBC 3. 0 specification. § Retrieve auto-generated keys from the database server. § In addition, J/Foundation supports JRE Version 1. 4 and the JDBC 3. 0 specification. 55 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Applications : Full. NET support § The IBM Informix. NET Provider enables Windows. NET applications to access and manipulate data in IBM Informix databases. § The IBM Informix. NET Provider is a runtime library that encapsulates a data access API for use by Microsoft. NET applications. It consists of a set of specialized classes that implement standard Microsoft ADO. NET interfaces and serves as a bridge between IBM Informix databases (data sources) and. NET applications. § Windows client applications written in any. NET supported language can take advantage of the IBM Informix. NET Provider. Some examples of client applications are: – Visual BASIC. NET applications – Visual C#. NET applications – Visual J#. NET applications – ASP. NET web applications § The IBM Informix. NET Provider a connection editor dialog box, a command editor dialog box, and a data adapter wizard as Microsoft Visual Studio add-ins. 56 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Applications: ESQL/C to DB 2 § You can run Informix ESQL/C applications with DB 2 servers and databases. § The Informix ESQL/C product provides a new library that is called when you use the esql command to preprocess your files to work with DB 2. § Informix ESQL/C runs with DB 2 Version 8. 2, or later, running on Linux, UNIX, and Windows operating systems. 57 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

Topics : HDR/ER Enhancements hi Resending indexes in HDR Engine Impact Automatic switchover of HDR servers (DRAUTO) low 58 Easier ER setup with templates Seamless online resynchronization of ER nodes ALTER of replicated tables Number of times requested by users IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 hi © 2005 IBM Corporation

HDR: Resending Indexes in HDR § You can resend an index that became corrupt on the secondary server in an HDR pair. § Resending an index is quicker than dropping and then rebuilding the index on the primary server. § This feature increases the availability of the HDR primary server. 59 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

HDR: DRAUTO § You can automate switching servers for High. Availability Data Replication if the primary server fails by using the DRAUTO configuration parameter. § If DRAUTO is set to either – RETAIN_TYPE or REVERSE_TYPE, the secondary database server switches to type standard automatically when an HDR failure is detected. • RETAIN_TYPE, the original secondary database server switches back to type secondary when the HDR connection is restored. • REVERSE_TYPE, the original secondary database server switches to type primary when the HDR connection is restored, and the original primary switches to type secondary. 60 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

ER Enhancements: Alter table/fragment support § Overview – This feature provides alter support for tables being replicated via Enterprise Replication. – Currently, if the table schema needs to be altered or if the fragmentation strategy needs to be changed, then replication must be stopped, then alter is performed and then replication must be restarted. This is problematic as it makes it impossible to really consider ER in a 24 X 7 environment. List of supported alter operations 1. The ability to add/drop default values 2. The ability to add/drop SQL checks 3. The ability to add/drop fragments 4. The ability to attach/detach fragments 5. The ability to add/drop columns 6. The ability to recluster indexes 7. The ability to alter non replicated columns 61 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

ER Enhancements: Alter table/fragment support § Alter mode – Alter mode is a new state of a replicated table. – DML operations are disallowed in this mode. – DDL operations on a replicated table are allowed only while the table is in alter mode. – Alter mode can be set/unset manually through CDR CLI or implicitly through SQL alter statement itself. – Clients can alter the replicated table without placing the table in alter mode through ‘cdr alter …’ command. SQL layer will internally do a callback to ER to set/unset alter mode before and after performing the alter operation. One exception to this is ‘attach fragment’ scenario. 62 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

ER Enhancements: Alter table/fragment support § Attach fragment scenario For attaching a new fragment, first DBA needs to place the replicated table in alter mode through CDR CLI interface, drop the primary key, attach the fragment, recreate the primary key then unset alter mode through CDR CLI interface. 63 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

ER Enhancements: Alter table/fragment support § Mastered Replicates 4 Currently no way to know if data types match between replicate nodes 4 Provides data type checking to eliminate possibility of corruption 4 New syscdr tables track data type information 64 IBM Informix Dynamic Server | Key Features in IDS 10. 00 | Chat With The Lab | 2005 -04 -06 © 2005 IBM Corporation

ER Enhancements: Alter table/fragment support § Remastering process – Existing replicate can be redefined by “remastering the replicate”. Through remastering process, a new column can be added/dropped to/from a replicate definition. – Also an existing non-mastered replicate can be converted to a mastered replicate using remastering process. – A replicate can be remastered using two different procedures • Auto remastering – – – 65 Auto remastering can be performed through “cdr remaster” command syntax for “cdr remaster” command cdr remaster –m