ArcMap

Set up a geodatabase in DB2 on z/OS

Available with Standard or Advanced license.

To create a geodatabase in DB2 on the z operating system (z/OS), you need to do the following:

Configure an ArcGIS client

You must install an ArcGIS client to obtain files necessary for the DB2 database setup and to run the tool or script to create a geodatabase.

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.

Next, configure the DB2 system.

Configure ZPARMs

To enable ArcGIS to run as a distributed client to IBM DB2 on z/OS, the distributed data facility (DDF) must be started. The following DB2 ZPARM values are highly recommended to optimize the performance of the geodatabase with ArcGIS and other clients:

  1. Log in to DB2 as the administrator on z/OS.
  2. Set the following in the DSN6FAC section of DSNTIJUZ:

    • CMTSTAT=INACTIVE
    • IDTHTOIN=3600
    • TCPKPALV=120

  3. Set these parameters in the DSN6SYSP section of DSNTIJUZ:

    • CONDBAT=10000
    • EXTSEC=YES
    • IMPDSDEF=YES
    • IXQTY=0
    • MAXDBAT=1000
    • TSQTY=0
    • XMLVALA=204800
    • XMLVALS=10240

  4. You also need to set the correct default WLM environment in WLMENV in the DSN6SPRM section of DSNTIJUZ. They are as follows:

    • CACHEDYN=YES
    • EDMDBDC=102400
    • EDMPOOL=32000
    • EDMSTMTC=102400
    • EDM_SKELETON_POOL=102400

  5. After changing the ZPARM values above, you also need to make sure that SYSLMOD in job step DSNTIZL points to the correct dataset name so that DB2 loads the correct ZPARM module when it starts.
  6. Stop and start DB2 using the SET SYSPARM command to bring the changes online.

Create an sde user and group

Create a primary authorization ID in RACF on z/OS (sde by default) with access to the Time Sharing Option (TSO). For information on creating users via RACF, refer to the z/OS administration manual.

IBM recommends you use a secondary authorization ID (a user group in RACF on z/OS) to streamline user authority management. You need to enable the secondary authorization IDs on z/OS. Refer to the z/OS manual for information on enabling secondary authorization.

Only the user who is responsible for managing the GIS data and the geodatabase should log in as the sde user. Other ArcGIS users should connect using accounts other than sde.

  1. Create a stand-alone user account named sde in the system using RACF.
  2. Create a user group, such as GISUSERS, on z/OS using RACF. To do this, submit the name of the group on VM14 to your EC.
  3. Add the sde user to the GISUSERS group using RACF.

To connect to the geodatabase as the sde user from the Windows client machine where the ArcGIS client is installed, a local Windows user with the same name (sde) as specified for the connection to the server must be present on the client machine. This user does not require any special permission. To create a local Windows user, refer to Microsoft Windows documentation.

Transfer a dataset to IBM z/OS

To create a geodatabase on DB2 version 9.1 for the z/OS system, you must transfer a dataset—esriobj.XMIT—to the target z/OS system. The esriobj.XMIT file can be found in the DB2zOS folder inside the DatabaseSupport folder of your ArcGIS client. The file contains images of programs that must be link-edited into load libraries on z/OS. The following steps are required to accomplish this setup:

  1. Log in to a SYSADM user account ID on the z/OS system.
  2. Upload the esriobj.XMIT dataset to the z/OS system using ftp.

    The data is in Time Sharing Option/Extension (TSO/E) Transmit/Receive format and must be uploaded to the z/OS using a BINARY transfer. You can find the dataset in the DatabaseSupport\DB2zOS folder in your ArcGIS installation directory on Windows.

  3. Assuming ftp is used for the transfer, the dataset must eventually be defined on z/OS with these characteristics: 

    RECFM=FB,LRECL=80,BLKSIZE=3120

    Typically, ftp systems are set up such that for any data that is received and results in a new dataset, the resultant new dataset is not in the required format. However, that can be changed by using ftp commands. The command to ftp from Windows is 

    quote site recfm=fb lrecl=80 blksize=3120

    If you are logged on to the z/OS and pull the files using ftp, the command is 

    locsite recfm=fm lrecl=80 blksize=3120

  4. The dataset must be TSO/E RECEIVED by a user account on the z/OS system. Log on to a SYSADM user account ID on the z/OS system and open option 6 (the command panel) of Interactive System Productivity Facility (ISPF).
  5. The dataset that you uploaded is named esriobj.XMIT; therefore, issue the following TSO/E command to receive the dataset: 

    RECEIVE INDA('SYSADM.ESRIOBJ.XMIT')

    A prompt for specification of dataset creation overrides is returned for this command. If you do not provide any overrides, the dataset will be created under the high-level qualifier (HLQ) of the user ID with which you logged in.

    You should now have a dataset in SCR03: <user>.ESRI.OBJLIB.

  6. Review the name of the dataset. If it is correct, it is to be referenced in the ESRI UDF installation job that must now be uploaded to z/OS, edited, and run on z/OS, as described in the next section.

