This is an old revision of the document!
Bordel
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
Infinite insert of random data
# 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