What's new for IBM i 7.6
The following information was added or updated in the initial release of IBM i 7.6:
- The QRO hash value used to uniquely identify queries has been increased in size from 32 bits to 64 bits. This reduces hash collisions and may improve the plan cache search time for plan caches with a large number of queries.
- Adaptive Query Processing (AQP) has been enhanced to detect when a temporary hash table is too small for the data being processed. AQP can dynamically increase the size of the temporary hash table for subsequent runs of the query to provide improved query performance.
- A new data access method allows more efficient use of multi-key indexes for some queries that have complex predicates over the index keys.
- Additional information about queries has been added to Visual Explain
- Additional information about lock waits encountered by queries has been add to the Database Monitor and to Visual Explain.
- The SQL Error Logging Facility (SELF) can be turned off at the system level: SQL Error Logging Facility (SELF) and VALIDATE_SELF scalar function
- New system limit for maximum message keys in job message queue: System Health Services
New services:
- AUDIT_JOURNAL_PO and AUDIT_JOURNAL_SF table functions: AUDIT_JOURNAL_PO (Printer Output) table function and AUDIT_JOURNAL_SF (Action to Spooled File) table function
- AUTHORITY_COLLECTION_IFS view: AUTHORITY_COLLECTION views
- CHANGE_TOTP_KEY table function: CHANGE_TOTP_KEY table function
- CHECK_TOTP scalar function: CHECK_TOTP scalar function
- DELETE_OLD_JOURNAL_RECEIVERS procedure: DELETE_OLD_JOURNAL_RECEIVERS procedure
- IFS_PATH scalar function: IFS_PATH scalar function
- KERBEROS_TEYTAB_ENTRIES table function: KERBEROS_KEYTAB_ENTRIES table function
- PROGRAM_RESOLVED_IMPORTS table function: PROGRAM_RESOLVED_IMPORTS table function
- SQLSTATE_INFO table: SQLSTATE_INFO table
- VERIFY_NAME scalar function: VERIFY_NAME scalar function
Changed services:
- ACTIVE_JOB_INFO table function has a new DETAILED_INFO filter that omits information that is potentially slow to access: ACTIVE_JOB_INFO table function
- ACTIVE_JOB_INFO table function, ACTIVE_QUERY_INFO table function, CLEAR_PLAN_CACHE procedure, and MTI_INFO table function changed to support a larger QRO hash value: ACTIVE_JOB_INFO table function, ACTIVE_QUERY_INFO table function, CLEAR_PLAN_CACHE procedure, and MTI_INFO table function
- AUDIT_JOURNAL_AD, AUDIT_JOURNAL_CP, AUDIT_JOURNAL_DS, AUDIT_JOURNAL_GR, AUDIT_JOURNAL_NA, AUDIT_JOURNAL_M8, AUDIT_JOURNAL_M9, AUDIT_JOURNAL_PW, AUDIT_JOURNAL_SM, and AUDIT_JOURNAL_VP table functions have new result columns to reflect additional auditing added in IBM i 7.6: AUDIT_JOURNAL_AD (Auditing Change) table function, AUDIT_JOURNAL_CP (User Profile Changes) table function, AUDIT_JOURNAL_DS (Service Tools User ID and Attribute Changes) table function, AUDIT_JOURNAL_GR (Generic Record) table function, AUDIT_JOURNAL_NA (Attribute Change) table function, AUDIT_JOURNAL_M8 (Db2 Mirror Product Services) table function, AUDIT_JOURNAL_M9 (Db2 Mirror Replication State) table function, AUDIT_JOURNAL_PW (Password) table function, AUDIT_JOURNAL_SM (Systems Management Change) table function, and AUDIT_JOURNAL_VP (Network Password Error) table function
- DUMP_PLAN_CACHE has a new filter for QRO hash: DUMP_PLAN_CACHE procedure
- GROUP_PTF_DETAILS view returns the value for PTF_IPL_REQUIRED based on the live feed: GROUP_PTF_DETAILS view
- JOB_INFO table function has a new job name filter: JOB_INFO table function
- NETSTAT_ROUTE_INFO view removed columns PPP_CONFIGURATION_PROFILE, PPP_AUTHENTICATION_USER_ID, PPP_INTERNET_ADDRESS, and PPP_DIAL_ON_DEMAND_PROFILE due to withdrawal of support for the point-to-point protocol: NETSTAT_ROUTE_INFO view
- POWER_SCHEDULE_INFO view has a new column that returns the date in the job format: POWER_SCHEDULE_INFO view
- SECURITY_INFO has new columns for additional sign-on factor: SECURITY_INFO view
- Host connection server added to SERVER_SBS_ROUTING view and SET_SERVER_SBS_ROUTING procedure: SERVER_SBS_ROUTING view and SET_SERVER_SBS_ROUTING procedure
- SYSDISKSTAT view and table function return additional columns of information: SYSDISKSTAT view and SYSDISKSTAT table function
- SYSTEM_STATUS_INFO view, SYSTEM_STATUS_INFO_BASIC view, and SYSTEM_STATUS table function removed columns that were previously deprecated (AVERAGE_CPU_RATE,AVERAGE_CPU_UTILIZATION, MINIMUM_CPU_UTILIZATION, MAXIMUM_CPU_UTILIZATION, SQL_CPU_UTILIZATION): SYSTEM_STATUS_INFO view, SYSTEM_STATUS_INFO_BASIC view, and SYSTEM_STATUS table function
- USER_INFO and USER_INFO_BASIC views have new columns for multi-factor authentication: USER_INFO view and USER_INFO_BASIC view
- USER_INFO_BASIC view removed previously deprecated column, USER_DEFAULT_PASSWORD: USER_INFO_BASIC view
The following existing services are now documented:
- ADD_ISCSI_TARGET procedure: ADD_ISCSI_TARGET procedure
- CHANGE_IOP procedure: CHANGE_IOP procedure
- CHANGE_ISCSI_TARGET procedure: CHANGE_ISCSI_TARGET procedure
- ISCSI_INFO view: ISCSI_INFO view
- REMOVE_ISCSI_TARGET procedure: REMOVE_ISCSI_TARGET procedure
Authorization changed for the following services:
- ADD_TIME_SERVER procedure: ADD_TIME_SERVER procedure
- ASP_INFO view: ASP_INFO view
- ASP_JOB_INFO view: ASP_JOB_INFO view
- DATABASE_MONITOR_INFO view: DATABASE_MONITOR_INFO view
- MEDIA_LIBRARY_INFO view: MEDIA_LIBRARY_INFO view
- NETSTAT_INFO view: NETSTAT_INFO view
- NETSTAT_INTERFACE_INFO view: NETSTAT_INTERFACE_INFO view
- NETSTAT_JOB_INFO view: NETSTAT_JOB_INFO view
- NETSTAT_ROUTE_INFO view: NETSTAT_ROUTE_INFO view
- NETWORK_ATTRIBUTE_INFO view: NETWORK_ATTRIBUTE_INFO view
- OBJECTCONNECT_INFO view: OBJECTCONNECT_INFO view
- REMOVE_TIME_SERVER procedure: REMOVE_TIME_SERVER procedure
- RDB_ENTRY_INFO view: RDB_ENTRY_INFO view
- SECURITY_INFO view: SECURITY_INFO view
- SERVER_SBS_CONFIGURATION view: SERVER_SBS_CONFIGURATION view
- SERVER_SHARE_INFO view: SERVER_SHARE_INFO view
- SYSTEM_VALUE_INFO view: SYSTEM_VALUE_INFO view
- TAPE_CARTRIDGE_INFO view: TAPE_CARTRIDGE_INFO view
- TELNET_SERVER_ATTRIBUTES view: TELNET_SERVER_ATTRIBUTES view
- TIME_PROTOCOL_INFO view: TIME_PROTOCOL_INFO view
- USER_INFO view: USER_INFO view
- USER_INFO_BASIC view: USER_INFO_BASIC view
What’s new since the first IBM i 7.5 publication
The following revisions or additions have been made to the Performance and query optimization
documentation since the first 7.5 publication:
- Hardware compression can be used for compressing cached plans: Plan cache
- SQL Error Logging Facility (SELF): SQL Error Logging Facility (SELF), SQL_ERROR_LOG view, SELFCODES global variable, VALIDATE_SELF scalar function
- New PARALLEL_MIN_TIME QAQQINI option: QAQQINI query options
- Column statistics include information about varying length column allocated lengths: Tips for using VARCHAR and VARGRAPHIC data types in databases
- System limit alerting is instrumented for the maximum number of deleted rows in a partition limit, for maximum members in a source file, and for a maximum sequence number in a journal: System limit alerts
- New services
- Many new audit journal entry services: Audit journal entry services
- ADD_VALIDATION_LIST_ENTRY, CHANGE_VALIDATION_LIST_ENTRY, and REMOVE_VALIDATION_LIST_ENTRY scalar functions simplify validation list handling: ADD_VALIDATION_LIST_ENTRY scalar function, CHANGE_VALIDATION_LIST_ENTRY scalar function, and REMOVE_VALIDATION_LIST_ENTRY scalar function
- AUDIT_JOURNAL_DATA_MART_INFO view and MANAGE_AUDIT_JOURNAL_DATA_MART provide a way to gather audit journal information for efficient query access: AUDIT_JOURNAL_DATA_MART_INFO view and MANAGE_AUDIT_JOURNAL_DATA_MART procedure
- CHANGE_DISK_PATHS procedure: CHANGE_DISK_PATHS procedure
- CHECK_PRODUCT_OPTIONS table function returns formatted information generated by the Check Product Option (CHKPRDOPT) CL command: CHECK_PRODUCT_OPTIONS table function
- COMPARE_IFS table function compares objects or directories in the Integrated File System (IFS): COMPARE_IFS table function
- COMMAND_INFO view returns information about CL commands: COMMAND_INFO view
- CONFIGURATION_STATUS view returns the status for each configuration description on the system: CONFIGURATION_STATUS view
- DB_TRANSACTION_JOURNAL_INFO returns information about journal resources for a transaction: DB_TRANSACTION_JOURNAL_INFO table function
- DB_TRANSACTION_OBJECT_INFO table function returns information about objects under commitment control for a transaction: DB_TRANSACTION_OBJECT_INFO table function
- DB_TRANSACTION_RECORD_INFO table function returns record level information for a transaction: DB_TRANSACTION_RECORD_INFO table function
- DEFECTIVE_PTF_CURRENCY view identifies defective PTFs on the system without the corresponding corrective PTF: DEFECTIVE_PTF_CURRENCY view
- DNS_LOOKUP_IP scalar function returns a hostname for a specified IP address: DNS_LOOKUP_IP scalar function
- END_JOBS procedure simplifies ending a group of jobs: END_JOBS procedure
- ENDED_JOB_INFO returns details about jobs that have ended: ENDED_JOB_INFO table function
- ERRNO_INFO scalar function returns descriptive text for an errno value: ERRNO_INFO scalar function
- GENERATE_SPREADSHEET generates a spreadsheet from a table or query: GENERATE_SPREADSHEET scalar function
- GETENV and PUTENV scalar functions add, change, retrieve the value of an environment variable: GETENV scalar function and PUTENV scalar function
- Hardware resource information: HARDWARE_RESOURCE_INFO view and HARDWARE_RESOURCE_INFO table function
- IFS_ACCESS scalar function indicates whether a file in the integrated file system can be accessed in the specified way: IFS_ACCESS scalar function
- IFS_RENAME renames an IFS file or directory: IFS_RENAME scalar function
- IFS_UNLINK scalar function deletes an IFS file: IFS_UNLINK scalar function
- JOB_QUEUE_ENTRIES returns a list of entries in a job queue: JOB_QUEUE_ENTRIES view
- JVM_INFO table function provides a wait time option for accessing JVM information: JVM_INFO table function
- NETWORK_ATTRIBUTE_INFO view returns information about the network attributes of the system: NETWORK_ATTRIBUTE_INFO view
- OBJECTCONNECT_INFO view and CHANGE_OBJECTCONNECT procedure provide ObjectConnect over IP information: OBJECTCONNECT_INFO view and CHANGE_OBJECTCONNECT procedure
- PING checks connectivity to another system: PING table function
- POWER_SCHEDULE_INFO view returns the power on and power off schedule for the next year: POWER_SCHEDULE_INFO view
- PRINTER_FILE_INFO view returns information about printer files: PRINTER_FILE_INFO view
- The PROCESS_SYSTEM_LIMITS_ALERTS procedure is documented: PROCESS_SYSTEM_LIMITS_ALERTS procedure
- PTF_COVER_LETTER returns the content of a cover letter: PTF_COVER_LETTER table function
- RDB_ENTRY_INFO view returns information about RDB directory entries: RDB_ENTRY_INFO view
- REPLY_INQUIRY_MESSAGES sends a reply to one or more QSYSOPR inquiry messages: REPLY_INQUIRY_MESSAGES scalar function
- SAVE_FILE_INFO view returns information about save files: SAVE_FILE_INFO view
- SAVE_FILE_OBJECTS view and table function return information about objects within save files: SAVE_FILE_OBJECTS view and SAVE_FILE_OBJECTS table function
- SEND_EMAIL sends an email to a user: SEND_EMAIL scalar function
- SERVICE_TOOLS_SERVER_INFO view returns service tools server configuration information: SERVICE_TOOLS_SERVER_INFO view
- SMAPP_ACCESS_PATHS view returns information about access paths monitored by System-Managed Access Path Protection (SMAPP): SMAPP_ACCESS_PATHS view
- SPECIAL_AUTHORITY_DATA_MART materialized query table can be used to gather all the special authorities for all users: SPECIAL_AUTHORITY_DATA_MART table
- SQLCODE_INFO returns message information that corresponds to a specific SQLCODE: SQLCODE_INFO table function
- Submitted Job Tracker services: ADD_TRACKED_JOB_QUEUE procedure, CLEAR_TRACKED_JOB_INFO procedure, REMOVE_TRACKED_JOB_QUEUE procedure, TRACKED_JOB_INFO table function, and TRACKED_JOB_QUEUES view
- SYSMEMBERSTAT view returns statistical information for file members, including program described files: SYSMEMBERSTAT view
- SYSTEM_OBJECT_TYPES table lists all the type values for system objects: SYSTEM_OBJECT_TYPES table
- TAPE_CARTRIDGE_INFO view returns information about tape cartridges: TAPE_CARTRIDGE_INFO view
- Support for creating and managing locking policies for NVMe devices: ADD_DEVICE_LOCKING_POLICY procedure, CHANGE_DEVICE_LOCKING_POLICY procedure, CREATE_LOCKING_POLICY procedure, DELETE_LOCKING_POLICY procedure, FACTORY_RESET_DEVICE procedure, LOCKING_POLICY_INFO view, REMOVE_DEVICE_LOCKING_POLICY procedure, and UNLOCK_DEVICE procedure
- Updated services
- ACTIVE_DB_CONNECTIONS table function returns information about Db2 Mirror suspend processing: ACTIVE_DB_CONNECTIONS table function
- ACTIVE_JOB_INFO table function returns additional query information: ACTIVE_JOB_INFO table function
- ACTIVE_QUERY_INFO has a filter for the job's user: ACTIVE_QUERY_INFO table function
- ASP_INFO returns new geographic mirroring information: ASP_INFO view
- ASP_INFO view, SYSTEM_STATUS_INFO view, and SYSTEM_STATUS table function return SMAPP information: ASP_INFO view, SYSTEM_STATUS_INFO view and SYSTEM_STATUS table function
- CANCEL_SQL allows a user to cancel their own job: CANCEL_SQL procedure
- CHANGE_USER_PROFILE allows special authorities to be changed: CHANGE_USER_PROFILE table function
- DB_TRANSACTION_INFO lets users see their own transaction information: DB_TRANSACTION_INFO view
- DISPLAY_JOURNAL has filters for commit cycle and internal entries: DISPLAY_JOURNAL table function
- DUMP_PLAN_CACHE has filters for SQL statement text, system queries, and Independent ASP name: DUMP_PLAN_CACHE procedure
- FIRMWARE_CURRENCY view includes additional columns with information about the server's firmware fixpack: FIRMWARE_CURRENCY view
- GROUP_PTF_INFO view has new authorization requirements: GROUP_PTF_INFO view
- HISTORY_LOG_INFO returns the job name in individual columns: HISTORY_LOG_INFO table function
- IFS_OBJECT_STATISTICS returns information about exit programs registered with open-related exit points: IFS_OBJECT_STATISTICS table function
- JOBLOG_INFO table function supports a longer string of message token values: JOBLOG_INFO table function
- JVM_INFO view returns the job name in individual columns: JVM_INFO view
- LPRINTF can print a longer string: LPRINTF procedure
- MTI_INFO returns lists of QRO hashes and plan identifiers that have used the MTI and returns the table's partition or member name: MTI_INFO table function
- NETSTAT_JOB_INFO returns the job name in individual columns: NETSTAT_JOB_INFO view
- OUTPUT_QUEUE_ENTRIES view and table function return TOTAL_RECORDS, MAXIMUM_RECORDS, and USER_DEFINED_DATA: OUTPUT_QUEUE_ENTRIES view and OUTPUT_QUEUE_ENTRIES table function
- OUTPUT_QUEUE_INFO returns the internet address: OUTPUT_QUEUE_INFO view
- PARSE_STATEMENT table function returns additional details for DDL statements: PARSE_STATEMENT table function
- PROGRAM_INFO view returns copyright strings for ILE programs and service programs: PROGRAM_INFO view
- PTF_INFO view returns additional product option information, the latest superseding PTF, and has new authorization requirement: PTF_INFO view
- STACK_INFO table function has a new IGNORE_ERRORS parameter to allow processing to continue when an error is encountered: STACK_INFO table function
- SYSDISKSTAT view and table function return an indication of whether all the pages for the disk unit have been cleared, world wide port number information for certain devices, and a hardware status value: SYSDISKSTAT view and SYSDISKSTAT table function
- SYSFILES view returns based on file information for logical files and views: SYSFILES view
- SYSTEM_STATUS_INFO and SYSTEM_STATUS return basic summary information: SYSTEM_STATUS_INFO view and SYSTEM_STATUS table function
- SYSTEM_STATUS_INFO view, SYSTEM_STATUS_INFO_BASIC view, and SYSTEM_STATUS table function return both the machine serial number and the virtual serial number, and the enterprise enabled processor count: SYSTEM_STATUS_INFO view, SYSTEM_STATUS_INFO_BASIC view, and SYSTEM_STATUS table function
- SYSTEM_VALUE_INFO returns addition information about system values and a formatted version of the system value: SYSTEM_VALUE_INFO view
- USER_INFO_BASIC returns null for the USER_DEFAULT_PASSWORD column. Use the USER_INFO view to access this information: USER_INFO_BASIC view
- WLM_SET_CLIENT_INFO procedure supports a verbose option: WLM_SET_CLIENT_INFO procedure
How to see what's new or changed
To help you see where technical changes have been made, this information uses:
- The
image to mark where new
or changed information begins. - The
image to mark where new
or changed information ends.
To find other information about what's new or changed this release, see the Memo to users.
