Search This Blog

Loading...

2015-12-09

Common Acura Integra 3rd Generation (1994 - 2001) Common Known Issue


  1. Camshaft seal cap/plug on side of cylinder block
    1. This is a common part in 2001 Civic and 1992 Prelude.  Year 2000 Civic doesn't has this part
    2. It is a plastic cap with o-ring
    3. Plastic plug with o-ring cost CAD$13
    4. The o-ring itself cost CAD$0.40, which I recommend to replace every 10 yr
    5. If plastic cap is good, then just replace the o-ring size about 27mmx2.5mm or 28mmx2.5mm (IDxCS) nitrile
    6. High temperature material like Viton, FPM, AFLAS are waste of money because nitrile can last for 9 yr, and 4 to 10 times cheaper
    7. Inspect for oil leak at the bottom of the plug as the o-ring harded by age.  The plastic plug should be good for 15 year
    8. Use a flat head screw driver to pop up the old plastic cap.  It is a 1 minute job
    9. Don't waste money on billet type 2 or 3 o-ring cap.  The nitrile o-ring for those will harden quickly, and need to remove valve cover to replace.  The Honda/Acura plastic cap design will last equally long with 1 o-ring, and quick to replace every 10 yr
    10. Buy a couple o-ring if you have multiple Honda/Acura car dated 1990 to 2011, including Civic, Prelude, Accord, Fit, Insight, Fit
    11. Camshaft plug picture
  2. Valve cover gasket
    1. Needs to use Ultra grey RTV at the round and corner when installing
    2. Inspect for oil leak and replace as needed
    3. Cost CAD$35
  3. Front upper control arm
    1. Torn rubber bushing due to age.  Inspect the rubber bushing, and replace whichever side as needed
    2. Cost CAD$35
    3. Car alignment is recommended
    4. Front upper control arm picture
  4. Coolant temperature sensor for driver cluster
    1. It could slowly degrade and show lower than normal temperature at early failure and eventually stop moving the needle at end of life
    2. Cost CAD$8
    3. This is a common part with 2001 Civic
    4. Non-OEM might be longer, so Honda part is not require
    5. This is a 1-wire sensor for the instrument cluster, not the 2-wire sensor to ECU.  There are 2 coolant temperature sensor
    6. Coolant temperature sensor
  5. Front outer tie rod
    1. Inspect for torn rubber bushing
    2. Cost CAD$18
    3. Car alignment is recommended
  6. Front inner tie rod
    1. Inspect for torn rubber bushing
    2. Cost CAD$15
    3. Car alignment is recommended
  7. Rear trailing arm bushing
    1. The rubber bushing will crack slowly after 10 yr.  When it is totally broken, when going on uneven surface, the metal knocking sound will be heard
    2. It affects the wheel height, but not chamber or toe
    3. Recommend to replace in pair
    4. Needs pressing tool such as Honda Lower Control Arm Bushing ToolBushing Extractor
    5. No car alignment needed
  8. Antenna auto up/down
  9. Electronic trunk release - electric motor failure
  10. Rough idle
    1. Clean the Fast Idle Thermo Valve, and tighten any internal parts in it.  This part is located directly under the plastic air intake connector to the air intake body, where the butterfly valve is.  You need to remove large plastic air intake hose
    2. This is a common part since 1990's, such as 1992 Honda Prelude
    3. This part is DIY cleanable.  Remove it from intake valve body, then remove all the bolts to clean every part by paper tower
    4. If it doesn't resolve the issue, then remove and clean Idle Air Control Valve
  11. Low brake fluid warning light
    1. The sensor is easy to trigger intermittently on low brake fluid, even it is above MIN level
    2. When brake pads wear to 80%, the warning light may intermittently on, which you can add a few drops of brake fluid to shut it off, or replace the brake pads early
    3. Cost $30 (non-OEM) - $120 (OEM) front sets or rear sets
    4. Recommend to buy brake lube when replacing.  The smallest packet cost $3
    5. When removing the reservoir cover, inspect filter for clog, which might trigger the warning light
    6. Inspect wire on the cover.  Loose wire might trigger the light
    7. Recommend to replace all 4 wheels brake pad, then top it up.  Then this warning light eventually will tell me either the front or rear brake pad is 80% worn, and need replacement.  The brake fluid will never evaporated, and will always in the brake system even after 15 years.  It is a good indicator of worn brake pad, if you don't check it annually
  12. Water/moisture in rear brake light
    1. Replace the rubber seal
    2. Alternatively, drill a small hole at the bottom of light assembly to let the water to drain out
    3. Another cheap workaround is to use kitchen sink silicon seal.  Apply around the existing old rubber seal

