Error encounter when trying to load data into IBM target endpoint.

desmondchew — Thu, 29 Aug 2024 06:31:39 GMT

We have a new task on replicate server 2024.5.0.144.

Source database is IBM DB2. Version is 11.5.7.0
Target database is IBM DB2. Version is 11.5.7.0
The server OS of Qlik replicate is windows Server 2022 Version 21H2 (OS building 20348.2527).

We encountered following error while trying to replicate to the target endpoint.

execute statement failed
RetCode: SQL_ERROR SqlState: 22001 NativeError: -302 Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use. SQLSTATE=22001

Failed (retcode -1) to execute statement: 'INSERT INTO "P_MD"."VESSEL"("ID","CHANGEDATE","CHANGEDBYUSER","CREATEDBYUSER","CREATIONDATE","RECORDVERSION","AUTOMATICTWISTLOCK","BERTHINGSIDE","CHARACTERISTIC","CODE","DESCRIPTIONUUID","EFFECTIVITYFROM","EFFECTIVITYUNTIL","FLAGNUMBER","GAUGE","GROSSREGISTERTON","IMOHULLNUMBER","LENGTH_","MAXIMUMREGISTEREDDRAFT","MINIMUMSPACELAG","MINIMUMTIMELAG","NUMBEROFCRANES","PORTAUTHORITYVESSELNUMBER","POSITIONNOMENCLATURE","REMARK","TEUSIZE","TYPE_","USABLELENGTH","VESSELOPERATOR","CALLSIGN","DESCRIPTION","VESSELNAME","MOULDEDDEPTH") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'

