Help

Set up a geodatabase in DB2

Available with Standard or Advanced license.

Install and configure IBM DB2 and use the Enable Enterprise Geodatabase geoprocessing tool or a Python script to create geodatabases in a DB2 database.

This topic covers creating a geodatabase in DB2 on Linux, UNIX, or Windows servers. For instructions on setting up a geodatabase in DB2 on the z operating system (z/OS), see Set up a geodatabase in DB2 z/OS.

Set up DB2

Before you can run the Enable Enterprise Geodatabase tool or script to create a geodatabase in DB2, you must first install and configure the DB2 database management system. Check the ArcGIS system requirements before you set up a geodatabase to be sure the database, ArcGIS, and hardware combinations you want to use are supported.

You should follow the instructions provided by IBM to install and configure your DB2 instance. Then follow these steps to configure a database and login to use for your geodatabase.

  1. Create an operating system login named sde on the DB2 server.

    You will connect to the database with the sde login to create a geodatabase.

  2. Create a DB2 database and register it with the Spatial Extender module.
  3. Grant the sde user DBADM authority in the database.

Next, set up an ArcGIS client from which you will connect and create a geodatabase in the DB2 database.

Configure an ArcGIS client

You can run a Python script from ArcGIS for Desktop (Standard or Advanced), ArcGIS Engine Runtime with the Geodatabase Update extension, or ArcGIS for Server (Standard or Advanced) to create a geodatabase. If you want to run the Enable Enterprise Geodatabase geoprocessing tool instead of a script, use ArcGIS for Desktop.

  1. Install the ArcGIS client you want to use for geodatabase creation.

    Follow the instructions provided in the software installation guides.

  2. In most cases, your ArcGIS client will be installed on a different computer than the DB2 server; therefore, install and configure a DB2 client on the ArcGIS client computer.

    The IBM Data Server Runtime Client for DB2 can be downloaded from My Esri, or you can use your own installation of the DB2 client. See the DB2 documentation for instructions to install. If you are installing the DB2 client on a 64-bit operating system, run the 64-bit executable; it installs both 32- and 64-bit files.

  3. You can connect from ArcGIS to a DB2 database using a DSNless connection string. However, if you want to use a data source name to connect instead, you must catalog the database, thereby configuring the data source name. See the IBM DB2 documentation for instructions on creating a data source name.

Now you can create a geodatabase.

Create a geodatabase

Use one of the following methods to create a geodatabase in your DB2 database:

Use the Enable Enterprise Geodatabase tool

