61 Specific Ways to Write Better SQL
“Given the authors’ reputations, I expected to be impressed. I was blown away! . . . Most SQL books sit on my shelf. This one will live on my desk.”
–Roger Carlson, Microsoft Access MVP (2006-2015)
“Rather than stumble around reinventing wheels or catching glimpses of the proper approaches, do yourself more » a favor: Buy this book.”
—Dave Stokes, MySQL Community Manager, Oracle Corporation
Effective SQL brings together practical solutions and insights so you can solve complex problems with SQL and design databases that simplify data management in the future. It’s the only modern book that brings together advanced best practices and realistic example code for all of these versions of SQL: IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL.
Drawing on their immense experience as world-class database consultants and instructors, the authors identify 61 proven approaches to writing better SQL. Wherever SQL versions vary, the authors illuminate the key nuances, so you can get the most out of whatever version you prefer. This full-color guide provides clear, practical explanations; expert tips; and plenty of usable code. Going far beyond mere syntax, it addresses issues ranging from optimizing database designs to managing hierarchies and metadata. If you already know SQL’s basics, this guide will help you become a world-class SQL problem-solver.
* Craft better logical data models, and fix flawed models
* Implement indexes that improve query performance
* Handle external data from sources you don’t control
* Extract and aggregate the information you need, as efficiently as possible
* Write more flexible subqueries
* Analyze and retrieve metadata using your database platform of choice
* Use Cartesian Products and Tally Tables to solve problems you can’t address with conventional JOINs
* Model hierarchical data: managing SQL’s tradeoffs and shortcomings « less
Master Oracle GoldenGate technology on multiple database platforms using this step-by-step implementation guide. Learn about advanced features to use in building a robust, high-availability replication system. Provided are detailed illustration of Oracle GoldenGate concepts, GoldenGate tools and add-ons, more » as well as illustrative examples. The book covers Oracle GoldenGate for Oracle database, and also discusses setup and configuration for other common databases such as IBM DB2, SYBASE ASE, MySQL, and Microsoft SQL Server.
The technology landscape is fast-changing, and Mastering Oracle GoldenGate stays current by covering the new features included in Oracle GoldenGate 12c. The book covers both classic capture and integrated capture, as well as delivery. Also covered are Oracle GoldenGate security and performance tuning, to keep your system secure and performing at its best. You will learn to monitor your GoldenGate system using tools that come with Oracle GoldenGate management pack, as well as using shell scripts. Troubleshooting is well-illustrated with examples:
* Covering Oracle GoldenGate technology across common database brands
* Discussing high-performing and secure replication environments
* Speaking to replication in Big Data and cloud computing environments
What You Will Learn
* Implement Oracle GoldenGate for real time replication
* Secure and tune your replication environment for high performance
* Administer your Oracle GoldenGate environment
* Learn troubleshooting approaches with help of examples
* Learn and make use of GoldenGate Management Pack and its API
* Feed live data into Big Data and cloud-based systems
Who This Book Is ForMastering Oracle GoldenGate is for database professionals who have chosen to ride the Oracle GoldenGate roller coaster for real-time replication solutions. The book is for beginners as well as professionals who are willing to master the leading replication technology in the industry. It is an excellent choice for professionals who are implementing or maintaining Oracle GoldenGate replication environments on any of the major database management system platforms. « less
Achieving Optimal Database Application Performance and Scalability
The Data Access Handbook
Achieving Optimal Database Application Performance and Scalability
John Goodson • Robert A. Steward
Drive breakthrough database application performance by optimizing middleware and connectivity
Performance and scalability are more critical than ever in today’s enterprise more » database applications, and traditional database tuning isn’t nearly enough to solve the performance problems you are likely to see in those applications. Nowadays, 75-95% of the time it takes to process a data request is typically spent in the database middleware. Today’s worst performance and scalability problems are generally caused by issues with networking, database drivers, the broader software/hardware environment, and inefficient coding of data requests. In The Data Access Handbook, two of the world’s leading experts on database access systematically address these issues, showing how to achieve remarkable improvements in performance of real-world database applications.
Drawing on their unsurpassed experience with every leading database system and database connectivity API, John Goodson and Rob Steward reveal the powerful ways middleware affects application performance and guide developers with designing and writing API code that will deliver superior performance in each leading environment. In addition to covering essential concepts and techniques that apply across database systems and APIs, they present many API examples for ODBC, JDBC, and ADO.NET as well as database system examples for DB2, Microsoft SQL Server, MySQL, Oracle, and Sybase.
* Clearly understanding how each component of database middleware can impact performance and scalability
* Writing database applications to reduce network traffic, limit disk I/O, optimize application-to-driver interaction, and simplify queries—including examples for ODBC, JDBC, and ADO.NET
* Managing connections, transactions, and SQL statement execution more efficiently
* Making the most of connection and statement pooling
* Writing good benchmarks to predict your application’s performance
* Systematically resolving performance problems—including eight start-to-finish case-study examples
If you’re a software architect, system designer, or database application developer, The Data Access Handbook will be your most indispensable database application performance resource. It’s the one book that focuses on the areas where you can achieve the greatest improvements—whether you’re designing new database applications or troubleshooting existing ones.
John Goodson is vice president and general manager of the DataDirect division of Progress Software, a leader in
data connectivity and mainframe integration. For 20 years, he has worked with Sun, Microsoft, and others to develop database connectivity standards such as J2EE, JDBC, ODBC, and ADO. He served on the ANSI H2 committee that built the SQL standard and now participates in the JDBC Expert Group and Java Rowsets standards committees.
Rob Steward, vice president of R&D at the DataDirect
division of Progress Software,
is responsible for the development, strategy, and oversight of the company’s data connectivity products. Rob has spent the past 15 years developing high-performing database driver and data providers, including ODBC, JDBC, and ADO.NET.
Both authors have spoken on database application performance at many industry events.
Visit www.dataaccesshandbook.com to get the code examples presented in this book and other supplemental information for DB2, MicrosoftSQL Server, MySQL, Oracle, and Sybase. « less
Learning Visually with Examples
**** The Easy, Visual Way to Master IBM® DB2 for Linux®, UNIX®, and Windows®—Fully Updated for Version 9.5 ****
IBM DB2 9 and DB2 9.5 provide breakthrough capabilities for providing Information on Demand, implementing Web services and Service Oriented Architecture, and streamlining information management. more » Understanding DB2: Learning Visually with Examples, Second Edition, is the easiest way to master the latest versions of DB2 and apply their full power to your business challenges.
Written by four IBM DB2 experts, this book introduces key concepts with dozens of examples drawn from the authors' experience working with DB2 in enterprise environments. Thoroughly updated for DB2 9.5, it covers new innovations ranging from manageability to performance and XML support to API integration. Each concept is presented with easy-to-understand screenshots, diagrams, charts, and tables.
This book is for everyone who works with DB2: database administrators, system administrators, developers, and consultants. With hundreds of well-designed review questions and answers, it will also help professionals prepare for the IBM DB2 Certification Exams 730, 731, or 736.
* Choosing the right version of DB2 for your needs
* Installing and configuring DB2
* Understanding the DB2 environment, instances, and databases
* Establishing client and server connectivity
* Working with database objects
* Utilizing breakthrough pureXML™ technology, which provides for nativeXML support
* Mastering administration, maintenance, performance optimization, troubleshooting, and recovery
* Understanding improvements in the DB2 process, memory, and storage models
* Implementing effective database security
* Leveraging the power of SQL and XQuery « less
DBA Guide, Reference, and Exam Prep
DB2® 9 builds on the world's number one enterprise database to simplify the delivery of information as a service, accelerate development, and dramatically improve operational efficiency, security, and resiliency.
Now, this new edition offers complete, start-to-finish coverage of DB2 9 administration more » and development for Linux®, UNIX®, and Windows® platforms, as well as authoritative preparation for the latest IBM DB2 certification exam.
Written for both DBAs and developers, this definitive reference and self-study guide covers all aspects of deploying and managing DB2 9, including DB2 database design and development; day-to-day administration and backup; deployment of networked, Internet-centered, and SOA-based applications; migration; and much more. You'll also find an unparalleled collection of expert tips for optimizing performance, availability, and value.
* Important security and resiliency enhancements, including advanced access control; fine-grained, label-based security; and the new security administrator role
* Breakthrough pureXML™ features that make it easier to succeed with service-oriented architecture
* Operational improvements that enhance DBA efficiency--including self-tuning memory allocation, automated storage management, and storage optimization
* Table-partitioning features that improve scalability and manageability
* Powerful improvements for more agile and rapid development, including the new Eclipse-based Developer Workbench and simple SQL or XQuery access to all data
Whatever your role in working with DB2 or preparing for certification, DB2 9 for Linux, UNIX, and Windows, Sixth Edition is the one book you can't afford to be without.
*** Download Complete DB2 V9 Trial Version ***
Visit ibm.com/db2/9/download.html to download a complete trial version of DB2, which enables you to try out dozens of the most powerful features of DB2 for yourself: everything from pureXML support to automated administration and optimization.
* Straight from IBM, the ultimate guide to running DB2 9 and preparing for the latest IBM DB2 certification exam!
* Covers powerful DB2 9 enhancements ranging from automated management to improved compression
* Covers the full spectrum of DBA responsibilities, including server management, data placement, XML concepts, activity analysis, high availability, and security
* Presents expert tips and best practices from the DB2 customer support organization « less