Technology – When To Cache A Denodo View

Advertisements

Here’s a quick summary of practices about when to use cache when developing denotative views.  These guidelines come from the usual documentation and practical experience and may help you decide whether to cache a view. These are general guidelines, and they should happen the conflict with any guidance you’ve gotten from the Denodo; Please use the advice provided by Denodo.

What is a table cache?

In denodo, a cache is a database table that contains a result set of a view at the point in time, which is stored in a JDBC database

Why Cache?

Cache in Denodo can be used for several purposes:

Enhancing Performance

Improving performance is the primary purpose of caching and can be overcome slow data sources, data sources with limited SQL functionality, and/or tuned long runner views. 

Protecting data sources from costly queries

Caching can shield essential systems from excess load cause by query load from large, long-running queries and/or frequent queries during critical operation times.

Reusing complex data combinations and transformations

Caching views that consolidate data from multiple data sources, perform complex calculations, and apply complex derivations and business rules provide and optimize pre-enriched data set for consumption.

Cache View Modeling Best Practice

Add a primary key or a unique index

Adding a primary key or a unique index helps the optimizer define performance strategies and accurate cost estimates when the view joins to other views.

Add Cache indexes

Add Cache indexes based on understanding actual consumer usage of view (e.g., commonly used prompts, etc.)

Caching Tips and Cautions

Here are some considerations to keep in mind when making caching decisions.

Avoid Caching Intermediate Views

Where possible, avoiding caching of intermediate views allows the optimizer to make better decisions about data movement, pushdown, and branching.  This allows denodo to perform great SQL simplification. 

The volume of view to be cached

Where possible, avoid caching large views (e.g., views with a large number of rows/columns). Evaluate the cache size and make an appropriate decision.

Denodo Reference Links

Best Practices to Maximize Performance III: Caching

Denodo E-books

Denodo Cookbook: Query Optimization

Related Blog Posts

Denodo View Performance Best Practice

Technology – Denodo Supported Business Intelligence (BI) and Reporting Tools

Advertisements

The question of which PI tools to Denodo supports comes up perhaps more often than it should. The question usually comes in the form of a specific intelligence (BI) and reporting tool being asked about. For example, does Denodo support tableau or Cognos, etc.

Denodo does provide a list of intelligence (BI) and reporting tools that they support. However, the list of the most commonly used intelligence (BI) and reporting tools. And there is a reason for that, which, basically, boils down to whether or not the intelligence (BI) and reporting tools can use ODBC or has a JDBC driver.  So, even if it’s not on Denodo’s list doesn’t mean you can’t use the tool. , it may just mean that the software may not be one of the most frequently used.

Simple List Of Business Intelligence (BI) And Reporting Tools Supported By Denodo.

Here is a simple list of the tools which Donato has provided on their knowledgebase page. So, I strongly recommend you visit the page and for additional details and software specific documentation links.

  • Alteryx
  • IBM Cognos
  • Informatica Power Center
  • Looker
  • Microsoft SQL Server Reporting Services (SSRS)
  • Microstrategy
  • OBIEE
  • Pentaho
  • Power BI Desktop
  • Qlik
  • SAP Business Objects
  • SAP Lumira
  • Splunk
  • Tableau
  • Tibco Spotfire

Finding the Denodo page that lists these commonly use business intelligence (BI) and reporting tools sometimes causes issues.  Because they discuss it in terms of northbound, which is typical for them, but not the way other people think about it.

I have provided a link to the Denodo list of supported ODBC and JDBC business intelligence (BI) and reporting tools.  Hopefully, this post will make it a little easier for you to find the Denodo list of supported ODBC and JDBC business intelligence (BI) and reporting tools.

Denodo Reference Links

Denodo > Knowledge Base > Northbound Connections > Denodo and BI Tools

Technology – Denodo VQL To Get A List Of Cached View Names

Advertisements

Hello, this is a quick code snippet of a Denodo VQL (Denodo Virtual Query Language) to pull a list of cached view names which can be useful in pulling list of cached views. It’s not a complicated thing, but now that I’ve bothered to look it up on putting this note here mostly for me but you may find useful. I have found this useful for several reasons not the least of which is for creating jobs to do maintenance of cached view statistics.

Example VQL List Of Cached View Names

select name view_name

from get_views()

       where cache_status <> 0

       and database_name = ‘uncertified’

       and name not like ‘%backup’

       and name not like ‘%copy’

       and name not like ‘%test’

       and name <> ‘dv_indexes’;

Denodo Reference Links

·         Denodo > Denodo Platform 8.0 > User Manuals > Virtual DataPort VQL Guide > Stored Procedures > Predefined Stored Procedures > GET_VIEWS

Technology – Denodo View Performance Best Practice

Advertisements

Since I have been doing more training of beginning users of the Denodo, there have been a lot of questions around performance best practices and optimization. This article is a quick summary of some of the high points of the Donodo documentation, which are typically useful.

