Wednesday, 31 August 2016

Real-time Operational Analytics in SQL Server 2016 - Part 1


Organizations have been focusing more and investing heavily in Analytics, to gain the knowledge required to make improvements or changes to enhance business. Analytics help organizations to remain competitive, optimize cost and resources by helping them study their past data (e.g. internal operational data and external data like social media, data from research or government organizations) to identify potential trends, help them in analyzing the effects of certain decisions or events (e.g. Ad campaign, offers on their products and services), and analyzing past/current data to predict outcomes (e.g. credit scoring).

Tuesday, 30 August 2016

Online Move Datafile in Oracle Database 12c Release 1

Prior to Oracle 12c, moving datafiles has always been an offline task. There were certain techniques you could employ to minimize that downtime, but you couldn't remove it completely. Oracle 12c includes an enhancement to the ALTER DATABASE command to allow datafiles to be moved online.
  • Basic Syntax
  • Examples
  • Pluggable Database (PDB)
  • Tempfiles

Monday, 29 August 2016

Invisible Columns in Oracle Database 12c Release 1

Invisible columns can be useful during application migrations. Making new columns invisible means they won't be seen by any existing applications, but can still be referenced by any new applications, making the online migration of applications much simpler.
  • Invisible Columns
  • Invisible Columns and Column Ordering
  • Mandatory Invisible Columns

Tuesday, 23 August 2016

Extended Data Types in Oracle Database 12c Release 1

Prior to Oracle 12c, regardless of the character semantics used, the maximum size of a VARCHAR2, NVARCHAR2 and RAW columns in a database are as follows.
  • VARCHAR2 : 4000 bytes
  • NVARCHAR2 : 4000 bytes
  • RAW : 2000 bytes
With the introduction of Extended Data Types, Oracle 12c optionally increases these maximum sizes.

Monday, 22 August 2016

Database Smart Flash Cache in Oracle Database 12c Release 1

The database Smart Flash Cache was originally released as an Exadata feature. From it was available for use with any flash disks, not just Exadata storage. In Oracle Database 12c the Smart Flash Cache can now use multiple flash disks without needing a volume manager. I'm not sure why this makes it worthy of inclusion in the 12c OCP DBA exam, since it seems like a very minor change.

Sunday, 21 August 2016

Administrative Privileges and Job Role Separation in Oracle Database 12c Release 1

Oracle 12c includes additional administrative privileges to allow a greater level of job role separation if that is necessary in your organisation.
  1. Groups
  2. Creating OS Groups and Users
  3. Using Administrative Privileges
  4. Identify Users with Administrative Privileges (V$PWFILE_USERS)

Saturday, 20 August 2016

Active Session History (ASH) Analytics in Oracle Enterprise Manager Cloud Control 12c

The Active Session History (ASH) was introduced in Oracle 10g to provide real-time diagnostics information. ASH Analytics is a feature of Enterprise Manager Cloud Control 12c, which visualises ASH information, making it even simpler to diagnose performance problems.

ASH Analytics is listed as requirement for the 1Z0-060 "Upgrade to Oracle Database 12c OCP upgrade exam, hence this short overview for those who don't use Cloud Control.

Friday, 19 August 2016

Installing node-oracledb on Microsoft Windows

I. Introduction

Node.js has revolutionized the web development industry by facilitating the use of the JavaScript programming language for server-side development. This also brings with it JavaScript’s non-blocking architecture, which permits commands to be executed in parallel in a single thread, allowing for the construction of highly concurrent applications. Before Node.js, developers were forced to use other languages such as PHP or ASP on the server-side, in addition to using JavaScript on the client-side. Having the ability for a more unified API opens the doors for increased efficiency and the potential of code sharing between the client and server.

Thursday, 18 August 2016

DB2 Data Warehouse Capacity Planning

Early data warehouse implementations began as collections of financial and customer data that were accumulated over time. This allowed for historical reporting and some trend analysis.  Modern warehouses have evolved into complex and elegant enterprise analytics platforms, hosting a broad collection of multiple data types, queried by advanced business intelligence software.

Every day more data arrives, the total amount of data grows larger, and analysts across the organization are presenting the warehouse with higher volumes of complex queries. As the warehouse environment becomes more valuable, capacity planning becomes critical.

Wednesday, 17 August 2016

Oracle's ANSI Join Syntax Can Result In Wrong Results

Ever since Oracle provided their version of ANSI syntax it's been improving with each release. Unfortunately some steps forward also take steps backwards. A case in point is the following example, brought to my attention by Jonathan Lewis. Let's look at the queries and see where Oracle has gone afoul of things.

Tuesday, 16 August 2016

Big Data Architecture

The next few years will be critical for the information technology staff, as they attempt to integrate and manage multiple, diverse hardware and software platforms. This article addresses how to meet this need, as users demand greater ability to analyze ever-growing mountains of data, and IT attempts to keep costs down.

Saturday, 13 August 2016

Index Sanity in Oracle

Unused indexes are a major topic for Oracle DBAs, since each index a table has increases the maintenance "cost" of inserts, updates and deletes. Many a blog post has been written providing ways to find such 'unused' indexes, many relying on a relatively newer Oracle feature, invisible indexes. Two other articles discuss this, one an effort to help the DBA discover unused indexes and the other discusses an issue where those invisible indexes aren't totally invisible. Neither one shows what could happen when an index, thought to be unused and dropped, really wasn't. [Invisible indexes were not used in this example, the indexes were simiply dropped based upon the execution plan generated.]

Saturday, 6 August 2016

Temporal Data Part 3 – Reporting Out Current and Historical Information

As you start creating temporal tables to track history information you might find it necessary to join the history records with the current records into a single set of records.  With the introduction of temporal table support in SQL Server 2016 Microsoft also added some additional functionality that makes it easy for you to join the current and history records of a system-versioned table. In this article I will show you some of the different ways to do analysis of your system-versioned records over time.

Friday, 5 August 2016

Introduction to SQL Server Stretch Database

In many cases Azure SQL Database offers an economically and functionally viable alternative to SQL Server deployments. However, there are also scenarios where we might discover that rather than serving as a replacement, it provides synergy, working side by side with your on-premises databases. One of technologies that illustrate this paradigm is Stretch Database, introduced in SQL Server 2016. We will describe its basic characteristics and review its implementation steps in this article.

Thursday, 4 August 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 5

Tips & Tricks

Auto Layout

The Auto Layout button - found on the main toolbar - automatically arranges objects on the canvas.  This is often faster and easier than trying to arrange every diagram component yourself.

Here is a Conceptual Diagram of a Travel Bookings before and after executing the Auto Layout command:

Wednesday, 3 August 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 4

Creating Database Structures from a Model

Once the Physical Model has been finalized, it acts as a template for the real database structure.  Building the database schema from a model typically involves consulting the Physical Model for each step of database construction, much like blueprints in the construction of buildings. Some of the downsides to this approach is that it is time consuming and prone to errors.

Tuesday, 2 August 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 3

Creating a Physical Model

The Model Conversion command can also be employed to create a Physical Model from the Logical one.
  • On the Convert to New Model dialog, select MySQL 5.6 as the target database.

Monday, 1 August 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 2

Converting a Model into another Type

All the work that you put into the Conceptual model need not go to waste.  We can utilize it as the basis for the Logical model.
  • Select File > Model Conversion... from the main menu to open the Convert to New Model dialog.
  • Choose Logical as the Model Type and click OK: