Sonntag, 20 of April of 2014

Archives from month » Februar, 2012

REXX procedure to call ADMIN_INFO_SYSPARM

REXX to call the IBM DB2-supplied stored procedure ADMIN_INFO_SYSPARM to access DB2 DSNZPARM parameters and writes them an output dataset. This make it easy to archive DB2 subsystem parameters or compare parameter sets of different DB2 subsystems.

See example output at end of page.

Look here for the XMPABND module.


Example:


Shell Script xmpSetInstance.sh – set DB2 environment

Activate requested DB2 environment:

Search for registered DB2 software version, instances, databases and execute the appropriate DB2 environment. The KSH shell script is quite useful on sites with more than one DB2 instance.


REXX procedure to execute DSNACCOX DB2-supplied stored procedure (V9)

DSNACCOX, the successor of DSNACCOR, is a IBM DB2-supplied stored procedure to provide information to maintain DB2 objects. It includes proposals for creating image copyies (COPY), reorganization (REORG) and renewal of statistics (RUNSTATS).

This version covers DB2 for z/OS Version 9.

A possible result looks like …
###############################################################################
# XMPACCOX V2.0 - Recommendations to maintain DB2 objects 11/02/17 19:25:30 #
###############################################################################

Executing without arguments, setting default DB2 subsystem id and criteria.
XMPACCOX …: Following values have been set for DSNACCOX
DB2 SSID …: DB2T
CHKLVL …..: Use the chklvl parameter to cause dsnaccox to
check for related table spaces when processing
index spaces, and delete recommendation for
indexspaces when an action (such as reorg) on the
table space will also cause the action to be done
on the index space. (CHKLVL=64)
CRITERIA …: DBNAME LIKE ‘DSN%’ AND NAME LIKE ‘%’
Use the criteria parameter to cause DSNACCOX to
make recommendations only for objects in databases
you want to.
Other parms : Following parameter values have been set, which are
lower than the defaults:
CRUPDATEDPAGESPCT 4 RRIAPPENDINSERTPCT 5
CRCHANGESPCT 2 SRTINSDELUPDPCT 5
RRTINSERTPCT 2 SRIINSDELPCT 5
RRTUNCLUSTINSPCT 5 EXTENTLIMIT 3
RRTDISORGLOBPCT 5
Excluded the checking for these criteria by set the
following values to a negative value:
RRTMASSDELLIMIT -1 RRIMASSDELLIMIT -1

