Dia 1

1
(No Transcript)
2
DB2 - tietokannan hyödyntäminen SASin
välineilläSAS Technical Club 28.1.2010
Henrik LähdeniemiArto Valtanen
3
DB2 - tietokannan hyödyntäminen SASin välineillä

• OP-Pohjola-ryhmä
• DB2 käyttö Tietopalveluympäristössä
• Tekninen ympäristö
• Volyymit
• Rajapinnat
• Tietoturva
• Table Server
• DB2 käyttö OPDWssä
• OPK Tietopalveluarkkitehtuuri / DWH sovellus /
  OPDW tietovarasto
• OPDW tietovarasto, tietolähteet/liittymät ja
  tietomalli
• OPDWn ETL ratkaisu(SAS), tietokanta(DB2) ja OUT
  rajapinnat (SAS ja DB2)
• OPDW ETL - DB2paivi makro
• OPDW OUT - db2sqlpassthroughextract makro
• Tietovaraston kantojen virheet ja häiriötilanteet
• Eri hyödyntämistavat tietovaraston DB2 ja SAS
  tauluille/näkymille

4
OP-Pohjola-ryhmä on johtava finanssiryhmä
Suomessa

• Yli 4,1 miljoonaa asiakasta Suomessa
• pankkiasiakkaita noin 3,3 miljoonaa
• Pohjolan vakuutusasiakkaita noin 1,8 miljoonaa
• pankki- ja vakuutustoiminnan yhteisten
  asiakkaiden määrä kasvussa nyt yli miljoona
• Baltiassa noin 200 000 asiakasta
• 1,3 miljoonaa omistajajäsentä
• 12 750 pankki- ja vakuutusalan ammattilaista
• maan kattavin palveluverkosto

5
OP-Pohjola-ryhmän rakenne
Omistajajäsenet
221 jäsenosuuspankkia
OP-KESKUS OSK
Ryhmän ulkopuolisia osakkeen- omistajia
POHJOLA PANKKI OYJ
OP-Rahastoyhtiö Oy
OP-Henkivakuutus Oy
Pankkitoiminta
Helsingin OP Pankki Oyj
Muut tytäryhtiöt
Varainhoito
Vahinkovakuutus
OP-KESKUS OSKN TYTÄRYHTIÖT
6
Henkilöstö

• Henkilöstöä n. 12 750
• osuuspankeissa n. 7 000 henkilöä
• OP-Keskus -konsernissa n. 5 700 henkilöä, joista
  Pohjola-konsernissa noin 3 000
• Keski-ikä noin 44 vuotta
• Naisia 75 , miehiä 25
• Käytössä lyhyen ja pitkän ajan kannustejärjestelmä
  t
• OP-Pohjola-ryhmän henkilöstöstä noin 94 kuuluu
  OP-Pohjola-ryhmän
• henkilöstörahastoon. Rahasto aloitti
  toimintansa 1.1.2005.
• OP-Keskus valittu Suomen toiseksi
  parhaaksityöpaikaksi

) Great Place to Work Institute 2009
7
OP-Keskus osk (OPK)

• OP-Pohjola-ryhmän kehittämis- ja palvelukeskus
• valmistelee OP-Pohjola-ryhmän yhteiset
  toimintalinjat.
• edistää ryhmän yhteistoimintaa.
• huolehtii ryhmän edunvalvonnasta.
• valvoo osuuspankkien riskienhallintaa,
  vakavaraisuutta ja maksuvalmiutta.
• tuottaa osuuspankeille keskitettyjä palveluita.
• Ydinpalvelualueet
• Tuote- ja palvelukehitys
• Palveluiden tuottaminen
• ICT-palvelut
• Ryhmäohjaus
• Strateginen omistusyhteisö
• OP-Keskuksen tytäryhtiöt tukevat ja täydentävät
  osuuspankkien toimintaa.

• Keskeiset tytäryhtiöt
• Pohjola Pankki Oyj
• Helsingin OP Pankki Oyj
• OP-Henkivakuutus Oy
• OP-Rahastoyhtiö Oy
• OP-Asuntoluottopankki Oyj
• OP-Kotipankki Oyj

8
DB2 - tietokannan hyödyntäminen SASin välineillä

• OP-Pohjola-ryhmä
• DB2 käyttö Tietopalveluympäristössä
• Tekninen ympäristö
• Volyymit
• Rajapinnat
• Tietoturva
• Table Server
• DB2 käyttö OPDWssä
• OPK Tietopalveluarkkitehtuuri / DWH sovellus /
  OPDW tietovarasto
• OPDW tietovarasto, tietolähteet/liittymät ja
  tietomalli
• OPDWn ETL ratkaisu(SAS), tietokanta(DB2) ja OUT
  rajapinnat (SAS ja DB2)
• OPDW ETL - DB2paivi makro
• OPDW OUT - db2sqlpassthroughextract makro
• Tietovaraston kantojen virheet ja häiriötilanteet
• Eri hyödyntämistavat tietovaraston DB2 ja SAS
  tauluille/näkymille

9
Tietopalveluympäristö

• Alustat
• HP Superdome 64 Itanium
• IBM z9
• käyttöjärjestelmät
• HP-UX 11.31
• z/OS 1.9
• Tietokannat
• z/OS DB2 9.5
• UDB DB2 9.5
• SAS 9.1.3 / DB2sta hyödyntävät SAS clientit
• DI-Studio 3.4
• EG 4.1
• Base SAS
• Management Console

10
Tietopalveluympäristö
Batch
TCP/IP
IMS
DB2 connect
DB2
Tipa
Pohjola IBM Z/OS
SAS Applications
TCP/IP
SAS data
Batch
DB2 connect
Metadata
CICS
Control-M
DB2
Patrol
OP IBM Z/OS
DB2
HPCenter -server NFS
TCP/IP
TCP/IP FTP
Archive SAS table
11
Volyymit

• Levytilaa SAS käytössä n. 7.2 Tb
• UDB DB2ssa 1,4Tb
• UDB DB2 tauluja / näkemyksiä n. 700 kpl.
• SAS tauluja, näkemyksiä indeksejä yhteensä
  2,18 milj.
• Päivittäisiä SAS eräajoja n. 1100 kpl.
• SAS sovelluksia yht. 62 kpl.
• Sovelluskoodeja n. 12000 kpl.

12
Rajapinnat

• SAS/ACCESS Interface to DB2
• toteuttaa nopean ja näkymättömän rajapinnan
  DB2seen
• hoitaa taustalla DB2ssa kaiken toiminnan,
  tulkitsee käskyt tietokantaan käyttäjän näkemättä
• vähentää koodausta, eikä käyttäjän tarvitse
  tietää DB2sta välttämättä mitään, poislukien
  uusien DB2 taulujen luonnit
• libnamella (DB2) käyttöön kuin mikä tahansa SAS
  kirjasto
• LIBNAME dwidb2 DB2 DatasrcDWIUDB
  SCHEMA"dwiudb_schema." USER"dwiudb_user."
  PASSWORD"dwiudb_pw."
• Voidaan päivittää ja lukea dataa kuin mitä
  tahansa taulua ja DB2 hoitaa eheyden ja taulujen
  lukituksen samalla
• kyselyn tulos voidaan suoraan kirjoittaa SAS
  tauluun tai näkymään
• rajapinta käytettävissä eräajoissa ja
  käyttöliittymissä

13
Rajapinnat

• Datan suuruuden mukaan muutetaan taulujen
  latauksissa dbcommit optiotiota vaikka SAS
  makroilla
• DBCOMMIT1000 (insert) 0 (update) 10000 (bulk
  load)
• Hakujen, kyselyiden tai taulujen yhdistelyn
  tehokkuuden lisäämiseksi kannattaa käyttää
  tietokanta apufunktiota
• DBKEY primary key(t) tietokantataulussa
• DBINDEX parametrinä indeksin nimi kannassa tai
  YES, jolloin etsii tarvittavan indeksin kannasta.

14
Rajapinnat

• Pass-Through Facility
• Mahdollistaa Suoran toiminnan koodaukseen
  kantaan
• Vaatii SQL tuntemusta
• Koodi suoritetaan kannassa suoraan ja tuodaan
  vain tarvittava tietojoukko vastauksena takaisin
• Mahdollista tehdä execute käskyjä eli
  kantamuutoksia
• vähentää CPUn ja muistin käyttöä SASssa.
• Voidaan kutsua kantaan määriteltyjä
  StoredProcesseja
• Paluukoodit ja messut kannasta lokille tai
  ohjelmalle
• 24 put SQLXRC
• -1
• 25 put SQLXMSG
• ERROR Connection to the db2 DBMS does not
  exist.

15
Execute käskyt

• CREATE
• Luodaan tauluja, näkymiä, indeksejä tai muita
  DBMS objecteja
• DELETE
• Tuhotaan rivejä tauluista
• DROP
• Poistetaan tauluja, näkymiä, indeksejä tai muita
  DBMS objecteja
• GRANT
• Annetaan käyttäjille oikeuksia muokata mm.
  tauluja tai indeksejä
• INSERT
• Lisätään rivejä tauluun
• REVOKE
• Kumotaan käyttäjien oikeuksia
• UPDATE
• Päivitetään dataa tauluihin

16
Execute