2015-11-15

Alpine SEC-8027 Installation/Wiring

Alpine SEC-8027 Installation/Wiring

Wiring Information

10 Pins Connector (Ordered according to cable position from edge)

Yellow ~ Main Battery Connection (+) Fuse ~ 5 Amp MAX
Connect to a constant 12-volt source. Alpine recommends this wire to be connected directly to the battery. Do not share this power lead with any other components or accessories. Use a 5-amp fuse in-line at the battery lead to protect against short circuit
Red ~ Ignition (+)
Connect to a 12-volt ignition source. When selecting the N/O starter interrupt, it is necessary to find a “true” ignition source for the Red wire.  When selecting the N/C starter interrupt, you may use an accessory lead
Blue ~ Siren (+) 1.5 Amp
Connect to the Blue wire of the siren. Connect the Black wire of the siren to chassis ground. If more than one siren will be used, use a relay to drive all of the sirens.
Yellow/Red ~ Extension LED Output (+) 20mA
Connect to the Yellow/Red of the extension LED. Connect the Black wire of the extension LED to chassis ground. Up to 4 additional LEDs can be added in series with the original LED. Do Not Parallel LEDs. The correct mounting hole size is 15/64".
Green/White ~ Hood /Trunk Wire (–)
Connect the Green/White wire to the vehicle’s hood pin and/or trunk pin wire. If both hood and trunk pin are connected to this input, you must diode isolate each wire.  If not connected, it won’t detect intruder when armed
Brown/White ~ Door Switch Input (+ or –)
Determine the vehicle’s door pin polarity. Refer to the Programming section if positive door input is required. Connect the Brown/White wire to the vehicle’s door pin wire.  If not connected, it won’t detect intruder when armed
Red/White ~ Valet/Disarm Push Button (–)
Connect this wire to the Red/White wire of the Valet/Disarm switch. Connect the Black wire of the Valet/Disarm switch to chassis ground
Black ~ Chassis Ground ()
Connect directly to a metal surface that has been cleaned from any paint or coating. This wire should be as short as possible and should not be connected directly to the battery.
Yellow/Green ~ EXT-1 Output {(–) 1-Second or Latched} 200mA
Connect the Yellow/Green wire to control an optional accessory when the EXT-1/Valet button is pressed and released. Some examples might include devices such as a trunk release (through relay), remote starter or window roll-up modules.  For this output, Valet push button and remote control will be used to program it for different accessory.  For example, programs to latch (non-default) for trunk release or 1-sec pulse (default) for windows roll up.  Refer to the accessory’s owner’s manual for installation instructions
Orange ~ Starter Interrupt Output (+) 200mA
Connect the Orange wire of the security system to the Orange wire of the supplied relay. Connect the Black wire of the starter interrupt relay to chassis ground. The starter interrupt can be configured as a normally open (N/O) or normally closed (N/C) circuit.
NORMALLY CLOSED STARTER INTERRUPT – Normally Closed is the factory default setting. This configuration will assure that the vehicle will always start as long as the security system is disarmed, even in the event of a security system malfunction or component failure.
NORMALLY OPEN STARTER INTERRUPT – This configuration adds more security for the customer by not allowing the vehicle to start even if a would-be thief finds and removes the main unit or main power connection.  Open the Main Unit. Locate the jumper labeled JP1. This is the only jumper selection inside of the main unit. Jumper JP1 will select either normally open or normally closed starter kill relay operation. The factory default is normally closed, or in the “OFF” position. To select normally open starter disable, place jumper JP1 to the “ON” position. When using this feature, it is important that the Red (ignition) wire be connected to a “true” ignition source-12 volts through the “On” and “Start” cycles. This feature must be enabled. See the Jumper Selection section for more details.

9 Pins Connector (Ordered according to cable position from 10 pins connector)

Blue/Yellow ~ Dome Light Surv. Output (+ or –) 10 Amp MAX
NEGATIVE TYPE- Connect the Blue/Yellow wire to pin 85 of a standard Bosch type automotive relay. Connect a fused 12-volt source to pin 86. Connect chassis ground to pin 87. Finally, connect the vehicle’s dome light wire to pin 30 of the relay.
POSITIVE TYPE- Connect the Blue/Yellow wire to pin 85 on a standard Bosch type automotive relay. Connect a fused 12-volt source to pin 86 and pin 87. Finally, connect the vehicle’s dome light wire to pin 30 of the relay.
Brown/Blue ~ Door Lock, Normally Close (N/C)
For Japanese car, e.g. Honda, Acura, this wire is not used.  No connection is required.  This is the N/C contact in the build-in relay #1
White/Brown ~ Door Lock, Common.  Connects to factory lock circuit
For Japanese car, e.g. Honda, Acura, this wire connects to door lock circuitry.  Looks at bottom of the door lock button to determine the wire color to connect to.  When arm, a pulse of GND will send to this output, which is from wire Brown/Red.  This is the common contact in the build-in relay #1
Brown/Red ~ Door Lock, Normally Open (N/O).  Connects to chassis ground
For Japanese car, e.g. Honda, Acura, this wire connects to chassis ground, which will used to send the GND to White/Brown wire.  If a positive pulse required, which is non-Japanese car, then connects it to constant 12V.  This is the N/O contact in the build-in relay #1
Green/Blue ~ Door Unlock, Normally Close (N/C)
For Japanese car, e.g. Honda, Acura, this wire is not used.  No connection is required.  This is the N/C contact in the build-in relay #2
White/Green ~ Door Unlock, Common.  Connects to factory unlock circuit
For Japanese car, e.g. Honda, Acura, this wire connects to door unlock circuitry.  Looks at bottom of the door unlock button to determine the wire color to connect to.  When disarm, a pulse of GND will send to this output, which is from wire Green/Red.  This is the COMMON contact in the build-in relay #2
Green/Red ~ Door Unock, Normally Open (N/O).  Connects to chassis ground
For Japanese car, e.g. Honda, Acura, this wire connects to chassis ground, which will used to send the GND to White/Green wire.  If a positive pulse required, which is non-Japanese car, then connects it to constant 12V.  This is the N/O contact in the build-in relay #2
Yellow ~ Dome Light Surv. Output (+ or –) 10 Amp MAX
NEGATIVE TYPE – Connect this wire to pin 85 of a standard Bosch type automotive relay.  Connect a fused 12-volt source to pin 86. Connect chassis ground to pin 87. Finally, connect the vehicle’s dome light wire to pin 30 of the relay.
POSITIVE TYPE – Connect this wire to pin 85 on a standard Bosch type automotive relay.  Connect a fused 12-volt source to pin 86 and pin 87. Finally, connect the vehicle’s dome light wire to pin 30 of the relay.
Yellow/White & White ~ Parking Light Flash Input (+ or –) 10 Amp MAX

Determine the correct polarity of the parking light circuit for the vehicle.  Connect the Yellow/White wire to a constant 12V+ source or to chassis ground, depending upon

2015-10-08

Oracle: Hacking Timezone

Product: Oracle Database
Version: 10.x - 12.x

Oracle release DST patch yearly or more frequent.  DBA needs to install the DST patch especially if it is affecting their city.  Some DBA do not install it for good reason, e.g. the DST change is not affecting their city, their software is not using timezone, they want to use old DST format.  Some DBA are ignorant, e.g. it is not PROD env, overlooked, overloaded.