However, I would like to point out that the performance of Denodo views is:

  • usually, an ongoing process as your environment evolves and that your code changes
  • also, the performance of Denodo views may involve elements be on the Denodo framework itself, such as source system databases
  • and may require some administration configuration and reengineering to achieve your full benefits in terms of establishing environment sizing, data movement databases, use of bulk load processes, and maintenance processes (E. G., Scheduled index maintenance, scheduled statistics maintenance)
  • furthermore, good general SQL and coding practices have a great deal to do with performance unrelated to the denotative toolset.

Avoid ‘Create View From Query’

Using ‘Create view from Query’ to create base views bypasses the denodo optimization engine and pushes directly to the data source as written.

Make Sure Primary Keys (PK) And Unique Indexes Have Been Set

Accurately setting the primary key (PK) on views (especially, base views): aides:

  • The static optimization phase, primary keys, and unique indexes enable Join pruning and Aggregation push-down when appropriate
  • The Primary Key is presented to consuming applications and RESTFUL web services
  • Allow browsing across the associations of views with Data Catalog.

Mirror Source System Indexes

Aiding source database indexes to denodo base views aids the denodo optimizer to make appropriate decisions. However, avoid adding indexes on the table which do not exist in the source database. This will cause the optimizer to make incorrect execution plans and will undercut performance.

Note: Primary Keys (PK) are enforced by denodo, only used to enable optimization and application capabilities.

Apply Foreign Key (FK) And Referential Constraint Associations

An association represents a foreign key relation.  However, when a referential constraint is applied to an association, every row of the ‘Dependent’ view has a matching value in the ‘Principal’ view, which meets the Condition mapping. 

Adding Indexes To Cache Views

Adding Primary Keys and Unique indexes to cached tables also aids the optimizer and, if properly maintained, aids normal database operation when querying cache tables.

Gather and Maintain View Statistics

View statistics play an essential role, helping the optimizer make decisions about execution plans and data movement.  Statistics are most important for base views and cached, especially the total rows, average size, and distinct view values.

Caching Derived Views

Caching large, long-running, complex views can improve performance and limit source system impacts and Denodo if the cache guidelines are followed.  However, to optimize efficient cached views should have Primary Keys, Unique Indexes, performance indexes, and Statistics. See caching Guidelines for additional detail.

Use Effective Joins

Effective joins are essential to performant view.  Here are some high-level tips to keep in mind when building joins:

  • When possible, use simple join condition
  • Join on primary keys or unique indexes
  • Leverage Foreign Key (FK) and Primary Keys (PK).  Especially when an association referential constraint is defined
  • Use Inner joins when Possible
  • When using outer joins, Organize joins by data source when using multiple data sources
  • Avoid using view parameters and subqueries on the join condition

Use A Building Block Approach

Breaking views into discreet units allows the optimizer more opportuning to optimize SQL’s and performance.  Here are a few tips for using the building block approach:

  • Create views for different entities (Fact, Dimension, or Subject set)
  • Build views for discreet and/or distinct data subsets
  • Use SQL tuning rules to arrive at the smallest result set as soon as possible
  • Tune each view individually

Let Denodo Determine Optimal Data Movement

Where possible, avoid manually assigning data movement strategy.  Letting Denodo determine the optimal data movement strategy (assume other view optimizations have been applied) provides the greatest flexibility as the data changes across time.  When precursor views are updated and/or tuned and Prevents errors due to data movement strategy conflicts.

Denodo Reference Links

Best Practices to Maximize Performance I: Modeling Big Data and Analytics Use Cases

Best Practices to Maximize Performance II: Configuring the Query Optimizer

Best Practices to Maximize Performance III: Caching

Best Practices to Maximize Performance IV: Detecting Bottlenecks in a Query

Denodo Knowledge Base > Performance & Optimization

Denodo Knowledge Base > Performance

Denodo E-books

Denodo Cookbook: Query Optimization

Oracle TO_CHAR to SQL Server CONVERT Equivalents to change Date to String

Advertisements

When it comes to SQL I tend to lean on the SQL I have used the most over the years, which is Oracle.  Today was no exception, I found myself trying to use the TO_CHAR command in SQL Server to format a date, which of course does not work. So, after a little thought, here are some examples of how you can the SQL Server Convert Command the achieve the equivalent result.

Example SQL Server Date Conversion SQL Code

This SQL of examples runs, as is, no from table required.

