IBM Informix - SQL & NoSQL
keshavamurthy 0600019DXM Tags:  performance faster update tuning smarter informix statistics 3,580 Views
At 2010 IIUG conference, when Jack Parker saw the preview of Informix warehouse accelerator, he offered his Amex card to buy the product. Two problems: I couldn't sell since the product was not released. I presume his Amex card has some credit limits :-) I suspect, after that incident, Jack started working on his performance tuning in the old route and produced nice article on Informix Performance tuning (part 1).
Note to Jack -- Good news is Informix Ultimate Warehouse Edition with Informix Warehouse Accelerator is now GA. You can buy it any time, even with your Amex.
Nita Dembla developed smarter, faster update statistics for Informix 11.70 and has a good article on it. If you're on 11.70, you should automatically be experiencing the the faster update statistics. With little bit of time, you can make it way faster!. Check out the article.
keshavamurthy 0600019DXM 3,577 Views
Count the number of times the basketballs are passed in this video.
Did you see the Gorilla? Apparently, 46% people focused on counting passes
failed to observe the Gorilla walking around. With so much focus on BI (Business Intelligence),
predictive analysis, we could use a tool to predict, catch disruptive forces on our business or to create one ourselves.
What's the gorilla in database or RDBMS business now? Map-reduce model? Hadoop, HadoopDB?
keshavamurthy 0600019DXM Tags:  intel westmere nehalem accelerator warehouse informix 3,521 Views
Earlier this year, we collaborated with Intel measure performance and scaling of IWA on Intel's latest processors: Nehalem and Westmere. The detailed report was published as article in IBM Data Management Magaine.Apart from the details of the technology, the core message is, IWA exploits the latest features like SIMD, larger on-chip cache in Intel processors. As Intel innovates, IWA customers benefit directly with better performance and lower TCO.Here is a snippet from the article.
keshavamurthy 0600019DXM 3,513 Views
[All errors are mine. The views for information only. These are views of Edward Tufte, not IBM].
Organized by The Hive
Edward Tufte will discuss seeing, reasoning, and producing in high science and high art. His current project “The Thinking Eye” suggests self-aware strategies for improving the interface where the real world meets the human eye-brain system.
Do "whatever it takes" to represent the data in a way to accurately understand, think, interpret and compare.
keshavamurthy 0600019DXM Tags:  grid phoenix flexible user group informix warehouse accelerator ultimate arizona edition 3,460 Views
On May 2nd, we have a Arizona Informix user group meeting. Mahesh, Madison and I will be talking and demoing at the event.
We're in conference room 539, 2929 N. Central Ave, Phoenix, AZ 85012
Here's the agenda:
12:30 - 1:00 Lunch
1:00 - 1:30 Roadmap + future directions. Mahesh dashora, Senior Manager, IBM Informix Development
1:30 - 2:30 Informix Ultimate Warehouse + IWA + IWA Demo. Keshav Murthy. STSM, Informix Development
2:30 - 4:00 Flexible Grid Madison Pruet. STSM, Informix Development.
4.00+ Q&A, Networking.
Looking forward to seeing our AZ customers.
Database deployments are increasing on the cloud. Data management requirements are increasing. There are couple database-as-a-services already in place (SQL Azure from Microsoft and database.com from salesforce)... There could be more..I haven't done exhaustive search.
The promise of perceived unlimited hardware in the cloud -- public, private, hybrid -- requires change in the database approach/architecture/etc. The traditional single machine or cluster assumptions won't work. Although, I've referenced Amazon cloud many times, these should be applicable to any cloud. I plan to do more with IBM Cloud soon... :-)
1. Automatic installation, patching and upgrade: Customer requiring the database service should have no requirement to request/copy/install/etc. What if customer running a app on the cloud can simply connect to database service, specify some high level sizing (low/med/high), start creating databases/tables/etc without having to worry about *anything else*. This should be part of the service rather than a customer/IT/DBA worrying about it. Eventually, we'll probably have sense the data growth and transparently handle via instance migration/scale out/etc.
2. Automatic High Availability and failover. Because this is a service, high availability should be part of the infrastructure/installation and not something DBA/etc have to setup. Amazon storage services comes with HA automatically... There's nothing for the user of this service to do. When you request for database service, the database/etc should have HA as a built-in feature.
3. Automatic Scaling: The mechanism to add or subtract the servers will be very domain specific. Amazon itself has a scaling service (http://aws.amazon.com/autoscaling/). However, at the database level, scaling brings multitude of issues. Does that mean replicated servers or application aware data partitioning (aka sharding). When you have grid of servers, do you support grid object reference or support query partitioning based on workload? This is a big question. With Hadoop, customers have scaled out to 1000s of nodes. We've to define the domain for database and deal with it.
4. Automatic Tuning: Based on the instance (small, medium, large) and policy (oltp/warehouse/mixed) the tuning (initial and feedback based) should be done (mostly) automatically and behind the scene. Some of the recent products like Informix Warehouse Accelerator, SAP's HANA try to limit or eliminate tuning altogether.
5. Automatic Backup. In the EC2 for example, the databases should be automatically backed up to S3. IBM Cloud will have its equivalent. With HA available, restore will be required lesser for recovery, but more for cloning across data centers/zones/etc.
6. Automatic Syncronization: public, private, hybrid cloud is the reality. Enterprises need to export and import data between on premise and cloud or between cloud to cloud deployment. The service should provide very easy way to do this synchronization without customers having to write programs/maintain it/etc.
7. Automatic Security: For customers who haven't used (or are not going to use) Cloud, Security is the biggest concern. Obviously, many customers have solved/moved beyond this. We've to implement the industry best practices and enable standard protocol (SSL,etc) for data access.
I'll have some blogs in later on to see how Informix is addressing these as a product...we don't have a cloud service... yet!
Today marks 10 years of Informix as part of IBM family. 10 years is a long time. But, IBM celebrated its 100 year anniversary couple of weeks ago and will celebrating the centennial for the next year. To put it simply, IBM has been around for an order of magnitude longer than Informix has been with IBM. And that’s a good thing.
Informix creations, innovations in the last 10 years are too
numerous to mention. Rocky to roaring
Panther have moved the state of art to higher level. Apart from inventing and innovating within
Informix team, we’ve learnt to collaborate with IBM teams across the blue
planet to bring new technologies to market in record time. Whether it’s deep compression in IDS or deep
columnar technology in IWA, we’ve gotten tremendous support within IBM to bring
the innovations from research to Market. For the SQL/Optimizer team, bringing and improving new technologies like ISTAR to Star joins, fragmentation to fragment level statistics, accelerator, has been gratifying.
But, I’m more excited about how our customers have been able to meet their technical and business goals using Informix. We started working with Cisco around 2004 and created the backbone for their mission critical applications like Unified Call Manager and Telepresence. In Retail, system performance and availability directly translates to revenue and profit. We’ve worked with number of retailers – small and big – to improve their performance and data availability. Below is a nice ad featuring some of the latest examples of customers exploiting Informix technlogy to build smarter planet. See the ad at: http://youtu.be/D1LbxxfD_tY
IBM has seen most of the technological revolutions of 20th century, created many of them, contributed to majority of them. See the highlights at this movie at http://www.youtube.com/IBM/
I expect Informix to thrive even more in coming years -- innovating, collaborating, succeeding even more under the big IBM family.
So, for Informix, this is not the end. It is not even the beginning of the end. But it is, perhaps, the end of the beginning. Let’s start of another phenomenal journey ahead.
keshavamurthy 0600019DXM Tags:  informix procedure dynamic accelerator stored warehouse sql 3,338 Views
Once you deploy and load the data mart on to Informix Warehouse Accelerator, all your dynamic queries will get accelerated. The phrase "dynamic queries" needs little bit of explanation. Dynamic queries include the following:
Straight forward, so far. The unsupported statements are the static statements within SPL stored procedure. These statements are prepared once and the same plan is reused by Informix for subsequent invocation of the procedure/statement. Only when there change of table schema, permissions or newer statistics, Informix will recompile the statements referencing affected tables.
These static statements are not evaluated for IWA acceleration and are always executed locally on Informix.
Summary, before we look at the examples:
Now, let's look at couple of , somewhat lengthy, examples:
drop procedure sales_report(int);
This procedure returns the result set of up to 100 rows. The following is the plan used.
drop procedure dyn_sales_report(int);
And, here is the query plan for the dynamic statement within this stored procedure.
The iterators within query statistics section of this plan shows only one iterator (dwa) indicating the query was sent over to IWA and the results were received via the dwa iterator. On 11.70, this iterator is shown as "remote", but it does the same thing. There is one of problem (bug) in this -- we're not printing the SQL-- sent over to IWA.. When you execute the query directly (outside of the procedure), the whole plan including SQL-- gets printed. The fix for this is coming up soon.
keshavamurthy 0600019DXM Tags:  columnar extreme compression warehouse informix accelerator deep 3,250 Views
IBM Webcast: Data Management for a New Generation with IBM Informix.
Date: Sep 8, 2011
Time: 1:00 PM - 2:00 PM (Eastern Time)
Hosted By: Kristine Enes (IBM)
Presented By: Tom Rieger (IBM), Scott Whalen (IBM)
This session is designed to help you learn about why Informix still is the best solution - from the most mundane of data needs, all the way to your largest of systems. We will show examples where Informix is better than any other technology when you combine all of its capabilities.
Come listen to what your competition is telling your customers about 3rd Generation database in-memory technologies and find out how IBM - Informix can deliver unprecedented query response times in a completely integrated, flexible and cost effective environment.
This webcast is intended for technical and business professionals new to Informix as well as individuals familiar with the platform who are interested in learning how to gain incremental value within your environment.
Featuring live Q&A with: Tom Rieger & Scott Whalen, IBM Informix SWAT Team Leaders.
Please join us to learn more about our latest offers available to both existing and new Informix clients!
Register Now: https://events.webdialogs.com/portal/wipevents/register.php?id=338476fce3&am
This relational database systems runs or embedded in applications for worlds biggest retailers, banks, telecom systems including Whitehouse.
At IIUG, on Monday and Tuesday, we'll have two sessions on What's New in IBM Informix 12.10? giving overview of 12.10 features as well as talking about other sessions in various domains. In the remote case, you cannot attend that talk :-), here is the IIUG session grid with my recommendations for Informix 12.10 warehouse features.
keshavamurthy 0600019DXM 2,621 Views
This redbook will be published later this year. We'd like to invite comments/requests for topics.
Here is the draft table of contents. Checkout the available Informix redbooks at: http://www.redbooks.ibm.com/redbooks.nsf/searchsite?SearchView&query=informix&SearchOrder=4&SearchFuzzy=
See this for the pdf version.
Title: Query Acceleration for business using Informix Warehouse Accelerator
The Informix Warehouse Accelerator is a breakthrough technology that changes how businesses will view and deploy analytics. Not only does it promise several order of magnitude differences in performance for business queries, it eliminates the need for query tuning, data management, and cube building activities normally associated with enterprises seeking acceptable performance in analytics. Together with the Informix Dynamic Server, it offers a single environment where traditional OLTP and current OLAP processing can be combined in a cost-effective manner. This IBM Redbooks publication will provide a comprehensive understanding on how to configure and deploy in this hybrid environment. It will include data mart design, data mart loading and incremental refresh, and proper execution of queries. It will also include discussion of a BI tool, for example, Cognos, to be used in conjunction with IWA.
Audiences: DBAs, system supports, and users who are interested in the products.
Product: Informix Ultimate Warehouse Edition and Informix Growth Warehouse Edition, 12.10 release
Table of content:
Chapter 1 Introduction to Informix Warehouse Accelerator
1.1. Origin of Informix Warehouse Accelerator
1.2. Informix Warehousing and Informix Warehouse Accelerator architecture
-- Include high level information about how/where IWA can be used
Chapter 2 Designing Data mart
2.1. Logical database design overview
2.2. Design considerations
-- Including considerations for operational applications and data warehousing applications
2.3. IWA data mart
Chapter 3 Designing an IWA environment
-- Including guidelines for properly sizing for # of CPU/cores and amount of memory
3.2.1. Concurrent query in a single IWA instance
3.2.2. Availability considerations
Chapter 4 Installation and configuration
-- including examples of using tools if applicable
Chapter 5 Creating data mart
5.1. Data mart definition
5.1.1. Workload analysis (using OAT)
5.1.2. Interactive design (with ISAO Studio)
5.2. Data mart deployment
5.3. Initial data loading
Chapter 6 Query execution
-- including examples of using tools if applicable
6.1. Query execution flow
6.2. Enabling query acceleration
6.3. Accelerated query types
6.4. Unaccelerated query types (rewording the title)
6.5. Mixed mode
6.6. Monitoring query execution (title ??)
-- Including query troubleshooting
Chapter 7 Managing data mart
-- including examples of using tools if applicable
7.1. Data synchronization
7.1.1. Incremental loading
7.1.2. Partition refresh and calendar refresh
7.1.3. Automating data load
18.104.22.168. Trickle feed
7.2. Enabling data mart
7.3. Disabling data mart
7.4. Dropping data mart
7.5. Changing schema
Chapter 8 Administration
-- Administration tasks other than managing data mart
8.1. Administrating IWA using ondwa
8.2.1. Informix Data Server
8.3. Misc administration tasks (such as backup and recovery)
Chapter 9 Use of Cognos with Informix Warehouse Accelerator
9.1. Mapping a Cognos Framework Manager to the IWA mart
9.2. Reviewing models and reports with IWA routing requirements
9.3. Leveraging sending application context to Informix and IWA
9.4. Considerations for interactive analysis and query workloads
Chapter 10 Developing a proof-of-concept application
10.1. Assessing qualification and compatibility for IWA
-- Assessing customer pains for analytic queries
-- Assessing po- Justify the Need for IWA PoC
-- Qualification feasibility / suitability questionnaire or semi-automated qualification tool / decision workflow (an IWA qualification questionnaire exists)
-- BVA / ROI tool (exists already)
-- Help with Suitability of IWA and justification for PoC
10.2. Assessing Platform, Environment and Client Application Readiness
-- Identify Potential Need for Hardware (Loaner) for PoC and real environment (in future proposal that includes hardware)
-- Automated Sizing Tool for IWA (does not exist yet)?
--- Automated System Requirements Assessment tool / scripts for IDS and IWA sides, for IWA readiness (does not exist)
--- Automated SQL Tracing + Identify I/O bound slow queries candidates for IWA, based on workload (sql file) given by customer, or based on SQL tracing during Adhoc or OLAP/BI/DSS processing (does not exist)
10.3. Quick Start guide
-- Create a virtual image, or make a real/physical environment ready for the IWA PoC
--- Download a ready-to-use TPC-E or TPC-DS demo database and step-by-step + ready to run queries
--- Steps to install and set up an on-site or virtual environment, from the Informix Virtual Appliance + Trial edition of IWA - quick start guide
10.4. Deploy a technical PoC and deliver results
-- Benchmark templates, ready to use, to show performance results and deliver report with results
-- FAQ on How to re-write different queries (ex: correlated sub-queries, expressions that are not supported, etc... identify which queries are not supported or would require a rewrite)
-- Tool to gather information automatically for tracing and errors logs, for Tech Support : MustGather guide
Appendix A --Tools for Informix Warehouse Accelerator lifecycle
10.5. -- Using a table to include the information about where/when to use the tools
10.6. IBM OpenAdmin Tool
10.6.1. IWA administration
10.6.2. Workload Analysis
10.7. Builtin stored procedures
10.7.1. Java CLI
10.8. ISAO Studio
I'm writing an article on this topic. Here are the questions I've collected so far.
Please let me know if you have more questions/comments...
I'll include those Q&A in the final article. Thanks!
Information week is conducting a survey on database technology. Attached is the info.
Please go ahead participate at: http://informationweek.stateofdatabase.sgizmo.com
As infrastructure elements go, databases may not be as sexy blade servers or private clouds. But they’re arguably the foundation of most enterprises’ businesses.
InformationWeek Analytics is conducting a survey on the state of database technology in the enterprise. The results of the survey will appear in an upcoming issue of InformationWeek as well as an in-depth InformationWeek Analytics report.
This survey will take about 10 minutes to complete. Upon completing the survey, you will be eligible to enter a drawing to receive one Apple 32GB iPod touch valued at $299 from TechWeb. Your responses will remain confidential and will only be reported in aggregate.
To take the survey, simply click here:
We look forward to sharing the results of this survey with you soon at www.analytics.informationweek.com and www.informationweek.com.
Managing Editor, Research
Contest rules: http://www.informationweek.com/research/2010databasetechnology_rules.jhtml