Last year, 2014, there are several major countries have DST change, which causing Oracle database to return wrong time:
Cairo
Fiji
Jamica
Chile
Egypt
Russia

DBA will need to have access to Oracle Support to download the latest DST patch for their specific database, which I am not going to cover here.  DST patch is cumulative, which means if you install v24 over v14, then it will covers all the DST changes.

This post mainly to cover how to identify current DST version, in order to know whether it is responsible to returning wrong time.

Run following SQL, and determine whether all the time are consistent

Alter session set time_zone = 'Europe/Moscow'; -- This is one of the city change to no DST in 2014
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI') "sysDate",
  TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI TZR') "sysTimestamp",
  TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH24:MI')  "curDate",
  TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI TZR') "CurTimestamp,
  TO_CHAR(LOCALTIMESTAMP, 'YYYY-MM-DD HH24:MI TZR')  "LocalTimestamp,
  DBTIMEZONE ,
  SESSIONTIMEZONE

FROM DUAL;

Following SQL will tell you whether which version of DST patch you are using.  Oracle 12.1.0.2 delivered with v14, which covers up to 2012 DST change, if I'm not mistaken.  The lastest DST version as of Jan 2014 is v24

select filename, version, con_id from v$TIMEZONE_FILE;

If you have access to Oracle database server file system, then you can check following:

Location of timezone file: $ORACLE_HOME/oracore/zoneinfo/

Search for the desire city with DST change, such as Cairo or Moscow, using following command.  You can confirm whether it is missing 2014 DST change, and know that this database is using older DST patch (or no patch):
$ grep Moscow $ORACLE_HOME/oracore/zoneinfo/timezdif.csv
--- Following is the output based on OOTB Oracle 12.1.0.2 which is shown that it is missing 2014 entry
17, Europe/Moscow, 2011,
12, Europe/Moscow, 1919, 1919
11, Europe/Moscow, 1879, 1919

9, Europe/Moscow, 2021,

There is a zoneinfo/readme.txt with will tell the DST version.  Oracle 12.1.0.2 comes with v14, which is 10 version and few years behind now.

You can also do a ls or dir in this directory to find out how many .dat files there.  v14 will have 14 dat binary files, while v24 should have 24 .dat binary files.

If you are running Oracle database 10.x and older, and you don't have access to Oracle Support, you can manually modify the timezone text file.  After Oracle 11.x, Oracle provides binary timezone.dat file which can't be modified.

See https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm for detail instruction

Please note that OS (Windows, UNIX), Java (JRE or JDK), firmware (Cisco route, firewall, load balancer, security system) will required to install the latest DST patch as well to ensure.  Otherwise, you might be unable to enter the door using your swipe access card, or server failed to startup due to inconsistent time check mechanism build into the application

2015-08-17

Quick Charger for Samsung Galaxy Note 2

Researched into Qualcomm Quick Charge 2.0 spec, and comparing what I can archive with my 2 yr old Galaxy Note 2. Found that Quick Charge 2.0 phones (mid to high end phone released 2014) can charge 1%/min. When I look into my phone charging rate by installing Android app "Battery Log" I found that using original Samsung charger, it only charge at 1,000 mA, and using computer is 460 mA, although my powerful motherboard's USB port can handle 1A.

I have a junk cigarette lighter charger burn after 1 yr of usage, so I am salvaging it for this small project. My research from QualComm found that the phone has build-in battery charging management circuit, and not relying on external power adapter, or computer USB port. I made a brave assumption that Samsung Galaxy Note 2 also has build-in charging circuitry, therefore I can plug in any 5V power supply that I want. I am more confident with proper electronic voltage regulator then unknown China phone charger, especially cigarette lighter charger.

The 2 weeks journey starts by ordering a pre-fabricated 5V 3A voltage regulator from eBay. It is made in China, but I have better fate in proper IC then poor quality charger. For the cable, I have the old cable that attached to cigarette lighter charger, as well as thicker cables laying around. The internal phone charger circuitry has its API expose in Android API, and I found app "Galaxy Charging" to be able to detect the avg/ref/max current, so I don't need to connect it to expansive multi-meter, which I already have one that can measure up to 20 A.

