Unable to update existing rows in DB2

[manuel.gomez]

Hello all,

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=?;

Transformer derivation:

D_DAT_START_DATE: StringToDate(Transform.D_DAT_START_DATE,"%yyyy-%mm-%dd")
D_DAT_END_DATE: StringToDate(DATE_EXTRACTION_DAYBEFORE,"%yyyy%mm%dd")

Some input files rows (final field corresponds to Transform.D_DAT_START_DATE:

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

Execution parameters (ones involved here):

TARGET_TABLE1=DWH.DWH_LVC_GIS_HIST
DATE_EXTRACTION=20100414
DATE_EXTRACTION_DAYBEFORE=20100413

Error got:

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

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: 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

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)

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

[battaliou]

Try changing your target metadata setting to varchar size 10 even though its a date

[manuel.gomez]

Try changing your target metadata setting to varchar size 10 even though its a date

Thanks for your answer, but it did not work either

[manuel.gomez]

Try changing your target metadata setting to varchar size 10 even though its a date

Thanks for your answer, but it did not work either

[battaliou]

did it give you the same message with truncated dates? If so, try peeking the data too.

[chowdhury99]

Remove hypens from the date format make it yyyy%mm%dd"

Thanks