ERROR from ExecDirect: ERROR: Attribute 'Col_Name' is of type 'NUMERIC' but expression is of type 'DATE' You will need to rewrite or cast the expression

 As obvious it seems, but this error might come if you are using Insert/Update then Insert in Netezza Enterprise Stage. In the above error, the column used was Nullable but still the job shows error. This is because in Netezza Enterprise stage, we need to have the entire metadata defined in the target even if the columns defined are not getting any values from source and they have been defined as NULLABLE.

Update in Netezza Enterprise stage

Doing update or update then insert requires the use of unique key column in Input->Properties tab.

The following needs to be configured in Input->Properties tab :

Load Method = NZLOAD

Table_Name = ‘give your table name’

Update Mode = Update or Update Then Insert

Drop Work Table = ‘True’ (Usually we don’t create a work table for load. But if you have a work table, you can specify it here.)

Key Column = Specify Key Column

(You can add any number of Key column. Just add one Key Column property for each column)

Unique Key Column :

This column can be a sequential number which is generated and may or may not be present in the target table. Its ok even if this column is not present in the target table and still appears in

Target table metadata in datastage.

Usually we generate it in a transformer with some column like SEQ_SURR, and assign a value = @PARTITIONNUM+(@NUMPARTITIONS*(@INROWNUM-1))+1

The requisite is that it should have a unique value for each row coming from source.

Updatable column :

If you want the table to get updated values for all the columns for the specified key combination, then don’t select this property. Else if you want only some of your columns to be updated,

Then select this property, one for each column you want to update.

Write Method = Update

Connection :

Provide the connection details that are required for the properties

Datasource, Database, Password, Server and User

That’s it! That’s the minmum steps you require to configure updates for Netezza Enterprise stage.

Info : Use of WITH Keyword in SQL query

One of the easiest and powerful keywords I found in SQL, for debugging or for writing complex queries is the WITH keyword. Using this we can define multiple blocks  and perform

different operations between the defined blocks and store the result in a different block. It should be clear with the below query. Here I have defined

different blocks as SRC,TGT, JN, SRC1, SRC2, EXTRA, MISSING.

(In the below SQL I am trying to validate some of the column values for validity. The values in Target have been loaded from the ETL job. So I am generating the source records using the different operations as was required by business. Here I will be joining the source table : table_1 with a reference table table_2 for a particular batch. The result obtained will be further joined with table_3 to fetch additional columns required. Finally the generated values from source are compared with the target if any EXTRA or MISSING records are there.)

The different functions performed by the blocks are :

SRC – Extracts result of table_1 for a particular batch. (Source table)

TGT – Extracts result of table_2 for a particular batch.

JN – Joins results of SRC and TGT and puts some filter.

SRC1 – Joins result of JN with table_3.

TGT1 – Extracts result of table_4 (target table).

EXTRA – finds records which are in Target but not in Source.

MISSING – finds records which are in Source but not in target.

WITH SRC AS

(SELECT DATEIG,VENDOR_NUM,POSEQNC_NUM,WKSTSQC_NUM,CONCAT(VENDOR_NUM,POSEQNC_NUM) AS PO_NUM FROM table_1 WHERE

BATCH_TS = TIMESTAMP'2012-11-25 04:01:00')

,TGT AS

(

SELECT DATEDOCK,VENDOR_NUM,POSEQNC_NUM,WKSTSQC_NUM FROM table_2 WHERE

BATCH_TS = TIMESTAMP'2012-11-25 04:01:00'

),

JN AS

(

SELECT CASE WHEN DATEIG IS NULL THEN DATEDOCK ELSE DATEIG END AS IN_GATE_DATE FROM SRC LEFT JOIN TGT ON

SRC.VENDOR_NUM = TGT.VENDOR_NUM AND

SRC.POSEQNC_NUM = TGT.POSEQNC_NUM AND

SRC.WKSTSQC_NUM = TGT.WKSTSQC_NUM

--WHERE PO_NUM = '15046327' AND SRC.WKSTSQC_NUM IN ('0001','0005','0009','0013','0017','0002','0006')

)

,SRC1 AS

(

SELECT

IN_GATE_DATE,DAY_ID AS IN_GATE_DATE_ID FROM JN LEFT OUTER JOIN table_3 ON

IN_GATE_DATE = DAY_DATE

)