• options sastrace',,,d' sastracelocsaslog
• proc sql
• connect to db2 as db1 (databaseDWIUDB
  schema"dwiudb_schema."
• user"dwiudb_user." password"dwiudb_pw.
  ")
• execute ( update "dwiudb_schema.".tts_c
  ontrol
• set ts_end
• case system
• when 'EL' then el_start
• when 'HV' then hv_start
• when 'IF' then if_start
• when 'MA' then ma_start
• end
• where system in(el_system,hv_system,if_syst
  em,ma_system,rv_system,
• tv_system,vv_system,ye_system,y
  k_system )
• AND table_id 'LGSQ'
• ) by db1
• disconnect from db1
• quit

17
DBKEY

• data work.astaulu
• set opdwout.vasiakk
• set dwdata1.VASIAKK_MUUASTUN(DBKEY(OYTUN ASTUN
  ASTUNTYYPPI MUUASTUN MUUAST_ALKUPVM)
• ) KEYDBKEY
• select(_iorc_)
• when(sysrc(_sok)) do
• put astun
• output
• end
• run

18
DBKEY

• options sastrace',,,d' sastracelocsaslog
• proc sql
• create table saswork.task_henk as select
• b.asko_id, a.oytun, b.astun, b.liitos_kd
• from dwdata1.vasiakk_tunl01(dbkey(astun)) a
  left join dwdata1.vaskok_asiakas b
• on a.astun b.astun
• quit

19
Rajapinnat

• DI-Studiota varten luodaan metadataan tarvittavat
  libname, schema, server määritykset
• Päivämäärien ja aikaleimien konversioita
• DB2 timestampit ja datet näkyviin kantamuodossa
  mm. EGssä, laittamalla libname lauseeseen
  STRINGDATESYES
• Tekee char muodossa 2009-11-26 152314.000000
• Oletuksena NO ja tekee date tyyppisen sarakkeen
  datetime25.6 formaatilla näin 26NOV2009152314.
  000000
• data _null_
• dtmdatetime()
• yyyymmddput(datepart(dtm), yymmddn8.)
• hhput( hour(timepart(dtm)), z2.)
• mmput(minute(timepart(dtm)), z2.)
• ssput(second(timepart(dtm)), z2.)
• timestampyyyymmdd!!hh!!mm!!ss
• call symput('timestamp', timestamp)
• run

20
Rajapinnat
21
lokioptioita DB2options sastrace ',,,d'

• REMOTE(UNIX) DB2 AUTOCOMMIT turned ON for
  connection id 0 0 1263215581 no_name 0 SQL
• REMOTE(UNIX) 1 1263215581 no_name 0 SQL
• REMOTE(UNIX) DB2_1 Prepared 2 1263215581
  no_name 0 SQL
• REMOTE(UNIX) SELECT FROM DDWH.TASKOK_ASIAKAS
  FOR READ ONLY 3 1263215581 no_name 0 SQL
• REMOTE(UNIX) 4 1263215581 no_name 0 SQL
• REMOTE(UNIX) DB2 COMMIT performed on connection
  0. 5 1263215581 no_name 0 SQL
• REMOTE(UNIX) 6 1263215581 no_name 0 SQL
• REMOTE(UNIX) DB2_2 Prepared 7 1263215581
  no_name 0 SQL
• REMOTE(UNIX) select COUNT_BIG() from
  DDWH.TASKOK_ASIAKAS FOR READ ONLY 8 1263215581
  no_name
• 0 SQL

22
lokioptioita DB2 options sastrace ',,,s'

• REMOTE(UNIX) Summary Statistics for DB2 are 79
  1263217060 no_name 0 SQL
• REMOTE(UNIX) Total row fetch seconds were
  0.068400 80 1263217060 no_name
• 0 SQL
• REMOTE(UNIX) Total SQL execution seconds were
  0.006135 81 1263217060 no_name
• 0 SQL
• REMOTE(UNIX) Total SQL prepare seconds were
  0.000051 82 1263217060 no_name
• 0 SQL
• REMOTE(UNIX) Total SQL describe seconds were
  0.025576 83 1263217060 no_name
• 0 SQL
• REMOTE(UNIX) Total seconds used by the DB2
  ACCESS engine were 0.153348 84 1263217060
• no_name 0 SQL
• REMOTE(UNIX) 85 1263217060 no_name 0 SQL

23
Tietoturva

• Tiedon eheyden hallinta, taulujen constraints
  määritykset
• Tietomalli
• Taulujen lukitukset, päivitysten hallinta
• rivi / sivu lukitukset, duplicaatit, rollback
• Varmistukset / palautukset
• Lokitukset statistiikka tarkalla tasolla
• Kantaoikeudet
• tekniset tunnukset, käyttäjä ryhmä tasoilla

24
9.2 Table Server

• SAS 9.2 sisältää arkkitehtuurisesti merkittävän
  uutuuden eli skaalautuvan, säikeistetyn ja
  standardeihin perustuvan Table Server
  -palvelimen, joka tarjoaa yhden rajapinnan SASiin
  ja kolmansien osapuolten tietolähteisiin.
• Hoitaa lukitukset ja natiivin keskustelun eri
  kantoihin
• koodianalysaattori jakaa monimutkaiset kyselyt
  pienempiin palasiin, jotka suoritetaan
  säikeistetysti
• Table Server tukee Base SAS ja SPDS-tauluja sekä
  kolmansien osapuolten tietokantoja, kuten DB2ta,
  Oraclea, Teradataa ja kaikkia tietokantoja,
  joihin otetaan yhteyttä ODBC-rajapinnan kautta.

25
9.2 Table Server
26
DB2 - tietokannan hyödyntäminen SASin välineillä

• OP-Pohjola-ryhmä
• DB2 käyttö Tietopalveluympäristössä
• Tekninen ympäristö
• Volyymit
• Rajapinnat
• Tietoturva
• Table Server
• DB2 käyttö OPDWssä
• OPK Tietopalveluarkkitehtuuri / DWH sovellus /
  OPDW tietovarasto
• OPDW tietovarasto, tietolähteet/liittymät ja
  tietomalli
• OPDWn ETL ratkaisu(SAS), tietokanta(DB2) ja OUT
  rajapinnat (SAS ja DB2)
• OPDW ETL - DB2paivi makro
• OPDW OUT - db2sqlpassthroughextract makro
• Tietovaraston kantojen virheet ja häiriötilanteet
• Eri hyödyntämistavat tietovaraston DB2 ja SAS
  tauluille/näkymille

27
Tietopalveluarkkitehtuuri
Operatiivinen liiketoiminta
Liiketoiminnan ohjaus ja kehittäminen
Tietopalvelualue tietovarastointi ja
tietopalvelut
Suunnittelu, seuranta, analysointi,
ohjaus

Raportointi- ja analysointi- palvelut
Yhteiskäyttöiset yhtenäiset tiedot DW
Tietolähteet
OPDW
Talouden ohjaus ja vakavaraisuuden hallinta
Tieto- virta
VakuutusDW
Myynti, asiakkuudet ja palvelut
ASO
Itsenäiskäyttö ja analysointi
ICTn vastuulla oleva Tipa-infra
Liiketoiminnan vastuulla olevat tietopalvelut
28
OPDW tietolähteet/liittymät

• tietolähteet/input liittymät
• asiakastiedot
• asiakastapahtumat
• sopimustiedot
• organisaatiotiedot
• ohjaustiedot ja muut parametritiedot
• kolme erilaista tietovarastoa OPDW, VAKDW ASO
• OPDW OP pankit
• VAKDW Pohjola vakuutus
• ASO Pohjola pankki
• tietovarastojen eri rajapinnat (käytössä OPDW,
  ASO VakuutusDW).
• DB2 ( muut tietokannat)
• SAS
• tekstitiedostot (mukaan lukien excel ja csv
  tiedostot)
• XML
• hyödyntäminen
• raportointi- ja analysointipalvelut
• hyödyntäjät ovat sovelluksia tai itsenäiskäyttäjiä

29
DW-out Tietolähteet ja hyödyntäminen
Hyödyntäjät
Luottoriskimallinnus
Hyödyntäjät
Kampanjanhallinta
Hyödyntäjät
Basel2 Vakavaraisuuslaskenta
Tasetäsmäytykset
Vastuulaskenta
Vastuuryhmäpäättely
OKO Vira
DW-out
Jälkipäivitys
RHASO
ASOOUT
OPDW
ASO
MH-Kanta
VakuutusDW
30
OPDW tietomalli

• OPDW sisältää OPDW DB2 tietokannan ja OPDW out
  SAS datamartin.
• OPDW tietovaraston tietomallit
• Eri tietomallit OPDW ja OPDW outssa.
• OPDW kohdealueet
• ORGANISAATIO
• ASIAKAS
• TUOTE
• SOPIMUS
• PKTILI
• OMAISUUSKOHDE
• OPDWn DB2 kannan tietomalli on suunniteltu
  tietovaraston tietojen tehokkaaseen
  tallentamiseen. OPDW on EDW tyyppinen pitkälle
  normalisoitu DB2 tietovarasto. Tietomallissa
  tiedot jaettu eri osa-alueisiin ja niiden sisällä
  tiedot pilkottu pienempiin osiin tehokkaasti
  tallennettaviksi.
• OPDW out on SAS rajapinta hyödyntäjille.
  Tietomalli on eri kuin OPDW DB2 kannan
  tietomalli. OPDW out tietomallissa on OPDW tiedot
  yhtenäistetty hyödyntäjien tarpeita vastaaviksi
  kokonaisuuksiksi (SAS tauluiksi).

31
(No Transcript)
32
OPDW tietovaraston ETL ratkaisu(SAS),
tietokanta(DB2) ja OUT rajapinnat (SAS ja DB2).

• OPDW tietovaraston ETL ratkaisu (SAS),
  tietokanta(DB2) ja OUT rajapinnat (SAS ja DB2).
• Haitat SAS käyttöoikeushallinta
  käyttöjärjestelmän kautta, datan monistus (DB2
  kanta ja SAS out rajapinta), levytila (SAS out
  rajapinnoille), kahden tietojärjestelmän tekninen
  tuki ylläpidettävä, kahden tietojärjestelmän
  päivitykset, kahden tietojärjestelmän vaatimat
  resurssit (osaaminen). DB2 taulujen luonti
  yleisesti DBA työtä.
• Hyödyt ei välineriippuvaisuutta, SAS
  hyödyntäminen joustavaa, DB2 käyttöoikeushallinta,
  SAS data helposti siirrettävää ja muokattavaa.
  DB2 taulujen partitiointi mahdollisuudet. SAS
  taulujen luonti SAS kehittäjien työtä.

33
OPDW ETL SAS Access to DB2

• OPDW ETL prosessissa DB2 rajapinta on DB2paivi
  makro.
• DB2paivi makro käyttää libnamea DB2n (SAS Access
  to DB2).
• DB2paivi makro tekee DB2 taulun päivitykset ja
  insertit data stepin OUTPUT, REPLACE ja MODIFY
  komennoilla mikäli ei havaitse tarpeelliseksi
  tehdä insert tapauksessa bulkloadia.
• DB2paivi makro tekee aineistolle bulkloadin kun
  havaitsee että aineistossa joka viedään
  tietovaraston kantaan yhteen tauluun on niin
  paljon rivejä että kannattaa käyttää bulkloadia.
  Bulkload tehdään proc appendlla.
• SAS supports three methods of loading data into
  DB2 Import,Load and CLI LOAD. SAS accesses these
  load options through the SAS accesses these load
  options through the bulk load interface.
• DB2paivi makron bulkload metodina on CLILOAD.

34
DB2paivi makro

• OPDWn ETL prosessi ja rajapinta DB2 kantaan
  tapahtuu SAS makrolla. Tämä makro (DB2paivi)
  käsittelee OPDW kantaa bulk load interfacen
  kautta.
• DB2PAIVI makrolla tehdään DB2 taulujen ylläpito
  tapahtumataulujen tiedoin. Tapahtumataulut
  sisältävät joko muutostapahtumia (UPDATE) tai
  lisäystapahtumia (INSERT).
• Pääajatuksena makron käyttämisessä ovat seuraavat
  seikat
• 1) Ylläpidon työmäärän minimointi
• Makro ja transformaation kautta muokattava
  makrokutsu ovat ETL ohjelmoijalle helpoin
  mahdollinen tapa lisätä DB2 päivitysrutiineja
  ajoihin.
