Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013
    Posts
    8

    Unanswered: Lets play a game - Warningcount and messages

    Gents, let me explain an issue I am trying to come up with a workaround for.

    We have jobs where db2 loads data from txt files. Say like below;

    db2 "LOAD FROM ${LOADFILE}_markdown.txt OF ASC \
    MODIFIED BY noheader striptblanks usedefaults \
    METHOD L ( 1 3, \
    4 12, \
    13 19, \
    20 20, \
    21 23, \
    24 28, \
    29 42, \
    43 54, \
    55 60, \
    61 72, \
    73 79) \
    WARNINGCOUNT 1 \
    MESSAGES ${LOAD_ERR} \
    ${LOAD_TYPE} INTO mbs.ctf_markdown \
    (region_code, \
    store_number, \
    ctf_id, \
    markdown_type, \
    markdown_subtype, \
    pos_dept_number, \
    upc_number, \
    discount_amount, \
    markdown_qty, \
    markdown_weight, \
    markdown_percent) \
    NONRECOVERABLE" &

    ...so, as you can see if we get one error (typically for us it a SMALLINT or DECIMAL field and the number trying to be loaded is too big) the script errors out, loads messages to the message file and induces a page out to support people. I am trying to think of some way to not have the script fail yet still populate a messages file. In the morning the support people could then look at this messages file and pull the bad rows from the text file, fix the bad data (as messages file would tell us rows and error) and then have it be loaded on the next run of the job. These jobs only run at 2-4am and thus the need to at least look for other ways than job failure and immediate fixing of the bad data.

    I can increase the warningcount so the script would keep loading but when i do that nothing is written to the messages file (which i believe is working as designed).

    Can any of you think of ways to do this?

    Thanks
    Brad

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    One approach is to pre-process the data file(s) before load, with a perl script or awk script, and just find the broken lines and either exclude them from the output file of the script, or do a data-repair before writing the corrected line to the output file of the script. Then load the modified file.
    Other approaches exist...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •