DB2 oktatás

Adatbázis létrehozásakor feltett kérdések

-ilyen fajta adataink vannak

-mennyi adatunk van

-mennyi adatunk lesz

-milyen gyorsan fog nőni az adatmennyiség

-milyen gyakran használjuk az adatokat

-milyen gyakran változnak az adatok

-mennyi alkalmazás használja egyidejüleg

-hogyan tároljuk az adatokat

db2 szolgáltatásai

-optimizer

-logolás

-biztonság(security: ki férhet hozzá)

-folyamatos működés

-integritás(bármikor elérhessük)

-visszaálllitás

-lock manager( IRLM started task)

-concurency(többen elérhetik ugyanazt az adatot)

Catalog: itt van tárolva minden hogy mit csinálunk a táblánkkal,statisztikai infókat tartalmaz

LOG: miden ami a dB2 ben történik, tárolunk, mit módosítottunk, miről - mire, mindig szinkron módon kezeljük

Commit pont: felszabadítjuk a lockokat, sikerült a tranzakció

Rollback pont:  tranzakció kezdete, kezdőpont

Hierarchikus adatbázis

IMS-akkor jó ha tudjuk hogy hol találjuk az adatot

Reláció: Egyedek közötti kapcsolat

tábla: egyedek tulajdonságai

SQL: 1989-ben szabványosították

Tábla létrehozásánál mindenképp kell tudni a tábla nevét

Oszlop -milyen gyakran neve és típusát is meg kell adni

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Feladatkörök

SYSADMIN

- telepítés (új verzió, patch)

- minden jogot tud adni

- rendszer teljesitményért felelős

- létrehozás(adatbázisok stb stb...)

DBADMIN:  adatbázison belül nagyfönök (ezek leszünk mi)

- jogot tud adni adatbázsion belül

- létrehozás tábla,index stb...

- karbantartás, load, copy, recovery data

- rebind

Programozó

-tesztkörnyezet kialakitása

-SQL-eket prototipizálja (hogy egy lekérdezést a lehető leggyorsabb legyen)

-programok létrehozása, tárolt eljárások létrehozása

-binding packages

END USER

 -használja az adatokat

OPERATOR

-fent van-e a db2

-komponensek ellenőrzése

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

!!!!!

UNIQUE KEY egyedileg azonositja az adott sort(lehet több oszlop is)

    -lehet benne olyan oszlop ami NULL értéket vehet fel

    -bármennyi lehet egy táblában

Primary KEY: speciális UNIQUE KEY, aminek viszont egy oszlopa se vehet fel NULL értéket

                        -egy lehet egy táblában csak

                        -több oszlopból is állhat

Foreign KEY: egy másik tábla UNIQUE KEY-ére mutat

Kötelező egy SELECT -be:  a SELECT és a FROM!!!!

OUTER JOIN -ezzel megkaphatjuk azokat a sorokat is összekapcsolt tábláknál, ahol nem volt konkrét kapcsolat a 2 tábla között,

pl egy dolgozonak nem volt departmentje, akkor attól még ki lesz listázva ő is

CASE : feltételeket tudunk megadni

INSERT INTO

UPDATE  táblanév SET oszlopnév = valami WHERE feltétel

DELETE FROM táblanév WHERE feltétel

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Kommunikáció a DB2-vel

UDB

    -command line processor(CLP) (dos command prompt szerű)

    -command center (grafikus)

DB2 OS/390

    -SPUFI

    -QMF

stb...

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Teszt környezet lényege: legyen egy olyan tábla halmon, ami az eredeti adat halmot reprezentativan jelképezi, hasonít az eredetire

SQLCA: üzenetközvetitő, pl megmondja hogy egy SQL utasítás eredményének van-e még sora, SQLSTATE,SQLCODES-t tartamazza

