topic Re: Control file for asc2sde in Data Management Questions

Control file for asc2sde

CliffVaughn — Wed, 26 Sep 2012 14:44:27 GMT

Successfully extracted feature class data from Oracle 10g with sdequery.
Created the feature class empty on our Oracle 11g database. Will be stored as ST_GEOMETRY.
asc2sde asks for a Control File and I need creating one.
The table is STREET_SPEED_POLY and description is:
COMMENT_DESC                VARCHAR2(200 BYTE)      nullable
DAY_PLAN_OID_NBR            NUMBER(10,0)               nullable
OBJECTID                          NUMBER(38,0)               NOT NULLABLE
ON_AREA_CD                     VARCHAR2(10 BYTE)       nullable
SHAPE                              NUMBER(38,0)               nullable
STEM_SPEED_CD                VARCHAR2(10 BYTE)       nullable
GEOCODED_STATUS_CD       NVARCHAR2(4 CHAR)      nullable
DATE_TMSTP                     DATE                           nullable
SYMBOL_CD                        CHAR(1 BYTE)              nullable
TIME_SEGMENT_OID_NBR     NUMBER(10,0)               nullable

Re: Control file for asc2sde

VinceAngelo — Wed, 26 Sep 2012 16:18:59 GMT

If you use the CTL or CTL=path options on export with sdequery, you won't need to
create one for import. If you have the (empty) table in the target database, you can
export (with a '-w 1=0' if it isn't empty) to make a new control file from the target
table.

- V

Re: Control file for asc2sde

CliffVaughn — Wed, 26 Sep 2012 16:34:06 GMT

I used sdequery to create the Control File, but when trying to load with asc2sde I get an error on the coordinate system.

asc2sde -o init -l STREET_SPEED_POLY,SHAPE -i sde:oracle11g:/:schema -f Data/STREET_SPEED_POLY.asc -u username -p password@ddb -D host.com -C STREET_SPEED_POLY.ctl

ASCII to ArcSDE 9.3 Loader Utility       Wed Sep 26 17:28:20 2012
------------------------------------------------------------------------
Error: Unrecoverable insert error failure
        Reason -> The given coordinate references are incompatible (-138)

Results:
        Records read: 1
        Rows created: at most 0
        Elapsed time: 392.17 ms

-bash-3.2$ cat STREET_SPEED_POLY.ctl
# Auto-generated file - created Wed Sep 26 11:15:26 2012

DELIMITERS              "|\r\n"
SKIP                    1

COORDREF_XY             -16688100.000000,-9068200.000000,10000.000000
COORDSYS                PCS_NAD_1983_N_AMERICA_ALBERS
EFLAGS                  "a+"
REGISTER                OBJECTID(SDE)

#column_name            class_name            start width nulls_allowed
COLUMNS
DAY_PLAN_OID_NBR        INT32                   -    10         Y
TIME_SEGMENT_OID_NBR    INT32                   -    10         Y
STEM_SPEED_CD           STRING                  -    10         Y
ON_AREA_CD              STRING                  -    10         Y
COMMENT_DESC            STRING                  -   200         Y
SHAPE                   SHAPE                   - 5000         Y
OBJECTID                INT32                   -    10         N
END

The properties in ArcCatalog on the source and target DBS:

Projection: Albers
False_Easting: 0.000000
False_Northing: 0.000000
Central_Meridian: -96.000000
Standard_Parallel_1: 20.000000
Standard_Parallel_2: 60.000000
Latitude_Of_Origin: 40.000000
Linear Unit: Meter (1.000000)

Geographic Coordinate System: GCS_North_American_1983
Angular Unit: Degree (0.017453292519943295)
Prime Meridian: Greenwich (0.000000000000000000)
Datum: D_North_American_1983
Spheroid: GRS_1980
    Semimajor Axis: 6378137.000000000000000000
    Semiminor Axis: 6356752.314140356100000000
    Inverse Flattening: 298.257222101000020000

I used sdeexport on the 10g database:

sdeexport -o create -l STREET_SPEED_POLY,SHAPE -i 5152 -f STREET_SPEED_POLY.sdx -u user -p pwd -w "where rownum < 1"

and sdeimport on the 11g database to create an empty feature class:

sdeimport -o create -l STREET_SPEED_POLY,SHAPE -i sde:oracle11g:/:schema -k RDS_TRAIN -f STREET_SPEED_POLY.sdx -u user -p pwd@ddb

Thank You for the Help!

Re: Control file for asc2sde

VinceAngelo — Wed, 26 Sep 2012 18:10:40 GMT

You'll need to make sure the coordrefs agree, or eliminate the COORD* keys
from the file and let 'asc2sde' to use the target layer coordref for import.
'sdelayer -o describe_long' will tell you what the target is expecting.

If you can pass 'sdeexport' files, why are you bothering with sdequery|asc2sde?

- V

Re: Control file for asc2sde

CliffVaughn — Wed, 26 Sep 2012 18:23:54 GMT

I need a way to move several large feature classes in Production. SDEEXPORT/SDEIMPORT takes too long and our ESRI rep. suggested trying sdequery/asc2sde to see if they are faster. Any help in this reguard would be greatly appreciated.

I commented out the values provide by the CTL option, but the feature class doesn't display the same in the target as the source:
#COORDREF_XY             -16688100.000000,-9068200.000000,10000.000000
#COORDSYS                PCS_NAD_1983_N_AMERICA_ALBERS

Below is the output with the -o describe_long option. What are the correct COORDREF_XY and COORDSYS values based on the input below?

-bash-3.2$ sdelayer -o describe_long -l STREET_SPEED_POLY,shape -i sde:oracle11g:/:schema -u user -p pwd@ddb

ArcSDE 9.3.1 for Oracle11g Build 1632 Thu Feb 26 12:05:37 2009
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: STREET_SPEED_POLY Feature Class for ROADS Release 3.9
Table Owner ..........: RDS_TRAIN_SCHEMA
Table Name ...........: STREET_SPEED_POLY
Spatial Column .......: SHAPE
Layer Id .............: 1
SRID .................: 1
Minimum Shape Id .....: 1
Offset ...............:
falsex: -16688100.000000
falsey:   -9068200.000000
System Units .........:      10000.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.001
Spatial Index ........:
parameter:    SPIDX_GRID,GRID0=8000,GRID1=24000,FULL
exist:        No
array form:   8000,24000,0
Layer Envelope .......:
minx:   1140303.01890,        miny:    212911.30030
maxx:   1144508.57780,        maxy:    228244.83160
Entities .............: a+
Layer Type ...........: Extended SQL Type
Creation Date ........: Wed Sep 26 13:50:37 2012
I/O Mode .............: LOAD
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT, UPDATE, INSERT, DELETE
Coordinate System ....: PROJCS["North_America_Albers_Equal_Area_Conic",GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Albers"],PARAMETER["False_Easting",0.0],PARAMETER["False_Northing",0.0],PARAMETER["Central_Meridian",-96.0],PARAMETER["Standard_Parallel_1",20.0],PARAMETER["Standard_Parallel_2",60.0],PARAMETER["Latitude_Of_Origin",40.0],UNIT["Meter",1.0]]

Layer Configuration ..: RDS_TRAIN

Re: Control file for asc2sde

VinceAngelo — Wed, 26 Sep 2012 18:37:07 GMT

Another thought - How old is your se_toolkit release?

'asc2sde --version' or 'sdequery --version' will tell you the toolkit release and build.

I did a bunch of work on synchronizing coordinate references between the coordref
that was requested (possibly with BASIC precision), and the coordref that was actually
created (not always the same, due to constraints outside my control). Those changes
were folded into the baseline a year ago.

- Vince

Re: Control file for asc2sde

CliffVaughn — Wed, 26 Sep 2012 19:36:36 GMT

On my source Oracle 10g database:
----------------------------------
> asc2sde --version
asc2sde 9.3 (Build 41) - ArcSDE 9.3.1sp2 (HP/UX [64-bit])
rdsdeva:/opt/esri/arcsde>
> uname -a
HP-UX db1 B.11.23 U 9000/800 2102886750 unlimited-user license

On my linux Application server I am using to access my Oracle 11g database:
--------------------------------------------------------------------------

-bash-3.2$ asc2sde --version
asc2sde 9.3 (Build 41) - ArcSDE 9.3.1sp0 (Linux [64-bit])
-bash-3.2$ uname -a
Linux host 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:48 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
-bash-3.2$

Re: Control file for asc2sde

VinceAngelo — Wed, 26 Sep 2012 19:48:53 GMT

That's the most recent, though it's disturbing that the Linux host is using an older ArcSDE
(unless the setk build is just older, but it works out to be the same).

So now it's just down to specifying the same coordref or letting it default to the target layer.

- V

PS: setk101b10 is about to be released; it has coordref compatibility testing for 'sdeupdate',
which I should probably back-port to 'asc2sde' (in my copious free time).

Re: Control file for asc2sde

CliffVaughn — Thu, 27 Sep 2012 12:02:43 GMT

I commented out the values provide by the CTL option, but the feature class doesn't display the same in the target as the source:
#COORDREF_XY -16688100.000000,-9068200.000000,10000.000000
#COORDSYS PCS_NAD_1983_N_AMERICA_ALBERS

1. Can you provide the appropriate values based on the information I provided earlier?

2. How can I verify that the feature classes are exactly the same in my Oracle 10g and Oracle 11g databases?

3. Do you have other suggestions on how to move the feature classes between the 2 instances in the shortest amount of time, insuring they are exactly the same?

Thanks

Re: Control file for asc2sde

VinceAngelo — Thu, 27 Sep 2012 12:47:19 GMT

I can't provide more than I have with the information available.

se_toolkit actually contains the tools I use to verify database contents (+DIGEST
option to sdequery), but working with perfect hashes isn't in the documentation,
so you'd need to review the source to see how to use it.

If both databases are available from a common host, then there are dozens of ways
to transfer the contents of a table. It's only on air-gapped systems that the process
becomes more difficult. I would suggest that you use just one mechanism, though,
so either use 'sdeexport' and 'sdeimport' to transfer the data, or 'sdequery' and
'asc2sde' for transfer, or just drag-and-drop with Desktop, because there seems
to be a problem with transferring the empty template with one then transferring
the data with another (though this certainly shouldn't be the case, and I've done
it plenty of times before, so the concept is sound, there's just an issue with the
implementation).

- V