• Päivityksen tekninen toteutus on tehty vain
  kerran, joten sen ylläpito tarvitsee tehdä vain
  yhteen paikkaan. Ja virheselvityksien yhteydessä
  tehtävät muutokset tekevät korjauksen yhdellä
  kertaa kaikkiin päivitysohjelmiin.
• DI studio käyttö transformaation kautta.
• 2) DB2 taulujen ylläpidon tehokkuuden optimointi
  
• Itse tehty makro antaa enemmän liikkumavaraa
  optimoinnin suhteen kuin vakioratkaisut olisivat
  antaneet.
• 3) OPDWn oman ohjelmalogiikan liittäminen mukaan
  päivitysrutiiniin
• Esimerkiksi loppupäivän käyttäminen
  historioitujen tietojen päivitysten yhteydessä.

35
DB2paivi makro

• INSERT DB2 LOADILLA
• DB2Paivi makro hakee ladattavalle taululle rajan,
  jota suurempia rivimääriä päivitettäessä
  käytetään DB2n LOADIA. Raja-arvot on määritelty
  parametritaulussa.
• Jos ladattavaa taulua ei löydy parametri taulusta
  käytetään oletusarvoa. Taulun load raja
  parametrin puuttumisesta tulee merkintä sas
  lokille. Ladattavat rivit, jotka rikkovat
  ladattavan taulun eheys rajoitteita talletetaan
  SAS-tauluun, joka on annettu VIRHETAULU
  parametrissa. DB2 LOAD lisää eheysrajoitteita
  rikkovat tietueet ensin DB2 tauluun, joka on
  jokaiselle datamartin taululle etukäteen tehty
  E-alkuinen taulu, tästä taulusta tietueet
  siirretään VIRHETAULU parametrissa määritettyyn
  SAS-tauluun, jonka jälkeen DB2 virhetaulu
  tyhjennetään.
