KC Informix Users Group 10 Cheetah 2 Features

1. KC Informix Users Group 10 Cheetah 2 Features Presented By: Sanjit Chakraborty / Jeff Laube Date: Jan 22, 2009

2. Agenda • Enhanced Configuration Options During Installation • Automatic Statistics Updating • Control External Directives for a Session • Automatic Statistics & Distributions with Create Index • Enhanced Startup script customization • Visual Explain • BIGINT/BIGSERIAL • Secure Socket Layer (SSL) • Savepoints • LIMITNUMSESSIONS

3. KC Informix Users Group Feature 1: Enhanced Configuration Options During Installation

4. Enhanced Configuration Options During Installation Instance Configuration Wizard • Automatically creates and custom onconfig file for future use • The onconfig file generates during create demonstration database server with custom setup • GUI and Console installation wizard both supports • Windows limitation – • Only available with a custom setup in GUI mode • Not available with silent installation • Parameters value decides depending on hardware and database system needs • Configuration file generated with Standard setting for any error encountered during using wizard

5. Input for Configuration File • Installation Directory • Database Server Name • Server Number • Rootpath • Rootsize • No. of central processing units (CPUs) • Memory: System RAM dedicated to the IDS server (in MB) • No. of online transaction clients • No. of decision support clients

6. Updated Configuration Parameters • ROOTPATH • ROOTSIZE • MSGPATH • DBSERVERNAME • DBSERVERALIASES • SERVERNUM • ALARMPROGRAM • DRLOSTFOUND • BAR_ACT_LOG • BAR_DEBUG_LOG • SYSALARMPROGRAM • DUMPDIR • JVPJAVAHOME • JVPHOME • JVPPROPFILE • JVPLOGFILE • JVPCLASSPATH • BUFFERPOOL • VPCLASS

7. Files Generated • Customized Onconfig file • <INFRMIXDIR>/etc • Onconfig file • onconfig.<server name> • Updated onconfig parameters will be added at bottom of the file • Environment Setup Files • <INFRMIXDIR>/demo/server • profile_settings (ksh) • Profile_settings.csh

8. Enhanced Configuration Options During InstallationInstance Configuration Wizard UNIX install (either Typical or Custom) Do you want to create an IDS demonstration database server instance? [X] 1 - Yes [ ] 2 - No Demonstration Database Instance Configuration [ ] 1 - Use my own configuration file. [ ] 2 - Use the default configuration file. [X] 3 - Customize the default configuration file to suit your needs and hardware. Specify your server configuration parameters (Server Number should be in the range of 0 to 255). Server Name [demo_on] ids1150 Server Number [0] 50 ROOTPATH [/usr2/products/11.50.UC2/demo/server/online_root] /chunks/1152/chunk1 ROOTSIZE (MB) [760] 204800 Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] Configuration Setup Modify any values if you want to customize database server configuration settings. Processors to use [1] 1 Memory to use (MB) [512] 128 Online Transaction Clients (applications that frequently update databases with multiuser input) [1] 1000 Decision Support Clients (applications for querying databases to gather reporting information) [1] 25

9. Enhanced Configuration Options During InstallationInstance Configuration Wizard Installation log after configuration parameters set: Installing IBM Informix Dynamic Server Version 11.50. Please wait... Creating demonstration database server instance ... Creating demonstration database server instance ... - Creating demonstration database server onconfig file at /usr2/products/11.50.UC2/etc/onconfig.ids1150 Creating demonstration database server instance ... - Demonstration database server instance initialized successfully . Please read the information below. The IBM Informix Dynamic Server demonstration database server has been created. Check the log file to determine if it was successfully initialized: /usr2/products/11.50.UC2/demo/server/online.log. (Note: the above log is the verbose output to screen, it is not logged to a physical file)

10. Enhanced Configuration Options During InstallationInstance Configuration Wizard Windows install

11. Enhanced Configuration Options During InstallationInstance Configuration Wizard

12. Enhanced Configuration Options During InstallationInstance Configuration Wizard .

13. Enhanced Configuration Options During InstallationInstance Configuration Wizard .

14. KC Informix Users Group Feature 2: Automatic Statistics Updating (AUS)

15. Why Auto Update Statistics (AUS)? • Users must update statistics and distributions manually to help optimizer make correct decisions to run query efficiently • Many of us do not realize this and encounter poor performance • When to run Update Statistics? • What statistics and distributions need to be updated? • Not easy to understand • Change periodically • Differ for each system • AUS provides ability to automate the maintenance of optimizer statistics

16. AUS Implementation • A set of procedures which will be invoked by the database scheduler to automate update statistics • Installed as part of the server • Users can write SQL statements to manipulate the AUS configuration • Open Admin Tool (OAT) graphical interface allows easier control of the different AUS policies • Open source download available from iiug.org and IBM websites • Display AUS information in easy to read format • Simple point and click interface

17. AUS Implementation - Database Tasks • Tasks in ph_task table • Auto Update Statistics Evaluation • Analyzes all the tables in all logged databases • Locates tables which require new or updated optimizer statistics • Builds the update statistics commands • Inserts the commands into the table aus_cmd_list • Auto Update Statistics Refresh • Executes the update statistics commands from aus_cmd_list table in a priority order within a specified time • After the command completes, it is moved to the aus_cmd_comp table

18. Configuration Parameters – PH_THRESHOLD Table Use OAT AUS configuration page or update sysadmin:ph_threshold to modify these parameters

19. Scheduling Information • Since AUS is a resource intensive operation, a specific run window can be defined. The run window definition includes start time, the end time and the days of the week to run the job. • Can be configured from the Open Admin tool. • Implemented by setting the start and end times of the AUS refresh (Auto Update Statistics Refresh) task in ph_task table in sysadmin. • Default runtime window is 1:00 AM – 5:00 AM daily. .

20. KC Informix Users Group Feature 3: Automatic Statistics & distributions With Create Index

21. Indexes and Statistics/Distributions • Without statistics or distributions the index will not be considered by the optimizer • Common Mistake • Create an index and see no improvement in select performance • We need to run UPDATE STATISTICS LOW at a minimum for newly created indexes to be considered for query access plans. Why can’t this be done automatically when new indexes are created?

22. Create Index Distributions & Statistics • Implicit or explicit CREATE INDEX automatically creates statistics & distribution for the leading column of an index • UPDATE STATISTICS LOW Statistics • UPDATE STATISTICS HIGH Distribution SET EXPLAIN Output for CREATE INDEX statement: CREATE INDEX idx1 on tab1(col1_int) Index: idx1 on informix.tab1 STATISTICS CREATED AUTOMATICALLY: Column Distribution for: informix.tab1.col1_int Mode: HIGH Number of Bins: 207 Bin size: 4800.0 Sort data: 0.9 MB Completed building distribution in: 0 minutes 1 seconds • Enabled always -- cannot turn off

23. Limitations to Create Index Distributions • Index distributions are NOT created when -- • If the lead of the index is a UDT (builtin or non-builtin) • Index is a functional index • Index is a VII index • Number of rows in table is < 2 .

24. KC Informix Users Group Feature 4: Control External Directives for a Session

25. External Directives (Previous references) Query performance problems and you cannot change it because • You don’t own the application or a 3rd party application • You don't have the source code access or the developer is unavailable • The application is heavily used and can't be switched off without schedule a downtime • Update statistics or change OPTCOMPIND doesn't affect

26. External Directives (Previous references) • External optimizer directives are useful when it is not feasible rewrite a query for a short-term solution to a problem • Enabling external directives Environment Variable: IFX_EXTDIRECTIVES Configuration parameter: EXT_DIRECTIVES • SQL Syntax: SAVE EXTERNAL DIRECTIVES <directive name> [ACTIVE | INACTIVE | TEST ONLY] FOR <query> • Directives saved in sysdirectives catalog

27. Example: External Directive • Schema for table items create table items ( item_num smallint, order_num integer, stock_num smallint not null , manu_code char(3) not null , quantity smallint, total_price money(8,2) ); create index items_idx1 on items (item_num, manu_code); • Query runs from an application which used the items_idx1 index path SELECT item_num, manu_code FROM items WHERE manu_code = "ANZ"

28. Example: External Directive (cont.) • Some reason query is running slow because of sequential scan instead follow the index path • Update statistics is not helpful • Only way query can ran faster, using INDEX directive SELECT {+INDEX (items item_idx1)} item_num, manu_code FROM items WHERE manu_code = "ANZ" • Without changing the application code force optimizer to chose the index path • Solution is use external directive

29. Example: External Directive (cont.) • Save and Active the query as external directive SAVE EXTERNAL DIRECTIVES --+INDEX(items item_idx1) ACTIVE FORSELECT item_num, manu_code FROM items WHERE manu_code = "ANZ" • If external directive enabled, query should follow the index path Environment Variable: IFX_EXTDIRECTIVES Configuration parameter: EXT_DIRECTIVES

30. Session Level External Directives Overview • New EXTDIRECTIVES session environment option of the SET ENVIRONMENT statement • Overwrites the EXT_DIRECTIVES configuration parameter • Controls whether external directives are enabled, disabled or have default behavior during a session • Default behavior specified in the EXT_DIRECTIVES ONCONFIG parameter and client-side environment variable IFX_EXTDIRECTIVES

31. Control External Directives for a Session • Syntax SET ENVIRONMENT EXTDIRECTIVES ‘[DEFAULT | off | on]’; or SET ENVIRONMENT EXTDIRECTIVES ‘[DEFAULT | 0 | 1]’; • Example * Disable external directives within a session SET ENVIRONMENT EXTDIRECTIVES ‘off’; .

32. KC Informix Users Group Feature 5: Enhanced Startup script customization

33. New oninit option: -w • Use to customize startup scripts and automate startup for the oninitutility. • The -w option forces the server to wait until it successfully initializes before returning a shell prompt. • The -w option provides a return code so that you can check if the IDS started without incident return 0 when success return 1 when initialization fails or configurable timeout achieved • In case of failure online.log updated appropriately

34. oninit -w <delay> • Configurable timeout period during server initializes oninit –w <delay in seconds> • If fails return 1 with a message in online.log: Warning: wait time expired • Default timeout: 600 sec • Return code 1- Not necessary server crash or failure

35. Notes • In a high-availability environment, you can only use the oninit -w command on primary servers; it is not valid on secondary servers • Returns success when sysmaster, sysutils, sysuser and sysadmin are successfully created .

36. KC Informix Users Group Feature 6: Visual Explain

37. Visual Explain • A new user defined function (C-UDR) EXPLAIN_SQL was implemented in IDS11.50. • The new UDR EXPLAIN_SQL can be used to obtain a query explain output in XML format. • IBM Data Studio can interpret the XML formatted explain file and show the query plan graphically to the user.

38. Visual Explain • EXPLAIN_SQL UDR is mainly implemented to allow common tooling such as the new IBM Data Studio to be able to get XML explain output through the function and show users the graphical query plans. • If user wants to obtain the XML explain output and use their own graphic tool to see the query plan, the UDR should be ran by JDBC or JCC program.

39. Visual Explain • Example of JDBC program running EXPLAIN_SQL CallableStatement cstmt2 = conn.prepareCall("{call informix.explain_sql(?, ?, ?, ?, ?, ?, ?)}"); /* set up the parameters */ cstmt2.registerOutParameter( 1, Types.INTEGER ); cstmt2.registerOutParameter( 2, Types.INTEGER ); cstmt2.setString(3,null); cstmt2.setNull( 5, Types.BLOB ); // Filter cstmt2.registerOutParameter( 6, Types.BLOB ); // XML_OUTPUT cstmt2.registerOutParameter( 7, Types.BLOB ); // XML_MESSAGE file = new File("./xmlins"); fin = new FileInputStream(file); byte[] buffer = new byte[8000]; IfxLobDescriptor loDesc = new IfxLobDescriptor(conn); IfxLocator loPtr = new IfxLocator(); IfxSmartBlob smb = new IfxSmartBlob(conn); int loFd = smb.IfxLoCreate(loDesc, smb.LO_RDWR, loPtr); n = fin.read(buffer); if (n > 0) n = smb.IfxLoWrite(loFd, buffer); smb.IfxLoClose(loFd); Blob blb = new IfxBblob(loPtr); cstmt2.setBlob(4, blb); // set the blob column

