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.
What does “ERROR: pg_atoi: error in <ascii>: can’t parse <ascii>” mean?
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.
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.
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)
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
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
Returns a list of all user tables
objid, TableName, Owner, and CreateDate
Returns a list of all the stored procedures and their attributes
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.
PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL command reference, GROOM TABLE
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.
ROW_NUMBER() OVER( PARTITION BY NULL ORDER BY <<Sort_By_Field_List [direction asc/desc]>> ) as ROW_NUM