Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    3

    Unanswered: How to Insert txt file into SQL table(DB2)

    Hi can anyone help me with a sample code that i can use to insert a txt file into an SQL database table in DB2.
    Below is the file to be inserted:

    1,00:22,63,132,73,142
    1,00:50,116,155,126,165
    1,01:35,206,182,216,192
    1,02:01,233,197,243,207
    1,05:14,218,233,228,243
    1,07:44,244,261,254,271
    1,13:26,272,256,282,266
    1,18:14,285,228,298,239
    1,18:25,356,238,366,248
    1,18:57,496,346,506,356
    2,01:07,221,116,231,126
    2,01:18,232,74,242,84
    2,01:50,196,75,206,85
    2,02:01,129,123,139,133
    2,12:50,113,157,123,167
    2,13:20,107,183,117,193
    2,15:58,192,214,202,224
    2,16:45,207,185,217,195
    2,18:14,202,145,212,155
    2,20:22,150,130,160,140
    3,02:48,303,62,313,72
    3,04:25,361,94,371,104
    3,06:18,295,219,305,229
    3,09:46,356,239,366,249
    3,15:26,392,164,402,174
    3,17:20,429,182,439,192
    3,22:48,446,242,456,252
    3,22:14,507,230,517,240
    3,23:05,464,297,474,307
    3,23:48,423,355,433,365
    4,10:02,40,294,50,304
    4,12:18,59,320,69,330
    4,15:22,169,280,179,290
    4,15:58,194,216,204,226
    4,18:03,227,227,237,237
    4,18:14,285,229,295,239
    4,20:33,358,99,368,109
    4,20:45,416,127,426,137
    4,21:06,578,216,588,226
    4,22:04,539,359,549,369
    5,03:13,501,42,511,52
    5,03:15,426,56,436,66
    5,04:25,361,93,371,103
    5,06:51,539,189,549,199
    5,07:44,506,233,516,243
    5,07:48,401,263,411,273
    5,09:10,267,204,277,214
    5,12:14,310,125,320,135
    5,17:28,233,74,243,84
    5,18:44,193,74,203,84

    I have this command which can only insert one row at a time:

    HTML Code:
    INSERT INTO TABLE(ID, T_IME, GEOMETRY) 
      VALUES (1, '12:22',DB2GSE.ST_Point('point(63 132,73 142)',1))
    How can i modify this code to insert it all the above data at once.
    Thanks

  2. #2
    Join Date
    Nov 2008
    Posts
    3

    Quering intersection of points

    Thanks. I manage to insert the whole row one by one using the insert command.
    My problem now is to query where two points intersect. Here is my command:

    HTML Code:
    SELECT a.ID, b.ID, CAST(DB2GSE.ST_AsText(DB2GSE.ST_Intersection(a.geometry, b.geometry)) 
    		 as VARCHAR(150)) Intersection
    FROM ADB5681.MLTABLE  a, ADB5681.MLTABLE  b
    But i am having the following error:
    [quote]DB2 Database Error: ERROR [38SSR] [IBM][DB2/NT] SQL0443N Routine "DB2GSE.ST_INTERSECTION" (specific name "ST_INTERS") has returned an error SQLSTATE with diagnostic text "GSE3427N Incompatible SRSs.". SQLSTATE=38SSR
    [/HTML]

    The problem is a compatibilty issue of B2GSE.ST_INTERSECTION
    Can anyone help please
    Thanks

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by kolaG
    Below is the file to be inserted:
    1,00:22,63,132,73,142
    1,00:50,116,155,126,165
    ...
    [CODE]INSERT INTO TABLE(ID, T_IME, GEOMETRY)
    VALUES (1, '12:22',DB2GSE.ST_Point('point(63 132,73 142)',1))
    [/HTML]
    How can i modify this code to insert all the above data at once.
    Use e.g. perl to convert the input to a sequence of valid INSERT statements:
    Code:
    perl -pe 's/(\d+),(\d\d:\d\d),(\d+),(\d+),(\d+),(\d+)/INSERT INTO TABLE(ID, T_IME, GEOMETRY) VALUES ($1, '\'\$2\'', DB2GSE.ST_Point('\''point($3 $4,$5 $6)'\'', 1)) ;/;' my_file.txt | db2 -tm
    where "my_file.txt" is the name of the file containing the given lines.
    Last edited by Peter.Vanroose; 11-09-08 at 16:07.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The geometries that you inserted use a different Spatial Reference System (SRS), which explains the error message. What I cannot tell is why you use different SRSs. Can you show us the INSERT statements?
    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
  •