Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Jan 25
    2008

    How do you handle DB2 discards?

    Mark Kerner
    [Pitney Bowes]
    A question for a Friday...

    At the present time we are running DB2 V7.1 with z/OS V1.7.

    A number of our DB2 tables are sourced from IMS data. Most of these
    tables are populated as LOAD REPLACE and allow for a certain number of
    discards (not sure why, but they do). Herein lies the problem, because as
    all DBA?s know, the data should be correct at its source. However, there
    are at times a handful of records that are discarded due to date
    conversion errors. IMS does not check to see that a value populated into
    a date field is actually formatted properly. I?ve found everything from
    spaces to zeros to invalid dates such as 20040431, and other packed
    garbage in columns that DB2 expects to see defined as DATE. And,
    according to their definition, they cannot be NULL either.

    How do your shops handle these situations?

    1- One approach I am thinking of taking is to define these columns in the
    LOAD control statement with a DEFAULTIF ERROR clause, which in the case of
    a date will default to the date of the load. I will do this only after
    notifying as many users of the table as I can find. As least the row of
    data won?t be lost at the cost of a bad date.

    2- If the DB2 column does allow NULL?s, I am not comfortable loading using
    the NULLIF ERROR clause because of how nulls are treated by DB2. In this
    case I am more prone to have someone examine the offending source programs
    and correct the data prior to presenting it to the DB2 load.

    3- Are there any other criteria I can use in the LOAD statement to tailor
    the values I want populated in DB2? Can I have DB2 load a date of
    2004-04-30 every time I encounter a value of 2004-04-31? Am I limited to
    the DEFAULTIF and NULLIF clauses to initialize values?

    Thanks for your input.


    Mark L. Kerner

    Tech Central/ Database Services
    1 Elmcroft Road
    Mail Drop 54-24
    Stamford, CT 06926-0700
    Phone: (203) 351-6637 (Internal 440-6637)
    Fax: (203) 546-4833
    Remedy Group: TC.EDSS.Database

    Please note: The information contained in this message may be privileged
    and confidential and protected from disclosure. If the reader of this
    message is not the intended recipient, or an employee or agent responsible
    for delivering this message to the intended recipient, you are hereby
    notified that any dissemination, distribution or copying of this
    communication is strictly prohibited. If you received this communication
    in error, please notify us immediately by replying to this message and
    deleting it from your computer. Thank you. Pitney Bowes, Inc.


    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
    Avram Friedman
    This seems to be a case of over enginered table design.
    A field that seems to contain a date in a non DB2 platform like IMS should not
    be automaticly maped to a timestamp field in DB2.

    It was only yesterday that there was a discussion about DB2 EXPLAIN and the
    Plan Table where a lister pointed out that in some older releases of DB2 fields
    that appeared to be TIMESTAMPS were not because DB2 did not support
    TIMESTAMPS from the get go.

    How are these problem matic fields defined to IMS via the DBD, if at all, Note
    in general it is not an IMS requirement that all fields be defined ... only
    Sequence and Key fields.

    If the fields are not defined in the DBD how are the defined in the copy books
    or DSECTS that reference them? a COBOL structure like
    07 DATE.
    09 YEAR PIC X(4).
    09 MONTH PIC XX.
    09 DAY PIC XX.

    Should not be mapped to a time stamp column no matter how much you think
    that it sort of looks like a date.

    Regards
    Avram Friedman

    On Fri, 25 Jan 2008 15:36:29 -0500, [login to unmask email] wrote:

    >A question for a Friday...
    >
    >At the present time we are running DB2 V7.1 with z/OS V1.7.
    >
    >A number of our DB2 tables are sourced from IMS data. Most of these
    >tables are populated as LOAD REPLACE and allow for a certain number of
    >discards (not sure why, but they do). Herein lies the problem, because as
    >all DBA?s know, the data should be correct at its source. However, there
    >are at times a handful of records that are discarded due to date
    >conversion errors. IMS does not check to see that a value populated into
    >a date field is actually formatted properly. I?ve found everything from
    >spaces to zeros to invalid dates such as 20040431, and other packed
    >garbage in columns that DB2 expects to see defined as DATE. And,
    >according to their definition, they cannot be NULL either.
    >
    >How do your shops handle these situations?
    >
    >1- One approach I am thinking of taking is to define these columns in the
    >LOAD control statement with a DEFAULTIF ERROR clause, which in the case
    of
    >a date will default to the date of the load. I will do this only after
    >notifying as many users of the table as I can find. As least the row of
    >data won?t be lost at the cost of a bad date.
    >
    >2- If the DB2 column does allow NULL?s, I am not comfortable loading using
    >the NULLIF ERROR clause because of how nulls are treated by DB2. In this
    >case I am more prone to have someone examine the offending source
    programs
    >and correct the data prior to presenting it to the DB2 load.
    >
    >3- Are there any other criteria I can use in the LOAD statement to tailor
    >the values I want populated in DB2? Can I have DB2 load a date of
    >2004-04-30 every time I encounter a value of 2004-04-31? Am I limited to
    >the DEFAULTIF and NULLIF clauses to initialize values?
    >
    >Thanks for your input.
    >
    >
    >Mark L. Kerner
    >
    > Tech Central/ Database Services
    > 1 Elmcroft Road
    > Mail Drop 54-24
    > Stamford, CT 06926-0700
    > Phone: (203) 351-6637 (Internal 440-6637)
    > Fax: (203) 546-4833
    > Remedy Group: TC.EDSS.Database
    >
    >Please note: The information contained in this message may be privileged
    >and confidential and protected from disclosure. If the reader of this
    >message is not the intended recipient, or an employee or agent responsible
    >for delivering this message to the intended recipient, you are hereby
    >notified that any dissemination, distribution or copying of this
    >communication is strictly prohibited. If you received this communication
    >in error, please notify us immediately by replying to this message and
    >deleting it from your computer. Thank you. Pitney Bowes, Inc.
    >
    >
    >The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
    archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
    under the Listserv tab. While at the site, you can also access the IDUG
    Online Learning Center, Tech Library and Code Place, see the latest IDUG
    conference information, and much more. If you have not yet signed up for
    Basic Membership in IDUG, available at no cost, click on Member Services at
    http://www.idug.org/lsms

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
    Mark Finnell
    [Dept of Social Services]
    Mark,

    In our data warehouse we handle situations like this by writing an
    intermediate program to "fix" data. We experience problems with dates
    (among other things) from IDMS so if we encounter 2004-04-31 we change
    it to 2004-04-30 in our transformation program before we LOAD. Sorry, I
    don't have any good LOAD utility tricks for handling this.
    -----------------------------------------------
    Mark E. Finnell
    Database Administration Team Lead
    Information Technology Services Division
    State of Missouri
    1621 East Elm Street
    Jefferson City, MO 65101
    573-751-2167 (voice)
    573-751-2026 (fax)
    [login to unmask email] <blocked::mailto:[login to unmask email]>


    ________________________________

    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
    Behalf Of [login to unmask email]
    Sent: Friday, January 25, 2008 2:36 PM
    To: [login to unmask email]
    Subject: [DB2-L] How do you handle DB2 discards?



    A question for a Friday...

    At the present time we are running DB2 V7.1 with z/OS V1.7.

    A number of our DB2 tables are sourced from IMS data. Most of these
    tables are populated as LOAD REPLACE and allow for a certain number of
    discards (not sure why, but they do). Herein lies the problem, because
    as all DBA's know, the data should be correct at its source. However,
    there are at times a handful of records that are discarded due to date
    conversion errors. IMS does not check to see that a value populated
    into a date field is actually formatted properly. I've found everything
    from spaces to zeros to invalid dates such as 20040431, and other packed
    garbage in columns that DB2 expects to see defined as DATE. And,
    according to their definition, they cannot be NULL either.

    How do your shops handle these situations?

    1- One approach I am thinking of taking is to define these columns in
    the LOAD control statement with a DEFAULTIF ERROR clause, which in the
    case of a date will default to the date of the load. I will do this
    only after notifying as many users of the table as I can find. As least
    the row of data won't be lost at the cost of a bad date.

    2- If the DB2 column does allow NULL's, I am not comfortable loading
    using the NULLIF ERROR clause because of how nulls are treated by DB2.
    In this case I am more prone to have someone examine the offending
    source programs and correct the data prior to presenting it to the DB2
    load.

    3- Are there any other criteria I can use in the LOAD statement to
    tailor the values I want populated in DB2? Can I have DB2 load a date
    of 2004-04-30 every time I encounter a value of 2004-04-31? Am I
    limited to the DEFAULTIF and NULLIF clauses to initialize values?

    Thanks for your input.



    Mark L. Kerner

    Tech Central/ Database Services
    1 Elmcroft Road
    Mail Drop 54-24
    Stamford, CT 06926-0700

    Phone: (203) 351-6637 (Internal 440-6637)
    Fax: (203) 546-4833
    Remedy Group: TC.EDSS.Database


    Please note: The information contained in this message may be privileged
    and confidential and protected from disclosure. If the reader of this
    message is not the intended recipient, or an employee or agent
    responsible for delivering this message to the intended recipient, you
    are hereby notified that any dissemination, distribution or copying of
    this communication is strictly prohibited. If you received this
    communication in error, please notify us immediately by replying to this
    message and deleting it from your computer. Thank you. Pitney Bowes,
    Inc.

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
    list archives, the FAQ, and delivery preferences are at www.idug.org
    < http://www.idug.org/lsidug > under the Listserv tab. While at the site,
    you can also access the IDUG Online Learning Center, Tech Library and
    Code Place, see the latest IDUG conference information
    < http://www.idug.org/lsconf > , and much more.
    If you have not yet signed up for Basic Membership in IDUG, available at
    no cost, click on Member Services < http://www.idug.org/lsms >


    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
    Randy Bright
    [BMC Software, Inc.]
    If the data item that is causing the discard when loading into the DB2
    table is not an important piece of information, then you should be able
    to set a default value as you describe with DEFAULTIF or NULLIF. But,
    if it isn't important, why do you even have it in the database? Since
    the data is bad in IMS, and applications using the IMS data do not seem
    to have any problem with it, maybe it is unused or unimportant data and
    setting it to a default value in DB2 is perfectly fine.



    If the information is critical to the DB2 applications, then you have a
    more severe problem. What value is correct for "20040431"? Should it
    be changed to "20040430" or "20040501"? Is it significant which month
    the data is recorded in? A data cleansing program may need to be
    written that intelligently resets the bad values based on other
    information in the record. For example if the "20040431" date is
    supposed to be 10 days after another date in the same record and that
    date is "20040421", then it becomes obvious the corrected value should
    be "20040501". Likewise, if it is supposed to be 10 days before another
    date that is "20040510", then the obvious correct value is "20040430".



    Your best solution is to determine what application is putting the bad
    values in the IMS data and fix it. Then cleanse the IMS database of the
    bad data using an intelligent program like I described above. Now your
    DB2 problems are gone. If cleansing the IMS data is too costly, then
    the cleansing program will have to be a pre-DB2 load process. Either
    way, I think you are going to have to write code based on your
    application's requirements to fix the data. If it is important data,
    you may not be able to just set an arbitrary value when an error occurs.



    This is just my opinion, by the way.



    Randy Bright

    Development Architect

    DB2 Utilities

    BMC Software, Inc.

    [login to unmask email]


    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
    David Seibert
    I'm curious, Mark.
    What makes you uncomfortable about the way DB2 handles nulls?

    Dave




    The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.


    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
    Behalf Of [login to unmask email]
    Sent: Friday, January 25, 2008 3:36 PM
    To: [login to unmask email]
    Subject: [DB2-L] How do you handle DB2 discards?



    A question for a Friday...

    At the present time we are running DB2 V7.1 with z/OS V1.7.

    A number of our DB2 tables are sourced from IMS data. Most of these
    tables are populated as LOAD REPLACE and allow for a certain number of
    discards (not sure why, but they do). Herein lies the problem, because
    as all DBA's know, the data should be correct at its source. However,
    there are at times a handful of records that are discarded due to date
    conversion errors. IMS does not check to see that a value populated
    into a date field is actually formatted properly. I've found everything
    from spaces to zeros to invalid dates such as 20040431, and other packed
    garbage in columns that DB2 expects to see defined as DATE. And,
    according to their definition, they cannot be NULL either.

    How do your shops handle these situations?

    1- One approach I am thinking of taking is to define these columns in
    the LOAD control statement with a DEFAULTIF ERROR clause, which in the
    case of a date will default to the date of the load. I will do this
    only after notifying as many users of the table as I can find. As least
    the row of data won't be lost at the cost of a bad date.

    2- If the DB2 column does allow NULL's, I am not comfortable loading
    using the NULLIF ERROR clause because of how nulls are treated by DB2.
    In this case I am more prone to have someone examine the offending
    source programs and correct the data prior to presenting it to the DB2
    load.

    3- Are there any other criteria I can use in the LOAD statement to
    tailor the values I want populated in DB2? Can I have DB2 load a date
    of 2004-04-30 every time I encounter a value of 2004-04-31? Am I
    limited to the DEFAULTIF and NULLIF clauses to initialize values?

    Thanks for your input.



    Mark L. Kerner

    Tech Central/ Database Services
    1 Elmcroft Road
    Mail Drop 54-24
    Stamford, CT 06926-0700

    Phone: (203) 351-6637 (Internal 440-6637)
    Fax: (203) 546-4833
    Remedy Group: TC.EDSS.Database


    Please note: The information contained in this message may be privileged
    and confidential and protected from disclosure. If the reader of this
    message is not the intended recipient, or an employee or agent
    responsible for delivering this message to the intended recipient, you
    are hereby notified that any dissemination, distribution or copying of
    this communication is strictly prohibited. If you received this
    communication in error, please notify us immediately by replying to this
    message and deleting it from your computer. Thank you. Pitney Bowes,
    Inc.

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
    list archives, the FAQ, and delivery preferences are at www.idug.org
    < http://www.idug.org/lsidug > under the Listserv tab. While at the site,
    you can also access the IDUG Online Learning Center, Tech Library and
    Code Place, see the latest IDUG conference information
    < http://www.idug.org/lsconf > , and much more.
    If you have not yet signed up for Basic Membership in IDUG, available at
    no cost, click on Member Services < http://www.idug.org/lsms >

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
    Mark Kerner
    [Pitney Bowes]
    Hi Dave,
    Thanks for your response.
    I may have misspoken.
    It's not the way that DB2 handles nulls, but the way programmers will or
    won't get data returned to them if a date in a row happens to be null.
    (Or, perhaps it's MY understanding of what will happen).


    Mark L. Kerner

    Tech Central/ Database Services
    1 Elmcroft Road
    Mail Drop 54-24
    Stamford, CT 06926-0700
    Phone: (203) 351-6637 (Internal 440-6637)
    Fax: (203) 546-4833
    Remedy Group: TC.EDSS.Database

    Please note: The information contained in this message may be privileged
    and confidential and protected from disclosure. If the reader of this
    message is not the intended recipient, or an employee or agent responsible
    for delivering this message to the intended recipient, you are hereby
    notified that any dissemination, distribution or copying of this
    communication is strictly prohibited. If you received this communication
    in error, please notify us immediately by replying to this message and
    deleting it from your computer. Thank you. Pitney Bowes, Inc.




    "Seibert, Dave" <[login to unmask email]>
    Sent by: DB2 Data Base Discussion List <[login to unmask email]>
    01/28/2008 10:47 AM
    Please respond to
    DB2 Database Discussion list at IDUG <[login to unmask email]>


    To
    [login to unmask email]
    cc

    Subject
    Re: [DB2-L] How do you handle DB2 discards?






    I'm curious, Mark.
    What makes you uncomfortable about the way DB2 handles nulls?
    Dave



    The contents of this e-mail are intended for the named addressee only. It
    contains information that may be confidential. Unless you are the named
    addressee or an authorized designee, you may not copy or use it, or
    disclose it to anyone else. If you received it in error please notify us
    immediately and then destroy it.

    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
    Of [login to unmask email]
    Sent: Friday, January 25, 2008 3:36 PM
    To: [login to unmask email]
    Subject: [DB2-L] How do you handle DB2 discards?


    A question for a Friday...

    At the present time we are running DB2 V7.1 with z/OS V1.7.

    A number of our DB2 tables are sourced from IMS data. Most of these
    tables are populated as LOAD REPLACE and allow for a certain number of
    discards (not sure why, but they do). Herein lies the problem, because as
    all DBA?s know, the data should be correct at its source. However, there
    are at times a handful of records that are discarded due to date
    conversion errors. IMS does not check to see that a value populated into
    a date field is actually formatted properly. I?ve found everything from
    spaces to zeros to invalid dates such as 20040431, and other packed
    garbage in columns that DB2 expects to see defined as DATE. And,
    according to their definition, they cannot be NULL either.

    How do your shops handle these situations?

    1- One approach I am thinking of taking is to define these columns in the
    LOAD control statement with a DEFAULTIF ERROR clause, which in the case of
    a date will default to the date of the load. I will do this only after
    notifying as many users of the table as I can find. As least the row of
    data won?t be lost at the cost of a bad date.

    2- If the DB2 column does allow NULL?s, I am not comfortable loading using
    the NULLIF ERROR clause because of how nulls are treated by DB2. In this
    case I am more prone to have someone examine the offending source programs
    and correct the data prior to presenting it to the DB2 load.

    3- Are there any other criteria I can use in the LOAD statement to tailor
    the values I want populated in DB2? Can I have DB2 load a date of
    2004-04-30 every time I encounter a value of 2004-04-31? Am I limited to
    the DEFAULTIF and NULLIF clauses to initialize values?

    Thanks for your input.

    Mark L. Kerner

    Tech Central/ Database Services
    1 Elmcroft Road
    Mail Drop 54-24
    Stamford, CT 06926-0700
    Phone: (203) 351-6637 (Internal 440-6637)
    Fax: (203) 546-4833
    Remedy Group: TC.EDSS.Database

    Please note: The information contained in this message may be privileged
    and confidential and protected from disclosure. If the reader of this
    message is not the intended recipient, or an employee or agent responsible
    for delivering this message to the intended recipient, you are hereby
    notified that any dissemination, distribution or copying of this
    communication is strictly prohibited. If you received this communication
    in error, please notify us immediately by replying to this message and
    deleting it from your computer. Thank you. Pitney Bowes, Inc.

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
    list archives, the FAQ, and delivery preferences are at www.idug.org under
    the Listserv tab. While at the site, you can also access the IDUG Online
    Learning Center, Tech Library and Code Place, see the latest IDUG
    conference information, and much more.
    If you have not yet signed up for Basic Membership in IDUG, available at
    no cost, click on Member Services


    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
    list archives, the FAQ, and delivery preferences are at www.idug.org under
    the Listserv tab. While at the site, you can also access the IDUG Online
    Learning Center, Tech Library and Code Place, see the latest IDUG
    conference information, and much more.
    If you have not yet signed up for Basic Membership in IDUG, available at
    no cost, click on Member Services

    The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact