DB2 Utilities- Unload Image Copy Dataset.

Sample Job:

//UTIL EXEC DSNUPROC,SYSTEM=DSNS,UID='TEMP',UTPROC=''

//**********************************************

//* UNLOADING IMAGE COPY DATASET

//**********************************************

//DSNUPROC.SYSREC DD DSN=USERID.SRI.SYSREC,

// DISP=(MOD,CATLG),

// SPACE=(16384,(20,20),,,ROUND),

// UNIT=SYSALLDA

//DSNUPROC.SYSPUNCH DD DSN=USERID.SRI.SYSPUNCH,

// DISP=(MOD,CATLG),

// SPACE=(16384,(20,20),,,ROUND),

// UNIT=SYSALLDA

//DSNUPROC.SYSIN DD *

UNLOAD TABLESPACE DBNAME.TSNAME FROMCOPY USERID.SRI.DATA;

If the table was dropped, its not possible to Unload data from the Image copies. You will receive the following error message.

DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = TEMP

DSNU1044I DSNUGTIS - PROCESSING SYSIN AS EBCDIC

DSNU050I DSNUGUTC - UNLOAD TABLESPACE DBNAME.ZSRIDHAR

DSNU260I -DSNS DSNUULIA-TABLESPACE DBNAME.ZSRIDHAR IS EMPTY

DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=4

Recovering an accidentally dropped table

Tables in a partitioned table space cannot be dropped without dropping the table space.

To perform this procedure, you need a full image copy or a DSN1COPY file that contains the data from the dropped table.

For segmented or universal table spaces, the image copy or DSN1COPY file must contain the table when it was active (that is, created). Because of the way space is reused for segmented table spaces, this procedure cannot be used if the table was not active when the image copy or DSN1COPY was made. For nonsegmented table spaces, the image copy or DSN1COPY file can contain the table when it was active or not active.

To recover a dropped table:

1. If you know the DBID, the PSID, the original OBID of the dropped table, and the OBIDs of all other tables in the table space, go to step 2.

If you do not know all of the preceding items, use the following steps to find them. For later use with DSN1COPY, record the DBID, the PSID, and the OBIDs of all the tables in the table space, not just the dropped table.

1. For the data set that contains the dropped table, run DSN1PRNT with the FORMAT and NODATA options. Record the HPGOBID field in the header page and the PGSOBD field from the data records in the data pages.

For the auxiliary table of a LOB table space, record the HPGROID field in the header page instead of the PGSOBD field in the data pages.

• Field HPGOBID is 4 bytes long and contains the DBID in the first 2 bytes and the PSID in the last 2 bytes.
• Field HPGROID (for LOB table spaces) contains the OBID of the table. A LOB table space can contain only one table.
• Field PGSOBD (for non-LOB table spaces) is 2 bytes long and contains the OBID of the table. If your table space contains more than one table, check for all OBIDs. By searching for all different PGSOBD fields. You need to specify all OBIDs from the data set as input for the DSN1COPY utility.
1. Convert the hex values in the identifier fields to decimal so that they can be used as input for the DSN1COPY utility.
1. Use the SQL CREATE statement to re-create the table and any indexes on the table.
2. To allow DSN1COPY to access the DB2 data set, stop the table space using the following command:

-STOP DATABASE(database-name) SPACENAM(tablespace-name)

Stopping the table space is necessary to ensure that all changes are written out and that no data updates occur during this procedure.

4. Find the OBID for the table that you created in step 2 by querying the SYSIBM.SYSTABLES catalog table.

The following statement returns the object ID (OBID) for the table:

SELECT NAME, OBID FROM SYSIBM.SYSTABLES

  WHERE NAME='table_name'

    AND CREATOR='creator_name';

This value is returned in decimal format, which is the format that you need for DSN1COPY.

5. Run DSN1COPY with the OBIDXLAT and RESET options to perform the OBID translation and to copy data from the dropped table into the original data set. You must specify a previous full image copy data set, inline copy data set, or DSN1COPY file as the input data set SYSUT1 in the control statement. Specify each of the input records in the following order in the SYSXLAT file to perform OBID translations:
1. The DBID that you recorded in step 1 as both the translation source and the translation target
2. The PSID that you recorded in step 1 as both the translation source and the translation target
3. The original OBID that you recorded in step 1 for the dropped table as the translation source and the OBID that you recorded in step 4 as the translation target
4. OBIDs of all other tables in the table space that you recorded in step 2 as both the translation sources and translation targets

