DB2 Installation, DB Configuration Instructions There are many pre-steps; Let do the first of first on Redhat Server Provide me Server IP and domain name Then do the following Before you begin 1. The X Window System is required on the AIX or Linux server to install DB2 by using the installation wizard. If you want to install DB2 as a non-root user, have your system administrator complete the following tasks on the target server before you begin the installation: • If you are installing DB2 on an AIX or LINUX server, install sudo on the server. The default AIX distribution does not include sudo. To get the sudo installation package, go to http://www.ibm.com/systems/power/software/aix/linux/toolbox/date.html. Find sudo-version in the Package column, and click RPM to download the package. • Ensure that the non-root user has full sudo privileges on the server. • Ensure that the following requirements are met to enable the installation of DB2 by using the Setup wizard: o The non-root user is able to use the X Window System on the target server and the X Window System DISPLAY environment variable is set correctly for the not-root user. o The DISPLAY environment variable is preserved in the sudo environment. o The root user has access to the X Window System display that is owned by the non-root user. These display requirements do not apply if you install DB2 in silent mode. • If you are installing DB2 on an AIX server, ensure that the ODMDIR environment variable is preserved in the sudo environment. If you want to install as a non-root user, your system administrator must complete configuration tasks on the target server before you begin the installation. These tasks are listed in Installing DB2 on AIX or Linux and Installing IBM Spectrum Control in a single-server AIX or Linux environment. 2. Pre steps to create db2 database on AIX or LINUX To create AIX or linux users and groups mkgroup id=999 db2iadm mkgroup id=998 db2fadm mkgroup id=997 dasadm mkgroup id=800 db2test mkgroup id=801 db2dev mkgroup id=802 db2prod mkgroup id=223 ccuser: permission: Select mkgroup id=224 ccdmin, select, insert, update and delete mkuser id=1001 pgrp=db2iadm groups=db2iadm home=/home/db2inst db2inst mkuser id=1002 pgrp=db2fadm groups=db2fadm home=/home/db2fenc db2fenc mkuser id=1003 pgrp=dasadm groups=dasadm home=/home/dasusr dasusr chown -R db2inst /u01/db2inst Create a sudo users: szhang, it requires accessing, creating files on folders below: 3. DB2® database products can be installed as a non-root user. Before you install any DB2 database product as a non-root user, be aware of the differences between root installations and non-root installations, and the limitations of non-root installations. Refer to the Related Links at the end of this topic for details. Prerequisites for installing a DB2 database product as a non-root user are: • You must be able to mount the installation DVD, or have it mounted for you. • You must have a valid user ID that can be used as the owner of a DB2 instance. User IDs have the following restrictions and requirements: o Must have a primary group other than guests, admins, users, and local o Can include lowercase letters (a-z), numbers (0-9), and the underscore character ( _ ) o Cannot be longer than eight characters o Cannot begin with IBM, SYS, SQL, or a number o Cannot be a DB2 reserved word (USERS, ADMINS, GUESTS, PUBLIC, or LOCAL), or an SQL reserved word o Cannot use any User IDs with root privilege for the DB2 instance ID, DAS ID or fenced ID. o Cannot include accented characters o If existing user IDs are specified instead of creating new user IDs, make sure that the user IDs:  Are not locked  Do not have expired passwords • Ensure that kernel parameters are updated as required. On Linux, kernel parameters are managed automatically for root installation but must be updated manually for non-root installations. • On AIX® Version 5.3, Asynchronous I/O (AIO) must be enabled. It is strongly recommended the system has I/O Completion Ports (IOCP) enabled. • Your home directory must be a valid DB2 path. 4. DB2 installation paths have the following rules: o Can include lowercase letters (a-z), uppercase letters (A-Z), and the underscore character ( _ ) o Cannot exceed 128 characters o Cannot contain spaces o Cannot contain non-English characters To perform a non-root installation: 1. Log in as a non-root user 2. Install your DB2 database product using any of the methods available to you. Options include: o The DB2 Setup wizard (GUI install) o The db2setup command with a response file (silent install) 5. After the DB2 database product is installed, you must open a new login session to use the non-root DB2 instance. Alternatively, you can use the same login session if you set up the (use this one at Concentrix ) DB2 instance environment with $HOME/sqllib/db2profile (for Bourne shell and Korn shell users) or $HOME/sqllib/db2chsrc (for C shell users), where $HOME is the non-root user's home directory. create a DAS, you must have root user authority on Linux operating systems or use an account that has the correct authorization to create a service. On Windows, if a specific user is to be identified, create a user with local Administrator authority. Enter db2admin create. If a specific user account is desired, you must use /USER: and /PASSWORD: when issuing the db2admin create command. On Linux operating systems: . Ensure that you have root user authority. . At a command prompt, issue the following command from the instance subdirectory in the DB2 installation path: dascrt -u DASUser 6. Download the installation packages or images into a separate directory for each product. The following list provides suggested download directory paths: • Windows operating systems: o c:\downloads\DB2 o c:\downloads\TPC o c:\downloads\JazzSM • AIX or Linux operating systems: o /downloads/DB2 o /downloads/TPC o /downloads/JazzSM // • If you are installing DB2 on an AIX or LINUX server, install sudo on the server. The default AIX distribution does not include sudo. To get the sudo installation package, go to http://www.ibm.com/systems/power/software/aix/linux/toolbox/date.html. Find sudo-version in the Package column, and click RPM to download the package. • Ensure that the non-root user has full sudo privileges on the server. • Ensure that the following requirements are met to enable the installation of DB2 by using the Setup wizard: o The non-root user is able to use the X Window System on the target server and the X Window System DISPLAY environment variable is set correctly for the not-root user. o The DISPLAY environment variable is preserved in the sudo environment. o The root user has access to the X Window System display that is owned by the non-root user. These display requirements do not apply if you install DB2 in silent mode. • If you are installing DB2 on an AIX server, ensure that the ODMDIR environment variable is preserved in the sudo environment. For db2 db files and tablespaces files /db2data/db2inst1/d01/* /db2data/db2inst1/d02/* /db2data/db2inst1/d03/* /db2data/db2inst1/d04/* /db2data/db2inst1/d05/* /db2data/db2inst1/d06/* /db2data/db2inst1/d07/* /db2data/db2inst1/d08/* /db2index/db2inst1/idx01/* /db2index/db2inst1/idx01/* /db2index/db2inst1/idx02/* /db2index/db2inst1/idx03/* /db2index/db2inst1/idx04/* /db2index/db2inst1/idx05/* /db2index/db2inst1/idx06/* /db2index/db2inst1/idx07/* /db2index/db2inst1/idx08/* /db2logs/logs/db2inst1/log01/* /db2logs/logs/db2inst1/log02/* /db2logs/logs/db2inst1/log03/* /db2logs/logs/db2inst1/log04/* /db2logs/logs/db2inst1/log05/* /db2logs/logs/db2inst1/log06/* /db2logs/logs/db2inst1/log07/* /db2logs/logs/db2inst1/log08/* 8 installation Install DB2 Software 8.1.1.64 or higher as root Untar IBM provided images or tar files Untar it Tar –xvf file Untar IBM provided images or tar files Untar it Tar –xvf file Will generate this one: /sys/DB2/005_ESE_AIX5_3264_SBCS1/ese.sbcsaix1 ( Or unzip file if it is zip files images : gunzip file.gz OR $ gzip -d file.gz ) Then (before need to set up for GUI on client machine) cd /sys/DB2/005_ESE_AIX5_3264_SBCS1/ese.sbcsaix1 . /db2install or ./db2_deinstall Run command as root using GUI xterm window export DISPLAY=ip:0.0 . /db2setup (install db2 server first ) Or . /db2isetup (install db2 server first ) . /db2setup (install db2 client for db2cc Control Center or for other clients to access db2 db server) . /db2setup (install db2 client for admin server for dasusr user) ( To use exceed xterm to 1. Open the xterm and client wizard on desktop after you crate exceed binary 2. 2. Click new entry using the client wizard 3. then use GUI 4. export DISPLAY=ip:0.0 5. sudo su – root 6. go to db2setup folder 7. . ./db2setup 8. will come up ) After it is done successfully, next: /usr/opt/db2_08_01/instance/db2icrt -a server –w 64 -u db2fenc1 db2inst1 9.Login as db2inst1 type: db2start Check process ps –ef |grep –i db2inst1 Create Instance All binary lib and executable and script files are create under this folder /usr/opt/db2_08_01/instance Which is linked at the instance owner account such as /people/db2isnt1/sqllib Start/Stop Instance Log in as instance owner in our case is db2isnt1 Type db2start db2stop Or db2_kill type db2 -- gets into the db2 command env prompt db2 stop database manager db2 start database manager Start/Stop db2Admin Server Process (allows connection 2 DB2 Sever) Log in as dasusr, type db2 db2admin start db2 db2admin stop 10. db2cc Use command line to run the commands or use GUI of control Center DB2 client How invoke it? At >prompt log in as db2isnt1 download IBM data studio(OEM) Type: db2cc -- ************************ -- create Database -- ************************ CREATE DATABASE tcdev ON /data/db2data/db2inst1 USING CODESET UTF-8 TERRITORY US COLLATE USING IDENTITY USER TABLESPACE MANAGED BY DATABASE USING (FILE '/data/db2data/db2inst1/tcdev_01.dat' 50000) EXTENTSIZE 16 PREFETCHSIZE 16 CATALOG TABLESPACE MANAGED BY DATABASE USING (FILE '/data/db2data/db2inst1/tcdev_system.dat' 20000) EXTENTSIZE 8 PREFETCHSIZE 8 TEMPORARY TABLESPACE MANAGED BY DATABASE USING (FILE '/data/db2data/db2inst1/tcdev_temp.dat' 20000) EXTENTSIZE 32 PREFETCHSIZE 32; Create Bufferpool first before table spaces are created on it -- ************************ -- *** create Bufferpool -- ************************ DROP BUFFERPOOL TCDEV_TCM_BP32K DROP BUFFERPOOL TCDEV_TCM_BP16K -- First connection first Connect to tcdev Create BUFFERPOOL TCDEV_TCM_BP16K IMMEDIATE SIZE 12500 PAGESIZE 16K Create BUFFERPOOL TCDEV_TCM_BP32K IMMEDIATE SIZE 100 PAGESIZE 32K -- alter BUFFERPOOL TCDEV_TCM_BP16K SIZE 12500 -- alter BUFFERPOOL TCDEV_TCM_BP32K SIZE 100 Install License: /usr/opt/db2_08_01/adm/db2lic -a filename (on aix) Create Table spaces with containers files for database We have created tcdev and dev1 on each DB server one for IT an done for support team -- ************************ -- create table spaces -- ************************ CONNECT TO tcdev Drop table space tcdev_tallydata Create regular tablespace tcdev_tallydata pagesize 32k managed by database using (file '/data/db2data/db2inst1/d07/tcdev_01.dbf' 128000, file '/data/db2data/db2inst1/d07/tcdev_tally01.dbf' 128000) extentsize 32 prefetchsize 16 Bufferpool tcdev_tcm_bp32k -- or changed its size Alter tablespace tcdev_tallydata resize (file '/data/db2data/db2inst1/d07/tcdev_01.dbf' 64000) Alter tablespace tcdev_tallydata resize (file '/data/db2data/db2inst1/d07/tcdev_tally01.dbf' 64000) CONNECT TO tcdev /db2index/db2inst1/idx02/ -- create tablespace tcdev_tallyindex Create regular tablespace tcdev_tallyindex pagesize 32k managed by database using (file '/db2index/db2inst1/idx08/tcdev_tallyidx01.dbf' 96000, file '/db2index/db2inst1/idx08/tcdev_tallyidx02.dbf' 96000) extentsize 32 prefetchsize 16 Bufferpool tcdev_tcm_bp32k CONNECT TO tcdev -- create tablespace tallyuser drop tablespace tallyuser Create regular tablespace tcdev_tallyuser pagesize 16k managed by database using (file '/data/db2data/db2inst1/d08/tcdev_tallyusr01.dbf' 125000) extentsize 32 prefetchsize 16 bufferpool tcdev_tcm_bp16k -- -- create system temp tablespace tallytemp -- cal szie of the page 2,000,000,000/32000=62500 -- create SYSTEM TEMPORARY tablespace tcdev_tallytemp pagesize 32k managed by database using (file '/data/db2data/db2inst1/d07/tcdev_tallysystemp01.dbf' 62500) extentsize 32 prefetchsize 16 bufferpool tcdev_tcm_bp32k Create SYSTEM TEMPORARY tablespace tcdev_tallytemp1 pagesize 32k managed by database using (file '/data/db2data/db2inst1/d07/tcdev_tallysystemp02.dbf' 62500) extentsize 32 prefetchsize 16 Bufferpool tcdev_tcm_bp32k -- -- create system temp tablespace tallytemp -- calculate size of the page 2,000,000,000/32000(32k)=62500 -- CREATE LONG TABLESPACE TCDEV_TALLYLONG PAGESIZE 32 K MANAGED BY DATABASE USING ( file '/data/db2data/db2inst1/d07/tcdev_tallylong01.dbf' 62500) extentsize 32 prefetchsize 16 bufferpool TCDEV_tcm_bp32k -- -- create system temp tablespace tallytemp -- cal szie of the page 2,000,000,000/32000=62500 -- CREATE USER TEMPORARY TABLESPACE TCDEV_TALLY_TMPUSR MANAGED BY database USING (file '/index/db2index/db2inst1/idx08/tcdev_tally_tmpusr.dbf' 62500) Update DB parameters and configurations It config and update according the needs of the performance and needs from the applications -- for Database UPDATE DB CFG FOR tcdev USING AUTO_MAINT ON UPDATE DB CFG FOR tcdev USING AUTO_TBL_MAINT ON UPDATE DB CFG FOR tcdev USING AUTO_RUNSTATS ON UPDATE DB CFG FOR tcdev USING AUTO_REORG ON UPDATE DB CFG FOR tcdev USING AUTO_DB_BACKUP ON -- for instance UPDATE ALERT CFG FOR DATABASE ON tcdev USING db.db_backup_req SET THRESHOLDSCHECKED YES UPDATE ALERT CFG FOR DATABASE ON tcdev USING db.tb_reorg_req SET THRESHOLDSCHECKED YES UPDATE ALERT CFG FOR DATABASE ON tcdev USING db.tb_runstats_req SET THRESHOLDSCHECKED YES -- ************************ -- update dbm cfg using dft_degree xx -- ************************ update dbm cfg using intra_parallel on update dbm cfg using max_querydegree -1 -- ************************ -- update db cfg using dft_degree -1 -- ************************ update db cfg using dft_degree -1 update db cfg using dbheap 20000 update db cfg using logbufsz 20000 update db cfg using util_heap_sz 100000 update db cfg using buffpage 12500 update db cfg using locklist 1000 update db cfg using APPGROUP_MEM_SZ 32000 update db cfg using APP_CTL_HEAP_SZ 1000 update db cfg using sortheap 10000 update db cfg using stmtheap 4096 update db cfg using applheapsz 30000 update db cfg using dlchktime 180000 update db cfg using maxlocks 60 update db cfg using num_iocleaners 100 update db cfg using num_ioservers 100 update db cfg using maxappls automatic update db cfg using logfilsiz 50000 update db cfg using logprimary 10 update db cfg using logsecond 10 -- UPDATE HEALTH NOTIFICATION CONTACT LIST ADD CONTACT "shermanzhang@gmail.com"; -- UPDATE DB CFG FOR dev1 USING AUTO_MAINT ON UPDATE DB CFG FOR dev1 USING AUTO_TBL_MAINT ON UPDATE DB CFG FOR dev1 USING AUTO_RUNSTATS ON UPDATE DB CFG FOR dev1 USING AUTO_REORG ON UPDATE DB CFG FOR dev1 USING AUTO_DB_BACKUP ON UPDATE ALERT CFG FOR DATABASE ON dev1 USING db.db_backup_req SET THRESHOLDSCHECKED YES UPDATE ALERT CFG FOR DATABASE ON dev1 USING db.tb_reorg_req SET THRESHOLDSCHECKED YES UPDATE ALERT CFG FOR DATABASE ON dev1 USING db.tb_runstats_req SET THRESHOLDSCHECKED YES Grants users privileges to DB -- ************************ -- GRANT USE OF TABLESPACE -- tallytemp_user TO USER techuser -- not done yet -- ************************ connect to tcdev GRANT USE OF TABLESPACE TCDEV_TALLYDATA TO USER techuser GRANT USE OF TABLESPACE TCDEV_TALLYINDEX TO USER techuser GRANT USE OF TABLESPACE TCDEV_TALLYUSER TO USER techuser GRANT USE OF TABLESPACE TCDEV_TALLYLONG TO USER techuser GRANT USE OF TABLESPACE TCDEV_TALLY_TMPUSR TO USER techuser GRANT CONNECT ON DATABASE TO techuser GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO techuser GRANT CREATE_NOT_FENCED_ROUTINE ON DATABASE TO techuser GRANT CREATETAB ON DATABASE TO techuser GRANT SELECT ON SYSIBM.SYSDUMMY1 TO USER techuser DISCONNECT ALL connect to tcdev GRANT USE OF TABLESPACE TCDEV_TALLYDATA TO USER techsys GRANT USE OF TABLESPACE TCDEV_TALLYINDEX TO USER techsys GRANT USE OF TABLESPACE TCDEV_TALLYUSER TO USER techsys GRANT USE OF TABLESPACE TCDEV_TALLYLONG TO USER techsys GRANT USE OF TABLESPACE TCDEV_TALLY_TMPUSR TO USER techsys GRANT CONNECT ON DATABASE TO techsys GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO techsys GRANT CREATE_NOT_FENCED_ROUTINE ON DATABASE TO techsys GRANT CREATETAB ON DATABASE TO techsys GRANT SELECT ON SYSIBM.SYSDUMMY1 TO USER techsys Configuration Parameters screenshots for DB tcdev 9/6/2011 at COM: