WIXLIB

Yann Neuhaus & Oli Sennhauser Trivadis AG DB2 UDB for Oracle DBA‘sThe other side of the fence.

AgendaA. IntroductionB. ArchitectureC. InstallationD. ToolsE. DB structureF. Literature Präsentationskennung2

DB2 – Introduction Goal of the presentation : gives an Overview of IBM DB2 to Oracle DBAs No DB2 know how required to understand this presentation Not a DB2 technocircle, not yet , just an overview of the main features When possible comparison has been done with Oracle concepts Give an overview of the DB2 tools and distributionsPräsentationskennung3

DB2 – Test environment DB2 7.2 has been installed on Linux and W2K Linux Suse 7.2 kernel 2.4.4 Hardware : PII 400Mhz / 256 MB and K6 2 500Mhz 92 MB For DB2 version 7.1 Pentium 233 MMX with 96 MB RAM works also Of course for huge productive systems an adapted server has to beconfiguredPräsentationskennung4

DB2 – Presentation layout DB2 architecture Installation Tools DB2 structure DB2 ProductsDB2 process architectureDB2 memory architectureDB2 Instance/database conceptSQLs/PLSQLTablespaceTablesBackup recoveryTuningLiteraturePräsentationskennung5

AgendaA. IntroductionB. ArchitectureC. InstallationD. ToolsE. DB structureF. Literature Präsentationskennung6

Terms / DB2 productsUNO-Systems or DB2 UDBDB2DB2TCP/IPUNIX SystemsIntel SystemsDB2-ConnectTCP/IP SNASNAIBM AS/400IBM S/390, S/370DB2 DB2Host-SystemsPräsentationskennung7

Trivadis AG:Trivadis AG:Trivadis AG:Scalability of DB2 UDBEnterprise Extended Edition(EEE)Enterprise Edition (EE)Cluster / MPPCompany ServerWorkgroup Edition (WGE)ServerDesktop PCLaptop Personal Edition(PE)Satellite Edition(SE)Präsentationskennung8

Components of DB2 UDBHostDRDAApplication Requester(AR)Run-Time Client(CLP, pment ClientCommunication Support (API)DB2 ConnectDB2 Relational ConnectDB2 - EngineDRDA Application ServerOracleHost Präsentationskennung9

Instance ConceptServerDB2 product (e.g v7.2)Instance 1Instance 2DB Config FileCatalogDB 1LogDB2 UDBDataJoinerDB Config FileCatalogDB 2 DB Config FileCatalogDB 3LogDB2 UDBData JoinerDB Config FileCatalogDB 4LogLogDBM Config FileDBM Config FilePräsentationskennung10

Processes modelInstance 1DB ConnectionADB agentdb2agntpdb2agentdb2agntpdb2tcpcmConnect Präsentationskennung11

Processes on NT Präsentationskennung12

Memory model I Präsentationskennung13

Memory model II Präsentationskennung14

Memory model III Präsentationskennung15

Programming Client-Server Dynamic and static SQL Programming languages (C, C , Java, etc.) Embedded SQL PL/SQL (ORA) SQL PL (UDB) Präsentationskennung16

Embedded SQLmyapp.aqcSourcePRECOMPILERmyapp.bndBINDERBind Filemyapp.cModifiedSource FileCOMPILERDBSERVICESmyapp.oObject ableFileLibraries Präsentationskennung17

SQL Procedure Language I SQL PL is for PORTING, not native!SQL PL (UDB)PL/SQL (ORA)BEGINDECLAREDECLARE variablea datatype1;rowtype table1%ROWTYPE;DECLARE variableb datatype2;Variable9 datatype9 : value9;DECLARE variable9 datatype9 DEFAULT value9;WHILE-ConstructSELECT a,b INTO variablea, variablebFROM table1 WHERE c xxxx;END; BEGINFOR-LoopSELECT * INTO rowtype1 FROM table1WHERE c xxxx;END;Präsentationskennung18

SQL Procedure Language IISQL PL (UDB)PL/SQL (ORA)FOR my loop ASDECLARE CURSOR mycursor ISSELECT a,b FROM table1WHERE b yyyy DOSELECT a,b FROM table1 WHERE b yyyy;BEGINBEGIN. ATOMICBEGINFOR table1 rec IN mycursor LOOPDECLAREENDFOR; myerr.CONDITION FOR SQLSTATE ‚99999’;DECALRE UNDO HANDLER FOR NOT FOUND;DECLARE UNDO HANDLER FOR SQLEXCEPTION;.END;IF conditionEXCEPTIONENDLOOP;WHEN NO DATA FOUND THENROLLBACK;WHEN OTHERS THENROLLBACK;END;SIGNAL myerror SET MESSAGE TEXT ‘Error’ Präsentationskennung19

AgendaA. IntroductionB. ArchitectureC. InstallationD. ToolsE. DB structureF. Literature Präsentationskennung20

Supported platforms UDB /2Windows 95XXWindows 98XXWindows XPWindows NTWindows 2000LinuxXXNUMA-QSolarisXEEEXLit: -eee.html Präsentationskennung21

Requirements CD (90 days Trial) On CD 300 MB (Linux), 500 MB (Windows) On Disk 500 MB should be enough (incl. sample DB) 128 MB RAM recommended root/Admin account OS-Requirements Präsentationskennung22

DeviderRequired OS-User/DB UserDeviderDevider cat /etc/passwd grep db2db2fenc1:x:46:46:DB2 Instance UDF 47:DB2 Instance main DB2 2:x:501:102: DB2 Instance main user:/home/db2inst2:/bin/bash cat /etc/group grep db2inst1,db2inst2db2iadm2:x:102:db2as These users are OS authenticated! Präsentationskennung23

„IFA“ OFA? Basically 2 different types of paths– a)– b)BinariesDB files Binaries– /usr/IBMdb2/V7.2/.– C:\Program Files\SQLLIB/. Linked (UNIX only)/home/db2inst1/sqllib/bin - /usr/IBMdb2/V7.2/bin DB files/specified path/ {DB2INSTANCE}/NODEnnnn/SQL00001C:\ DB2INSTANCE \ NODEnnnn \ SQL00001 Präsentationskennung24

DeviderFile SPCS.1\SQLSPCS.2 Präsentationskennung25

Environment variables / DB2 variable valuesexport var DB2INSTANCE ( {ORACLE SID})Environment VariablesDB2DIR ( {ORACLE HOME})INSTHOME ( {ORACLE BASE} / {HOME})Instance-LevelRegistry Profiledb2set (without reboot)Global-LevelRegistry Profile Präsentationskennung26

Installation snap shots (Linux) as root I Präsentationskennung27

Installation snap shots (Linux) as root II Präsentationskennung28

Installation snap shots (Linux) as root III Präsentationskennung29

Installation snap shots (Linux) as root IV Präsentationskennung30

Installation snap shots (Linux) as root V Präsentationskennung31

Installation snap shots (Linux) as db2inst3 VI Präsentationskennung32

Installation snap shots (NT) I Unzip CD Start setup.exe Präsentationskennung33

Installation snap shots (NT) II Präsentationskennung34

Installation snap shots (NT) III Präsentationskennung35

Installation snap shots (NT) IV Restart Präsentationskennung36

Installation snap shots (NT) V After restart (or with First Steps) Präsentationskennung37

Installation snap shots (NT) VI Präsentationskennung38

Installation snap shots (NT) VII For Trivadians there is an other way too! For a second instance it is a must!?! Präsentationskennung39

Sample DB I DB2 „sample“ DB: emp, dept, etc. (ORA: scott/tiger)PROJECTDEPARTMENTEMP ACTEMPLOYEEEMP PHOTOORGEMP RESUME Installation: db2sampl path Präsentationskennung40

Sample DB II Präsentationskennung41

AgendaA. IntroductionB. ArchitectureC. InstallationD. ToolsE. DB structureF. Literature Präsentationskennung42

DB2 – tools Control center, DB2 adinistration with GUI Needs the “db2jstrt” to be starteddb2jstrt 6799 & db2, is the main db2 Tool, it allows to perform ImportLoadBackup/restore/rollforwardSome selectListsActivate databaseAnd so ondb2inst1@freemind: db2 activate db DB2722DB20000I The ACTIVATE DATABASE command completed successfully. db2sql92 Performs SQL operationsDMLs/DDLsPräsentationskennung43

DB2 – tools db2 db2 is an integration of srvmgrl/rman/export/sqlldr The help function is quite usefull (? Or ? COMMAND)db2 ?ACTIVATE DATABASEADD DATALINKS MANAGER DISCONNECTDROP DATABASEDROP NODENote:ECHOEXPORTPREP/PRECOMPILEPRUNE HISTORY/LOGFILELOADLOAD QUERYPINGUPDATE HISTORYUPDATE LDAP NODEUPDATE MONITOR SWITCHESSome commands are operating system specific and may not be available.For further help: ? db2-command- help for specified command? OPTIONS- help for all command options? HELP- help for reading help screensThe preceding three options can be run as DB2 option from an OS prompt.!db2ic- DB2 Information Center (Windows/NT and OS/2only)This command can also be run as db2ic from an OS prompt. Präsentationskennung44

