====== Bordel ======

**BlueCARE export :**
<sxh bash>
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"
</sxh>
**Random MAC generator : **
<sxh bash>
echo -n 00:1A:64 ; for i in `seq 1 3` ; do echo -n `echo ":$RANDOM$RANDOM" | cut -n -c -3` ;done; echo \n
</sxh>

== Infinite insert of random data ==
<sxh bash>
# 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
</sxh>

**Loop snap :**
<sxh bash>
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
</sxh>

**Mailer for the loop snap :**
<sxh bash>
#!/bin/ksh

# agregate the collected data and send it by mail every hour
MAIL_TS=`date +'%H:%M'`
MAIL_FROM=lukas_dierze@cz.ibm.com
MAIL_SUBJECT="MEDIAW_P Performance monitoring statistics from $MAIL_TS"
#MAIL_TO="marco_graglia@it.ibm.com"
MAIL_TO="lukas_dierze@cz.ibm.com"
#MAIL_CC="emanuele_zanotti@nscsrl.it,marco.panzone@it.ibm.com,lukas_dierze@cz.ibm.com,lupia_fabio@it.ibm.com"
#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
</sxh>

**Mailer for loop - FINAL :**
<sxh bash>
#!/bin/ksh

# agregate the collected data and send it by mail every hour
MAIL_TS=`date +'%H:%M'`
MAIL_FROM=lukas_dierze@cz.ibm.com
MAIL_SUBJECT="MEDIAW_P Performance monitoring statistics from $MAIL_TS - FINAL"
MAIL_TO="marco_graglia@it.ibm.com"
MAIL_CC="emanuele_zanotti@nscsrl.it,marco.panzone@it.ibm.com,lukas_dierze@cz.ibm.com,lupia_fabio@it.ibm.com,petr.pastorcak@cz.ibm.com"
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
</sxh>

**Convert unix timestamp to timestamp :**
<sxh bash>
db2 "values timestamp('1970-01-01-00.00.00') + 1469536340 seconds"
</sxh>

**Prasecina :**
<sxh bash>
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
</sxh>


-----

====DB2 Tasks====

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:


<sxh bash>
--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


</sxh>



<sxh>
--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


</sxh>