zOs/JCL/XBINDEX
$#@
$=dbSys=DVBP
call sqlConnect $dbSys
$<>
$<=[
select t.creator cr, t.name tb, t.dbName db, t.tsName ts
, s.partitions pa, substr(t.name, 7, 2) c2
, row_number() over() rn
, substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, row_number() over(), 1) jc
from sysibm.sysTables t join sysibm.sysTableSpace s
on t.dbName = s.dbname and t.tsName = s.name
where t.name like 'XBDJ90__PM001002'
order by s.partitions
$]
call sqlSel
$|
$forWith t $@=/table/
$= job = XBINDEX$JC
$= ix = IDJ90${C2}06
$<>
$>~tmp.jcl($job)
//$job JOB (CP00,KE50),'DB2 REO',
// MSGCLASS=T,TIME=1440,
// NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2
//*MAIN CLASS=LOG SYSTEM=S42
//* index $ix
//* on $CR.$TB
//* in $DB.$TS $PA
//DDL EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99
//SYSTSIN DD *
DSN SYSTEM($dbSys)
RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//*
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM DD DUMMY
//SYSIN DD *
set current sqlid = 'S100447';
CREATE INDEX $CR.$ix
ON $CR.$TB
(EA1PID ASC
)
NOT CLUSTER
PARTITIONED
DEFINE YES
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
DEFER YES
COPY NO
USING STOGROUP GSMS4
PRIQTY -1
SECQTY -1
ERASE NO
GBPCACHE CHANGED
FREEPAGE 0
PCTFREE 00010
;
// IF RC <= 4 THEN
//REORG EXEC PGM=DSNUTILB,TIME=1440,
// PARM=($dbSys,'$job.REORG'),
// REGION=0M
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DISP=SHR,DSN=$dbSys.DBAA.LISTDEF(TEMPL)
$do ix=1 by 10 to $PA $@=[
$= mx =- right(($RN-1) // 10 + 1, 2, 0)
LISTDEF TPLIST$ix
$do px=$ix to min($ix+9, $PA) $@=[
INCLUDE TABLESPACE $DB.$TS PARTLEVEL $px
$]
-- reorg ----------------------------------------------------
REORG TABLESPACE LIST TPLIST$ix
LOG NO
SORTDATA
COPYDDN(TCOPYD)
SHRLEVEL CHANGE
-- Achtung mapping table zufällig gewählt|
MAPPINGTABLE S100447.MAPTAB$mx
DRAIN_WAIT 40
RETRY 20
RETRY_DELAY 180
MAXRO 40
DRAIN WRITERS
LONGLOG CONTINUE
DELAY 1200
TIMEOUT TERM
UNLDDN TSRECD
UNLOAD CONTINUE
PUNCHDDN TPUNCH
DISCARDDN TDISC
SORTKEYS
SORTDEVT DISK
STATISTICS
INDEX ALL KEYCARD
UPDATE ALL
$]
// ENDIF
$/table/
$#out 20150604 19:31:13
$#out 20150604 18:48:28
$#out 20150604 18:45:50
*** run error ***
adrTso rc= 1 stmt=execio 80 diskW CAT1 (stem M.FILETSO.BUF1.)
$#out 20150604 18:45:30
*** run error ***
undefined var job
$#out 20150604 18:42:58
$#out 20150604 18:42:10
*** run error ***
undefined var px
$#out 20150604 18:41:26
*** run error ***
undefined var px
$#out 20150604 18:39:59
*** run error ***
undefined var rn
$#out 20150604 18:39:43
*** run error ***
undefined var paritions
$#out 20150604 18:32:10
$#out 20150604 18:29:20
$#out 20150604 18:28:48
*** run error ***
undefined var cr
$#out 20150604 18:28:26
*** run error ***
undefined var JC
$#out 20150604 18:27:48
*** run error ***
undefined var JC
$#out 20150604 18:27:05
*** run error ***
undefined var jc
$#out 20150604 18:20:21
*** run error ***
no default subsys for RZ2
$#out 20150604 18:19:56
CR PARTITIONS
TB C2
DB RN
TS JCN
CR TB DB TS PA C2 RN J
BUA XBDJ9001PM001002 XBDJ9001 SDJ90011 7 1 1 A
BUA XBDJ9002PM001002 XBDJ9001 SDJ90021 8 2 2 B
BUA XBDJ9003PM001002 XBDJ9001 SDJ90031 41 3 3 C
BUA XBDJ9004PM001002 XBDJ9001 SDJ90041 42 4 4 D
BUA XBDJ9005PM001002 XBDJ9001 SDJ90051 42 5 5 E
BUA XBDJ9006PM001002 XBDJ9001 SDJ90061 39 6 6 F
BUA XBDJ9007PM001002 XBDJ9001 SDJ90071 43 7 7 G
BUA XBDJ9008PM001002 XBDJ9001 SDJ90081 43 8 8 H
BUA XBDJ9009PM001002 XBDJ9001 SDJ90091 42 9 9 I
BUA XBDJ9010PM001002 XBDJ9001 SDJ90101 44 10 10 J
BUA XBDJ9011PM001002 XBDJ9001 SDJ90111 33 11 11 K
BUA XBDJ9012PM001002 XBDJ9002 SDJ90121 41 12 12 L
BUA XBDJ9013PM001002 XBDJ9002 SDJ90131 17 13 13 M
13 rows fetched: select t.creator cr, t.name tb, t.dbName db, t.tsName ts ...
$#out 20150604 18:17:47
CR TB DB TS PA
BUA XBDJ9001PM001002 XBDJ9001 SDJ90011 7
BUA XBDJ9002PM001002 XBDJ9001 SDJ90021 8
BUA XBDJ9003PM001002 XBDJ9001 SDJ90031 41
BUA XBDJ9004PM001002 XBDJ9001 SDJ90041 42
BUA XBDJ9005PM001002 XBDJ9001 SDJ90051 42
BUA XBDJ9006PM001002 XBDJ9001 SDJ90061 39
BUA XBDJ9007PM001002 XBDJ9001 SDJ90071 43
BUA XBDJ9008PM001002 XBDJ9001 SDJ90081 43
BUA XBDJ9009PM001002 XBDJ9001 SDJ90091 42
BUA XBDJ9010PM001002 XBDJ9001 SDJ90101 44
BUA XBDJ9011PM001002 XBDJ9001 SDJ90111 33
BUA XBDJ9012PM001002 XBDJ9002 SDJ90121 41
BUA XBDJ9013PM001002 XBDJ9002 SDJ90131 17
13 rows fetched: select t.creator cr, t.name tb, t.dbName db, t.tsName ts ...
$#out