DB2 – tools (control center) Control center like Oracle OEM Allows to do all admin tasks but sometimes with fewer optionsPräsentationskennung45

DB2 – tools (db2sql92) Start SQL commands ( oracle sqlplus) SQLs must be ended by “;”db2inst1@freemind: db2sql92 db2inst1/manager -d DB2722SQL authorization ID DB2INST1Local database alias DB2722Running in Embedded Dynamic B2SQL92 select count(*) from cagdbadmin.method;Statement # 1 :select count(*) from cagdbadmin.method1------------232Number of rows retrieved is:Number of rows sent to output is:11Elapsed Time --------- Präsentationskennung46

DB2 – alert log file (db2alter.log) DB2 has also an alert log file Located in instance name /sqllib/db2dump db2alert.logAlert :000PID:8286(db2agent (DB272))Appid:*LOCAL.db2inst1.020514074828database utilities sqluCreateMsgQueueProbe:10DIA9999E An internal error occurred. Report the following error code : "". If an error is determined to be an alert, then an entry is made in thedb2alert.log file and to the operating system or native logging facility.Präsentationskennung47

DB2 – alert log file (db2diag.log) nst1Node:000PID:9828(db2agent (DB272))Appid:*LOCAL.db2inst1.020517090058database utilities sqlubckaProbe:0Database:DB272Backup terminated. When an error occurs, the db2diag.log file is updated with informationabout the error.Präsentationskennung48

AgendaA. IntroductionB. ArchitectureC. InstallationD. ToolsE. DB structureF. Literature Präsentationskennung49

DB2 – Tablespaces – containers - types DB2 tablespace Same concept as in Oracle Oracle datafile DB2 container Under DB2 several tablespace types : Each tablespace has its own storage type : Regular : any data typeLong : long varchar, long vargraphic or LOB data typesTemporary : Temporary dataSMS System Managed Space (Default)DMS Database Managed SpacePräsentationskennung50

DB2 – Tablespaces - containers SMS ( oracle autoextent) : DMS (space preallocated) System Space ManagementSpace automatically allocated as requiredThe containers are directories where files are locatedThe containers contain files with the form : SQLXXXXX.DAT SQLXXXXX.INX And so on Containers are OS datafilesA DMS tablespace defined with many containers with enable automatic data striping. In DMS, a table can have these 3 components (data index lob) in separatetable space In SMS all the components are in the same table spacePräsentationskennung51

DB2 – Tablespaces - containers DMS provides according to IBM better performances Each tablespace can be defined with its own page size (oracle block size) Each tablespace can have its own buffer pool Not only 3 buffer pool available (recycle, default, keep) like Oracle but foreach tablespace a buffer pool can be defined. Data are striped across the containers (DMS or SMS) As soon as a new container is added to a tablespace, the data areredistributed across the new storage Long tablespace can only be created in DMS mode. Adding of containersto SMS tablespaces is NOT possible.Präsentationskennung52

DB2 – Tablespaces - containers For each tablespace a PREFETCHSIZE can be defined number of datawhich will pre fetched when prefetching is done For each tablespace the response time (OVERHEAD) andTRANSFERRATE of the disks have to be set (average over thecontainers) These values should help DB2 to create and access the data in an optimalwayPräsentationskennung53

DB2 – Tablespaces - containers A container can be directory (SMS) a file (DMS), a raw device (DMS), apartition (DMS) or a logical volume (DMS) The DB2 extent concept is the same as in Oracle Default extent size 32 pages Parameter DFT EXTENT WZ to change the default extent size at DBcreation level At tablespace level the parameter EXTENTSIZE is available EXTENT size cannot be set at table level Be careful when tablespace is created with a page size, the buffer pool forthis page size has to be definedPräsentationskennung54

DB2 – Tablespaces Präsentationskennung55

DB2 – Tablespaces creation with CC Präsentationskennung56

DB2 – Tablespaces – containers – creation script Creation of DMS tablespace 1000 pages of 4K Extentsize 64CREATE REGULAR TABLESPACE DATA TBSPAGESIZE 4K MANAGED BY DATABASEUSING (FILE '/u03/db2data/DB272/data tbs 01.dbf' 1000)EXTENTSIZE 64 At database creation, three tablespaces are mandatory : Catalog tablespaceUser tablespaceSystem tablespacePräsentationskennung57