Select
CONVERT(VARCHAR(10), GETDATE(), 20) as
‘YYYY-MM-DD’
,CONVERT(VARCHAR(19), GETDATE(), 20) as ‘YYYY-MM-DD HH24:MI:SS’
,CONVERT(VARCHAR(8), GETDATE(), 112) as YYYYMMDD
,CONVERT(VARCHAR(6), GETDATE(), 112) as YYYYMM
,CONVERT(VARCHAR(12), DATEPART(YEAR, GETDATE()))+ RIGHT(‘0’+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2)
as
YYYYMM_Method_2
,CONVERT(VARCHAR(4), GETDATE(), 12) as YYMM
,CONVERT(VARCHAR(4), GETDATE(), 112) as YYYY
,CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE())) as YYYY_Method_2
,CONVERT(VARCHAR(4), YEAR(GETDATE())) as YYYY_Method_3
,RIGHT(‘0’+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2) as Two_Digit_Month
,SUBSTRING(ltrim(CONVERT(VARCHAR(4), GETDATE(), 12)),3,2) as Two_Digit_Month_2
,CONVERT(VARCHAR(10), GETDATE(), 111) as ‘YYYY/MM/DD’
,CONVERT(VARCHAR(5), GETDATE(), 8) as ‘HH24:MI’

Map TO_CHAR formats to SQL Server

You can map an Oracle TO_CHAR formats to SQL Server alternative commands as follows:

TO_CHAR
String

VARCHAR
Length

SQL
Server Convert Style

YYYY-MM-DD

VARCHAR(10)

20,
21, 120, 121, 126 and 127

YYYY-MM-DD
HH24:MI:SS

VARCHAR(19)

20,
21, 120 and 121

YYYYMMDD

VARCHAR(8)

112

YYYYMM

VARCHAR(6)

112

YYMM

VARCHAR(4)

12

YYYY

VARCHAR(4)

112

MM

VARCHAR(2)

12

YYYY/MM/DD

VARCHAR(10)

111

HH24:MI

VARCHAR(5)

8,
108, 14 and 114

HH24:MI:SS

VARCHAR(8)

8,
108, 14 and 114

Translating the formats commands

Here are some example of translating the formats commands.

Format

SQL
Server

YYYY-MM-DD

CONVERT(VARCHAR(10),
GETDATE(), 20)

YYYY-MM-DD
HH24:MI:SS

CONVERT(VARCHAR(19),
GETDATE(), 20)

YYYYMMDD

CONVERT(VARCHAR(8),
GETDATE(), 112)

YYYYMM

CONVERT(VARCHAR(6),
GETDATE(), 112)

YYMM

CONVERT(VARCHAR(4),
GETDATE(), 12)

YYYY

CONVERT(VARCHAR(4),
GETDATE(), 112)

YYYY

CONVERT(VARCHAR(4),
DATEPART(YEAR, GETDATE()))

YYYY

CONVERT(VARCHAR(4),
YEAR(GETDATE()))

MM

RIGHT(‘0’+CAST(MONTH(GETDATE())
AS VARCHAR(2)),2)

MM

SUBSTRING(ltrim(CONVERT(VARCHAR(4),
GETDATE(), 12)),3,2)

YYYY/MM/DD

CONVERT(VARCHAR(10),
GETDATE(), 111)

HH24:MI

CONVERT(VARCHAR(5),
GETDATE(), 8)

HH24:MI:SS

CONVERT(VARCHAR(8),
GETDATE(), 8)

Related Reference

Microsoft Docs, SQL, T-SQL Functions, GETDATE (Transact-SQL)

Microsoft Docs, SQL, T-SQL Functions, Date and Time Data Types and Functions (Transact-SQL)

Microsoft Docs, SQL, T-SQL Functions, DATEPART (Transact-SQL)

SQL server table Describe (DESC) equivalent

Advertisements
Transact SQL (T-SQL)

Microsoft SQL Server doesn’t seem have a describe command and usually, folks seem to want to build a stored procedure to get the describe behaviors.  However, this is not always practical based on your permissions. So, the simple SQL below will provide describe like information in a pinch.  You may want to dress it up a bit; but I usually just use it raw, as shown below by adding the table name.

Describe T-SQL Equivalent

Select *

 

From INFORMATION_SCHEMA.COLUMNS

Where TABLE_NAME = ‘<<TABLENAME>>’;

Related References

Netezza / PureData – How To Get A List Of When A Store Procedure Was Last Changed Or Created

Advertisements

In the continuing journey to track down impacted objects and to determine when the code in a database was last changed or added, here is another quick SQL, which can be used in Aginity Workbench for Netezza to retrieve a list of when Store Procedures were last updated or were created.

SQL List of When A Stored Procedure was Last Changed or Created

select t.database — Database
, t.OWNER — Object Owner
, t.PROCEDURE — Procedure Name
, o.objmodified — The Last Modified Datetime
, o.objcreated — Created Datetime

from _V_OBJECT o
, _v_procedure t
where
o.objid = t.objid
and t.DATABASE = ‘<<Database Name>>
order by o.objmodified Desc, o.objcreated Desc;

Related References

Netezza / PureData – How To Get a SQL List of When View Was Last Changed or Created

Advertisements

Netezza / PureData SQL (Structured Query Language)

Sometimes it is handy to be able to get a quick list of when a view was changed last.  It could be for any number of reason, but sometimes folks just lose track of when a view was last updated or even need to verify that it hadn’t been changed recently.  So here is a quick SQL, which can be dropped in Aginity Workbench for Netezza to create a list of when a view was created or was update dated last.  Update the Database name in the SQL and run it.

