Database administrators tend to think of SQL tuning as necessary, important, even critical. The reality is that the savings realized by tuning SQL, usually manifested in saved CPU time and decreases in elapsed times, may offset the costs incurred in time and resources.

This summary of the common SQL application features is a quick reference for application developers who need to understand the frequently used features and functions across platforms.

The Oracle Connector is a connectivity component in IBM InfoSphere Information Server. It is utilized by IBM InfoSphere DataStage and other products in the Information Server suite to perform extract, lookup, load, and metadata import operations on Oracle databases. This article is Part 1 of a series of 2 articles that provide a set of guidelines for tuning the Oracle Connector stages in DataStage parallel jobs with the goal of maximizing their performance.

Large numbers of extract, transform, and load (ETL) jobs need to be validated during the following two common job lifecycle scenarios: Migrating ETL projects/jobs from older version to a new version, or moving jobs from development to QA to production. Enterprises typically validate that their jobs running in a new version of the software or new hardware environment are producing same results as before, giving them confidence that the new system can replace the old system. In a similar way, before a job in the data integration process is deployed in the production environment, it must be shown to have the expected behavior in development, testing, and production environments. In this article, a step-by-step example is given on how DataStage users can use IBM InfoSphere Optim Test Data Management Solution to validate the results of ETL jobs.

It is important to have an up-to-date and consistent backup available so that you can speed database recovery times. IBM DB2 Merge Backup for Linux, UNIX and Windows gives you alternative strategies to eliminate the need to take regular DB2 full backups and instead use multiple delta and incremental backups to build a new full backup copy that is fully recognized by DB2. This step-by-step article introduces you to IBM DB2 Merge Backup and teaches you how to create merge backups using the control file structure in IBM DB2 Merge Backup.

The PostgreSQL Global Development Group will be releasing a security update for all supported versions on Thursday April 4th, 2013. This release will include a fix for a high-exposure security vulnerability. All users are strongly urged to apply the update as soon as it is available.

We are providing this advance notice so that users may schedule an update of their production systems on or shortly after April 4th.

As always, update releases only require installation of packages and a database system restart. You do not need to dump/restore or use pg_upgrade for this update release.

DBAs often need to provide a full clone of a production system for a development and/or testing system. To address the balance between protecting the data while providing realistic test data, some form of data masking is required. Oracle Database 11g's new Data Masking Pack feature provides a permanent, one way mask of sensitive data.

This article explores NoSQL databases, including an overview of the capabilities and features of NoSQL systems HBase, MongoDB, SimpleDB. It also covers cloud and NoSQL database design basics.

Registration for PGCon 2013 is now open. Have a look at our list of speakers. Please be sure to book your accommodation right away. Some hotels will be sold out within a few weeks. We have an accommodation page which will help you find somewhere to stay.

The concept of a sliding window scenario is to manage and keep the same number of partitions on a partitioned table over time. Learn how in this step-by-step from Arshad Ali.

IBM DB2 10.1 Linux, UNIX, and Windows introduces a new join method called zigzag join. Zigzag join improves consistency in performance as well as reduces the execution time of queries in data warehouse or data mart environments with large volumes of potentially partitioned data, complex ad hoc queries, and where database design uses a star schema. This article provides insight into the eligibility criteria of selecting the zigzag plan, how the zigzag join method works, using a zigzag join with a gap in the fact table multi-column index, using the ZZJOIN operator, and using the index advisor for zigzag join.

This article is a deep dive into the Health Advisor plug-in for the OpenAdmin Tool for Informix. As the graphical user interface for monitoring and administering the Informix database server, the OpenAdmin Tool for Informix (OAT) is an ideal vehicle for a health check system for your database server. With the introduction of the Health Advisor plug-in, you can use OAT to regularly analyze the health and performance of your Informix database server. This article covers how the Health Advisor plug-in works, how to use it, and how it can benefit an Informix DBA. It also provides a step-by-step guide to customizing the Health Advisor by adding your own health checks and alarms.

The latest Vectorwise offering provides
high-performance integration for Hadoop

Redwood City, Calif. – Mar. 19, 2013 - Actian Corp. (“Actian”), a leader in next generation big data management and creator of the analytic database Vectorwise and Action Apps, today announced the availability of its Vectorwise 3.0 analytic database with advanced Hadoop integration. This upgraded version opens up the majority of unstructured data for screamingly fast analysis and massive information production in Business Intelligence tools and other applications.

