Setup DB2 and sample data
- Note: this recipe uses the database name STLEC1 and the database connection userid SYSADM. Change these values in the examples to your database name and userid.
- Download and unzip the sample data to a convenient directory.
- Open a DB2 command window where you can execute SQL statements. If you are using a remote database, make sure it is cataloged locally. You can use the scripts catalog-luw.sql or catalog-zos.sql as appropriate and make changes for the remote server location.
You can execute the script with a command like:
db2 -tvf catalog-zos.sql
- Import sample spatial for banks, customers and San Jose streets.
– Modify the scripts import-luw.sql or import-zos.sql as appropriate to change the userid in the stored procedure. (The scripts contain descriptions of each of the steps and any modifications needed.
– Execute the script with a command like:
db2 -tvf import-zos.sql
– Check that there were no processing errors
- Verify the imported data with the SQL statement (change “osuser” to your connection userid):
db2 select name, street, varchar(db2gse.st_astext(geom),32) from osuser.banks
which should return the name, street address and location of the Meridian and San Carlos branches
Install and setup ArcGIS Desktop
If you already have ArcGIS Desktop, you are all set.
If you don’t, a 60-day full-function trial is available for download. It is straightforward to setup an account, download and install the product.
If you are not familiar with ArcGIS Desktop, you should work through the ArcMap Get-Started to gain familiarity with adding and symbolizing geospatial data before continuing on below to add data from DB2.
Connect to the database and add data
Start up an ArcMap session with an empty workspace. Click on the Add Data icon and select Database Connections.
Then click Add Database Connection which will display the dialog below to select the appropriate database provider, either DB2 or DB2 for z/OS and then provide the Data source (usually the database name) and specify the User name and Password for Database authentication.
Double-click the database Connection to show the list of tables.
Scroll until you find the BANKS, CUSTOMERS and SJSTREETS tables. Select all 3 and then click Add.
A dialog will be displayed to create a Query Layer corresponding to each table. Select the checkbox next to OBJECTID for each of the tables. This is necessary to tell ArcGIS which column has a unique object identifier.
Use the context menus to zoom to the CUSTOMER layer, label the CUSTOMER and BANK layers and change the symbols associated with these layers so the map looks like the following:
Add a basemap
One of the powerful capabilities included with ArcGIS Desktop is the ability to integrate attractive basemaps from ArcGIS online.
Click on the little triangle next to the Add Data icon to drop down the selection Add Basemap.
This will display a number of available basemaps that you can try out. Select the Streets basemap.
This will now display our sample data on top of a street basemap to provide additional cartographic detail.
The Esri family of ArcGIS products provide the capability to create, maintain, analyze and visualize geospatial data across server, desktop and web platforms. From the Esri home page you can find information on all the products, services and resources needed to meet application needs.
In this recipe we have shown how to take the first steps to connect ArcGIS Desktop to a DB2 database to visualize geospatial data residing in database tables. From here you can move on to create custom applications through Python scripts to analyze data, perform geoprocessing and produce hardcopy maps.