For mange år siden blev der indført en BIND option i DB2 kaldet REOPT(VARS), som senere blev omdøbt til REOPT(ALWAYS). Funktionen er, at for SQL med hostvariable, så vil DB2 optimizeren tjekke værdien af hostvariablene på eksekveringstidspunktet og eventuelt vælge en bedre accessvej end den, der blev valgt på BIND tidspunktet.

DBA'er har lige siden hadet denne option, da den jo gjaldt alle statements i programmet og ikke bare dem, der kunne have fordel af REOPT(ALWAYS). Forestil dig et program med et super enkelt og effektivt SQL statement, der udføres millioner af gange i løbet af en dag, og i samme program en såkaldt "query from hell" med BETWEEN og LIKE :hostvariable, som typisk kodes til at lave diverse generelle søgninger med, som kun udføres nogle få gange om dage. Hvis REOPT(ALWAYS) slås til i sådan et program, så vil DB2 evaulere begge statements, og det, der udføres millionvis af gange vil blive ekstremt tungt på grund af denne evaluering, mens det sjældent brugte måske vil drage fordel af REOPT(ALWAYS), men det vil ikke opveje tabet på det ofte brugte statement.

IBM har selvfølgelig lyttet til kritikken og i DB2 version 10 kom der funktionalitet til at definere REOPT(ALWAYS) på statement niveau. I mellemtiden har DBA'erne tillært sig en evne til bare at afvise REOPT(ALWAYS), selv på trods af denne nye mulighed, typisk fordi den ikke lige er blevt opdaget. Desværre er det ret besværligt at benytte REOPT(ALWAYS) på statement niveau, men jeg håber, at dette tip kan hjælpe dig på vej.

Første skridt er at oprette en tabel kaldet DSN_USERQUERY_TABLE. Der er garanteret en tabel på dit DB2 system, der allerede hedder dette, så den kan du benytte til at oprette din egen kopi. Næste skridt er at oprette en PLAN_TABLE, men den har du sandsynligvis allerede. Og så skal du lige oprette et indeks på PLAN_TABLE med følgende kolonner i den angivne rækkefølge: QUERYNO, APPLNAME, PROGNAME, VERSION, COLLID, OPTHINT. Disse indledende knæbøjninger skal du heldigvis kun foretage een gang.

Så er du klar til at aktivere REOPT(ALWAYS) på din udvalgte "query from hell". Vælg dig et QUERYNO, som ikke findes i din PLAN_TABLE. Jeg har valgt 117. Så skal du inserte en række i DSN_USERQUERY_TABLE med dette QUERYNO (det må heller ikke findes her). Antag, at dit SQL statement findes i program HELLPGM i collection HELLCOLL i section 10 (Query Monitor eller CA Detector kan fortælle dig, hvilken section dit statement findes i). Så kan du benytte følgende INSERT:

INSERT INTO DSN_USERQUERY_TABLE
( QUERYNO, SCHEMA, HINT_SCOPE, COLLECTION, PACKAGE, VERSION, REOPT
, SELECTVTY_OVERRIDE, ACCESSPATH_HINT, OPTION_OVERRIDE, QUERY_TEXT
)
SELECT 117, P.QUALIFIER, 1, P.COLLID, P.NAME, '*', 'Y', 'N', 'N', 'Y'
     , S.STATEMENT
FROM SYSIBM.SYSPACKSTMT S
   , SYSIBM.SYSPACKAGE P
WHERE P.LOCATION = ''
  AND P.COLLID = 'HELLCOLL'
  AND P.NAME = 'HELLPGM'
  AND P.LOCATION = S.LOCATION
  AND P.COLLID = S.COLLID
  AND P.NAME = S.NAME
  AND P.CONTOKEN = S.CONTOKEN
  AND S.SECTNO = 10
  AND S.EXPLAINABLE = 'Y'
  AND P.LASTUSED >= CURRENT DATE

Hvis dit program ikke kører hver dag, der hvor du vil lave REOPT(ALWAYS) på statementet, så vil ovenstående SQL ikke virke, da det med AND P.LASTUSED >= CURRENT DATE kun udvælger programmet, hvis det har været eksekveret dags dato. Betingelsen sikrer dig mod at få kopieret en masse rækker fra gamle versioner af programmet, der ikke bruges mere. Bemærk kolonnen VERSION sættes til '*', hvilket betyder, at alle versioner af dit program med præcis det samme statement som i QUERY_TEXT vil kunne benytte REOPT(ALWAYS). DSN_USERQUERY_TABLE kan benyttes til mange andre spændende ting, men for at aktivere REOPT(ALWAYS) skal kolonnen REOPT sættes til 'Y'.

Herefter skal du udføre en såkaldt BIND QUERY. Denne simple DB2-kommando vil tage indholdet af din DSN_USERQUERY_TABLE og putte det ind i to nye SYSIBM-tabeller kaldet SYSIBM.SYSQUERY og SYSIBM.SYSQUERYOPTS. Først nu ved DB2 noget om dine hensigter med at lave REOPT(ALWAYS) på et enkelt SQL statement i dit program.

Sidste trin er at foretage en REBIND af dit program. Desværre vil det ikke fremgå af outputtet fra REBIND kommandoen, om DB2 har taget din REOPT(ALWAYS) til sig eller ej. Det kan du til gængæld kontrollere med følgende SQL:

SELECT COLLID, NAME, VERSION, STATUS, STATEMENT
FROM SYSIBM.SYSPACKSTMT
WHERE STATUS = 'G'
  AND NAME = 'HELLPGM'
  AND COLLID = 'HELLCOLL'
  AND LOCATION = ''

Resultatet skal helst være en enkelt række, hvor kolonnen STATEMENT indeholder det SQL, du vil have udført REOPT(ALWAYS) på. Nu ved du, at din REBIND var en succes.

Vi har fire kendte "queries from hell", hvor vi har brugt ovenstående fremgangsmåde. Det har virket fantastisk på tre af dem, hvor svartider og CPU forbrug er faldet en faktor ti. Desværre var det virkningsløst på det fjerde og værste af dem, så håb på mirakler, men forvent dem ikke. En af udfordringerne ved at benytte REOPT(ALWAYS) er, at du ikke aner, hvilke forskellige accessveje DB2 vælger. Så hvis REOPT(ALWAYS) ikke virker eller måske forværrer situationen, så kan du ikke se nogen steder hvorfor.

Dette tip går ikke ret meget i detaljer, da det er et omfangsrigt emne, men du kan få dine lyster styret i præsentationen DB2 10 for z/OS optimization hints.