After the parts arrived, It took 4 days to assemble and test everything. The testing mainly to give me more low level understanding of the charging rate, as well as any short circuit protection (over current) in the power supply.

The testing concluded my research that:
1. Connecting pin 2 and 3 (used for data communication) in USB port is the way the phone decide whether it connects to computer (draw less amp), or AC power adapter (draw more amp)
2. Samsung phone charger 5V power supply has some resister in pin 2 and 3 which allows the phone to charge at 1000 mA
3. Original Samsung white USB cable is limited to 1000 mA by the build in phone charger (inside the phone), although the Samsung power adapter is rated at 2000 mA
4. If attached Samsung white USB cable to my 5V 3A power supply that shorted pin 2 & 3, it will only charge at 600 mA instead of 1000 mA
5. If attached any USB cable to computer that handle handle 1A, it will charge only at 460 mA
6. Cable itself can limit the current draw
7. When battery level > 95%, the charge rate decrease to half
8. Build-in charging circuit will decrease its rate after 85%, but I still see it detecting 1800 mA all the way to 100% battery level

After 2 days of testing, I concluded that the tiny cigarette lighter charger cable can only handle 600 mA max, although it detected it is AC charger.

The most important fact is that my circuit is comparable with QualComm Quick Charge 2.0, which charge the battery at 1% per 1 min, or slowest is 1% per 1.5 min. Battery charging is analog, so it isn't a constant rate, so I can accept it might slow down to 1.5 min to charge 1% of battery level from time to time.

Following is the charging rate for my Galaxy Note 2 when using computer USB port, vs customize network cable USB adapter

Charging Rate (mA, % in min):
Computer: 460 mA, 1% every 6-9 min
Samsung charger: 1000 mA, 1% every 2-4 min
DIY charger: 1800 mA, 1% every 1 - 1.5 min

The charger have not been tested with 0% battery level, and minimum I tried is 30% battery level. Moreover, the effect battery life due to high current charging is yet to be confirmed. By shorting the pin 3 to 5V (pin 1) and sometimes to ground (pin 5) has logically corrupted the /data partition, which I have to re-format the /data partition, and reinstall all the software

This power supply can easily attach to car's cigarette lighter cable, which always on, and charge your phone to +50% in 30 min

Advice for friends are:
1. Build this instead of cigarette lighter charger because it is reliable, powerful quick charger, and cheap to replace or repair
2. Use it when emergency with low battery, but don't use it for regular charging
3. Don't use it to charge to > 85%

This is not a QualComm Quick Charge 2.0, which has 12V, 9V, and 5V.  It is a constant 5V but can produce 3A.  As of this writing (2015-08-17), the phone only can handle 5V 2A, although I don't know exactly what phone actually charge at 2,000 mA or higher


Reference:
  1. 5V 3A power supply CAD$2.13 http://www.ebay.ca/itm/221422066455?_trksid=p2057872.m2749.l2649&ssPageName=STRK%3AMEBIDX%3AIT
  2. 10 unit of micro USB angle socket adapter CAD$3.99 http://www.ebay.ca/itm/10x-Right-Angle-Micro-USB-5P-Port-Male-Plug-Socket-Connector-Plastic-Cover-/141331120324?hash=item20e7fdc0c4
  3. Straight micro USB adapter is cheaper, but I like angle due to I put the phone in landscape when using Google Map
  4. Cigarette charger CAD$0.99 (but I only use it for testing before permanently attach it to the car) http://www.ebay.ca/itm/Mini-USB-Car-Cigarette-Plug-Adapter-Charger-DC-for-MP3-PDA-iPhone4-4S-iPod-Touch-/181811158811?var=&hash=item2a54ca1f1b
  5. Use any network cable, either old CAT3 or latest CAT6.  It can be any cable, such as speaker cable, home appliance cable, but must fit into the micro USB adapter housing

2015-06-19

