Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Unanswered: Date formatting for DB2 load utility

    Hi,

    I am trying to populate date and time into DB2 table using DB2 load utility script.

    The date coming from source is in the format of YYYY-MM-DD(eg.2006-12-01) and the time in the form of hh:mm:ss

    i am using the following utility script to perform the operation:

    Code:
    db2 LOAD CLIENT FROM <source filename> OF ASC modified by identitymissing dateformat="YYYY-MM-DD"  timeformat="HH:MM:SS"  
    striptblanks reclen=32 method L (1 12,13 13,14 24,24 32) messages <error msg filename>INSERT INTO Table Name(Col1,Col2,Col3,Col4,Col5)
    But this is giving a error :
    SQL3192N In the filetmod a user specified format "DATEFORMAT" beginning with
    the string "." is not valid.

    Could you help me out with this.


    Regards,
    Shilpa Bhat

  2. #2
    Join Date
    Dec 2007
    Location
    Amsterdam, NL
    Posts
    2
    dear Shilpa.Bhat,

    You should give all srings within single quotes, so '2007-12-21' would do it.

    Regards,
    FdeGrijs

  3. #3
    Join Date
    Dec 2007
    Posts
    2
    even after using single quotes 'YYYY-MM-DD',it still is giving the same error.

    SQL3192N In the filetmod a user specified format "DATEFORMAT" beginning with
    the string "." is not valid.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm thinking that may be the quotation marks get interpreted by the shell. Try enclosing the entire DB2 command in quotes:
    Code:
    db2 "load client ... modified by dateformat=\"YYYYMMDD\" ..."
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by n_i
    I'm thinking that may be the quotation marks get interpreted by the shell.
    Indeed; single quotes are even easier: (assuming this is a unix shell, not cmd.exe)
    Code:
    db2 'LOAD CLIENT FROM <source filename> OF ASC
         modified by identitymissing dateformat="YYYY-MM-DD"
         timeformat="HH:MM:SS" striptblanks reclen=32
         method L (1 12,13 13,14 24,24 32) messages <error msg filename>
         INSERT INTO Table Name(Col1,Col2,Col3,Col4,Col5)'
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2014
    Posts
    1

    thank you

    it's 2014 and yeah that fix helped me a lot, it's all about the *** quotations like in comment#4 & comment#5

Posting Permissions

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