DB2 load data
db2 “import from filename.del of del method p (1, 2) messages output.log insert into . (col1, col2)”
db2 “load client from filename.del of del insert into . copy yes to /dev/null” — worked. set proper access to del file, say 770, 755. Follow this by set integrity below.
db2 “set integrity for . immediate checked”
load from /dev/null of del replace into .;
db2 “set integrity for . immediate checked”
DB2 Set path
export PATH=$PATH:/opt/ibm/db2/V9.7/bin:
DB2 Alter table (Drop not null)
alter table alter column drop not null;
reorg table ;
DB2 describe index
db2 describe indexes for table . show detail
DB2 backup restore
db2 attach to db2 user using db2 backup database user using to c:/db2backups
db2 restore database user using from c:/db2backups taken at 20160408153909 into
db2 restart database
db2 list tablespaces show detail
DB2 Catalog
catalog tcpip node node2 remote server catalog database as at node node2
DB2 User Defined Function
CREATE FUNCTION DB2ADMIN.GETXSDDATETIME(mdmtimestamp VARCHAR(100))
RETURNS VARCHAR(100)
EXTERNAL NAME ‘ASIDATEUTIL!GETXSD’
LANGUAGE java
PARAMETER STYLE JAVA
DETERMINISTIC
FENCED
NOT NULL CALL
NO SQL
NO EXTERNAL ACTION
NO SCRATCHPAD
ALLOW PARALLEL;
DRIVER LOCATION: db2jcc.jar and db2jcc_license_cu.jar
(Both of these jars must be included)
JDBC URL FORMAT: jdbc:db2://[:]/
JDBC URL Examples:
jdbc:db2://127.0.0.1:50000/SAMPLE
IBM DB2 Universal Driver Type 2
——————————————————————————–
DRIVER CLASS: com.ibm.db2.jcc.DB2Driver
DRIVER LOCATION: db2jcc.jar and db2jcc_license_cu.jar
(Both of these jars must be included)
JDBC URL FORMAT: jdbc:db2:
JDBC URL Examples:
jdbc:db2:sample
DB2 Execute SQL files
db2 -tvf upd_inqlvlquery.sql | tee upd_inqlvlquery.log
db2 -td; -vf triggers.sql
DB2 Query CLOB
SELECT CAST(CAST( AS CLOB(1073741824)) as varchar(32000)) FROM .;
SELECT XMLSERIALIZE( AS VARCHAR(10000)) FROM .;
DB2 current timestamp
select current timestamp as runtime from sysibm.sysdummy1;
Linux
cd /opt/IBM/MDM/HTTP*/logs
x=$(grep ‘ 200 ‘ access_log | grep -c ’20/Nov/2017’)
y=$(grep ‘ 500 ‘ access_log | grep -c ’20/Nov/2017’)
z=$((x+y))
s=$((x*100/z))
echo Found : $x
echo Not Found: $y
echo Found %: $s
// Delete lines from a file that match a pattern
sed –in-place ‘/matchpattern/d’ filename
TAR
tar czvf sher.tgz /apps/temp/mainDir –exclude=/apps/temp/mainDir/subDir2
Run jar
java -cp jarname.jar arguments
Change mod to be writable by group (eg: chmod -R g+w folder_name) after creation or replacement. Otherwise you might lose the privilege as the sudo restart will change the owner back to admin.
Please don’t use 777, use 775. The developers are all part of group and you wouldn’t want other people delete the whole folder.
// remove duplicates
sort garbage.txt | uniq -u
word count
wc -l < file.txt
Linux Version Info
$ cat /etc/*-release
$ uname -a
linux bit info
$ uname -m
update jar
jar uf name.jar com jar uf AdaptiveServiceInterface.jar com
Enable SAM (Service Activity Monitoring)
SELECT * FROM CONFIGELEMENT WHERE NAME LIKE ‘%Report%’;
UPDATE CONFIGELEMENT SET VALUE = ‘true’, LAST_UPDATE_DT = CURRENT TIMESTAMP WHERE NAME LIKE ‘%Report%’;
Enable performance monitoring log
UPDATE .CONFIGELEMENT SET VALUE = ‘true’, LAST_UPDATE_DT = CURRENT TIMESTAMP WHERE NAME LIKE ‘%Performance%enabled’;
UPDATE .CONFIGELEMENT SET VALUE = 6, LAST_UPDATE_DT = CURRENT TIMESTAMP WHERE NAME = ‘/IBM/DWLCommonServices/PerformanceTracking/level’;
Enable PME Timer log
========================================================================================
To change the logging levels, In WebSphere Application Server admin console, add / set the following in logging of the InfoSphere MDM application server.
Login WebSphere console, navigate to
Troubleshooting – > Logs and trace – > Your MDM Server – > Diagnostic trace service
– You can define your own trace file name and maximum file size, or use the default (trace.log)
– Select “Change Log Detail Levels”, append current log level with the following to enable timer log.
*=info: com.ibm.mdm.eme.core.Timer=fine
With persistent changes (configured on Configuration Tab), MDM restart is required. With temporary changes (configured on Runtime Tab), changes will be taken affect without MDM restart.
Configelement comparison
——————————
SELECT SRC1.NAME, SRC1.VALUE, SRC1.VALUE_DEFAULT, SRC2.VALUE, SRC2.VALUE_DEFAULT FROM
(SELECT NAME, VALUE, VALUE_DEFAULT FROM CONFIGELEMENT
EXCEPT
SELECT NAME, VALUE, VALUE_DEFAULT FROM .CONFIGELEMENT) SRC1 LEFT OUTER JOIN
(SELECT NAME, VALUE, VALUE_DEFAULT FROM .CONFIGELEMENT
EXCEPT
SELECT NAME, VALUE, VALUE_DEFAULT FROM CONFIGELEMENT) SRC2 ON SRC1.NAME = SRC2.NAME
UNION
SELECT SRC1.NAME, SRC1.VALUE, SRC1.VALUE_DEFAULT, SRC2.VALUE, SRC2.VALUE_DEFAULT FROM
(SELECT NAME, VALUE, VALUE_DEFAULT FROM .CONFIGELEMENT
EXCEPT
SELECT NAME, VALUE, VALUE_DEFAULT FROM CONFIGELEMENT) SRC1 LEFT OUTER JOIN
(SELECT NAME, VALUE, VALUE_DEFAULT FROM CONFIGELEMENT
EXCEPT
SELECT NAME, VALUE, VALUE_DEFAULT FROM .CONFIGELEMENT) SRC2 ON SRC1.NAME = SRC2.NAME;
IBM Log Analyzer for MDM
SAM Analysis (Run from IBM HRE 1.7 – C:\IBM\SDP\runtimes\base_v7_stub\java\jre)
set JRE_HOME=C:\IBM\SDP\runtimes\base_v7_stub\java\jre
set JRE_HOME=C:\IBM\SDP\runtimes\base_v8_stub\java\jre
java -jar C:\Progra~2\IBM\LogAnalyzer-11.5.0\LogAnalyzer\LogAnalyzer-11.5.0.jar -l MDMSamLog -f C:\Progra~2\IBM\LogAnalyzer-11.5.0\LogAnalyzer\logs\transactiondata.log -ts “2016-05-06 06:00:00” -te “2016-05-06 11:00:00” -c n -y n -p “May06Forenoon” -d C:\Progra~2\IBM\LogAnalyzer-11.5.0\LogAnalyzer\results
Scripts
// Self attempt – Working
// works for ASI
tail -n 0 -F /apps/IBM/WebSphere/AppServer/profiles//logs/RequestResponseMessage.log | \
while read LINE; do
if [[ $LINE == *Service*Operation* ]]; then
echo “$LINE” | sed -e ‘s/2.*//’ -e ‘s/<\/ServiceTime>.*/,/’ -e ‘s/<\/Operation>/,/’ -e ‘s/<\/Name>//’ -e ‘s/<\/Version>.*//’
elif [[ $LINE == *ServiceTime\>\<DWLControl* ]]; then
value=”$(echo “$LINE” | sed -e ‘s/2.*<port://’ -e ‘s/Response .*/,/’ -e ‘s/<\/ServiceTime>.*//’)”
echo ${value##*,},${value%,*}
elif [[ $LINE == *ResultCode*FATAL* ]]; then
echo “$LINE” | sed -e ‘s/2.*//’ -e ‘s/<\/mdm:ServiceTime>.*/,/’ -e ‘s/<\/mdm:RequestType>.*//’
fi
done
MYVAR=”/var/cpanel/users/joebloggs:DNS9=domain.com”
NAME=${MYVAR%:*} # get the part before the colon
NAME=${NAME##*/} # get the part after the last slash
echo $NAME
declare -A animals=( [“key1″]=”val1” [“key2″]=”val2”)
// Self attempt – Working
// works for ASI
tail -n 0 -F RequestResponseMessage.log | \
while read LINE; do
if [[ $LINE == *Service*Operation* ]]; then
echo “$LINE” | sed -e ‘s/2.*//’ -e ‘s/<\/ServiceTime>.*/,/’ -e ‘s/<\/Operation>/,/’ -e ‘s/<\/Name>//’ -e ‘s/<\/Version>.*//’
elif [[ $LINE == *ServiceTime\>\<DWLControl* ]]; then
echo “$LINE” | sed -e ‘s/2.*<port://’ -e ‘s/Response .*/,/’ -e ‘s/<\/ServiceTime>.*//’
elif [[ $LINE == *ResultCode*FATAL* ]]; then
echo “$LINE” | sed -e ‘s/2.*//’ -e ‘s/<\/mdm:ServiceTime>.*/,/’ -e ‘s/<\/mdm:RequestType>.*//’
fi
done
// Missing admin client ids loop
while read -r var;
do
db2 -x “select count(*) from contequiv where admin_client_id = ‘$var’ and admin_sys_tp_cd = 100007 and x_end_date is not null” |
while read -r dbvar;
do
if [ “$dbvar” -eq 0 ]; then
echo $var >> missingAdminIds.out
fi
done
done < adminIds.txt
extract failed requests
grep -rh ‘,’ * | sed ‘s/<\/TCRMService>\,//g’ > personRetry.txt
JMeter
———–Ant Trigger with HTML Report——————
C:\Users\sheriff\JMeter\ProjectFolder>ant -Dshow-data=y -Dtest=jmeterprojectfile -Djmeter.home=C:\apache-jmeter-3.2
ant -Dshow-data=y -Dtest=jmeterprojectfile -Djmeter.home=C:\apache-jmeter-3.2
Buildfile: C:\Users\Sheriff\JMeter\ProjectFolder\build.xml
———–JMeter Non GUI Trigger with APDEX Report——————
cd C:\Users\sheriff\JMeter\ProjectFolder
jmeter -n -t file.jmx -l out.csv -e -o “C:\Users\sheriff\JMeter\ProjectFolder\out”
JMeter Scripts
BirthDateIncrement
${__javaScript(var d = new Date();d.setFullYear(0001\,0\,1);d.setDate(d.getDate() + ${__counter(FALSE)});new Date(d.getTime()).toISOString().substring(0\,10);)}
BirthDateRandom
${__javaScript(var d = new Date();d.setDate(d.getDate() – ${__Random(0,100000)});new Date(d.getTime()).toISOString().substring(0\,10);)}
Current time including ms
${__javaScript(var d = new Date();new Date(d.getTime()).toISOString();)}
${__javaScript(var d = new Date();new Date(d.getTime());)}
${__javaScript(var d = new Date();new Date(d.getTime(yyyy-mm-dd hh:mm:ss));)}
BirthDate
${__time(yyyy-MM-dd)}
${__javaScript(var d = new Date();d.setDate(d.getDate() – ${__Random(0,100000)});new Date(d.getTime()).toISOString().substring(0\,10);)}
// Find CRs between two baselines
cleartool diffbl -activities baseline:@/vobs/ | grep -v “deliver”
cleartool diffbl -activities baseline:@\/vobs\/IBM_Proj “/ – /g’ | sed ‘s/”//g’ | sed ‘s/>>//g’ | sed ‘s/->//g’
// Baseline diff – activity details
cleartool setview sheriff_Domain_Release_int_view
cleartool diffbl -activities baseline:@\/vobs\/IBM_Proj “/ – /g’ | sed ‘s/”//g’ | sed ‘s/>>//g’ | sed ‘s/->//g’ > bsldiff.log
@/vobs/
while read -r var;
do
cr=$(echo $var | cut -c1-10)
echo $cr
cleartool descr -l activity:$cr@/vobs/
done < bsldiff.log > sher.log
// Baseline comparison
–working–
cd ~
cd shershell
rm -f bsldiffdetails.txt
cleartool setview sheriff_Domain_Release_int_view
cleartool diffbl -activities baseline:@\/vobs\/IBM_Proj “/ – /g’ | sed ‘s/”//g’ | sed ‘s/>>//g’ | sed ‘s/->//g’ | grep “CR00*” | grep “deliver” > bsldiffdetails.log
while read -r var;
do
cr=$(echo $var | cut -c1-10)
echo $cr
cleartool descr -l activity:$cr@/vobs/ > acttemp.log
cat acttemp.log >> bsldiffdetails.txt
done < bsldiffdetails.log
exit 0
// Stream activity details
–working–
cd ~
cd shershell
rm -f strdayact.txt
cleartool setview sheriff_Domain_Release_view
cleartool describe stream:Domain_Release_int@/vobs/ | grep “CR00*” > temp.log
tac temp.log >> strallact.log
while read -r var;
do
cr=$(echo $var | cut -c1-10)
echo $cr
today=$($echo date +”%Y-%m-%d”)
echo $today
cleartool descr -l activity:$cr@/vobs/ > acttemp.log
if grep -q “$today” acttemp.log; then
cat acttemp.log >> strdayact.txt
else
break
fi
done < strallact.log
rm *.log
exit 0
DOS
findstr /S “string1” “string2” c:\users\sheriff\*.txt > sher.txt
DOS port usage
Display all ports – netstat -n
Display a specific port – netstat -n | find “2810”
DOS Runas
C:\Users\Sheriff>runas /noprofile /user:db2admin “C:\Program Files (x86)\IBM\SQLLIB\BIN\db2cw.bat”
Enter the password for db2admin:
Attempting to start C:\Program Files (x86)\IBM\SQLLIB\BIN\db2cw.bat as user “db2admin” …
WAS
The below setting was used to terminate long running queries that was triggered from the transaction after 120 seconds.
webSphereDefaultQueryTimeout = 120 (In seconds)
syncQueryTimeoutWithTransactionTimeout = false
=”‘”&A1&”‘,”
— Excel Macro to save multiple tabs in a sheet to multiple csv files
Public Sub SaveWorksheetsAsCsv()
Dim WS As Excel.Worksheet
Dim SaveToDirectory As String
SaveToDirectory = “C:\”
For Each WS In ThisWorkbook.Worksheets
WS.SaveAs SaveToDirectory & WS.Name, xlCSV
Next
com.ibm.websphere.ejbcontainer.poolSize
Specifies the size of the pool for the specified bean type. This property applies to stateless, message-driven, and entity beans. If you do not specify a default value, the container default value, 50 and 500, are used.
Set the pool size for a given entity bean as: beantype=[H]min,[H]max [:beantype=[H]min,[H]max…]
The beantype element is the Java EE name of the bean, formed by concatenating the application name, the # character, the module name, the # character, and the name of the bean, that is, the string assigned to the field in the deployment descriptor of the bean. The min and max elements are the minimum and maximum pool sizes for that bean type. Do not specify the square brackets shown in the previous prototype; they denote optional additional bean types that you can specify after the first bean type. Each bean type specification is delimited by a colon (:).
Use an asterisk (*) as the value of beantype to indicate that all bean types are to use those values unless overridden by an exact bean-type specification somewhere else in the string; for example: *=30,100
To specify a default value, omit either the min or max value but retain the comma (,) between the two values; for example:
Dcom.ibm.websphere.ejbcontainer.poolSize=ivtApp#ivtEJB.jar#ivtEJBObject=125,1327
Shell1 – Run Windows cmd shell as Adminstrator
cd C:\mysql-5.7.22-winx64
mkdir data
bin\mysqld –initialize –console
alter user ‘root’@’localhost’ identified by ‘root’; — Write this line to init.txt
bin\mysqld –init-file=C:\mysql-5.7.22-winx64\init.txt
delete init.txt
Shell2 – Run a new Windows cmd shell as Adminstrator
mysql -u root -p <new password set in init.txt>
create database datacruncher;
techtrail 