JRH DB2I2 Commands

The JRH DB2I2 command consists of a db2i2 command and command options. The global command options can be used with most of the JRH DB2I2 command are:

  • %=### ###% Space adjustment from the current space allocation. Use sub-parameter CYL or TRK to roundup to cylindeer or track boundary.
  • APPEND output append to an existing sequential file.
  • DSPRE=dataset-prefix assign dataset-prefix for the output or work files.
  • ERROR(CONTINUE|SKIP #) continue process next JRH DB2I2 command or skip # JRH DB2I2 commands in batch mode if there is an error return from the current JRH DB2I2 command.
  • IDD='line-object.input.ddname' direct JRH DB2I2 to read the specified file ddname in batch mode process as the line object input source.
  • IDSN='line-object.input.dsname' direct JRH DB2I2 to read the specified file as the line object input source.
  • JOB#=## assign the start job number for generated JCL when ## is used on the jobcard.
  • JOBCARD=N directs JRH DB2I2 to disable the generation of JOBCARD.
  • NEWJOB=## directs JRH DB2I2 to generate a new jobcard for ## of job steps.
  • NOTFOUND(CONTINUE|SKIP #) continue process next JRH DB2I2 command or skip # JRH DB2I2 commands in batch mode if there is No Record Found from the current JRH DB2I2 command.
  • ODSN='output.dsname' directs JRH DB2I2 to store output into the specified file.
  • SKIP(#1,#2) skip #1 JRH DB2I2 commands #2 times in batch mode. #1 can be a negative number.
  • SQLID=sqlid generate SET CURRENT SQLID to specified sqlid before any of applicable generated script output. For example, MIGR or GRANT commands.
  • STEP#=## assign step number for the JRH DB2I2 generated output JCL
  • T=N instruct JRH DB2I2 not to print any of the title or heading/footing information for the output
  • WKSP=# instruct JRH DB2I2 to override the default output file allocation from CYL,1,1 to CYL,#,1

The following is a list of all the DB2I2 Commands you can use on the command line:

JRH DB2I2 Command List

JRH DB2I2 Command Syntax

Line Object allowed

Command Description

drill down - leave as blank

DB,TS,TB,AL,SY,VW,IX,

PG,PL,SG,DM,TP,IP,DS

Drill down from the selected line object

AL

TB,VW,PG,PL,DB

Generate Alias line objects from the selected line object

ALTER

DB,TS,TB,IX,SG,SP,FU

Generate ALTER DDL for the selected line object

AUTH [GRANTOR|GRANTEE]

AL,BP,CL,DB,PG,PL,

SG,TS,TB,US,VW,

DT,SH,FU,SP,TR for V6 or above

Display Authorization information for the selected line objects

BATCH db2i2 command

N/A

Generate BATCH JCL to run DB2I2 commands. Majority of the DB2I2 command can be executed through batch mode, so that you can free your terminal for the long running command, or schedule it to run during the preferred time

BIND [CL=collection] [O=owner] [Q=qualifier] [MEM=*] [GRANT=Y]

PG, PL

Generate DB2 BIND command for the selected package or plan

BIND COPY [CL=collection] [O=owner] [Q=qualifier] [GRANT=Y]

PG

Generate DB2 BIND COPY commands against selected package

CHECK

TS, IX, TP, IP

Generate DB2 CHECK DATA or CHECK INDEX utility JCL for selected table space or index

COAUTH [GRANTOR|GRANTEE]

AL, TB, US, VW

Display Column Authorization information for the selected line objects

CONNECT(location name)

N/A

connect to remote location

CONNECT(RESET)

N/A

reset connection to default location

COPY ['copy.parmutil.dsname']

TS, TP

IX, IP for V6 or above

Generate DB2 IMAGE COPY or MERGCOPY utility JCL for selected table space. If 'copy.parm.dsname' is specified, the copy option will be generated based on the 'copy.parm.dsname'

COPYAUTH [GRANTOR|GRANTEE]

[TO=to-userid]

US

Generate GRANT DCL for specified DB2 user. This command is useful to copy all the authorizations from one DB2 user to another

 

 

 

CURSORD

TB, AL, SY, VW

Generate DECLARE CURSOR embedded SQL statement for the selected line object

DBAUTH [GRANTOR|GRANTEE]

DB, US

Display Database Authorization information for the selected line objects

DBDSIZE [threshold]

DB

Display Database DBD size and issues warning for any Database size grater than threshold,. Default is 300000 bytes

DB2CMD [LIST]

DB2 command lines

Issue DB2 Command using the line objects specified. Use LIST option to list all available DB2 commands

DCLGEN

TB,AL,VW, SY

Generate DB2 DCLGEN commands for the selected Table, Alias or View

DDL [%=### [CYL|TRK]] [ALLOC=(alloc_type,pri,sec)]

[SQLTERM(?) for V6 or above)]

AL, DB, TS, TB, IX, SY, VW, SG, RI

DT,SP,FU,TR for V6 or above

Generate DDL for the selected line object. ##% allows allocation space increase base on ##

DELETE [MAP='dclgen dsn']

TB,AL,SY,VW

Generate SQL DELETE statements for the selected table, alias, synonyms or view

DISPLAY [DB2 display command option]

DB, TS, IX, TP, IP

Issue DB2 DISPLAY command against specified database, tablespace or index. Ex. DISPLAY RESTRICT LIMIT(*)

DSADJ [%=### [CYL|TRK]] [MOVE=YES|NO] [ALLOC=(alloc_ype,pri,sec)]

TP,IP

Generates JCL to adjust the space allocation for the selected line objects. Use ##% option to adjust the space allocation ## percent increase. MOVE=YES option generates DSN1COPY step to adjust the VSAM linear dataset

DSCOPY [ICOPY=N|Y]

SC,XC

Generates DSN1COPY utility JCL to copy Tablespace or Indexspace with OBID translation extracted. ICOPY options allows imagecopy be used

DSNJU004 [BSDS=bsds.dsname]

N/A

Interface to DSNJU004 print log map interactively. Use BSDS= option to read the BSDS information directly from specified file. This is useful if DB2 subsystem is not up and running

DSNTEP2

SQL blocks

Interface to DSNTEP2 interactively

DSNTIAD

SQL blocks

Interface to DSNTIAD interactively

DSNTIAUL [DEV=D|T] [SQL]

TB,VW, AL, SY,

SQL blocks

Generate DSNTIAUL utility JCL for the selected table, view, alias or SQL blocks (Use SQL option). SYSREC output can either go to disk as default (DEV=D) or send to tape with DEV=T option

DSN1COPY

TS, IX, TP, IP

Generates DSN1COPY utility JCL for the selected line objects

DSN1LOGP [BSDS='bsds.dsname'] [BSDS|ACTV|ARCH]

TS, IX, TP, IP

Generates DSN1LOGP utility JCL for the selected line objects. . Use BSDS= option to read the BSDS information directly from specified file. This is useful if DB2 subsystem is not up and running. Use BSDS, ACTV or ARCH to specify the option for log print either from an active log, archive log or from RBA range by BSDS information

DSN1PRNT

TS, IX, TP, IP

Generate DSN1PRNT utility JCL for the selected line objects

DT

TB

Generate DT - distinct type from the selected line objects

ED 'edit.dsname'

N/A

Use ED to edit an existing dataset in online mode

EDIT 'edit.dsname'

ISPF Edit commands

END_EDIT

N/A

Use EDIT and END_EDIT pair commands in batch mode to allow ISPF edit commands be executed against a selected dataset

EXEC [#] [ERR=CONT]

DB2I2 scripts

Execute DB2 Commands, IDCAMS commands, DDL, DCL or any of the DB2I2 scripts generated from various DB2I2 commands such as MIGR or DDL. [#] is the batch restart line number

EXPLAIN [#|0] [O=planowner]

SQL statements block or DBRM statements block

Invokes DB2 EXPLAIN and display EXPLAIN output report. Query block number 0 is used as default explain output. Use [#] option to direct DB2I2 to use # as the result QUERYNO. Use O=planowner option to SET SQLID for unqualified name from DBRM statement (This option is only allowed if you have authorization to issue SET SQLID command)

EXPLAINP [O=plan-owner] [P=Program-Name]

N/A

Display report from existing PLAN_TABLE

FETCH MAP='dclgen dataset'

TB, AL, SY, VW

Generate fetch into embedded SQL statements against selected line object. The host variables from DCLGEN dataset are mapped onto column name

FILST 'flist.dsname'

N/A

List the content of a dataset. 'flist.dsname' is used to specify the name of the dataset

FREE [KEEP=0|#]

PG, PL

Generate DB2 FREE commands against selected package or plan. KEEP=# option allows you to generate FREE command with # of version to keep for a db2 package

FU

TB, VW, PG, PL

Generate FU - function for the sekected line object.

GENVCAT [VOL=*]

TS, TP, IX, IP

Generates VCAT information from the selected line objects. VOL=* option generate IDCAMS define with VOLUMES(*)

GRANT [FROM=set-sqlid] [TO=to-user] [grant options]

AL, BP, CL, DB, PG, PL, SG, TS, TB, US, VW

Generates GRANT DCL for the selected line objects

HELP [db2i2 command]

N/A

Displays HELP screen

HELPLO [db2i2 line object]

N/A

Displays HELP screen for DB2I2 line object

HMIGRATE

DS, AR

Invokes HSM HMIGRATE to migrate DS line objects or AR-archive log line objects

HRECALL

DS, AR

Invokes HSM HRECALL to recall DS line objects or AR-archive log line object

IMPACT

AL, DB, TS, TB, IX, SY, VW

Generate impact line obejcts for selected objects

INFO

N/A

Display SSID information and connection information

INSERT [MAP='dclgen dsn']

TB, AL, SY, VW

Generate SQL INSERT statements for the selected line objects.Use MAP option to generate embedded SQL

IP

DB, IX, TB

Generate IP - Index Part line object for the selected line objects.

IX

DB, TS, TB, PL, PG

Show IndeX usage for a selected line object

JOBCARD

N/A

Set up JOBCARD Information. Please specify this if you want to use any JCL generated from DB2I2

LISTC [EXT(##)] [TSIX] [IN]

TS, IX, TP, IP, DS

Generates IDCAM List Catalog information against select line object. The summary extent and usage information is displayed. EXT(##) option direct DB2I2 to only list information when extents > ##. TSIX option allows DB2I2 to display TP or IP information instead of DS information. IN option insert output directly into your edit session

LOAD ['load.parmutil.dsname']

TB tbname [#]

Generate DB2 LOAD utility JCL for selected table with calculated space for work files. If multiple TB line objects are selected, you can use # on each TB line to indicate the estimated # of rows to be loaded. Make sure you select 0 on the estimated row field on the selection screen to allow estimated row to be selected from the TB line

MIGR [AL=Y] [SY=Y] [VW=Y] [BIND=Y] [GRANT=Y] [##%]

AL, DB, TS, TB, IX, SY, VW, SG

Generate migration DB2I2 script which includes all the DDL, IDCAM Defines, BIND plan, BIND package and DCL GRANT for selected objects. You can modify the generated DB2I2 scripts to fit your need. The scripts can then be executed by using the DB2I2 EXEC command

MODIFY ['modify.parm.dsname']

TS, TP

Generate DB2 MODIFY utility JCL for the selected line objects.Additional job step issues TSO DELETE for all the image copy data sets deleted from Ibm system catalog

PACKIT [CL=collection-ID] [O=owner] [Q=qualifier] [MEM=*]

DM, PL

Generate DB2 BIND command for the selected DBRM or plan

PARMUTIL[REORG|COPY|RUNSTATS|RECOVER|MODIFY|REPAIR|LOAD]

'parmutil.dsname'

N/A

Generate utility parameter control statement file for DB2 REORG, COPY, LOAD, RUNSTATS, MODIFY, REPAIR or RECOVER

PG

TS, AL, TB, SY, VW, IX, PL

Displays associated DB2 Package information for the selected line objects

PGAUTH [GRANTOR|GRANTEE]

PG, US

Display Package Authorization information for the selected line objects

PL

TS, AL, TB, SY, VW, IX, PG

Displays associated DB2 Plan information for the selected line objects

PLAUTH [GRANTOR|GRANTEE]

PL, US

Display Plan Authorization information for the selected line objects

QBUILD [F1=field name1] [F2=field name 2] [F3=field name 3] [F4=field name 4]

any valid line object except AC, AR, CI, CO, CP, CU, GV, IC, RL

Generates SQL WHERE predicates directly from the selected line objects

QUIESCE

TS, TP

Generates DB2 QUIESCE utility JCL for the selected line objects

RBA [yyyy-mm-dd] [JU004='dsnju004.output']

TS, TP

IX, IP for V6 or above

Generates all available RBA/RLSN points from SYSIBM.SYSCOPY table for the selected line objects

REBIND

PG, PL

Generate DB2 REBIND commands for the selected package or plan

REBUILD ['rebuild.parmutil.dsname']

IX, IP

Generate DB2 REBUILD utility JCL for the selected line objects

RECOVER ['recover.parmutil.dsname']

TS, IX, TP, IP

Generate DB2 RECOVER utility JCL for the selected line objects

REORG ['reorg.parm.dsname'] [RCHK]

TS, IX, TP, IP

Generate DB2 REORG utility JCL for the selected tablespace or index. If 'reorg.parm.dsname' is specified, the reorg option is generated based on the 'reorg.parm.dsname'. Use option RCHK to generate REORGCHK step before REORG step

REORGCHK RC(CC1,CC2)

TS,TP,IX,IP

Use REORGCHK to set condition in batch mode to CC1 if a tablespace, tablepart, index or indexpart does not require to run REORG. Set to CC2 if it does require to run REORG. The default CC1 is 0 and CC2 is 1

REPAIR ['repair.parmutil.dsname']

DB,TS, IX, TP, IP

Generate DB2 REPAIR utility JCL for the selected tablespace or index

REPORT

TS, TP

Generate DB2 REPORT utility JCL for the selected tablespace

RESETG

 

Reset Global variables.

REVOKE [FROM=from-user] [revoke opitons]

AL, BP, CL, DB, PG, PL, SG, TS, TB, US,VW

Generates REVOKE DCL for the selected line objects

REXX [IDD=input.ddname|IDSN=input.dsname]

N/A

Use REXX DB2I2 command in batch mode to invoke REXX Exec or TSO CLIST with a stream of DB2I2 commands. Either IDD= or IDSN command option is required for this commnad. Use command option IDD= to indicate the DDNAME of the REXX/CLIST source. Use IDSN= to indicate the DSNAME of the REXX/CLIST source

RI

TB, DB

Generates RI - referential integrity line object for selected line objects

RSAUTH [GRANTOR|GRANTEE]

BP, CL, SG, TS, US

Display Resource Authorization information for the selected line objects

RUN [#|300] [IN] [T=Y|N]

SQL blocks

Produces result for specified SQL SELECT statement block. IN option returns result within your workbench edit session. Use the # option to change the run result fetch limit 300. T=Y default option display the result column heading. T=N suppress heading display

RUNSTATS ['runstats.parmutil.dsname']

TS, IX, TP, IP

Generate DB2 RUNSTATS utility JCL for the selected tablespaces or indexes. If 'runstats.parm.dsname' is specified, RUNSTATS option is generated base on the 'runstats.parm.dsname'

SELECT

TB, AL, SY,VW

Generate SQL SELECT statements for the selected table, alias, synonyms or view

SELPATHU

CI, CU

Generates SQL UPDATE statement for SYSIBM catalog table which influence selection path of the DB2 Optimizer

SELPATHV

TB

Generates CI and CU line objects. The CI and CU lines display DB2 catalog information which influence selection path of the DB2 Optimizer

SETG

GV

Set Global Variables

SETRBA

any valid RBA field

Set incore RBA for recovery process

SHAUTH

SH, US

Generates SH - schema authorization for selected line objects

SNAPSHOT pgno-in-hex [DSN1PRNT options]

TS, TP, IX, IP

generates DSN1PRNT snapshot for a selected page of a line object. The DSN1PRNT options can be any valid DSN1PRNT options except the NUMPARTS, which is derived from the selected line object

SP

TB, VW, PL, PG

generates SP - Stored Procedure line object for selected line object

SPACE

TB, IX

allows you to estimate space requirement for a db2 table or an index

SPACEADJ [CYL|TRK|PAGE]

AI, AT

generates line objects to feed DSADJ command for selected line object

SSID(ssid)

N/A

Set DB2 subsystem ID

START [db2 start command options]

DB,TS, IX, TP, IP

Issue DB2 START command for the selected line objects

STATS

DB, TS, TB, IX, TP, IP, PL, PG

Produces DB2 catalog statistic summary for selected database, tablespace, table, indexes, plan or package and their dependent objects. The statistics recommendation assists you to identify potential problem

STOP

DB, TS, IX, TP, IP

Issue DB2 STOP command against selected database, tablespace or indexes

SUPERC newds oldds [ODSN='output.dsname']

N/A

Use SUPERC to invoke IBM SUPERC to compare the content of the newds and oldds

SY

TB, VW, PL, PG

Generate Synonyms line objects from the selected line object

SYSIBM(mirror-table-crator|SYSIBM)

N/A

Set DB2I2 catalog table creator ID. Default is SYSIBM

TAG

AC,AR,CP,RL

Tags a SYSCOPY line for RECOVER or DSN1COPY to identify RBA point of time recovery or TOCOPY image copy dataset name. Or tag a active log, archive log , check point or archive log rba line

TB

DB, TS, IX, PL, PG, AL, VW, SY

Shows associated DB2 tables for specified line objects

TBAUTH

AL,VW,TB

Shows table authorizations for specified line objects

TOKENSCN 'load.library[member]'

PG,DM

Use TOKENSCN to scan the specified 'load.library' to check if the consistent token matches. If optional member is specified the scan is done interactively, otherwise, a superc scan batch job is generated

TP

DB, TS, TB

Generates TP - Table Part line object for selected line object

TR

DB, TB, PG

Generates TR - Trigger line object for selected line object

TS

DB,TB, PL, PG

Shows associated DB2 Tablespaces for a specified line objects

TSIX

DS

Shows associated DB2 Tablepart or Indexpart information from a selected DS line object

TSO [TSO command]

N/A

Use TSO DB2I2 command in batch mode to invoke TSO command within a stream of DB2I2 commands

TSSET

TS

Generates Table Space set information in TS line object format for selected TS lines

UPDATE [MAP='dclgen dsn']

TB, AL, SY, VW

Generate SQL UPDATE statements for specified line object. Use MAP option to generate embedded SQL

USAUTH [GRANTOR|GRANTEE]

US

Display User Authorization information for the selected line objects

VIEWG

N/A

Display global variables information

VW

TB, VW, AL, SY, PL, PG

Generate View line objects from the selected line object

ZPARM [dsnzparm-name]

N/A

Display DSNZPARM information

(C)opyrighted 1997, 2008 By JRH Golden State Software Inc.