Create 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:

• Install and configure ArcGIS and Db2 clients.
• Configure ZPARMs on Db2.
• Create an sde user and group on z/OS.
• Create at least two databases.
• Grant privileges to the sde user and group.
• Customize the dbtune.sde file to specify system table storage.
• Create a geodatabase.

Configure clients

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 Desktop (Desktop Standard or Desktop Advanced), ArcGIS Engine Geodatabase Update, or ArcGIS Server (enterprise edition) to create a geodatabase. If you want to run the Enable Enterprise Geodatabase geoprocessing tool instead of a script, use ArcGIS Desktop.

Note:

You need an ArcGIS Server (enterprise) keycodes file to authorize your geodatabase in the next section. Even if you do not run a Python script from an ArcGIS Server machine to create the geodatabase, you must install and authorize ArcGIS Server to get the keycodes file. You may need to copy the file from the ArcGIS Server machine to a location the geoprocessing tool or Python script can access.

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.

   You can download the IBM Data Server Runtime Client for Db2 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, allowing you to connect from both 32- and 64-bit ArcGIS clients.

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

   Note:

   Be sure your EDM buffer is large enough to allow you to set EDMDBDC to 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.

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;
   

   Note:

   The name of a database used to store a geodatabase cannot contain special characters.

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;
   

   Note:

   Additional user privileges need to be authorized if you use a storage group. See the next section for more information.

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

Note:

The dbtune.sde file you altered in the last section must be in the client installation directory on the machine where you run the geoprocessing tool or Python script.

Use the Enable Enterprise Geodatabase tool

If you installed and configured ArcGIS 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 Server keycodes file that was created when you authorized ArcGIS Server and add it to the Authorization File text box.

   When you use the wizard to authorize ArcGIS Server, a keycodes file is written to the machine where the software is installed. If you authorized ArcGIS 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. Copy this file to a location you can access from the Enable Enterprise Geodatabase tool. If you have not already done so, authorize ArcGIS Server to create this file.

6. Click OK to run the tool.

   You can find messages related to geodatabase creation in the sde_setup.log file, which is created in the directory specified for your %TEMP% variable on the computer where the tool is run. If you have problems creating a geodatabase, check this 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 Desktop (Desktop Standard or Desktop Advanced), ArcGIS Server (enterprise edition), 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
   """
   
   # 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 Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS 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\License\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/License/sysgen/keycodes'
   

   Tip:

   Type -h or --help at the command prompt to get syntax help.

   You can find messages related to geodatabase creation in the sde_setup.log file, which is created in the directory specified for your TEMP or TMP variable on the computer where the Python script is run. If you have any problems creating a geodatabase, check this file to troubleshoot the problem.

A geodatabase is created in your Db2 subsystem.

Now, create users who can load data.