Database Management & Programming News, Articles & Tutorials for Database Administrators

Big Data Analytics on Current Data 07/16/2018

IBM now provides an option to configure its Db2 version 12 for z/OS and complementary IBM Db2 Analytics Accelerator (IDAA) to permit concurrent transactional processing of operational data with analytics processing of data in the appliance. This new feature, zero-latency HTAP (hybrid transactional analytical processing) provides a patented replication process that propagates native Db2 table changes to the IDAA data store. This then allows BI queries to act on up-to-date data.

Oracle's ASCIISTR() and Unicode Characters 07/12/2018

Although documented, the ASCIISTR() function may return 'interesting' results depending on the characters translated.  Read on to see what problems this can cause and a possible fix.

Displaying Leading and Trailing Whitespace on Varchar Columns in MySQL 07/09/2018

It's fairly trivial to enclose varchar data within quotes using the CONCAT() function as long as you know which fields you want to enclose in quotes. Coming up with a more generic solution takes a little more doing. In this tutorial, Rob Gravelle describes two approaches, one employing a user function, the other, using a stored procedure.

Introduction to Azure Cosmos DB Global Distribution 07/05/2018

Global distribution of updatable data stores is one of the more challenging goals that software designers and architects have been struggling with over the years. Addressing this challenge has become increasingly pressing as the rapid growth of cloud technologies increased the need for deployments capable of spanning multiple geographical areas. In this article, we will provide an introduction to global distribution-related functionality in Cosmos DB.

Placing a Mark in SQL Server's Transaction Log for Fall Back 07/02/2018

How many times have you had a programmer come to you and say they want you (the DBA) to restore their database to sometime prior to when they accidentally corrupted it? If you are doing FULL transaction logging you can do a point in time recovery to restore the database to just prior to when the corruption occurred. But in order to do that you need to know exactly when the programmer corrupted the data, which in a lot of cases is not known down to the second.

Using SQL Server's Default Trace to Identify Autogrow Events in tempdb 07/02/2018

We all know that you should try to size tempdb appropriately, so it doesn’t need to autogrow shortly after starting up SQL Server. It isn’t always easy to do this. Therefore, when you first implement a new server and/or add new databases you should monitor the autogrowth events on tempdb. By monitoring the autogrowth events you can easily determine if you have sized tempdb appropriately.

Automatic Query Performance Tuning in SQL Server Database 06/28/2018

SQL Server's Automatic Tuning provides insight into possible query performance problems, recommends solutions, and provides options to fix identified problems either manually or automatically.

Using Oracle VM to Create Dev and Test Environments 06/25/2018

Using Oracle VM can be a fast and reliable way to create dev and test environments.  Read on to see how this can be done and how it can help migrate databases from other operating systems.

GDPR for the DBA 06/18/2018

The General Data Protection Regulation (GDPR) went into effect worldwide on May 25, 2018. In response, companies throughout the world increased their data security awareness, appointed data protection officers and updated their privacy policies. IT support staff responded with updated data dictionaries, flagging of personal data, encryption at various points (local and cloud storage, network traffic, etc.) and heightened security procedures. However, more work is needed. In this article we focus on what the DBA must do in the near term in order to anticipate and prevent performance and capacity issues.

Creating an Oracle Patching Strategy 06/14/2018

Patching an Oracle installation can be interesting, especially if the patch needs to be backed out.  Read on to see how to create a process that preserves the original home.

Placing a Mark in SQL Server's Transaction Log for Fall Back 07/02/2018

How many times have you had a programmer come to you and say they want you (the DBA) to restore their database to sometime prior to when they accidentally corrupted it? If you are doing FULL transaction logging you can do a point in time recovery to restore the database to just prior to when the corruption occurred. But in order to do that you need to know exactly when the programmer corrupted the data, which in a lot of cases is not known down to the second.

Using SQL Server's Default Trace to Identify Autogrow Events in tempdb 07/02/2018

We all know that you should try to size tempdb appropriately, so it doesn’t need to autogrow shortly after starting up SQL Server. It isn’t always easy to do this. Therefore, when you first implement a new server and/or add new databases you should monitor the autogrowth events on tempdb. By monitoring the autogrowth events you can easily determine if you have sized tempdb appropriately.

Databases with MAXSIZE Set 06/04/2018

When a database grows to the MAXSIZE the database will not grow anymore and if someone tries to add more rows they will get an error saying the database is full. Greg Larsen shows you how to find all database files that have their max_size set.

Indexes That Have Not Been Used but Are Being Updated 06/04/2018

We all know indexes are important for improving your query performance, but to store and maintain indexes SQL Server requires disk space and compute resources. If you have indexes that are not being used, they are costing you valuable resources and causing your update statements to run longer. Those updates run longer because they have to maintain those unused indexes. If your application is not using an index, then there is no value in having that index. Read on to learn more.

Tables Without Clustered Indexes 05/03/2018

Do all of your tables contain clustered indexes? Some say every table should have a clustered index. Greg Larsen shares a simple script to identify those tables in your database that don’t have a clustered index.

Why Are You Dropping and Recreating Your Indexes? 05/03/2018

Why are you dropping and recreating your indexes, when you could just disable and then rebuild them? By disabling an index, you have basically turned off the index, but have allowed SQL Server to retain the index definition in the database metadata. If you want the index back all you need to do is rebuild the index.

How to Programmatically Identify When Your SQL Server Was Last Started 04/02/2018

Greg Larsen shows you a quick trick to programmatically script a way to identify when SQL Server was last started.

How to Show the Execution Plan of a Running Query 04/02/2018

By looking at a query execution plan, you can determine which steps are used to resolve the query. But what if you just want to find out what the query plans are for only the queries that are currently running?

How to Send a TDE Encrypted Backup to Someone Outside Your Organization 03/01/2018

Once you enable your database to be encrypted with Transparent Data Encryption (TDE), the physical database files, and the database backups are encrypted.  If your database and database backup are encrypted, then how can you send the encrypted backup to a person outside your organization?

What is the State of My Transparent Data Encrypted Database? 03/01/2018

When using Transparent Data Encryption, you might wonder “What is the state of my transparent data encrypted database?” Read on to learn the many different states that a transparent data encrypted database might go through.