Overview

Skill Level: Any Skill Level

Esri ArcGIS Desktop is a full-function GIS tool for displaying and analyzing spatial data. This recipe takes you through the steps to load spatial data into a DB2 for z/OS, DB2 LUW or DashDB database, connect to DB2 and visualize the spatial tables.

Ingredients

With this recipe you will create a map of San Jose streets, two bank branches and a number of sample customers that looks like the following screen capture from ArcGIS Desktop on Windows connecting to a DB2 for z/OS database.

arcgis-final-map

Step-by-step

  1. Setup DB2 and sample data

    1. 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.

    2. Download and unzip the sample data to a convenient directory.

    3. 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

    4. 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

    5. 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
  2. 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.

  3. Connect to the database and add data

    Start up an ArcMap session with an empty workspace. Click on the Add Dataarcgis-add-data-icon-2 icon and select Database Connections.

    arcgis-db-connect

    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.

    arcgis-db2-connect

    Double-click the database Connection to show the list of tables.

    arcgis-open-connection

    Scroll until you find the BANKS, CUSTOMERS and SJSTREETS tables. Select all 3 and then click Add.

    arcgis-select-tables

    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.

     

    arcgis-set-objectid

    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:

    argis-initial-map

  4. 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.

     

    arcgis-add-basemap

    This will display a number of available basemaps that you can try out. Select the Streets basemap.

    argis-streets-basemap

    This will now display our sample data on top of a street basemap to provide additional cartographic detail.

    arcgis-final-map

     

  5. Conclusion

    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.

Join The Discussion