Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    6

    Unanswered: db2move problem with VARCHAR columns

    Hi list,

    please be gently explicative to me since I am rather new to this LUW stuff (I have worked on DB2 for over 10 years, but on mainframes).

    Guess what, I'm lost in AIX-land.

    I am trying to execute the db2move utility with the EXPORT option of a DB with 300 tables, and the problem is that the does not come out "right", a lot of tables are missing - it exported 111 tables only out of the lot, and the problem seems to be the type of the column (VARCHAR in my case).

    The export.out file contains a lot of warning messages with SQL3132W , such as:

    SQL3132W
    The character data in column column will be truncated to size size.
    Explanation:

    The character data column has a defined size that is longer than the longest default character column which can be exported; each value will be truncated to the specified size.

    For example, by default, only the first SQL_LONGMAX bytes of a LOB column will be exported. If the entire LOB column is desired, the LOBSINFILE keyword must be specified in the filetype modifier and each LOB column will be stored into a separate file.


    I searched the APARs database, and found exactly sweet FA.

    Anyone have any ideas or suggestions on how to overcome this? As mentioend right at the top, any suggestions would need to be almost step-by-step because this platform's intricacies are still a bit of a mystery to me.

    Thanks in advance for any pain relief.



    Aurora

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Please post your exact release and fixpack level (type db2level at the command line), and the exact db2move statement you used.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Are you talking about LOBs or VARCHARs? Both are different data types (also on DB2 z/OS). Due to the size of LOBs, they are not stored inline in the export file. "db2move" has the -l (ell) option where you can provide a directory to create files for the LOBs in. Have you tried that already?

    p.s: Which version of DB2 are you using? What's the exact db2move command you used?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Nov 2007
    Posts
    6
    Hi guys,

    DB21085I Instance "db2insd8" uses "32" bits and DB2 code release "SQL08027" with level identifier "03080106".
    Informational tokens are "DB2 v8.1.1.128", "s061108", "U810098", and FixPak "14".

    have a look at the output I get:

    $ db2move EDIDCE07 export -l /db2/backup/db2move_20071114/lobdata_move

    ***** DB2MOVE *****

    Action: EXPORT

    Start time: Wed Nov 14 12:19:09 2007


    Connecting to database EDIDCE07 ... successful! Server: DB2 Common Server
    V8.2.7

    EXPORT: 28 rows from table "TXDSD2 "."PARSEEDIACTIONS"

    *** Table "TXDSD2 "."PARTIES": ERROR 3107. Check message file tab2.msg!
    *** SQL Warning! SQLCODE is 3107
    *** SQL3107W There is at least one warning message in the message file.

    EXPORT: 66 rows from table "TXDSD2 "."PARTYBUSINESSPROTOCOLS"
    EXPORT: 0 rows from table "TXDSD2 "."PARTYCATEGORIES"

    *** Table "TXDSD2 "."PARTYCERTIFICATEIDS": ERROR 3107. Check message file tab5.msg!
    *** SQL Warning! SQLCODE is 3107
    *** SQL3107W There is at least one warning message in the message file.

    And so on and so forth.

    Boo.

    I have deleted the whole directory and it simply repeats the same messages for EACH table :-P AT least this time specifying the lobpath it has given me all 365 tables instead of 111.
    The definition of these problem columns is LONG VARCHAR :-s

    I am going to forward this to the vendor and let them see if the data really is truncated - they insist it always is but I have a feeling they stop at looking at the messages...

    Thanks.

    Aurora

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    LONG VARCHAR is completely different from VARCHAR, and is treated similar to a LOB. LONG VARCHAR is being phased out (depreciated), and you should think about converting the LONG VARCHAR to VARCHAR (if you can use the appropriate page size and are OK giving up a few bytes in maximum length), or to a LOB if you cannot.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Nov 2007
    Posts
    6
    thanks Marcus, I'll tell the vendor to keep this in mind - I am just DB2 support to the application people, and my support is currently being impeded by this POXY UTILITY NOT DOING WHAT I TELL IT TO DO grrrr.

    sorry, just having a bossy woman moment there :-S

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Now we're getting somewhere... Who the heck is still using LONG VARCHAR? (Never mind.)

    I just had a look at the manual: if you don't specify the -aw option (allow warnings), then tables with warnings will not be added to the db2move.list file. That would explain why not all the tables were imported/loaded in the target system.

    As for the other warnings, have you check the respective .msg files to see what DB2 is worried about?

    db2move documentation:
    http://publib.boulder.ibm.com/infoce...e/r0002079.htm
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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