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

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

satyender — Thu, 09 Mar 2023 05:23:50 GMT

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

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

john_wang — Thu, 09 Mar 2023 06:32:51 GMT

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.

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

satyender — Fri, 10 Mar 2023 16:13:33 GMT

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

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

satyender — Fri, 10 Mar 2023 16:25:10 GMT

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

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

shashi_holla — Fri, 10 Mar 2023 16:27:07 GMT

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,

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

satyender — Fri, 10 Mar 2023 17:57:04 GMT

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')'

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

shashi_holla — Fri, 10 Mar 2023 18:27:09 GMT

@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,

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

satyender — Fri, 10 Mar 2023 19:00:14 GMT

@shashi_holla ,

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

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

shashi_holla — Fri, 10 Mar 2023 19:20:14 GMT

@satyender

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

Thank you,

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

satyender — Fri, 10 Mar 2023 20:03:15 GMT

Sure.

Thank you @shashi_holla , @john_wang for your support.

-Satyender

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

Barb_Fill21 — Sun, 26 Mar 2023 18:42:29 GMT

Hello @satyender

Not sure if this will help.

. Open the table from the bottom right of the Designer screen. Select Transform. On the screen that opens, click the Expression field for the column. Enter: CASE $ColumnName WHEN null Then ' ' Else $ColumnName END Actually this might work just as well: ifnull($ColumnName,' ')

Please try and let me know.

Thanks,

Barb

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

Heinvandenheuvel — Sun, 26 Mar 2023 21:32:34 GMT

Well, it reports 'null result', and source and target are numeric (some encoding for a date like YYMMDD perhaps).

Is it possibly that some IBM 'numerics' are not recognizable as numeric by other like 'all spaces' an 'overpunch' or trailing vs leading sign? in that case the ibm source would not have a null as such, but something which cannot map to a number on an other platform. Can you 'keep' or copy the CSV and study that instead of the source? It my help you identify the source row triggering the error.

File '452' suggests many have rows been successfully loaded, is that true?

The same error appears to repeat itself. You did not include the timestamp(s) are they rapid-fire or multiple seconds apart suggesting a forever failing retry?

Low 1 could be a sloppy error message, or it is the first row in the first file and nothing was ever loaded?

Maybe a transformation such as @Barb_Fill21 suggests can help but considering it is a numeric column instead of '' I'd suggest a recognizable number like 987654, 123456, 424242 - if you catch my drift. Then after the load (if it now completes) search for those rows on target and then the corresponding rows on source.

hth,

Hein.

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

saikrishna8 — Fri, 14 Jul 2023 18:07:12 GMT

Hi @satyender We are having similar issue for oracle as source, what was the outcome from the tread you discussed with QLIK PS. Any inputs would help us resolve the issue.

Thank you,

SK Pyata

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

john_wang — Sat, 15 Jul 2023 03:18:13 GMT

Hello @saikrishna8 ,

Many reasons may lead to the same error in Snowflake during Full Load stage. In most cases please try to add an internal parameter ,emptyFieldAsNull , and set it to TRUE, then Reload the task(or table) to see if it works for you.

Hope this helps.

Regards,

John.

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

Barb_Fill21 — Fri, 18 Aug 2023 19:01:56 GMT

Hi @satyender

As this is a DB2 iSeries source, please ask the DB2 DBA to make sure you have *BOTH set for the journaling of the tables. And depending on the target and version of Replicate it may be an issue that is already fixed.

Sincerely

Barb

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

harsh2 — Sat, 18 Nov 2023 06:09:01 GMT

hey hi @satyender

I am having the same issue, so could you please tell me what conclusion you get in the end? It would be helpful for me.

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

suvbin — Sun, 03 Dec 2023 05:51:42 GMT

@satyender

Am also facing similar issue. Source is SAP Hana , Target is Snowflake. How to know exactly in which column the error would be?

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

harsh2 — Mon, 04 Dec 2023 11:06:49 GMT

Hi suvbin,

refer to following community's link this might help

link : https://community.qlik.com/t5/Qlik-Replicate/Data-type-issue-in-Qlik-replicateTask/m-p/2142942#M8304

Regards,

Harsh

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

suvbin — Wed, 28 Feb 2024 03:16:57 GMT

@john_wang Am also facing the same issue, could you please provide the script for the same .

Thanks

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

john_wang — Wed, 28 Feb 2024 09:48:05 GMT

Hello @suvbin ,

Are you able to share the source and target DBs types? it's better with a table sample (obfuscating sensitive information as needed).

thanks,

John.