40. Visual Explain • Example of JDBC program running EXPLAIN_SQL … continued ResultSet rs = cstmt2.executeQuery(); int outmajver = cstmt2.getInt(1); int outminver = cstmt2.getInt(2); /* read the xml explain output if there is any */ while (rs.next()) { byte[] buf = new byte[80000]; b = (IfxBblob) rs.getBlob(1); if (b != null { IfxLocator loptr = b.getLocator(); IfxSmartBlob smbl = new IfxSmartBlob(conn); int lofd = smbl.IfxLoOpen(loptr, smbl.LO_RDONLY); outfile = new File("./out.xml"); fout = new FileOutputStream(outfile); int size = smbl.IfxLoRead(lofd, fout, 80000); smbl.IfxLoClose(lofd); smbl.IfxLoRelease(loptr); } }

41. Visual Explain • Example of JDBC program running EXPLAIN_SQL … continued /* get blob out parameters */ outmsg_b = (IfxBblob)cstmt2.getBlob(7); if (outmsg_b == null) System.out.println("outmsg_b is null"); else { IfxLocator xml_msg_loptr = outmsg_b.getLocator(); IfxSmartBlob xml_msg_smbl = new IfxSmartBlob(conn); int msg_out_lofd = xml_msg_smbl.IfxLoOpen(xml_msg_loptr, xml_msg_smbl.LO_RDONLY); msg_out_outfile = new File("./xml_msg.xml"); msg_out_fout = new FileOutputStream(msg_out_outfile); int xml_msg_size = xml_msg_smbl.IfxLoRead(msg_out_lofd, msg_out_fout, 80000); xml_msg_smbl.IfxLoClose(msg_out_lofd); xml_msg_smbl.IfxLoRelease(xml_msg_loptr); }

42. Visual Explain • Sample XML explain output <?xml version="1.0" encoding="UTF-8"?> <explain dbplatform="IDS" dbversion="11.11" timestamp="11-14-2007 11:50:29"> <plans> <source> <query>select * from chartab where c1 = 2 ;</query> </source> <dataview id="v0" type="label"> <dataseq dataid="0" /> </dataview> ... <diagram id="g0" name="Query" structure="tree"> <node id="n0" type="060f002b" labelviewid="l0"> <descriptorlink descriptorid="d0" /> ... <node id="n3" type="0212003a" labelviewid="l3"> <descriptorlink descriptorid="d3" /> </node> </node> </diagram> <descriptor id="d0" name="Query" type="ids.query"> <datatitle nametitle="NAME">VALUE</datatitle> </descriptor> ... </plans> </explain>

43. Visual Explain • Sample visual explain in Data studio

44. KC Informix Users Group Feature 7: BIGINT / BIGSERIAL

45. BIGINT / BIGSERIAL Overview: • New ANSI standard SQL data types BIGINT and BIGSERIAL were introduced in IDS. • Aims to provide a better performance alternative to INT8 and SERIAL8 data types. However, IDS will continue its support of INT8 and SERIAL8 data types in existing customer applications.

46. BIGINT / BIGSERIAL Description • INT8 and SERIAL8 data types which are internally implemented as a 10-byte structure, ifx_int8_t. INT8 and SERIAL8 take up to 10 byte of storage in IDS. • This feature will implement BIGINT and BIGSERIAL data types using native 8-byte integers. It will take 8-byte to store these data types. • Like INT8, BIGINT will store numbers range from 9,223,372,036,854,775,807 to 9,223,372,036,854,775,807 [(2^63-1) to 2^63-1]. • The number –9,223,372,036,854,775,808 is a reserved for a NULL value and cannot be used.

47. BIGINT / BIGSERIAL Description • Like SERIAL8, BIGSERIAL will store numbers range from 1 to 9,223,372,036,854,775,807 [or 1 to 2^63-1]. • Compare to INT8, BIGINT requires less storage space and more important, should offer better performance in general because all arithmetic operations will be done using the native 8-byte integer instead of dealing with the 10-byte ifx_int8_t structure. • BIGINT and BIGSERIAL will be reserved keywords. • A table can have only one SERIAL column, and either one SERIAL8 column or one BIGSERIAL column.

48. BIGINT / BIGSERIAL Description • BIGINT and BIGSERIAL uses native 8-byte data type of the OS. On 32-bit platforms it is ‘long long’. Under certain compiler options the ‘long long’ is not supported. During compilations of customer application if they detect error they can remove the Compiler option that causes not define ‘long long’. The alternative to this compile with –DNOBIGINT, by doing this customer may not be able to use this feature fully in their applications. • The new SQL data types will be classified as Exact Numeric Types under Built-in Data Types and will be applicable to all DDL/DML statements wherever built-in data types are currently used.

49. BIGINT / BIGSERIAL Description • The BIGINT and BIGSERIAL SQL data types is supported in the following products • IDS and its Utilities • ESQL/C • ODBC • JDBC • Common Clients and derivatives

50. BIGINT / BIGSERIAL Example • CREATE TABLE T1 (C1 BIGSERIAL(12345), C2 BIGINT); • CREATE UNIQUE INDEX IX1 ON T1(C2); • INSERT INTO T1 VALUES (0, 1234567); • CREATE TABLE CT1 ( A BIGINT , B BIGSERIAL) FRAGMENT BY EXPRESSION A <= 1000000000 IN DBSPACE2, A > 1000000000 AND A <= 29990000000 IN DBSPACE3, A > 29990000000 AND A <= 999999999999999 IN DBSPACE1;