BlueCARE export :
db2 "export to bodb.csv of del select varchar(c.name,60) customer,char(slc.customers_code,3) customers_code, varchar(sp.name,20) service_responsibility,sln.nodes_name from portal.SERVICELEVELPARTY_CUSTOMER slc join portal.customer c on c.code = slc.customers_code join portal.SERVICELEVELPARTY_node sln on sln.SERVICELEVELPARTIES_SLPID = slc.SERVICERESPONSIBILITY_SLPID join portal.SERVICELEVELPARTY sp on sp.slpid = slc.SERVICERESPONSIBILITY_SLPID join portal.supportgroup sg on sg.supportgroupid = sp.SUPPORTGROUP_SUPPORTGROUPID where sg.name like 'BRNO BODB%' order by c.name with ur"Random MAC generator :
echo -n 00:1A:64 ; for i in `seq 1 3` ; do echo -n `echo ":$RANDOM$RANDOM" | cut -n -c -3` ;done; echo \n
# SAMPLE database has to be created (db2sampl)
db2 connect to <DBNAME>
db2 "CREATE TABLE LOGTEST4 (
EMP_ID integer,
DATE_OF_BIRTH date,
SALARY integer,
EMP_NAME char(10)
)"
while true; do db2 "INSERT INTO LOGTEST4 (EMP_ID, DATE_OF_BIRTH, SALARY, EMP_NAME)
WITH EMP_IDS(EMP_ID) AS
( VALUES(1) UNION ALL
SELECT EMP_ID+1 FROM EMP_IDS WHERE EMP_ID < 1000 )
SELECT EMP_ID,
CURRENT DATE - ((18 * 365) + RAND()*(47*365)) DAYS,
INTEGER(50000 + RAND()*90000),
TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdefgHij', '1234567890' )
FROM EMP_IDS" && sleep 2; done
Loop snap :
while true; do date | tee -a mon_UOW_appl.out; echo 'Number of UOW Exec' | tee -a mon_UOW_appl.out; db2 list applications show detail | grep -i "UOW Executing" | wc -l | tee -a mon_UOW_appl.out; echo 'Number of UOW Waiting' | tee -a mon_UOW_appl.out; db2 list applications show detail | grep -i "UOW Waiting" | wc -l | tee -a mon_UOW_appl.out; echo 'Number of listed appls' | tee -a mon_UOW_appl.out; db2 list applications | wc -l | tee -a mon_UOW_appl.out; echo 'collection list application' | tee -a mon_UOW_appl.out; db2 list applications show detail >> List_appl.out; echo '---------' | >> List_appl.out; sleep 300; done
Mailer for the loop snap :
#!/bin/ksh # agregate the collected data and send it by mail every hour MAIL_TS=`date +'%H:%M'` [email protected] MAIL_SUBJECT="MEDIAW_P Performance monitoring statistics from $MAIL_TS" #MAIL_TO="[email protected]" MAIL_TO="[email protected]" #MAIL_CC="[email protected],[email protected],[email protected],[email protected]" #MAIL_CC="" MAIL_EML="/db2dump/mw_prod/snaps_20140924/mon_mail_${MAIL_TS}.eml" SNAP_OUT="/db2dump/mw_prod/snaps_20140924/mon_UOW_appl.out" # prepare the mail cat > $MAIL_EML <<!EOF From:${MAIL_FROM} To:${MAIL_TO} Cc:${MAIL_CC} Subject:${MAIL_SUBJECT} Statistics collected from the loop output in $SNAP_OUT : !EOF # collect the statistics # Average echo "Average number of UOW Executed up-to now : `sed -n '/Exec/,/Waiting/P' $SNAP_OUT | egrep '[0-9]' | awk '{cnt+=$1; num+=1} END{print cnt/num}'`" >> $MAIL_EML echo "Average number of UOW Waiting up-to now : `sed -n '/Waiting/,/appls/P' $SNAP_OUT | egrep '[0-9]' | awk '{cnt+=$1; num+=1} END{print cnt/num}'`" >> $MAIL_EML echo "Average number of Applications connected up-to now : `sed -n '/appls/,/list/P' $SNAP_OUT | egrep '[0-9]' | awk '{cnt+=$1; num+=1} END{print cnt/num}'`" >> $MAIL_EML # Top echo "Top number of UOW Executed up-to now : `sed -n '/Exec/,/Waiting/P' $SNAP_OUT | egrep '[0-9]' | sort | awk '{print $1}' | tail -1`" >> $MAIL_EML echo "Top number of UOW Waiting up-to now : `sed -n '/Waiting/,/appls/P' $SNAP_OUT | egrep '[0-9]' | sort | awk '{print $1}' | tail -1`" >> $MAIL_EML echo "Top number of Applications connected up-to now : `sed -n '/appls/,/list/P' $SNAP_OUT | egrep '[0-9]' | sort | awk '{print $1}' | tail -1`" >> $MAIL_EML # Send the mail /usr/sbin/sendmail -t < $MAIL_EML exit 0
Mailer for loop - FINAL :
#!/bin/ksh # agregate the collected data and send it by mail every hour MAIL_TS=`date +'%H:%M'` [email protected] MAIL_SUBJECT="MEDIAW_P Performance monitoring statistics from $MAIL_TS - FINAL" MAIL_TO="[email protected]" MAIL_CC="[email protected],[email protected],[email protected],[email protected],[email protected]" MAIL_EML="/db2dump/mw_prod/snaps_20140924/mon_mail_${MAIL_TS}_FINAL.eml" SNAP_OUT="/db2dump/mw_prod/snaps_20140924/mon_UOW_appl.out" # prepare the mail cat > $MAIL_EML <<!EOF From:${MAIL_FROM} To:${MAIL_TO} Cc:${MAIL_CC} Subject:${MAIL_SUBJECT} Statistics collected from the loop output in $SNAP_OUT since the loop script was started. Complete output of the loop is attached as mon_UOW_appl.out. !EOF # collect the statistics # Average echo "Average number of UOW Executed up-to now : `sed -n '/Exec/,/Waiting/P' $SNAP_OUT | egrep '[0-9]' | awk '{cnt+=$1; num+=1} END{print cnt/num}'`" >> $MAIL_EML echo "Average number of UOW Waiting up-to now : `sed -n '/Waiting/,/appls/P' $SNAP_OUT | egrep '[0-9]' | awk '{cnt+=$1; num+=1} END{print cnt/num}'`" >> $MAIL_EML echo "Average number of Applications connected up-to now : `sed -n '/appls/,/list/P' $SNAP_OUT | egrep '[0-9]' | awk '{cnt+=$1; num+=1} END{print cnt/num}'`" >> $MAIL_EML # Top echo "Top number of UOW Executed up-to now : `sed -n '/Exec/,/Waiting/P' $SNAP_OUT | egrep '[0-9]' | sort | awk '{print $1}' | tail -1`" >> $MAIL_EML echo "Top number of UOW Waiting up-to now : `sed -n '/Waiting/,/appls/P' $SNAP_OUT | egrep '[0-9]' | sort | awk '{print $1}' | tail -1`" >> $MAIL_EML echo "Top number of Applications connected up-to now : `sed -n '/appls/,/list/P' $SNAP_OUT | egrep '[0-9]' | sort | awk '{print $1}' | tail -1`" >> $MAIL_EML # Attach the loop output uuencode $SNAP_OUT $SNAP_OUT >> $MAIL_EML # Send the mail /usr/sbin/sendmail -t < $MAIL_EML exit 0
Convert unix timestamp to timestamp :
db2 "values timestamp('1970-01-01-00.00.00') + 1469536340 seconds"
Prasecina :
WITH SYSIBM.SQLFOREIGNKEYS( PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, FKCOLUMN_NAME, KEY_SEQ, UPDATE_RULE, DELETE_RULE, F K_NAME, PK_NAME, DEFERRABILITY, UNIQUE_OR_PRIMARY ) AS (SELECT CAST( NULL AS VARCHAR(128) ), varchar(RTRIM(pktabinfo.CLI_REQ_SCHEMA), 128), varchar(pktabinfo.CLI_REQ_TABLE, 128), v archar(pk.COLNAME, 128), CAST( NULL AS VARCHAR(128) ), varchar(RTRIM(fktabinfo.CLI_REQ_SCHEMA), 128), varchar(fktabinfo.CLI_REQ_TABLE, 128), varchar(fk.COLNAME, 128), pk.COLSEQ, small int( CASE UPDATERULE WHEN 'C' then 0 WHEN 'R' then 1 WHEN 'N' then 2 WHEN 'A' then 3 END), smallint( CASE DELETERULE WHEN 'C' then 0 WHEN 'R' then 1 WHEN 'N' then 2 WHEN 'A' then 3 END), varchar(fk.CONSTNAME, 128), varchar(pk.CONSTNAME, 128), smallint(7), CASE TC.CONSTRAINTYP WHEN 'P' THEN CAST( 'PRIMARY' AS CHAR(7) ) WHEN 'U' THEN CAST( 'UNIQUE' AS CHAR(7) ) END FROM SYSIBM.SYSRELS as rels, SYSIBM.SYSKEYCOLUSE as pk, SYSIBM.SYSKEYCOLUSE as fk, SYSIBM.SYSTABCONST as tc, (SELECT CASE systab.TYPE WHEN 'A' then systab.BASE_SCHEMA ELSE systab.CREATOR END as CLI_BASE_SCHEMA, CASE systab.TYPE WHEN 'A' then systab.BASE_NAME ELSE systab.NAME END as CLI_BASE_TABLE, systab.CREATOR as CLI_REQ_SCHEMA, systab.NAME as CLI_REQ_TABLE FROM SYSIBM.SYSTABLES systab WHERE s ystab.CREATOR = ? AND systab.NAME = ? ) as fktabinfo, (SELECT CASE systab.TYPE WHEN 'A' then systab.BASE_SCHEMA ELSE systab.CREATOR END as CLI_BAS E_SCHEMA, CASE systab.TYPE WHEN 'A' then systab.BASE_NAME ELSE systab.NAME END as CLI_BASE_TABLE, systab.CREATOR as CLI_REQ_SCHEMA , systab.NAME as CLI_REQ_TABLE FROM SYSIBM.SYSTABLES systab ) as pktabinfo WHERE rels.REFTBCREATOR = pktabinfo.CLI_BASE_SCHEMA AND rels.REFTBNAME = pktabinfo.CLI_BAS E_TABLE AND rels.CREATOR = fktabinfo.CLI_BASE_SCHEMA AND rels.TBNAME = fktabinfo.CLI_BASE_TABLE AND pk.colseq = fk.colseq AND pk.tbname = rels.reftbname AND pk.tbcreat or = rels.reftbcreator AND fk.tbname = rels.tbname AND fk.tbcreator = rels.creator AND pk.constname = rels.refkeyname AND fk.constname = rels.relname AND tc.name = rels.refkeyname AND tc.tbname = pk.tbname AND tc.tbcreator = pk.tbcreator ) SELECT PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, FKCOLUMN_NAME, KE Y_SEQ, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY FROM SYSIBM.SQLFOREIGNKEYS WHERE FKTABLE_SCHEM = ? AND FKTABLE_NAME = ? ORDER BY 1,2,3,9
In this task we:
1) Perform offline backup which we restore
2) Change the database to archivelog mode
3) Perform online backup + archivelogs
4) Restore database and roll forward:
--excercise 4
--verify if database is running in circular or archival mode
connect to NEWDB
db2 get db cfg | grep LOGARCHMETH
-- First log archive method (LOGARCHMETH1) = OFF
-- Second log archive method (LOGARCHMETH2) = OFF
--do offline backup of database and note timestamp of backup
backup database NEWDB
--delete database NEWDB
drop database NEWDB
--restore database NEWDB from previous offline backup
restore database NEWDB from '/home/db2inst1' taken at <backup_timestamp>
--create new table inside tablespace USERSPACE1
db2 connect to NEWDB
db2 "create table cust(id integer, name varchar(20))in USERSPACE1"
--create unique index
db2 "create unique index i1_cust on cust(id)"
--see table definition
db2 describe table cust
--verify indexes defined on table cust
db2 describe indexes for table cust
--insert some data
db2 "insert into cust values(1, 'Pavel')"
--insert another data
db2 "insert into cust values(1, 'Jana')"
--Cant be inserted due to constraint on column ID where is defined unique key
db2 "insert into cust values(2, 'Jana')"
--insert some data without commit
-- +c will turn off autocommit
db2 +c "insert into cust values(3, 'Peter')"
--on 2nd session see contain of table
db2 "select * from cust"
--by specifiying UR - uncommited read you can see changes in tables which werent commited yet
db2 "select * from cust with ur"
--So this change was most probably also stored on disk, inside container, but still not commited
--now kill db2 instance
ps -ef | grep db2sysc
[db2inst1@db2_fund ~]$ ps -ef | grep db2sysc
db2inst1 1564 1562 0 03:20 ? 00:01:01 db2sysc 0
db2inst1 11810 11224 0 06:34 pts/2 00:00:00 grep --color=auto db2sysc
[db2inst1@db2_fund ~]$ kill -9 1564
--on 1 session try to commit your transaction (insert statement)
db2 commit
--you will receive
--SQL1224N The database manager is not able to accept new requests, has
--terminated all requests in progress, or has terminated the specified request
--because of an error or a forced interrupt. SQLSTATE=55032
--start database manager and activate database
db2start
db2 activate db newdb
--see contain of diagnostic file, last lines
--location of diagnostic file
db2 get dbm cfg | grep DIAGPATH
less /home/db2inst1/sqllib/db2dump/DIAG0000/db2diag.log
--You should find following records, so crash recovery was done
--MESSAGE : ADM1530I Crash recovery has been initiated.
--Starting recovery replay phase
--End of redo phase.
--Crash recovery completed.
--see contain of table: 3rd line with 'Peter' was rollback
db2 "select * from cust"
--switch database to archive log mode
--create path for archived logs
mkdir /home/db2inst1/arch_logs
--setup first archivation method to disk
db2 update db cfg using LOGARCHMETH1 DISK:/home/db2inst1/arch_logs
--disconnect and deactivate database
db2 terminate
db2 deactivate database NEWDB
--try to connect again
db2 connect to NEWDB
--switching from circular logging mode to archive logging mode require backup to be taken
--SQL1116N A connection to or activation of database "NEWDB" failed because the
--database is in BACKUP PENDING state. SQLSTATE=57019
--try to run online backup
db2 backup database NEWDB online
--offline backup must be taken
db2 backup database NEWDB
--try to connect
db2 connect to NEWDB
--perform online backup + note backup timestamp
--online backup can be done while database is active and applications are connected
db2 backup database NEWDB online
--delete database
db2 drop database NEWDB
--restore database
db2 "restore database newdb taken at <timestamp_of_backup>"
--connect to database
db2 connect to NEWDB
--Connection isnt possible because in archive log mode we have to apply logs as well
--SQL1117N A connection to or activation of database "NEWDB" cannot be made
--because of ROLL-FORWARD PENDING. SQLSTATE=57019
--rollforward database - apply all possible logs
db2 "rollforward database newdb to end of logs and complete"
--connection to database should be possible now
db2 connect to NEWDB
--excercise 5 --create table create table BANK(id SMALLINT not null,name varchar(20),address varchar(50)) --insert data insert into BANK values (1, 'KB' ,'Brno'),(2, 'CSOB' ,'Zlin'),(3, 'CSOB' ,'Ostrava'),(4, 'CS' ,'Prague'),(5, 'CS' ,'Usti'),(6, 'CS' ,'Zlin') --export data export to BANK.ixf of ixf "select * from BANK" --import table, using INSERT method records are added to existing db2 import from BANK.ixf of ixf insert into BANK --we have 12 rows --import table, now using REPLACE, table is truncated before inserting db2 import from BANK.ixf of ixf replace into BANK --back to 6 rows --verify you are running in archive log mode, if not switch to archive log mode --db2 update db cfg using LOGARCHMETH DISK:/home/db2inst1/arch_logs/ --db2 backup database SAMPLE db2 get db cfg | grep LOGARCHMETH1 --export DDL for table BANK db2look -d SAMPLE -t BANK -e -o BANK.sql --delete table db2 drop table BANK --recreate table by script generated by db2look command db2 -tvf BANK.sql -l BANK.out --load table using COPY NO db2 load from BANK.ixf of ixf insert into BANK copy no --with COPY NO tablespace is put to backup pending state, see status of USERSPACE1 tablespace --You should find: State = 0x0020 db2 list tablespaces db2tbst 0x0020 --perform tablespace level backup for tablespace USERSPACE1 db2 "backup database SAMPLE tablespace(SYSCATSPACE, USERSPACE1) online" --verify status of tablespace again, should be back in normal state db2 list tablespaces --see syntax of backup image, tablespace level backup are marked 3 in compare 0 for full backups ls -latr *001