Be sure that you have named the VSAM data sets correctly by checking messages DSN1998I and DSN1997I after DSN1COPY completes.

6. Use DSN1COPY with the OBIDXLAT and RESET options to apply any incremental image copies. You must apply these incremental copies in sequence, and specify the same SYSXLAT records that step 5 specifies.

Important: After you complete this step, you have essentially recovered the table space to the point in time of the last image copy. If you want to use log records to perform forward recovery on the table space, you must use the IBM^® DB2 Log Analysis Tool for z/OS at this point in the recovery procedure.

7. Start the table space for normal use by using the following command:

-START DATABASE(database-name) SPACENAM(tablespace-name)

8. Rebuild all indexes on the table space.
9. Execute SELECT statements on the previously dropped table to verify that you can access the table. Include all LOB columns in these queries.
10. Make a full image copy of the table space.
11. Re-create the objects that are dependent on the recovered table.

When a table is dropped, objects that are dependent on that table (synonyms, views, indexes, referential constraints, and so on) are dropped. (Aliases are not dropped.) Privileges that are granted for that table are also dropped. Catalog reports or a copy of the catalog taken prior to the DROP TABLE can make this task easier.

Sample JCL:

//H136830$ JOB MSGCLASS=X,MSGLEVEL=(1,1),CLASS=A,

// REGION=0M,NOTIFY=&SYSUID

//EXECUTE EXEC PGM=DSN1COPY,PARM='OBIDXLAT,RESET,INLCOPY'

//*EXECUTE EXEC PGM=DSN1COPY,PARM='OBIDXLAT,RESET'

//STEPLIB DD DSN=DSN810.SDSNLOAD,DISP=SHR

//SYSPRINT DD SYSOUT=A

//SYSUT1 DD DSN=full image copy dataset,DISP=SHR

//SYSUT2 DD DSN=tablespace VSAM file,DISP=SHR

//SYSXLAT DD *

273,273

42,42

43,43

44,44

60,61 (60-> OBID of source and 61-> OBID of target)