• Bulk loadilla ladatut taulut jäävät check pending
  tilaan ja niiden taulutilat jäävät backup bending
  tilaan. Tällöin taulutiloista täytyy ottaa backup
  ja ladatuille tauluille pitää tehdä check
  (eheystarkistus), jotta tauluja voi taas
  käsitellä. Tätä varten ajojonoihin tulee liittää
  rutiini, joka tarkistaa latausten jälkeen ko.
  taulujen, sekä niiden taulutilojen tilat. Rutiini
  suorittaa tarvittaessa backupin ja checkin
  automaattisesti. Loadin jälkeen tauluihin
  rakennetaan indeksit uusiksi.
• DB2 LOAD tekee oman lokin logs hakemistoon
  nimelle db2load_taulun nimi_vvvvmmddhhmmss.log,
  jossa taulun nimi on ladattavan taulun nimi tai
  alias (näkymä). Loadissa tapahtuvista virheistä
  tulee virheilmoitus sas-lokille, joka kertoo
  vain, että virhe on tapahtunut. Tarkempi
  virhekoodi ja virheilmoitus tulee DB2 loadin
  omaan lokiin.

36
DB2paivi makro-tietotyypit DB2 / SAS

• Tietotyypit DB2 / SAS (Base)
• DB2 aikatietotyypit DATE ja TIMESTAMP
• SAS aika aina numeerisena käsittely DATE ja
  TIMESTAMP formaateilla SASssa.
• input(put(ajopaiva,date9.)' '
  put(ajoaika,time14.6),datetime30.6)
• into ctrl_muutoshetki
• put muutoshetki sysfunc (putn(ctrl_muutoshetki
  ,datetime30.6)) (ctrl_muutoshetki)
• Output muutoshetki 31JAN2007220000.00000
  0 (1.4859E9)
• DB2paivi aikaleima numerosarja
• data _null_
• dtmdatetime()
• yyyymmddput(datepart(dtm), yymmddn8.)
• hhput( hour(timepart(dtm)), z2.)
• mmput(minute(timepart(dtm)), z2.)
• ssput(second(timepart(dtm)), z2.)
• timestampyyyymmdd!!hh!!mm!!ss
• call symput('timestamp', timestamp)
• run
• DB2 numeeriset tietotyypit esim. integer ja
  decimal ovat kaikki SAS numeric tietotyyppisiä,
  SAS käsittely formaateilla. DB2paivi makrossa
  pyöristetään muutos aineiston numeeriset kentät,
  kohdetaulun mukaan (sarakkeiden desimaalien
  määrä).

37

• /
• Esimerkki DB2PAIVI makron käytöstä suoralle DB2
  insertille SAS datasta.
• DB2 DW niminen kanta jossa on ASIAKAS
  niminen taulu.
• DB2PAIVI makro on tässä esimerkissä
  ajettuna istunnossa.
• /
• rsubmit
• / DB2 yhteys parametrit/
• let db2_dwh_user "user"
• let db2_dwh_pw "password"
• let db2_dwh_schema "DB2 schema"
• let db2_dwh_datasrc "data-source-name"
• / input taulu/
• let muutos work.test_asiakas
• / Makro muuttujat /
• let insert_kentat
• let Loppupvm
• let Avaimet ID NIMI
• let Kentat A1 B1 C1 ALKUPVM LOPPUPVM
  MUUTOSHETKI
• let Yllapitotapa nrquote(INSERT)

38

• /db2paivi
  /
• rsubmit
• db2paivi(kantakanta,
• taulutaulu,
• commitcommit,
• muutosmuutos,
• avaimetavaimet,
• kentatkentat,
• insert_kentatinsert_kentat,
• loppupvmloppupvm,
• yllapitotapayllapitotapa,
• lokillelokille,
• virhetauluvirhetaulu
• )
• endrsubmit

39

• /
• MLOGIC(DB2PAIVI) Beginning execution.
• MLOGIC(DB2PAIVI) This macro was compiled from
  the autocall file /.../db2paivi.sas
• /
• /
• MLOGIC(DB2PAIVI) Parameter KANTA has value
  _DW_
• - Muuttuja päivitettävälle kannalle.
• MLOGIC(DB2PAIVI) Parameter TAULU has value
  _ASIAKAS_
• - Muuttuja päivitettävälle taululle.
• MLOGIC(DB2PAIVI) Parameter COMMIT has value
  _1000_
• - Muuttuja päivitettävälle kannalle. Kertoo
  kuinka monennen rivin jälkeen
• tehdään commit DB2ssa.
• - Muutostapahtumille tulee commit olla 0.
• - Lisäystapahtumilla commit arvo on hyvä olla
  500-1000.
• MLOGIC(DB2PAIVI) Parameter MUUTOS has value
  sta.MASIAKAS
• - Muuttuja päivitys aineiston sisältävälle sta
  taululle SAS kirjastossa.
• MLOGIC(DB2PAIVI) Parameter AVAIMET has value ID
  NIMI ...
• - Muuttuja jossa on ne sarakkeet, joita
  käytetään avaintietoina,
• kun tapahtumatietoja kohdistetaan kannan
  tauluun.

40

• MLOGIC(DB2PAIVI) Parameter INSERT_KENTAT has
  value
• - Muuttuja jota käytetään vain kun käytetään
  UPDATE_JA_INSERT päivitystapaa.
• - Valitaan INSERT_KENTAT muuttujaan ne
  sarakkeet, joille halutaan antaa arvo
• lisättäessä tauluun rivejä, mutta joita
  ei haluta päivittää muutostaulun arvoilla kun
• avaimia vastaava rivi löytyy
  kohdetaulusta.
• Rivejä päivitettäessä, päivitetään vain
  ne sarakkeet jotka on valittu KENTAT
• muuttujaan.
• Ei arvoa esimerkki tapauksessa
• MLOGIC(DB2PAIVI) Parameter LOPPUPVM has value
• - Muuttuja voimassaolon loppupäivälle.
• - Muuttujan arvoksi valitaan sarake, jota
  käytetään päivitettävien tietojen
• rajaukseen, kun tapahtumatietoja viedään
  sellaiseen tauluun, jossa on
• historioituja tietoja.
• - Kun tämä sarake on annettu, kohdistaa DB2PAIVI
  makro päivitystapahtumat vain
• sellaisiin riveihin, joiden voimassaolon
  loppupäivä on 2099-12-31 (eli riveihin,
• jotka ovat toistaiseksi voimassa).
• Ei arvoa esimerkki tapauksessa.
• Esimerkkinä tilanne, jossa päivitetään
  taulua, jossa ei ole historioituja tietoja.
• Silloin ei tarvita voimassaolon
  loppupäivä saraketta ollenkaan ja tämä muuttuja

41
OPDW out SAS Access to DB2 / SQLPassThrough

• OPDW out SAS Access to DB2 libname vai
  SQLPassThrough
• SAS Access to DB2 libname
• SAS Access to DB2 generoi SAS kielen lauseet sql
  lauseiksi omalla parhaalla näkemyksellään.
• hyödyntäminen pienissä OPDW tauluissa ok.
• SQLPassThrough
• Ei SAS käsittelyä.
• Kun käytetään DB2 spesifistä SQLää
• In general, non-SAS processing executed from a
  SAS application.
• OPDW out tarpeiden vuoksi tehty
  db2sqlpassthroughextract makro yhtenäiseksi OPDW
  DB2 SAS poiminta rajapinnaksi. Db2sqlpassthroughex
  tract makrossa mahdollista käyttää esim. SQLn
  where ja exists lauseita.

42

• Macro DB2SQLPassThroughExtract(databasedatabase,
  schemaschema,useruser,passwordpassword,
• DB2tableSYSLAST,
• DB2whereDB2where,
• selectKEEP,
• num4bytenum4byte,
• DB2ExistsTableDB2ExistsTable,
• DB2ExistsKeysDB2ExistsKeys,
• DB2ExistsWhereDB2ExistsWhere,
• out_OUTPUT,
• out_typeout_type
• )
• Oletuksena tämä makro käyttää globaaleja DI
  studion generoimia makro muuttujia
• SYSLAST The input library and table name
• _OUTPUT The output library and
  table name
• KEEP The list of columns
  which should be read

43
OPDW out SAS Access to DB2 / SQLPassThrough

• OPDW out ETL (SQLPassThrough extract DB2 kannasta
  ja SAS yhdistely)
• OPDW tiedot viedään OPDW outn kaksivaiheisella
  ETL prosessilla. Tällä jaolla minimoidaan DB2
  kyselyjen määrä ja DB2-SAS tiedonsiirron määrä.
  Kukin tieto poimitaan ja siirretään vain yhden
  kerran. Jatkokäsittelyssä kutakin tietoa luetaan
  niin monta kertaa kuin se on tarpeen.
• Poiminnassa käytetään SAS näkymiä, joiden
  taustalla suoritetaan DB2n natiivi SQL kyselyjä.
  DB2 tekee tietojen rajauksen, jotta tietoa
  siirrettäisiin mahdollisimman vähän. Tätä varten
  on tehty oma DB2SQLPassThroughExtract
  transformaatio SAS DI studioon ja
  db2sqlpassthroughextract makro.
• Kun tiedot on poimittu näkymän kautta, muokataan
  DB2 tiedot SAS tiedoiksi ja samalla optimoidaan
  rakenteet niin, että levytilaa käytetään
  mahdollisimman vähän. Tähän liittyen
  DB2SQLPassThroughExtract transformaatiossa
  lyhennetään numeeristen tietojen kentän pituutta
  8 tavusta 4 tavuun, jos se on mahdollista.
• Poimitut tiedot lajitellaan jatkossa tehtäviä
  yhdistelyjä varten sopivimpaan järjestykseen
  ennen kuin ne talletetaan levylle.

44

• Varsinaista tiedon muokkausta ei tehdä tässä
  vaiheessa. Tämä on hyvä ratkaisu niin loogisen
  selkeytensä, testattavuutensa kuin
  tehokkuutensakin vuoksi. Poiminnassa tehdään
  ainoastaan poimintaa, jossa kukin tieto poimitaan
  ja siirretään vain yhden ainoan kerran.
  Testiympäristöjen hallinnassa saamme tähän
  välitason, jonka tilanne voidaan pitää samana,
  vaikka DWn tilanteen ja OPDWOUT tilanteet
  muuttuisivatkin.
• Osa DB2 tauluista on partitioitu ja tällaisten
  taulujen lukua varten on tehty oma Select
  Partiton transformaatio. Tämä transformaatio ei
  tee mitään muuta kuin alustaa SYSLAST
  makromuuttujaan input taulun (joka on itse
  asiassa DB2 näkymä) nimen tilalle partitioidun
  fyysisen taulun nimen. Tätä transformaatiota
  kannattaa käyttää kaikkien partitioitujen
  taulujen lukurutiineissa. Näin varmistamme sen,
  että DB2 poiminta on näiltäkin osin niin nopea
  kuin mahdollista.
• Joissakin tilanteissa tietojen muokkaus on
  tehtävissä tehokkaimmin niin, että on vain
  yksivaiheinen prosessi, jossa tietojen poiminta,
  yhdistely ja muokkaus tehdään DB2n puolella ja
  lajittelu yhtäaikaisesti SASin puolella.
  Seuraavissa tapauksissa OPDW out tiedot luetaan
  suoraan OPDWstä
• 1) Poiminnassa poimitaan tietoa, jota ei viedä
  kuin yhteen OPDWOUT tauluun, joten ratkaisu on
  DB2-SAS tiedonsiirron osalta optimoitu2)
  Poimintalogiikka on sellainen, että sen saa
  parhaiten optimoitua DB2 SQL kyselyssä3)
  Poimintalogiikka on niin suoraviivainen, että sen
  ohjelmointi ja ylläpito DB2 SQL kyselyssä ei
  aiheuta ylimääräisiä ongelmia

45

• Esimerkki OPDW outn luonnissa SQLPassThroughn
  avulla, poiminta ja yhdistely DB2ssa ja
  lopputulos SASn
• 1) DB2 yhteyden luonti ympäristökohtaisia kanta,
  skeema ja käyttäjämäärityksiä käyttäen
• / Luodaan DB2 yhteys autoexecissä alustetuilla
  yhteysparametreilla / connect to db2 as
  db2sqlpt ( database db2_dw_datasrc.
  schema db2_dw_schema. user
  db2_dw_user. passworddb2_dw_pw.
  )
• 2) SAS näkymä, joka saa tietonsa DB2 SQL
  kyselyltä- muutetaan sarakkeiden nimiä
  tarvittaessa- muutetaan numeeristen kenttien
  pituus 8 tavusta 4 tavuun, jos mahdollista-
  päivämäärät, kellonajat ja pienet kokonaisluvut
  (lt2.000.000) voidaan ja kannattaa muuttaa-
  desimaalilukuja ja aikaleimoja (timestamp,
  datetime) ei saa muuttaa / Tehdään DB2-gtSAS
  siirto ja sen kentänpituuksien muutokset /
  create view _output. as select ORG
  ,ID ,ID2 , A_loppupvm1 as
  A_loppupvm_tallettaja length4 ,
  A_loppupvm2 as A_loppupvm_kasittelija length4
  , A_tila_loppupvm length4 ... (tästä
  poistettu joukko sarakkeita)

