Netezza / PureData – List of Views against a table

I have found myself using this simple, but useful SQL time in recent weeks to research different issues and to help with impact analysis.  So, I thought I would post it while I’m thinking about it.  It just gives a list of views using a table, which can be handy to know.  This SQL is … More Netezza / PureData – List of Views against a table

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

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 … More Technology – Netezza: How to replace or trim CHAR(0) is NULL characters in a field

Technology – Netezza: How to Substring on a Character

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 … More Technology – Netezza: How to Substring on a Character

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

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 … More Netezza / PureData – How To Get A List Of When A Store Procedure Was Last Changed Or Created

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

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 … More Netezza / PureData – How To Get a SQL List of When View Was Last Changed or Created

Netezza / PureData – How To Quote a Single Quote in Netezza SQL

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

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

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, … More Netezza / PureData – How to build a multi table drop command from a select

Netezza / PureData – Aginity for Netezza shortcut key list

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 … More Netezza / PureData – Aginity for Netezza shortcut key list

Aginity For Netezza – How to Generate DDL

  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 … More Aginity For Netezza – How to Generate DDL

Netezza / PureData – Substring Function Example

The function Substring (SUBSTR) in Netezza PureData provides the capability to 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 Substring SQL Used In Example SELECT  LOCATIONTEXT — … More Netezza / PureData – Substring Function Example

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 … More Netezza / PureData – Substring Function On Specific Delimiter

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

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. … More Aginity for Netezza – How to Display Query Results in a Single Row Grid

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

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. … More Netezza / PureData – How to add multiple columns to a Netezza table in one SQL

Netezza / PureData – Casting Numbers to Character Data Type

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 … More Netezza / PureData – Casting Numbers to Character Data Type

PureData / Netezza – What date/time ranges are 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 … More PureData / Netezza – What date/time ranges are supported by Netezza?

Netezza / PureData – How to add a Foreign Key

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) … More Netezza / PureData – How to add a Foreign Key

Netezza / PureData – SQL Cast Conversion to Integers

From time to time, I have had to look up this information, especially, when working with old school SQL intensive ETL patterns where the transformations are being performed in SQL rather than within the ETL application.  So, rather than scrambling to find the differing integer casting range limits, bit type conversions, and alias for different integer … More Netezza / PureData – SQL Cast Conversion to Integers

PureData / Netezza – Useful links

Here are a few Netezza references, which I have found useful. Reference Type Link Vendor Documentation https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.welcome.doc/kc_welcome_V721.html IBM PureData-Netezza Developer Network (NDN) https://www.ibm.com/developerworks/community/groups/service/html/communitystart?communityUuid=266888e9-4b4b-44cd-bd51-e32d05da9143 Aginity Workbench for Netezza http://www.aginity.com/workbench/netezza/ IBM Netezza database user documentation https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/c_dbuser_plg_overview.html Client Software Packages https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.datacon.doc/c_datacon_client_sw_packages.html Netezza Client Software Installation https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.adm.doc/c_sysadm_client_software_install.html

Netezza / PureData – Dictionary Views

This is quick listing of Netezza dictionary view, sometimes call catalog views, which I have found useful when needing to pull lists of objects and to do other descriptive and/or investigative activities as a developer (Non-DBA): List of User Dictionary Views View Type View Name View Description Fields User  _v_table Returns a list of all … More Netezza / PureData – Dictionary Views

Netezza/PureData – Which to Run First, Statistics or Groom?

Now, I know, this seems like a simple question, but for folks new to Netezza, this question has come up more than a few times.  Also, this choice ultimately impacts how performant Netezza will be once you complete your maintenance operations. As a general guideline,  groom operations should be completed first, then followed by statistics … More Netezza/PureData – Which to Run First, Statistics or Groom?

Infosphere DataStage – Node Best Practices

In general, the performance and overall efficiency of your Datastage ETLs can be impacted by a number of items one of the more common of which is the configuration of nodes within infosphere.  Nodes, when properly configured,  allow Infosphere to perform Massively Parallel Processing (MPP) and the ultimate goal of your Node configuration in Infosphere … More Infosphere DataStage – Node Best Practices

Infosphere Datastage – Useful Date Transformations

Here are few Datastage date transformation, which I have found useful, many of these can Also be accomplished in SQL, if sourcing your data from an RDBMS. Useful Date Transformations Item Description Result Tomorrow DateFromDaysSince(1, CurrentDate())   Yesterday DateFromDaysSince(-1, CurrentDate())   Convert date to string with dashes DateToString((<< Date_Field or CurrentDate() >>,”%,”%YYYY-%MM-%DD”) 2011-11-02 Convert date … More Infosphere Datastage – Useful Date Transformations

Netezza [HY000] ERROR: Please issue a groom on table

While altering a Netezza table this error was produced: ERROR [HY000] ERROR: Please issue a groom on table ‘<<TableName>>’, maximum table versions exceeded. The error was resolved by running these commands on succession: GROOM TABLE ‘<<TableName>> VERSIONS; GROOM TABLE ‘<<TableName>> PAGES ALL; Related References Groom Table PureData System for Analytics, PureData System for Analytics 7.2.0, … More Netezza [HY000] ERROR: Please issue a groom on table

Netezza PureData SQL Date Formatting Examples

Below is a SQL of various examples of IBM Netezza/PureData date formats, which may to be a useful reference. SELECT CURRENT_DATE, —————–Sort Pattern1——————— TO_CHAR(CURRENT_DATE,’YYYYMMDD’) AS DATE_SORT_PATTERN1, ——————– Date Formats—————— TO_CHAR(CURRENT_DATE,’DD/MM/YYYY’) AS DATE_PATTERN1, TO_CHAR(CURRENT_DATE,’DD/MON/YY’) AS DATE_PATTERN2, TO_CHAR(CURRENT_DATE,’DD/MON/YYYY’) AS DATE_PATTERN3, TO_CHAR(CURRENT_DATE,’DD-MON-YY’) AS DATE_PATTERN4, TO_CHAR(CURRENT_DATE,’MM.DD.YY’) AS DATE_PATTERN5, TO_CHAR(CURRENT_DATE,’MM.DD.YYYY’) AS DATE_PATTERN6, TO_CHAR(CURRENT_DATE,’MM/DD/YYYY’) AS DATE_PATTERN7, TO_CHAR(CURRENT_DATE,’MM-DD-YY’) AS DATE_PATTERN8, TO_CHAR(CURRENT_DATE,’MM-DD-YYYY’) AS … More Netezza PureData SQL Date Formatting Examples