Create databases

You must have at least two DB2 databases within the spatially enabled database subsystem to store a geodatabase: one to store the geodatabase repository and one to store user data. Together, the database with the geodatabase repository and the database or databases containing the user data make up one geodatabase. Therefore, these databases must be created before you can set up the geodatabase.

The JCL script, ESRIUDFI, is provided to create databases and UDFs for geodatabase creation. This script is installed in the DatabaseSupport\DB2zOS folder of your ArcGIS client installation directory. You must customize the ESRIUDFI script using information specific to your implementation and upload and run the script on z/OS. The script itself includes instructions to create two databases.

To customize ESRIUDFI, refer to the instructions in the JCL script and carefully check the following:

  1. In the body of ESRIUDFI, there are several strings that need to be replaced with the corresponding user environment parameters. Details of the meaning of each string and how to replace them are explained at the top of the ESRIUDFI script.
  2. Step ESRIS1 of the JCL script creates two databases and sets the collation. To use these lines, you must uncomment them and provide names and collations for the databases. An example for this is as follows: 
    CREATE DATABASE SDEDBR00 CCSID UNICODE;
CREATE DATABASE SDEDBU00 CCSID UNICODE;

  3. Optionally, you can create a storage group for managing the geodatabase data more efficiently. You need to create a storage group first, so you must add an additional line to the ESRIUDFI script, as shown in this example: 
    CREATE STOGROUP ARCSDE VOLUMES (VOL1, VOL2, …) VCAT SDECAT;
CREATE DATABASE SDEDBR00 CCSID UNICODE STOGROUP ARCGIS;
CREATE DATABASE SDEDBU00 CCSID UNICODE STOGROUP ARCGIS;

  4. Once you have finished customizing the ESRIUDFI script, you need to submit it on your target machine.

    When the ESRI UDF installation job has completed, you will notice that during the last step, the package binding and creation of the SDE.SDE_UPDT_LMOD procedure were successful but had warnings. These warnings indicate the geodatabase system tables have not yet been created in the database. You can ignore these warnings, because when you run the Enable Enterprise Geodatabase tool, the tables will be created and the package object will be autobound when it is referenced by the tool.

  5. Spatially enable the database subsystem. See the DB2 z/OS documentation for instructions.

Grant privileges

Permissions and database privileges must be granted to the sde user and the ArcGIS user group after the databases and UDFs are created.

  1. Log in to DB2 as an account with the SYSADM role to grant the necessary privileges to the ArcGIS user group you set up and the sde user. In these example GRANT statements, the group is named GISUSERS. 
    GRANT SELECT ON SYSIBM.SYSTABAUTH TO GISUSERS;
