The Simplified Geodatabase Schema in ArcGIS 10

The GDB dev team has put some time into a series of posts on our beta blog lately regarding the new simplified geodatabase schema coming at ArcGIS 10. While that blog is for beta user eyes only, we thought that there was a valuable story being told which should be shared publically, especially given that it’s the same story we’ll be giving at the Dev Summit next week in Palm Springs.

So here it is, The Simplified Geodatabase Schema part one of three:

Consolidation of the Geodatabase system tables

The geodatabase team has done some work under the hood for the 10 release and has restructured the geodatabase schema. At ArcGIS 10 we have amalgamated all of the information related to the schema such as feature classes, domains, subtypes, etc…. This information, which was previously stored in the 36+ geodatabase system tables (the tables prefixed with “GDB_”) has been consolidated into four main tables:

  • GDB_ITEMS
  • GDB_ITEMTYPES
  • GDB_ITEMRELATIONSHIPTYPES
  • GDB_ITEMRELATIONSHIPS

The majority of the information is now stored primarily within an XML column, the Definition field in the GDB_Items table which is shown here:

How will this change affect you?

Unless you have custom code you’ve written in an application directly accessing the system tables it won’t affect you. The way you work with the geodatabase in ArcMap, ArcCatalog and ArcObjects remains the same. Like I said, this was really a behind-the-scenes change that most users won’t notice.

So why did we do this?

Because consolidating geodatabase system tables rocks! Here’s why:

 

 

 

 

 

 

 

The above double necked Gibson SG bullets were graciously lent to the GDB dev team by guitar legend Jimmy Page

Some additional notes about the new geodatabase schema:

  • All new and upgraded file, personal, and ArcSDE geodatabases will have the new geodatabase schema.  Because of this consolidation of the system tables, ArcGIS 10 geodatabases will not be accessible from previous releases of ArcGIS. When planning your organization’s migration strategy to 10 this is an important point to consider.
  • The upgrade does not affect the underlying data within the geodatabase. For example, while the information stored in the geodatabase system tables with respect to how feature classes are stored changes, the data in the underlying feature class remains unaffected.  In addition, none of the ArcSDE repository tables (the tables prefixed with “SDE_”) are part of the consolidation.
  • To implement this new schema in an ArcSDE geodatabase, the database must be able to use XML columns.  For Personal and File geodatabases this is handled by ArcGIS, users do not need to configure XML support for these types of geodatabases.  For ArcSDE geodatabases there are instructions on how to configure this for each DBMS. Additionally, existing ArcSDE geodatabases must be upgraded using the new Upgrade Geodatabase geoprocessing tool or Python script instead of the Post Installation wizard or the sdesetup command (we’ll add a post about this upgrade process soon).
  • There is no change needed to existing applications which access the Geodatabase through ArcObjects.  The consolidation of the system tables should be seamless and not require any code changes for ArcObjects applications.

This post was contributed by Craig Gillgrass, product engineer on the geodatabase team

This entry was posted in Geodata and tagged , , . Bookmark the permalink.

Leave a Reply

