Version 11.70 Overview

1. Version 11.70 Overview John F. Miller III, IBM

2. Talk Outline 11.70 Overview Storage Enhancements Storage Provisioning Storage Optimization Compression Index Improvements Forest of Tree Indexes Create Index extent sizes Constraint without an index Miscellaneous Network Performance Pre-Load C-UDRs Fragmentation / Partitioning Interval Fragmentation Add and Drop Fragments Online Fragment Level Statistics Data Warehouse Multi Index Scans Star and Snowflake joins 1 Page 1

3. Storage Provisioning 2

4. What is Storage Provisioning • To proactively or reactively add storage to eliminate out of space errors • Monitoring spaces and automatically grow a container when its free space falls below a specific amount. • Stalling an SQL which is about to fail because of insufficient space until space is allocated to the depleted container • The ability to tell Informix about disk space that can be used to solve storage issues in the future • Raw Devices • Cooked Files • Directories

5. Benefits of Storage Provisioning "Out-of-space" errors are virtually eliminated. Manual expansion and creation of storage spaces without having to worry about where the space will come from Automatic expansion of dbspaces, temporary dbspaces, sbspaces, temporary sbspaces, and blobspaces. Feature is fully incorporated into OAT.

6. Storage Provisioning: The Power of 2 • Two available modes: • Manual • Automatic • Two available space expansion methods: • Chunk extension • Chunk creation • Two available interfaces: • sysadmin task()/admin() functions (SQL interface) • OAT (Graphical interface)

