Friday, 29 July 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool

A proper database design is essential to achieving your goals in working with a database, whether they prioritize rapid information retrieval, efficient storage, or some combination of both.  Therefore, investing the time to apply the principles of good design should be a high priority.  Once completed, your database will better meet your needs and can easily accommodate future changes.

As in all aspects of database development and management, specialized software has emerged to make the process much easier to accomplish.  In today’s tutorial, we’ll learn how to design, implement, maintain, and synchronize MySQL database schemas using just such an application - the Navicat Data Modeler.

Thursday, 28 July 2016

Is Your Database Healthy?

Despite the sophistication of the latest DB2 software versions and the power of current IBM z/server technology, it is still possible for performance and data availability to deteriorate due to a variety of things, including increased dataset extents, loss of clustering, index page splits, and other factors.

This article presents simple SQL statements*  that the database administrator (DBA) can execute against the DB2 catalog to determine if one or more application databases suffer from common maladies, and what the DBA can do to fix or mitigate potential problems.

Wednesday, 27 July 2016

Is Your DB2 Subsystem Healthy?

Each new release of DB2 for z/OS contains "autonomic" features that allow the subsystem to diagnose potential issues in real-time. These include the gathering of data distribution statistics in real-time, the ability to reorg databases based on performance criteria, and other functions included in the new DB2 Autonomics Director.

However, it is still possible for performance and data availability to deteriorate due to a variety of things, including lack of proper referential integrity support, excessive package versions and potential security holes.

Monday, 25 July 2016

Getting started with Query Store feature in SQL Server 2016 – Part 3

Introduction


Query Store is a new feature in SQL Server 2016, which once enabled, automatically captures a history of queries, execution plans, and runtime statistics, retaining them for your troubleshooting performance problems caused by query plan changes. This new feature greatly simplifies performance troubleshooting by helping you quickly find performance differences, even after server restart or upgrade.

Friday, 22 July 2016

Oracle Database 12c – RMAN New Features: Part 3

Oracle Database 12c has new enhancements and additions in Recovery Manager (RMAN).  The Recovery Manager continues to enhance and extend the reliability, efficiency, and availability of Oracle Database Backup and Recovery. In this article series, I will be explaining the new features and how they help the Oracle community.

See new features around SYSBACKUP, pluggable databases, and the SQL interface covered in Part 1 – Oracle Database 12c – RMAN New Features: Part 1

See new features around backups, file recovery, snapshots, and duplication covered in Part 2 – Oracle Database 12c – RMAN New Features: Part 2

Thursday, 21 July 2016

Oracle Database 12c – RMAN New Features: Part 2

Oracle Database 12c has new enhancements and additions to Recovery Manager (RMAN).  The Recovery Manager continues to enhance and extend the reliability, efficiency, and availability of Oracle Database Backup and Recovery. In this article series, I will be explaining the new features and how it will help the Oracle community.

See new features covered in Part 1 – Oracle Database 12c – RMAN New Features: Part 1

In this article I will cover:
  • Multisection Backup Improvements
  • Restoring and Recovering Files Over Network
  • Storage Snapshot Optimization
  • Active Database Duplication Improvements

Wednesday, 20 July 2016

Oracle Database 12c – RMAN New Features: Part1

Oracle Database 12c has new enhancements and additions in Recovery Manager (RMAN).  The Recovery Manager continues to enhance and extend the reliability, efficiency, and availability of Oracle Database Backup and Recovery. In this article series, I will be explaining the new features and how it will help Oracle community.

In this article I will cover:
  • SQL Interface Improvements
  • SYSBACKUP Privilege
  • Support for multitenant container and pluggable databases
  • DUPLICATE enhancements

Monday, 18 July 2016

When is an index access path better than a full table scan?

The cost based optimizer makes decisions that can be hard to understand. One of the hardest may be why it chooses indexed or scan access paths: a burning question for many DBAs.

You will often hear a rule-of-thumb regarding what percentage of a table needs to be selected before a full table scan is more efficient thatn an index range scan. 2% is frequently suggested. However, you cannot place any reliance on such a figure. The cross over point will be dependent on many factors, including (but not limited to):

Thursday, 14 July 2016

Inheritance in the Database

In martial arts, fundamentals are everything. The basic kicks, rolls and jumps are far more important that any fancy technique you will learn during the years of practice. When you improve your basic rolls even a little bit, all the body throws that depend on your ability to roll on the floor will improve tenfold.

Software engineering is almost like training martial arts. We should practice every day, and focus on improving our fundamentals instead of running for the next big shinny thing that we read on Hacker News. Of course, this is easier said that done. It is always easier to learn the basics of a new programming language, than to get better at designing classes and polymorphism.

Tuesday, 12 July 2016

Getting Started with JSON Support in SQL Server 2016 – Part 2

Introduction


SQL Server 2005 introduced native support for storing, managing and processing XML data when XML had become de-facto standard for data interchange. This feature still exists in SQL Server as XML has some inherent advantages and is still widely used but because many new applications have started using JSON for a light-weight data interchange mechanism, SQL Server 2016 introduces built-in support for storing, managing and processing JSON data. In my earlier article, I talked about JSON support in SQL Server 2016, how to format or convert tabular data to JSON format using the FOR JSON clause. We also looked at different variants of FOR JSON, to use AUTO to automatically get the structure of the JSON data based on the order of source tables and columns in the query or by using PATH to have full control in specifying the output structure of the JSON data.

Monday, 11 July 2016

Getting Started with JSON Support in SQL Server 2016 – Part 1

Introduction


SQL Server 2005 introduced native support for storing, managing and processing XML data when XML had become the de-facto standard for data interchange. This feature still exists in SQL Server as XML has some inherent advantages and is still used widely, but because many new applications have started using JSON for a light-weight data interchange mechanism, SQL Server 2016 introduces built-in support for storing, managing and processing JSON data. In this article series, I will explain this new feature and demonstrate how to use it in your applications in different scenarios.

Thursday, 7 July 2016

Creating and Deleting System-Versioned Tables

In my last article Altering an Existing Table to Support Temporal Data. I showed you how to modify an existing table to take advantage of the new temporal data feature in SQL Server 2016. By having a temporal data table (history table) I was able to show the changes that have taken place after temporal data support for an existing table was enabled.  In this article I plan to expand the topic to show you how to create and deleted system-versioned tables and the associated history temporal table.

Business Situation


Now that SQL Server 2016 has finally been released management is keen on using this feature as part of a new Employee Management portal application we are building.  One of the first tables they want to create that will track historical data is a table named dbo.EmpSalary.  This table will contain the salaries of current employees, and the historical records associated with this table.  By using the historical temporal data support in SQL Server 2016, management will be able to track salary changes over time.  By creating this system-versioned table using the new temporal data table the new Employee Management portal application will be able to track employees’ salaries over time.