Preparing to use query layers
To create a query layer on a spatial database, you must complete specific tasks to ensure the query layer works with ArcGIS. Some of these tasks vary depending on the type of database management system (DBMS) and the SQL spatial type you want to use.
- You must use a release of the DBMS that is supported by the ArcGIS version you are using. Check the database system requirements on the ArcGIS Resource Center for information on DBMS support.
- The query layer definition must include a unique, not null column or combination of columns that can be used as the object ID in the query layer. If the data you want to use does not have such a column or combination of columns, you must add one. See Choosing a unique identifier field for more information.
- Depending on the type of DBMS you are using, there is some configuration you must do in the DBMS to use spatial types.
- You must install and configure DBMS clients on each of the client computers from which you will create query layer connections. You can download a supported client for all databases except Netezza from the Esri Customer Care portal. Since you must be an administrator on your computer to install software, contact your information systems department to set up your computer for query layer connections if your login is not a member of the Windows Administrator group on your computer.
IBM DB2 databases
The DB2 database must be registered with DB2 Spatial Extender before you can use the ST_Geometry spatial type. Contact your GIS administrator or database administrator (DBA) if you are not sure if the database you are connecting to is enabled for ST_Geometry.
Install the DB2 client application on your client computers. You can configure the clients to connect to a specific DB2 database or use a Data Source Name-less (DSN-less) connection string when connecting from ArcGIS. You can download the DB2 client from the Esri Customer Care portal or obtain it from IBM.
See the IBM DB2 Database Information Center (http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?) for information on how to register the DB2 database with Spatial Extender and install and configure a DB2 client. See Database connections in ArcGIS for Desktop for example connection information.
IBM Informix databases
The Informix database you want to use must be registered with Informix Spatial DataBlade before you can use the ST_Geometry spatial type. Contact your GIS administrator or DBA if you are not sure if the database you are connecting to is enabled for ST_Geometry.
Install on your client computer the Informix IConnect application and configure the Setnet32 application. Unless you will be using a DSN-less connection string to connect to Informix, you must configure an Open Database Connectivity (ODBC) connection.
You can download the Informix client application from the Esri Customer Care portal or obtain it from IBM. See the IBM Informix Information Center (http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp) for information on installing and configuring the Informix client.
Microsoft SQL Server databases
Install on your client computer a supported version of SQL Server Native Client. This allows you to make a query layer connection. You can obtain the SQL Server Native Client from the Esri Customer Care portal or Microsoft.
See the Microsoft SQL Server documentation for installing the SQL Server Native Client.
Netezza data warehouse appliances
To access a spatial column in your query layer from a database on a Netezza data warehouse appliance, ArcGIS requires that the column be named Shape.
To connect to the Netezza database, you must obtain and install the Netezza SQL client and configure an ODBC connection on your client computer. When setting up the ODBC data source name, be sure to choose the option to Optimize for ASCII character set.
You can obtain the Netezza driver from IBM. See your Netezza documentation for information on installing and configuring the ODBC driver.
Oracle databases
Two spatial types are supported in Oracle that you can use in your query layers: ST_Geometry and SDO_Geometry.
The ST_Geometry spatial type is installed when you create an enterprise geodatabase in Oracle or can be installed in an Oracle database using the Create Spatial Type geoprocessing tool. You also need to configure the Oracle EXTPROC to use ST_Geometry. Your GIS administrator or DBA can tell you if your database has been configured to use this spatial type. For information on configuring the EXTPROC, see Configuring the Oracle listener to use SQL and your Oracle documentation.
Oracle Locator objects, including the SDO_Geometry type, are present by default in Oracle databases. Therefore, you can access the SDO_Geometry type without additional installations. However, to access all SDO_Geometry functions, you may need to install Oracle Spatial in the DBMS.
Install on your client computer the Oracle Net application. You can obtain the Oracle Instant Client from the Esri Customer Care portal or get the Instant or full client from Oracle.
For information on installing and configuring the Oracle client, see Oracle's documentation set.
PostgreSQL databases
Two spatial types are supported in PostgreSQL that you can use in your query layers: ST_Geometry and PostGIS geometry.
The ST_Geometry type is installed when you create an enterprise geodatabase in PostgreSQL or can be installed in a PostgreSQL database using the Create Spatial Type geoprocessing tool.
The PostGIS geometry type must be installed separately on the PostgreSQL database server. Once installed, the database you use must be created using the PostGIS template database or must be configured to use PostGIS so that it is enabled to store the PostGIS geometry type.
Contact your GIS administrator or DBA to determine whether your database has been configured to use either of these spatial types.
Place the PostgreSQL libpq in the ArcGIS bin folder on your client computer. The libpq files can be downloaded from the Esri Customer Care portal. See Setting up a connection to PostgreSQL for more information.