SQL List of When A view was Last Changed or Created

select t.database — Database
, t.OWNER — Object Owner
, t.VIEWNAME — View Name
, o.objmodified — The Last Modified Datetime
, o.objcreated — Created Datetime

from _V_OBJECT o
,_V_VIEW_XDB t
where
o.objid = t.objid
and DATABASE = ‘<<Database Name>>
order by o.objcreated Desc, o.objmodified Desc;

Related References

 

Netezza / PureData – Table Describe SQL

Advertisements

Netezza / Puredata Table Describe SQL

If you want to describe a PureData / Netezza table in SQL, it can be done, but Netezza doesn’t have a describe command.  Here is a quick SQL, which will give the basic structure of a table or a view.  Honestly, if you have Aginity Generating the DDL is fast and more informative, at least to me.  If you have permissions to access NZSQL you can also use the slash commands (e.g. d).

Example Netezza Table Describe SQL

select  name as Table_name,

owner as Table_Owner,

Createdate as Table_Created_Date,

type as Table_Type,

Database as Database_Name,

schema as Database_Schema,

attnum as Field_Order,

attname as Field_Name,

format_type as Field_Type,

attnotnull as Field_Not_Null_Indicator,

attlen as Field_Length

from _v_relation_column

where

name='<<Table Name Here>>’

Order by attnum;

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Command-line options for nzsql, Internal slash options

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza getting started tips, About the Netezza data warehouse appliance, Commands and queries, Basic Netezza SQL information, Commonly used nzsql internal slash commands

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL introduction, The nzsql command options, Slash options

 

 

Aginity For Netezza – How to Generate DDL

Advertisements

Aginity

How to Generate Netezza Object DDL

In ‘Aginity for Netezza’ this process is easy, if you have a user with sufficient permissions.

The basic process is:

  • In the object browser, navigate to the Database
  • select the Object (e.g. table, view, stored procedure)
  • Right Click, Select ‘Script’ > ‘DDL to query window’
  • The Object DDL will appear in the query window

Create DDL to Query Window

Related References

 

Netezza / PureData – Substring Function On Specific Delimiter

Advertisements

Netezza / PureData – Substring Function On Specific Delimiter

The function Substring (SUBSTR) in Netezza PureData provides the capability parse character type fields based on position within a character string.  However, it is possible, with a little creativity, to substring based on the position of a character in the string. This approach give more flexibility to the substring function and makes the substring more useful in many cases. This approach works fine with either the substring or substr functions.  In this example, I used the position example provide the numbers for the string command.

 

Example Substring SQL

Netezza PureData Substring Function On Specific Character In String

 

Substring SQL Used In Example

select LOCATIONTEXT

,position(‘,’ in LOCATIONTEXT) as Comma_Postion_In_String

—without Adjustment

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)) as Substring_On_Comma

—Adjusted to account for extra space

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as Substring_On_Comma_Ajusted

,’==Breaking_Up_The_Sting==’ as Divider

— breaking up the string

,SUBSTRING(LOCATIONTEXT,1, position(‘ ‘ in LOCATIONTEXT)-1) as Beggining_of_String

,SUBSTRING(LOCATIONTEXT,position(‘ ‘ in LOCATIONTEXT)+1, position(‘ ‘ in LOCATIONTEXT)-1) as Middle_Of_String

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as End_Of_String

 

FROM Blog.D_ZIPCODE

where STATE = ‘PR’

AND CITY = ‘REPTO ROBLES’

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

Netezza / PureData – Position Function

Advertisements

Netezza / PureData Position Function

 

The position function in Netezza is a simple enough function, it just returns the number of a specified character within a string (char, varchar, nvarchar, etc.) or zero if the character not found. The real power of this command is when you imbed it with character function, which require a numeric response, but the character may be inconsistent from row to row in a field.

The Position Function’s Basic Syntax

position(<<character or Character String>> in <<CharacterFieldName>>)

 

Example Position Function SQL

Netezza PureData Position Function

 

Position Function SQL Used in Example

select LOCATIONTEXT, CITY

,’==Postion Funtion Return Values==’ as Divider

,position(‘,’ in LOCATIONTEXT) as Postion_In_Nbr_String

,position(‘-‘ in LOCATIONTEXT) as Postion_Value_Not_Found

,’==Postion Combined with Substring Function==’ as Divider2

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as Position_Used_in_Substring_Function

FROM Blog.D_ZIPCODE  where STATE = ‘MN’ AND CITY = ‘RED WING’ limit 1;

 

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

 

PureData / Netezza – What date/time ranges are supported by Netezza?

Advertisements

Date/Time ranges supported by Netezza

Here is a synopsis of the temporal ranges ( date, time, and timestamp), which Netezza / PureData supports.

Temporal Type

Supported Ranges

Size In Bytes

Date

A month, day, and year. Values range from January 1, 0001, to December 31, 9999. 4 bytes

