Db2 commands

About Db2 commands

See the following sections for general information about commands in Db2 for z/OS.

• Privileges and authorization IDs for commands
• Syntax rules for DSN subcommands
• Syntax rules for Db2 commands
• Data sharing scope of commands
• Output from commands
• Recovery logs and commands

The DSN command and DSN subcommands

DSN the Db2 command processor and executes as a TSO command processor.

DSN command (TSO)

The TSO command DSN starts a DSN session.

END command (TSO)

The DSN subcommand END is used to end the DSN session and return to TSO.

All of the DSN subcommands, except SPUFI, run under DSN in either the foreground or background. All commands except BIND QUERY and END also run under Db2 Interactive (DB2I). SPUFI runs only in the foreground under ISPF.

Options that are used in the several BIND and REBIND subcommands of the DSN are documented separately. For descriptions of these options, see BIND and REBIND options for packages, plans, and services .

DSN subcommand	Description
ABEND	The DSN subcommand ABEND causes the DSN session to terminate with abend completion code X'04E' and reason code of X'00C50101'. Important: The ABEND subcommand is used for diagnostic purposes only, and is intended to be used only under the direction of IBM Support. Use it only when diagnosing a problem with DSN or Db2.
BIND PACKAGE	The DSN subcommand BIND PACKAGE builds an application package. Db2 records the description of the package in the catalog tables and saves the prepared package in the directory. BIND PACKAGE also deletes phased-out package copies.
BIND PLAN	The DSN subcommand BIND PLAN builds an application plan. All Db2 programs require an application plan to allocate Db2 resources and support SQL requests made at run time.
BIND QUERY	The DSN subcommand BIND QUERY reads the statement text, default schema, and a set of bind options from every row of DSN_USERQUERY_TABLE, and information from correlated EXPLAIN table rows. When LOOKUP(NO) is in effect, Db2 inserts the pertinent data into certain catalog tables.
BIND SERVICE	The BIND SERVICE (DSN) subcommand builds an application package that represents a Db2 REST service.
DCLGEN (declarations generator)	The declarations generator (DCLGEN) produces an SQL DECLARE TABLE statement and a COBOL, PL/I, or C data declaration for a table or a view named in the catalog.
FREE PACKAGE	The FREE PACKAGE subcommand can be used to delete a specific version of a package, all versions of a package, or whole collections of packages.
FREE SERVICE	The FREE SERVICE subcommand deletes an application package that represents a Db2 REST service.
FREE PLAN	The FREE PLAN subcommmand deletes application plans from Db2.
FREE QUERY	The FREE QUERY subcommand removes rows from certain catalog tables for one or more queries. If any of the specified queries are in the dynamic statement cache, FREE QUERY purges them from the dynamic statement cache.
FREE STABILIZED DYNAMIC QUERY	The FREE STABILIZED DYNAMIC QUERY command removes from certain catalog tables one or more stabilized dynamic queries. If any of the specified queries are in the dynamic statement cache, FREE STABILIZED DYNAMIC QUERY also purges the statements from the dynamic statement cache.
REBIND PACKAGE	The DSN subcommand REBIND PACKAGE rebinds an application package when you make changes that affect the package, but have not changed the SQL statements in the program.
REBIND PLAN	The DSN subcommand REBIND PLAN rebinds an application plan when you make changes to the attributes of the plan, such as the package list.
REBIND TRIGGER PACKAGE	The DSN subcommand REBIND TRIGGER PACKAGE rebinds a package for a basic trigger. You can identify basic triggers by querying the SYSIBM.SYSTRIGGERS catalog table. Blank values in the SQLPL column identify basic triggers. For advanced triggers, use the REBIND PACKAGE command instead.
RUN	The DSN subcommand RUN executes an application program, which can contain SQL statements.
SPUFI	The DSN subcommand SPUFI executes the SQL processor using file input.

Db2 commands

You can use Db2 commands to control most of the operational environment.

START DB2 commands can be issued only from a z/OSconsole or TSO SDSF. All other Db2 commands can be issued from the following environments:

• z/OS consoles
• TSO terminals, by any of the following methods:
  • Issuing the DSN command from the TSO READY prompt
  • Entering commands in the DB2 Commands panel in DB2I
• IMS terminals
• Authorized CICS® terminals

Db2 commands that are issued from a logged-on z/OS console or TSO SDSF can be checked by Db2 authorization using primary and secondary authorization IDs. A logged-on z/OS user ID must be defined in RACF or a similar security server.

Db2 command	Description
-ACCESS DATABASE	The -ACCESS DATABASE command forces a physical open of a table space, index space, or partition, or removes the GBP-dependent status for a table space, index space, or partition, or externalizes the real-time statistics and optimizer statistics recommendations from in-memory blocks to the appropriate catalog tables. The MODE keyword specifies the desired action.
-ACTIVATE	The -ACTIVATE command enables use of new capabilities and enhancements at the specified function level, and lower function levels. Use of the ACTIVATE command to activate function level 500 or higher also marks the boundary between the ability to coexist with or fallback to Db2 11.
-ALTER BUFFERPOOL	The -ALTER BUFFERPOOL command alters attributes for active or inactive buffer pools. Altered values are used until altered again.
-ALTER GROUPBUFFERPOOL	The -ALTER GROUPBUFFERPOOL command alters attributes of group buffer pools.
-ALTER UTILITY	The -ALTER UTILITY command changes the values of certain parameters of an execution of the REORG utility that uses SHRLEVEL REFERENCE or CHANGE and the REBUILD utility that uses SHRLEVEL CHANGE.
-ARCHIVE LOG	The -ARCHIVE LOG command enables a site to close a current active log and open the next available log data set.
-CANCEL THREAD	The -CANCEL THREAD command cancels processing for specific local or distributed threads.
-DISPLAY ACCEL	The -DISPLAY ACCEL command displays information about accelerator servers.
-DISPLAY ARCHIVE	The -DISPLAY ARCHIVE command displays input archive log information.
-DISPLAY BLOCKERS	The -DISPLAY BLOCKERS command displays locks and claims that active threads hold against the databases, tables, indexes, or spaces that are specified in the command.
-DISPLAY BUFFERPOOL	The -DISPLAY BUFFERPOOL command displays the current status for one or more active or inactive buffer pools.
-DISPLAY DATABASE	The -DISPLAY DATABASE command displays status information about Db2 databases.
-DISPLAY DDF	The -DISPLAY DDF command displays information regarding the status and configuration of DDF, as well as statistical information regarding connections or threads controlled by DDF.
-DISPLAY DYNQUERYCAPTURE	The -DISPLAY DYNQUERYCAPTURE command displays all currently active dynamic query capture monitors.
-DISPLAY FUNCTION SPECIFIC	The -DISPLAY FUNCTION SPECIFIC command displays statistics about external user-defined functions that Db2 applications access.
-DISPLAY GROUP	The -DISPLAY GROUP command displays information about the data sharing group to which a Db2 subsystem belongs, including information about the code level, catalog level, and function level of the group.
-DISPLAY GROUPBUFFERPOOL	The -DISPLAY GROUPBUFFERPOOL command displays information about the status of Db2 group buffer pools. It can also display related statistics.
-DISPLAY LOCATION	The -DISPLAY LOCATION command displays various information about the specified remote locations.
-DISPLAY LOG	The -DISPLAY LOG command displays log information about the status of the offload task, the current checkpoint scheduling parameters, and the current active log data sets.
-DISPLAY ML	The command -DISPLAY ML command displays the current status of IBM Db2 AI for z/OS.
-DISPLAY PROCEDURE	The -DISPLAY PROCEDURE command displays statistics about stored procedures that are accessed by Db2 applications.
-DISPLAY PROFILE	The DISPLAY PROFILE command allows you to determine if profiling is active or inactive
-DISPLAY RLIMIT	The -DISPLAY RLIMIT command displays the current status of the resource limit facility (governor).
-DISPLAY RESTSVC	The -DISPLAY RESTSVC command displays the status of REST services that exist in Db2.
-DISPLAY STATS	The -DISPLAY STATS command displays statistics about the use of resources by Db2 for certain processes.
-DISPLAY THREAD	The -DISPLAY THREAD command displays current status information about Db2 threads.
-DISPLAY TRACE	The -DISPLAY TRACE command displays a list of active traces.
-DISPLAY UTILITY	The -DISPLAY UTILITY command displays the status of utility jobs, including utility jobs in a data sharing group.
-MODIFY DDF	The MODIFY DDF command modifies the status and configuration of the Db2 distributed data facility (DDF) and statistics for connections or threads that are controlled by DDF.
-MODIFY TRACE	The -MODIFY TRACE command changes the IFCIDs (trace events) associated with a particular active trace.
-RECOVER BSDS	The -RECOVER BSDS command reestablishes dual bootstrap data sets (BSDS) after one has been disabled by a data set error.
-RECOVER INDOUBT	The -RECOVER INDOUBT command recovers threads that are left in an indoubt state because Db2 or a transaction manager could not automatically resolve the indoubt status with the commit coordinator.
-RECOVER POSTPONED	The Db2 command RECOVER POSTPONED completes back-out processing for units of recovery that are left incomplete during an earlier restart (POSTPONED ABORT units of recovery). Use this command when automatic resolution is not selected.
-REFRESH DB2,EARLY	The -REFRESH DB2,EARLY command reloads the ERLY code modules that were loaded at IPL time, and rebuilds the ERLY control block.
-RESET GENERICLU	The RESET GENERICLU command allows you to purge information stored by VTAM in the coupling facility for one or more partners of a particular Db2 subsystem.
-RESET INDOUBT	The -RESET INDOUBT command purges the information that is displayed in the indoubt thread report that is generated by the DISPLAY THREAD command.
-SET ARCHIVE	The -SET ARCHIVE command sets the maximum number of tape units for the archive log. It also sets the maximum deallocation time of tape units for the archive log.
-SET LOG	You can use -SET LOG command to modify checkpoint frequency, to suspend or resume logging, or to add a new active log.
-SET SYSPARM	You can use the -SET SYSPARM command to change subsystem parameters that can be updated online, while Db2 is started.
-START ACCEL	The -START ACCEL command notifies the Db2 subsystem that it should use the specified accelerator servers.
-START CDDS	The -START CDDS command directs all members of a Db2 data sharing group to allocate and open the compression dictionary data set (CDDS).
-START DATABASE	The -START DATABASE command makes the specified database available for use.
-START DB2	The -START DB2 command initializes the Db2 subsystem. When the operation is complete, the Db2 subsystem is active and available to TSO applications and to other subsystems (for example, IMS and CICS).
-START DDF	The -START DDF starts the distributed data facility (DDF) if it is not already started.
-START DYNQUERYCAPTURE	The -START DYNQUERYCAPTURE command stabilizes access paths for qualified cached dynamic queries. This command can also optionally start monitoring of cached dynamic queries that qualify for a scope but have not met the specified execution threshold for stabilization.
-START FUNCTION SPECIFIC	The -START FUNCTION command SPECIFIC starts an external function that is stopped. Built-in functions or user-defined functions that are sourced on another function cannot be started with this command.
-START ML	The -START ML command starts the Db2 functions used by IBM Db2 AI for z/OS.
-START PROCEDURE	The -START PROCEDURE command activates the definition of a stored procedure that is stopped or refreshes one that is stored in the cache. You can qualify stored procedure names with a schema name.
-START PROFILE	The -START PROFILE command loads or reloads the profile table into a data structure in memory.
-START RLIMIT	The -START RLIMIT command starts the resource limit facility (governor) and specifies a resource limit specification table for the facility to use.
-START RESTSVC	The -START RESTSVC command starts the definition of a REST service that is stopped. You can qualify REST service names with a collection ID name.
-START TRACE	The -START TRACE command starts Db2 traces.
-STOP ACCEL	The -STOP ACCEL command causes the Db2 subsystem to stop using the specified accelerator servers.
-STOP CDDS	The -STOP CDDS command directs all members of a Db2 data sharing group to close and deallocate the compression dictionary data set (CDDS).
-STOP DATABASE	The -STOP DATABASE command makes the specified objects unavailable for applications and closes their data sets.
-STOP DB2	The -STOP DB2 command stops the Db2 subsystem.
-STOP DDF	The -STOP DDF command stops the distributed data facility (DDF) if it has already been started; use this command to terminate the DDF interface to VTAM or TCP/IP.
-STOP DYNQUERYCPATURE	The -STOP DYNQUERYCAPTURE command stops the capture of dynamic SQL statements by the specified monitors.
-STOP FUNCTION SPECIFIC	The -STOP FUNCTION command SPECIFIC prevents Db2 from accepting SQL statements with invocations of the specified functions.
-STOP ML	The -STOP ML command stops the Db2 functions used by IBM Db2 AI for z/OS, if IBM Db2 AI for z/OS has already been started.
-STOP PROCEDURE	The -STOP PROCEDURE command prevents Db2 from accepting SQL CALL statements for one or more stored procedures.
-STOP PROFILE	The -STOP PROFILE command is used to stop or disable the profile function.
-STOP RESTSVC	The -STOP RESTSVC command prevents Db2 from accepting any new discover details or invoke requests for one or more REST services. You can qualify REST service names with a collection ID name.
-STOP RLIMIT	The -STOP RLIMIT command stops the resource limit facility.
-STOP TRACE	The -STOP TRACE command stops tracing.
-TERM UTILITY	The -TERM UTILITY command terminates execution of a Db2 utility job step and releases all resources associated with the step.

