Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013

    Unanswered: Passing Parameters to DB2 Load Utility

    I'm trying to load a file into DB2 database table using DB2 Load. I'm able to load the file, using the following statement from the unix prompt.

    db2 +p -x load from BOM_Sales_20130326.txt of DEL MODIFIED BY COLDEL\| timestampformat=\"YYYY-MM-DD hh:mm:ss\" insert into GEO_SALES.SDM_STL_VFRSK_SALES NONRECOVERABLE

    But, when I try to call DB2 Load from a script after parameterising many of the options, it does not seem to work.

    For example, I have re-written the statement as follows:

    db2 +p -x "load client from $FILE_DIR/$filenm of DEL MODIFIED BY COLDEL\$FILE_DELMTR timestampformat=\"YYYY-MM-DD HH:MMS\" insert into $TGT_SCHEMA_NM.$FILE_STG_TBL_TGT NONRECOVERABLE"

    All the variable are assigned with correct values prior to calling the statemant. But it seemed to fail due to wrong timestampformat. The error is as follows:

    SQL3191N The field in row "F2-1", column "1" which begins with
    does not match the user specified DATEFORMAT,
    TIMEFORMAT, or TIMESTAMPFORMAT. The row will be rejected.

    In fact, my need is to give the value "timestampformat" also as a parameter, as follows:
    db2 +p -x "load client from $FILE_DIR/$filenm of DEL MODIFIED BY COLDEL\$FILE_DELMTR timestampformat=$DTTIME_FMT insert into $TGT_SCHEMA_NM.$FILE_STG_TBL_TGT NONRECOVERABLE"

    Since it was not working, just for the testing purpose, the variable $DTTIME_FMT was replaced with \"YYYY-MM-DD HH:MMS\". As said earlier, that too does not seem to work.

    Request your help to fix this and also like to know whether I can parameterise the all these load options as I've tried.


  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Firstly, the field value in the file you've shown does not seem to contain the time portion, so the timestampformat error doesn't surprise me much. Secondly, try to print out the entire LOAD command after the variable substitution to make sure it looks the way you expect it to.
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2013
    Hi, Thanks for the quick reply and suggestion. Somehow I missed printing the full load command into the file, though I printed the individual values . The error was due to two reasons :
    1. All the substituted variables had trailing spaces. (These variables were taken from DB).
    2. The character "\", I had added after timestampformat and COLDEL keywords created issues. It's working fine after the above two corrections were made.

    Even though the time portion is not available in data, the utility adds default time(12:00:00) to the dates in the file.


Posting Permissions

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