8/8/2024 20:02	WARNING_GENERAL	Allocated 250 ODBC statement handles
8/8/2024 20:02	WARNING_GENERAL	Allocated 200 ODBC statement handles
8/8/2024 20:02	WARNING_GENERAL	Allocated 150 ODBC statement handles
8/8/2024 18:30	WARNING_GENERAL	Task 'GTOS_PRD_TO_RP' encountered a recoverable error
8/8/2024 18:30	ERROR_GENERAL	Stream component 'st_0_GTOS_PRD' terminated Stream component failed at subtask 0, component st_0_GTOS_PRD Error executing source loop
8/8/2024 18:30	ERROR_GENERAL	ReadNextBufferWait: Error reading buffer Error at 'Reading log records': A communication error has been detected. Original SQLCODE -30081: ' message SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "10.20.201.100". Communication function detecting the error: "recv". Protocol specific error code(s): "", "", "0". SQLSTATE=08001 ., SQLSTATE SQLSTATE 08001: The connection was unable to be established to the application server or other server. '
8/8/2024 18:29	WARNING_GENERAL	Task 'GTOS_PRD_TO_RP' encountered a recoverable error
8/8/2024 18:29	ERROR_GENERAL	Stream component 'st_0_GTOS_PRD' terminated Stream component failed at subtask 0, component st_0_GTOS_PRD Error executing source loop
8/8/2024 18:29	ERROR_GENERAL	ReadNextBufferWait: Error reading buffer Error at 'Reading log records': A communication error has been detected. Original SQLCODE -30081: ' message SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "10.20.201.100". Communication function detecting the error: "recv". Protocol specific error code(s): "", "", "0". SQLSTATE=08001 ., SQLSTATE SQLSTATE 08001: The connection was unable to be established to the application server or other server. '
8/8/2024 16:29	WARNING_GENERAL	Task 'GTOS_PRD_TO_RP' encountered a fatal error
8/8/2024 16:29	ERROR_GENERAL	Stream component 'st_0_GTOS_PRD_RP' terminated Cannot initialize subtask Failed while preparing stream component 'st_0_GTOS_PRD_RP'. The "Explicit alternate schema" option is not allowed when "Allow unlimited LOB size" is enabled in the task settings
8/8/2024 16:28	WARNING_GENERAL	As "Allow unlimited LOB size" is enabled in the task settings, tables with LOB columns will be applied using "Transactional apply" mode, even though "Batch optimized apply" mode is configured in the task settings.
8/8/2024 16:06	WARNING_GENERAL	Table 'P_OPS'.'UNITHANDLING' (subtask 1 thread 1) is suspended. Failed (retcode -1) to execute statement: 'INSERT INTO "P_OPS"."UNITHANDLING"("ID","CHANGEDATE","CHANGEDBYUSER","CREATEDBYUSER","CREATIONDATE","RECORDVERSION","ACHECKOK","ADJACENTTERMINAL","AGENT","DEMANDREFERENCE","DEMANDTYPE","DISCHARGEPOSITION","DOCUMENTSAVAILABLE","EXECUTIONAPPROVED","EXECUTIONDATETIME","EXPECTEDEXECUTIONDATETIME","HANDLINGSEQUENCE","LATESTACHECKDATETIME","LATESTEXECUTEAPPROVALDATETIME","LATESTLINKDATETIME","LATESTPCHECKDATETIME","LINE","LINKOK","LOADPOSITION","LOADSTATUS","OPERATIONTYPE","ORDERMISMATCH","ORDERTYPE","PCHECKOK","RETRIEVEDBESTUNIT","SEQUENCE_","STATUS","SUBTYPE","TAMSPRENOTIFICATIONID","TERMINAL","THIRDMOVEINDICATION","TYPE_","POOL","CALLID","PLANNINGRESULTID","QUAYOFFICEDECLARATIONID","UNITREGISTRATIONID","UNITLINKID","UNITVISITID","DIRECTIVEID","PRENOTIFICATIONID","RAILCARREGISTRATIONID","RLSINSTRUCTIONLINEID","UNITDEMANDID","INDEMANDID","OUTDEMANDID","SPECIALDEMANDID","SHIFTDEMANDID","HANDLINGREFERENCE","RAI
8/8/2024 16:06	ERROR_GENERAL	execute statement failed RetCode: SQL_ERROR SqlState: 22001 NativeError: -302 Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use. SQLSTATE=22001 Failed (retcode -1) to execute statement: 'INSERT INTO "P_OPS"."UNITHANDLING"("ID","CHANGEDATE","CHANGEDBYUSER","CREATEDBYUSER","CREATIONDATE","RECORDVERSION","ACHECKOK","ADJACENTTERMINAL","AGENT","DEMANDREFERENCE","DEMANDTYPE","DISCHARGEPOSITION","DOCUMENTSAVAILABLE","EXECUTIONAPPROVED","EXECUTIONDATETIME","EXPECTEDEXECUTIONDATETIME","HANDLINGSEQUENCE","LATESTACHECKDATETIME","LATESTEXECUTEAPPROVALDATETIME","LATESTLINKDATETIME","LATESTPCHECKDATETIME","LINE","LINKOK","LOADPOSITION","LOADSTATUS","OPERATIONTYPE","ORDERMISMATCH","ORDERTYPE","PCHECKOK","RETRIEVEDBESTUNIT","SEQUENCE_","STATUS","SUBTYPE","TAMSPRENOTIFICATIONID","TERMINAL","THIRDMOVEINDICATION","TYPE_","POOL","CALLID","PLANNINGRESULTID","QUAYOFFICEDECLARATIONID","UNITREGISTRATIONID","UNITLINKID","UNITVISITID","DIRECTIVEID","PRENOTIFICATIONID","RAILCARREGISTRATIONID","RLSINSTRUCTIONLINEID","UNITDEMANDID","INDEMANDID","OUTDEMANDID","SPECIALDEMANDID","SHIFTDEMANDID","HANDLINGREFERENCE","RAILCARNUMBER","ORDERID","PINENTRYATTEMPS","REPORTEDPLANNINGRESULTID","EXECUTEDMODIFIEDDATETIME","EXTERNALORDERREFERENCE","PREMOVEUNITVISITID","ACHECKPENDING","VATINDICATOR","PLANNEDSHIPSPOSITIONID","LOCKED","CUTOFFDIFFERENCE","HANDLINGDATETIME","ACTIVEVERIFIEDGROSSMASSID","OTHERLINEINDICATION","IMPORTEXPORTINDICATION","DOCUMENTMODIFIEDDATETIME","RAILCARSEQUENCE","UC_UNITID","INORDERINDICATION","EL_ID","HANDLINGDATETIMEMANUALINDICATION","GATEZONE","TRANSPORTERID","OCRAPPLYSTATUS","ORDEREXISTINDICATION","EXTERNALREFERENCE","BOOKINGREFERENCE","OPR_OPERATIONALBLOCKED","OPR_ADMINBLOCKED","OPR_CUSTOMSBLOCKED","OPR_OPERATIONALBLOCKREASON","OPR_ADMINBLOCKREASON","OPR_CUSTOMSBLOCKREASON","OPR_OPERATIONALBLOCKCODE","OPR_CUSTOMSBLOCKCODE","RBUONNUMBER","TERMINALLOCATIONDIRECTIVE","CASHINDICATOR","STORAGELOCATION") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
8/8/2024 16:06	WARNING_GENERAL	Table 'P_OPS'.'OPS_VESSELAPPOINTMENT' (subtask 5 thread 1) is suspended. Failed (retcode -1) to execute statement: 'INSERT INTO "P_OPS"."OPS_VESSELAPPOINTMENT"("ID","CHANGEDATE","CHANGEDBYUSER","CREATEDBYUSER","CREATIONDATE","RECORDVERSION","CARRIEROPERATOR","ORDERPORTCHECK","SHOWEXTERNAL","STATUS","TERMINAL","FOREIGNQUAYCODE","FOREIGNQUAYTERMINAL","FOREIGNQUAYCOUNTRY","REGISTRYNUMBER","VESSEL","TERMINALOPERATOR","VOYAGE_IN_ID","VOYAGE_OUT_ID","ATE_CUTEDISCSTARTDT","ATE_CUTEDISCPARTIALSTARTDT","ATE_CUTIDISCSTARTDT","ATE_CUTIDISCPARTIALSTARTDT","ATE_CUTELOADSTARTDT","ATE_CUTELOADPARTIALSTARTDT","ATE_CUTILOADSTARTDT","ATE_CUTILOADPARTIALSTARTDT","ATE_TRFOL","ATE_ATA","ATE_ATD","ATE_ETA","ATE_ETD","V_IN_VOYAGE","V_IN_SERVICE","V_IN_COASTALINDICATION","V_OUT_VOYAGE","V_OUT_SERVICE","V_OUT_COASTALINDICATION","VESSELDESCRIPTION","DEFAULTGATEZONEDIS","DEFAULTGATEZONELD","PLANNEDGATEZONEDIS","PLANNEDGATEZONELD","REQUESTREFERENCENUMBER","VESSELVOYAGETYPE","REQUESTEDMOORINGSIDE","ATE_BTR","DGINDICATION","PLANNEDINBAS",
8/8/2024 16:06	ERROR_GENERAL	execute statement failed RetCode: SQL_ERROR SqlState: 22001 NativeError: -302 Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use. SQLSTATE=22001 Failed (retcode -1) to execute statement: 'INSERT INTO "P_OPS"."OPS_VESSELAPPOINTMENT"("ID","CHANGEDATE","CHANGEDBYUSER","CREATEDBYUSER","CREATIONDATE","RECORDVERSION","CARRIEROPERATOR","ORDERPORTCHECK","SHOWEXTERNAL","STATUS","TERMINAL","FOREIGNQUAYCODE","FOREIGNQUAYTERMINAL","FOREIGNQUAYCOUNTRY","REGISTRYNUMBER","VESSEL","TERMINALOPERATOR","VOYAGE_IN_ID","VOYAGE_OUT_ID","ATE_CUTEDISCSTARTDT","ATE_CUTEDISCPARTIALSTARTDT","ATE_CUTIDISCSTARTDT","ATE_CUTIDISCPARTIALSTARTDT","ATE_CUTELOADSTARTDT","ATE_CUTELOADPARTIALSTARTDT","ATE_CUTILOADSTARTDT","ATE_CUTILOADPARTIALSTARTDT","ATE_TRFOL","ATE_ATA","ATE_ATD","ATE_ETA","ATE_ETD","V_IN_VOYAGE","V_IN_SERVICE","V_IN_COASTALINDICATION","V_OUT_VOYAGE","V_OUT_SERVICE","V_OUT_COASTALINDICATION","VESSELDESCRIPTION","DEFAULTGATEZONEDIS","DEFAULTGATEZONELD","PLANNEDGATEZONEDIS","PLANNEDGATEZONELD","REQUESTREFERENCENUMBER","VESSELVOYAGETYPE","REQUESTEDMOORINGSIDE","ATE_BTR","DGINDICATION","PLANNEDINBAS","INDIRECTCARRIERTYPE","COMINGFROM","GOINGTO","ATE_DELETEREQUESTINDICATOR","BERTH","SHIPAGENT","CUSTOMSCODEIN","CUSTOMSCODEOUT","VESSELNAME","PORTROTATIONNUMBER") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
8/8/2024 16:06	WARNING_GENERAL	Table 'P_MD'.'VESSEL' (subtask 1 thread 1) is suspended. Failed (retcode -1) to execute statement: 'INSERT INTO "P_MD"."VESSEL"("ID","CHANGEDATE","CHANGEDBYUSER","CREATEDBYUSER","CREATIONDATE","RECORDVERSION","AUTOMATICTWISTLOCK","BERTHINGSIDE","CHARACTERISTIC","CODE","DESCRIPTIONUUID","EFFECTIVITYFROM","EFFECTIVITYUNTIL","FLAGNUMBER","GAUGE","GROSSREGISTERTON","IMOHULLNUMBER","LENGTH_","MAXIMUMREGISTEREDDRAFT","MINIMUMSPACELAG","MINIMUMTIMELAG","NUMBEROFCRANES","PORTAUTHORITYVESSELNUMBER","POSITIONNOMENCLATURE","REMARK","TEUSIZE","TYPE_","USABLELENGTH","VESSELOPERATOR","CALLSIGN","DESCRIPTION","VESSELNAME","MOULDEDDEPTH") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'; RetCode: SQL_ERROR SqlState: 22001 NativeError: -302 Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use. SQLSTATE=22001 ; execute statement failed
8/8/2024 16:06	ERROR_GENERAL	execute statement failed RetCode: SQL_ERROR SqlState: 22001 NativeError: -302 Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use. SQLSTATE=22001 Failed (retcode -1) to execute statement: 'INSERT INTO "P_MD"."VESSEL"("ID","CHANGEDATE","CHANGEDBYUSER","CREATEDBYUSER","CREATIONDATE","RECORDVERSION","AUTOMATICTWISTLOCK","BERTHINGSIDE","CHARACTERISTIC","CODE","DESCRIPTIONUUID","EFFECTIVITYFROM","EFFECTIVITYUNTIL","FLAGNUMBER","GAUGE","GROSSREGISTERTON","IMOHULLNUMBER","LENGTH_","MAXIMUMREGISTEREDDRAFT","MINIMUMSPACELAG","MINIMUMTIMELAG","NUMBEROFCRANES","PORTAUTHORITYVESSELNUMBER","POSITIONNOMENCLATURE","REMARK","TEUSIZE","TYPE_","USABLELENGTH","VESSELOPERATOR","CALLSIGN","DESCRIPTION","VESSELNAME","MOULDEDDEPTH") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'

