Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    14

    Unhappy Unanswered: Migrating from Sybase to DB2

    Hello,

    I'm migrating a database from sybase to DB2. Does anyone have ideas how to migrate the "timestamp" in sybase to DB2 tables?

    Here is the timestamp defined in DB2 tables:

    timestamp CHAR(8) FOR BIT DATA DEFAULT '00000000'

    However, I used "BCP" to export data from sybase in ASCII format, and tried to use "LOAD" to import data to DB2. It failed to import the complete timestamp with this error:

    SQL3125W The character data in row "F0-1" and column "21" was truncated because the data is longer than the target database column.

    Pls help!! Many thanks.

    Brgds/db2worker

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Migrating from Sybase to DB2

    Hope you have seen the messsages manual ... If not ..

    Compare the value in the output table with the input file. If
    necessary, correct the input file and resubmit the command or
    edit the data in the table. The width of the database column
    cannot be increased. If necessary, define a new table with wider
    columns and repeat the process.



    You can visit www.ibm.com/db2/migration for migration resources .

    There are sample UDFs at http://www7b.software.ibm.com/dmdd/l...dfs/index.html for functions equivalent to Sybase ...

    For more links, refer the "Read Me : Useful DB2 Stuff" thread at the top of this forum ...
    Cheers
    Sathyaram

    Originally posted by db2worker
    Hello,

    I'm migrating a database from sybase to DB2. Does anyone have ideas how to migrate the "timestamp" in sybase to DB2 tables?

    Here is the timestamp defined in DB2 tables:

    timestamp CHAR(8) FOR BIT DATA DEFAULT '00000000'

    However, I used "BCP" to export data from sybase in ASCII format, and tried to use "LOAD" to import data to DB2. It failed to import the complete timestamp with this error:

    SQL3125W The character data in row "F0-1" and column "21" was truncated because the data is longer than the target database column.

    Pls help!! Many thanks.

    Brgds/db2worker
    Last edited by sathyaram_s; 07-23-03 at 03:43.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2003
    Posts
    14

    Re: Migrating from Sybase to DB2

    Thx Sathyaram.

    However, the problem is:
    In sybase timestamp is a user-defined data type varbinary(8), and by using IBM Migration Toolkit, it converts Sybase timestamp to Char(8) for bit data by default.

    1. Below is what i selec from sybase table. I supposed to have the same result when data migrated to DB2. (However, it didn't show me the same thing!)

    Sybase timestamp
    ---------
    0x4001000036a3136f
    0x4001000036a31401
    0x4001000036a313fe


    2. I use BCP command to export data from sybase. this is some of the data i copy from the datafile.

    BCP file
    ---------
    000100000034fb45
    000100000034fcde


    3. When i use "LOAD" command to load data into DB2 database, it returns warning with the SQL message prompting me that there is not enough space.

    timestamp (DB2) char(8)
    ---------------------
    x'303030313030303
    x'303030313030303
    x'303030313030303


    4. Below is the result after I have enlarge the timestamp column size. However, it still shows completely different things from the sybase timestamp.

    timestamp (DB2) char(16)
    ----------------------------
    x'30303031303030303830663261653264'
    x'30303031303030303735656135386637'
    x'30303031303030303737396237656464'
    x'30303031303030303765336662313563'

    Do u have any idea how to migrate Sybase timestamp to DB2 timestamp? I really do need the same timestamp values for both data in Sybase and DB2(e.g. 0x4001000036a313fe). Many many thx.

    Brgds/db2worker

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Migrating from Sybase to DB2

    Originally posted by db2worker


    timestamp (DB2) char(8)
    ---------------------
    x'303030313030303
    x'303030313030303
    x'303030313030303

    I don't think DB2 LOAD (or IMPORT) can parse binary data represented by a hexadecimal string. What happens is the utility treats your timestamp as a string (whose length is 16 characters) and tries to stuff it into the CHAR(8) field, which obviously truncates half of data.

    I can thing of two ways to accomplish what you need:

    a) if you can export your Sybase timestamps as human-readable string (like '2003-07-21-10:04.11.938') then you will be able to import them into a field defiened as TIMESTAMP

    b) if it's impossible then you will probably have to import timestamps as strings (into CHAR(16) column) and then have a UDF that would convert your hexadecimal strings into binary timestamps

    Hope this helps.

    Nick

  5. #5
    Join Date
    Jul 2003
    Posts
    14

    Re: Migrating from Sybase to DB2

    Thx Nick!

Posting Permissions

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