Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Answered: IMPORT INSERT_UPDATE to NNWD does not result in default values

    Hi All,

    Running db2 v9.1 LUW. Table def is :

    Code:
    CREATE TABLE "REPMAN  "."DAILY_TRAN_COUNTS"  (
    		  "SITEID" VARGRAPHIC(32) NOT NULL , 
    		  "BUSINESSDATE" TIMESTAMP NOT NULL , 
    		  "TRANCOUNT" BIGINT , 
    		  "BOSS_IMPORTED_TS" TIMESTAMP not null with DEFAULT current timestamp)   
    		 IN "TSR_POSCOM4" ;
    An EXPORT into IXF format file succeeds and results in the first 3 columns but not the BOSS_IMPORTED_TS column in the file.

    Code:
    EXPORT TO finename.IXF OF IXF MESSAGES filename.emm SELECT SITEID, BUSINESSDATE, TRANSCOUNT ...
    A subsequent import :

    Code:
    db2 "IMPORT FROM filename.IXF OF IXF MESSAGES messages.imm INSERT_UPDATE INTO REPMAN.DAILY_TRAN_COUNTS"
    results in

    Code:
    SQL3086N  There was no source column specified to be loaded into database
    column "BOSS_IMPORTED_TS" or the specified source column does not exist, but
    the database column is not nullable.
    I have tried the MODIFIED BY USEDEFAULTS option but this does not change it. Removing the NOT NULLS but keeping the DEFAULT clause in the table definition results in nulls being inserted, not the default value.

    The 9.1 IC mentions (wrt the IMPORT statement) :

    if a source column for one of the target table columns is not explicitly specified, one of the following occurs:

    If a default value can be specified for a column, the default value is loaded
    If the column is nullable, and a default value cannot be specified for that column, a NULL is loaded
    If the column is not nullable, and a default value cannot be specified, an error is returned, and the utility stops processing.



    Can someone please advise why DB2 is not behaving the way i'm interpreting it should? Or where my logic is incorrect? Possibly do i need to add a DEFAULT keyword into the INSERT_UPDATE statement somewhere to allow for the fourth column?

    Thanks,

    db

  2. Best Answer
    Posted by mark.b

    "Hi,

    If you want to get defaults in your 4-th column, try this:
    Code:
    import from finename.IXF OF IXF method p(1,2,3) INSERT_UPDATE INTO REPMAN.DAILY_TRAN_COUNTS
    --or
    import from finename.IXF OF IXF INSERT_UPDATE INTO REPMAN.DAILY_TRAN_COUNTS ("SITEID", "BUSINESSDATE", "TRANCOUNT")
    "


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    If you want to get defaults in your 4-th column, try this:
    Code:
    import from finename.IXF OF IXF method p(1,2,3) INSERT_UPDATE INTO REPMAN.DAILY_TRAN_COUNTS
    --or
    import from finename.IXF OF IXF INSERT_UPDATE INTO REPMAN.DAILY_TRAN_COUNTS ("SITEID", "BUSINESSDATE", "TRANCOUNT")
    Regards,
    Mark.

  4. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Try specifying the column-list on the IMPORT command (to match that of the export command).

  5. #4
    Join Date
    Jan 2012
    Posts
    3

    Using METHOD worked.

    Quote Originally Posted by mark.b View Post
    Hi,

    If you want to get defaults in your 4-th column, try this:
    Code:
    import from finename.IXF OF IXF method p(1,2,3) INSERT_UPDATE INTO REPMAN.DAILY_TRAN_COUNTS
    --or
    import from finename.IXF OF IXF INSERT_UPDATE INTO REPMAN.DAILY_TRAN_COUNTS ("SITEID", "BUSINESSDATE", "TRANCOUNT")
    Mark, Thankyou. Your first option did the trick for me. And I'm a little annoyed that the Knowledge Centre is misleading... or at least omitting this detail.
    But thanks again.

    db

Tags for this Thread

Posting Permissions

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