ArcGIS Help 10.1 - What's new for databases in ArcGIS 10.1

At ArcGIS 10.1, you can connect to and work with databases directly from the Database Connection dialog box. The data in the database to which you have access is listed under the connection.

Once connected, you can create database users, load data to existing tables, add or drop tables, map spatial data, and publish services. To edit data, you can publish an editable feature service to ArcGIS Spatial Data Server, then edit through the service in ArcGIS for Desktop or a web application. See A quick tour of working with databases for more information.

Database connections from ArcGIS

Create connections

At ArcGIS 10.1, the Database Connection dialog box has been simplified to require less input to make a connection, and functionality has been expanded to allow you to connect to any supported database, even if it does not contain an enterprise geodatabase. Supported database platforms include the following:

IBM DB2
Informix
Microsoft SQL Server
Windows Azure SQL Database
Netezza
Oracle
PostgreSQL

First, install the database client for your database management system (DBMS) to connect to a database from ArcGIS. You can obtain the database client from your DBMS vendor, or for all databases except Netezza, you can download the database client from the Esri Customer Care portal.

Next, in ArcGIS for Desktop, open the Database Connection dialog box, choose the database you want to connect to, then provide connection information. The following is an example of connecting to a PostgreSQL database:

Database Connection dialog box

Access this dialog box from the Database Connections node (formerly the Spatial Database Connections node) in the Catalog tree. For more information on database connections, see Database connections in ArcGIS for Desktop.

Administrators can create a connection file that contains the necessary information and share it with other users. See Preconfiguring connection files for more information.

In addition, the new Create Database Connection geoprocessing tool allows you to create database connections. It takes the same inputs as the Database Connection dialog box. This tool can be found in the Workspaces toolset of the Database Administration toolbox.

Because you can now connect to databases from the Database Connection dialog box or Create Database Connection tool, the Add OLE DB Connection dialog box has been removed from the Catalog tree. However, you can add this tool to ArcCatalog if you need it. See Adding OLE DB connections for more information.

Update password on connection

If your database password is set to expire, ArcGIS 10.1 will intercept the warning or error returned from the database and provide you with a dialog box allowing you to create a new password.

Install the ST_Geometry storage type in an Oracle or PostgreSQL database

A new geoprocessing tool—Create Spatial Type—is available to let you install the ST_Geometry storage type and all its subtypes and functions in your Oracle or PostgreSQL database. This allows you to use the ST_Geometry type to store and access vector data without having to create a geodatabase.

Create a database user to own data

Two new geoprocessing tools are available to help you manage users in your database or enterprise geodatabase: Create Database User and Create Role.

The Create Database User geoprocessing tool lets you add a user to a database in Oracle, PostgreSQL, or SQL Server. The user that gets created is automatically granted the privileges required to create objects in the database. If a database role already exists to which you want to add this user, you can do that at the time of user creation.

In PostgreSQL, the login role is created in the database cluster and a corresponding schema is created in the specified database.

In SQL Server, a login is added to the SQL Server instance, a user is created in the specified database, a corresponding schema is created in the database, and the specified database is set as the user's default database. If an operating system or network login already exists in the SQL Server instance, you can use the Create Database User tool to add that login to the database you specify and create a schema for it.

In Oracle, the user is created in the Oracle database. if an operating system or network login already exists with that name, you can use the Create Database User to create a database user to which that login gets mapped in the specified Oracle database.

The Create Role geoprocessing tool lets you create a database role in Oracle, PostgreSQL, or SQL Server. With the same tool, you can add a user or list of users to or remove a user or list of users from a database role.

Load data to a database

From ArcGIS for Desktop, you can copy datasets from databases or geodatabases and paste them into a database, create new tables and feature classes in the database, create views on database tables, and load records to database tables using the Simple Data Loader in ArcCatalog or the Catalog window in ArcMap, or using the Copy Features, Copy Rows, or Append geoprocessing tools. See A quick tour of working with databases for more information.

Work with database data

You can map spatial data from your database without having to create a query layer. You can drag spatial tables from your database connection in the Catalog window in ArcMap to the table of contents. If needed, you can specify a unique identifier field and spatial reference for the data, and the data is displayed in the map.

Once you create a map that contains your database data, you can publish that data to ArcGIS for Server. If you want to edit the data, you can publish an editable feature service to ArcGIS Spatial Data Server.

You can also perform the following operations on tables that you own in the database:

Rename tables.
Delete tables.
Add, delete, or rename fields.
Truncate tables.
Grant or revoke privileges to other users or groups.
Add a database-maintained ID field. An incrementing ID field maintained by the database is required if you want to edit the data through a feature service published to an ArcGIS Spatial Data Server.

Create a database view from ArcGIS for Desktop

You can create views on tables or feature classes in a database by right-clicking a database connection under the Database Connection node in the Catalog tree, pointing to New, then clicking View. On the dialog box that opens, provide a name for the view and construct an SQL query to define it. See Creating a database view in ArcGIS for Desktop for more information.

There is also a new geoprocessing tool for creating database views: the Create Database View tool. You can use this tool or a Python script to define views in your database or enterprise geodatabase.

Create query layers using a geoprocessing tool or Python script

You can continue to create and use query layers to access your database data from ArcMap. At ArcGIS 10.1, there is a new geoprocessing tool that allows you to create them: the Make Query Layer tool.

This tool can be found in the Layers and Table Views toolset of the Data Management toolbox.

Rebuild indexes and update database statistics for database tables

Table owners can use the Rebuild Indexes geoprocessing tool or a Python script to rebuild existing indexes on their tables. The tool can be used to rebuild the indexes on multiple tables at once.

To update database statistics for database tables, the owner of the tables can run the Analyze Datasets geoprocessing tool or a Python script.

What's new for databases in ArcGIS 10.1