BIND:(forráskódot leforditjuk és gépi kod lesz belőle: a precompiler kiszedi a programkodból az SQL részt egy DBRM fájlba, betesz egy hívást a forráskodba

helyette, majd compiler, megcsinálja  gépi kódot, a DB2 oldalán pedig bekerül ez a DBRM fájl, és szépen leforditja magának ebben a sorrendbe:

          -syntax check(catalogba megnézi van e olyan objektum amire az SQLbe hivatkozunk-e)

          -security check

          -optimalzálja az SQL-t

Amikor a leforditott program fut és odaér az SQL részhez, akkor meghivja a DB2-t és a DB2 által előzőleg leforditott package-et lefuttatja

REBIND: néha érdmes frissiteni az infókat, de elötte mindig futtatni kell a RUNSTAT-ot, az optimizer miatt, DBADMIN csinálja, a teljesítmény miatt, megcsinálja mégegyszer a teljes BIND-ot,de nem nem modositja a package sql kodját, csak optimalizál, megnézi hogy nincs -e uj index pl a táblához, amivel gyorsabban el lehet érni, stb...

Stored  Procedure (tárolt eljárások) hátrány: folyamatosan megy a ping-pong a hálózaton keresztül a cliens és a server között

Statikus VS Dinamikus SQL

Statikus SQL : ami be van drótozva a programba, lehet hozzá egy package-et létrehozni, GYORS

Dinamikus SQL: pl SPUFI elött elkezdek SQL-eket írni, futás időbe kell megnézni a DB2-nek, hogy szintaktiallag helyes-e, security, optimalizálás, stb...

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Applikáció Generátorok

-lényegtelen

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

UDB

-minden egyes database tartalmazza a catalogot, data-át, buffer poolt, külön - külön

zOS

- a db2 tartalmazza a catalogot, az adatbázisokat,  directorykat, buffer poolokat, mindent egybe

Teljesítménymérés

-Performance Monitor

    -trace facility (nagyon költséges)

    -statistics

    -locking activity(tábla szintű, page szintű, sorszintű)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

NON CLUSTERED INDEX : fizikailag vannak sorrendbe az adatok

CLUSTERED INDEXED: az index pageken ABC sorba vannak az emberek, fizikailag nincsenek sorrendbe

CREATE INDEX indnév ....

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Views(nézetek) : Egy olyan leirás ami leirja, hogy milyen sorokkal, oszlopokkal szeretnénk dolgozni, úgy viselkedik, mint egy tábla, de az adat nincs tárolva.

CREATE VIEW név AS SELECT ...... FROM WHERE .....

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TABLE: logikai definició, lerja milyen adataink legyenek, milyen kapcsolat legyen közöttük stb...

TABLESPACE : fizikai definició,fájl, viszont egy tablespace-en több TABLE lehet , létrehozásakor lehet megadni, hogy milyen LOCK-olás legyen rá érvényes

DROP: eldobja az objektumokat,definiciókat, minden ami hozzá volt deffiniálva azok szintén mennek a lecsóba

LOCKING :IRLM lock manager tartja nyilván, hogy mi van fogva

-tabla szintű

-page szintű

-sor színtű : ( nagyon nagy erőforrásigényes!!)

PAGE: az a legkisebb adatmennyiség amit az I/O mozgatni tud, pl.: 4Kb

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ADMIN ESZKÖZÖK (UTILITIES)

ONLINE UTILITY: csak akkor lehet futtatni amikro a DB2 is fut

OFFLINE UTILITY: akkor is lehet használni job-ból amikor a db2 nem fut

-karbantartás(pl: reorg)

-beállitások , mozgatások (Load: betöltünk adatokat tablespace-be, UNLOAD:)

-recovery

ADATVISSZAÁLLÍTÁS(RECOVERY)

FULL IMAGE COPY: csinál egy teljes mentést a TABLESPACE-ekről

INCREMANTAL COPY: a FULL IMAGE COPY óta eltelt változásokat menti csak

MEREGECOPY: fogja az FULL IMAGE copy és az utolsó FULL után keletkezett összes INCREMANTAL COPY-t összemásolja és csinál belüle egy FULL IMAGE copyt

QUIESCE: csendes pont, amikor a TABLESPACE "konzisztens" állapotban van, ide lehet vissza állni ha valami gáz van

REORG -al elvégezhető

    -reorg

    -mentés

    -sort

    -load/unload

RUNSTAT: beleteszi a CATALOG-ba a statisztikai infókat

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Jogosultságszerzés

IMPLICIT módon: saját táblánkhoz mindenhez van jogunk

EXPLICIT: amikor GRANT-al kapunk jogot valamihez

INDIRECT módon: amikor pl egy SYSADMIN által létrehozott package-et van jogunk futtatni

TRIGGER: bizonyos események bekövetkezésekor lefut valami

SQL - DB2SQL Workshop

DDL: data definition language ( create, drop, alter)

DML: data Manipulation language(SELECT , insert, update, delete)

DCL: data control language( grant, revoke)

TABLE: nevének kell lenni

OSZLOP: nevének kell lenni és egyedinek kell lennie a táblában

UNION ALL -nál nem dobja ki az ismétlődéseket, nem rendezi 2 SELECT eredményét

UNION -kidobálja az ismétlődéseket 2 SELECT eredményét

SELECT * FROM table FETCH FIRST 5 ROW ONLY   -csak az első 5 db eredményt adja ki

SELECT * FROM table WHERE oszlopnev IN('xx','ddd','d')   -csak azok kerülnek be amik beleesnek az IN résznél felsorolt elemekbe

SELECT * FROM table WHERE oszlopnev BETWEEN  érték1 AND érték2 BETWEEN-nél a két szélsőérték is a halmaz része

SELECT * FROM table WHERE oszlopnev IS NULL  -azokat a sorokat amiknek NULL az értéke

SELECT * FROM table WHERE oszlopnev LIKE 'G%'  -azokat a sorokat amiknek az olszlopnev oszlop eleme G vel kezdődik

SELECT * FROM table WHERE oszlopnev NOT LIKE 'G%'  -azokat a sorokat amiknek az olszlopnev oszlop eleme nem G vel kezdődik

1,SELECT empno,lastname,birthdate,salary FROM employee WHERE salary>30000  order by salary desc

2,SELECT lastname,firstnme,workdept FROM employee order by workdept,lastname

3,SELECT DISTINCT edlevel FROM employee

4,SELECT DISTINCT empno,projno FROM emp_act WHERE empno<'000100'

5,SELECT lastname,salary,bonus FROM employee WHERE sex='M'

6,SELECT lastname,salary,comm,hiredate FROM employee WHERE salary>20000 and  hiredate>'1979-01-01' order by hiredate

7,SELECT lastname,salary,bonus,comm FROM employee WHERE (salary>22000 and  bonus=400) or (bonus=500 and comm<1900) order by lastname

8,SELECT lastname,salary,bonus,comm FROM employee WHERE salary>22000 and  bonus=400 or bonus=500 and comm<1900 order by lastname

8,SELECT lastname,salary,bonus,comm FROM employee WHERE salary > 20000 and bonus in (400,500) and comm < 1500 order by lastname

9,SELECT * FROM emp_act WHERE projno LIKE 'AD%' and actno IN(10,80,180) order by projno,actno,emstdate,emendate

10, SELECT workdept,empno,lastname,firstnme FROM employee WHERE job='MANAGER' order by empno

11, SELECT empno,lastname,salary,bonus FROM employee WHERE bonus between 800 and 1000 order by bonus,empno

12, SELECT empno,lastname,workdept FROM employee WHERE workdept >= 'A00' and workdept <= 'C01' order by lastname desc,empno

13, SELECT * FROM project WHERE projname like '%SUPPORT%' order by projno

14, SELECT * FROM department WHERE deptno like '_1_' order by deptno

15, SELECT lastname,firstnme,midinit,salary,job FROM employee WHERE job<>'MANAGER' and job<>'PRES' order by salary FETCH FIRST 5 ROWS ONLY

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1, SELECT lastname,firstnme,deptname FROM employee,department WHERE deptno=workdept order by firstnme,lastname,deptname

2, SELECT lastname,firstnme,job,deptno FROM employee,department WHERE deptno=workdept and  job<>'MANAGER' and deptno >= 'A02' and deptno<='D22' order by firstnme,lastname,job,deptname

2.1, SELECT lastname,firstnme,deptname,job FROM department,employee WHERE deptno=workdept AND job<>'MANAGER' and deptno between 'A02' and 'D22'  order by firstnme,lastname

3, SELECT deptname,lastname,firstnme,job FROM employee,department WHERE mgrno=empno AND JOB='MANAGER' order by deptname

4, SELECT deptname,lastname,firstnme,job FROM employee,department WHERE workdept=deptno AND JOB='MANAGER' order by deptname

5, SELECT DISTINCT project.projno,actno,projname FROM projact,project WHERE projact.projno=project.projno AND project.projno like 'AD%' ORDER BY project.projno,actno

6, SELECT DISTINCT EMPLOYEE.empno,lastname,projno FROM EMPLOYEE,emp_act WHERE EMPLOYEE.empno=emp_act.empno AND PROJNO='AD3113' ORDER BY EMPLOYEE.empno,projno

7, SELECT   projno,EMPLOYEE.empno,actno,lastname,emstdate FROM EMPLOYEE,emp_act WHERE EMPLOYEE.empno=emp_act.empno AND emstdate>'2002-10-01' ORDER BY

projno,EMPLOYEE.empno,actno

7.1, SELECT  emp_act.emstdate,emp_act.projno,employee.empno,emp_act.actno,employee.firstnme

      FROM emp_act,employee

      WHERE employee.empno=emp_act.empno and emp_act.emstdate>'2002-10-01'

  ORDER BY emp_act.projno,employee.empno,emp_act.actno

8,

9,

10,

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1,

SELECT lastname,salary,(salary*1.05)  AS INC_Y_SALARY,DECIMAL(((salary/12)*1.05),10,2) AS INC_M_SALARY

FROM employee

WHERE (SALARY*1.05)<=40000

2,

SELECT lastname,edlevel,salary,salary+1200 AS NEWSALARY,bonus,DECIMAL((bonus/2),9,2) AS NEWBONUS

FROM employee

WHERE edlevel=18 or edlevel=20

ORDER BY EDLEVEL DESC,salary

3,

SELECT lastname,edlevel,salary,salary-1000 AS DECR_SALARY,salary*0.8 AS S08,salary*1.2 AS S12,workdept

FROM employee

WHERE workdept='D11' AND SALARY BETWEEN 40000 AND 50000

ORDER BY SALARY

4,

SELECT lastname,edlevel,salary,salary*0.9 AS DESCSALARY,SALARY+bonus+comm AS INCOME,workdept

FROM employee

WHERE workdept='D11' AND SALARY+bonus+comm>salary*0.9

ORDER BY SALARY

5,

SELECT deptno,deptname,value(mgrno,'UNKNOW') AS mgrno

FROM department

WHERE mgrno IS NULL

6,

SELECT projno,value(majproj,'UNKNOW') AS MAJOR_PROJECT FROM project WHERE projno LIKE('MA%')

7,

SELECT empno,lastname,(YEAR(HIREDATE)-YEAR(BIRTHDATE)) AS age

FROM employee

WHERE (YEAR(HIREDATE)-YEAR(BIRTHDATE))>25

ORDER BY age,empno

8,

SELECT projno,YEAR(prstdate) AS YEAR,MONTH(prstdate) AS Month,prendate

FROM project

WHERE prendate='1982-12-01'

ORDER BY projno

9,

-----------------------

1,

SELECT workdept,SUM(salary) AS SUM_SALARY

FROM employee

GROUP BY workdept

2,

SELECT workdept,COUNT(empno) AS EMP_COUNT

FROM employee

GROUP BY workdept

3,

SELECT workdept,COUNT(empno) AS EMP_COUNT

FROM employee

GROUP BY workdept

HAVING COUNT(empno)>3

4,

SELECT workdept,COUNT(JOB) AS DESIGNERS

FROM employee

WHERE JOB='DESIGNER'

GROUP BY workdept

5,

SELECT workdept,sex,avg(salary) AS AvgSal,AVG(bonus) AS AvgBon,AVG(comm) AS AvgComm

FROM employee

GROUP BY sex,workdept

ORDER BY workdept

6,

SELECT workdept,DECIMAL(AVG(bonus),9,2) AS AvgBon,DECIMAL(AVG(comm),9,2) AS AvgComm

FROM employee

GROUP BY sex,workdept

HAVING AVG(bonus)>500 AND AVG(comm)<5000

ORDER BY workdept

----------------------------------------------------

1,

2,

SELECT workdept,empno,lastname,salary

FROM

"USER".EMPLOYEE

WHERE workdept='A00'

UNION ALL

SELECT '','','',SUM(salary)

FROM

"USER".EMPLOYEE

WHERE workdept='A00'

3,

4,

-----------------------------------------------

1,

SELECT workdept,empno,lastname,salary

FROM EMPLOYEE

WHERE SALARY >= (

SELECT AVG(salary)+5000

FROM

"USER".EMPLOYEE)

2,

SELECT empno,lastname

FROM EMPLOYEE

WHERE empno NOT IN (

SELECT empno

FROM

emp_act)

ORDER BY empno

3,

SELECT projno,DAY(prendate-prstdate) AS DAYS

FROM project

WHERE (prendate-prstdate)=(

SELECT MIN(prendate-prstdate)

FROM project)

4,

______________________________________________________________________________________________________________________________________________________________________________________________

INSER INTO

Sima insert

INSERT INTO tablanév(oszlopnevek,....) VALUSE(értékek,......)

Egy SELECT eredményével feltölteni egy táblát

INSERT INTO tablanév

    SELECT * FROM másik tábla WHERE valami='ndfg'  

UPDATE

UPDATE tablanév SET oszlopnev=ertek WHERE feltéte

DELETE

DELETE FROM táblanév WHERE feltétel QUERYNO hánydbsort

DROP

vigyázni kell, ha olyat DROP-olunk ami kapcsolódik hozzá, azt is törli!!!!!

ALTER

ALTER TABLE talenév

ALTER