Friday, 29 September 2017

Tips for Simplifying Crosstab Query Statements

The CASE Statement

The CASE statement is a type of conditional construct much like an IF ELSE. It evaluates a search condition and executes the corresponding SQL statement list, depending on which condition evaluates to true. If no search condition matches, the statement list in the ELSE clause is executed instead. Each statement list may consist of one or more statements. Multiple statements are enclosed between the BEGIN and END delimiters.

Thursday, 28 September 2017

Oracle's Newest PL/SQL Feature 'ACCESSIBLE BY'

Oracle has offered packages for a while now, but until recently it's been difficult, if not impossible, to prevent 'out of context' execution of functions and procedures; in many cases package code is designed to be used within the context of the package, not as stand-alone procedures and functions. In release, Oracle has addressed this issue with the ACCESSIBLE BY clause. This clause allows developers to refine the scope of a procedure or function call and generate an error message when that procedure or function is called outside of the package context. Let's build an example to illustrate how this is written and what results are obtained when the context-restricted code is executed in context and out.

Tuesday, 26 September 2017

Overview of Azure Data Lake


Data with 4 Vs – variety, volume, velocity and veracity is known as Big Data. As we know, there are lots of tools and techniques available to handle big data. Big data is shifting the data paradigm and increasing the expectations with data analytics. We should consider how to manage and store this massive data from its source and apply data analytic techniques to meet expectations. On the cloud, there are various ways to store the data. Microsoft Azure supports various types of data storage like Blob storage, File storage, Queue storage, Table storage and Azure Data Lake. Although, the use cases are relatively different to use any of these storages. However, Azure Data Lake is widely accepted to store massive size i.e. petabytes and zettabytes of data.

Monday, 25 September 2017

Cascade Functionality for TRUNCATE PARTITION and EXCHANGE PARTITION in Oracle Database 12c

The TRUNCATE [SUB]PARTITION and EXCHANGE [SUB]PARTITION commands can now include a CASCADE clause, allowing the actions to cascade down the hierarchy of reference partitioned tables. For this to work, the referenced foreign keys must include the ON DELETE CASCADE clause.

Thursday, 21 September 2017

Where is my SQL Server Configuration Manager?

If you are running Windows 8 and above, you might have found out that you can’t find SQL Server Configuration Manager in the list of installed applications.   This is because SQL Server Configuration Manager is not a stand-alone program, and therefore doesn’t appear as an application in newer versions of Windows.   The SQL Server Configuration Manager is a Microsoft Management Console snap-in.

Wednesday, 20 September 2017

Machine Learning in Oracle Database – Classification of Conference Abstracts based on Text Analysis

Machine Learning is hot. The ability to have an automated system predict, classify, recommend and even decide based on models derived from past experience is quite attractive. And with the number of obvious applications of machine learning – Netflix and Amazon recommendations, intelligent chat bots, license plate recognition in parking garages, spam filters in email servers – the interest further grows. Who does not want to apply machine learning?

DB2 V12 Features Supporting Large Databases

Big data applications were once limited to hybrid hardware/software platforms. Now, recent advances are allowing applications like these to be integrated with and federated into operational systems. In particular, IBM's DB2 for z/OS Version 12 (DB2 V12) delivers new features and functions that allow the DBAs to design, define and implement very large databases and business intelligence query platforms that fulfill some big data expectations. Such applications can then provide value to the enterprise without expending money and time on specialized infrastructure, and can also be the first step towards building out one or more true big data applications.

Tuesday, 19 September 2017

Database as a Service: Defining Quality

Enterprises are delegating various aspects of application development and support to outside firms in order to shorten implementation schedules. The sooner you implement, the thought goes, the sooner you are in front of customers and making profits. Another advantage is getting your application running before your competition implements their own. This delegation comes at a cost. Application and database definitions are now spread among multiple firms across multiple platforms. Business rules are implemented in (potentially) many places, and many support staff skills are not under your control. This raises the complexity of the application and increases the risks of failure during application changes, maintenance or upgrades.

Monday, 18 September 2017

Introduction to the Azure SQL Database Advisor

One of the challenges associated with running your workloads in Azure SQL Database is the limited level of management oversight (when compared with databases hosted on full-fledged SQL Server instances) over its resources. While you can easily scale it vertically by changing the associated service tier and performance level, you do not have, for example, the option of running SQL Server Profiler or Index Tuning Wizard, commonly used to evaluate, troubleshoot, and optimize database performance. Fortunately, there is an alternative approach that leverages the functionality incorporated into the recently introduced Azure SQL Database Advisor component of Azure SQL Database. In this article, we will present its basic characteristics.

Thursday, 14 September 2017

Database as a Service: Choosing a Provider

Rolling out a new customer-facing application can be quite a challenge, considering the time it takes to develop and test the code. In addition, much time is taken on data modeling and database design, usually with multiple levels of operating systems, network and database professionals. Providers of database as a service (DBaaS) promise to handle all these database tasks, including hardware acquisition, database management software installation and configuration, database definition, performance tuning, and backup and recovery.

Tuesday, 12 September 2017

DBMS_PIPE - For Inter-Session Communication

The DBMS_PIPE package provides a non-secure mechanism for inter-session messaging. It is considered non-secure because messages can be lost from the pipe if the instance crashes or is shutdown before they are processed. Advanced Queues are arguably a better mechanism when secure messaging and greater flexibility are required.

Monday, 11 September 2017

Introduction to SAP HANA on Azure

Microsoft and SAP share a long history of working together on a variety of interoperability solutions, including, for example, SAP Fiori and Office 365. They have been technology partners for over 20 years, with development teams co-located in their respective corporate headquarters in Redmond and Waldorf, Germany. In recent years, this trend has been exemplified by advancements in support for SAP-based solutions on Microsoft Azure. The purpose of our article is to provide their overview, focusing in particular on support for SAP HANA on Azure.

Sunday, 10 September 2017

Managing Azure SQL Database Recovery

Azure SQL Database offers a number of benefits, built into the underlying cloud infrastructure, that leverage resiliency and redundancy. You can take advantage of this functionality in order to facilitate failover and failback in response to events that affect availability of an Azure region. In this article, we will step through a procedure illustrating this capability.

Saturday, 9 September 2017

Network Configuration of SQL Server Always On Availability Groups in Azure

As we pointed out in our article recently published on this forum, implementing SQL Server Failover Clustering in Azure virtual machines differs in several aspects from its on-premises implementations. These differences reflect some of the unique characteristics of the storage and network infrastructure services in the Microsoft cloud environment. In this article, we will focus on the networking aspects of clustered deployments of SQL Server 2016 in Azure. For more information regarding storage-related considerations, refer to the aforementioned article.

Wednesday, 6 September 2017

Understanding Azure Data Factory – A Cloud Based Integration Service

Azure Data Factory is a cloud based integration service that allows you to transfer data from disparate cloud and on premise data sources to various data storage destinations. This is similar to another on premise ETL tool, SQL Server Integration Service (SSIS), provided by Microsoft.