Recommendations: You should watch …
——————————————————————————-
DATABASE: DSN8D81A SPACENAME: DSN8S81D (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
8 % UPDATED PAGES
28 % ROWS INS/DEL/UPD
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
- Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
- Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
- Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
- Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
- Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81P (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81A SPACENAME: DSN8S81R (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
- Reorg …….: YES, last done 0001-01-01-00.00.00.000000
DATABASE: DSN8D81A SPACENAME: DSN8S81S (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
- Reorg …….: YES, last done 0001-01-01-00.00.00.000000
DATABASE: DSN8D81E SPACENAME: DSN8S81U (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81P SPACENAME: DSN8S81C (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81P SPACENAME: DSN8S81Q (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81U SPACENAME: NEWDEPT (TS)
- Imagecopy …: FULL, last done NEVER
- Reorg …….: YES, last done 2008-12-16-15.00.33.305895
100 % ROWS INS/DEL/UPD
DATABASE: DSN8D81U SPACENAME: NEWPHONE (TS)
- Imagecopy …: FULL, last done NEVER
- Reorg …….: YES, last done 2008-12-16-15.00.33.712209
100 % ROWS INS/DEL/UPD
DATABASE: DSNADMDB SPACENAME: DSNADMTH (TS)
- Runstats ….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNASDMA (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNASDME (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNASDMU (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNATPTS (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNDB04 SPACENAME: TSQ18925 (TS)
- Imagecopy …: FULL, last done NEVER
- Runstats ….: YES, last done NEVER
DATABASE: DSNDB04 SPACENAME: TSQ41683 (TS)
- Imagecopy …: FULL, last done NEVER
- Runstats ….: YES, last done NEVER
DATABASE: DSNDB04 SPACENAME: TSQ89405 (TS)
- Imagecopy …: FULL, last done NEVER
- Runstats ….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSALTER (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.16.724754
586 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.11.27.622702
43 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSCONTX (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.17.032202
300 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.16.32.605912
DATABASE: DSNDB06 SPACENAME: SYSCOPY (TS)
- Imagecopy …: INC , last done 2012-02-17-06.07.18.872718
- Runstats ….: YES, last done 2010-08-23-11.54.18.580229
1029 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.11.34.498504
114 % ROWS INS/DEL/UPD
7 % ROWS UNCLUSTERED INSERT’S
DATABASE: DSNDB06 SPACENAME: SYSDBASE (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.19.636099
274 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSDBAUT (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.27.024685
65 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.12.27.742294
DATABASE: DSNDB06 SPACENAME: SYSDDF (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.27.699100
13 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.12.44.538830
DATABASE: DSNDB06 SPACENAME: SYSEBCDC (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSGPAUT (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.28.917269
29 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSGROUP (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.29.166183
76 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.12.56.197333
DATABASE: DSNDB06 SPACENAME: SYSGRTNS (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.29.747266
DATABASE: DSNDB06 SPACENAME: SYSHIST (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.30.205863
111 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.13.12.652053
42 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSJAUXA (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSJAUXB (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSJAVA (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.31.545214
DATABASE: DSNDB06 SPACENAME: SYSOBJ (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.32.159832
161 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.13.44.983536
31 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSPKAGE (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.36.461244
335 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.14.05.817685
34 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSPLAN (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.38.156912
243 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.14.13.682557
32 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSPLUXA (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.38.515056
15700 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.16.00.877993
3000 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSROLES (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.38.630576
DATABASE: DSNDB06 SPACENAME: SYSSEQ (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.40.091097
96 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.14.20.156097
DATABASE: DSNDB06 SPACENAME: SYSSEQ2 (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.40.556231
94 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.14.26.592014
DATABASE: DSNDB06 SPACENAME: SYSSTATS (TS)
- Imagecopy …: INC , last done 2012-02-17-06.07.07.620002
- Runstats ….: YES, last done 2010-08-23-11.54.40.996292
7221 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.14.58.985117
2006 % ROWS INS/DEL/UPD
1451 % ROWS UNCLUSTERED INSERT’S
DATABASE: DSNDB06 SPACENAME: SYSSTR (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.41.821443
94 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.15.07.509454
49 % ROWS INS/DEL/UPD
6 % ROWS UNCLUSTERED INSERT’S
DATABASE: DSNDB06 SPACENAME: SYSTARG (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.42.414679
1900 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.15.49.328602
900 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSUSER (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.42.699836
8 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSVIEWS (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.44.840788
84 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSXML (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.46.074462
212 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.15.43.445283
150 % ROWS INS/DEL/UPD
DATABASE: DSNOSCDB SPACENAME: DSNMQLTS (TS)
- Runstats ….: YES, last done NEVER
DATABASE: DSNOSCDB SPACENAME: DSNOSCL1 (TS)
- Runstats ….: YES, last done NEVER
DATABASE: DSNOSCDB SPACENAME: DSNOSCTS (TS)
- Reorg …….: YES, last done 2010-07-20-09.52.42.282010
520 % ROWS INS/DEL/UPD
DATABASE: DSNRLST SPACENAME: DSNRLS01 (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP2 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSNADMDB SPACENAME: ADMIN_TASKS_IX (IX)
- Imagecopy …: YES , last done NEVER
DATABASE: DSNADMDB SPACENAME: ADMIN_TASKS_HIST_IX (IX)
- Imagecopy …: YES , last done NEVER
DATABASE: DSNDB06 SPACENAME: DSNACX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780825
DATABASE: DSNDB06 SPACENAME: DSNDXX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780470
DATABASE: DSNDB06 SPACENAME: DSNDRX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780618
DATABASE: DSNDB06 SPACENAME: DSNDKX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780677
DATABASE: DSNDB06 SPACENAME: DSNDYX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780854
DATABASE: DSNDB06 SPACENAME: DSNATX03 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780766
DATABASE: DSNDB06 SPACENAME: DSNATX02 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780737
DATABASE: DSNDB06 SPACENAME: DSNDPX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780040
DATABASE: DSNDB06 SPACENAME: DSNDPX03 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780108
DATABASE: DSNDB06 SPACENAME: DSNDTX02 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780253
*** end ***


DB2 Space calculation – from pages to tracks

All system z DASD I/O systems base on the IBM DASD device type IBM 3390 Model 3 and are compatible to it. This means that all calculations a DB2 DBA or sysadm can to do are on basis of this type and model characteristics. Quite typical for this DASD model: 12 pages of DB2 data fit into 1 track. For older types, such as IBM 3380, which are not considered here, only 10 pages to fit into a track. More about DB2 data and storage, see below.

IBM Type 3390 Model 3

  • 56664 bytes per track
  • 849960 bytes per cylinder
  • 15 tracks per cylinder
  • 3339 cylinders per device

3339 cylinders
x 15 trk/cyl
= 50100 tracks/device

2838 MB/Device

approx. 2.75 GB

DB2 space calcalution on basis of device type 3390

smallest unit DB2 can externalize:
1 page with 4 KB,
 which is 4096 bytes

1 track =
56664 bytes

overhead per block of records stored
= 340 Bytes

56664 /
(4096 + 340) = 12.773

which is 12 pages per track

12 pages = 12 x 4 KB = 48 KB

net user data = 48 KB
which claim 55 KB space on DASD device

DB2 data + 15% overhead
= total usage of DASD storag

1 cylinder
= 15 rracks x 12 pages
= 180 pages
= 720 KB

1 Device
= 50100 tracks
= 601200 pages
= 2.4 GB DB2 net user data


CICS and MQ Explorer

IBM CICS Explorer: Common, intuitive, Eclipse-based environment for architects, developers, administrators, system programmers, and operators.  Looking for new ways to accelerate the transfer of knowledge, skills, and best practice to the next generation of technical staff/experts? Need to maintain productivity and protect service-levels? CICS Explorer and System z lead the way to platform simplification.

Download

IBM CICS Explorer Quick Reference

IBM’s MQ Explorer is a free graphical configuration tool built on Eclipse which enables you to remotely explore and configure all WebSphere MQ objects and resources, including Java Message Service (JMS), and publish and subscribe. MQ Explorer can remotely connect to queue managers on any supported platform – enabling your entire messaging backbone to be viewed, explored and altered from one place. MQ Explorer runs on Windows and Linux (x86 and x86_64) systems. It does not need to be deployed with a WebSphere MQ client or server, and can be installed on as many machines as you require.

Download


Perl Script xmpBkp.pl – DB2 Backup, Restore, List Backup and Backup Script Creation

Everything you need for total control of your DB2 backup process: