The Quick and Dirty Guide To IQ Training
[Note: This post dates to May of 2013; like all things, our curriculum is constantly changing and updating. Scroll to the bottom to see information on changes that have occurred since the original publication.]
So you’re starting to play with SAP Sybase IQ (or just “IQ” for short). Chances are, your immediate reaction is a mixture of two opposite emotional responses:
- This is really neat and powerful
- I have no idea how to make this work
The good news is, everybody falls somewhere into that particular cognitive dissonance field at the start; in part that’s by design, because IQ treats a lot of the nuts-and-bolts tasks of a relational database management system in very different ways than you’ve been accustomed to. Get used to a few key elements, learn how to handle a few unique situations, and you’ll be well on your way to making IQ sing.
But first you’re going to have to get used to the idea that the ways you know things are supposed to work are pretty much all wrong. In addition to the usual things that vary from product to product – connectivity, SQL dialect, particulars of the security model – other more fundamental items work contrary to your expectations. Little things like “index design” and “table loading” and “transaction management”.
Not to worry. We’re here to help.
First things first: what are you going to do with IQ? Are you going to be on the administrative end of things, or the coding end of things, or maybe you get to do it all? Administrators tend towards product installation, resource allocation, configuration, database creation, multiplex operations, troubleshooting, and maintenance. Coders are probably more concerned with modeling, the local SQL dialect (including analytic and OLAP extensions), and query optimization. Everybody needs to know about basic architecture (particularly the vertical storage/bitmap structures), index design, data loading, transaction best practices, and the importance of cardinality.
First up: Sybase IQ Administration (EDB785) is fast-paced five days that will take you from an empty host machine to a working IQ system that is up, running, and stable. It won’t be the single most theoretically perfect installation of IQ that could possibly exist in the best of all possible universes, but it will be usable in a practical sense. You’ve set aside the disk, memory and CPU resources that IQ wants, set up the communications links necessary for your users, configured a permissions model, created your tables and – crucially – your indexes, set the foundation for a multiplex, and determined what ongoing monitoring, troubleshooting, and maintenance operations you require. Oh, and you’ve loaded a few squillion rows into your tables, so now your users can start querying and teasing answers out of all of those data.
The day will come when you need something that’s fancier than just up, running, and stable; you need to squeeze every last bit of power and flexibility out of IQ, and to implement the fancier capabilities. This is where you need to carve out four days from your busy schedule to attend Sybase IQ Advanced Administration (EDB795); you’ll learn the finer details of space management, fragmentation control, security, multiplex operations, diagnostics, and repairs. You’ll also learn your way around unstructured data, disk fragmentation and defragmentation, advanced load capabilities, and interactions with remote data. With these skills under your belt and a good deal of practice, you’ll be able to make IQ respond to the unique demands of your environment; if you need to document your ability, you’re also ready for the Sybase IQ Administrator (Professional) certification.
For those of you that are interested in the query end of things, it’s probably best to start with Sybase IQ for Developers (EDB775); you may notice a few administrators in class with you, broadening out their base of knowledge, but don’t worry – this isn’t a class that requires superuser privileges. Like your administrator colleagues, you’ll learn how IQ is structured, how the local SQL dialect is put together, how tables and indexes should be built, and how to get all that data you need to plow through into a place where you can actually use it. But then we’ll go a bit deeper into things that the admins generally are too busy to deal with on a daily basis: functions and procedures, data analysis, and query plans – so many query plans. The IQ query optimizer is powerful and cleverly put together, and we’ll show you how it’s making decisions, so you can be certain to provide all the inputs it needs to work to its best capabilities. For those of you that have experience in modeling and PowerDesigner, we’ll show you how that tool can be used to its best advantage in a data warehouse. It’s a varied four days, so come ready to explore a lot of different topics.
At this point, you have several choices for followup education; if the query optimizer part of EDB775 caught your fancy, we can dig deeper into that topic in Sybase IQ: Understanding Optimization and Improving Performance (EDB792). For three days, you’ll examine the process of query execution, the query plans that tell you what’s happening, the decisions made by the optimizer, and the algorithms that are chosen. Along the way, we’ll learn how to get IQ to tell us what can be improved, and determine what we can do to give the optimizer a shot at a better plan.
Lastly, those using Hadoop (an open-source framework designed to allow scalable, distributed analysis of big data) will be pleased to know that there are ways to make it work with IQ, which we explore in the one day Sybase IQ Big Data Analytics & Hadoop Workshop (WNAIQH). We’ll show you different ways to integrate IQ and Hadoop, develop the Java code that ties the two together and wrap it up inside stored procedures and functions, read the Hadoop filesystem into IQ, and tie Hadoop jobs to IQ queries. This workshop requires a lot of expertise beyond just IQ, so it’s not located at a particular point on the curriculum path, except to say that you’ll need to know your way around Hadoop, and IQ, and Java when you sit down.
Now that you know what we offer, a quick question: what did we leave off the list? What are the aspects of IQ and data warehouse implementation that you need in your environment? Let us know what serves your needs and we’ll work with you to find a solution (consulting, customized training, maybe even new courses) that help you the most.
Update: July 2015
The courses that are described above have undergone changes; let’s talk about what’s accurate today.
The descriptions of EDB785 and EDB795 are largely unchanged, but they’re been updated to reflect product version changes. EDB792 and EDB775 have been combined into a single five-day course, and also updated to reflect product version changes. A new certification is in the final stage of being released (I’d expect things to be finished by mid-August), again reflecting product changes.
Rather than post links which will inevitably become dated again in the future, let’s point you to the best place for getting current information: the SAP Training and Certification Shop, where a search on the term IQ will point you to the current releases, today and in the future. If you have any specific questions, don’t hesitate to reach out to your SAP education rep, or to me. Happy searching, and welcome to the world of IQ.