zOs/WK/PLANTBMU
$#@ $*(******** create plan tables and views db2v10 ********** 22.03.12
1) Parameter eingeben und creator Liste
2) wsh
3) job kontrollieren und sub
v10 plan_table 64 Kolonnen und Unicode
v9 plan_table 59 Kolonnen
v8 plan_table 58 Kolonnen
*********************** v7 plan_table 51 Kolonnen ******************$*)
$=subsys=DBAF $** db2 subsystem
$=fun = d $** funktion
$*( c= create plan tables and views
u=update tables for v10.1 and recreate views
must already be UniCode ||||
r=drop Tablespaces and recreate everything
d=drop Tablespaces only
v=drop/recreate views only $*)
$=job=Y4PLANTB $** jobName
$=db=DB2PLAN
if $subsys == 'DBAF' then
$=path= OA1A
else if $subsys == 'DBTF' then
$=path= OA1T
else
$=path= OA1P
$*( *** history *******************************************************
29. 3.12 neu fun=d: drop Only, r= drop and recreate
21. 3.12 drop only necessary TS and Views ==> JobCondCode <=4
21. 3.12 create v10 views or migrate to v10
3. 2.12 allow v10 views (but do not change v9 to v10 yet|)
****************************************************************** $*)
$=isCre=- pos($fun, 'cr') > 0 $** create everything
$=isUpd=- pos($fun, 'crud') > 0 $** update allowed
$=defer=- if($isCre, NO, YES)
if pos($fun, 'druv') > 0 then
call sqlConnect $subsys
$;
$>.fEdit() $@/fEdit/
$@=[
//$job JOB (CP00,KE50),
// MSGCLASS=T,TIME=1440,
// NOTIFY=&SYSUID
//*MAIN CLASS=LOG0 ,SYSTEM=S12
$]
$;
$<=/creators/
DB2ADMIN DB2PLAN U 59 10.02.2012 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
ANLUSER1 IDTANL00 E 59 15.07.2009 PARENT_P 10 to 2011-11-11 f2Plan ---204 ro
F288874 VV29A1X E 59 20.08.2010 PARENT_P 120 to 2010-12-09 f2Plan ---204 ro
A618725 DA618725 E 58 31.07.2007 STMTTOKE 17780 to 2010-11-29 f2Plan ---204 ro
A700522 DA700522 E 58 04.11.2010 STMTTOKE 3 to 2010-11-04 f2Plan 0 rows
A273818 DA273818 U 59 04.03.2010 PARENT_P 26 to 2010-06-08 f2Plan ---204 ro
A108875 SV02A1A E 58 12.09.2006 STMTTOKE 2 to 2010-02-11 f2Plan 0 rows
A188662 DA188662 U 59 13.01.2010 PARENT_P 19 to 2010-01-13 f2Plan 5 rows
A426625 DA426625 E 58 26.05.2008 STMTTOKE 449 to 2009-05-26 f2Plan 114 rows
A533734 DSNDB04 E 58 19.10.2006 STMTTOKE 4 to 2008-02-08 f2Plan ---204 ro
GDB0275 DGDB0275 E 49 08.10.2007 PRIMARY_ 8 to 2007-10-09 f2Plan ---204 ro
A573701 WA01A1A E 46 25.02.2002 BIND_TIM 634 to 2007-09-05 f2Plan 727 rows
A634011 DSNDB04 E 58 19.10.2006 STMTTOKE 1 to 2006-10-20 f2Plan ---204 ro
GDB0135 DGDB0135 E 46 17.02.2006 BIND_TIM 5 to 2006-02-17 f2Plan ---204 ro
A547666 WN01A1A E 51 11.05.2005 TABLE_TY 1 to 2005-12-09 f2Plan 15 rows
OPTHINT DS100447 E 51 03.12.2003 TABLE_TY 28 to 2004-12-13 f2Plan ---204 ro
A215396 WA01A1A E 46 11.12.2003 BIND_TIM 6 to 2003-12-11 f2Plan 9 rows
X002151 DX002151 E 46 13.06.2003 BIND_TIM 5 to 2003-06-13 f2Plan 3 rows
X002127 DX002127 E 46 13.06.2003 BIND_TIM 3 to 2003-06-13 f2Plan 2 rows
A459429 DA459429 E 51 24.04.2003 TABLE_TY 2 to 2003-04-24 f2Plan ---204 ro
F890588 DHPSD53 E 46 19.07.2001 BIND_TIM 4 to 2001-07-19 f2Plan 5 rows
A830011 DA830011 E 46 26.09.2000 BIND_TIM 3 to 2000-09-26 f2Plan ---204 ro
T022527 DT022527 E 46 26.11.1999 BIND_TIM 24 to 2000-09-20 f2Plan 1 rows
X979074 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F996967 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F985779 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F975595 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
F873288 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F731398 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F701065 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
F633341 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F631527 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F629042 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F616625 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F616617 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F614967 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F607057 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F587242 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F578271 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F578255 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F572296 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F558781 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F547534 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F542850 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F540769 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F540657 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
F531557 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F524941 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F496361 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
F492730I DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
F473828 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F403358 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F383764 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
F377760 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F326930 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F325400 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
F305976 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
F293923 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F278290 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F273764 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F263146 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
F262061 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F256274 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F248700 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
F247743 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F227955 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F151800 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
F107992 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
F100150 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
B223522 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A981516 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A955213 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A951242 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A919233 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A915246 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A892564 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A850128 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A736627 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A705273 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A666308 DB2PLAN U 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A635743 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A632051 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A629518 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A629484 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A626217 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A621463 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A620076 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A596175 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A587951 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A577182 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A572942 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A572695 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A541931 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A532359 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A532022 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A524624 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A524551 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A524500 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A524438 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A524365 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A524314 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A524292 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A523539 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A523482 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A523385 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A523318 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A523288 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A522582 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A521072 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A512928 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A512707 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A511115 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A510860 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A510291 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A510178 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A509137 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A508938 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A508742 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A506455 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A506375 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A485020 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A474375 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A472348 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A472313 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A467700 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A465791 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A462253 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A454262 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A406205 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A394898 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A390880 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A385879 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A359673 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A351230 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A349155 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A347067 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A346474 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A342480 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A337145 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A327913 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A323691 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A317296 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A317210 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A313483 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A313291 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A309200 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A281305 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A276719 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A274961 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A268813 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A267256 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A267248 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A266578 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A258214 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A255371 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A244058 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A242407 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A230698 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A227440 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A223569 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A223348 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A223062 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A222384 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A215813 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A197300 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A177397 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A155853 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A152110 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan 0 rows
A116231 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan 0 rows
A523593 AU03A1A U 59 22.08.2011 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
A377027 DA377027 U 59 21.10.2009 PARENT_P 0 to 0001-01-01 f2Plan 0 rows
F718952 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan ---204 ro
F940771 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F604252 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F600435 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F551132 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F546562 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F540270 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F535609 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F526782 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F306871 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F260200 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F240889 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F222112 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
F192213 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
EXTRSQL DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan ---204 ro
A979074 DB2PLAN U 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan ---204 ro
A961140 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A927198 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan ---204 ro
A918249 DB2PLAN E 58 28.12.2011 STMTTOKE 0 to 0001-01-01 f2Plan ---204 ro
A914227 DB2PLAN U 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan ---204 ro
A589446 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A586033 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A570617 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A563742 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A563637 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A559315 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan ---204 ro
A532800 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A530267 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A497088 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A487481 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A471902 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A424475 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A373734 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan ---204 ro
A370464 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A366335 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A361694 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A345745 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A344129 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A331647 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A267272 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan ---204 ro
A254812 DB2PLAN E 59 28.12.2011 PARENT_P 0 to 0001-01-01 f2Plan ---204 ro
A249894 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A217534 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A169890 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A167669 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A167452 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A167138 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A166573 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A164392 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A156586 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A139274 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A118753 DB2PLAN E 51 28.12.2011 TABLE_TY 0 to 0001-01-01 f2Plan ---204 ro
A168658 DA168658 U 59 25.06.2010 PARENT_P 0 to 0001-01-01 f2Plan ---204 ro
GOWM200 DGOWM200 E 58 18.10.2006 STMTTOKE 0 to 0001-01-01 f2Plan ---204 ro
W015660 DW015660 E 46 30.07.2001 BIND_TIM ---904 to sqlCode f2Plan ---904 ro
A829269 DA829269 E 43 27.08.1997 IBM_SERV 32 to --- f2Plan 55 rows
A449415 DA449415 E 34 09.05.1995 JOIN_PGR 13 to --- f2Plan ---204 ro
A516058 DA516058 E 34 08.03.1995 JOIN_PGR 10 to --- f2Plan ---204 ro
A620181 DA620181 E 30 04.01.1994 COLLID 48 to --- f2Plan ---204 ro
$/creators/
$@for cr $@/forCreator/
$=cr=-word($cr, 1) $** creator
$=ts=- strip(left($cr, 7))
$=cmnViews =- $cr = 'CMNBATCH'
$=doReo = 0
$=suf=- '' $** Parameter für Spezialfälle
$=tsH= ${ts}H
$=tsL= ${ts}L
$=tsX= ${ts}X
if 0 then $@[ $** Spezial Namen für Daten Migration
$=suf= _New
$=tsH= ${ts}Q
$**$=tsL= ${ts}O
$]
$@=[
//*
//* db2SubSys = $subsys
//* tableSpaces = $db.$ts*
//* creator = $cr
//*
$]
if $fun == 'c' then $@=[
//* create plan tables only
$] else if $fun == 'r' then $@=[
//* drop recreate plan tables and views
//* all data lost |||||||||||||||||||||||||||||||||||| ||
$] else if $fun == 'd' then $@=[
//* drop tablespaces, tables and views
//* all data lost ||||||||||||||||||||||||||||||||||||
$] else if $fun == 'u' then $@=[
//* update plantables for v10.1 and recreate views
$] else if $fun == 'v' then $@=[
//* drop recreate plan views only
$] else $@[
call err 'bad fun='$fun
$]
if $suf \== '' then
$$ //* suf=$suf ||||||||||||||||||||||||||||||||||||||||||
if $cmnViews then
$$ //* with changeman views
$@=[
//*
//$cr EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99
//SYSTSIN DD *
DSN SYSTEM($subsys)
RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM DD DUMMY
//SYSIN DD *
set current path = '$path';
set current sqlid = 'S100447';
$]
$$ --#SET MAXERRORS 99
if $fun == 'd' | $fun == 'r' then
$@dropTS()
else if $fun \== 'c' then
$@dropViews()
$$ --#SET MAXERRORS 0
if $fun \== 'd' then $@/createOrUpdate/
if $isCre then $@=[
-----------------------------------------------------------------------
--CREATE THE SAMPLE TABLESPACE FOR THE V10 SAMPLE PLAN_TABLE,
--DSN_FUNCTION_TABLE, DSN_STATEMNT_TABLE, AND DSN_STATEMENT_CACHE_TABLE
CREATE TABLESPACE ${ts}X
IN ${db}
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP8K0
SEGSIZE 16
COMPRESS YES CLOSE YES
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE LOB TABLE SPACE FOR THE SAMPLE DSN_STATEMENT_CACHE_TABLE
CREATE LOB TABLESPACE $tsL
IN ${db}
BUFFERPOOL BP32K
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
CLOSE YES;
-----------------------------------------------------------------------
--CREATE THE TABLESPACES FOR THE OTHER V10 SAMPLE EXPLAIN TABLES
CREATE TABLESPACE ${ts}G
IN ${db}
BUFFERPOOL BP16K0
CCSID UNICODE
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
COMPRESS YES CLOSE YES;
CREATE TABLESPACE $tsH
IN ${db}
BUFFERPOOL BP8K0
CCSID UNICODE
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
COMPRESS YES CLOSE YES;
CREATE TABLESPACE ${ts}I
IN ${db}
BUFFERPOOL BP8K0
CCSID UNICODE
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
COMPRESS YES CLOSE YES;
CREATE LOB TABLESPACE ${ts}Y
IN ${db}
BUFFERPOOL BP8K0
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
CLOSE YES;
CREATE TABLESPACE ${ts}J
IN ${db}
BUFFERPOOL BP2
CCSID UNICODE
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
COMPRESS YES CLOSE YES;
$]
$@tb={plan PLAN_TABLE}
if $plan == '64 MERGN' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update plan_table from:' $plan
$] else if $plan == '59 PARENT_PLANNO' then $@=[ $** v9 update
$=doReo=1
ALTER TABLE $cr.PLAN_TABLE
ADD "BIND_EXPLAIN_ONLY" CHAR(1) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.PLAN_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.PLAN_TABLE
ADD "EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.PLAN_TABLE
ADD "MERGC" CHAR(1) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.PLAN_TABLE
ADD "MERGN" CHAR(1) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_FUNCTION_TABLE
ADD "FUNC_VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_FUNCTION_TABLE
ADD "SECURE" CHAR(1) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_FUNCTION_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_FUNCTION_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_STATEMNT_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_STATEMNT_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_STRUCT_TABLE
ADD "ORIGIN" CHAR(1) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STRUCT_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STRUCT_TABLE
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STRUCT_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "LOWBOUND" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "HIGHBOUND" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "LOWKEY" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "HIGHKEY" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "FIRSTPAGE" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ALTER "LASTPAGE" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PGROUP_TABLE
ADD "STRAW_MODEL" CHAR(1) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTLOKEY" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTHIKEY" SET DATA TYPE VARCHAR(40) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTLOPAG" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTHIPAG" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTLOPG" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ALTER "LPTHIPG" SET DATA TYPE CHAR(4) FOR BIT DATA ;
ALTER TABLE $cr.DSN_PTASK_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PTASK_TABLE
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PTASK_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ALTER "TABREF" SET DATA TYPE VARCHAR(64) FOR BIT DATA;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "UNCERTAINTY" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "UNCERTAINTY_1T" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "IMNP" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "DMNP" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "IMJC" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "IMFC" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "IMJBC" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "IMJFC" FLOAT(4) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_DETCOST_TABLE
ADD "CRED" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_SORT_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_SORT_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_SORTKEY_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_SORTKEY_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_PGRANGE_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_PGRANGE_TABLE
ADD "APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_PGRANGE_TABLE
ADD "PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_PGRANGE_TABLE
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_PGRANGE_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_QUERY_TABLE
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_QUERY_TABLE
ADD "APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_QUERY_TABLE
ADD "PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_QUERY_TABLE
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT ;
ALTER TABLE $cr.DSN_QUERY_TABLE
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT ;
$] else if $plan == '' then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE PLAN_TABLE.
CREATE TABLE $cr.PLAN_TABLE$suf
( "QUERYNO" INTEGER NOT NULL,
"QBLOCKNO" SMALLINT NOT NULL,
"APPLNAME" VARCHAR(24) NOT NULL,
"PROGNAME" VARCHAR(128) NOT NULL,
"PLANNO" SMALLINT NOT NULL,
"METHOD" SMALLINT NOT NULL,
"CREATOR" VARCHAR(128) NOT NULL,
"TNAME" VARCHAR(128) NOT NULL,
"TABNO" SMALLINT NOT NULL,
"ACCESSTYPE" CHAR(2) NOT NULL,
"MATCHCOLS" SMALLINT NOT NULL,
"ACCESSCREATOR" VARCHAR(128) NOT NULL,
"ACCESSNAME" VARCHAR(128) NOT NULL,
"INDEXONLY" CHAR(1) NOT NULL,
"SORTN_UNIQ" CHAR(1) NOT NULL,
"SORTN_JOIN" CHAR(1) NOT NULL,
"SORTN_ORDERBY" CHAR(1) NOT NULL,
"SORTN_GROUPBY" CHAR(1) NOT NULL,
"SORTC_UNIQ" CHAR(1) NOT NULL,
"SORTC_JOIN" CHAR(1) NOT NULL,
"SORTC_ORDERBY" CHAR(1) NOT NULL,
"SORTC_GROUPBY" CHAR(1) NOT NULL,
"TSLOCKMODE" CHAR(3) NOT NULL,
"TIMESTAMP" CHAR(16) NOT NULL,
"REMARKS" VARCHAR(762) NOT NULL,
"PREFETCH" CHAR(1) NOT NULL WITH DEFAULT,
"COLUMN_FN_EVAL" CHAR(1) NOT NULL WITH DEFAULT,
"MIXOPSEQ" SMALLINT NOT NULL WITH DEFAULT,
"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT,
"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT,
"ACCESS_DEGREE" SMALLINT,
"ACCESS_PGROUP_ID" SMALLINT,
"JOIN_DEGREE" SMALLINT,
"JOIN_PGROUP_ID" SMALLINT,
"SORTC_PGROUP_ID" SMALLINT,
"SORTN_PGROUP_ID" SMALLINT,
"PARALLELISM_MODE" CHAR(1),
"MERGE_JOIN_COLS" SMALLINT,
"CORRELATION_NAME" VARCHAR(128),
"PAGE_RANGE" CHAR(1) NOT NULL WITH DEFAULT,
"JOIN_TYPE" CHAR(1) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT,
"IBM_SERVICE_DATA" VARCHAR(254) FOR BIT DATA
NOT NULL WITH DEFAULT,
"WHEN_OPTIMIZE" CHAR(1) NOT NULL WITH DEFAULT,
"QBLOCK_TYPE" CHAR(6) NOT NULL WITH DEFAULT,
"BIND_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"OPTHINT" VARCHAR(128) NOT NULL WITH DEFAULT,
"HINT_USED" VARCHAR(128) NOT NULL WITH DEFAULT,
"PRIMARY_ACCESSTYPE" CHAR(1) NOT NULL WITH DEFAULT,
"PARENT_QBLOCKNO" SMALLINT NOT NULL WITH DEFAULT,
"TABLE_TYPE" CHAR(1),
"TABLE_ENCODE" CHAR(1) NOT NULL WITH DEFAULT,
"TABLE_SCCSID" SMALLINT NOT NULL WITH DEFAULT,
"TABLE_MCCSID" SMALLINT NOT NULL WITH DEFAULT,
"TABLE_DCCSID" SMALLINT NOT NULL WITH DEFAULT,
"ROUTINE_ID" INTEGER NOT NULL WITH DEFAULT,
"CTEREF" SMALLINT NOT NULL WITH DEFAULT,
"STMTTOKEN" VARCHAR(240),
"PARENT_PLANNO" SMALLINT NOT NULL WITH DEFAULT,
"BIND_EXPLAIN_ONLY" CHAR(1) NOT NULL WITH DEFAULT,
"SECTNOI" INTEGER NOT NULL WITH DEFAULT,
"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"MERGC" CHAR(1) NOT NULL WITH DEFAULT,
"MERGN" CHAR(1) NOT NULL WITH DEFAULT
)
IN ${db}.${ts}X
VOLATILE
CCSID UNICODE;
$] else call err 'cannot update plan_Table'$suf 'from' $plan
if $isCre then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_FUNCTION_TABLE.
CREATE TABLE $cr.DSN_FUNCTION_TABLE$suf
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT,
"QBLOCKNO" INTEGER NOT NULL WITH DEFAULT,
"APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT,
"PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT,
"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"SCHEMA_NAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"FUNCTION_NAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"SPEC_FUNC_NAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"FUNCTION_TYPE" CHAR(2) NOT NULL WITH DEFAULT,
"VIEW_CREATOR" VARCHAR(128) NOT NULL WITH DEFAULT,
"VIEW_NAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"PATH" VARCHAR(2048) NOT NULL WITH DEFAULT,
"FUNCTION_TEXT" VARCHAR(1500) NOT NULL WITH DEFAULT,
"FUNC_VERSION" VARCHAR(122) NOT NULL WITH DEFAULT,
"SECURE" CHAR(1) NOT NULL WITH DEFAULT,
"SECTNOI" INTEGER NOT NULL WITH DEFAULT,
"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.${ts}X
VOLATILE
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_STATEMNT_TABLE.
CREATE TABLE $cr.DSN_STATEMNT_TABLE$suf
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT,
"APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT,
"PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT,
"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT,
"GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT,
"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT,
"STMT_TYPE" CHAR(6) NOT NULL WITH DEFAULT,
"COST_CATEGORY" CHAR(1) NOT NULL WITH DEFAULT,
"PROCMS" INTEGER NOT NULL WITH DEFAULT,
"PROCSU" INTEGER NOT NULL WITH DEFAULT,
"REASON" VARCHAR(254) NOT NULL WITH DEFAULT,
"STMT_ENCODE" CHAR(1) NOT NULL WITH DEFAULT,
"TOTAL_COST" FLOAT NOT NULL WITH DEFAULT,
"SECTNOI" INTEGER NOT NULL WITH DEFAULT,
"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
$]
$@tb={stCa DSN_STATEMENT_CACHE_TABLE}
if $stCa == '67 STAT_SUS_LOG' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_STATEMENT_CACHE_TA'$suf
$] else if $stCa == '49 GROUP_MEMBER' $*+
| $stCa == '48 BIND_RA_TOT' then $@=[ $** v8 oder v9
$=doReo=1
$@[
if $stCa == '48 BIND_RA_TOT' then $@=[ $** v8
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT;
$] $]
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_EXECB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_GPAGB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SYNRB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_WRITB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_EROWB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_PROWB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SORTB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_INDXB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_RSCNB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_PGRPB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_RIDLIMTB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_RIDSTORB" BIGINT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "LITERAL_REPL" CHAR(1) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SUS_LATCH" FLOAT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SUS_PLATCH" FLOAT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SUS_DRAIN" FLOAT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SUS_CLAIM" FLOAT NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_STATEMENT_CACHE_TABLE
add "STAT_SUS_LOG" FLOAT NOT NULL WITH DEFAULT;
$] else if $stCa == '' then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_STATEMENT_CACHE_TABLE
CREATE TABLE $cr.DSN_STATEMENT_CACHE_TABLE$suf
( "STMT_ID" INTEGER NOT NULL,
"STMT_TOKEN" VARCHAR(240) ,
"COLLID" VARCHAR(128) NOT NULL,
"PROGRAM_NAME" VARCHAR(128) NOT NULL,
"INV_DROPALT" CHAR(1) NOT NULL,
"INV_REVOKE" CHAR(1) NOT NULL,
"INV_LRU" CHAR(1) NOT NULL,
"INV_RUNSTATS" CHAR(1) NOT NULL,
"CACHED_TS" TIMESTAMP NOT NULL,
"USERS" INTEGER NOT NULL,
"COPIES" INTEGER NOT NULL,
"LINES" INTEGER NOT NULL,
"PRIMAUTH" VARCHAR(128) NOT NULL,
"CURSQLID" VARCHAR(128) NOT NULL,
"BIND_QUALIFIER" VARCHAR(128) NOT NULL,
"BIND_ISO" CHAR(2) NOT NULL,
"BIND_CDATA" CHAR(1) NOT NULL,
"BIND_DYNRL" CHAR(1) NOT NULL,
"BIND_DEGRE" CHAR(1) NOT NULL,
"BIND_SQLRL" CHAR(1) NOT NULL,
"BIND_CHOLD" CHAR(1) NOT NULL,
"STAT_TS" TIMESTAMP NOT NULL,
"STAT_EXEC" INTEGER NOT NULL,
"STAT_GPAG" INTEGER NOT NULL,
"STAT_SYNR" INTEGER NOT NULL,
"STAT_WRIT" INTEGER NOT NULL,
"STAT_EROW" INTEGER NOT NULL,
"STAT_PROW" INTEGER NOT NULL,
"STAT_SORT" INTEGER NOT NULL,
"STAT_INDX" INTEGER NOT NULL,
"STAT_RSCN" INTEGER NOT NULL,
"STAT_PGRP" INTEGER NOT NULL,
"STAT_ELAP" FLOAT NOT NULL,
"STAT_CPU" FLOAT NOT NULL,
"STAT_SUS_SYNIO" FLOAT NOT NULL,
"STAT_SUS_LOCK" FLOAT NOT NULL,
"STAT_SUS_SWIT" FLOAT NOT NULL,
"STAT_SUS_GLCK" FLOAT NOT NULL,
"STAT_SUS_OTHR" FLOAT NOT NULL,
"STAT_SUS_OTHW" FLOAT NOT NULL,
"STAT_RIDLIMT" INTEGER NOT NULL,
"STAT_RIDSTOR" INTEGER NOT NULL,
"EXPLAIN_TS" TIMESTAMP NOT NULL,
"SCHEMA" VARCHAR(128) NOT NULL,
"STMT_TEXT" CLOB(2M) NOT NULL,
"STMT_ROWID" ROWID NOT NULL GENERATED ALWAYS,
"BIND_RO_TYPE" CHAR(1) NOT NULL WITH DEFAULT,
"BIND_RA_TOT" INTEGER NOT NULL WITH DEFAULT,
"GROUP_MEMBER" VARCHAR(24) NOT NULL WITH DEFAULT,
"STAT_EXECB" BIGINT NOT NULL WITH DEFAULT,
"STAT_GPAGB" BIGINT NOT NULL WITH DEFAULT,
"STAT_SYNRB" BIGINT NOT NULL WITH DEFAULT,
"STAT_WRITB" BIGINT NOT NULL WITH DEFAULT,
"STAT_EROWB" BIGINT NOT NULL WITH DEFAULT,
"STAT_PROWB" BIGINT NOT NULL WITH DEFAULT,
"STAT_SORTB" BIGINT NOT NULL WITH DEFAULT,
"STAT_INDXB" BIGINT NOT NULL WITH DEFAULT,
"STAT_RSCNB" BIGINT NOT NULL WITH DEFAULT,
"STAT_PGRPB" BIGINT NOT NULL WITH DEFAULT,
"STAT_RIDLIMTB" BIGINT NOT NULL WITH DEFAULT,
"STAT_RIDSTORB" BIGINT NOT NULL WITH DEFAULT,
"LITERAL_REPL" CHAR(1) NOT NULL WITH DEFAULT,
"STAT_SUS_LATCH" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_PLATCH" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_DRAIN" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_CLAIM" FLOAT NOT NULL WITH DEFAULT,
"STAT_SUS_LOG" FLOAT NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
-----------------------------------------------------------------------
--CREATE THE AUXILIARY TABLE FOR THE SAMPLE DSN_STATEMENT_CACHE_TABLE
CREATE AUX TABLE $cr.DSN_STATEMENT_CACHE_AUX$suf
IN ${db}.$tsL
STORES $cr.DSN_STATEMENT_CACHE_TABLE$suf
COLUMN STMT_TEXT;
$] else call err 'cannot update DSN_STATEMENT_CACHE_TA'$suf $*+
'from' $stCa
$@ix={keys drop PLAN_TABLE_HINT_IX$suf}
if $keys == 'QUERYNO<APPLNAME<PROGNAME<VERSION<COLLID<OPTHINT<' ,
then $@[
$] else if $fun == 'v' then $@[
say 'warning fun=v but index PLAN_TABLE_HINT_IX'$suf':' $keys
$] else $@=[
$=doReo=- $doReo | ($defer == 'YES')
$drop
--CREATE AN INDEX ON THE V10 SAMPLE PLAN_TABLE
--IT IS RECOMMENDED TO CREATE THIS INDEX IF
--USING USER-LEVEL AND/OR INSTANCE-LEVEL
--OPTIMIZATION HINTS
CREATE INDEX $cr.PLAN_TABLE_HINT_IX$suf
ON $cr.PLAN_TABLE$suf
( "QUERYNO",
"APPLNAME",
"PROGNAME",
"VERSION",
"COLLID",
"OPTHINT" )
$@ixAtt={}
$]
$@ix={keys drop PLAN_TABLE_idx1$suf}
if $keys == 'QUERYNO<BIND_TIME<' then $@[
$] else if $fun == 'v' then $@[
say 'warning fun=v but index PLAN_TABLE_idx1'$suf':' $keys
$] else $@=[
$=doReo=- $doReo | ($defer == 'YES')
$drop
CREATE INDEX $cr.PLAN_TABLE_idx1$suf
ON $cr.PLAN_Table$suf
( "QUERYNO"
,"BIND_TIME"
)
$@ixAtt={c}
$]
$@ix={keys drop PLAN_TABLE_PROG_IX$suf}
if $keys == 'PROGNAME<COLLID<VERSION<BIND_TIME<QUERYNO<' ,
then $@[
$] else if $fun == 'v' then $@[
say 'warning fun=v but index PLAN_TABLE_PROG_IX'$suf':' $keys
$] else $@=[
$=doReo=- $doReo | ($defer == 'YES')
$drop
CREATE INDEX $cr.PLAN_TABLE_PROG_ix$suf
ON $cr.PLAN_Table$suf
(PROGNAME ASC,
COLLID ASC,
VERSION ASC,
BIND_TIME ASC,
QUERYNO ASC)
$@ixAtt={}
$]
if $isCre then $@=[
CREATE INDEX $cr.FUNC_EXPidx1$suf
ON $cr.DSN_FUNCTION_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
CREATE INDEX $cr.DSN_STATEMNT_TABLE_idx1$suf
ON $cr.DSN_STATEMNT_Table$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
$]
if $isUpd & $stCa == '' then $@=[
-----------------------------------------------------------------------
--CREATE INDEXES ON THE V10 SAMPLE DSN_STATEMENT_CACHE_TABLE
CREATE INDEX $cr.DSN_STATEMENT_CACHE_IDX1$suf
ON $cr.DSN_STATEMENT_CACHE_TABLE$suf
( "STMT_ID" ASC )
$@ixAtt={}
CREATE INDEX $cr.DSN_STATEMENT_CACHE_IDX2$suf
ON $cr.DSN_STATEMENT_CACHE_TABLE$suf
( "STMT_TOKEN" ASC )
$@ixAtt={c}
CREATE INDEX $cr.DSN_STATEMENT_CACHE_IDX3$suf
ON $cr.DSN_STATEMENT_CACHE_TABLE$suf
( "EXPLAIN_TS" DESC )
$@ixAtt={}
CREATE INDEX $cr.DSN_STATEMENT_CACHE_AUXINX$suf
ON $cr.DSN_STATEMENT_CACHE_AUX$suf
$@ixAtt={l}
$]
$@tb={pred DSN_PREDICAT_TABLE}
if $pred == '41 VERSION' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_PREDICAT_Table'$suf $pred
$] else if $pred == '36 GROUP_MEMBER' then $@=[ $** V9
$=doReo=1
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "ORIGIN" CHAR(1) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "UNCERTAINTY" FLOAT(4) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "COLLID" VARCHAR(128) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_PREDICAT_Table$suf
ADD "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT;
$] else if $pred == '' then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_PREDICAT_TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_PREDICAT_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"PREDNO" INTEGER NOT NULL
,"TYPE" CHAR(8) NOT NULL
,"LEFT_HAND_SIDE" VARCHAR(128) NOT NULL
,"LEFT_HAND_PNO" INTEGER NOT NULL
,"LHS_TABNO" SMALLINT NOT NULL
,"LHS_QBNO" SMALLINT NOT NULL
,"RIGHT_HAND_SIDE" VARCHAR(128) NOT NULL
,"RIGHT_HAND_PNO" INTEGER NOT NULL
,"RHS_TABNO" SMALLINT NOT NULL
,"RHS_QBNO" SMALLINT NOT NULL
,"FILTER_FACTOR" FLOAT NOT NULL
,"BOOLEAN_TERM" CHAR(1) NOT NULL
,"SEARCHARG" CHAR(1) NOT NULL
,"JOIN" CHAR(1) NOT NULL
,"AFTER_JOIN" CHAR(1) NOT NULL
,"ADDED_PRED" CHAR(1) NOT NULL
,"REDUNDANT_PRED" CHAR(1) NOT NULL
,"DIRECT_ACCESS" CHAR(1) NOT NULL
,"KEYFIELD" CHAR(1) NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"CATEGORY" SMALLINT NOT NULL
,"CATEGORY_B" SMALLINT NOT NULL
,"TEXT" VARCHAR(2000) NOT NULL
,"PRED_ENCODE" CHAR(1) NOT NULL WITH DEFAULT
,"PRED_CCSID" SMALLINT NOT NULL WITH DEFAULT
,"PRED_MCCSID" SMALLINT NOT NULL WITH DEFAULT
,"MARKER" CHAR(1) NOT NULL WITH DEFAULT
,"PARENT_PNO" INTEGER NOT NULL
,"NEGATION" CHAR(1) NOT NULL
,"LITERALS" VARCHAR(128) NOT NULL
,"CLAUSE" CHAR(8) NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"ORIGIN" CHAR(1) NOT NULL WITH DEFAULT
,"UNCERTAINTY" FLOAT(4) NOT NULL WITH DEFAULT
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_PREDICAT_TABLE_IDX1$suf
ON $cr.DSN_PREDICAT_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
$] else call err 'cannot update DSN_PREDICAT_Table'$suf 'from' $pred
if $isCre then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_STRUCT_TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_STRUCT_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"PARENT" SMALLINT NOT NULL
,"TIMES" FLOAT NOT NULL
,"ROWCOUNT" INTEGER NOT NULL
,"ATOPEN" CHAR(1) NOT NULL
,"CONTEXT" CHAR(10) NOT NULL
,"ORDERNO" SMALLINT NOT NULL
,"DOATOPEN_PARENT" SMALLINT NOT NULL
,"QBLOCK_TYPE" CHAR(6) NOT NULL WITH DEFAULT
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"QUERY_STAGE" CHAR(8) NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"ORIGIN" CHAR(1) NOT NULL WITH DEFAULT
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_STRUCT_TABLE_IDX1$suf
ON $cr.DSN_STRUCT_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_PGROUP_TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_PGROUP_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"PLANNAME" VARCHAR(24) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"VERSION" VARCHAR(122) NOT NULL
,"GROUPID" SMALLINT NOT NULL
,"FIRSTPLAN" SMALLINT NOT NULL
,"LASTPLAN" SMALLINT NOT NULL
,"CPUCOST" REAL NOT NULL
,"IOCOST" REAL NOT NULL
,"BESTTIME" REAL NOT NULL
,"DEGREE" SMALLINT NOT NULL
,"MODE" CHAR(1) NOT NULL
,"REASON" SMALLINT NOT NULL
,"LOCALCPU" SMALLINT NOT NULL
,"TOTALCPU" SMALLINT NOT NULL
,"FIRSTBASE" SMALLINT
,"LARGETS" CHAR(1)
,"PARTKIND" CHAR(1)
,"GROUPTYPE" CHAR(3)
,"ORDER" CHAR(1)
,"STYLE" CHAR(4)
,"RANGEKIND" CHAR(1)
,"NKEYCOLS" SMALLINT
,"LOWBOUND" VARCHAR(40) FOR BIT DATA
,"HIGHBOUND" VARCHAR(40) FOR BIT DATA
,"LOWKEY" VARCHAR(40) FOR BIT DATA
,"HIGHKEY" VARCHAR(40) FOR BIT DATA
,"FIRSTPAGE" CHAR(4) FOR BIT DATA
,"LASTPAGE" CHAR(4) FOR BIT DATA
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"HOST_REASON" SMALLINT
,"PARA_TYPE" CHAR(4)
,"PART_INNER" CHAR(1)
,"GRNU_KEYRNG" CHAR(1)
,"OPEN_KEYRNG" CHAR(1)
,"APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"STRAW_MODEL" CHAR(1) NOT NULL WITH DEFAULT
)
IN ${db}.${ts}G
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_PGROUP_TABLE_IDX1$suf
ON $cr.DSN_PGROUP_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_PTASK_TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_PTASK_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"PGDNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"LPTNO" SMALLINT NOT NULL
,"KEYCOLID" SMALLINT
,"DPSI" CHAR(1) NOT NULL
,"LPTLOKEY" VARCHAR(40) FOR BIT DATA
,"LPTHIKEY" VARCHAR(40) FOR BIT DATA
,"LPTLOPAG" CHAR(4) FOR BIT DATA
,"LPTHIPAG" CHAR(4) FOR BIT DATA
,"LPTLOPG" CHAR(4) FOR BIT DATA
,"LPTHIPG" CHAR(4) FOR BIT DATA
,"LPTLOPT" SMALLINT
,"LPTHIPT" SMALLINT
,"KEYCOLDT" SMALLINT
,"KEYCOLPREC" SMALLINT
,"KEYCOLSCAL" SMALLINT
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsH
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_PTASK_TABLE_IDX1$suf
ON $cr.DSN_PTASK_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
$]
$@tb={filt DSN_FILTER_TABLE}
if $filt == '17 PUSHDOWN' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_Filter_Table from' $filt
$] else if $filt == '14 GROUP_MEMBER' then $@=[
$=doReo=1
ALTER TABLE $cr.DSN_FILTER_TABLE
add "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_FILTER_TABLE
add "VERSION" VARCHAR(122) NOT NULL WITH DEFAULT;
ALTER TABLE $cr.DSN_FILTER_TABLE
add "PUSHDOWN" CHAR(1) NOT NULL WITH DEFAULT;
$] else if $filt == '' then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_FILTER_TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_FILTER_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"PLANNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"ORDERNO" INTEGER NOT NULL
,"PREDNO" INTEGER NOT NULL
,"STAGE" CHAR(9) NOT NULL
,"ORDERCLASS" INTEGER NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"MIXOPSEQNO" SMALLINT NOT NULL
,"REEVAL" CHAR(1) NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
,"PUSHDOWN" CHAR(1) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_FILTER_TABLE_IDX1$suf
ON $cr.DSN_FILTER_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
$] else call err 'cannot update DSN_FilterAT_Table'$suf 'from' $filt
if $isCre then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_DETCOST_TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_DETCOST_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"PLANNO" SMALLINT NOT NULL
,"OPENIO" FLOAT(4) NOT NULL
,"OPENCPU" FLOAT(4) NOT NULL
,"OPENCOST" FLOAT(4) NOT NULL
,"DMIO" FLOAT(4) NOT NULL
,"DMCPU" FLOAT(4) NOT NULL
,"DMTOT" FLOAT(4) NOT NULL
,"SUBQIO" FLOAT(4) NOT NULL
,"SUBQCPU" FLOAT(4) NOT NULL
,"SUBQCOST" FLOAT(4) NOT NULL
,"BASEIO" FLOAT(4) NOT NULL
,"BASECPU" FLOAT(4) NOT NULL
,"BASETOT" FLOAT(4) NOT NULL
,"ONECOMPROWS" FLOAT(4) NOT NULL
,"IMLEAF" FLOAT(4) NOT NULL
,"IMIO" FLOAT(4) NOT NULL
,"IMPREFH" CHAR(2) NOT NULL
,"IMMPRED" INTEGER NOT NULL
,"IMFF" FLOAT(4) NOT NULL
,"IMSRPRED" INTEGER NOT NULL
,"IMFFADJ" FLOAT(4) NOT NULL
,"IMSCANCST" FLOAT(4) NOT NULL
,"IMROWCST" FLOAT(4) NOT NULL
,"IMPAGECST" FLOAT(4) NOT NULL
,"IMRIDSORT" FLOAT(4) NOT NULL
,"IMMERGCST" FLOAT(4) NOT NULL
,"IMCPU" FLOAT(4) NOT NULL
,"IMTOT" FLOAT(4) NOT NULL
,"IMSEQNO" SMALLINT NOT NULL
,"DMPREFH" CHAR(2) NOT NULL
,"DMCLUDIO" FLOAT(4) NOT NULL
,"DMNCLUDIO" FLOAT(4) NOT NULL
,"DMPREDS" INTEGER NOT NULL
,"DMSROWS" FLOAT(4) NOT NULL
,"DMSCANCST" FLOAT(4) NOT NULL
,"DMCOLS" SMALLINT NOT NULL
,"DMROWS" FLOAT(4) NOT NULL
,"RDSROWCST" FLOAT(4) NOT NULL
,"DMPAGECST" FLOAT(4) NOT NULL
,"DMDATAIO" FLOAT(4) NOT NULL
,"DMDATACPU" FLOAT(4) NOT NULL
,"DMDATATOT" FLOAT(4) NOT NULL
,"RDSROW" FLOAT(4) NOT NULL
,"SNCOLS" SMALLINT NOT NULL
,"SNROWS" FLOAT(4) NOT NULL
,"SNRECSZ" INTEGER NOT NULL
,"SNPAGES" FLOAT(4) NOT NULL
,"SNRUNS" FLOAT(4) NOT NULL
,"SNMERGES" FLOAT(4) NOT NULL
,"SNIOCOST" FLOAT(4) NOT NULL
,"SNCPUCOST" FLOAT(4) NOT NULL
,"SNCOST" FLOAT(4) NOT NULL
,"SNSCANIO" FLOAT(4) NOT NULL
,"SNSCANCPU" FLOAT(4) NOT NULL
,"SNSCANCOST" FLOAT(4) NOT NULL
,"SCCOLS" SMALLINT NOT NULL
,"SCROWS" FLOAT(4) NOT NULL
,"SCRECSZ" INTEGER NOT NULL
,"SCPAGES" FLOAT(4) NOT NULL
,"SCRUNS" FLOAT(4) NOT NULL
,"SCMERGES" FLOAT(4) NOT NULL
,"SCIOCOST" FLOAT(4) NOT NULL
,"SCCPUCOST" FLOAT(4) NOT NULL
,"SCCOST" FLOAT(4) NOT NULL
,"SCSCANIO" FLOAT(4) NOT NULL
,"SCSCANCPU" FLOAT(4) NOT NULL
,"SCSCANCOST" FLOAT(4) NOT NULL
,"COMPCARD" FLOAT(4) NOT NULL
,"COMPIOCOST" FLOAT(4) NOT NULL
,"COMPCPUCOST" FLOAT(4) NOT NULL
,"COMPCOST" FLOAT(4) NOT NULL
,"JOINCOLS" SMALLINT NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"COSTBLK" INTEGER NOT NULL
,"COSTSTOR" INTEGER NOT NULL
,"MPBLK" INTEGER NOT NULL
,"MPSTOR" INTEGER NOT NULL
,"COMPOSITES" INTEGER NOT NULL
,"CLIPPED" INTEGER NOT NULL
,"PARTITION" INTEGER NOT NULL
,"TABREF" VARCHAR(64) NOT NULL FOR BIT DATA
,"MAX_COMPOSITES" INTEGER NOT NULL
,"MAX_STOR" INTEGER NOT NULL
,"MAX_CPU" INTEGER NOT NULL
,"MAX_ELAP" INTEGER NOT NULL
,"TBL_JOINED_THRESH" INTEGER NOT NULL
,"STOR_USED" INTEGER NOT NULL
,"CPU_USED" INTEGER NOT NULL
,"ELAPSED" INTEGER NOT NULL
,"MIN_CARD_KEEP" FLOAT(4) NOT NULL
,"MAX_CARD_KEEP" FLOAT(4) NOT NULL
,"MIN_COST_KEEP" FLOAT(4) NOT NULL
,"MAX_COST_KEEP" FLOAT(4) NOT NULL
,"MIN_VALUE_KEEP" FLOAT(4) NOT NULL
,"MIN_VALUE_CARD_KEEP" FLOAT(4) NOT NULL
,"MIN_VALUE_COST_KEEP" FLOAT(4) NOT NULL
,"MAX_VALUE_KEEP" FLOAT(4) NOT NULL
,"MAX_VALUE_CARD_KEEP" FLOAT(4) NOT NULL
,"MAX_VALUE_COST_KEEP" FLOAT(4) NOT NULL
,"MIN_CARD_CLIP" FLOAT(4) NOT NULL
,"MAX_CARD_CLIP" FLOAT(4) NOT NULL
,"MIN_COST_CLIP" FLOAT(4) NOT NULL
,"MAX_COST_CLIP" FLOAT(4) NOT NULL
,"MIN_VALUE_CLIP" FLOAT(4) NOT NULL
,"MIN_VALUE_CARD_CLIP" FLOAT(4) NOT NULL
,"MIN_VALUE_COST_CLIP" FLOAT(4) NOT NULL
,"MAX_VALUE_CLIP" FLOAT(4) NOT NULL
,"MAX_VALUE_CARD_CLIP" FLOAT(4) NOT NULL
,"MAX_VALUE_COST_CLIP" FLOAT(4) NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"PSEQIOCOST" FLOAT(4) NOT NULL
,"PSEQCPUCOST" FLOAT(4) NOT NULL
,"PSEQCOST" FLOAT(4) NOT NULL
,"PADJIOCOST" FLOAT(4) NOT NULL
,"PADJCPUCOST" FLOAT(4) NOT NULL
,"PADJCOST" FLOAT(4) NOT NULL
,"UNCERTAINTY" FLOAT(4) NOT NULL WITH DEFAULT
,"UNCERTAINTY_1T" FLOAT(4) NOT NULL WITH DEFAULT
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
,"IMNP" FLOAT(4) NOT NULL WITH DEFAULT
,"DMNP" FLOAT(4) NOT NULL WITH DEFAULT
,"IMJC" FLOAT(4) NOT NULL WITH DEFAULT
,"IMFC" FLOAT(4) NOT NULL WITH DEFAULT
,"IMJBC" FLOAT(4) NOT NULL WITH DEFAULT
,"IMJFC" FLOAT(4) NOT NULL WITH DEFAULT
,"CRED" INTEGER NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_DETCOST_TABLE_IDX1$suf
ON $cr.DSN_DETCOST_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_SORT_TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_SORT_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"PLANNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"SORTC" CHAR(5) NOT NULL WITH DEFAULT
,"SORTN" CHAR(5) NOT NULL WITH DEFAULT
,"SORTNO" SMALLINT NOT NULL
,"KEYSIZE" SMALLINT NOT NULL
,"ORDERCLASS" INTEGER NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_SORT_TABLE_IDX1$suf
ON $cr.DSN_SORT_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_SORTKEY_TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_SORTKEY_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"PLANNO" SMALLINT NOT NULL
,"APPLNAME" VARCHAR(24) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"SORTNO" SMALLINT NOT NULL
,"ORDERNO" SMALLINT NOT NULL
,"EXPTYPE" CHAR(3) NOT NULL
,"TEXT" VARCHAR(128) NOT NULL
,"TABNO" SMALLINT NOT NULL
,"COLNO" SMALLINT NOT NULL
,"DATATYPE" CHAR(18) NOT NULL
,"LENGTH" INTEGER NOT NULL
,"CCSID" INTEGER NOT NULL
,"ORDERCLASS" INTEGER NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_SORTKEY_TABLE_IDX1$suf
ON $cr.DSN_SORTKEY_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_PGRANGE_TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_PGRANGE_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"QBLOCKNO" SMALLINT NOT NULL
,"TABNO" SMALLINT NOT NULL
,"RANGE" SMALLINT NOT NULL
,"FIRSTPART" SMALLINT NOT NULL
,"LASTPART" SMALLINT NOT NULL
,"NUMPARTS" SMALLINT NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT
,"PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_PGRANGE_TABLE_IDX1$suf
ON $cr.DSN_PGRANGE_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
$]
$@tb={viRe DSN_VIEWREF_TABLE}
if $viRe == '12 SECTNOI' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_VIEWREF_TABLE' $viRe
$] else if $viRe == '11 GROUP_MEMBER' then $@=[
$=doReo=1
alter TABLE $cr.DSN_VIEWREF_TABLE$suf
add "SECTNOI" INTEGER NOT NULL WITH DEFAULT;
$] else if $viRe == '' then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_VIEWREF_TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_VIEWREF_TABLE$suf
( "QUERYNO" INTEGER NOT NULL WITH DEFAULT
,"APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT
,"PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"CREATOR" VARCHAR(128) NOT NULL WITH DEFAULT
,"NAME" VARCHAR(128) NOT NULL WITH DEFAULT
,"TYPE" CHAR(1) NOT NULL WITH DEFAULT
,"MQTUSE" SMALLINT NOT NULL WITH DEFAULT
,"EXPLAIN_TIME" TIMESTAMP NOT NULL WITH DEFAULT
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_VIEWREF_TABLE_IDX1$suf
ON $cr.DSN_VIEWREF_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
$] else call err 'cannot update DSN_VIEWREF_TABLE from' $viRe
if $isCre then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_QUERY_TABLE AND ITS INDEXES
CREATE TABLE $cr.DSN_QUERY_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"TYPE" CHAR(8) NOT NULL
,"QUERY_STAGE" CHAR(8) NOT NULL
,"SEQNO" INTEGER NOT NULL
,"NODE_DATA" CLOB(2M) NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"QUERY_ROWID" ROWID NOT NULL
GENERATED BY DEFAULT
,"GROUP_MEMBER" VARCHAR(24) NOT NULL
,"HASHKEY" INTEGER NOT NULL
,"HAS_PRED" CHAR(1) NOT NULL
,"SECTNOI" INTEGER NOT NULL WITH DEFAULT
,"APPLNAME" VARCHAR(24) NOT NULL WITH DEFAULT
,"PROGNAME" VARCHAR(128) NOT NULL WITH DEFAULT
,"COLLID" VARCHAR(128) NOT NULL WITH DEFAULT
,"VERSION" VARCHAR(122) NOT NULL WITH DEFAULT
)
IN ${db}.${ts}I
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_QUERY_TABLE_IDX1$suf
ON $cr.DSN_QUERY_TABLE$suf
( "QUERYNO"
,"EXPLAIN_TIME"
)
$@ixAtt={}
CREATE INDEX $cr.DSN_QUERY_TABLE_IDX2$suf
ON $cr.DSN_QUERY_TABLE$suf
( "QUERYNO"
,"TYPE"
,"QUERY_STAGE"
,"EXPLAIN_TIME"
,"SEQNO"
)
$@ixAtt={}
CREATE UNIQUE INDEX $cr.DSN_QUERY_TABLE_IDX3$suf
ON $cr.DSN_QUERY_TABLE$suf
( "QUERY_ROWID"
)
$@ixAtt={}
-----------------------------------------------------------------------
--CREATE THE AUXILIARY TABLE FOR THE V10 SAMPLE DSN_QUERY_TABLE
--AND ITS INDEX
CREATE AUX TABLE $cr.DSN_QUERY_AUX$suf
IN ${db}.${ts}Y
STORES $cr.DSN_QUERY_TABLE$suf
COLUMN "NODE_DATA";
CREATE INDEX $cr.DSN_QUERY_AUXINX$suf
ON $cr.DSN_QUERY_AUX$suf
$@ixAtt={l}
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_VIRTUAL_INDEXES TABLE AND ITS INDEX
CREATE TABLE $cr.DSN_VIRTUAL_INDEXES$suf
( "TBCREATOR" VARCHAR(128) NOT NULL
,"TBNAME" VARCHAR(128) NOT NULL
,"IXCREATOR" VARCHAR(128) NOT NULL
,"IXNAME" VARCHAR(128) NOT NULL
,"ENABLE" CHAR(1) NOT NULL
CHECK("ENABLE" IN('Y','N'))
,"MODE" CHAR(1) NOT NULL
CHECK("MODE" IN('C','D'))
,"UNIQUERULE" CHAR(1) NOT NULL
CHECK("UNIQUERULE" IN('D','U'))
,"COLCOUNT" SMALLINT NOT NULL
CHECK("COLCOUNT" > 0)
,"CLUSTERING" CHAR(1) NOT NULL
CHECK("CLUSTERING" IN('Y','N'))
,"NLEAF" INTEGER NOT NULL
CHECK("NLEAF" >= -1)
,"NLEVELS" SMALLINT NOT NULL
CHECK("NLEVELS" >= -1)
,"INDEXTYPE" CHAR(1) NOT NULL WITH DEFAULT
CHECK("INDEXTYPE" IN('D','2'))
,"PGSIZE" SMALLINT NOT NULL
CHECK("PGSIZE" IN(4, 8, 16, 32))
,"FIRSTKEYCARDF" FLOAT NOT NULL WITH DEFAULT -1
CHECK("FIRSTKEYCARDF" = -1
OR "FIRSTKEYCARDF" >= 0)
,"FULLKEYCARDF" FLOAT NOT NULL WITH DEFAULT -1
CHECK("FULLKEYCARDF" = -1
OR "FULLKEYCARDF" >= 0)
,"CLUSTERRATIOF" FLOAT NOT NULL WITH DEFAULT -1
CHECK("CLUSTERRATIOF" = -1
OR "CLUSTERRATIOF" >= 0)
,"PADDED" CHAR(1) NOT NULL WITH DEFAULT
CHECK("PADDED" IN(' ','Y','N'))
,"COLNO1" SMALLINT
CHECK("COLNO1" IS NULL
OR "COLNO1" > 0)
,"ORDERING1" CHAR(1)
CHECK("ORDERING1" IS NULL
OR "ORDERING1" IN('A','D'))
,"COLNO2" SMALLINT
CHECK("COLNO2" IS NULL
OR "COLNO2" > 0)
,"ORDERING2" CHAR(1)
CHECK("ORDERING2" IS NULL
OR "ORDERING2" IN('A','D'))
,"COLNO3" SMALLINT
CHECK("COLNO3" IS NULL
OR "COLNO3" > 0)
,"ORDERING3" CHAR(1)
CHECK("ORDERING3" IS NULL
OR "ORDERING3" IN('A','D'))
,"COLNO4" SMALLINT
CHECK("COLNO4" IS NULL
OR "COLNO4" > 0)
,"ORDERING4" CHAR(1)
CHECK("ORDERING4" IS NULL
OR "ORDERING4" IN('A','D'))
,"COLNO5" SMALLINT
CHECK("COLNO5" IS NULL
OR "COLNO5" > 0)
,"ORDERING5" CHAR(1)
CHECK("ORDERING5" IS NULL
OR "ORDERING5" IN('A','D'))
,"COLNO6" SMALLINT
CHECK("COLNO6" IS NULL
OR "COLNO6" > 0)
,"ORDERING6" CHAR(1)
CHECK("ORDERING6" IS NULL
OR "ORDERING6" IN('A','D'))
,"COLNO7" SMALLINT
CHECK("COLNO7" IS NULL
OR "COLNO7" > 0)
,"ORDERING7" CHAR(1)
CHECK("ORDERING7" IS NULL
OR "ORDERING7" IN('A','D'))
,"COLNO8" SMALLINT
CHECK("COLNO8" IS NULL
OR "COLNO8" > 0)
,"ORDERING8" CHAR(1)
CHECK("ORDERING8" IS NULL
OR "ORDERING8" IN('A','D'))
,"COLNO9" SMALLINT
CHECK("COLNO9" IS NULL
OR "COLNO9" > 0)
,"ORDERING9" CHAR(1)
CHECK("ORDERING9" IS NULL
OR "ORDERING9" IN('A','D'))
,"COLNO10" SMALLINT
CHECK("COLNO10" IS NULL
OR "COLNO10" > 0)
,"ORDERING10" CHAR(1)
CHECK("ORDERING10" IS NULL
OR "ORDERING10" IN('A','D'))
,"COLNO11" SMALLINT
CHECK("COLNO11" IS NULL
OR "COLNO11" > 0)
,"ORDERING11" CHAR(1)
CHECK("ORDERING11" IS NULL
OR "ORDERING11" IN('A','D'))
,"COLNO12" SMALLINT
CHECK("COLNO12" IS NULL
OR "COLNO12" > 0)
,"ORDERING12" CHAR(1)
CHECK("ORDERING12" IS NULL
OR "ORDERING12" IN('A','D'))
,"COLNO13" SMALLINT
CHECK("COLNO13" IS NULL
OR "COLNO13" > 0)
,"ORDERING13" CHAR(1)
CHECK("ORDERING13" IS NULL
OR "ORDERING13" IN('A','D'))
,"COLNO14" SMALLINT
CHECK("COLNO14" IS NULL
OR "COLNO14" > 0)
,"ORDERING14" CHAR(1)
CHECK("ORDERING14" IS NULL
OR "ORDERING14" IN('A','D'))
,"COLNO15" SMALLINT
CHECK("COLNO15" IS NULL
OR "COLNO15" > 0)
,"ORDERING15" CHAR(1)
CHECK("ORDERING15" IS NULL
OR "ORDERING15" IN('A','D'))
,"COLNO16" SMALLINT
CHECK("COLNO16" IS NULL
OR "COLNO16" > 0)
,"ORDERING16" CHAR(1)
CHECK("ORDERING16" IS NULL
OR "ORDERING16" IN('A','D'))
,"COLNO17" SMALLINT
CHECK("COLNO17" IS NULL
OR "COLNO17" > 0)
,"ORDERING17" CHAR(1)
CHECK("ORDERING17" IS NULL
OR "ORDERING17" IN('A','D'))
,"COLNO18" SMALLINT
CHECK("COLNO18" IS NULL
OR "COLNO18" > 0)
,"ORDERING18" CHAR(1)
CHECK("ORDERING18" IS NULL
OR "ORDERING18" IN('A','D'))
,"COLNO19" SMALLINT
CHECK("COLNO19" IS NULL
OR "COLNO19" > 0)
,"ORDERING19" CHAR(1)
CHECK("ORDERING19" IS NULL
OR "ORDERING19" IN('A','D'))
,"COLNO20" SMALLINT
CHECK("COLNO20" IS NULL
OR "COLNO20" > 0)
,"ORDERING20" CHAR(1)
CHECK("ORDERING20" IS NULL
OR "ORDERING20" IN('A','D'))
,"COLNO21" SMALLINT
CHECK("COLNO21" IS NULL
OR "COLNO21" > 0)
,"ORDERING21" CHAR(1)
CHECK("ORDERING21" IS NULL
OR "ORDERING21" IN('A','D'))
,"COLNO22" SMALLINT
CHECK("COLNO22" IS NULL
OR "COLNO22" > 0)
,"ORDERING22" CHAR(1)
CHECK("ORDERING22" IS NULL
OR "ORDERING22" IN('A','D'))
,"COLNO23" SMALLINT
CHECK("COLNO23" IS NULL
OR "COLNO23" > 0)
,"ORDERING23" CHAR(1)
CHECK("ORDERING23" IS NULL
OR "ORDERING23" IN('A','D'))
,"COLNO24" SMALLINT
CHECK("COLNO24" IS NULL
OR "COLNO24" > 0)
,"ORDERING24" CHAR(1)
CHECK("ORDERING24" IS NULL
OR "ORDERING24" IN('A','D'))
,"COLNO25" SMALLINT
CHECK("COLNO25" IS NULL
OR "COLNO25" > 0)
,"ORDERING25" CHAR(1)
CHECK("ORDERING25" IS NULL
OR "ORDERING25" IN('A','D'))
,"COLNO26" SMALLINT
CHECK("COLNO26" IS NULL
OR "COLNO26" > 0)
,"ORDERING26" CHAR(1)
CHECK("ORDERING26" IS NULL
OR "ORDERING26" IN('A','D'))
,"COLNO27" SMALLINT
CHECK("COLNO27" IS NULL
OR "COLNO27" > 0)
,"ORDERING27" CHAR(1)
CHECK("ORDERING27" IS NULL
OR "ORDERING27" IN('A','D'))
,"COLNO28" SMALLINT
CHECK("COLNO28" IS NULL
OR "COLNO28" > 0)
,"ORDERING28" CHAR(1)
CHECK("ORDERING28" IS NULL
OR "ORDERING28" IN('A','D'))
,"COLNO29" SMALLINT
CHECK("COLNO29" IS NULL
OR "COLNO29" > 0)
,"ORDERING29" CHAR(1)
CHECK("ORDERING29" IS NULL
OR "ORDERING29" IN('A','D'))
,"COLNO30" SMALLINT
CHECK("COLNO30" IS NULL
OR "COLNO30" > 0)
,"ORDERING30" CHAR(1)
CHECK("ORDERING30" IS NULL
OR "ORDERING30" IN('A','D'))
,"COLNO31" SMALLINT
CHECK("COLNO31" IS NULL
OR "COLNO31" > 0)
,"ORDERING31" CHAR(1)
CHECK("ORDERING31" IS NULL
OR "ORDERING31" IN('A','D'))
,"COLNO32" SMALLINT
CHECK("COLNO32" IS NULL
OR "COLNO32" > 0)
,"ORDERING32" CHAR(1)
CHECK("ORDERING32" IS NULL
OR "ORDERING32" IN('A','D'))
,"COLNO33" SMALLINT
CHECK("COLNO33" IS NULL
OR "COLNO33" > 0)
,"ORDERING33" CHAR(1)
CHECK("ORDERING33" IS NULL
OR "ORDERING33" IN('A','D'))
,"COLNO34" SMALLINT
CHECK("COLNO34" IS NULL
OR "COLNO34" > 0)
,"ORDERING34" CHAR(1)
CHECK("ORDERING34" IS NULL
OR "ORDERING34" IN('A','D'))
,"COLNO35" SMALLINT
CHECK("COLNO35" IS NULL
OR "COLNO35" > 0)
,"ORDERING35" CHAR(1)
CHECK("ORDERING35" IS NULL
OR "ORDERING35" IN('A','D'))
,"COLNO36" SMALLINT
CHECK("COLNO36" IS NULL
OR "COLNO36" > 0)
,"ORDERING36" CHAR(1)
CHECK("ORDERING36" IS NULL
OR "ORDERING36" IN('A','D'))
,"COLNO37" SMALLINT
CHECK("COLNO37" IS NULL
OR "COLNO37" > 0)
,"ORDERING37" CHAR(1)
CHECK("ORDERING37" IS NULL
OR "ORDERING37" IN('A','D'))
,"COLNO38" SMALLINT
CHECK("COLNO38" IS NULL
OR "COLNO38" > 0)
,"ORDERING38" CHAR(1)
CHECK("ORDERING38" IS NULL
OR "ORDERING38" IN('A','D'))
,"COLNO39" SMALLINT
CHECK("COLNO39" IS NULL
OR "COLNO39" > 0)
,"ORDERING39" CHAR(1)
CHECK("ORDERING39" IS NULL
OR "ORDERING39" IN('A','D'))
,"COLNO40" SMALLINT
CHECK("COLNO40" IS NULL
OR "COLNO40" > 0)
,"ORDERING40" CHAR(1)
CHECK("ORDERING40" IS NULL
OR "ORDERING40" IN('A','D'))
,"COLNO41" SMALLINT
CHECK("COLNO41" IS NULL
OR "COLNO41" > 0)
,"ORDERING41" CHAR(1)
CHECK("ORDERING41" IS NULL
OR "ORDERING41" IN('A','D'))
,"COLNO42" SMALLINT
CHECK("COLNO42" IS NULL
OR "COLNO42" > 0)
,"ORDERING42" CHAR(1)
CHECK("ORDERING42" IS NULL
OR "ORDERING42" IN('A','D'))
,"COLNO43" SMALLINT
CHECK("COLNO43" IS NULL
OR "COLNO43" > 0)
,"ORDERING43" CHAR(1)
CHECK("ORDERING43" IS NULL
OR "ORDERING43" IN('A','D'))
,"COLNO44" SMALLINT
CHECK("COLNO44" IS NULL
OR "COLNO44" > 0)
,"ORDERING44" CHAR(1)
CHECK("ORDERING44" IS NULL
OR "ORDERING44" IN('A','D'))
,"COLNO45" SMALLINT
CHECK("COLNO45" IS NULL
OR "COLNO45" > 0)
,"ORDERING45" CHAR(1)
CHECK("ORDERING45" IS NULL
OR "ORDERING45" IN('A','D'))
,"COLNO46" SMALLINT
CHECK("COLNO46" IS NULL
OR "COLNO46" > 0)
,"ORDERING46" CHAR(1)
CHECK("ORDERING46" IS NULL
OR "ORDERING46" IN('A','D'))
,"COLNO47" SMALLINT
CHECK("COLNO47" IS NULL
OR "COLNO47" > 0)
,"ORDERING47" CHAR(1)
CHECK("ORDERING47" IS NULL
OR "ORDERING47" IN('A','D'))
,"COLNO48" SMALLINT
CHECK("COLNO48" IS NULL
OR "COLNO48" > 0)
,"ORDERING48" CHAR(1)
CHECK("ORDERING48" IS NULL
OR "ORDERING48" IN('A','D'))
,"COLNO49" SMALLINT
CHECK("COLNO49" IS NULL
OR "COLNO49" > 0)
,"ORDERING49" CHAR(1)
CHECK("ORDERING49" IS NULL
OR "ORDERING49" IN('A','D'))
,"COLNO50" SMALLINT
CHECK("COLNO50" IS NULL
OR "COLNO50" > 0)
,"ORDERING50" CHAR(1)
CHECK("ORDERING50" IS NULL
OR "ORDERING50" IN('A','D'))
,"COLNO51" SMALLINT
CHECK("COLNO51" IS NULL
OR "COLNO51" > 0)
,"ORDERING51" CHAR(1)
CHECK("ORDERING51" IS NULL
OR "ORDERING51" IN('A','D'))
,"COLNO52" SMALLINT
CHECK("COLNO52" IS NULL
OR "COLNO52" > 0)
,"ORDERING52" CHAR(1)
CHECK("ORDERING52" IS NULL
OR "ORDERING52" IN('A','D'))
,"COLNO53" SMALLINT
CHECK("COLNO53" IS NULL
OR "COLNO53" > 0)
,"ORDERING53" CHAR(1)
CHECK("ORDERING53" IS NULL
OR "ORDERING53" IN('A','D'))
,"COLNO54" SMALLINT
CHECK("COLNO54" IS NULL
OR "COLNO54" > 0)
,"ORDERING54" CHAR(1)
CHECK("ORDERING54" IS NULL
OR "ORDERING54" IN('A','D'))
,"COLNO55" SMALLINT
CHECK("COLNO55" IS NULL
OR "COLNO55" > 0)
,"ORDERING55" CHAR(1)
CHECK("ORDERING55" IS NULL
OR "ORDERING55" IN('A','D'))
,"COLNO56" SMALLINT
CHECK("COLNO56" IS NULL
OR "COLNO56" > 0)
,"ORDERING56" CHAR(1)
CHECK("ORDERING56" IS NULL
OR "ORDERING56" IN('A','D'))
,"COLNO57" SMALLINT
CHECK("COLNO57" IS NULL
OR "COLNO57" > 0)
,"ORDERING57" CHAR(1)
CHECK("ORDERING57" IS NULL
OR "ORDERING57" IN('A','D'))
,"COLNO58" SMALLINT
CHECK("COLNO58" IS NULL
OR "COLNO58" > 0)
,"ORDERING58" CHAR(1)
CHECK("ORDERING58" IS NULL
OR "ORDERING58" IN('A','D'))
,"COLNO59" SMALLINT
CHECK("COLNO59" IS NULL
OR "COLNO59" > 0)
,"ORDERING59" CHAR(1)
CHECK("ORDERING59" IS NULL
OR "ORDERING59" IN('A','D'))
,"COLNO60" SMALLINT
CHECK("COLNO60" IS NULL
OR "COLNO60" > 0)
,"ORDERING60" CHAR(1)
CHECK("ORDERING60" IS NULL
OR "ORDERING60" IN('A','D'))
,"COLNO61" SMALLINT
CHECK("COLNO61" IS NULL
OR "COLNO61" > 0)
,"ORDERING61" CHAR(1)
CHECK("ORDERING61" IS NULL
OR "ORDERING61" IN('A','D'))
,"COLNO62" SMALLINT
CHECK("COLNO62" IS NULL
OR "COLNO62" > 0)
,"ORDERING62" CHAR(1)
CHECK("ORDERING62" IS NULL
OR "ORDERING62" IN('A','D'))
,"COLNO63" SMALLINT
CHECK("COLNO63" IS NULL
OR "COLNO63" > 0)
,"ORDERING63" CHAR(1)
CHECK("ORDERING63" IS NULL
OR "ORDERING63" IN('A','D'))
,"COLNO64" SMALLINT
CHECK("COLNO64" IS NULL
OR "COLNO64" > 0)
,"ORDERING64" CHAR(1)
CHECK("ORDERING64" IS NULL
OR "ORDERING64" IN('A','D'))
)
IN ${db}.${ts}J
VOLATILE
CCSID UNICODE;
CREATE INDEX $cr.DSN_VIRTUAL_INDEXES_IDX1$suf
ON $cr.DSN_VIRTUAL_INDEXES$suf
( "TBCREATOR"
,"TBNAME"
)
$@ixAtt={}
$]
$@tb={coDi DSN_COLDIST_TABLE}
if $coDi == '20 HIGHVALUE' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_COLDIST_TABLE' $coDi
$] else if $coDi == '' then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_COLDIST_TABLE
CREATE TABLE $cr.DSN_COLDIST_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"APPLNAME" VARCHAR(128) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL
,"GROUP_MEMBER" VARCHAR(128) NOT NULL
,"SECTNOI" INTEGER NOT NULL
,"VERSION" VARCHAR(122) NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"SCHEMA" VARCHAR(128) NOT NULL
,"TBNAME" VARCHAR(128) NOT NULL
,"NAME" VARCHAR(128) NOT NULL
,"COLVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
,"TYPE" CHAR(1) NOT NULL
,"CARDF" FLOAT NOT NULL
,"COLGROUPCOLNO" VARCHAR(254) NOT NULL FOR BIT DATA
,"NUMCOLUMNS" SMALLINT NOT NULL
,"FREQUENCYF" FLOAT NOT NULL
,"QUANTILENO" SMALLINT NOT NULL
,"LOWVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
,"HIGHVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
$] else call err 'cannot update DSN_COLDIST_TABLE from' $coDi
$@tb={kyDi DSN_KEYTGTDIST_TABLE}
if $kyDi == '20 HIGHVALUE' then $@=[ $** already v10 ok
$] else if \ $isUpd then $@[
call err 'fun' $fun 'cannot update DSN_KEYTGTDIST_TABLE' $kyDi
$] else if $kyDi == '' then $@=[
-----------------------------------------------------------------------
--CREATE THE V10 SAMPLE DSN_KEYTGTDIST_TABLE
CREATE TABLE $cr.DSN_KEYTGTDIST_TABLE$suf
( "QUERYNO" INTEGER NOT NULL
,"APPLNAME" VARCHAR(128) NOT NULL
,"PROGNAME" VARCHAR(128) NOT NULL
,"COLLID" VARCHAR(128) NOT NULL
,"GROUP_MEMBER" VARCHAR(128) NOT NULL
,"SECTNOI" INTEGER NOT NULL
,"VERSION" VARCHAR(122) NOT NULL
,"EXPLAIN_TIME" TIMESTAMP NOT NULL
,"IXSCHEMA" VARCHAR(128) NOT NULL
,"IXNAME" VARCHAR(128) NOT NULL
,"KEYSEQ" VARCHAR(128) NOT NULL
,"KEYVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
,"TYPE" CHAR(1) NOT NULL
,"CARDF" FLOAT NOT NULL
,"KEYGROUPKEYNO" VARCHAR(254) NOT NULL FOR BIT DATA
,"NUMKEYS" SMALLINT NOT NULL
,"FREQUENCYF" FLOAT NOT NULL
,"QUANTILENO" SMALLINT NOT NULL
,"LOWVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
,"HIGHVALUE" VARCHAR(2000) NOT NULL FOR BIT DATA
)
IN ${db}.$tsX
VOLATILE
CCSID UNICODE;
$] else call err 'cannot update DSN_KEYTGTDIST_TABLE from' $kyDi
if $isCre then $@=[
-----------------------------------------------------------------------
-- fileAid plan table
CREATE TABLE $cr.F2PLAN_TABLE$suf
(TIMESTAMP CHAR(16) FOR SBCS DATA NOT NULL
WITH DEFAULT,
STMTNO INTEGER NOT NULL WITH DEFAULT,
SEQNO INTEGER NOT NULL WITH DEFAULT,
TEXT VARCHAR(254) FOR SBCS DATA NOT NULL
WITH DEFAULT,
REMARKS CHAR(64) FOR SBCS DATA NOT NULL
WITH DEFAULT)
IN ${db}.$tsX
AUDIT NONE
DATA CAPTURE NONE
NOT VOLATILE
APPEND NO ;
$]
if $suf == '' then $@=/views/
--- versionen, compiles und explains eines packages -------------------
-- does not work if plan_table ebcdic ||
create view $cr.plan_view0 as
with e as (
select p.collid
, p.name prog
, p.version
, count(e.collid) expCnt
, e.bind_Time
, case when e.bind_time is null then 'p'
when e.bind_time > min(p.bindTime) then '>'
when e.bind_time = min(p.bindTime) then '='
else '<' end c
, min(p.pcTimeStamp) pcTimestamp
, sum(case when hint_Used = '' then 0 else 1 end) hiUse
, sum(case when e.optHint = '' then 0 else 1 end) optHi
, max(max(e.optHint, hint_Used)) hint
from sysIbm.sysPackage p
left join $cr.plan_table e
on e.collid = p.collid and e.progName = p.name
and e.version = p.version
where p.location = ''
group by p.collid, p.name, p.version, e.bind_Time
union all select collid
, progName prog
, version
, count(e.collid) expCnt
, e.bind_Time
, 'e' c
, case when 1=0 then current timestamp else null end pcTimestamp
, sum(case when hint_Used = '' then 0 else 1 end) hiUse
, sum(case when e.optHint = '' then 0 else 1 end) optHi
, max(max(e.optHint, hint_Used)) hint
from CMNBATCH.plan_table e
where not exists (select 1 from sysibm.sysPackage p
where e.collid = p.collid and e.progName = p.name
and e.version = p.version and p.location = '' )
group by e.collid, e.progName, e.version, e.bind_Time
)
select substr(collid, 1, 6) "collid"
, substr(prog, 1, 8) "prog"
, substr(version, 1, 12) "version"
, smallint(expCnt) "expCnt"
, bind_time, c, pcTimestamp
, smallint(hiUse) "hiUse"
, smallint(optHi) "optHi"
, substr(hint, 1, 8) "hint"
, collid, prog, version
, coalesce(pcTimestamp, bind_time) pcBi
from e
;
CREATE VIEW $cr.PLAN_VIEW1 AS
select substr(right(' ' || strip(char(queryNo)) , 6)
|| right(' ' || strip(char(qBlockNo)) , 2)
|| right(' ' || strip(char(planNo)) , 2)
|| right(' ' || strip(char(mixOpSeq)), 1)
,1 ,11) "Queryn B PM",
QBLOCK_TYPE AS "TYPE",
CASE WHEN PRIMARY_ACCESSTYPE = 'D' then 'dirRow'
WHEN ACCESSTYPE = 'I ' THEN 'ixScan'
WHEN ACCESSTYPE = 'I1' THEN 'ixOne '
WHEN ACCESSTYPE = 'R ' THEN 'tsScan'
WHEN ACCESSTYPE = 'RW' THEN 'woScan'
WHEN ACCESSTYPE = 'N ' THEN 'ixSPin'
WHEN ACCESSTYPE = 'M ' THEN 'ixMult'
WHEN ACCESSTYPE = 'MX' THEN 'ixMSca'
WHEN ACCESSTYPE = 'MI' THEN 'ixMInt'
WHEN ACCESSTYPE = 'MU' THEN 'ixMUni'
WHEN ACCESSTYPE = 'T ' THEN 'ixSPRS'
WHEN ACCESSTYPE = 'V ' THEN 'insBuf'
WHEN ACCESSTYPE = ' ' THEN ' '
ELSE '??' || accessType
END AS ACCESS,
CASE WHEN ACCESSTYPE = 'R ' THEN ' '
when PRIMARY_ACCESSTYPE = 'D' THEN ' '
ELSE SUBSTR(ACCESSNAME, 1, 12) END AS "INDEX",
SUBSTR(TNAME, 1, 12) AS "TABLE",
CASE WHEN TABLE_TYPE IS NULL THEN ' '
ELSE TABLE_TYPE END AS TTYP,
CASE WHEN METHOD = 3 THEN ' '
WHEN ACCESSTYPE = 'R ' THEN ' '
ELSE SUBSTR(DIGITS(MATCHCOLS), 5, 1) || ' ' || indexOnly
END AS MC_O,
CASE METHOD WHEN 0 THEN ' '
WHEN 1 THEN 'NLJOIN'
when 2 THEN 'SMJOIN'
WHEN 3 THEN 'SORT '
WHEN 4 THEN 'HYJOIN'
ELSE NULL END AS METHOD,
CASE JOIN_TYPE WHEN 'F' THEN 'full'
WHEN 'L' THEN 'left'
WHEN 'S' THEN 'star'
ELSE ' ' END AS "joiT",
SORTN_UNIQ CONCAT SORTN_JOIN CONCAT SORTN_ORDERBY CONCAT
SORTN_GROUPBY AS UJOG,
SORTC_UNIQ CONCAT SORTC_JOIN CONCAT SORTC_ORDERBY CONCAT
SORTC_GROUPBY AS UJOC,
TSLOCKMODE AS LCK,
SUBSTR(CORRELATION_NAME, 1, 4) AS CORR,
CASE PARALLELISM_MODE WHEN 'C' THEN 'CPU'
WHEN 'I' THEN 'I-O'
WHEN 'X' THEN 'SYSPLEX'
ELSE NULL END AS PARAL,
STRIP(DIGITS(ACCESS_DEGREE), LEADING, '0') || ' '
|| STRIP(DIGITS(JOIN_DEGREE), LEADING, '0') AS AJ_DEG,
STRIP(DIGITS(ACCESS_PGROUP_ID), LEADING, '0')|| ' '
|| STRIP(DIGITS(JOIN_PGROUP_ID), LEADING, '0') PG_DEG,
STRIP(DIGITS(MERGE_JOIN_COLS), LEADING, '0') AS MC,
PREFETCH AS PRE,
page_range as pgRa,
substr(opthint, 1, 10) optHint,
substr(hint_used, 1, 10) hint_used,
-- full length names
TNAME, ACCESSNAME, accessType,
-- package identifikation
collid, progName, applName, version, bind_time,
-- query node identificaten
queryno, qBlockNo, planno, mixopSeq, timestamp,
PARENT_QBLOCKNO
FROM $cr.PLAN_TABLE A
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW2 AS
SELECT CASE WHEN METHOD = 'SORT ' THEN ' '
ELSE substr(right(' '
|| strip(CHAR(S.PROCMS)),9), 1, 9) END AS MSEC,
a.*,
S.COST_CATEGORY,
S.PROCMS, S.PROCSU, S.REASON
FROM $cr.PLAN_VIEW1 A
LEFT OUTER JOIN
$cr.DSN_STATEMNT_TABLE S
ON S."COLLID" = A."COLLID"
AND S.APPLNAME = A.APPLNAME
AND S.PROGNAME = A.PROGNAME
AND S.EXPLAIN_TIME = A.BIND_TIME
AND S.QUERYNO = A.QUERYNO
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW2Det AS
SELECT substr(fosFmtE7(dmRows), 1, 7) dmRows,
substr(fosFmtE7(rdsRow), 1, 7) rdsRow,
substr(fosFmtE7(snRows), 1, 7) snRows,
substr(fosFmtE7(compCost), 1, 7) compCost,
substr(fosFmtE7(openCost), 1, 7) openCost,
a.*
FROM $cr.PLAN_VIEW1 A
LEFT OUTER JOIN
$cr.DSN_DetCost_TABLE d
on --d.APPLNAME = A.APPLNAME ??? ist manchmal x'0000
d.PROGNAME = A.PROGNAME
AND d.EXPLAIN_TIME = A.BIND_TIME
AND d.QueryNO = A.QueryNO
AND d.QBlockNO = A.QBlockNO
AND d.PlanNo = A.PlanNo
;
CREATE VIEW $cr.plan_ViewPred as
select a."Queryn B PM" "Queryn_B_PM"
, smallint(p.PREDNO) predno
, f.stage
-- , substr(fosFmte7(p.filter_factor), 1, 7) ff
-- optimizer otherwise choose bad path|||
, real(p.FILTER_FACTOR) FILTER_FACTOR
, p.type prTy
, p.boolean_term || ' ' || p.negation "BoN"
, p.text
, p.LEFT_HAND_SIDE
, p.LEFT_HAND_PNO
, p.LHS_TABNO
, p.LHS_QBNO
, p.RIGHT_HAND_SIDE
, p.RIGHT_HAND_PNO
, p.RHS_TABNO
, p.RHS_QBNO
, p.BOOLEAN_TERM
, p.SEARCHARG
, p.JOIN
, p.AFTER_JOIN
, p.ADDED_PRED
, p.REDUNDANT_PRED
, p.DIRECT_ACCESS
, p.KEYFIELD
, p.CATEGORY
, p.CATEGORY_B
, p.PRED_ENCODE
, p.PRED_CCSID
, p.PRED_MCCSID
, p.MARKER
, p.PARENT_PNO
, p.NEGATION
, p.LITERALS
, p.CLAUSE
, f.reEval
, f.ORDERNO
, a.*
from $cr.plan_view1 a
left join $cr.dsn_filter_Table f
on f.applName = a.applName
and f.collid = a.collid
and f.progName = a.progName
and f.explain_time = a.bind_time
and f.QUERYNO = a.QUERYNO
and f.QBLOCKNO = a.QBLOCKNO
and f.PLANNO = a.PLANNO
and f.mixOpSeqNo = a.mixOpSeq
and a.accessType not in ('MX', 'MI', 'MU')
left join $cr.dsn_predicat_table p
on --p.applName = a.applName ??? ist manchmal x'0000
p.progName = a.progName
and p.explain_time = a.bind_time
and p.queryNo = a.queryNo
and p.qBlockNo = a.qBlockNo
and p.predNo = f.predNo
;
-- sortierung --------------------------------------
-- order by applName, collid, progName, bind_time,
-- queryNo, qBlockNo, planno, mixOpSeq,
-- stage, predNo
;
------------------------------------------------------------------------
-- use of view1 instead of view2 directly
-- because otherwise optimizer makes TS Scan ||||
CREATE VIEW $cr.PLAN_VIEW5 AS
with l as
( select collid, progName, max(r.bind_time) bind_time
FROM $cr.PLAN_TABLE r
group by collid, progName
)
select CASE WHEN METHOD = 'SORT ' THEN ' '
ELSE substr(right(' '
|| strip(CHAR(S.PROCMS)),9), 1, 9) END AS MSEC
, a.*
, S.COST_CATEGORY,
S.PROCMS, S.PROCSU, S.REASON
FROM l
join $cr.PLAN_VIEW1 a
on A.collid = l.collid
AND A.PROGNAME = l.PROGNAME
AND A.bind_time = l.bind_time
LEFT OUTER JOIN
$cr.DSN_STATEMNT_TABLE S
ON S."COLLID" = A."COLLID"
AND S.APPLNAME = A.APPLNAME
AND S.PROGNAME = A.PROGNAME
AND S.EXPLAIN_TIME = A.BIND_TIME
AND S.QUERYNO = A.QUERYNO
;
commit;
------------ Ende user Views ----- cmnViews = $cmnViews----------------
$/views/
if $cmnViews & $suf == '' then $@=/cmnViews/
------------ diese views braucht es nur für changeman -----------------
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW3
( QN,QB,AP,PG, PN,ME,CR,TN,TO, AT,JT,MC,AN,IO, SP,SU,SJ, SO, SG,ZP,ZU,
ZJ, ZO,ZG,TL,TS,PR,EV, CO,VR,MO, AD,AI, JD,JI, PA,MJ,CN, PF,GM,WO,QT,
BT,RM,SD, OH,HU,PAC )
AS SELECT QUERYNO, QBLOCKNO, APPLNAME, PROGNAME, PLANNO, METHOD,
CREATOR, TNAME, TABNO, ACCESSTYPE, JOIN_TYPE, MATCHCOLS, ACCESSNAME,
INDEXONLY, SORTN_PGROUP_ID, SORTN_UNIQ, SORTN_JOIN, SORTN_ORDERBY,
SORTN_GROUPBY, SORTC_PGROUP_ID, SORTC_UNIQ, SORTC_JOIN, SORTC_ORDERBY,
SORTC_GROUPBY, TSLOCKMODE, TIMESTAMP, PREFETCH, COLUMN_FN_EVAL, COLLID,
VERSION, MIXOPSEQ, ACCESS_DEGREE, ACCESS_PGROUP_ID, JOIN_DEGREE,
JOIN_PGROUP_ID, PARALLELISM_MODE, MERGE_JOIN_COLS, CORRELATION_NAME,
PAGE_RANGE, GROUP_MEMBER, WHEN_OPTIMIZE, QBLOCK_TYPE, BIND_TIME,
REMARKS, IBM_SERVICE_DATA, OPTHINT, HINT_USED, PRIMARY_ACCESSTYPE
FROM $cr.PLAN_TABLE
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW6 AS
SELECT SUBSTR(A.PROGNAME, 1, 8) AS PROGNAME,
SUBSTR(DIGITS(A."QUERYNO"), 5) AS STMT,
CASE WHEN A.METHOD = 3
THEN ' '
-- ELSE SUBSTR(CHAR(S.PROCSU), 1, 7) END AS SUNITS,
ELSE '1 '
END AS SUNITS,
CASE WHEN A.PRIMARY_ACCESSTYPE = 'D' THEN 'DIR.ROW'
WHEN A.ACCESSTYPE = 'I ' THEN 'IX-SCAN'
WHEN A.ACCESSTYPE = 'I1' THEN 'IX-ONEF'
WHEN A.ACCESSTYPE = 'M ' THEN 'MULT-IX'
WHEN A.ACCESSTYPE = 'MX' THEN 'IX-SC.X'
WHEN A.ACCESSTYPE = 'MI' THEN 'IX-SC.I'
WHEN A.ACCESSTYPE = 'MU' THEN 'IX-SC.U'
WHEN A.ACCESSTYPE = 'N ' THEN 'IX-INLI'
WHEN A.ACCESSTYPE = 'R ' THEN 'TS-SCAN'
WHEN A.ACCESSTYPE = 'RW' THEN 'WF-SCAN'
WHEN A.ACCESSTYPE = 'T ' THEN 'IX-SPRS'
WHEN A.ACCESSTYPE = 'V ' THEN 'BUFFERS'
WHEN A.ACCESSTYPE = ' ' THEN ' '
ELSE ' ' END AS ACCESS,
CASE WHEN A.PRIMARY_ACCESSTYPE = 'D' THEN ' '
WHEN A.ACCESSTYPE = 'R ' THEN ' '
ELSE SUBSTR(A.ACCESSNAME, 1, 12) END AS "INDEX",
CASE WHEN A.TNAME = ' ' THEN ' '
ELSE SUBSTR(A.TNAME, 1, 12) END AS "TABLE",
SUBSTR(A.CORRELATION_NAME, 1, 5) AS CORR,
CASE WHEN A.METHOD = 3 THEN ' '
WHEN A.ACCESSTYPE = 'R ' THEN ' '
WHEN A.QBLOCK_TYPE = 'INSERT' THEN ' '
ELSE SUBSTR(DIGITS(A.MATCHCOLS), 5, 1) END AS MC,
CASE WHEN A.INDEXONLY = 'Y' THEN 'XO'
ELSE ' ' END AS XO,
CASE A.METHOD
WHEN 0 THEN '0 '
WHEN 1 THEN 'NLJOIN'
WHEN 2 THEN 'SMJOIN'
WHEN 3 THEN 'SORT '
WHEN 4 THEN 'HYJOIN'
ELSE CHAR(A.METHOD) END AS METHOD,
CASE A.JOIN_TYPE
WHEN 'F' THEN 'FULL '
WHEN 'L' THEN 'LEFT '
WHEN 'S' THEN 'STAR '
ELSE ' ' END AS "JOIN",
A.SORTN_UNIQ CONCAT A.SORTN_JOIN CONCAT A.SORTN_ORDERBY
CONCAT A.SORTN_GROUPBY AS UJOG,
A.SORTC_UNIQ CONCAT A.SORTC_JOIN CONCAT A.SORTC_ORDERBY
CONCAT A.SORTC_GROUPBY AS UJOC,
A.QBLOCK_TYPE AS QBTYPE,
CASE WHEN A.TABLE_TYPE IS NULL THEN ' '
WHEN A.TABLE_TYPE = 'B' THEN 'BUFFER'
WHEN A.TABLE_TYPE = 'C' THEN 'CTE '
WHEN A.TABLE_TYPE = 'F' THEN 'TBLFNC'
WHEN A.TABLE_TYPE = 'M' THEN 'MQT '
WHEN A.TABLE_TYPE = 'Q' THEN 'VMQT '
WHEN A.TABLE_TYPE = 'R' THEN 'RC#CTE'
WHEN A.TABLE_TYPE = 'T' THEN 'TABLE/'
WHEN A.TABLE_TYPE = 'W' THEN 'WRKFIL'
ELSE A.TABLE_TYPE END AS TTYP,
A.TSLOCKMODE AS LCK,
CASE A.PARALLELISM_MODE
WHEN 'C' THEN 'CPU '
WHEN 'I' THEN 'I-O '
WHEN 'X' THEN 'PLEX'
ELSE ' ' END AS PARAL,
STRIP(DIGITS(A.ACCESS_DEGREE), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(A.JOIN_DEGREE), LEADING, '0')
AS AJ_DEG,
STRIP(DIGITS(A.ACCESS_PGROUP_ID), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(A.JOIN_PGROUP_ID), LEADING, '0')
AS PG_DEG,
STRIP(DIGITS(A.MERGE_JOIN_COLS), LEADING, '0') AS MJC,
CASE A.PREFETCH
WHEN 'S' THEN 'SEQ '
WHEN 'L' THEN 'LIST'
WHEN 'D' THEN 'DYN '
ELSE ' ' END AS PREFETCH,
STRIP(DIGITS(A."QUERYNO"), LEADING, '0') AS QNO,
SUBSTR(DIGITS(A.QBLOCKNO), 4, 2) CONCAT ' '
CONCAT SUBSTR(DIGITS(A.PLANNO), 4, 2)
AS BL_PL,
A.PARENT_QBLOCKNO,
A.QBLOCKNO, A.PLANNO, A.TNAME, A.ACCESSNAME, A.OPTHINT,
A.HINT_USED, A.APPLNAME, A."COLLID", A.VERSION,
A.TIMESTAMP, A.BIND_TIME, A."QUERYNO", A.MIXOPSEQ, A.TABNO,
A.CORRELATION_NAME, A.COLUMN_FN_EVAL, A.SORTC_PGROUP_ID,
A.SORTN_PGROUP_ID, A.PAGE_RANGE, A.WHEN_OPTIMIZE,
A.TABLE_ENCODE, A.TABLE_SCCSID, A.ROUTINE_ID, A.CTEREF,
A.STMTTOKEN,
-- S.COST_CATEGORY, S.PROCMS, S.PROCSU, S.REASON
'A' AS COST_CATEGORY, 1 AS PROCMS, 1 AS PROCSU, ' ' AS REASON
FROM $cr.PLAN_TABLE A
-- JOIN
-- (SELECT B.PROGNAME AS BPROGNAME,
-- B.COLLID AS BCOLLID,
-- MAX(B.BIND_TIME) BBIND_TIME
-- FROM $cr.PLAN_TABLE B
-- GROUP BY B.PROGNAME, B.COLLID) AS N1
-- ON A.PROGNAME = N1.BPROGNAME
-- AND A.BIND_TIME = N1.BBIND_TIME
-- AND A.COLLID = N1.BCOLLID
-- LEFT OUTER JOIN $cr.DSN_STATEMNT_TABLE S
-- ON S."COLLID" = A."COLLID"
-- AND S.APPLNAME = A.APPLNAME
-- AND S.PROGNAME = A.PROGNAME
-- AND S."QUERYNO" = A."QUERYNO"
-- AND S.EXPLAIN_TIME = A.BIND_TIME
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW6_CMN AS
SELECT SUBSTR(A.PROGNAME, 1, 8) AS PROGNAME,
SUBSTR(DIGITS(A."QUERYNO"), 5) AS STMT,
CASE WHEN A.METHOD = 3
THEN ' '
-- ELSE SUBSTR(CHAR(S.PROCSU), 1, 7) END AS SUNITS,
ELSE '1 '
END AS SUNITS,
CASE WHEN A.PRIMARY_ACCESSTYPE = 'D' THEN 'DIR.ROW'
WHEN A.ACCESSTYPE = 'I ' THEN 'IX-SCAN'
WHEN A.ACCESSTYPE = 'I1' THEN 'IX-ONEF'
WHEN A.ACCESSTYPE = 'M ' THEN 'MULT-IX'
WHEN A.ACCESSTYPE = 'MX' THEN 'IX-SC.X'
WHEN A.ACCESSTYPE = 'MI' THEN 'IX-SC.I'
WHEN A.ACCESSTYPE = 'MU' THEN 'IX-SC.U'
WHEN A.ACCESSTYPE = 'N ' THEN 'IX-INLI'
WHEN A.ACCESSTYPE = 'R ' THEN 'TS-SCAN'
WHEN A.ACCESSTYPE = 'RW' THEN 'WF-SCAN'
WHEN A.ACCESSTYPE = 'T ' THEN 'IX-SPRS'
WHEN A.ACCESSTYPE = 'V ' THEN 'BUFFERS'
WHEN A.ACCESSTYPE = ' ' THEN ' '
ELSE ' ' END AS ACCESS,
CASE WHEN A.PRIMARY_ACCESSTYPE = 'D' THEN ' '
WHEN A.ACCESSTYPE = 'R ' THEN ' '
ELSE SUBSTR(A.ACCESSNAME, 1, 12) END AS "INDEX",
CASE WHEN A.TNAME = ' ' THEN ' '
ELSE SUBSTR(A.TNAME, 1, 12) END AS "TABLE",
SUBSTR(A.CORRELATION_NAME, 1, 5) AS CORR,
CASE WHEN A.METHOD = 3 THEN ' '
WHEN A.ACCESSTYPE = 'R ' THEN ' '
WHEN A.QBLOCK_TYPE = 'INSERT' THEN ' '
ELSE SUBSTR(DIGITS(A.MATCHCOLS), 5, 1) END AS MC,
CASE WHEN A.INDEXONLY = 'Y' THEN 'XO'
ELSE ' ' END AS XO,
CASE A.METHOD
WHEN 0 THEN '0 '
WHEN 1 THEN 'NLJOIN'
WHEN 2 THEN 'SMJOIN'
WHEN 3 THEN 'SORT '
WHEN 4 THEN 'HYJOIN'
ELSE CHAR(A.METHOD) END AS METHOD,
CASE A.JOIN_TYPE
WHEN 'F' THEN 'FULL '
WHEN 'L' THEN 'LEFT '
WHEN 'S' THEN 'STAR '
ELSE ' ' END AS "JOIN",
A.SORTN_UNIQ CONCAT A.SORTN_JOIN CONCAT A.SORTN_ORDERBY
CONCAT A.SORTN_GROUPBY AS UJOG,
A.SORTC_UNIQ CONCAT A.SORTC_JOIN CONCAT A.SORTC_ORDERBY
CONCAT A.SORTC_GROUPBY AS UJOC,
A.QBLOCK_TYPE AS QBTYPE,
CASE WHEN A.TABLE_TYPE IS NULL THEN ' '
WHEN A.TABLE_TYPE = 'B' THEN 'BUFFER'
WHEN A.TABLE_TYPE = 'C' THEN 'CTE '
WHEN A.TABLE_TYPE = 'F' THEN 'TBLFNC'
WHEN A.TABLE_TYPE = 'M' THEN 'MQT '
WHEN A.TABLE_TYPE = 'Q' THEN 'VMQT '
WHEN A.TABLE_TYPE = 'R' THEN 'RC#CTE'
WHEN A.TABLE_TYPE = 'T' THEN 'TABLE/'
WHEN A.TABLE_TYPE = 'W' THEN 'WRKFIL'
ELSE A.TABLE_TYPE END AS TTYP,
A.TSLOCKMODE AS LCK,
CASE A.PARALLELISM_MODE
WHEN 'C' THEN 'CPU '
WHEN 'I' THEN 'I-O '
WHEN 'X' THEN 'PLEX'
ELSE ' ' END AS PARAL,
STRIP(DIGITS(A.ACCESS_DEGREE), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(A.JOIN_DEGREE), LEADING, '0')
AS AJ_DEG,
STRIP(DIGITS(A.ACCESS_PGROUP_ID), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(A.JOIN_PGROUP_ID), LEADING, '0')
AS PG_DEG,
STRIP(DIGITS(A.MERGE_JOIN_COLS), LEADING, '0') AS MJC,
CASE A.PREFETCH
WHEN 'S' THEN 'SEQ '
WHEN 'L' THEN 'LIST'
WHEN 'D' THEN 'DYN '
ELSE ' ' END AS PREFETCH,
STRIP(DIGITS(A."QUERYNO"), LEADING, '0') AS QNO,
SUBSTR(DIGITS(A.QBLOCKNO), 4, 2) CONCAT ' '
CONCAT SUBSTR(DIGITS(A.PLANNO), 4, 2)
AS BL_PL,
A.PARENT_QBLOCKNO,
A.QBLOCKNO, A.PLANNO, A.TNAME, A.ACCESSNAME, A.OPTHINT,
A.HINT_USED, A.APPLNAME, A."COLLID", A.VERSION,
A.TIMESTAMP, A.BIND_TIME, A."QUERYNO", A.MIXOPSEQ, A.TABNO,
A.CORRELATION_NAME, A.COLUMN_FN_EVAL, A.SORTC_PGROUP_ID,
A.SORTN_PGROUP_ID, A.PAGE_RANGE, A.WHEN_OPTIMIZE,
A.TABLE_ENCODE, A.TABLE_SCCSID, A.ROUTINE_ID, A.CTEREF,
A.STMTTOKEN,
-- S.COST_CATEGORY, S.PROCMS, S.PROCSU, S.REASON
'A' AS COST_CATEGORY, 1 AS PROCMS, 1 AS PROCSU, ' ' AS REASON
FROM $cr.PLAN_TABLE A
-- JOIN
-- (SELECT B.PROGNAME AS BPROGNAME,
-- B.COLLID AS BCOLLID,
-- MAX(B.BIND_TIME) BBIND_TIME
-- FROM $cr.PLAN_TABLE B
-- GROUP BY B.PROGNAME, B.COLLID) AS N1
-- ON A.PROGNAME = N1.BPROGNAME
-- AND A.BIND_TIME = N1.BBIND_TIME
-- AND A.COLLID = N1.BCOLLID
-- LEFT OUTER JOIN $cr.DSN_STATEMNT_TABLE S
-- ON S."COLLID" = A."COLLID"
-- AND S.APPLNAME = A.APPLNAME
-- AND S.PROGNAME = A.PROGNAME
-- AND S."QUERYNO" = A."QUERYNO"
-- AND S.EXPLAIN_TIME = A.BIND_TIME
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW7 AS
SELECT (
SUBSTR(DIGITS(A."QUERYNO"),5) || ' ' || -- STMT
QBLOCK_TYPE || ' ' || -- TYPE
CASE WHEN METHOD = 3 THEN ' '
ELSE CHAR(S.PROCMS) END || -- MSEC
CASE WHEN PRIMARY_ACCESSTYPE = 'D' THEN 'DIR.ROW'
WHEN ACCESSTYPE = 'I ' THEN 'IX-SCAN'
WHEN ACCESSTYPE = 'I1' THEN 'IX-ONE '
WHEN ACCESSTYPE = 'R ' THEN 'TS-SCAN'
WHEN ACCESSTYPE = 'N ' THEN 'IX-ACC '
WHEN ACCESSTYPE = 'M ' THEN 'IX-SC.M'
WHEN ACCESSTYPE = 'MX' THEN 'IX-SC.X'
WHEN ACCESSTYPE = 'MI' THEN 'IX-SC.I'
WHEN ACCESSTYPE = 'MU' THEN 'IX-SC.U'
WHEN ACCESSTYPE = 'T' THEN 'IX-SPRS'
WHEN ACCESSTYPE = ' ' THEN ' '
ELSE NULL END || ' ' || -- ACCESS
CASE WHEN ACCESSTYPE = 'R' THEN ' '
WHEN PRIMARY_ACCESSTYPE = 'D' THEN ' '
ELSE SUBSTR(ACCESSNAME,1,12) END || ' ' ||
-- "INDEX"
SUBSTR(TNAME,1,18) || ' ' || -- TABLE
CASE WHEN TABLE_TYPE IS NULL THEN ' '
ELSE TABLE_TYPE END || ' ' || -- TTYP
CASE WHEN METHOD = 3 THEN ' '
WHEN ACCESSTYPE = 'R ' THEN ' '
ELSE SUBSTR(DIGITS(MATCHCOLS), 4, 2)
END || ' ' || -- MC_O
CASE WHEN INDEXONLY='Y' THEN 'Y '
ELSE ' '
END || ' ' || -- IXO
CASE WHEN OPTHINT<>' ' THEN '*'
ELSE ' '
END || -- HINT
CASE WHEN HINT_USED<>' ' THEN '*'
ELSE ' '
END -- HINT_USED
) AS TEXT,
CASE METHOD
WHEN 0 THEN ' '
WHEN 1 THEN 'NLJOIN'
WHEN 2 THEN 'SMJOIN'
WHEN 3 THEN 'SORT '
WHEN 4 THEN 'HYJOIN'
ELSE NULL
END AS METHOD,
CASE JOIN_TYPE WHEN 'F' THEN 'FULL ' WHEN 'L' THEN 'LEFT '
WHEN 'S' THEN 'STAR ' ELSE ' ' END AS "JOIN",
SORTN_UNIQ CONCAT SORTN_JOIN CONCAT SORTN_ORDERBY CONCAT
SORTN_GROUPBY AS UJOG,
SORTC_UNIQ CONCAT SORTC_JOIN CONCAT SORTC_ORDERBY CONCAT
SORTC_GROUPBY AS UJOC, TSLOCKMODE AS LCK,
SUBSTR(CORRELATION_NAME, 1, 4) AS CORR,
CASE PARALLELISM_MODE
WHEN 'C' THEN 'CPU'
WHEN 'I' THEN 'I-O'
WHEN 'X' THEN 'SYSPLEX'
ELSE NULL
END AS PARAL,
STRIP(DIGITS(ACCESS_DEGREE), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(JOIN_DEGREE), LEADING, '0') AS AJ_DEG,
STRIP(DIGITS(ACCESS_PGROUP_ID), LEADING, '0') CONCAT ' '
CONCAT STRIP(DIGITS(JOIN_PGROUP_ID), LEADING, '0') AS
PG_DEG,
STRIP(DIGITS(MERGE_JOIN_COLS), LEADING, '0') AS MC,
PREFETCH AS PRE,
STRIP(DIGITS(A."QUERYNO"), LEADING, '0') AS QNO,
SUBSTR(DIGITS(QBLOCKNO), 4, 2) CONCAT ' ' CONCAT SUBSTR(
DIGITS(PLANNO), 4, 2) AS BL_PL,
A.PARENT_QBLOCKNO,
A.QBLOCKNO,
A.PLANNO,
A.TNAME,
A.ACCESSNAME,
A.OPTHINT,
A.HINT_USED,
A.APPLNAME,
A."COLLID",
A.PROGNAME,
A.VERSION,
A.BIND_TIME,
A.TIMESTAMP,
A."QUERYNO",
A.MIXOPSEQ,
S.COST_CATEGORY,
S.PROCMS,
S.PROCSU,
S.REASON
FROM $cr.PLAN_TABLE A
JOIN $cr.DSN_STATEMNT_TABLE S
ON S."COLLID" = A."COLLID"
AND S.APPLNAME = A.APPLNAME
AND S.PROGNAME = A.PROGNAME
AND S."QUERYNO" = A."QUERYNO"
AND S.EXPLAIN_TIME = A.BIND_TIME
;
------------------------------------------------------------------------
CREATE VIEW $cr.PLAN_VIEW9 AS
SELECT A.PROGNAME, A.VERSION, S.PROCMS
FROM $cr.PLAN_TABLE A
LEFT OUTER JOIN $cr.DSN_STATEMNT_TABLE S
ON S."COLLID" = A."COLLID"
AND S.APPLNAME = A.APPLNAME
AND S.PROGNAME = A.PROGNAME
AND S.QUERYNO = A.QUERYNO
AND S.EXPLAIN_TIME = A.BIND_TIME
;
------------------------------------------------------------------------
commit
;
$/cmnViews/
if $doReo then $@=/reo/
// IF DDL.RC LE 4 THEN
//REO EXEC PGM=DSNUTILB,TIME=1440,
// PARM=($subsys,'$job.REORG'),
// REGION=0M
//DSSPRINT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DISP=SHR,DSN=$subsys.DBAA.LISTDEF(TEMPL)
//SYSIN DD *
LISTDEF LSTBASE INCLUDE TABLESPACE $db.$ts* BASE
REORG TABLESPACE LIST LSTBASE
LOG NO
SORTDATA
COPYDDN(TCOPYD)
SHRLEVEL CHANGE
MAPPINGTABLE S100447.MAPTAB03
MAXRO 300
DRAIN ALL
DELAY 1500
TIMEOUT TERM
UNLDDN TSRECD
UNLOAD CONTINUE
PUNCHDDN TPUNCH
DISCARDDN TDISCA
SORTKEYS
SORTDEVT DISK
STATISTICS
INDEX ALL KEYCARD
REPORT NO UPDATE ALL
LISTDEF LSTLOB INCLUDE TABLESPACE $db.$ts* LOB
REORG TABLESPACE LIST LSTLOB
SHRLEVEL REFERENCE
COPYDDN(TCOPYD)
UNLOAD CONTINUE
// ENDIF
$/reo/
$/createOrUpdate/
$/forCreator/
$/fEdit/
$;
$@proc tb $@[
parse arg ., var tb
if $isCre then
$=$-{var}=- ''
else
$=$-{var}=- catTbLastCol($cr, tb)
$]
$@proc tbCol $@[
parse arg ., var tb col
call sqlPreAllCl 1, "select count(*) from sysibm.sysColumns" ,
"where tbCreator = '"$cr"' and tbName = '"tb"'" ,
"and name = '"col"'", st, ':cn'
$=$-{var}=- cn
$]
$@proc ix $@[
parse arg ., vKy vDr ix
if $isCre then
$=$-{vKy}=- ''
else
$=$-{vKy}=- catIxKeys($cr, ix)
if ${$-{vKy}} == '' then
$=$-{vDr}=- ''
else
$=$-{vDr}=- 'drop index' $cr'.'ix';'
$]
$@proc ixAtt $@=[
USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
$@[ if pos('l', arg(2)) < 1 then $@=[
NOT PADDED
$-{if(pos('c', arg(2)) < 1, NOT) CLUSTER}
DEFER $defer
$] $]
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
$]
$@proc dropViews $@/dropViews/
$<=[ select (select strip(d.bcreator) || '.' || strip(d.bname)
from sysibm.sysViewDep d
where bType = 'V'
and d.dcreator = v.creator and dName = v.name
and d.bcreator = v.creator and bName like 'PLAN%'
fetch first row only
) dep, v.creator cr, v.name na
from sysibm.sysTables v
where type = 'V' and name like 'PLAN%'
and creator = '$cr'
$]
call sqlSel
$| $@forWith v $@[
if $DEP == m.sqlNull then
$$ drop view $CR.$NA;
else
$$ $' --' view $CR.$NA depends on $DEP
$]
$/dropViews/
$@proc dropTS $@/dropTS/
$<=[ WITH pt AS
( select *
from sysibm.sysTables o
where type in ('T', 'X') and creator = '$cr'
and name in ('COST_TABLE'
,'DSN_COLDIST_TABLE'
,'DSN_DETCOST_TABLE'
,'DSN_FILTER_TABLE'
,'DSN_FUNCTION_TABLE'
,'DSN_KEYTGTDIST_TABLE'
,'DSN_PGRANGE_TABLE'
,'DSN_PGROUP_TABLE'
,'DSN_PREDICAT_TABLE'
,'DSN_PTASK_TABLE'
,'DSN_QUERY_AUX'
,'DSN_QUERY_TABLE'
,'DSN_SORTKEY_TABLE'
,'DSN_SORT_TABLE'
,'DSN_STATEMENT_CACHE_AUX'
,'DSN_STATEMENT_CACHE_TABLE'
,'DSN_STATEMNT_TABLE'
,'DSN_STRUCT_TABLE'
,'DSN_VIEWREF_TABLE'
,'DSN_VIRTUAL_INDEXES'
,'EEEAUTH'
,'EEEDBRM'
,'EEEHINT'
,'EEEPATH'
,'EEEPLAN'
,'EEEWORK'
,'OBJECT_DATA'
,'OBJECT_DIRECTORY'
,'F2PLAN_TABLE'
,'PLAN_TABLE'
,'PREDICATE_TABLE'
,'STRUCTURE_TABLE'
)
)
, ts as
( select dbName db, name ts, type tsTy, nTables nTb
from sysibm.sysTableSpace s
where (dbName = '$db' and name like '$ts%')
union select dbName db, name ts, type tsTy, nTables nTb
from sysibm.sysTableSpace s
where (dbName, name) in (select pt.dbName, pt.tsName from pt)
)
select ts.*
, (select strip(o.creator) || '.' || strip(o.name)
from sysibm.sysTables o
where o.dbName = ts.db and o.tsName = ts.ts
and o.type not in ('V', 'A')
and not exists (select 1
from pt
where pt.creator = o.creator and pt.name = o.name)
fetch first 1 row only
) oth
from ts
order by translate(tsTy, 'ZZ', 'OP') || ts
$]
call sqlSel
$| $@forWith v $@[
if $OTH == m.sqlNull then
$$ drop tablespace $DB.$TS; -- type $TSTY, $NTB tables
else
say err 'cannot drop ts' $DB'.'$TS 'contains tb' $OTH
$]
$;
$$ COMMIT;
$/dropTS/
$#out 20120329 14:00:54
$#out 20120329 12:06:08
$#out 20120329 12:04:39
*** run error ***
cannot drop ts IDTANL00.ANLSPACE contains tb ANLUSER1.REGISTRY
$#out 20120329 12:02:59
$#out 20120329 12:02:02