Log file table options for geodatabases in Db2

Enterprise geodatabases use log file tables to maintain lists of selected records. Records are written to log file tables for later use by the application in the following situations:

• You (or a tool or other process you run) create a selection set of a specific size—more than 100 records in ArcMap; more than 1,000 records in ArcGIS Pro; more than 1,000 records when Object IDs are requested from a feature service.
• You reconcile or post to a versioned geodatabase.
• A client application checks out data for disconnected editing.

Log file tables store IDs of the selected features so they can be redisplayed. This allows for faster analysis and data processing.

There are three log file options: shared, session-based, and pools of session-based log files. Each is described in its own section in this topic.

Geodatabases in IBM Db2 use shared log file tables by default, but you can change log file table settings using the Configure Geodatabase Log File Tables geoprocessing tool.

Shared log files

Shared log files are shared by all sessions that connect as the same user. If you have multiple users connecting with the same user account, all those sessions insert records into and delete records from the same log file data table.

When to use shared log files

Use shared log files if each client and user connects with a different database user account.

When you might not want to use shared log files

You may not want to use shared log files if you have numerous connections made with the same login, such as when you publish feature services, which will generate multiple connections with the same login. If many of your users will be running attribute queries against the same feature service, this could lead to contention and longer wait times for the SDE_LOGFILE_DATA table. In those cases, you might want to use session-based log files.

Tables created for shared log files

The log file tables used for this option are SDE_LOGFILES and SDE_LOGFILE_DATA. They are created in the schema of the connecting user the first time the user makes a selection that exceeds the selection threshold.

SDE_LOGFILES stores information about each selection set (log file) that is created. The logfile_name and logfile_id columns in this table uniquely identify the name of the log file, and the logfile_id column links the log file record to the SDE_LOGFILE_DATA table. The SDE_LOGFILE_DATA table contains the logfile_data_id and the feature identifier for the selected records.

All records are deleted as soon as the selection set is cleared to prevent the SDE_LOGFILE_DATA table from growing too large. The SDE_LOGFILES table is truncated when the session ends. Both SDE_LOGFILE_DATA and SDE_LOGFILES remain in the user's schema.

Permissions required for shared log files

Since the log file tables are owned by the connecting user, users must be granted privileges that allow them to create the required data objects, such as tables. This is required even if the user has read-only access to the geodatabase. If these privileges are not granted, users receive an error message the first time they create a selection set larger than the threshold size for that particular client application. However, once the SDE_LOGFILES and SDE_LOGFILE_DATA tables are created for a user, the database administrator can revoke the privileges.

For example, Ian is a city planner who would only select data from the geodatabase to perform analyses related to his work. Therefore, he would be considered a read-only user. However, for Ian to create the SDE_LOGFILES and SDE_LOGFILE_DATA tables in the city's enterprise geodatabase, he needs to be able to create tables in the geodatabase.

Rather than grant Ian permission to create tables indefinitely, the database administrator grants the privlileges temporarily, asks Ian to log in to the geodatabase and make a selection that exceeds the selection threshold. The database administrator then revokes Ian's privilege to create tables.

The following permissions are required to use shared log file tables in Db2:

• CONNECT
• CREATETAB
• IMPLICIT_SCHEMA

Note:

The CONNECT privilege is granted by default to all users; therefore, you only need to explicitly grant this permission if you have revoked it from PUBLIC.

Optional storage control for shared log file tables and indexes

There are several parameters under the LOGFILE_DEFAULTS keyword of the DBTUNE table that control how or where log file tables are stored in the database. You do not need to set these to use shared log files, but you can set them if you want to alter how the SDE_LOGFILES and SDE_LOGFILE_DATA tables and indexes are stored in the database.

For Db2, the following parameters control storage for shared log file tables and indexes:

• LD_INDEX_DATA_ID
• LD_INDEX_ROW_ID
• LD _STORAGE
• LF_INDEXES
• LF_STORAGE

For explanations of these parameters, see Db2 configuration parameters.

Session-based log files

Session-based log file data tables are dedicated to a single session and may contain multiple selection sets (log files). Each session that logs in requires a set of tables for selections.

When to use session-based log files

You might use session-based log files if you have numerous concurrent connections being made to the geodatabase with the same login. For example, if many services reference the data in your geodatabase, you might use session-based log files.

When you might not want to use session-based log files

If you have read-only users who connect to the database, you cannot use session-based log files.

The session table is dropped from the user's schema when the session ends. That means it has to be re-created when needed; therefore, users need permissions to create tables to be able to use session-based log file tables.

Tables created for session-based log files