The Vectorwise 3.0 analytic database offers companies high-performance integration for Hadoop with the Vectorwise Hadoop Connector, allowing them to quickly get critical business answers from big data in a way that is solid, reliable and immediately impactful. “We are talking about moving data at a rate of up to 3 Terabytes per hour onto a modest x86 server costing less than $15,000*,” said Fred Gallagher, general manager, Vectorwise. “That’s like downloading 20 movies in two minutes.” Vectorwise 3.0 is now even faster, with a more efficient storage engine, supporting more data types and analytical SQL functions, and enhanced DDL features. It also has improved monitoring and profiling accessibility.

Vectorwise adoption has followed an emerging group of businesses, including data aggregators and providers, and social media and online gaming companies, whose operations are data intensive and where data serves as the company’s core product or service offering. Studying these companies’ usage of Hadoop data led to the innovations in the Vectorwise 3.0 analytic database integrated with the Vectorwise Hadoop Connector.

Previously, companies using Hadoop data stores were challenged with the slowness of the file system’s batch processing of analytics. The Vectorwise Hadoop Connector brings Hadoop analysis closer to the business with extremely timely and responsive multi-user querying and analytics processing. The Vectorwise Hadoop Connector makes it possible for a much wider range of companies to embrace Hadoop technologies and capitalize on its benefits by either logically optimizing IT resources for unlimited low-cost storage or optimizing computational resources by performance requirements.

"An architecture that uses the massive data processing of Hadoop MapReduce to produce dimensionalized data sets that can quickly be moved to the high-performance Vectorwise SQL engine is compelling,” said Steve Miller, co-founder and president of OpenBI. “You get the best of both computational worlds: a reliable scalable data transformation engine in Hadoop MapReduce and a high-performance big data SQL engine in Vectorwise. The Vectorwise Hadoop Connector allows you to move data incredibly fast from one to the other - speeding the process timeline of data engineering to analytics.”

Customers will be able to run high-performance analysis on Hadoop data that meets the speed requirements of doing business on the Internet. Gartner Inc. estimates that by 2015, 65 percent of packaged analytic applications with advanced analytics will come embedded with Hadoop**. The Vectorwise Hadoop Connector is a big step forward for enabling other software companies to begin to leverage the benefits of Hadoop in the design of their solutions.

“Tableau is able to analyze a Vectorwise database with 1 billion rows of data as fast as if it were analyzing on 100 rows of data in a local spreadsheet,” said Dan Jewett, vice president of product management, Tableau Software. “We are looking forward to some of the new capabilities in Vectorwise 3.0 and what we can offer our joint customers.”

Vectorwise 3.0 with Hadoop Connector will be available for download April 2013.

Moving into 2013, Actian continues to think strategically and develop new solutions that will transform how companies manage and derive value from big data. With more than 10,000 customers worldwide, Actian is a leader in helping companies analyze big data in industries ranging from retail, ecommerce, healthcare, manufacturing, finance and social media. To learn more:

• Read the detailed performance results of Vectorwise Hadoop integration in the white paper “Fast Answers from Hadoop with Vectorwise” www.actian.com/fastanswers
• Go to www.openbi.com to learn more about OpenBI’s expertise. OpenBI builds enterprise solutions that use low-cost big data, business intelligence and analytics technologies.

* See whitepaper “Fast Answers from Hadoop with Vectorwise 3.0” www.actian.com/fastanswers

** “Gartner Says Business Intelligence and Analytics Need to Scale Up to Support Explosive Growth in Data Sources,” Gartner Inc. Jan. 24, 2013 http://www.gartner.com/newsroom/id/2313915

About Actian: Take Action on Big Data
Actian Corporation enables organizations to transform big data into business value with data management solutions to transact, analyze, and take automated action across their business operations. Actian helps 10,000 customers worldwide take action on their big data with Action Apps, Vectorwise the analytic database, Ingres an independent mission-critical OLTP database, and Versant object-oriented databases. Actian is headquartered in California with offices in New York, London, Paris, Frankfurt, Hamburg, Amsterdam and Melbourne. Stay connected with Actian Corporation on Facebook, Twitter and LinkedIn.

Actian, Action Apps, Ingres, Vectorwise and Versant are trademarks of Actian Corporation. All other trademarks, trade names, service marks, and logos referenced herein belong to their respective companies.

Gartner’s annual Business Intelligence & Analytics Summit kicked off earlier today where we are focused on sharing our perspective and solutions with customers, partners and industry leaders interested in big data and business intelligence. Later this week, we’ll also be at the GigaOM Structure event. While we’re out attending all these industry events, our engineering efforts have been moving at light speed. I’d like to highlight two efforts from our engineering team that have now come to fruition.