46

• from connection to db2sqlpt
• 3) DB2 SQL kysely DB2n natiivimuodossa
• / Sulkujen sisällä DB2n puolella
  suoritettava SQL kysely, josta tiedonsiirto
  saa tiedot. HUOMAA DB2 kyselyn lopussa
  olevaa merkkiä ei saa laittaa sulkujen
• sisällä olevan kyselyn loppuun.
• / (SELECT A.ORG AS PANKKI
  ,A.ID ,T.ID2 ... (tästä poistettu joukko
  sarakkeita) FROM db2_dw_schema..VA...
• INNER JOIN db2_dw_schema.. VB ON ...
• WITH UR )
• 4) DB2 yhteyden katkaisu
• / Puretaan DB2 yhteys / disconnect from
  db2sqlpt

47
Tietovaraston kantojen virheet ja
häiriötilanteet

• DB2 ongelmat/virheet
• DB2 taulu on suuren loadin jälkeen ei
  luettavassa tilassa.

WARNING The following ERROR/WARNING is being
ignored (due to ERRLIMIT option)
ERROR CLI execute error IBMCLI
DriverDB2/HPUX-IA64 SQL0668N Operation not
allowed for reason code "1" on table
DW.TS12345". SQLSTATE57016 The
number of ERROR/WARNINGs occurred so far
1. OPDW TAULU ON CHECK PENDING TILASSA
48
Tietovaraston kantojen virheet ja
häiriötilanteet

• DB2 ongelmat/virheet
• Taulutila loppuu (yleisin). Huom! DB2 ilmoitus
  tässä virheessä ollut harhaanjohtava. Yleensä
  taulutila virheistä tulee selvemmät ilmoitukset.
  Esim. could not allocate more space....

ERROR CLI execute error IBMCLI Driver
CLI0125E Function sequence error.
SQLSTATEHY010 NOTE Statements not processed
because of errors noted above.
49
Tietovaraston kantojen virheet ja
häiriötilanteet

• DB2 ongelmat/virheet
• Muita, taulu samanaikaisesti toisella prosessilla
  käytössä.

ERROR IBMCLI DriverDB2/HPUX-IA64 SQL0911N
The current transaction has been rolled back
because of a deadlock or timeout. Reason code
"68". SQLSTATE40001
50
Tietovaraston kantojen virheet ja
häiriötilanteet

• Virheiden korjaus rutiinit SAS ja DB2 taulut
• DB2 delete /update / insert käyttöoikeuksien
  mukaan
• SAS delete /update / insert käyttöoikeuksien
  mukaan
• SAS taulujen päivitykset
• Base SASn kautta
• DB2 taulujen päivitykset
• yleensä tehdään nyt Base SASn kautta käyttäen
  proc sqlää.
• voidaan tehdä myös Base SASn kautta SAS data
  vaiheen avulla (merge).
• voitaisiin tehdä myös suoraan DB2 kantaan DB2
  työkalulla (Optim / DB2 ControlCenter).

51
Eri hyödyntämistavat tietovaraston DB2 ja SAS
tauluille/näkymille

• OPDW hyödyntäminen on sovellus tai käyttäjä
  kohtaista
• OPDW hyödyntäminen pääsääntöisesti OPDW out SAS
  tauluista.
• datamartit jotka hyödyntävät OPDWn tietoja
  lukevat tietonsa OPDW outn SAS tauluista.
• muille alustoille välitettävien output liittymien
  data tehdään OPDW outn SAS tauluista.
• Yksittäishyödyntäjät saattavat käyttää sekä OPDW
  DB2 tauluja että OPDW out SAS tauluja.
• Löytyy myös kombinaatioita OPDWn SAS ja DB2
  hyödyntämiselle (molemmat tietolähteet käytössä).
• Vanhat järjestelmät lukevat tietonsa osittain
  OPDWstä ja osittain OPDW outn SAS tauluista.

52
Eri hyödyntämistavat tietovaraston DB2 ja SAS
tauluille/näkymille

• Jos SAS sovellus lukee OPDW tietoja myös suoraan
  DB2 tauluista on mahdollista että joudutaan
  tekemään myös OPDW optimointia SAS optimoinnin
  lisäksi.
• H raportointi käyttää osaa OPDW tiedoista OPDW
  DB2 kannasta suoraan ja osaa OPDW out SAS
  rajapinnasta.
• H raportoinnille oma näkymä/select jossa
  asiakkaan nimi joinattu OPDW DB2 taulusta H
  sovelluksen DB2 taululle.
• OPDW asiakastauluun tehtiin uusi indeksi H
  raportoinnin haku tekijöille.
• Tehokkuus eri kyselyissä DB2 ja SAS, SAS tai DB2.
  Haetaanko tietoa DB2 kannasta vaiko SAS taulusta
  vaiko molemmista.
• Tehottomin vaihtoehto on yleensä tehdä kysely
  jossa yhdistetään SAS ja DB2 taulut SAS
  istunnossa.
• Tehokkuus näkökulma. Tehdään kysely siellä missä
  se on tehokkainta. Tämä saattaa muuttua kun
  taulujen datamäärät muuttuvat.

53
Kiitos!

• Kysymyksiä ?