DB2 10.5 for Linux, UNIX, and Windows

DB2 command and SQL statement changes summary

DB2® Version 10.5 introduces changes to DB2 CLP commands, DB2 system commands, and SQL statements to support new capabilities. Also, some DB2 CLP commands, DB2 system commands, and SQL statements are deprecated or discontinued. These changes can affect your existing database applications or database administration scripts.

Changes to DB2 commands

The following DB2 commands have been changed:

Table 1. Changed DB2 commands
DB2 command name	Details about the change
db2iupgrade	The default behavior of db2iupgrade command is changed as of DB2 Cancun Release 10.5.0.4. The new –g parameter performs a global upgrade on all the members and CFs in a DB2 pureScale® cluster.
db2cat	The db2cat output now displays information for random ordering of index keys. Since random ordered indexes are extended indexes, Extend Index Info is also displayed for any output that is returned by the command.
db2exfmt	The db2exfmt output now displays information for random ordering of index keys. `R` indicates a column with a RANDOM order. `A` indicates a column with an ASCENDING order. `D` indicates a column with a DESCENDING order. The db2exfmt command now also generates output for queries that access column-organized tables.
db2expln	The db2expln output now displays information for random ordering of index keys. `Random` indicates a column with a RANDOM order. The db2expln command now also generates output for queries that access column-organized tables.
db2iupdt	The new -commit_level parameter completes the update of a DB2 pureScale instance to a higher code level. The new -check_commit parameter determines whether the update of a DB2 pureScale instance to a higher code level can be completed successfully. The new -recover_ru_metadata parameter recovers metadata information from backup files related to online fixpack updates in a DB2 pureScale environment.
db2level	This command now displays the current version and service level for a IBM® DB2 pureScale instance. To display the same information for a member, use the db2level command with the -localMember parameter. In earlier releases, the db2level command displayed the current version and service level for the member where the command was issued.
db2look	The db2look command now generates DDL statements to create column-organized tables in addition to row-organized tables.
db2pd	The -apinfo parameter now displays more information about current and past activities of the unit of work (UOW). The -edus parameter now also displays the agents that are processing column-organized data. The new -ruStatus parameter displays the status of a fix pack update in a DB2 pureScale instance. The showlocks parameter now displays the TableNm and SchemaNm columns, which indicates the table name and schema name of locks that are being held by applications. To display this information, you must use the showlocks parameter with the -locks parameter. The -tablespace parameter now displays the RSE column to indicate whether the reclaimable space feature is enabled. The -transactions parameter now displays the total number of application commits and the total number of application rollbacks. The new details uboption for the -wlocks parameter displays the TableNm, SchemaNm, and AppNode columns for application locks that are being waited on. The new -extentmovement parameter displays the extent movement status of a database. The new -membersubsetstatus parameter displays the state of member subsets. The new -subsetid parameter identifies subsets.
db2support	The -d parameter now supports collection of information from multiple databases. To specify multiple databases, separate the database names with a comma. The new -alldatabases parameter specifies that information about all databases in the database directory is collected. The new -system_group parameter collects system-related information about the specified system group. The new -system_user parameter collects system-related information about the specified system user. The new -wlm parameter collects information that is related to WLM issues as part of the optimizer mode with collection level 0 and above.
db2_install	Starting with DB2 Version 10.5 Fix Pack 5, you can install some DB2 product configurations without the IBM Global Security Kit (GSKit). You can use the new -f noencryption parameter to specify that the IBM GSKit should not be installed. The encryption facility is not available for the DB2 instance if you use this installation parameter. Users and applications cannot encrypt or decrypt data in the database or in transit between DB2 servers and clients. You can use the new INSTALL_ENCRYPTION response file keyword to specify whether to install the IBM GSKit in a response file installation.
installFixPack	The new -check_commit parameter determines whether the update of a DB2 pureScale instance to a higher code level can be completed successfully. The new -commit_level parameter updates the DB2 pureScale instance to a new level of code. The new -I parameter specifies the DB2 pureScale instance name to which the fix pack update is applied. The new -online parameter starts or continues with an online fix pack update. The new -offline parameter starts or continues with an offline fix pack update. The new -quiesce_time_out parameter specifies how long the command waits before disconnecting applications from the given member. Once this timeout is reached, any active units of work remaining at the time are interrupted. The new -show_level_info parameter displays the attribute status of a fix pack level.
LOAD	For column-organized tables, automatic statistics collection occurs by default during the execution of the LOAD REPLACE command. To disable automatic statistics collection, specify the STATISTICS NO parameter. Also, during a LOAD REPLACE operation against a column-organized table, the column compression dictionaries are replaced by default.

Changes to SQL statements

The following SQL statements have been changed:

Table 2. Changed SQL statements
SQL statement	Details about the change
CREATE TABLE statement	New ORGANIZE BY COLUMN and ORGANIZE BY ROW clauses specify whether the table data is stored with column or row organization. If you do not specify either of these clauses, the data is stored in accordance with the default table organization that is specified by the dft_table_org database configuration parameter. The default table organization is row, unless you set the DB2_WORKLOAD registry variable is set to ANALYTICS before creating the database. For more details, see Creating column-organized tables.
CREATE INDEX statement	New RANDOM clause to specify a random ordering for the index that is created.

Discontinued DB2 commands or SQL statements

The following DB2 commands or SQL statements are discontinued:

Table 3. Discontinued DB2 commands or SQL statements
DB2 command or SQL statement	Details about the change
db2IdentifyType1	The db2IdentifyType1 command was provided to help you convert type-1 indexes to type-2 before upgrading to Version 9.7 because type-1 indexes were discontinued in Version 9.7. In Version 10.5, this command is no longer required because you can only upgrade from Version 9.7 or later releases. Databases in these releases no longer have type-1 indexes.
STATISTICS YES parameter of the LOAD command	The STATISTICS YES parameter of the LOAD command is discontinued. The functionality associated with this parameter has been replaced with the functionality associated to the STATISTICS USE PROFILE parameter.
dynexpln	The dynexpln command is discontinued. Use the db2expln command instead of the dynexpln command. The db2expln command provides equivalent functionality because it can process dynamic statements directly.