Tag Archives: Netezza

Netezza / Puredata – How to replace or trim CHAR(0) is NULL characters in a field

Posted on by
Reply
PureData Powered by Netezza

PureData Powered by Netezza

Occasionally, one runs into the problem of hidden field values breaking join criteria.  I have had to clean up bad archive and conversion data with hidden characters serval times over the last couple of weeks, so, I thought I might as well capture this note for future use.

I tried the Replace command which is prevalent for Netezza answers to this issue on the web, but my client’s version does not support that command.  So, I needed to use the Translate command instead to accomplish it.  It took a couple of searches of the usual bad actors to find the character causing the issue, which on this day was chr(0).  Here is a quick mockup of the command I used to solve this issue.

Example Select Statement

Here is a quick example select SQL to identify problem rows.

SELECT TRANSLATE(F.BLOGTYPE_CODE, CHR(0), ”) AS BLOGTYPE_CODE, BT.BLOG_TYP_ID, LENGTH(BT.BLOG_TYP_ID) AS LNGTH_BT, LENGTH(F.BLOGTYPE_CODE) AS LNGTH_ BLOGTYPE

FROM  BLOGS_TBL F,  BLOG_TYPES BT WHERE TRANSLATE(F.BLOGTYPE_CODE, CHR(0), ”) =  BT.BLOG_TYP_ID AND LENGTH(BT.BLOG_TYP_ID) <>Length(LENGTH(F.BLOGTYPE_CODE) ;

 

Example Update Statement

Here is a quick shell update statement to remove the Char(0) characters from the problem field.

Update <<Your Table Name>> A

Set A.<<Your Field Name>> = TRANSLATE(A.<<Your FieldName>>, CHR(0), ”)

where length(A.<<Your Field Name>>) <> Length(A.<<Your FieldName>>) And << Additional criteria>>;

 

 

 

Netezza / PureData – How to Substring on a Character

Posted on by
PureData Powered by Netezza

PureData Powered by Netezza

 

I had a reason this week to perform a substring on a character in Netezza this week, something I have not had a need to do before.  The process was not as straightforward as I would have thought, since the command is explained as a static position command, and the IBM documentation, honestly, wasn’t much help.  Knowing full well, that text strings are variable having to provide a static position is not terribly useful in and of itself.  So, we need to use an expression to make the substring command flexible and dynamic.

I did get it work the way I needed, but it took two commands to make it happen:

  • The First was the ’instr’ command to identify the field and character I wanted to substring on: instr(<<FIELD_NAME>>,’~’) as This provides the position number of the tilde (~).
  • The second was the ‘substr’ command in which I embedded the ‘instr’ command: substr(<<FIELD_NAME>>,0,instr(<<FIELD_NAME>>,’~’) )

This worked nicely for what I needed, which was to pick out a file name from the beginning of a string, which was delimited with a tilde (~)

Substring on a Character Command Format

  • This format example starts with position zero (0) as position 1 of substring command and goes to the first tilde (~) as position 2 of the substring command.
Select  <<FIELD_NAME>>

, instr(<>,’~’) as pos2

, substr(<<FIELD_NAME>>,0,instr(<<FIELD_NAME>>,’~’) ) as Results

From <<Table_Name>>

where  <<Where_Clause>>;

 

 

Related references

IBM Knowledge Center, Home, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, Home PureData System for Analytics 7.0.3, IBM Netezza Database User’s Guide, Netezza SQL basics, Netezza SQL extensions, Character functions

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

Posted on by
Netezza / Puredata - SQL (Structured Query Language)

Netezza / Puredata – SQL (Structured Query Language)

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

Posted on by
Netezza / PureData SQL (Structured Query Language)

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 – How To Quote a Single Quote in Netezza SQL

Posted on by

How To Quote a Single Quote in Netezza SQL?

The short answer is to use four single quotes (””), which will result in a single quote within the select statement results.

How to Assemble the SQL to Quote a Single Quote in a SQL Select Statement

Knowing how to construct a list to embed in a SQL where clause ‘in’ list or to add to an ETL job can be a serious time saver eliminating the need to manually edit large lists.  In the example below, I used the Select result set to create a rather long list of values, which needed to be included in an ELT where clause.  By:

  • Adding the comma delimiter (‘,’) and a Concatenate (||) on the front
  • Followed by adding a quoted single Quote (entered as four single quotes (””)) and a Concatenate (||)
  • The Field I which to have delaminated and Quoted (S1.ORDER_NUM)
  • And closed with a quoted single Quote (entered as four single quotes (””))

This results in a delimited and quoted list ( ,’116490856′) which needs only to have the parentheses added and the first comma removed, which is much less work than manually editing the 200 item that resulted from this select.

Example SQL:

SELECT Distinct

‘,’||””|| S1.ORDER_NUM||”” as Quoted_Order_Number

FROM Sales S1

 

How to Quote A Single Quote Example SQL

How to Quote A Single Quote Example SQL

Related Reference

Netezza / PureData – How to build a multi table drop command from a select

Posted on by
Database Management

Database Management

How to Quick Drop Multiple Tables

occasionally, there is a need to quickly drop a list of tables and you don’t always want to write or generate each command individually in Aginity.  So, here is a quick example of how you can use a ‘Select’ SQL statement to generate a list of drop commands for you. Now, this approach assumes there is a common naming convention, so, you may need to adapt it to your needs.

An outline of the Drop Multiple Tables Process

Here is a quick summary of the steps to generate the drop statements from _V_Table:

  1. Build required Netezza SQL select; paying particular attention to the where clause criteria to exclude any unnecessary tables.
  2. Execute the SQL statement
  3. Copy from Aginity Results Tab without headers
  4. Past into new Aginity Query window
  5. validate that only the tables are in the list — No extras
  6. Click with the SQL Drop command list and Execute as a single batch

Example generate the drop statements

select  ‘Drop table ‘||tablename||’;’

from _V_TABLE

where tablename like ‘NZCC_TT_%’;

 

Related References

IBM Knowledge Center > PureData System for Analytics 7.2.1

IBM Netezza database user documentation > Netezza SQL command reference > Drop Table

Netezza / PureData – Aginity for Netezza shortcut key list

Posted on by
Aginity for netezza shortcut key list

Aginity for Netezza

Recently, while working with a couple of my teammates on different projects I picked up a couple shortcut keys for Aginity for netezza, which I did not know existed. So, I thought about be nice to put a list of shortcut keys for future reference. I don’t use most of them very often, but I have flagged the ones that I have found to be frequently useful. I hope you find this useful as well.

Frequently Used By Me

Shortcut Keystrokes

Shortcut Description
Alt-C Complete Code Snippet
Alt-F4 Exit
Alt-Q Go to Query
Alt-R Go to Results
Alt-T Go to Tree
Atl-H User Query History
Ctrl-Alt-0 Goto Bookmark 0
Ctrl-Alt-1 Goto Bookmark 1
Ctrl-Alt-2 Goto Bookmark 2
Ctrl-Alt-3 Goto Bookmark 3
Ctrl-Alt-4 Goto Bookmark 4
Ctrl-Alt-5 Goto Bookmark 5
Ctrl-Alt-6 Goto Bookmark 6
Ctrl-Alt-7 Goto Bookmark 7
Ctrl-Alt-8 Goto Bookmark 8
Ctrl-Alt-9 Goto Bookmark 9
X Ctrl-Alt-C Comment Selection
Ctrl-Alt-Left Goto Previous Bookmark
Ctrl-Alt-Right Goto Next Bookmark
Ctrl-Alt-Shift-0 Set Bookmark 0
Ctrl-Alt-Shift-1 Set Bookmark 1
Ctrl-Alt-Shift-2 Set Bookmark 2
Ctrl-Alt-Shift-3 Set Bookmark 3
Ctrl-Alt-Shift-4 Set Bookmark 4
Ctrl-Alt-Shift-5 Set Bookmark 5
Ctrl-Alt-Shift-6 Set Bookmark 6
Ctrl-Alt-Shift-7 Set Bookmark 7
Ctrl-Alt-Shift-8 Set Bookmark 8
Ctrl-Alt-Shift-9 Set Bookmark 9
Ctrl-Alt-Shift-U Change select case
X Ctrl-Alt-U Uncomment Selection
Ctrl-Alt-W Word Wrap
X Ctrl-A Select All
Ctrl-B Toggle Object Browser
X Ctrl-C Copy
X Ctrl+Double click object name Find object in browser panel for current database
X Ctrl-F Find
Ctrl-F5 Execute as Single Batch
Ctrl-F6 Next Query Tab
Ctrl-G Goto Line
Ctrl-H Replace
Ctrl-N New Query Window
Ctrl-O Open SQL File
Ctrl-P Print
Ctrl-R Toggle
Ctrl-S Save Query
Ctrl-Shift-F6 Previous Query Tab
Ctrl-Shift-U Make selection UPPER case
Ctrl-T Add New Query Editor
Ctrl-U Make selection LOWER case
Ctrl-V Paste
X Ctrl-X Cut
X Ctrl-Y Redo
X Ctrl-Z Undo
F11 Toggle full screen
F12 Select Query at Cursor
 X F3 Find Again
F5 Execute
F8 Explain
F9 Toggle Bookmark
Shift-F5 Execute All

Related References

 

Netezza / PureData – Table Describe SQL

Posted on by
Netezza Puredata Table Describe SQL

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

Posted on by
Aginity, Aginity for Netezza, Netezza, PureData, DDL, SQL

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

Create DDL to Query Window

Related References

 

Netezza / PureData – Substring Function Example

Posted on by
SQL (Structured Query Language), Netezza PureData – Substring Function Example, Substr

Netezza / PureData – Substring Function Example

The function Substring (SUBSTR) in Netezza PureData provides the capability parse character type fields based on position within a character string.

Substring Functions Basic Syntax

SUBSTRING Function Syntax

SUBSTRING(<<CharacterField>>,<< StartingPosition integer>>, <<for Number of characters Integer–optional>>)

 

SUBSTR Function Syntax

SUBSTR((<>,<< StartingPosition integer>>, <>)

 

Example Substring SQL

Netezza / PureData Substring Example

Netezza / PureData Substring Example

Substring SQL Used In Example

SELECT  LOCATIONTEXT

— From the Left Of the String

—

— Using SUBSTRING Function

,’==SUBSTRING From the Left==’ as Divider1

,SUBSTRING(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTRING_LFT

,SUBSTRING(LOCATIONTEXT,7,6) as Middle_Using_SUBSTRING_LFT

,SUBSTRING(LOCATIONTEXT,15) as End_Using_SUBSTRING_LFT

,’==SUBSTR From the Left==’ as Divider2

—Using SUBSTR Function

 

,SUBSTR(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTR_LFT

,SUBSTR(LOCATIONTEXT,7,6) as Middle_Using_SUBSTR_LFT

,SUBSTR(LOCATIONTEXT,15) as End_Using_SUBSTR_LFT

—

— From the right of the String

—

,’==SUBSTRING From the Right==’ as Divider3

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTRING_RGT

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTRING_RGT

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTRING_RGT

,’==SUBSTR From the right==’ as Divider4

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTR_RGT

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTR_RGT

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTR_RGT

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 – Substring Function On Specific Delimiter

Posted on by
SQL (Structured Query Language), Netezza / PureData - Substring Function On Specific Delimiter, substr

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, substr

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

Aginity for Netezza – How to Display Query Results in a Single Row Grid

Posted on by
Aginity

Aginity

Displaying your Netezza query results in a grid can be useful.  Especially, when desiring to navigation left and right to see an entire rows data and to avoid the distraction of other rows being displayed on the screen. I use this capability in Aginity when I’m proofing code results and/or validating data in a table.

How To switch to the Single Row Grid

  • Execute your Query
  • When the results return, right click on the gray bar above your results (where you see the drag a column box
  • Choose the ‘Show a Single Row Grid’ Menu item

    Aginity Show Single Row Grid

    Aginity Show Single Row Grid

 

Grid View Change

  • Your result display will change from a horizontal row to a vertical grid as shown below
Aginity Single Row Grid Display

Aginity Single Row Grid Display

How to Navigate in the Single Row Grid

  • To navigate in the single row grid, use the buttons provided at the bottom of the results section.
Aginity Single Row Grid Navigation Buttons

Aginity Single Row Grid Navigation Buttons

Related References

 

Netezza / PureData – How to add multiple columns to a Netezza table in one SQL

Posted on by
add multiple columns to a Netezza table , alter table

SQL (Structured Query Language)

 

I had this example floating around in a notepad for a while, but I find myself coming back it occasionally.  So, I thought I would add it to this blog for future reference.

The Table Alter Process

This is an outline of the Alter table process I follow, for reference, in case it is helpful.

  • Generate DDL in Aginity and make backup original table structure
  • Perform Insert into backup table from original table
  • Create Alter SQL
  • Execute Alter SQL
  • Refresh Aginity table columns
  • Generate new DDL
  • visually validate DDL Structure
  • If correct, archive copy of DDL to version control system
  • Preform any update commands, if required, required to populate the new columns.
  • Execute post alter table cleanup
    • Groom Versions
    • Groom table
    • Generate statistics
  • Once the any required processes and the data have been validated, drop the backup table.

 

Basic Alter SQL Command Structure

Here is the basic syntax for adding multiple columns:

ALTER TABLE <<OWNER>>.<<TABLENAME>>

ADD COLUMN <<FieldName1>> <<Field Type>> <<Constraint, if applicable>>

, <<FieldName2>> <<Field Type>> <<Constraint, if applicable>>;

 

Example Alter SQL Command to a Multiple Columns

Here is a quick example, which is adding four columns:

Example SQL Adding Multiple Columns

ALTER TABLE BLOG.PRODUCT_DIM

ADD COLUMN MANUFACTURING_PLANT_KEY NUMERIC(6,0) NOT NULL DEFAULT 0

, LEAD_TIME_PRODUCTION NUMERIC(2,0)  NOT NULL DEFAULT 0

, PRODUCT_CYCLE CHARACTER VARYING(15)  NOT NULL DEFAULT ‘ ‘::”NVARCHAR”

, PRODUCT_CLASS CHARACTER VARYING(2)  NOT NULL  DEFAULT ‘ ‘::”NVARCHAR” ;

 

Cleanup Table SQL Statements

GROOM TABLE BLOG.PRODUCT_DIM VERSIONS;

GROOM TABLE BLOG.PRODUCT_DIM;

GENERATE STATISTICS ON BLOG.PRODUCT_DIM;

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, ALTER TABLE

Netezza / PureData – Casting Numbers to Character Data Type

Posted on by
Cast Conversion Format

Cast Conversion Format

I noticed that someone has been searching for this example on my site, so, here is a quick example of how to cast number data to a character data type.  I ran this SQL example in netezza and it worked fine.

Basic Casting Conversion Format

cast(<<FieldName>> as <<IntegerType_or_Alias>>) as <<FieldName>>

 

Example Casting Number to Character Data Type SQL

 

SELECT
—-Casting Integer to Character Data Type —————

SUBMITDATE_SRKY as  SUBMITDATE_SRKY_INTEGER
, cast(SUBMITDATE_SRKY as  char(10)) as Integer_to_CHAR
, cast(SUBMITDATE_SRKY as Varchar(10)) as Integer_to_VARCHAR
, cast(SUBMITDATE_SRKY as Varchar(10)) as Integer_to_VARCHAR
, cast(SUBMITDATE_SRKY as Nchar(10)) as Integer_to_NCHAR
, cast(SUBMITDATE_SRKY as NVarchar(10)) as Integer_to_NVARCHAR

—-Casting Double Precision Number to Character Data Type —————

, HOST_CPU_SECS as DOUBLE_PRECISION_NUMBER
, cast(HOST_CPU_SECS as  char(30)) as DOUBLE_PRECISION_to_CHAR
, cast(HOST_CPU_SECS as Varchar(30)) as DOUBLE_PRECISION_to_VARCHAR
, cast(HOST_CPU_SECS as Varchar(30)) as DOUBLE_PRECISION_to_VARCHAR
, cast(HOST_CPU_SECS as Nchar(30)) as DOUBLE_PRECISION_to_NCHAR
, cast(HOST_CPU_SECS as NVarchar(30)) as DOUBLE_PRECISION_to_NVARCHAR

—-Casting Numeric to Character Data Type —————

, TOTALRUNTIME  as NUMERIC_FIELD
, cast(TOTALRUNTIME as  char(30)) as NUMERIC_FIELD_to_CHAR
, cast(TOTALRUNTIME as Varchar(30)) as NUMERIC_FIELD_to_VARCHAR
, cast(TOTALRUNTIME as Varchar(30)) as NUMERIC_FIELD_to_VARCHAR
, cast(TOTALRUNTIME as Nchar(30)) as NUMERIC_FIELD_to_NCHAR
, cast(TOTALRUNTIME as NVarchar(30)) as NUMERIC_FIELD_to_NVARCHAR
FROM NETEZZA_QUERY_FACT ;

Related References

IBM, IBM Knowledge Center, PureData System for Analytics,Version 7.2.1

IBM Netezza stored procedures, NZPLSQL statements and grammar, Variables and constants, Data types and aliases

IBM, IBM Knowledge Center, PureData System for Analytics,Version 7.2.1

IBM Netezza database user documentation,SQL statement grammar,Explicit and implicit casting, Summary of Netezza casting

IBM, IBM Knowledge Center, PureData System for Analytics,Version 7.2.1

IBM Netezza database user documentation ,Netezza SQL basics,Netezza SQL extensions

 

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

Posted on by
SQL (Structured Query Language), Date/Time ranges supported by Netezza

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

Posted on by
DDL (Data Definition Language), Netezza PureData How to add a Foreign Key

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

 

 

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

Posted on by
How To Generate View or table DDL in Aginity For Netezza PureData

How To Generate View or table DDL in Aginity For Netezza

 

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.

Related References

 

Netezza / PureData – Two ways to get Numeric Day of Year

Posted on by
Netezza PureData Numeric Day Of Year

Netezza PureData Numeric Day Of Year

 

Two Ways To Get Numeric Day of Year

In Netezza there are at least two way to get the numeric day of year.  These are using:

  • The cast ‘DDD’ function or
  • The Extract ‘doy’ function

Example SQL for Numeric Day of Year

Here is a quick sample SQL of two ways to get the Numeric day of year in Netezza / PureData.

SELECT

CURRENT_DATE as “CURRENT_DATE”,

—————-Day Of Year Cast Method  ———————

 

TO_CHAR(CURRENT_DATE,’DDD’) AS CALENDAR_DAY_OF_YEAR_NUMBER_CAST_METHOD,

————Day Of Year Extract Method ———————–

DATE_PART(‘doy’, current_date) AS CALENDAR_DAY_OF_YEAR_NUMBER_EXTRACT_METHOD

FROM _V_DUAL;

 

Related References

Explicit and implicit casting

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, SQL statement grammar, Explicit and implicit casting

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

 

 

 

 

Netezza / PureData – How to calculate months between two dates

Posted on by
Netezza, PureData, Months Between Two Dates, function, SQL, difference between two dates

Netezza PureData Months Between Two Dates

Recently, I had reason to during the months between two dates to test some data against business rule/requirement.  Pleasantly surprised I was to find that Netezza had an easy to use function ‘months_between’ function to calculate the difference.

 

The Months Between SQL Function syntax

The months_Between function uses two dates to perform the calculation.  Whether you want the output to be a positive or negative number determines the field order within the function.

  • For a positive number result, put the Newest Date Field first, separated by a comma, then Oldest Date Field
  • For a negative number result, just reverse the order putting the Oldest Date Field first, separated by a comma, then the Newest Date Field

The results will contain a decimal for the days of the month and you will need to round, based on your business requirements, to achieve a whole number.

 

SELECT months_between(<<DateField>>, <<DateField>>) as <<OutputFiledName>>,

from <<TableName>>;

 

Example Months Between SQL

SELECT months_between(current_date, Date(‘2017-01-01’)) as Difference_In_Months,

round(months_between(current_date, Date(‘2017-01-01’)) ) as Difference_In_Months_Rounded

from _v_dual;

 

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 / PureData Date – Difference in Days SQL

Posted on by
Netezza PureData Date Difference in Days SQL, Subtracting Non-inclusive dates, Subtracting inclusive dates

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