What does “ERROR: pg_atoi: error in : can’t parse ” mean?

This is one of those vague messages, which takes a little time to run down, but once you understand it, is easily fixed.  Basically, this error happens because you have mismatched and incompatible fields.

Question

What does “ERROR: pg_atoi: error in <ascii>: can’t parse <ascii>” mean?

Answer

The error is the result of an implicit conversion being performed on a character field (varchar/char) while being compared to a numeric field or value.

Vendor Reference Link

Netezza JDBC Error – Unterminated quoted string

The ‘Unterminated quoted string’ error occurs from time to time when working with the InfoSphere DataStage Netezza JDBC Connector stage and is nebulas, at best.  However, the solution is, normally, straight forward enough once you understand it.  Usually, this error is the result of target table fields or field being shorter than the input data.  The fix is, normally, to compare you input field lengths (or composite field length, if consolidation fields into one field) and adjusting the field length higher.  In some cases, if business rules allow you may be able to substring or truncate the input data length (not a recommended approach), but information can be lost with this approach.

Error

org.netezza.error.NzSQLException: ERROR:  Unterminated quoted string

Example Error Message

Tgt_IIS_Job_Dim,0: The connector encountered a Java exception:  org.netezza.error.NzSQLException: ERROR:  Unterminated quoted string    at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:287)    at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76)  at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)       at org.netezza.sql.NzStatement._execute(NzStatement.java:885)           at org.netezza.sql.NzPreparedStatament.executeUpdate(NzPreparedStatament.java:229)   at com.ibm.is.cc.jdbc.CC_JDBCRecordDataSetConsumer.executeStatements(CC_JDBCRecordDataSetConsumer.java:2846)               at com.ibm.is.cc.jdbc.CC_JDBCBigBufferRecordDataSetConsumer.consumeBigBuffer(CC_JDBCBigBufferRecordDataSetConsumer.java:712)

Infosphere Information Server – Server Edition – Netezza Connector

Recently, I was asked to research if a Netezza Connector can be used in a server job.  Basically, the answer is yes.  The connector stage does exist and can be used.  At least, I was able to make it work fine.  However, a word of caution, the Netezza Connector stage, as of now, is not listed as a supported Server Edition stage in the IIS DataStage 11.5 ‘Parallel job stages and server job stages’ list page.

Parallel job stages and server job stages’ list page

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 TypeView NameView DescriptionFields
User _v_tableReturns a list of all user tablesobjid, TableName, Owner, and CreateDate
User_v_procedureReturns a list of all the stored procedures and their attributesobjid, procedure, owner, createdate, objtype, description, result, numargs, arguments, proceduresignature, builtin, proceduresource, sproc, and executedasowner
User_v_viewReturns a list of all user viewsobjid, ViewName, Owner, CreateDate, relhasindex, relkind, relchecks, reltriggers, relhasrules, relukeys, relfkeys, relhaspkey, and relnatts
User_v_sequenceReturns a list of all defined sequencesobjid, SeqName, Owner, and CreateDate
User_v_sessionReturns a list of all active sessionsID, PID, UserName, Database, ConnectTime, ConnStatus, and LastCommand
User_v_table_dist_mapReturns a list of all fields that are used to determine the table’s data distributionobjid, TableName, Owner, CreateDate, DistNum, and DistFldName

Examples SQL’s

A listing of all User tables

Select *

from _v_table

Order by TABLENAME;

List of tables for a particular owner

Select TABLENAME, OWNER

from _v_table

WHERE OWNER = ‘<<OwnerID>>’

Order by TABLENAME;

A listing of all User procedures

Select *

from _v_procedure

Order by Procedure;

List of procedures for a particular owner

Select PROCEDURE, OWNER*

from _v_procedure

WHERE OWNER = ‘<<OwnerID>>’

Order by Procedure;

A listing of all User views

Select *

from _v_view

Order by VIEWNAME;

List of views for a particular owner

Select OBJID, VIEWNAME, OWNER

from _v_view

WHERE OWNER = ‘<<OwnerID>>’

Order by VIEWNAME;

Related References

These references provide additional information, which may be helpful, but was not covered here:

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 operations.

Related References

Groom Table

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL command reference, GROOM TABLE

Generate Statistics

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL command reference, GENERATE STATISTICS

Netezza/PureData – How to pull a list of tables with row counts

Pulling a list of Netezza tables with row counts is fairly straight forward, once you figure it out.  Here is a sample of an easy way to do to.

SELECT TABLENAME, RELTUPLES

FROM _V_TABLE where objtype = ‘TABLE’ ORDER BY TABLENAME;

Netezza/PureData – Row Number Function

I recently had to research how to create a unique row number for a select result in a Netezza query (1 to Last row number).  So, I thought would share the things I learned, having come from an Oracle background where ROWNUM pseudo column does this very nicely.

My solution, which worked

  • Using the ROW_NUMBER() function
  • Using a ‘Partition By’ on ‘NULL’
  • Creating a composite unified sort key. Using multiple fields would reset the count on the last field break.  The derived composite sort key field got past that problem.

SQL Format:

ROW_NUMBER() OVER( PARTITION BY NULL ORDER BY <<Sort_By_Field_List [direction asc/desc]>> ) as ROW_NUM

Related References