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

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

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

It looks like it has to do with spatial extenders (db2gse). Do a select from syscat.datatypes and see what is there.

Andy

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..

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

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..

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