Time

An hour, minute, and second to six decimal places (microseconds). Values range from 00:00:00.000000 to 23:59:59.999999. 8 bytes

Related References

Temporal data types

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Data types, Temporal data types

Netezza date/time data type representations

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza user-defined functions, Data type helper API reference, Temporal data type helper functions, Netezza date/time data type representations

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

Netezza / PureData – How to add a Foreign Key

Advertisements
DDL (Data Definition Language)

Adding a forging key to tables in Netezza / PureData is a best practice; especially, when working with dimensionally modeled data warehouse structures and with modern governance, integration (including virtualization), presentation semantics (including reporting, business intelligence and analytics).

Foreign Key (FK) Guidelines

  • A primary key must be defined on the table and fields (or fields) to which you intend to link the foreign key
  • Avoid using distribution keys as foreign keys
  • Foreign Key field should not be nullable
  • Your foreign key link field(s) must be of the same format(s) (e.g. integer to integer, etc.)
  • Apply standard naming conventions to constraint name:
    • FK_<<Constraint_Name>>_<<Number>>
    • <<Constraint_Name>>_FK<<Number>>
  • Please note that foreign key constraints are not enforced in Netezza

Steps to add a Foreign Key

The process for adding foreign keys involves just a few steps:

  • Verify guidelines above
  • Alter table add constraint SQL command
  • Run statistics, which is optional, but strongly recommended

Basic Foreign Key SQL Command Structure

Here is the basic syntax for adding Foreign key:

ALTER TABLE <<Owner>>.<<NAME_OF_TABLE_BEING_ALTERED>>

ADD CONSTRAINT <<Constraint_Name>>_fk<Number>>

FOREIGN KEY (<<Field_Name or Field_Name List>>) REFERENCES <<Owner>>.<<target_FK_Table_Name>.(<<Field_Name or Field_Name List>>) <<On Update | On Delete>> action;

Example Foreign Key SQL Command

This is a simple one field example of the foreign key (FK)

ALTER TABLE Blog.job_stage_fact

ADD CONSTRAINT job_stage_fact_host_dim_fk1

FOREIGN KEY (hostid) REFERENCES Blog.host_dim(hostid) ON DELETE cascade ON UPDATE no action;

Related References

Alter Table

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL command reference, Alter Table, constraints

Database – What is TCL?

Advertisements

SQL (Structured Query Language)

TCL (Transaction Control Language) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. The main TCL commands are:

  • COMMIT
  • SAVEPOINT
  • ROLLBACK
  • SET TRANSACTION

Related References

 

Database – What is a foreign key?

Advertisements
Acronyms, Abbreviations, Terms, And Definitions

Definition of a Foreign Key

  • A foreign Key (FK) is a constraint that references the unique primary key (PK) of another table.

Facts About Foreign Keys

  • Foreign Keys act as a cross-reference between tables linking the foreign key (Child record) to the Primary key (parent record) of another table, which establishing a link/relationship between the table keys
  • Foreign keys are not enforced by all RDBMS
  • The concept of referential integrity is derived from foreign key theory
  • Because Foreign keys involve more than one table relationship, their implementation can be more complex than primary keys
  • A foreign-key constraint implicitly defines an index on the foreign-key column(s) in the child table, however, manually defining a matching index may improve join performance in some database
  • The SQL, normally, provides the following referential integrity actions for deletions, when enforcing foreign-keys

Cascade

  • The deletion of a parent (primary key) record may cause the deletion of corresponding foreign-key records.

No Action

  • Forbids the deletion of a parent (primary key) record, if there are dependent foreign-key records.   No Action does not mean to suppress the foreign-key constraint.

Set null

  • The deletion of a parent (primary key) record causes the corresponding foreign-key to be set to null.

Set default

  • The deletion of a record causes the corresponding foreign-keys be set to a default value instead of null upon deletion of a parent (primary key) record

Related References

Netezza / PureData – How to rebuild a Netezza view in Aginity

Advertisements

Rebuilding Netezza view sometimes becomes necessary when the view’s source table have changed underneath the view.  Rebuilding a view can be done on Netezza or in Aginity. In Aginity, it is a simple process, assume your user has permissions to create or replace a view.  The process breaks down into just a few steps:

Generate the create / replace view SQL of the original view into the query window, if you don’t have it already

In the object browser:

  • Navigate to the Database and view you wish to rebuild
  • Select the view and right click
  • Select ‘Scripts’, then ‘DDL to Query window’

Make may updates to create / replace View SQL

  • This step is not always necessary, sometimes the changes which invalided the view did not actually impact the code of the view. If changes are necessary, make may updates to the SQL code.

Execute The code

  • This I usually do by choosing the ‘Execute as a single batch’ option.  Make sure the code executes successfully.

Verify the view

  • To verify the simply execute a select statement and make it executes without errors and/or warning.

Netezza / PureData Date – Difference in Days SQL

Advertisements

Netezza /PureData Date Difference in Days