Three tables are created: SDE_LOGFILES, SDE_LOGFILE_DATA, and SDE_SESSION<SDE_ID>. SDE_LOGFILE_DATA is not actually used in this case, but it is created automatically. The SDE_LOGFILES table stores information about the selection set plus a session tag <SDE_ID> that is appended to the name of the SDE_SESSION table. The SDE_SESSION table stores the feature identifier for the selected set and the log file ID.

The SDE_LOGFILES and SDE_LOGFILE_DATA tables remain in the geodatabase. The SDE_LOGFILES table is truncated when the connecting application disconnects. The SDE_SESSION<SDE_ID> table is truncated when the connecting application deletes the log files, and the table is dropped when the session disconnects.

Permissions required for session-based log files

Session-based log files are owned by the user who started the connecting session. This means users need privileges to create the necessary database objects.

All users require the following permissions to use session-based log file tables in Db2:

• CONNECT
• CREATETAB
• IMPLICIT_SCHEMA

Optional storage control for session-based log file tables and indexes

There are several parameters under the LOGFILE_DEFAULTS keyword of the DBTUNE table that control how or where log file tables are stored in the database. You do not have to set these to use session-based log files, but you can set them if you want to alter how the SDE_LOGFILES, SDE_LOGFILE_DATA, and SDE_SESSION tables and indexes are stored in the database.

For Db2, the following parameters control storage for session-based log file tables and indexes:

• LD_INDEX_DATA_ID
• LD_INDEX_ROW_ID
• LD_STORAGE
• LF_INDEXES
• LF_STORAGE
• SESSION_INDEX
• SESSION_STORAGE

See Db2 configuration parameters for explanations of these parameters.

Pools of log files owned by the geodatabase administrator

The geodatabase administrator can create a pool of session-based log files that can be checked out and used by other users. Shared log files cannot be checked out from a log file pool.

Using a pool of log files avoids having to grant users the permissions necessary to create objects in the database.

When to use log file pools

You would use a pool of log files if you cannot give users the ability to create log file tables in their own schemas. Users still need to have permissions to create a session or connect to the database, though.

When you might not want to use log file pools

Overall, using pools of log files requires slightly more maintenance because you must estimate the number of necessary log file tables, and you might find yourself adjusting the size of the pool or the number of pools used. Keep in mind that a large log file pool or a large number of log file pools can have a negative impact on performance.

Tables created for log file pools

You specify the number of SDE_LOGPOOL_<table_ID> tables that the Configure Geodatabase Log File Tables tool creates in the geodatabase administrator's schema. For example, if you specify 5 tables, the following tables are created in the schema of the geodatabase administrator:

• SDE_LOGPOOL_1
• SDE_LOGPOOL_2
• SDE_LOGPOOL_3
• SDE_LOGPOOL_4
• SDE_LOGPOOL_5

An additional table in the geodatabase administrator's schema, SDE_LOGFILE_POOL, records the SDE_ID for the session and a table ID. The <table_ID> in the name of the SDE_LOGPOOL table corresponds to the value in the table_ID column of the SDE_LOGFILE_POOL table.

Each session that needs a log file table adds one record to the SDE_LOGFILE_POOL table and the session is allocated to one of the SDE_LOGPOOL_<table_ID> tables. If additional log files are created by the same session—for example, a second selection set of 300 records is created in one ArcMap session—the new log files (selection set) are added to the same SDE_LOGPOOL table.

When log files are cleared, the SDE_LOGPOOL table that is checked out to the session is truncated. For example, if the second selection set in the ArcMap session is cleared, the 300 records are removed from the SDE_LOGPOOL table but the records for the first selection set remain. When the first selection set is cleared, these records are removed from the SDE_LOGPOOL table.

As a log file (selection set) is cleared, the corresponding SDE_LOGPOOL table is truncated.

Permissions required for log file pools

To use the log file tables in the pool, users only require the ability to connect to the database and use the objects in the geodatabase administrator's schema. Therefore, the only permission needed to use pools of log file tables is the CONNECT permission.

Note:

The CONNECT privilege is granted by default to all users; therefore, you only need to explicitly grant this permission if you have revoked it from PUBLIC.

Optional storage control for log file pools

There are only a few parameters under the LOGFILE_DEFAULTS keyword that control how the SDE_LOGPOOL<SDE_ID> tables and their indexes are stored.

In geodatabases in Db2, the LD_STORAGE, LD_INDEX_ROWID, and LD_INDEX_DATA_ID DBTUNE parameters are used to set storage for the SDE_LOGPOOL<SDE_ID> tables and their indexes. These three parameters also control the storage of the SDE_LOGFILE_DATA table and indexes. See Db2 configuration parameters for explanations of these parameters.

You do not have to set these to use pools of log file tables, but you can set them if you want to alter how the SDE_LOGPOOL<SDE_ID> tables and indexes are stored in the database.