[ERROR] Bad int8 external representation (SQL-HY000) - NETEZZA Sql

This is caused to due to invalid data getting queried in SELECT Query.

Common data errors:

1. For Integer/Decimal fields we are getting Character values.
2. Date or TimeStamp Datatype we are getting incorrect date formats or invalid characters.


For Instance,

We have two tables and have to do union on them.

TABLE A                                               TABLE B
COL1   VARCHAR                                COL1 VARCHAR
COL2   INTEGER                                   COL2  INTEGER
COL3   VARCHAR                                 COL3  VARCHAR
COL4   VARCHAR                                 COL4   VARCHAR


When we are doing Union of two datasets. If the column order is incorrect we would be be select wrong data for other datatype.
In below query, Integer datatype is getting mismatched with VARCHAR datatype.

This would lead to BAD INT8 error.


SELECT
COL1
COL2
COL3
COL4
FROM TABLE A

UNION

SELECT
COL1
COL3
COL2
COL4
FROM TABLE A


WORKAROUND:

Correct the SELECT order for all column in both the SQLs

[ERROR] The Integration Service fails the session, as Netezza may not be able to serialize execution of queries." when running a PowerCenter session with a ​Netezza target OR Could not serialize - transaction aborted

A PowerCenter session with a ​Netezza Bulk Writer target fails with the following error:

[ERROR] The Integration Service fails the session, as Netezza may not be able to serialize execution of queries.

OR

Could not serialize - transaction aborted

CAUSE :

Data is being inserted or updated in same target table from different instances or sessions.

For Ex.

Target Update Instance 1 is running below command:- 

UPDATE SET ROW1 FROM TABLE A

Target Update Instance 2 is running below command :-

UPDATE SET ROW2 FROM TABLE A

OR 

Target Insert Instance  is running below command:- 

INSET ROW 3 FROM TABLE A

Target Update Instance  is running below command :-

UPDATE SET ROW2 FROM TABLE A

This would cause serialization error in Netezza target

WORKAROUND :

1. If you have primary Key defined on target table, Check if "Ignore Key Constraint" is CHECKED for all instance in Session Level properties.

2. Try using Relational Writer instead of Bulk writer. This will cause performance impact.

3. If we have multiple instance of Updates or Inserts. Try creating separate pipeline for each target instance.