Since Netezza does not have a datediff function, the ‘old school’ of calculating the difference, in days, between dates must be used.

Subtracting Inclusive Dates

To subtract to day and include end date, as a day, in calculation (1 day is added)

select (date(‘2015-12-31’) – date(‘2015-01-01’))+1 as Inclusive_dates

From _v_dual;

 

Subtracting Non-inclusive dates

To subtract dates non-inclusive simply subtract the dates

select date(‘2015-12-31’) – date(‘2015-01-01’) as Non_Inclusive_dates

From _v_dual;

 

Example SQL From Graphic

select (date(‘2015-12-31’) – date(‘2015-01-01’))+1 as Inclusive_dates,

date(‘2015-12-31’) – date(‘2015-01-01’) as Non_Inclusive_dates

From _v_dual;

 

Related References

Netezza / PureData – How to convert a timestamp to date in SQL

Advertisements

Netezza Convert Timestamp to Date

 

Since timestamps are integer, which are numbers, the date function can easily convert a timestamp to a date in Netezza SQL.

Date Function Arguments, per IBM Documentation

First argument data type Second argument data type Returns
date numeric(8,0) date

Example Timestamp to Date Conversion SQL

In this example CREATEDATE is stored as a timestamp, despite the field name.

SELECT

DATE(CREATEDDATE)

, COUNT(*) as CNT

FROM Blog.CASES_FS

GROUP BY

date(CREATEDDATE)

ORDER BY

date(CREATEDDATE);

Related References

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

Netezza date/time data type representations

PureData System for Analytics, PureData System for Analytics 7.2.0, IBM Netezza User-Defined Functions, Data type helper API reference, Temporal data type helper functions, Netezza date/time data type representations

Netezza / PureData – What date ranges are supported by Netezza?

Advertisements

SQL (Structured Query Language)

I almost touch on this question in a previous post, but neglected to answer the question directly.  So, here is a table of the valid value range for a PureData / Netezza date field.

Valid date ranges for PureData / Netezza

Field data Type Storage Data Type Minimum Valid Value Minimum Value Shown as a Date Maximum Valid Value Maximum Value Shown as a Date Disk Usage
Date numeric(8,0) -730119 0001-01-01 00:00:00 2921939 9999-12-31 00:00:00 4

Related reference

Temporal data types

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Data types, Temporal data types

Netezza date/time data type representations

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza user-defined functions, Data type helper API reference, Temporal data type helper functions, Netezza date/time data type representations

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_ntz_sql_extns_date_time_funcs.html

Netezza / PureData – How to Number for day of week in SQL?

Advertisements

Netezza / PureData – Numeric Day of Week

I had reason today to get the number of the day of the week, in PureData / Netezza, which I don’t seem to have discussed in previous posts.  So, here is a simple script to get the number for the day of week with a couple of flavors, which may prove useful.

Basic Format

select extract(dow from <<FieldName>>) from <<SchemaName>>.<<tableName>>

Example SQL

 

SELECT

CURRENT_DATE

,  TO_CHAR(CURRENT_DATE,’DAY’) AS DAY_OF_WEEK

—WEEK STARTS ON MONDAY

,  EXTRACT(DOW FROM CURRENT_DATE)-1 AS DAY_OF_WEEK_NUMBER_STARTS_ON_MONDAY

—WEEK STARTS ON SUNDAY

,  EXTRACT(DOW FROM CURRENT_DATE) AS DAY_OF_WEEK_NUMBER_STARTS_ON_SUNDAY

—WEEK STARTS ON SATURDAY

,  EXTRACT(DOW FROM CURRENT_DATE)+1 AS DAY_OF_WEEK_NUMBER_STARTS_ON_SATURDAY

FROM _V_DUAL;

 

Related References

Extract date and time values

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Functions and operators, Functions, Extract date and time values

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

Template patterns for date/time conversions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Conversion functions, Template patterns for date/time conversions

https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_ntz_sql_extns_templ_patterns_date_time_conv.html

Database – What is a Composite Primary Key?

Advertisements
Database Table

What is a Composite Primary Key?

A Composite Primary key is Primary key What a primary key, which is defined by having multiple fields (columns) in it.  Like a Primary Key what a composite Primary Key is depends on the database.  Essentially a Composite Primary Key:

  • Is a combination of Fields (columns) which uniquely identifies every row.
  • Is an index in database systems which use indexes for optimization
  • Is a type of table constraint
  • Is applied with a data definition language (DDL) alter command
  • And may define parent-Child relationship between tables

Related References

Database – What is a Primary Key?

Advertisements
Database Table

What is a primary Key?

What a primary key is depends, somewhat, on the database.  However, in its simplest form a primary key:

  • Is a field (Column) or combination of Fields (columns) which uniquely identifies every row.
  • Is an index in database systems which use indexes for optimization
  • Is a type of table constraint
  • Is applied with a data definition language (DDL) alter command
  • And, depending on the data model can, define parent-Child relationship between tables