7. Storage Pool Facts EXECUTE FUNCTION task("storagepool add", “/work/dbspaces/dbs1", “0", “1GB", “100MB", “1"); • What is the Storage Pool • How the DBA tell’s Informix about space it can use to solve future space issues • A file, device, or directory in the pool is called an entry. • There is one storage pool per IDS instance. • You can add, modify, delete and purge storage pool entries.

8. OAT’s View of the Storagepool Automatic policies Summary of space left in the storage pool

9. Extendable Chunks EXECUTE FUNCTION task(“modify chunk extendable on”, “13”) EXECUTE FUNCTION task(“modify chunk extend”, “27”, “2GB”); The ability to expand an existing chunk Default upon creation is non-expanding chunks Example of enabling the extendable property of chunk 13 A Chunk can be extended automatically or manually Example of manually extending chunk 27 by 2 GB Extending chunks do NOT consume space from the storagepool

10. OAT’s View of the Chunk Pod Fragmentation map of selected chunk • Chunk Actions • Extend a Chunk • Add a new chunk • Modify chunk settings • Drop a chunk

11. Expanding a Storage Container • Keep the addition of space to a storage container simple • The creator of a storage container specifies how a space should grow • Manual allocations of space, Just say do it • Use the predefined container provisioning policies to allocated new space to a container • Determines if any chunk in the storage container is expandable • If no chunk can successfully expand, then add a new chunk

12. Expanding a Space in OAT

13. Creating or Dropping a Space with the Storagepool EXECUTE FUNCTION ADMIN ('create dbspace FROM STORAGEPOOL', 'orders_dbs', '100M') EXECUTE FUNCTION ADMIN ('drop dbspace to storagepool', 'dbs1'); You can create a new storage container utilizing the space from the storage pool Example of create a 100MB dbspace called orders_dbs You can drop an existing storage container and return the space to the storage pool Example of dropping a dbspace called dbs1

14. Save your Company

15. Prevent Accidental Disk Initialization Save companies from potential disasters Accidental disk re-initialization (i.e. oninit –i) New onconfig FULL_DISK_INIT

16. Storage Optimization 15 Page 15

17. Optimizing Tables All this while accessing and modifying the table!!! AND While you are watching your favorite TV show • As a DBA I need to … • Reduce the number of extents a table contains • Move all rows to the beginning of a table • Return unused space at the end of a table to the system • Shrink a partial used extent at the end of a table Page 16

18. Storage Management Overview • Defragment Extents • Combine extents reduce the • Data Compression • Reduces the amount of storage taken by a single row • Table Compaction • Reduce the number of pages utilized by a table • Index Compaction • Ensure the index pages are kept full • Automate the optimization of table storage • Applies policies to optimize tablese 17

19. Optimizing Table Extents - Defragment dbspace1 Customer Extent 1 Orders Extent 1 Customer Extent 2 Items Extent 1 Customer Extent 3 2 Orders Extent 2 3 2 Customer Extent 4 Products Extent 1 EXECUTE FUNCTION ADMIN (‘DEFRAGMENT', ‘db1:customer') Customer Extent 5 3 4 Items Extent 2 Number of extents for the customer table 5 3 4 Customer Extent 1 Customer Extent 1 MERGE MERGE Page 18 • The number of extents a table/partition can have has increased • Defragment Extents • Moves extents to be adjacent • Merges the extents into a single extent Example

20. Optimizing Tables and Indexes 19

21. Defragment Table Extents OnLine Page 20

22. Data Compression • Many Benefits • Smaller Archives • More data in the buffer pool • Fewer long/forwarded rows • Few I/O for same amount of data read/written execute function task(“compress table”, “tab1”,”db”) Reduce the space occupied by the row Compressing a table can be done online Compress either a table or fragment Custom dictionary built for each fragment to ensure highest levels of compression Tables with compressed rows are ALWAYS variable length rows 21

23. REPACK Command Customer Tim Frank Chris Jamie Lenny execute function task(“table repack”, “customer”, ”db”) Roy Travis Steve John Moves all rows in a table/fragment to the beginning, leaving all the free space at the end of the table Online operation, users can be modifying the table 22

24. SHRINK Command Customer John Tim Steve Frank Travis Chris Jamie Roy Lenny execute function task(“table shrink”, “customer”, ”db”) • Frees the space at end of table so other table can utilize this space • Entire extents are free • The last extent in a table can be partially freed • Will not shrink a table smaller than the first extent size • New command to modify first extent size • “ALTER TABLE MODIFY EXTENT SIZE” • Online operation 23

25. Automatically Optimize Data Storage 24

26. Index Optimization 25 Page 25

27. Create Index with a Specific Extent Size • CREATE INDEX index_1 ON tab_1(col_1) EXTENT SIZE 32 NEXT SIZE 32; • Create Index with a Specific Extent Size • The create index syntax has been enhanced to support the addition of an extent size for indexes • Better sizing and utilization • Default index extent size is the • index key size / data row size * data extent size

28. Create Index Extent Sizes Ability to specify the extent size when creating an index Allow for optimal space allocation Utilities such as, dbschema, report index extent size create index cust_ix1 on customer (cust_num) in rootdbs extent size 80 next size 40 ; Page 27

29. Creating Constraints without an Index Saves the overhead of the index for small child tables CREATE TABLE parent(c1 INT PRIMARY KEY CONSTRAINT parent_c1, c2 INT, c3 INT); CREATE TABLE child(x1 INT, x2 INT, x3 VARCHAR(32)); ALTER TABLE child ADD CONSTRAINT (FOREIGN KEY(x1) REFERENCES parent(c1) CONSTRAINT cons_child_x1 INDEX DISABLED); Page 28

30. B-Tree Index The figure above one Root Node and access to the underlying twig and leave pages are through this page, which is where there can be mutex contention 29 Page 29

31. New Index Type “Forest Of Trees” Traditional B-tree index suffer from performance issues when many concurrent users access the index Root Node contention can occur when many session are reading the same index at the same time The depth of large B-tree index increases the number of levels created, which results in more buffer reads required Forest Of Tress (FOT) reduces some of the B-tree index issues: Index is larger but often not deeper Reduces the time for index traversals to leaf nodes Index has multiplesubtrees (root nodes) called buckets Reduces root node contention by enabling more concurrent users to access the index 30 Page 30

32. Forrest of Trees Index (FOT Index) create index index_2 on TAB1( C1, C2 ) hash on ( C1 ) with 3 buckets; • Reduces contentions on an indexes root node • Several root nodes • Some B-Tree functional is NOT supported • max() and min()

33. FOT - Determining use - ONCHECK & SYSMASTER • Check oncheck –pT information • Check sysmaster database select nhashcols, nbuckets from sysindices Average Average Level Total No. Keys Free Bytes ----- -------- -------- ---------- 1 100 27 21350 2 655 15 4535 ----- -------- -------- ---------- Total 755 42 25885 There are 100 Level 1buckets (Root Nodes)

34. Network & UDR Performance 33 Page 33

35. Network Performance Improvements 34 • Caching network services • Multiple listener threads for a single server name • Multiple file descriptor servers • Previous network improvements • Dynamic start and stop of listener threads • Pre-allocate users session Page 34

36. Network Performance - Caching Network Services 35 • Database caching of Host, Services, Users and Groups • Avoids going to the operating system for each network call • Administrator defined timeout value set for network caches • ONCONFIG example NS_CACHE host=900,service=900,user=900,group=900 • Each cache is dynamically configurable • onstat –g cache prints out how effectiveness of the caches Page 35

37. Network Performance – Multiple Listeners 36 • Able to define multiple listener threads for a single DBSERVERNAME and/or DBSERVERALIAS • Add the number of listeners to the end of the alias • EXAMPLE • To start three listener threads for the idsserver • Modify the ONCONFIG as follows DBSERVERNAME idsserver-3 Page 36

38. Network Performance Results 37 • My simple network performance tests • 200 users connecting and disconnecting • Connection throughput on an AIX server improved by 480% • Connection throughput on a Linux server improved by 720% Page 37

39. Improve Throughput of C User Defined Routines (C-UDR) Preloading a C-UDR shared library allows Informix threads to migrate from one VP to another during the execution of the C-UDR Increase in performance Balance workloads Without this feature The C UDR shared libraries are loaded when the UDRs are first used The thread executing the UDR is bound to the VP for the duration of the C-UDR execution PRELOAD_DLL_FILE $INFORMIXDIR/extend/test.udr PRELOAD_DLL_FILE /var/tmp/my_lib.so 38 Page 38

40. Verifying the C-UDR shared library is preloaded • online.log during server startup 14:23:41 Loading Module </var/tmp/test.udr> 14:23:41 The C Language Module </var/tmp/test.udr> loaded • onstat –g dll new flags • ‘P’ represents preloaded • ‘M’ represents thread can migrate Datablades: addr slot vp baseaddr flags filename 0x4b247310 15 1 0x2a985e3000 PM /var/tmp/test.udr 0x4c2bc310 15 2 0x2a985e3000 PM 0x4c2e5310 15 3 0x2a985e3000 PM

41. Update Statistics 40 Page 40

42. Agenda • New Brand Name and Editions • Simplified Installation, Migration, and Portability • Flexible Grid with Improved Availability • Easy Embeddability • Expanded Warehouse Infrastructure • Empower Applications Development • Enhanced Security Management • Increased Performance • Other Features 41

43. Seamless installation and Smarter configuration • Can migrate from Informix Version 11.50 11.10, 10.0, 9.40, or 7.31 directly to Informix Version 11.70 • New installation application, using the new ids_install command, makes it easier to install and configure Informix products and features • A typical installation now has improved default settings to quickly install all of the products and features in the software bundle, with preconfigured settings • The custom installation is also smarter than before and allows you to control what is installed • Both types of installations allow you can create an instance that is initialized and ready to use after installation • Must use a custom installation setup if you want to configure the instance for your business needs

44. Changes to Installation Commands • Some installation commands changed • To take advantage of new and changed functionality • To improve consistency across products and operating systems • Depreciated commands • installserver • installclientsdk • installconn • Must use ids_install to install Informix with or without bundled software • New uninstallids command • Removes the server, any bundled software, or both • To remove specific products • uninstall/uninstall_server/uninstallserver • uninstall/uninstall_clientsdk/uninstallclientsdk • uninstall/uninstall_connect/uninstallconnect (formerly uninstallconn) • uninstall/uninstall_jdbc/uninstalljdbc.exe or java -jar uninstall/uninstall_jdbc/uninstaller.jar (depending on how you install the JDBC driver)

45. Auto-Registration and Auto VP Creation • Database extensions (formerly known as built-in DataBlade modules) are automatically registered • Prerequisite tasks, such as registering the extensions or creating specialized virtual processors, no longer required • The BTS, WFSVP, and MQ virtual processors are created automatically • The idsxmlvp virtual processor is created automatically when an XML function is first used • An sbspace is created automatically for basic text searches and spatial extensions, if a default sbspace does not exist • Basic Text Search, Web Feature Service, Node, Spatial, Binary, Large Object Locator, Timeseries, MQ Messaging, and Informix web feature service now be used without first registering them in your database

46. dbschema and dbexport Enhancements • dbschema and dbexport utility enhancement for omitting the specification of an owner • Can use the new –nw option to generate the SQL for creating an object without specifying an owner

47. Generating Storage Spaces and Logs with dbschema • Can now generate the schema of storage spaces, chunks, and physical and logical logs with the dbschema utility • Choose to generate: • SQL administration API commands dbschema -c dbschema1.out • onspaces and onparams utility commands dbschema -c –ns dbschema2.out • For migrations, generate the schema before unload data using the dbexport and dbimport utilities SQL administration API format # Dbspace 1 -- Chunk 1 EXECUTE FUNCTION TASK ('create dbspace', 'rootdbs', '/export/home/informix/data/rootdbs', '200000', '0', '2', '500', '100') # Dbspace 2 -- Chunk 2 EXECUTE FUNCTION TASK ('create dbspace', 'datadbs1', '/export/home/informix/data/datadbs', '5000000', '0', '2', '100', '100') # Physical Log EXECUTE FUNCTION TASK ('alter plog', 'rootdbs', '60000') # Logical Log 1 EXECUTE FUNCTION TASK ('add log', 'rootdbs', '10000') onspaces/onparams format # Dbspace 1 -- Chunk 1 onspaces -c -d rootdbs -k 2 -p /export/home/informix/data/rootdbs-o 0 -s 200000 -en 500 -ef 100 # Dbspace 2 -- Chunk 2 onspaces -c -d datadbs1 -k 2 -p /export/home/informix/data/usrdbs -o 0 -s 5000000 -en 100 -ef 100 # Logical Log 1 onparams -a -d rootdbs -s 10000

48. Support for the IF EXISTS and IF NOT EXISTS keywords • Now you can include the IF NOT EXISTS keywords in SQL statements that create a database object (or a database) • You can also include the IF EXISTS keywords in SQL statements that destroy a database object (or a database) • If the condition is false, the CREATE or DROP operation has no effect, but no error is returned to the application • Simplifies the migration to Informix of SQL applications that were originally developed for other database servers that support this syntax

49. Simplified SQL syntax for Defining Database Tables • No more restrictions on the order in which column attributes can be defined in DDL statements • Simplifies the syntax rules for column definitions in the CREATE TABLE and ALTER TABLE statements • The specifications for default values can precede or follow any constraint definitions • List of constraint definitions can also be followed (or preceded) by the default value, if a default is defined on the column • The NULL or NOT NULL constraint does not need to be listed first if additional constraints are defined • Simplifies the migration to Informix of SQL applications that were originally developed for other database servers that support this syntax

50. Stored Procedure Debugging (SPD) • Need for application developers to debug SPL procedures in Informix when necessary • Should be able to execute the SPL routine line by line, stepping into nested routines, analyzing the values of the local, global and loop variables • Should be able to trace the execution of SPL procedures • Trace output should show the values of variables, arguments, return values, SQL and ISAM error codes • Pre-requisites • Informix 11.70 or above • Integration with the Optim Data Studio procedure debugger • Integration with Microsoft Visual Studio debugger • DRDA must be enabled