Data load error using Qlik Replicate - NULL result in a non-nullable column

[satyender]

Greetings!

I am encountering an error while loading the data from DB2. Any code/expression to fix this?

Thanks!

Error:

NULL result in a non-nullable column

[john_wang]

Hello @satyender ,

Thanks for reaching out.

Looks to me there are 3 proposals:

1- Check the source side why a column captured NULL values, if it's reasonable or not;

2- Check the target side why a column was defined with "non-nullable" property; if can be changed to "nullable"

3- If both of above cannot fix the issue, then use transformation to replace the NULL by a given flag, eg empty string, or a specific string.

Hope this helps.

Regards,

John.

[satyender]

Hi @john_wang,

Thank your for your response. 

The Source table has all NOT NULL Columns and at Target table was created as is by the Qlik Replicate. 

Looks like the empty string or spaces are in the Source table.

I will try the transformation. 

Could you please provide me the script to replace the NULLs with Empty string? 

Thanks!

Satyender

[satyender]

Hello @john_wang ,

I have checked the Source table and it does not have any NULL values in the column as the column is PK.

Any idea why this error while loading? 

Thanks!

Satyender

[shashi_holla]

Hi @satyender 

Could you please set the logging for source_unload and target)load to verbose and check if we are capturing data for all 4 columns in the table. Screenshot doesn't show the full COPY command that was run. 

Thanks,

[satyender]

Hi @shashi_holla,

Thanks for your response.

Currently, there job window is busy, I have to run this job only in the evenings. 

Herein is the complete script of the log from screenshot.

Handling End of table 'HCSSLIB'.'COMMIFSBK' loading failed by subtask 4 thread 1
Failed to copy data of file D:\Attunity\Data\tasks\P_TSK_DC_SON400_BACKUP\cloud\542\LOAD00000001.csv to database
Failed to load HCSSLIB.COMMIFSBK from stage, file name: LOAD00000001.csv
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column
File '542/LOAD00000001.csv.gz', line 1, character 1
Row 1, column "COMMIFSBK"["SRCDATE":2]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: 'COPY INTO "HCSSLIB"."COMMIFSBK"("SEQUENCE", "SRCDATE", "SRCDATA", "RRN") FROM (select $1, $2, $3, $4 FROM '@"PROD_SON400_BACKUP_DB"."PUBLIC"."ATTREP_IS_PROD_SON400_BACKUP_DB_9b2a9b15_08c6_c540_9dfa_f21f3b4a1a8e"/542/') files = ('LOAD00000001.csv.gz')'
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column
File '542/LOAD00000001.csv.gz', line 1, character 1
Row 1, column "COMMIFSBK"["SRCDATE":2]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: 'COPY INTO "HCSSLIB"."COMMIFSBK"("SEQUENCE", "SRCDATE", "SRCDATA", "RRN") FROM (select $1, $2, $3, $4 FROM '@"PROD_SON400_BACKUP_DB"."PUBLIC"."ATTREP_IS_PROD_SON400_BACKUP_DB_9b2a9b15_08c6_c540_9dfa_f21f3b4a1a8e"/542/') files = ('LOAD00000001.csv.gz')'
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column
File '542/LOAD00000001.csv.gz', line 1, character 1
Row 1, column "COMMIFSBK"["SRCDATE":2]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: 'COPY INTO "HCSSLIB"."COMMIFSBK"("SEQUENCE", "SRCDATE", "SRCDATA", "RRN") FROM (select $1, $2, $3, $4 FROM '@"PROD_SON400_BACKUP_DB"."PUBLIC"."ATTREP_IS_PROD_SON400_BACKUP_DB_9b2a9b15_08c6_c540_9dfa_f21f3b4a1a8e"/542/') files = ('LOAD00000001.csv.gz')'
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column
File '542/LOAD00000001.csv.gz', line 1, character 1
Row 1, column "COMMIFSBK"["SRCDATE":2]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: 'COPY INTO "HCSSLIB"."COMMIFSBK"("SEQUENCE", "SRCDATE", "SRCDATA", "RRN") FROM (select $1, $2, $3, $4 FROM '@"PROD_SON400_BACKUP_DB"."PUBLIC"."ATTREP_IS_PROD_SON400_BACKUP_DB_9b2a9b15_08c6_c540_9dfa_f21f3b4a1a8e"/542/') files = ('LOAD00000001.csv.gz')'
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column
File '542/LOAD00000001.csv.gz', line 1, character 1
Row 1, column "COMMIFSBK"["SRCDATE":2]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: 'COPY INTO "HCSSLIB"."COMMIFSBK"("SEQUENCE", "SRCDATE", "SRCDATA", "RRN") FROM (select $1, $2, $3, $4 FROM '@"PROD_SON400_BACKUP_DB"."PUBLIC"."ATTREP_IS_PROD_SON400_BACKUP_DB_9b2a9b15_08c6_c540_9dfa_f21f3b4a1a8e"/542/') files = ('LOAD00000001.csv.gz')'
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column
File '542/LOAD00000001.csv.gz', line 1, character 1
Row 1, column "COMMIFSBK"["SRCDATE":2]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: 'COPY INTO "HCSSLIB"."COMMIFSBK"("SEQUENCE", "SRCDATE", "SRCDATA", "RRN") FROM (select $1, $2, $3, $4 FROM '@"PROD_SON400_BACKUP_DB"."PUBLIC"."ATTREP_IS_PROD_SON400_BACKUP_DB_9b2a9b15_08c6_c540_9dfa_f21f3b4a1a8e"/542/') files = ('LOAD00000001.csv.gz')'

[shashi_holla]

@satyender 

Please let me know if you have any transformation for the column COMMIFSBK.SRCDATE. Looks like it's unable to fetch the date value correctly.

File '542/LOAD00000001.csv.gz', line 1, character 1
Row 1, column "COMMIFSBK"["SRCDATE":2]

Thank you,

[satyender]

@shashi_holla ,

There is no transformation for the column SRCDATE.  It is just REGULAR which is empty inside the 'fx' .

[shashi_holla]

@satyender 

Please open a support case, let's take a closer look on a web session.

Thank you,

[satyender]

Sure.

Thank you  @shashi_holla ,  @john_wang for your support.

-Satyender