/*

//

Hinting Optimizer to choose the access path of your choice

Let us analyse the following SQL:

EXPLAIN ALL SET QUERYNO=999 FOR
SELECT TACCONT.AC_NA , TACCONT.AC_TYP_CD
FROM TACCONT
WHERE TACCONT.AC_NR = '123456' AND
TACCONT.CNY_CD = 'US' and
TACCONT.AC_TYP_CD = 'A';

Suppose that DB2 would pick up index IACCONT1, which is an
index based on AC_NR.
In the event of DB2 picking up the normal index, the PLAN_TABLE
entry for the above query would appear as follows:

Query # MTH TNAME TABNO ACCESS MATCH ACCESS INDEX
TYPE COLS NAME ONLY
999 Ø TACCONT 1 I Ø1 IACCONT1 Y

But, based on our knowledge of the data, we want DB2 to pick up
IACCONT2, a secondary index based on CNY_CD and AC_TYP_CD.
In earlier versions of DB2 we could not influence the access path, but
illustrated below is a feature provided by DB2 Version 6 by which we
can give hints to the DB2 optimizer to pick up IACCONT2.
Some new columns to the PLAN_TABLE have been added in DB2
Version 6 to facilitate this feature of DB2. They are OPTHINT and
HINT_USED.

Now, let’s update the OPTHINT field to name our new access path.
Let’s call that new plan for accessing the secondary index PICKSECX.
We need to update the PLAN_TABLE as follows:

UPDATE PLAN_TABLE
SET OPTHINT = 'PICKSECX'
WHERE OPTHINT = '' AND QUERYNO = 999;

Now let’s update the PLAN_TABLE to indicate the access path for
our new plan,PICKSECX. In this case, you will update the
ACCESSNAME column of PLAN_TABLE, with the following SQL
statement:

UPDATE PLAN_TABLE
SET ACCESSNAME = 'IACCONT2'
WHERE OPTHINT = 'PICKSECX' AND QUERYNO = 999;

When updating the PLAN_TABLE for the second time, do make sure
you are updating the right row, by predicating the OPTHINT column
by the plan – which picks the secondary index.
After updating the PLAN_TABLE, the row of interest to us would
look like:

Query # MTH TNAME TABNO ACCESS MATCH ACCESS INDEX OPT_HINT HINT_USED
TYPE COLS NAME ONLY
999 Ø TACCONT 1 I Ø1 IACCONT2 Y PICKSECX

Now after setting the hint to the optimizer in the PLAN_TABLE, we
should bind our static SQL accordingly.
For static SQL, let’s use the following parameter in our bind package
or bind plan cards:

OPTHINT('PICKSECX').

For dynamic SQL, issue a new special DB2 register statement as follows:
SET CURRENT QUERY OPTIMIZATION HINT = 'PICKSECX' WHERE QUERYNO=999;
And then execute the same SQL as in the first example above as
follows:

SELECT TACCONT.AC_NA , TACCONT.AC_TYP_CD
FROM TACCONT
WHERE TACCONT.AC_NR = '123456' AND
TACCONT.CNY_CD = 'US' and
TACCONT.AC_TYP_CD = 'A'
QUERYNO 999;

The difference between the two SQL examples is the presence of
QUERYNO in the second example.
After execution, the PLAN_TABLE row suggests the following:

Query # MTH TNAME TABNO ACCESS MATCH ACCESS INDEX OPT_HINT HINT_USED
TYPE COLS NAME ONLY
999 Ø TACCONT 1 I Ø2 IACCONT2 Y PICKSECX

After executing the second SQL with hints to the optimizer, if we then
run an EXPLAIN, PLAN_TABLE shows that plan PICKSECX has
moved from column OPT_HINT to HINT_USED. This confirms that
DB2 has indeed picked the secondary index for the second SQL
example. MATCHCOLS for the second SQL, on picking up the
secondary index, IACCONT2, has become 02.

The BIND statement for static SQL and the PREPARE statement for
dynamic SQL indicates a SQLCODE of +394, suggesting that the hint
was successfully picked up for execution.

If IACCONT2 is a non-existent secondary index, then the BIND
statement for static SQL and the PREPARE statement for dynamic
SQL indicates a SQLCODE of +395, suggesting that the hint was
found, but execution was found to be invalid.

Important DB2 commands

-DISPLAY UTIL(*) --> Displays any object which is placed in utility mode due to abnormal completion of any utilitity job.

-TERM UTIL(*) --> Terminates the utility and resets the utility mode.

-STA DATABASE(DBNAME) SPACENAM(TSNAME) ACCESS(RW) - ->
Starts the TS in Read/Write mode.

-STO DATABASE(DBNAME) SPACENAM(*) --> Stops all the TS in the Database.

-DIS DB(DBNAME) SPACENAM(TSNAME) LOCKS/CLAIMERS LIMIT(*) -->
Displays list of TS which are held/locked by the users and the corresponding USERID.

-DIS GROUP --> Displays information about the DB2 subsystem (which mode the
DB2 is in, name of the subsystem,Active/Inactive,current version of the DB2 )

-DIS DB(DBNAME) SPACENAM(TSNAME) RESTRICT --> Displays TS and IX which are in utility pending status (COPY,RECOVERY,REORG pending,..etc)

-DIS DB(DBNAME) SPACENAM(TSNAME) RESTRICT LIMIT(*) --> Displays all
information without any space restriction. LIMIT(100) displays 100 rows of information.
Default is 50.

Adding a column to the middle of a table

The following steps need to be performed to add a new column to the midddle of a table.

1. Retrieve the current definition of the table by querying the system catalog or data dictionary.
2. Retrieve the current definition of any views that specify the table by querying the system catalog or data dictionary.
3. Retrieve the current definition of any indexes defined on the table by querying the system catalog or data dictionary.
4. Retrieve the current definition of any triggers defined on the table by querying the system catalog or data dictionary.
5. Capture all referential constraints for the table and its related tables and determine what their impact will be if the table is dropped (causing all data in the table to be deleted).
6. Retrieve all security authorizations that have been granted for the table by querying the system catalog or data dictionary.
7. Obtain a list of all programs that access the table by using the system catalog, data dictionary, and any other program documentation at your disposal.
8. Unload the data in the table.
9. Drop the table, which in turn drops any views and indexes associated with the table, as well as invalidates any SQL statements against that table in any application programs.
10. Recreate the table with the new column by using the definition obtained from the system catalog.
11. Reload the table, using the unloaded data from step 8.
12. Recreate any referential constraints that may have been dropped.
13. Recreate any triggers, views, and indexes for the table.
14. Recreate the security authorizations captured in step 6.
15. Examine each application program to determine if changes are required for it to continue functioning appropriately.

Tablespace Creation and parameters used

Explanation for all parameters used during tablespace creation:

CREATE TABLESPACE TSNAME

IN DBNAME

USING STOGROUP STGNAME

PRIQTY 40012 --> Primary quantity. Space allocated once the create statement is executed.

SECQTY 8004 --> Seconday quantity. Space allocated only when primary quantity is fully used.

ERASE NO --> Specifies whether data in the table should be erased or overwritten by binary zeroes when the TS is dropped

FREEPAGE 5 --> Allocated an empty page after every 5 data pages. Freepage will be used during INSERTS and UPDATES to place new records close to related records

PCTFREE 10 --> Percentage of space left free per page. Freespace will be used during INSERS and UPDATES to place new records close to related records and avoid page splits.

TRACKMOD YES --> specifies whether DB2 tracks modified pages in the space map pages in the table space or partition. This information will be used during incremental image copy creation.

SEGSIZE 64 --> specifies the number of pages in a segment for segmented table spaces.

BUFFERPOOL BP0 --> specifies which buffer pool the table space is assigned to.Decides the pagesize of the TS

LOCKSIZE ANY --> specifies the size of locks used within the table space . The allowable lock sizes are ANY, TABLESPACE,TABLE,PAGE and ROW. For ANY , DB2 choose an optimal lock on its own depending on the amount of records locked.

LOCKMAX SYSTEM --> Specifies maximum number of locks on an object. In this case it takes the value of SYSTEM which is a DB2ZPARM.

CLOSE NO --> Specifies whether underlying VSAM data sets for the TS should be closed each time after use or not.

COMPRESS NO -->specifies whether data compression is used for rows in the table space

CCSID EBCDIC --> data encoding scheme.

DEFINE YES --> specify an TS and defer the physical creation. The data sets will not be created until data is inserted into the TS. This option is helpful in order to reduce the number of physical data sets.

MAXROWS 255; -->specifies the maximum number of rows allowed on a page, up to a maximum of 255.

COMMIT;

DB2 Utilities - IMAGE COPY

Sample job for full image copy:

//JOBNAME JOB ,'BACKUP',MSGCLASS=H,CLASS=A,NOTIFY=&SYSUID

//UTIL EXEC DSNUPROC,SYSTEM=DB2,UID='UTILID',UTPROC=''

//**********************************************

//* FULL IMAGE COPY UTILITY

//**********************************************

//DSNUPROC.SYSCOPY DD DSN=USERID.FULL.COPY,

// DISP=(MOD,CATLG),

// SPACE=(CYL,(50,50),RLSE),

// UNIT=SYSALLDA

//DSNUPROC.SYSIN DD *

COPY TABLESPACE DBNAME.TSNAME SHRLEVEL REFERENCE

FULL YES

Sample job for incremental image copy:

//JOBNAME JOB ,'BACKUP',MSGCLASS=H,CLASS=A,NOTIFY=&SYSUID

//UTIL EXEC DSNUPROC,SYSTEM=DB2,UID='UTILID',UTPROC=''

//**********************************************

//* INCREMENTAL IMAGE COPY UTILITY

//**********************************************

//DSNUPROC.SYSCOPY DD DSN=USERID.INCR.COPY,

// DISP=(MOD,CATLG),

// SPACE=(CYL,(50,50),RLSE),

// UNIT=SYSALLDA

//DSNUPROC.SYSIN DD *

COPY TABLESPACE DBNAME.TSNAME SHRLEVEL REFERENCE

FULL NO

Leaving the choice to DB2 to go for either FULL or
INCREMENTAL copy using CHANGELIMIT parameter:

//***********************************************************************

//* STEP DECIDES TO GO FOR FULL OR INCREMENTAL COPY BASED ON

//* THE PERCENTAGE OF DATA PAGES CHANGED IN THE TABLESPACE

//**********************************************************************

//IMAGE1 EXEC PGM=DSNUTILB,PARM=(DB2,'COPY01')

//STEPLIB DD DSN=SYS1.DSNDB2.SDSNLOAD,DISP=SHR

//SYSCOPY DD DUMMY

//DSNTRACE DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SYSUDUMP DD DUMMY

//SYSIN DD *

COPY TABLESPACE DBNAME.TSNAME

CHANGELIMIT(50) REPORTONLY

/*

//***********************************************************************

//* BELOW INCREMENTAL COPY STEP WILL BE EXECUTED IF THE

//* PERCENTAGE OF CHANGED PAGES IS LESS THAN 50%

//**********************************************************************

//IMAGE2 EXEC PGM=DSNUTILB,PARM=(DSNS,'COPY02'),

// COND=(2,NE,IMAGE1)

//STEPLIB DD DSN=SYS1.DSNDSNS.SDSNLOAD,DISP=SHR

//SYSCOPY DD DSN=USERID.INCR.COPY

// DISP=(NEW,CATLG,DELETE),

// UNIT=SYSDA,

// SPACE=(TRK,(50,30),RLSE)

//DSNTRACE DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SYSUDUMP DD DUMMY

//SYSIN DD *

COPY TABLESPACE DBNAME.TSNAME

FULL NO

/*

//***********************************************************************

//* BELOW FULL COPY STEP WILL BE EXECUTED IF THE PERCENTAGE

//* OF CHANGED PAGES IS GREATER THAN 50%

//**********************************************************************

//IMAGE3 EXEC PGM=DSNUTILB,PARM=(DB2,'COPY03'),

// COND=(3,NE,IMAGE1)

//STEPLIB DD DSN=SYS1.DSNDB2.SDSNLOAD,DISP=SHR

//SYSCOPY DD DSN=USERID.FULL.COPY

// DISP=(NEW,CATLG,DELETE),

// UNIT=SYSDA,

// SPACE=(TRK,(50,30),RLSE)

//DSNTRACE DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SYSUDUMP DD DUMMY

//SYSIN DD *

COPY TABLESPACE DBNAME.TSNAME

FULL YES

/*

SAMPLE OUTPUT:output of the first step(IMAGE1) in the above job

=============================================================

DSNUGUTC - COPY TABLESPACE DBNAM.TSNAME CHANGELIMIT(5)
REPORTONLY

-DSNS DSNUBACH - IMAGE COPY INFORMATION

4KB CHANGED PERCENT OF

TSNAME DSNUM PAGES PAGES CHANGED PAGES ICTYPE

-------- ----- ------------- ------------- ------------- ------

ZSRIDHAR ALL 48 0 0.00 NONE

-DSNS DSNUBACH - NO CHANGE LIMIT MET FOR TABLESPACE DBNAME.TSNAME

DSNUGBAC - UTIL EXECUTION COMPLETE, HIGHEST RETURN CODE=1

================================================================

DSNUGUTC - COPY TABLESPACE DBNAM.TSNAME CHANGELIMIT(5)
REPORTONLY

-DSNS DSNUBACH - IMAGE COPY INFORMATION

4KB CHANGED PERCENT OF

TSNAME DSNUM PAGES PAGES CHANGED PAGES ICTYPE

-------- ----- ------------- ------------- ------------- ------

ZSRIDHAR ALL 48 4 8.33 F

-DSNS DSNUBACH - FULL CHANGE LIMIT MET FOR TABLESPACE DBNAME.TSNAME

DSNUGBAC - UTIL EXECUTION COMPLETE, HIGHEST RETURN CODE=3

==================================================================

DSNUGUTC - COPY TABLESPACE DBNAM.TSNAME CHANGELIMIT(50)
REPORTONLY

-DSNS DSNUBACH - IMAGE COPY INFORMATION

4KB CHANGED PERCENT OF

TSNAME DSNUM PAGES PAGES CHANGED PAGES ICTYPE

-------- ----- ------------- ------------- ------------- ------

ZSRIDHAR ALL 48 4 8.33 I

-DSNS DSNUBACH - INCREMENTAL CHANGE LIMIT MET FOR TABLESPACE DBNAME.TSNAME

DSNUGBAC - UTIL EXECUTION COMPLETE, HIGHEST RETURN CODE=2