none
Copy activity fails when copying from DB2 source

    Question

  • Hi, 

    I am trying to copy data from a DB2 source. I used 'SELECT Col1, Col2, Col3.... FROM Table1' query and it fails with error message :

    Activity Copy F4211 From E1 failed: Failure happened on 'Source' side. 'Type=Microsoft.HostIntegration.DrdaClient.DrdaException,Message=HISMPCB0014 In BasePrimitiveConverter an invalid packed decimal value was encountered. SQLSTATE=HY000 SQLCODE=-343,Source=Microsoft.HostIntegration.Connectors,'

    I've checked and found that the data source does contain 'packed decimals'.

    However when I use 'SELECT * FROM Table1' ....It works just fine.

    Could anyone please help understand this.

    Thanks.

    • Moved by Sandeep BR Thursday, August 23, 2018 8:20 PM better suited here,Moved from azure storage to azure data factory
    Thursday, August 23, 2018 2:03 PM

All replies

  • Can you elaborate the scenario for better understanding? How exactly you are copying? Are you using Azure Data factory for this? Please confirm, are you copying from DB2 source to table or any other scenario?

    Thursday, August 23, 2018 6:03 PM
  • Yes, I'm using Azure Data Factory V2 (copy activity) for copying from DB2 source to Data Lake.

    Pipeline JSON : The query shown here fails. If I replace it with 'SELECT * FROM TableName' it works.

    {
    "name": "Load_F4211",
    "properties": {
    "activities": [
    {
    "name": "Copy F4211 From E1",
    "type": "Copy",
    "policy": {
    "timeout": "7.00:00:00",
    "retry": 0,
    "retryIntervalInSeconds": 30,
    "secureOutput": false
    },
    "typeProperties": {
    "source": {
    "type": "RelationalSource",
    "query": "select \nTRIM(SDKCOO) AS SDKCOO,\nSDDOCO,\nTRIM(SDDCTO) AS SDDCTO,\nSDLNID,\nTRIM(SDSFXO) AS SDSFXO,\nTRIM(SDMCU) AS SDMCU,\nTRIM(SDCO) AS SDCO,\nTRIM(SDOKCO) AS SDOKCO,\nTRIM(SDOORN) AS SDOORN,\nTRIM(SDOCTO) AS SDOCTO,\nSDOGNO,\nTRIM(SDRKCO) AS SDRKCO,\nTRIM(SDRORN) AS SDRORN,\nTRIM(SDRCTO) AS SDRCTO,\nSDRLLN,\nTRIM(SDDMCT) AS SDDMCT,\nSDDMCS,\nSDAN8,\nSDSHAN,\nSDPA8,\nSDDRQJ,\nSDTRDJ,\nSDPDDJ,\nSDADDJ,\nSDIVD,\nSDCNDJ,\nSDDGL,\nSDRSDJ,\nSDPEFJ,\nSDPPDJ,\nTRIM(SDVR01) AS SDVR01,\nTRIM(SDVR02) AS SDVR02,\nSDITM,\nTRIM(SDLITM) AS SDLITM,\nTRIM(SDAITM) AS SDAITM,\nTRIM(SDLOCN) AS SDLOCN,\nTRIM(SDLOTN) AS SDLOTN,\nTRIM(SDFRGD) AS SDFRGD,\nTRIM(SDTHGD) AS SDTHGD,\nSDFRMP,\nSDTHRP,\nSDEXDP,\nTRIM(SDDSC1) AS SDDSC1,\nTRIM(SDDSC2) AS SDDSC2,\nTRIM(SDLNTY) AS SDLNTY,\nTRIM(SDNXTR) AS SDNXTR,\nTRIM(SDLTTR) AS SDLTTR,\nTRIM(SDEMCU) AS SDEMCU,\nTRIM(SDRLIT) AS SDRLIT,\nSDKTLN,\nSDCPNT,\nSDRKIT,\nSDKTP,\nTRIM(SDSRP1) AS SDSRP1,\nTRIM(SDSRP2) AS SDSRP2,\nTRIM(SDSRP3) AS SDSRP3,\nTRIM(SDSRP4) AS SDSRP4,\nTRIM(SDSRP5) AS SDSRP5,\nTRIM(SDPRP1) AS SDPRP1,\nTRIM(SDPRP2) AS SDPRP2,\nTRIM(SDPRP3) AS SDPRP3,\nTRIM(SDPRP4) AS SDPRP4,\nTRIM(SDPRP5) AS SDPRP5,\nTRIM(SDUOM) AS SDUOM,\nSDUORG,\nSDSOQS,\nSDSOBK,\nSDSOCN,\nSDSONE,\nSDUOPN,\nSDQTYT,\nSDQRLV,\nTRIM(SDCOMM) AS SDCOMM,\nTRIM(SDOTQY) AS SDOTQY,\nSDUPRC,\nSDAEXP,\nSDAOPN,\nTRIM(SDPROV) AS SDPROV,\nTRIM(SDTPC) AS SDTPC,\nTRIM(SDAPUM) AS SDAPUM,\nSDLPRC,\nSDUNCS,\nSDECST,\nTRIM(SDCSTO) AS SDCSTO,\nSDTCST,\nTRIM(SDINMG) AS SDINMG,\nTRIM(SDPTC) AS SDPTC,\nTRIM(SDRYIN) AS SDRYIN,\nTRIM(SDDTBS) AS SDDTBS,\nSDTRDC,\nSDFUN2,\nTRIM(SDASN) AS SDASN,\nTRIM(SDPRGR) AS SDPRGR,\nTRIM(SDCLVL) AS SDCLVL,\nSDCADC,\nTRIM(SDKCO) AS SDKCO,\nSDDOC,\nTRIM(SDDCT) AS SDDCT,\nSDODOC,\nTRIM(SDODCT) AS SDODCT,\nTRIM(SDOKC) AS SDOKC,\nSDPSN,\nSDDELN,\nTRIM(SDTAX1) AS SDTAX1,\nTRIM(SDTXA1) AS SDTXA1,\nTRIM(SDEXR1) AS SDEXR1,\nTRIM(SDATXT) AS SDATXT,\nTRIM(SDPRIO) AS SDPRIO,\nTRIM(SDRESL) AS SDRESL,\nTRIM(SDBACK) AS SDBACK,\nTRIM(SDSBAL) AS SDSBAL,\nTRIM(SDAPTS) AS SDAPTS,\nTRIM(SDLOB) AS SDLOB,\nTRIM(SDEUSE) AS SDEUSE,\nTRIM(SDDTYS) AS SDDTYS,\nTRIM(SDNTR) AS SDNTR,\nSDVEND,\nSDCARS,\nTRIM(SDMOT) AS SDMOT,\nTRIM(SDROUT) AS SDROUT,\nTRIM(SDSTOP) AS SDSTOP,\nTRIM(SDZON) AS SDZON,\nTRIM(SDCNID) AS SDCNID,\nTRIM(SDFRTH) AS SDFRTH,\nTRIM(SDSHCM) AS SDSHCM,\nTRIM(SDSHCN) AS SDSHCN,\nTRIM(SDSERN) AS SDSERN,\nTRIM(SDUOM1) AS SDUOM1,\nSDPQOR,\nTRIM(SDUOM2) AS SDUOM2,\nSDSQOR,\nTRIM(SDUOM4) AS SDUOM4,\nSDITWT,\nTRIM(SDWTUM) AS SDWTUM,\nSDITVL,\nTRIM(SDVLUM) AS SDVLUM,\nTRIM(SDRPRC) AS SDRPRC,\nTRIM(SDORPR) AS SDORPR,\nTRIM(SDORP) AS SDORP,\nTRIM(SDCMGP) AS SDCMGP,\nTRIM(SDGLC) AS SDGLC,\nSDCTRY,\nSDFY,\nTRIM(SDSO01) AS SDSO01,\nTRIM(SDSO02) AS SDSO02,\nTRIM(SDSO03) AS SDSO03,\nTRIM(SDSO04) AS SDSO04,\nTRIM(SDSO05) AS SDSO05,\nTRIM(SDSO06) AS SDSO06,\nTRIM(SDSO07) AS SDSO07,\nTRIM(SDSO08) AS SDSO08,\nTRIM(SDSO09) AS SDSO09,\nTRIM(SDSO10) AS SDSO10,\nTRIM(SDSO11) AS SDSO11,\nTRIM(SDSO12) AS SDSO12,\nTRIM(SDSO13) AS SDSO13,\nTRIM(SDSO14) AS SDSO14,\nTRIM(SDSO15) AS SDSO15,\nTRIM(SDACOM) AS SDACOM,\nTRIM(SDCMCG) AS SDCMCG,\nTRIM(SDRCD) AS SDRCD,\nSDGRWT,\nTRIM(SDGWUM) AS SDGWUM,\nTRIM(SDSBL) AS SDSBL,\nTRIM(SDSBLT) AS SDSBLT,\nTRIM(SDLCOD) AS SDLCOD,\nTRIM(SDUPC1) AS SDUPC1,\nTRIM(SDUPC2) AS SDUPC2,\nTRIM(SDUPC3) AS SDUPC3,\nTRIM(SDSWMS) AS SDSWMS,\nTRIM(SDUNCD) AS SDUNCD,\nTRIM(SDCRMD) AS SDCRMD,\nTRIM(SDCRCD) AS SDCRCD,\nSDCRR,\nSDFPRC,\nSDFUP,\nSDFEA,\nSDFUC,\nSDFEC,\nTRIM(SDURCD) AS SDURCD,\nSDURDT,\nSDURAT,\nSDURAB,\nTRIM(SDURRF) AS SDURRF,\nTRIM(SDTORG) AS SDTORG,\nTRIM(SDUSER) AS SDUSER,\nTRIM(SDPID) AS SDPID,\nTRIM(SDJOBN) AS SDJOBN,\nSDUPMJ,\nSDTDAY,\nTRIM(SDSO16) AS SDSO16,\nTRIM(SDSO17) AS SDSO17,\nTRIM(SDSO18) AS SDSO18,\nTRIM(SDSO19) AS SDSO19,\nTRIM(SDSO20) AS SDSO20,\nTRIM(SDIR01) AS SDIR01,\nTRIM(SDIR02) AS SDIR02,\nTRIM(SDIR03) AS SDIR03,\nTRIM(SDIR04) AS SDIR04,\nTRIM(SDIR05) AS SDIR05,\nSDSOOR,\nTRIM(SDVR03) AS SDVR03,\nSDDEID,\nTRIM(SDPSIG) AS SDPSIG,\nTRIM(SDRLNU) AS SDRLNU,\nSDPMDT,\nSDRLTM,\nSDRLDJ,\nSDDRQT,\nSDADTM,\nSDOPTT,\nSDPDTT,\nSDPSTM,\nTRIM(SDXDCK) AS SDXDCK,\nSDXPTY,\nTRIM(SDDUAL) AS SDDUAL,\nTRIM(SDBSC) AS SDBSC,\nTRIM(SDCBSC) AS SDCBSC,\nSDCORD,\nSDDVAN,\nTRIM(SDPEND) AS SDPEND,\nTRIM(SDRFRV) AS SDRFRV,\nSDMCLN,\nSDSHPN,\nSDRSDT,\nSDPRJM,\nSDOSEQ,\nTRIM(SDMERL) AS SDMERL,\nTRIM(SDHOLD) AS SDHOLD,\nTRIM(SDHDBU) AS SDHDBU,\nTRIM(SDDMBU) AS SDDMBU,\nTRIM(SDBCRC) AS SDBCRC,\nSDODLN,\nSDOPDJ,\nTRIM(SDXKCO) AS SDXKCO,\nSDXORN,\nTRIM(SDXCTO) AS SDXCTO,\nSDXLLN,\nTRIM(SDXSFX) AS SDXSFX,\nTRIM(SDPOE) AS SDPOE,\nTRIM(SDPMTO) AS SDPMTO,\nSDANBY,\nTRIM(SDPMTN) AS SDPMTN,\nSDNUMB,\nSDAAID,\nTRIM(SDSPATTN) AS SDSPATTN,\nSDPRAN8,\nSDPRCIDLN,\nSDCCIDLN,\nSDSHCCIDLN,\nSDOPPID,\nTRIM(SDOSTP) AS SDOSTP,\nSDUKID,\nTRIM(SDCATNM) AS SDCATNM,\nTRIM(SDALLOC) AS SDALLOC,\nSDFULPID,\nTRIM(SDALLSTS) AS SDALLSTS,\nSDOSCORE,\nTRIM(SDOSCOREO) AS SDOSCOREO,\nTRIM(SDCMCO) AS SDCMCO,\nSDKITID,\nSDKITAMTDOM,\nSDKITAMTFOR,\nTRIM(SDKITDIRTY) AS SDKITDIRTY,\nTRIM(SDOCITT) AS SDOCITT,\nSDOCCARDNO \nfrom \"HILLDTA\".\"F4211\"\n\n\n"
    },
    "sink": {
    "type": "AzureDataLakeStoreSink",
    "copyBehavior": "MergeFiles"
    },
    "enableStaging": true,
    "stagingSettings": {
    "linkedServiceName": {
    "referenceName": "EDW Staging",
    "type": "LinkedServiceReference"
    },
    "path": "edwstaging",
    "enableCompression": true
    },
    "dataIntegrationUnits": 0
    },
    "inputs": [
    {
    "referenceName": "F4211_DB2",
    "type": "DatasetReference"
    }
    ],
    "outputs": [
    {
    "referenceName": "F4211",
    "type": "DatasetReference"
    }
    ]
    },
    {
    "name": "Copy from Transient to Curated",
    "type": "Copy",
    "dependsOn": [
    {
    "activity": "Copy F4211 From E1",
    "dependencyConditions": [
    "Succeeded"
    ]
    }

    ]

    ##################################################################

    Dataset JSON :

    {
    "name": "F4211_DB2",
    "properties": {
    "linkedServiceName": {
    "referenceName": "DB2",
    "type": "LinkedServiceReference"
    },
    "folder": {
    "name": "E1_DB2"
    },
    "type": "RelationalTable",
    "typeProperties": {
    "tableName": "\"HILLDB.\"F4211\""
    }
    }
    }

    Thursday, August 23, 2018 7:42 PM