New HDInsight Service Preview

First, I’m excited to announce that a preview of the Windows Azure HDInsight Service -- our cloud-based distribution of Hadoop – is broadly available through the Windows Azure Portal. The Windows Azure HDInsight Service is built on the Hortonworks Data Platform to ensure long term compatibility with Apache Hadoop, while providing customers with the low cost and elasticity of cloud computing. Starting today, customers can perform Hadoop-based big data analysis in the cloud through the Windows Azure Portal, paying only for the storage and compute capacity that they use. The preview will enable customers to deploy permanent clusters with up to 40 nodes.

Customers like Microsoft’s own 343 Industries and UK-based Microsoft partner Ascribe have been early adopters of HDInsight Service and are enjoying the benefits of big data insights at cloud speed and scale. Their feedback, and the feedback of all those who participated in the private preview was invaluable – thank you! Based on their participation we are able to deliver a rock solid and high performance service. We are excited to offer this preview broadly to everyone and look forward to hearing your feedback.

Working with the Hadoop Community

Second, we’ve also been working with Hortonworks as part of our commitment to accelerating enterprise adoption of Hadoop by offering a solution that is open and fully compatible for the long run with Apache Hadoop. As part of that I am excited to share that last week Hortonworks reported that the Hadoop community voted to move Hadoop on Windows to the main Apache Hadoop trunk. The engineering team at Microsoft and Hortonworks in collaboration with the Hadoop community made a number of enhancements to the Windows Server and Windows Azure Hadoop development and runtime environments which were merged into the Apache trunk. All of these enhancements are also incorporated into our Windows Azure HDInsight Service and HDInsight Server products and in Hortonworks’ HDP for Windows product.

Moving Hadoop on Windows to the main Apache Hadoop trunk and our continued investment in the Hadoop project is important to us. It means that when our customers choose to use our HDInsight products, they can do so with the confidence of knowing they are 100 percent Apache Hadoop compatible now and in the future.

Stop by and See us at Gartner BI

Big data holds great promise – for companies and for the world. We’re excited about the progress we’re making with our products, and the community, to help customers realize that promise and we’ll have more to share over the next few months.

If you’re attending the Gartner BI Summit, visit our booth to meet our business intelligence experts and enter to win an Xbox 360 with Kinect. Also, don’t miss Herain Oberoi and Marc Reguera’s session on the new self-service BI capabilities in Excel 2013 on Tuesday at 3:15PM, and a chance to win a Surface with Windows RT. If you’re attending GigaOM Structure, be sure to stop by our booth hear more about our approach to big data. For those not attending these events, follow us on Facebook and Twitter @microsoftbi to get the latest on Microsoft at the Summit.

Eron Kelly
General Manager
SQL Server

Marcin Policht shows you how to deploy SSIS projects using PowerShell.

The challenge of running a business is becoming more complex each day. Though data is more plentiful and accessible in current times, how to analyze that data and create actionable insights remains a challenge. 

Navistar, one of the world’s largest truck manufacturers and parts suppliers, has addressed the complexities of big data head on. Selling more than one million product SKUs and serving more than 70 dealer locations across North America, Navistar needed the ability to analyze high volumes of data to assign pricing across channels and market segments in a timely way. 

What was the final solution? Navistar adopted the PROS pricing solution powered by Microsoft SQL Server to harness the data and identify the optimal price structure for their markets. You can read more about Navistar’s solution here.

You really want to have Smart Scans working on an Oracle Exadata machine. But how do you know they're working? David Fitzjarrell shares a few ways to prove you have Smart Scans working for you.

Although Tools Customizer currently supports only the local LPAR, you can use several methods to customize products on multiple LPARs where Tools Customizer is not installed.

Did you ever wonder how you can collect performance data about your DB2 system in a simple way without using extra tools? In this article, learn how to leverage the new DB2 for Linux, UNIX, and Windows monitoring framework available in V10.1 (initially introduced in version 9.7). The article also explains how to combine DB2 utilities to select, capture, and store data that you can use for analysis with plain SQL or using BI tools.

Having good data backing Enterprise Marketing Management (EMM) solutions reduces the number of duplicate, missing, and incorrect offers that you send to your customers and prospects. In this article, learn the design considerations and patterns for supplying master data to EMM solutions. The article also describes good practices when using IBM InfoSphere Master Data Management in your solution. After you have a single view of the master data for EMM, you can leverage this source-trusted data across your enterprise. Master data then becomes the foundation of your Smarter Analytics and Smarter Commerce solutions.