Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: DB2-2-mssql: what a hassle

    Have to move some tables from db2 to ms-SQL.
    Simple: "export to CSV of del" from DB2 and "bulk insert" into ms-SQL, right?

    Yeah right.
    1 - got a "timestamp" column which I implemented as "datetime" on that other dbms
    2 - got some varchar columns
    3 - got 1 char(1) column

    So
    1 - In my perl/DBI scripts I already figured out how to handle that format.
    Code:
    ## Opmaken timestamps:
    ##   db2   : 20101124120253 := 2010-11-24-12.02.53.0000
    ##   mssql : 20101124120253 := 20101124 12:02:53:000
    ##
    my  $in_ts = $_[0] ;
    my  $db_ts ;
    ##
      if    ( $otap eq 'd' ) 
            { 
              $db_ts = substr($in_ts, 0, 4)
                  ."-".substr($in_ts, 4, 2)
                  ."-".substr($in_ts, 6, 2)
                  ."-".substr($in_ts, 8, 2)
                  .".".substr($in_ts,10, 2)
                  .".".substr($in_ts,12, 2)
                  .".0000" ;
            }
      else 
            {        	
              $db_ts = substr($in_ts,0,8)
                  ." ".substr($in_ts,8,2)
                  .":".substr($in_ts,10,2)
                  .":".substr($in_ts,12,2)
                  .".000" ;
            }
      return $db_ts ;
    so one might think that the same format will work in your CSV file, right? Wrong. You'll need this:
    Code:
    --
    -- desired result: 2010/11/24 12:02:53:000		
     , rtrim(char(substr(digits(smallint((year   (dosis_sub_timestmp)))),2,4) || '/'
       ||  substr(digits(smallint((month  (dosis_sub_timestmp)))),4,2) || '/'
       ||  substr(digits(smallint((day    (dosis_sub_timestmp)))),4,2) || space(1)
       ||  substr(digits(smallint((hour   (dosis_sub_timestmp)))),4,2) || ':'
       ||  substr(digits(smallint((minute (dosis_sub_timestmp)))),4,2) || ':'
       ||  substr(digits(smallint((second (dosis_sub_timestmp)))),4,2) || ':000'
       ))  as dosis_sub_datetime
    and that is not all.
    The db2 export command will embed this data in double-quotes. Ahh too bad. Will not be accepted. Loose the quotes and it will load.

    2 - No big deal at 1st sight
    3 - DB2 export will embed this in double-quotes as well. No go, on a mssql database a char(1) column is not embedded by quotes.

    Bottom line: when you create a CVS file to be loaded in mssql you must find a way to delete all the double-quotes. The only format which will accept those quotes is varchar but they will load just as good without them (that was point 2 at 2nd sight).

    P.S.#1 Did not get too much usefull google hits when I tried to figure this all out, hence this entry.
    P.S.#2 used syntax in mssql:
    Code:
    bulk insert dbo.dinges from 'G:\msSQL\dinges.csv' with (keepidentity , fieldterminator = ',' , rowterminator = '\n' );
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Why didn't you use SSIS (Integration Services)? It's way faster and can parse almost any format.

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by n_i View Post
    Why didn't you use SSIS (Integration Services)? It's way faster and can parse almost any format.
    Because I've never heard of it ... until now. Thank you.

    next time Gadget, next time.....
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    You can do some formatting in the db2 export command, have a look at modified by coldel and chardel


    /Lennart
    --
    Lennart

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You can also just export the data to a staging table where every column is varchar(max)..the use t-sql to audit and modify the data for the load to the base table

    MOO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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