Related References

Oracle Database – Useful links

Advertisements

Some useful oracle documentation references.

Reference TypeLink

Database SQL Language Reference (11.2)

https://docs.oracle.com/cd/E11882_01/server.112/e41084/

Database SQL Language Reference (11.2)

https://docs.oracle.com/cd/E11882_01/server.112/e16604/toc.htm
Database SQL Language Reference (12c)https://docs.oracle.com/database/121/SQLRF/toc.htm

Database (12c) SQL*Plus User’s Guide and Reference

https://docs.oracle.com/database/121/SQPUG/toc.htm

SQL Developer Documentation

http://docs.oracle.com/cd/E12151_01/

Database – What is DDL?

Advertisements
SQL (Structured Query Language)

What is DDL (Data Definition Language)?

DDL (Data Definition Language), are the statements used to manage tables, schemas, domains, indexes, views, and privileges.  The the major actions performed by DDL commands are: create, alter, drop, grant, and revoke.

Related References

Data Modeling – Fact Table Effective Practices

Advertisements

Here are a few guidelines for modeling and designing fact tables.

Fact Table Effective Practices

  • The table naming convention should identify it as a fact table. For example:
    • Suffix Pattern:
      • <<TableName>>_Fact
      • <<TableName>>_F
    • Prefix Pattern:
      • FACT_<TableName>>
      • F_<TableName>>
    • Must contain a temporal dimension surrogate key (e.g. date dimension)
    • Measures should be nullable – this has an impact on aggregate functions (SUM, COUNT, MIN, MAX, and AVG, etc.)
    • Dimension Surrogate keys (srky) should have a foreign key (FK) constraint
    • Do not place the dimension processing in the fact jobs

Database – What is DML?

Advertisements

SQL (Structured Query Language)

What is DML (Data Manipulation Language)?

As the name indicates, Data manipulation for working with information inside a database structure.  There are four main DML commands:

  • Select – reading data rows
  • Insert – adding data rows
  • update – changing values within data rows
  • Delete – removing data row

Related References

 

What is DCL?

Advertisements

What is DCL (Data Control Language)?

Data control language (DCL) is anything that is used for administrating access (permissions/security) to database content.  The main DCL commands are:

  • Grant
  • Revoke

Related References

Infosphere DataStage – Boolean Handling for Netezza

Advertisements

Beware when you see this message when working with Boolean in DataStage, the message displays as informational (at list it did for me) not as a warning or an error.  Even though it seems innocuous, what it meant for my job, was the Boolean (‘true’ / ‘false’) was not being interpreted and everything posted to ‘false’.

In DataStage the Netezza ‘Boolean’ field/Data SQL type maps to the ‘Bit’ SQL type, which expects a numeric input of Zero (0) or one (1).  So, my solution (once I detected the problem during unit testing) was to put Transformer Stage logic in place to convert the Boolean input to the expected number value.

Netezza to Datastage Data Type Mapping

Netezza data types

InfoSphere DataStage

data types (SQL types)

Expected Input value

BOOLEANBit0 or 1 (1 = true, 0 = false)

Transformer Stage logic Boolean Handling Logic

A Netezza Boolean field can store: true values, false values, and null. So, some thought should be given to you desired data outcome for nulls

This first example sets a that the nulls are set to a specific value, which can support a specific business rule for null handling and, also, provide null handling for non-nullable fields.  Here we are setting nulls to the numeric value for ‘true’ and all other non-true inputs to ‘false’.

If isnull(Lnk_Src_In.USER_ACTIVE) then 1 Else if Lnk_Src_In.USER_ACTIVE = ‘true’ Then 1 Else 0

These second examples sets a that the nulls are set by the Else value, if your logic direction is correct value and still provides null handling for non-nullable fields.

  • If  Lnk_Src_In.USER_ACTIVE = ‘true’ Then 1 Else 0

  • If  Lnk_Src_In.USER_ACTIVE = ‘False’ Then 0 Else 1

Director Log Message

Message ID

  • IIS-DSEE-TBLD-00008

Message Text

  • <<Link Name Where Message Occurred>>: Numeric string expected. Use default value.

Or something like this:

  • <<Link Name Where Message Occurred>>: Numeric string expected for input column ‘<<Field Name Here>>‘. Use default value.

Related References

Boolean

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza user-defined functions, UDX data types reference information, Supported data types, Boolean

https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.udf.doc/r_udf_boolean_datatype.html

Data types and aliases

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza stored procedures, NZPLSQL statements and grammar, Variables and constants, Data types and aliases

https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.sproc.doc/c_sproc_data_types_aliases.html

Logical data types

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Data types, Logical data types

https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_data_types_logical.html

Data type conversions from Netezza to DataStage

InfoSphere Information Server, InfoSphere Information Server 11.5.0, Connecting to data sources, Databases, Netezza Performance Server, Netezza connector, Designing jobs by using the Netezza connector, Defining a Netezza connector job, Data type conversions, Data type conversions from Netezza to DataStage

