1. tracer.pl "queuename" usage: tracer.pl "XF_TOPOSGRD" Description: Trace the records from inbound queue to outbound queue base on the queue name and key value. Implementation: a. define transaction table to contain a transaction id, trasaction name, related inbound, outbound, map id. b. tracer.pl use queuename check against the inbound's queuename, and get related transaction information. c. get the map id's primay key information as select criteria and prompt input for the key field eg. #>tracer.pl "XF_TOPOSGRD" please input - xf_datetime: 2008-02-25 15:14:53 xf_grstorecode:
xf_grno: 110004_056 xf_sku: A09w38403201001
return: a. Send out at successfully b. Fail:
2. showqueuename.pl "[inbound|outbound]" "%asn%" usage: showqueuename.pl "inbound" "%grd%" Description: show the full tables name that match the criteria return: inbound_xf_toposgrd
3. checkalltransactionstatus.pl Description: it is an nagios plugin that will check the ws1' inbound and outbound 's all transaction status_flag, if found status_flag=2, it will give an critical alert. return: a. OK b. Problem:
:
4. checkintegrity.pl Description: it will dump the mysql's schema out and compare with the release(fix) schema. return: a. OK b. Problem:
6. stopwservices.pl it will require "stop_3306.sh" implementation: 1. block the port 3306 2. check transaction status 3. if all transaction status =1 or 2, then we can shutdown the crond and mysql 4. if transaction status=0, we will reran the transaction until no status=0 5 done Description: stop ws1 services safely, it will stop mysql network connection, then execute all the transaction once until no records status remain checkalltransactionstatus.pl
Implementation: 1. add "skip-networking" to stop network connection of mysql. 2. restart mysql 3. then execute all transaction until no records remain status_flag=0;
return: 1. Done 2. Fail:
7. startwservices.pl it will require "start_3306.sh"; implementation: 1. if all transaction status=1 or 2, the start crond and mysql.. 2. release port 3306.. 3. done. Description: start ws1 services safely, it will start the mysql network connection and accept transaction. Implementation: 1. remove the skip-networking from the /etc/my.cnf 2. restart mysql to accept remote connection for the transaction
return: 1. Done 2. Fail:
8. checkquerylog.pl -t "080502 7:54:20" -d "646656" -f "pattern match" Description: check the query log for particular transaction with match datetime or mysql query id. checkquerylog.pl -t "080502" -f "sku_no" to get an query id. then checkquerylog.pl -d "646656"
return result: 646656 Connect ws1@localhost on ws1 646656 Query set autocommit=0 646656 Query SET NAMES 'utf8' 646656 Query select map_id,target_tables_name,target_fields,src_tables_name,src_fields,src_columnnum,pri_key,default_value from database_field_map where map_id=84 and src_fields<>'' order by src_columnnum 646656 Query select map_id,target_tables_name,target_fields,src_tables_name,src_fields,src_columnnum,pri_key,default_value from database_field_map where map_id=84 and src_fields='' and default_value<>'' order by src_columnnum 646656 Query select xf_storerkey, xf_sku, xf_desc, xf_brand, xf_manusku, xf_company, xf_seasonname, xf_maincat, xf_itemcode, xf_chidesc1, xf_chidesc2, xf_plu, xf_color, xf_size, xf_sizeseq from inbound_xf_towmsitemmas where (xf_storerkey='18328' ) and (status_flag=0 or status_flag=100) for update 646656 Query update inbound_xf_towmsitemmas set status_flag=100 where xf_storerkey='18328' 646656 Query select map_id,target_tables_name,target_fields,src_tables_name,src_fields,src_columnnum,pri_key,default_value from database_field_map where map_id=81 and src_fields<>'' order by src_columnnum 646656 Query select map_id,target_tables_name,target_fields,src_tables_name,src_fields,src_columnnum,pri_key,default_value from database_field_map where map_id=81 and src_fields='' and default_value<>'' order by src_columnnum 646656 Query select xf_storerkey, xf_sku, xf_desc, xf_brand, xf_manusku, xf_company, xf_seasonname, xf_maincat, xf_itemcode, xf_chidesc1, xf_chidesc2, xf_plu, xf_color, xf_size, xf_sizeseq from inbound_xf_towmsitemmas where (xf_storerkey='11328' ) and (status_flag=0 or status_flag=100) for update 080502 7:54:16 646656 Query update inbound_xf_towmsitemmas set status_flag=100 where xf_storerkey='11328' 080502 7:54:20 646656 Query update inbound_xf_towmsitemmas set status_flag=1 where status_flag=100 080502 7:54:22 646656 Query commit
9: dbcleanup 30 Description: it will clean all old records (create_time) more than 30 days. usage:dbcleanup30.pl
10. filecleanup 30 Description: it will clean all old file (file create time) more than 30 days. usuage: filecleanup30.pl
11. re-ran "inbound_" Description: re-ran of transaction usage:re-ran.pl "inbound_xf_toposgrd" --