,TGT1 AS

(SELECT IN_GATE_DATE,IN_GATE_DATE_ID,'TARGET' AS DESCR FROM table_4

--WHERE PURCHASE_ORDER_NUM = '15046327' AND WORKSHEET_NUM IN ('0001','0005','0009','0013','0017','0002','0006')

)

,EXTRA AS

(SELECT IN_GATE_DATE,IN_GATE_DATE_ID FROM TGT1 MINUS SELECT IN_GATE_DATE,IN_GATE_DATE_ID FROM SRC1)

,

MISSING AS

(SELECT IN_GATE_DATE,IN_GATE_DATE_ID FROM SRC1 MINUS SELECT IN_GATE_DATE,IN_GATE_DATE_ID FROM TGT1)

SELECT IN_GATE_DATE,IN_GATE_DATE_ID,'EXTRA' AS DESCR FROM EXTRA

UNION

SELECT IN_GATE_DATE,IN_GATE_DATE_ID,'MISSING' AS DESCR FROM MISSING

Warning : The OCI function OCIStmtExecute returned status 1. Error code: 24,381, Error message: ORA-24381: error(s) in array DML.

The OCI function OCIStmtExecute returned status 1. Error code: 24,381, Error message: ORA-24381: error(s) in array DML.


This warning is obtained usually when there is a problem in the number of records getting loaded in the array. For this we need to get the correct balance between the record count and array size property.

The record count should be a multiple of the value for array size. For one of the projects we set the value of record count as 10000 and array size as 2000 and the warning was gone.

Fatal Error: Attempt to setIsNull() on the accessor interfacing to non-nullable field "column_name".

Fatal Error: Attempt to setIsNull() on the accessor interfacing to non-nullable field "column_name".


As evident this error can occur when you are trying to assign a NULL value to a NOT NULLABLE column. But this can be a little tricky sometimes.

Like if you use trim(col_name) where col_name is a NOT NULLABLE column. Still it can have a NULL value as trim(Blank Spaces) in oracle gives NULL.

So, if you are using a trim in the query and the column is defined NOT NULL, beware that it can give the above error in case blank values(which are in fact NOT NULL) come in that

Column.

You can resolve it  by defining the column as NOT NULL, or remove the trim from the query and use the trim in a transformer.

Warning : When checking operator: When binding input interface field ... to field ...: Implicit conversion from source type "int64" to result type "decimal[18,0]": Possible range limitation.

Warning : When checking operator: When binding input interface field "field_name" to field "field_name": Implicit conversion from source type "int64" to result type "decimal[18,0]": Possible range limitation.

It means you are trying to convert a bigint to decimal which requires a explicit treatment. Use the AsInteger() function, or assign the column to a stage variable and then use the stage variable to populate the column in the target.

Warning : When checking operator: When validating import schema: At field "field_Name": Importing potential null to not nullable field

When checking operator: When validating import schema: At field "field_name": Importing potential null to not nullable field 

while using Netezza Enterprise stage.


 In Netezza Enterprise stage sometimes even if the column is defined not null in metadata, still the above warning can be seen. To remove this use NVL function.

e.g I have a column PURCHASE_ORDER_NUM VARCHAR(20) (NOT NULL). Use the nvl function in the query as NVL(PURCHASE_ORDER_NUM,’?’) as PURCHASE_ORDER_NUM and convert its nullability to YES in the metadata.

Since the column is defined NOT NULL in source we should not have any ‘?’ actually coming in the data.

Now since here we have converted the nullablity of the column to NULLABLE, we might have the column defined as NOT NULLABLE in the target. In such as a case you might have to use a transformer or a modify stage to convert the NULLABLITY of the column to NOT NULLABLE and define a rule like :

If(IsNull(link_name.PURCHASE_ORDER_NUM)) Then ‘?’ Else link_name.PURCHASE_ORDER_NUM

To use NVL for decimal or integer field, give a integer decimal value.

e.g if the column PURCHASE_ORDER_NUM was an integer or decimal column I would have used :

NVL(PURCHASE_ORDER_NUM,’-1’) AS PURCHASE_ORDER_NUM