Re: SQL performance with Local journalling

[vhamberg@xxxxxxxxxxx]

5722SS1 -> opt 42 -> OS/400 - HA Journal Performance can significantly help 
performance - it looks like it is a no-charge feature in V5R3 - it used to be 
costly, IIRC. It adds a "Journal caching (JRNCACHE)" parameter to CRTJRN. Help 
says that if you are using commit, there is already some journal caching going 
on, so the improvement will not be as great.

HTH
Vern
-------------- Original message -------------- 

> Hi all 
> 
> I have a customer that is starting out on a HA implementation who wondered 
> about the additional overhead of journaling. 
> 
> To test the impact they wrote a simple SQL update statement and ran it on a 
> journaled file, then ran it again after turning journaling off. The impact 
> was significantly different (in the order of 1000%) and although they 
> didn't process a whole lot of records (a couple of thousand) the result is 
> enough to have them concerned. 
> 
> After I spoke to them a couple of days later I had them run the statement 
> again, but asked them to run the SQL statement while the file was not 
> journaled first to see if the order of execution had had any effect on the 
> relative performance of the two operations. This had essentially the same 
> result. 
> 
> I am not overly concerned about the likely impact of journaling on their 
> system as the hardware should handle it and my experience is that it will 
> not add anywhere near the overhead that they are seeing in their admittedly 
> limited testing, however, I am curious as to what could cause such a weird 
> result. 
> 
> The SQL itself was selecting approximately 2000 records via the relative 
> record number and performing a simple update on a field in the record 
> layout. The file was created by doing a copy file and there were no 
> logicals over the new file. Relative record was uses to avoid using a key 
> for selection. The SQL was submitted to batch in both cases via some kind 
> of RUNSQL command they have access to. Essentially they were trying to 
> assess the raw impact of journaling by removing all the variables that 
> might have influenced the performance of the SQL. 
> 
> Is there anything that anyone has seen that could account for this result ? 
> Is there any relationship between SQL and journaling that I should take 
> into account ? 
> 
> Regards 
> Evan Harris