MS SQL Server: Finding Table's Index Count

Product: MS SQL Server
Version: 2005 - 2012

Following SQL can be used to find out number of indexes per table

select top 20 OBJECT_NAME(i.object_id) table_name , count(1) n_idx from sys.indexes i
where OBJECT_NAME(i.object_id) like 'c%'
group by object_id
having COUNT(1) > 10

In above SQL, I only listing tables which have more than 10 indexes, and limiting to first 20 rows.  You can adjust them to meet your need

2015-06-11

MS SQL Server: Truncate Transaction Log File

Product: MS SQL Server
Version: 2008R2 - 2012

Following is the steps to truncate and optionally shrink DB transaction log file.  Unlike other post you found in the internet, this step includes replication database, which many others do not consider

Step 1: Determine whether DB replication is enabled.  If the database name shows up, then you have a choice to disable it (applicable if you restore the DB), or tell replications to ignore data in transaction log files (will require to re-sync replication later)

select name, log_reuse_wait_desc from sys.databases where log_reuse_wait_desc = 'REPLICATION'

Step 2a: If you want to tell replication to ignore all data in transaction logs.  The database must be published with replication in place.  This won't work if this database is restored, where replication is not setup

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

If you encounters following error, then the replication is not setup, and use Step 2b

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

Step 2b: Disable database replication.  Suitable to use in restored DB

USE [Database Name]
EXEC sp_removedbreplication

Before execute above statement, if you check sys.databases, you will see it shown "REPLICATION"

select name, recovery_model_desc, log_reuse_wait_desc from sys.databases

Step 3: Verify that all transaction logs are inactive, where STATUS=0.  If recovery mode is SIMPLE, then it must be 0 at this step.  If recovery mode is FULL, then there will be some still in STATUS=2.  Once you execute Step 7, then it will be 0

DBCC LOGINFO

FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
225395281921519220640
22539522621441519230640
22539525160961519240640
227852877004815192501280
2262144104857615192606423000000019400021
2262144131072015192706424000000009300239
2262144157286415192806425000000016500010
2262144183500815192906426000000015700030
2262144209715215193006427000000016200020
2262144235929615193106428000000008000001
325395281921522300640

Step 4: Verify transaction log wait status is no longer in Replication anymore.  It must display NOTHING

select name, log_reuse_wait_desc from sys.databases

Step 5: Verify whether DB in Simple or Full recovery mode.  Column recovery_model_desc will be either SIMPLE or FULL

select name, recovery_model_desc, log_reuse_wait_desc from sys.databases

Step 6: Check whether transaction log in first virtual log (StartOffset=8192) for 1st log file

DBCC LOGINFO

FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
21341521928192145522640
2134152192134160384145480640

If last row has STATUS=2, then you won't be able to shrink it smaller than this size.

Regardless of recovery mode, change it to Simple recovery mode in order to force it to first virtual file

ALTER DATABASE [Database Name]  SET RECOVERY SIMPLE WITH NO_WAIT
CHECKPOINT
CHECKPOINT
DBCC LOGINFO
DBCC SHRINKFILE([Transaction Log File Name], 1)

Step 7: If DB recovery mode is SIMPLE, then proceed with shrink the transaction log file to 1 MB size

DBCC SHRINKFILE([Transaction Log File Name], 1)

If you don't want to shrink it, then use this statement

DBCC SHRINKFILE([Transaction Log File Name], EmptyFile)

Step 7: If DB recovery mode is FULL, then create a dummy backup before shrink

BACKUP database [Database Name] TO DISK = 'nul:'
BACKUP LOG [Database Name] TO DISK = 'nul:'
DBCC SHRINKFILE([Transaction Log File Name], 1)

Note: "nul" doesn't backup the database to physical file.  It just mimic a DB backup, but doesn't write to anywhere

If you don't want to shrink it, then use this statement

DBCC SHRINKFILE([Transaction Log File Name], EmptyFile)

If you restored the DB and would like to change it to Simple recovery mode, then use following:

ALTER DATABASE [Database Name]  SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([Transaction Log File Name], 1)

