Results 1 to 8 of 8

Thread: prob withexport

  1. #1
    Join Date
    Nov 2007
    Posts
    27

    Unanswered: prob withexport

    i have one prod table like this
    create table t1(
    col1 db2.sha
    col2 integer not null)

    and another table

    create table t2(
    col1 integer not null
    col2 db2.sha)

    my question is when i do an export like
    db2 "export to t1.del of del select * from table1".

    how do i do import into table2 since the positions are
    different.using method option can we do it

    and i am not able to do the export i am getting the following error
    SQL3529N The "EXPORT" operation encountered the unsupported data type "DB2
    .Sha" in column "30".
    i think the db2.sha is a user defined data type.
    does the export command supports only the built in data types?
    my database is v8.1 fp 12 solaris

    is there any other way to export data from table other than
    export comand

    any help is very appreciated
    thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    On the export have you select put the order of columns in the order you need for import:


    You will also need to convert the UDT to its base type

    export to t1.del of del select col2,fcn(col1) from t1

    where fcn is the method to convert the data.

    Andy

  3. #3
    Join Date
    Nov 2007
    Posts
    27
    thanks andy for your response

    when i took the ddl for database i found the following

    ---------------------------------
    -- DDL Statements for distinct types and/or Abstract Data Types
    ---------------------------------

    CREATE TYPE "DB2 "."sha" UNDER "DB2 "."pta"
    INSTANTIABLE
    INLINE LENGTH 659
    WITHOUT COMPARISONS
    NOT FINAL
    MODE DB2SQL;


    is this a user defined data type? if so what is its base datatype?.
    or is it a function?



    the table ddl is like this

    create table t1(
    sha db2.sha
    col2 integer not null)

    when i described the table its like

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------
    SHA DB2 sha 0 0 Yes

    here its length is 0 so i wonder what kind of datatype is this?.

    andy can you explain me in little detail in doing a export with this kind of data type

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It looks like it has to do with spatial extenders (db2gse). Do a select from syscat.datatypes and see what is there.

    Andy

  5. #5
    Join Date
    Nov 2007
    Posts
    27
    sorry andy its actually db2gse.st_point

    its actually like this
    CREATE TYPE "DB2GSE "."ST_POINT" UNDER "DB2GSE "."ST_GEOMETRY"
    INSTANTIABLE
    INLINE LENGTH 659
    WITHOUT COMPARISONS
    NOT FINAL
    MODE DB2SQL;

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------
    SHAPE DB2GSE ST_POINT 0 0 Yes



    the table ddl is like this

    create table t1(
    "SHAPE" "DB2GSE "."ST_POINT"
    )
    i have no idea on doing EXPORT with spatial extenders Andy can u plz give me some details on doing this

    Thanks..
    Last edited by rajaryan4545; 01-31-08 at 14:43.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Instead of doing the select * from table in the export, you will need to convert the ST_POINT. You can do this one of two ways.

    1) select col1,col2,...,db2gse.st_x(shape),db2gse.st_y(shape )
    2) select col1,col2,...,db2gse.st_asText(shape)

    Personally I prefer the first one.

    Andy

  7. #7
    Join Date
    Nov 2007
    Posts
    27
    hi Andy
    when i am using db2gse.st_x(shape),db2gse.st_y(shape) in the export command
    i am getting the value of shape as the following
    -1.22593805000000E+002,+4.70851990000000E+001

    when i am using the db2gse.st_asText(shape) i am getting the value of shape as the following
    POINT (-122.593805 47.085199)

    but the actual shape value in the table is as follows
    x'01010000000000000900000000000000B3CBDF368F84DE82 0130303000000001C05EA600E6AFCCE240478AE7CD035370C0 5EA600E6AFCCE240478AE7CD03537030303000000001C05EA6 00E6AFCCE240478AE7CD035370C05EA600E6AFCCE240478AE7 CD035370'


    and also when i am trying to import into target table i am getting the following error

    import from add_spe2.del of del insert into abc.xyz

    SQL0408N A value is not compatible with the data type of its assignment
    target. Target name is "SHAPE". SQLSTATE=42821


    1) how to get the same data values for shape column as in the table and the exported result

    2)and also andy can u plz tell me how to do import.

    3) can you tell me how to create a shape file

    your help is very appreciated.

    thanks..

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not think you are going to be able to use IMPORT to load the data directly into the table. I think you are going to have to use the first method to export using st_x and st_y. Then create another table just like like the one you want to import into but instead of the column SHAPE have 2 columns x and y. Then import into that table. When the import is complete you can then move the data to the original table using: insert into origTable select col1,col2,db2gse.st_point(x,y)


    Andy

Posting Permissions

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