The extended MCS console feature enables a z/OS system have more than 99 consoles. Because Db2 supports extended MCS consoles, messages returned from a Db2 command are routed to the extended MCS console that issued the command.

Message DSN9022I indicates the normal end of Db2 command processing; DSN9023I indicates the abnormal end of Db2 command processing.

Administrative task scheduler commands

You can use administrative task scheduler commands to start, stop, and change the administrative task scheduler. All administrative task scheduler commands can be issued from a z/OS console.

z/OS IRLM commands

You can use z/OS Internal Resource Lock Manager (IRLM) commands to start, stop, and change the IRLM. All z/OS IRLM command can be issued from a z/OS console.

IMS commands

You can use the following IMS commands to control IMS connections as well as to start and stop connections to Db2 and display activity on the connections. You can issue IMS commands from an IMS terminal or you can invoke IMS transactions or commands by using the Db2-supplied stored procedures DSNAIMS or DSNAIMS2. DSNAIMS2 has the same functions as DSNAIMS but also provides multi-segment input support for IMS transactions.

For descriptions of the IMS commands, see IMS commands.

/CHA SUBSYS DB2 RESET

/CHA SUBSYS ALL RESET

/CHA SUBSYS DB2 OASN 99 685 2920 RESET

/DISPLAY SUBSYS ALL

SUBSYS    CRC    REGID    PROGRAM    LTERM    STATUS
SSTR      ?                                   CONN
                   1      DDLTLM17   PTERM01  CONN,ACTIVE
                   2      DDLTLM06   PTERM02  CONN
*85202/065933*

/TRACE SET ON TABLE SUBS OPTION LOG

/TRACE SET ON TABLE ALL OPTION LOG

CICS attachment facility commands

You can use CICS commands to control CICS connections as well as to start and stop connections to Db2 and display activity on the connections. Each CICS attachment facility command can be issued from a CICS terminal.

• Issuing commands to Db2 using the DSNC transaction
• DSNC DISCONNECT
• DSNC DISPLAY
• DSNC STOP
• DSNC STRT

TSO CLISTs

You can use Time Sharing Option (TSO) commands to perform TSO tasks such as prepare and execute programs under TSO. For example, see DSNH command procedure (TSO CLIST)