If you installed and configured ArcGIS for Desktop to connect to your DB2 database, you can run the Enable Enterprise Geodatabase tool.

  1. Start ArcMap or ArcCatalog.
  2. Connect to the DB2 database from the Catalog tree. Connect with the sde login.

    Be sure to save the sde user's password on the Database Connection dialog box.

  3. Right-click the database connection and click Enable Geodatabase.

    The Enable Enterprise Geodatabase tool opens.

  4. Add the database connection file for your DB2 database to the Input Database text box.
  5. Browse to the ArcGIS for Server keycodes file that was created when you authorized ArcGIS for Server and add it to the Authorization File text box.

    When you use the wizard to authorize ArcGIS for Server, a keycodes file is written to the server where the software is installed. If you authorized ArcGIS for Server on a Linux box, the file was created in /arcgis/server/framework/runtime/.wine/drive_c/Program Files/ESRI/License<release>/sysgen. If you authorized on a Windows server, the file was created in Program Files\ESRI\License<release>\sysgen. If you have not already done so, authorize ArcGIS for Server to create this file.

  6. Click OK to run the tool.

    A log file for the geodatabase creation (GDBCreateGeodatabase<#>.log) is written to the directory specified for your %TEMP% variable on the computer where the tool is run. If you have any problems running the tool, check this log file to troubleshoot the problem.

A geodatabase is created in your DB2 database.

Next, create users to load data into the geodatabase. See the IBM DB2 Information Center for instructions on creating users.

Use a Python script

You can run a Python script from an ArcGIS for Desktop (Standard or Advanced), ArcGIS for Server (Standard or Advanced), or ArcGIS Engine (with the Geodatabase Update extension) client machine to create a geodatabase in a DB2 database.

Follow these steps to run a Python script for geodatabase creation in a DB2 database:

  1. Create a text file on the ArcGIS client machine and copy the following script into the file. 

    """
Name: enable_enterprise_gdb.py
Description: Provide connection information to an enterprise database
and enable enterprise geodatabase.
Type enable_enterprise_gdb.py -h or enable_enterprise_gdb.py --help for usage
Author: Esri
"""

# Import system modules
import arcpy, os, optparse, sys


# Define usage and version
parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for " + arcpy.GetInstallInfo()['Version'] )

#Define help and options
parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', 'DB2','INFORMIX','DB2ZOS', ''], default="", help="Type of enterprise DBMS:  SQLSERVER, ORACLE, POSTGRESQL, DB2, INFORMIX, or DB2ZOS.")
parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
parser.add_option ("--auth", dest="account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
parser.add_option ("-u", dest="User", type="string", default="", help="Geodatabase administrator user name")
parser.add_option ("-p", dest="Password", type="string", default="", help="Geodatabase  administrator password")
parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
parser.add_option ("-l", dest="Authorization_file", type="string", default="", help="Full path and name of authorization file")


# Check if value entered for option
try:
	(options, args) = parser.parse_args()

	
#Check if no system arguments (options) entered
	if len(sys.argv) == 1:
		print "%s: error: %s\n" % (sys.argv[0], "No command options given")
		parser.print_help()
		sys.exit(3)
	

	#Usage parameters for spatial database connection
	database_type = options.Database_type.upper()
	instance = options.Instance
	account_authentication = options.account_authentication.upper()
	username = options.User.lower() 
	password = options.Password	
	database = options.Database.lower()
	license = options.Authorization_file


	if( database_type ==""):	
		print " \n%s: error: \n%s\n" % (sys.argv[0], "DBMS type (--DBMS) must be specified.")
		parser.print_help()
		sys.exit(3)		
		
	if (license == ""):
		print " \n%s: error: \n%s\n" % (sys.argv[0], "Authorization file (-l) must be specified.")
		parser.print_help()
		sys.exit(3)

	if (database_type == "SQLSERVER"):
		database_type = "SQL_SERVER"
		
	# Get the current product license
	product_license=arcpy.ProductInfo()
	
	if (license == ""):
		print " \n%s: error: %s\n" % (sys.argv[0], "Authorization file (-l) must be specified.")
		parser.print_help()
		sys.exit(3)
	
	# Checks required license level
	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
		print "\n" + product_license + " license found!" + "  Enabling enterprise geodatabase functionality requires an ArcGIS for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server license."
		sys.exit("Re-authorize ArcGIS before enabling an enterprise geodatabase.")
	else:
		print "\n" + product_license + " license available!  Continuing to enable..."
		arcpy.AddMessage("+++++++++")
	
	# Local variables
	instance_temp = instance.replace("\\","_")
	instance_temp = instance_temp.replace("/","_")
	instance_temp = instance_temp.replace(":","_")
	Conn_File_NameT = instance_temp + "_" + database + "_" + username    
	
	if os.environ.get("TEMP") == None:
		temp = "c:\\temp"	
	else:
		temp = os.environ.get("TEMP")
	
	if os.environ.get("TMP") == None:
		temp = "/usr/tmp"		
	else:
		temp = os.environ.get("TMP")  
	

	Connection_File_Name = Conn_File_NameT + ".sde"
	Connection_File_Name_full_path = temp + os.sep + Conn_File_NameT + ".sde"
	
	# Check for the .sde file and delete it if present
	arcpy.env.overwriteOutput=True
	if os.path.exists(Connection_File_Name_full_path):
		os.remove(Connection_File_Name_full_path)
	
	print "\nCreating Database Connection File...\n"	
	# Process: Create Database Connection File...
	# Usage:  out_file_location, out_file_name, DBMS_TYPE, instnace, database, account_authentication, username, password, save_username_password(must be true)
	arcpy.CreateDatabaseConnection_management(out_folder_path=temp, out_name=Connection_File_Name, database_platform=database_type, instance=instance, database=database, account_authentication=account_authentication, username=username, password=password, save_user_pass="TRUE")
        for i in range(arcpy.GetMessageCount()):
		if "000565" in arcpy.GetMessage(i):   #Check if database connection was successful
			arcpy.AddReturnMessage(i)
			arcpy.AddMessage("\n+++++++++")
			arcpy.AddMessage("Exiting!!")
			arcpy.AddMessage("+++++++++\n")
			sys.exit(3)            
		else:
			arcpy.AddReturnMessage(i)
			arcpy.AddMessage("+++++++++\n")
	
	
	# Process: Enable geodatabase...
	try:
		print "Enabling Enterprise Geodatabase...\n"
		arcpy.EnableEnterpriseGeodatabase_management(input_database=Connection_File_Name_full_path, authorization_file=license)
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
		arcpy.AddMessage("+++++++++\n")
	except:
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
			
	if os.path.exists(Connection_File_Name_full_path):
		os.remove(Connection_File_Name_full_path)
			
#Check if no value entered for option	
except SystemExit as e:
	if e.code == 2:
		parser.usage = ""
		print "\n"
		parser.print_help() 
		parser.exit(2)

  2. Save the file with a .py extension.
  3. Run the script, providing options and information specific to your site.

    In the following example run from a Windows machine, the file enable_gdb.py is run for database spdata on instance db2prod. The connection is made as the SDE login with password Tgdbst@rtsh3r3. A keycodes file in the default ArcGIS Server location is specified to authorize the geodatabase.

    enable_gdb.py --DBMS DB2 -i db2prod --auth DATABASE_AUTH 
-u sde -p Tgdbst@rtsh3r3 -D spdata -l \\Program Files\ESRI\License10.3\sysgen\keycodes

    This is an example of running the script on a Linux machine:

    /enable_gdb.py --DBMS DB2 -i db2prod --auth DATABASE_AUTH 
-u sde -p Tgdbst@rtsh3r3 -D spdata -l /usr/arcgis/server/framework/runtime/.wine/drive_c/Program Files/ESRI/License10.3/sysgen/keycodes

    A log file for the geodatabase creation (GDBCreateGeodatabase<#>.log) is written to the directory specified for your TEMP or TMP variable on the computer where the script is run.

A geodatabase is created in your DB2 database.

Next, create users to load data into the geodatabase. See the IBM DB2 Information Center for instructions on creating users and granting privileges to load data.