I am having a problem to update some existing records in DB2. I must be missing something really silly, but I cant just get this working.
I do have a text file being red, transformer and finally a DB2 stage.
The DB2 table key is formed by three fields: two varchars and one being a date, which is the one giving me problem. A fourth field, also date type, is the one being updated.
So lets go for the facts:
DB2 SQL query to update (generate update action from options and columns tab):
Code: Select all
UPDATE #TARGET_TABLE1# SET D_DAT_END_DATE=? WHERE V_COD_GIS_SN=? AND V_COD_ADLVC=? AND D_DAT_START_DATE=?;
Some input files rows (final field corresponds to Transform.D_DAT_START_DATE:D_DAT_START_DATE: StringToDate(Transform.D_DAT_START_DATE,"%yyyy-%mm-%dd")
D_DAT_END_DATE: StringToDate(DATE_EXTRACTION_DAYBEFORE,"%yyyy%mm%dd")
Execution parameters (ones involved here):3691|Enel-CMEC.GISM.09I4E5Q2104024093|24|09LVC51Z29802044|2010-01-27
3693|Enel-CMEC.GISM.09I4E5Q2104024098|24|09LVC51Z29802044|2010-01-27
3697|Enel-CMEC.GISM.09I4E5Q2104024102|24|09LVC51Z29802044|2010-01-01
3
Error got:TARGET_TABLE1=DWH.DWH_LVC_GIS_HIST
DATE_EXTRACTION=20100414
DATE_EXTRACTION_DAYBEFORE=20100413
Some sample data that could not be updated:DWH_LVC_GIS_HIST_UPD,0: Warning: Job02_LVC_METER_HIST_INS_UPD.DWH_LVC_GIS_HIST_UPD: [IBM][CLI Driver][DB2/AIX64] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000
As you can see, the date fields seem to be receiving the correct value, but with added '-' and some heading characters missing (but not always same number of missing)DWH_LVC_GIS_HIST_UPD,0: Warning: Job02_LVC_METER_HIST_INS_UPD.DWH_LVC_GIS_HIST_UPD: V_COD_GIS_SN = Enel-CMEC.GISM.09I4E5Q2104025507 V_COD_ADLVC = 09LVC51Z29802044 D_DAT_START_DATE = 010-01-01 D_DAT_END_DATE = 010-04-13
DWH_LVC_GIS_HIST_UPD,0: Warning: Job02_LVC_METER_HIST_INS_UPD.DWH_LVC_GIS_HIST_UPD: V_COD_GIS_SN = Enel-CMEC.GISM.09I4E5Q2104025743 V_COD_ADLVC = 09LVC51Z29802044 D_DAT_START_DATE = -01-01 D_DAT_END_DATE = -04-13
DWH_LVC_GIS_HIST_UPD,0: Warning: Job02_LVC_METER_HIST_INS_UPD.DWH_LVC_GIS_HIST_UPD: V_COD_GIS_SN = Enel-CMEC.GISM.09I4E5Q2104025554 V_COD_ADLVC = 09LVC51Z29802044 D_DAT_START_DATE = 0-01-01 D_DAT_END_DATE = 0-04-13
I tried changing type in input file, from varchar to date and viceversa, none worked. I dont know what else to try.
Any help will be so appreciated