23 Comments

  1. curtvprice says:

    I like the guitar metaphor, as geodatabases rock with better tuning.

    Fewer tables to deal with should help with that, and also make it a lot more doable to expose the GDB in a form acessible to mere mortals (like me) through python objects.

  2. brentardenpierce says:

    Curt,

    Glad you liked the guitar metaphor, love the tuning comment. Very true for both, as anyone who has heard Jonathan play guitar could attest.

    As for your python comment, most GP tools work with a geodatabase and we are definetly trying to add/augment tools to make python one of the key development tools which can be used to work with and manage geodatabases.

    Thanks for the comments,
    -Brent

  3. mdumka says:

    Any word on the release of the File Geodatabase API? Is it going to beta this year?

    Thanks,
    Mark

  4. JonMurphy says:

    Yup, it’s pretty much ready to roll. A bit more porting and testing, but expect an early Xmas.

  5. sherriekubis says:

    Jon,
    Our SDE app server is Linux 64-bit hitting Oracle Linux 11G RAC databases. As you stated, previously an upgrade was done with applypatch and then sdesetup -o upgrade. I must now use the Update Geoprocessing tool or a Python script (that access arcpy). This should be done from the server where the SDE app server is installed.

    If this is true, that now means I need to also install ArcGIS Server (since there isn’t ArcGIS Desktop for Linux) on the Linux machine just to upgrade SDE? This can’t be right, or at least please tell me it’s not right.

    If the answer is to install the binaries on to the Linux app server and then upgrade from desktop using a direct connect, it’s not clear how the binaries on Linux are accessed, or involved. What if this were a patch strictly for Linux? Using this manner also upgrades the SDE schema(s) (we are using master and user schema gdbs) from a different binary set than the app server is running, perhaps causing problems down the road. It’s sorta mixing apples and oranges to get to the end result of a SDE schema upgrade and app server that is consistent.

  6. JonMurphy says:

    We realize the upgrade at 10.0 is a lot different from previous releases and will cause some changes to your upgrade procedures. However, these changes are necessary for new functionality and capabilities that we have planned. As I’m sure you’re aware, the upgrade process updates existing system tables, functions, procedures, types and creates new ones when needed. At ArcGIS 10, we’ve changed the geodatabase system tables, consolidating the information previously stored in the geodatabase system tables into six tables. This is done partly by using XML columns to store information related to the data in the geodatabase. This was done for a number of reasons; but the main reasons were to provide:

    1. More open access to all the contents within a geodatabase
    2. Better forward compatibility for geodatabases in future releases so that older clients can connect to newer releases of the geodatabase

    The upgrade does need to be run from a machine that has ArcObjects installed on it. This means either running it from a Windows machine with Desktop, Engine or Server or from a Linux machine with Engine or Server installed. We’ve updated the steps on how to prepare your Oracle database for upgrade to highlight some of the steps needed for Linux:

    http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Preparing_to_upgrade_a_geodatabase_in_Oracle/002n000000p0000000/

  7. tomlind says:

    “The upgrade does not affect the underlying data within the geodatabase…”

    This sounds promising to us. We are having serious problmes after upgrading from ArcSDE 9.3.1 sp1 to ArcSDE 10.0 sp1 on oracle 11G.

    We are trying to get back to the 9.3.1 state again. The plan is to replace the upgraded SDE schema with the SDE-schema exported before the upgrade. Not having to consider all the data in user schemas would be a big help.

    Has this a chance of succeding? Is there any more documentation of what is done by the Upgrade Geodatabase tool, or if existing feature classes etc are affected?

    Thanks,
    Tomas Lindberg

  8. JonMurphy says:

    Tomas,

    While the upgrade does not affect the underlying data within the geodatabase, it will change the GDB_ system tables and depending on your database and configuration, there could be additional changes to other database objects such as indexes and triggers. For this reason, we cannot recommend only backing up or restoring only the SDE-schema. This is one of the reasons our documentation for upgrade mentions database backups before proceeding.

    More importantly, we’d like to understand what sort of serious problems you are having after upgrading from ArcSDE 9.3.1 sp1 to ArcSDE 10.0 SP1 on oracle 11G.

  9. tomlind says:

    Thanks for your answer!

    You are right, only restoring the SDE schema does not work (we looked into it on a development environment, we have not touched the production server so it is still available for investigation)

    We have contacted our swedish support organistation and they should have contacted esri inc. I don’t have a ersi inc. ticket number yet though…

    In short:
    Upgrading ArcSDE 9.3.1 sp1 to ArcSDE 10.0 SP1 on oracle 11G 64-bit on redhat linux 64-bit. At the same time migrating from Arcsde 32-bit to ArcSDE 64-bit. Upgrade geodatabase in ArcCatalog reported success, a few warnings on tables in GDB_Objectclasses that did not exist in database (skipping).

    ArcSDE starts OK.
    Reading and displaying data works in ArcCatalog and ArcMAP.
    ArcCatalog crashes directly when marking any feature class and right-clicking properties.
    ArcCatalog crashes directly when marking the database connection, right-clicking Import ->New feature Class..
    ArcMap crashes when browsing to ArcSDE to define output destination in tools.
    ArcMAP crashes when adding annotation feature classes (originally created with import coverage annotation).

    The same behaviour appears when using direct connect or application server.

    Some unexpected answer from Oracle to the client? Something corrupt in the repository? Have not found anything in intercept logs so far..tried to enable sdetraceloc but no log appeared.

    Any tips would be greatly appreciated.

    /Tomas

  10. JonMurphy says:

    The issue you’re describing sounds similar to a known issue; coded value domains that contain coded values missing descriptions are not flagged during Upgrade. We have a fix for this issue in 10.0 SP2, which is currently in certification. The easiest way to determine if this is the same issue is for our Technical Support to get a copy of your backup in house for testing. If we cannot get a copy of the backup, a copy of the upgrade geodatabase is sufficient. Let us know what the Redlands tech support incident is and we can take a look at the issue.

  11. tomlind says:

    Thanks,
    We’ve have received an “Esri Incident #912234″.

  12. tomlind says:

    Regarding “Esri Incident #912234″.

    “coded value domains that contain coded values missing descriptions”.
    I have tried to verify this and I find 8 coded value domains with code 0 without description. (right-clicking the arcsde connection–>properties–>domains and checking the contents of the domains in the 9.3.1 geodatabase before upgrade).

    What does the fix do? Will it fix out “corrupted” upgraded geodatabase, or only fix the upgrade process, so that we have to run the upgrade again?. We would really need a fix for the upgraded geodatabase:).

    Is there a “manual” fix that we could try?

    /Thanks

  13. JonMurphy says:

    It sounds like you are running into the issue with coded value domains that contain coded values that are missing descriptions. The fix we have for this issue in 10.0 SP2 is to find and flag these domains during the Upgrade process and notify the user of the issues. The Upgrade process is then cancelled and the user must manually correct the domains.

    Since you’ve already identified the domains that have this problem, you don’t need the fix, but instead can correct the domains. You can correct the domains in one of the following ways:
    - Delete the coded value domain itself
    - Delete the coded value that is missing a description
    - Enter a description for the coded value where one is missing

  14. tomlind says:

    Only problem is ArcCatalog crashes when i right-click properties on the database connection, or give the sde instance as workspace when using the tools…

    So we have other problems too…?

    Is there a way of deleting domains outside of arccatalog?

    /Tomas

  15. JonMurphy says:

    Against which release of the geodatabase and which client release (10.0 or 9.3.1) are you using when it crashes?

    ArcCatalog should not crash when using a 9.3.1 client to connect to the 9.3.1 geodatabase. You should then be able to correct the domains.

  16. tomlind says:

    ArcCatalog 10.0 sp1 crashes against ArcSDE 10.0 sp1 (our corrupted production database).

    ArcCatalog 9.3.1 does not crash against ArcSDE 9.3.1. This however is a duplicated geodatabase from a backup before the upgrade and not our production database. We can fix domains here, but right now it will not solve our problem.

    A lot of non-geodatabase (non-ESRI) production is going on in the same database instance, and we did not shut down this production during the upgrade. The possibility to completely roll back the database is gone, that is why we would really need a fix for the ArcGIS 10 environment.

    Another way would be to “remove” or “uninstall” SDE from our corrupted production database, reinstall a fresh empty ArcGIS 10 geodatabase, and then import the data from the ArcGIS 9.3.1 geodatabase.

    Is there a way to cleanly remove or “uninstall” the SDE-schema? We have tried on our development server but it does not seem trivial, at least not for us:)

    Thanks,

  17. JonMurphy says:

    At this point, the issue has gotten beyond what I can help you with through the Blog. You have an incident logged with Technical Support, that should be the mechanism that is used to investigate this issue.

    We’ll provide instructions for what we need through the Technical Support incident.

    Thanks

  18. tomlind says:

    Agree, thanks for your help.

  19. cafail says:

    Can anyone explain the (Spatial) index on the GDB_ITEMS table? In the 9.x world, we ran a SQL script each night to re-index all the tables in the DB (it was written by an ESRI person years ago!). Since going to 10, it fails because it cannot find the S*_idx table (the one attached to GDB_ITEMS.

    Any insight is appreciated!

    Thanks to all.

  20. mmestrov says:

    I’m noticing when I bring my address model over from 9.3.1 to 10 that any layers in my model associated with a spatial layer is not displaying its forward or backward label correctly. Instead it substiutes the name of the layer when I’m eding attributes. It makes it difficulty since I dont’ know what the forward or backward relationship is. I have dug into the GDB tables and found where the XML is stored. In there the Forward and Backward label tags are correct, but they are not displaying in the attribute window when I’m editing a feature associated with the relationship. IS this a bug. All my nonspatial tables in the relationships are displaying their labels correctly. Only seems to be spatial layers.

  21. vzr5t3 says:

    We store the FeatureClassID and ObjectID in some business tables where we need to relate data to sdeadmin features. When we create a new featureclass, we used to look up the FeatureClassID in the sde.GDB_ObjectClasses table, ID field. Now that we have converted to ArcGIS 10, how can I lookup the FeatureClassID on a new featureclass in the four new GDB tables?

  22. vzr5t3 says:

    I can answer my own question, though I do have another question. Here is the answer:

    1) Grab the UUID value from GDB_ItemTypes for Name = Feature Class.
    2) Filter GDB_Items for Type = UUID value to only see Feature Classes.
    3) Find the row where the Name matches the name of the new Feature Class. The ObjectID field contains the FeatureClassID.

    Here is new question – How to I register a custom class extension? We used to grab the GUID and put it in the EXTCLSID column in the GDB_ObjectClasses.

    • cgillgrass says:

      We don’t recommend that you directly edit the GDB_Items tables and the contents of the Definition column. For registering a class extension, the best way to do this is through ArcObjects, that way you’re ensured it will work correctly.