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

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

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

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

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 build a multi table drop command from a select

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

OLTP vs Data Warehousing

Database, OLTP vs Data Warehousing

Database

OLTP Versus Data Warehousing

I’ve tried to explain the difference between OLTP systems and a Data Warehouse to my managers many times, as I’ve worked at a hospital as a Data Warehouse Manager/data analyst for many years. Why was the list that came from the operational applications different than the one that came from the Data Warehouse? Why couldn’t I just get a list of patients that were laying in the hospital right now from the Data Warehouse? So I explained, and explained again, and explained to another manager, and another. You get the picture.
In this article I will explain this very same thing to you. So you know  how to explain this to your manager. Or, if you are a manager, you might understand what your data analyst can and cannot give you.

OLTP

OLTP stands for OLine Transactional Processing. With other words: getting your data directly from the operational systems to make reports. An operational system is a system that is used for the day to day processes.
For example: When a patient checks in, his or her information gets entered into a Patient Information System. The doctor put scheduled tests, a diagnoses and a treatment plan in there as well. Doctors, nurses and other people working with patients use this system on a daily basis to enter and get detailed information on their patients.
The way the data is stored within operational systems is so the data can be used efficiently by the people working directly on the product, or with the patient in this case.

Data Warehousing

A Data Warehouse is a big database that fills itself with data from operational systems. It is used solely for reporting and analytical purposes. No one uses this data for day to day operations. The beauty of a Data Warehouse is, among others, that you can combine the data from the different operational systems. You can actually combine the number of patients in a department with the number of nurses for example. You can see how far a doctor is behind schedule and find the cause of that by looking at the patients. Does he run late with elderly patients? Is there a particular diagnoses that takes more time? Or does he just oversleep a lot? You can use this information to look at the past, see trends, so you can plan for the future.

The difference between OLTP and Data Warehousing

This is how a Data Warehouse works:

How a Data Warehouse works

How a Data Warehouse works

The data gets entered into the operational systems. Then the ETL processes Extract this data from these systems, Transforms the data so it will fit neatly into the Data Warehouse, and then Loads it into the Data Warehouse. After that reports are formed with a reporting tool, from the data that lies in the Data Warehouse.

This is how OLTP works:

How OLTP works

How OLTP works

Reports are directly made from the data inside the database of the operational systems. Some operational systems come with their own reporting tool, but you can always use a standalone reporting tool to make reports form the operational databases.

Pro’s and Con’s

Data Warehousing

Pro’s:

  • There is no strain on the operational systems during business hours
    • As you can schedule the ETL processes to run during the hours the least amount of people are using the operational system, you won’t disturb the operational processes. And when you need to run a large query, the operational systems won’t be affected, as you are working directly on the Data Warehouse database.
  • Data from different systems can be combined
    • It is possible to combine finance and productivity data for example. As the ETL process transforms the data so it can be combined.
  • Data is optimized for making queries and reports
    • You use different data in reports than you use on a day to day base. A Data Warehouse is built for this. For instance: most Data Warehouses have a separate date table where the weekday, day, month and year is saved. You can make a query to derive the weekday from a date, but that takes processing time. By using a separate table like this you’ll save time and decrease the strain on the database.
  • Data is saved longer than in the source systems
    • The source systems need to have their old records deleted when they are no longer used in the day to day operations. So they get deleted to gain performance.

Con’s:

  • You always look at the past
    • A Data Warehouse is updated once a night, or even just once a week. That means that you never have the latest data. Staying with the hospital example: you never knew how many patients are in the hospital are right now. Or what surgeon didn’t show up on time this morning.
  • You don’t have all the data
    • A Data Warehouse is built for discovering trends, showing the big picture. The little details, the ones not used in trends, get discarded during the ETL process.
  • Data isn’t the same as the data in the source systems
    • Because the data is older than those of the source systems it will always be a little different. But also because of the Transformation step in the ETL process, data will be a little different. It doesn’t mean one or the other is wrong. It’s just a different way of looking at the data. For example: the Data Warehouse at the hospital excluded all transactions that were marked as cancelled. If you try to get the same reports from both systems, and don’t exclude the cancelled transactions in the source system, you’ll get different results.

online transactional processing (OLTP)

Pro’s

  • You get real time data
    • If someone is entering a new record now, you’ll see it right away in your report. No delays.
  • You’ve got all the details
    • You have access to all the details that the employees have entered into the system. No grouping, no skipping records, just all the raw data that’s available.

Con’s

  • You are putting strain on an application during business hours.
    • When you are making a large query, you can take processing space that would otherwise be available to the people that need to work with this system for their day to day operations. And if you make an error, by for instance forgetting to put a date filter on your query, you could even bring the system down so no one can use it anymore.
  • You can’t compare the data with data from other sources.
    • Even when the systems are similar. Like an HR system and a payroll system that use each other to work. Data is always going to be different because it is granulated on a different level, or not all data is relevant for both systems.
  • You don’t have access to old data
    • To keep the applications at peak performance, old data, that’s irrelevant to day to day operations is deleted.
  • Data is optimized to suit day to day operations
    • And not for report making. This means you’ll have to get creative with your queries to get the data you need.

So what method should you use?

That all depends on what you need at that moment. If you need detailed information about things that are happening now, you should use OLTP.
If you are looking for trends, or insights on a higher level, you should use a Data Warehouse.

 Related References

 

 

Netezza / PureData – Aginity for Netezza shortcut key list

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

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

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

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

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

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 – Position Function

SQL (Structured Query Language), Netezza PureData Position Function, SQL, Position Function

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, SQL, Position Function

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

 

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

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

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?

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

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

 

 

Database – What is a foreign key?

Acronyms, Abbreviations, Terms, And Definitions, DDL (Data Definition Language), What is a foreign key

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 – Two ways to get Numeric Day of Year

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

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