Re: Error encounter when trying to load data into IBM target endpoint.

OritA — Thu, 29 Aug 2024 07:42:22 GMT

Hi ,

Please open a salesforce case for your issue and attach to the case the task diagnostic pakcage and the task log with the error for further troubleshooting.

Thanks & regards,

Orit

Re: Error encounter when trying to load data into IBM target endpoint.

desmondchew — Thu, 29 Aug 2024 07:53:44 GMT

Got it. Opened a new ticket.

Thanks

Re: Error encounter when trying to load data into IBM target endpoint.

sureshkumar — Thu, 29 Aug 2024 09:36:07 GMT

Hello @desmondchew

Thanks for opening a support ticket.

Additional information like the Error looks to be possible issue with a host variable and the length between source and target columns.

Compare Source DDL and Target DDL and check is there any mismatch in the datatype.

Regards,
Suresh

Re: Error encounter when trying to load data into IBM target endpoint.

DesmondWOO — Thu, 29 Aug 2024 09:40:33 GMT

Hi @desmondchew ,

Please change the processing mode to 'Transactional Apply' and enable verbose logging on the TARGET_APPLY. Once the error is reproduced, you will find the complete INSERT statement in the task log. You can then run the statement manually to see if you can reproduce the same error.

If the same error can be reproduced, please check the length, collation, and other properties of your table and database.

Regards,
Desmond

topic Error encounter when trying to load data into IBM target endpoint. in Qlik Replicate

Error encounter when trying to load data into IBM target endpoint.

Re: Error encounter when trying to load data into IBM target endpoint.

Re: Error encounter when trying to load data into IBM target endpoint.

Re: Error encounter when trying to load data into IBM target endpoint.

Re: Error encounter when trying to load data into IBM target endpoint.