https://www.ibm.com/support/knowledgecenter/en/SSZJPZ_11.5.0/com.ibm.swg.im.iis.conn.netezza.use.doc/topics/nzcc_mappingdatatypes.html

SQL Server – Basic SQL Server Object Naming Convention Guidance

Advertisements

Microsoft doesn’t provide macro guidance for naming convention, however, sometimes it is useful to have a place to start.  Also, there are times when flexibility with naming conventions are necessary.  So, here is a quick set of SQL Server naming conventions, which may be helpful if you find yourself working with a customer who doesn’t have an established set of naming convention standards and you need to assemble a set fast.

Basic SQL Server Object Naming Convention Guidance

  1. Each project will have its own schema.
  2. Schema represents the project
  3. First letter of each word in table/Column starts with Uppercase.
  4. Put Underscore(_) between words of table/Column name
  5. No space between words in table/column name

Object Conventions

SQL Server objects

Subject

Prefix/Suffix

Naming convention

Example

Databasedbdb<name>dbODS
Jobjbjb<Jobname>jbSSIS_RunLoadPackage
Linked Serverlsls<linkedservername>lsServer01
Trigger(Delete)tdtd<name>tdAppointment
Trigger(Insert)titi<name>tiCustomer
Trigger(Update)tutu<name>tuAppointment

SQL Server Database objects

Subject

Prefix/Suffix

Naming convention

Example

Aggregate functionafaf<Name>afTotalSales
Assembliesasass<Name>assDetermineIPs
Attribuut<name>PatientName
Database data file(s)data#<database>_data#dbAdventureWorks_data1
Database log filelog<database>_logdbAdventureWorks_log
Defaultdfdf<name>dfDate
Dimension Table _Dim<<Table Name>>_DimGeography_Dim
Fact Table_Fact<<TableName>>_FactAccounts_Payable_Fact
Filegroupfgfg<name>fgdbAdventureWorks_Index
Filestreamfsfs<name>fsInvoiceDoc
Foreign keyFKx_FKx_<sourcetable>_<goaltable>_<fieldname(s)># 
Index (not unique, non clustered)IDX_IDX_<name>_<column> 
Index (unique, non clustered)UDX_UDX_<name>_<column> 
Index (not unique, clustered)CIX_CIX_<name>_<column> 
Index (unique, clustered)CUX_CUX_<name>_<column> 
Materialized ViewMV_MV_<Table name> 
Primary keyPK_PK_<name>PK_KlantID
Scalar valued functionscfscf<name>scfDetermineNumberOfCustomers
Schemaschsch<name>schOrders
Stored procedureuspusp<projectname>_<name>uspArchive_DetermineNumberOfOrders
Synonymssynsyn<name> 
Table tbltbl<name>tblCustomers
Table Valued functiontvftvf<name>tvfDeterminecustomers
Temporary Table#tmp#tmp<name>#tmpPatient25Years
Typeuddudd<name>uddPostcode
Typetptp<name>tpPosInt
Viewvw_VW_<table name>vwGetOpenOrders

Transact SQL (T-SQL)

Subject

Prefix/Suffix

Naming convention

Example

Bigintinbinb<Variabelename>inbPatientId
Binarybinbin<Variabelename>binMessage
Bitbitbit<Variabelename>bitOK
Charchrchr<Variabelename>chrPatientname
Code (Field)_Code<Variabelename>_CodeProgram_Code
Cursorcurcur<Variabelename>curField
Date (Field)_Dt<Variabelename>_DtCreated_Dt
Datetimedtmdtm<Variabelename>dtmInsertDate
Description (Field)_Desc<Variabelename>_DescPlan_Desc
Floatfltflt<Variabelename>fltXvalue
Identity (Field) _ID<Variabelename>_IDStatus_Rollup_ID
Integerintint<Variabelename>intPatientID
Moneymnymny<Variabelename>mnyTotalAmount
Ncharchrnchrn<Variabelename>chrnPatientname
Ntexttxtntxtn<Variabelename>txtMessage
Numeric of decimaldecdec<Variabelename>decAmount
Nvarcharchvnchvn<Variabelename>chvnPatientname
Realrearea<Variabelename>reaAmount
Smalldatetimedtsdts<Variabelename>dtsInsertDate
Smallintinsins<Variabelename>insSubcategorieID
Smallmoneymnsmns<Variabelename>mnsAmount
Sql_variantvarvar<Variabelename>varFreevalue
Surrogate key (Field) _ID<Variabelename>_IDPerson_ID
Table variabletavtav<name>tavCustomers
Texttxttxt<Variabelename>txtReport
Timestamptsptsp<Variabelename>tspDeleteDate
Tinyintinyiny<Variabelename>inyCategorieID
Uniqueidentifierguidguid<Variabelename>guidKeu
Varbinarybivbiv<Variabelename>bivMessage
Varcharchvchv<Variabelename>chvPatientname