Now the size of the ldf file should be 1 MB, if you specify 1, but no change if you specify EmptyFile

Step 8: Verify data in transaction log files.  There should be only 1 record with STATUS=2, if there is no other user using it.  You will get 1 row for each additional transaction log files with STATUS=0.  This is a good time to drop the additional transaction log files, but not the 1st transaction log file.  This is the restriction of database, and you can easily see that primary (1st) transaction log file has an entry with STATUS=2 (Active), so it can't be dropped

DBCC LOGINFO

Quick Steps - With Simple Recovery

Assume DB name is test1, and transaction log name is test1_log, and I would like to shrink the transaction log file to 1 MB

ALTER DATABASE test1 SET RECOVERY SIMPLE WITH NO_WAIT
EXEC sp_removedbreplication
DBCC LOGINFO
CHECKPOINT 2 times if DBCC LOGINFO not showing STATUS=2 for first row with StartOffset=8192
DBCC SHRINKFILE(test1_log, 1)
DBCC LOGINFO

Quick Steps - With Full Recovery

EXEC sp_removedbreplication
BACKUP database test1 TO DISK = 'nul:'
BACKUP LOG test1 TO DISK = 'nul:'
DBCC SHRINKFILE(test1_log, 1)
DBCC LOGINFO

If LOGINFO shows last row with STATUS=2, then you have to use next option

Quick Steps - With Full Recovery and Temporary Set to Simple Recovery

ALTER DATABASE test1 SET RECOVERY SIMPLE WITH NO_WAIT
EXEC sp_removedbreplication
DBCC LOGINFO
CHECKPOINT 2 times if DBCC LOGINFO not showing STATUS=2 for first row with StartOffset=8192
DBCC SHRINKFILE(test1_log, 1)
DBCC LOGINFO
ALTER DATABASE test1 SET RECOVERY FULL WITH NO_WAIT

2015-04-23

Gosu Performance Tuning #1

Product: Guidewire Software
Module: InsuranceSuite (CC, PC, BC, ContactCenter)
Version: All

This post is about generic Guidewire application tuning, but its principle is generic and applicable to any J2EE applications, programming language and reporting, such as:

  • InsuranceSuite: ClaimCenter, PolicyCenter, BillingCenter, ContactManager
  • SAP: ECC 6, BusinessObjects Data Integration, BusinessObjects InfoView 
  • Cognos reporting

General tips

  1. Do not pull in too much data that is not required.  Filter it from database (ensure WHERE clause exists in database) instead of pulling all the data into InsuranceSuite then filter (avoid WHERE clause in Gosu/Java)
  2. Always check Java garbage collection log whenever there is performance issue.  Ensure it is not due to GC activity.  If it is, then capture/analyze Java heap memory, permgen space to find out more
  3. Avoid displaying too much data in browser.  Remember that end user might not share the gigabit network in data center or main office.  Some remote office might has 1 Gbps, but share with 500 users, where effective throughput is 1 Mbps (~ 100 KB) or lower
  4. Engage DBA to ensure you cover the database related performance issue
  5. Engage OS administrator to cover OS related performance issue, especially on CPU contention, over-provision CPU/RAM in VM, storage contention, swapping, memory contention, hardware failure
  6. Engage VM administrator to cover VM related performance issue.  Such as vMotion, over-provisioning, host server contention, known performance bug
  7. From Java thread analysis, once determine the slow or long running thread, you should be able to identify the API/Gosu function/Java class.  Escalate to developer to review and tune the code, if applicable
  8. Engage network administrator that there is no drop packets or packet resend.  Network disruption going to affect both performance as well as stability
  9. You might be able to temporary gain performance improvement by suspending message queue, workflow, external integration point, if the performance is due to poor coding in those areas.  This could buy you more time to keep the business running
  10. Overtune JVM heap memory setting will lead to longer garbage collection.  More is not always good, but optimal/balance is the best

  The opinions and statements in this communication are my own and do not necessarily reflect the opinions or policies of Guidewire

Blog Archive