The 970 is a Power4 (THE big dog chip) derivative.
It will be the next high end chip in Apple's top end offerings.
The G5 moniker may be dead, or the 970 may be the G5....semantics. I think that "G" may get retired...
The 970 will be a workstation class chip (MIPs, UltraSparcIII, Xeon, Itanium, etc.). The 970 will have a lower GHz rating than much cheaper (and likely less powerful) Intel & AMD offerings.
The 970 will do great things for multitasking, db serving, multiuser computing, etc. due to its very high system communications bandwidth.
The 970 may not be schockingly fast for:
non Altivec apps
The 970 will dramatically increase the performance of:
64 bit apps which utilize full width (remember, Oracle's coming)
Apps which bottleneck between the processor and memory
As the 970 scales, and people utilize the bandwidth, it will perform even better.
Also, if IBM uses this chip in its workstations, we'll see a lot more Altivec enhancements in things ported from UNIX (Apache, GIMP, etc.).
The 970 will be sampling (low level production to fine tune yields and speed ratings) in the beginning months of next year.
It will be an available processor in the later months of next year.
When it will be in Apple hardware depends on how close the Apple/IBM relationship is, as well as how well the chip samples at the fab and whether serious tweaking needs to be done, and countless other factors, including economics, demand, remaining contracts to buy G4s, etc.