GRANT SELECT ON SYSIBM.SYSDBAUTH TO GISUSERS;
GRANT SELECT ON SYSIBM.SYSROUTINES TO GISUSERS;
GRANT SELECT ON SYSIBM.SYSTABCONST TO GISUSERS;
GRANT SELECT ON SYSIBM.SYSINDEXES TO GISUSERS;
GRANT SELECT ON SYSIBM.SYSKEYS TO GISUSERS;
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO GISUSERS;
GRANT SELECT ON SYSIBM.SYSCHECKS TO GISUSERS;
GRANT SELECT ON SYSIBM.SYSSCHEMAAUTH TO GISUSERS;
GRANT SELECT ON SYSIBM.SYSTABLES TO GISUSERS;
GRANT SELECT ON SYSIBM.SYSSEQUENCES TO GISUSERS;
GRANT SELECT ON SYSIBM.SYSDUMMY1 TO GISUSERS;
  2. Grant the following additional privileges to the sde user: 
    GRANT BINDADD TO SDE;
GRANT CREATE ON COLLECTION SDE TO SDE;
GRANT DBADM ON DATABASE <repository_database_name> TO SDE;
GRANT DBADM ON DATABASE <user_data_database_name> TO SDE;
  3. Grant the following permission to the PUBLIC role: 
    GRANT EXECUTE ON PACKAGE SYSPROC.* TO PUBLIC;
  4. The following statement is required if you use a storage group. In this example, the storage group is named ARCGIS. 
    GRANT USE OF STOGROUP ARCGIS to SDE;

Customize dbtune.sde

Alter the dbtune.sde file found in <ArcGIS client installation directory>\DatabaseSupport\DB2zOS to point your database subsystem.

The dbtune.sde file populates the DBTUNE geodatabase system table with configuration keywords, parameters, and values that control the physical storage of tables in the database. For DB2 databases on z/OS, you must edit this file prior to running the Enable Enterprise Geodatabase geoprocessing tool to specify in which database certain tables will be created.

  1. Open the dbtune.sde file in a text editor.
  2. Uncomment and change these configuration string values under the DATA_DICTIONARY keyword to indicate in which database you want specific geodatabase system tables created. The following are suggested settings. The database named SDEDBR00 is the database that will store the geodatabase repository tables. 

    B_STORAGE "IN DATABASE SDEDBR00"
MVTABLES_MODIFIED_TABLE "IN DATABASE SDEDBR00"
STATES_TABLE "IN DATABASE SDEDBR00"
STATE_LINEAGES_TABLE "IN DATABASE SDEDBR00"
VERSIONS_TABLE "IN DATABASE SDEDBR00"
XML_INDEX_TAGS_TABLE "IN DATABASE SDEDBR00"

  3. Make sure the BLOB_OPTION and CLOB_OPTION parameters under the DEFAULTS keyword are set to be empty strings.
  4. Uncomment and change these configuration string values under the DEFAULTS keyword to indicate in which database you want specific objects related to user-defined data to be created.

    The following are suggested settings. The database named SDEDBU00 is the database that will store user-defined data within the database subsystem.

     AUX_STORAGE "IN DATABASE SDEDBU00"
 A_STORAGE "IN DATABASE SDEDBU00"
 BLK_STORAGE "IN DATABASE SDEDBU00"
 BND_STORAGE "IN DATABASE SDEDBU00"
 B_STORAGE "IN DATABASE SDEDBU00"
 D_STORAGE "IN DATABASE SDEDBU00"
 RAS_STORAGE "IN DATABASE SDEDBU00"

  5. Uncomment and change these configuration string values under the LOGFILE_DEFAULTS keyword to indicate in which database you want log file tables to be created. 

     LD_STORAGE "IN DATABASE SDEDBU00"
 LF_STORAGE "IN DATABASE SDEDBU00"
 SESSION_STORAGE "IN DATABASE SDEDBU00"

  6. Save and close the dbtune.sde file.

Now you can create a geodatabase in the DB2 subsystem.

Create a geodatabase

Use one of the following methods to create a geodatabase in DB2 on z/OS:

Use the Enable Enterprise Geodatabase tool

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

  1. Start ArcMap or ArcCatalog.
  2. Connect to DB2 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 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 subsystem.

Now, create users who can create data.

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

Follow these steps to run a Python script for geodatabase creation in a DB2 z/OS subsystem:

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

Now, create users who can create data.