I assume that he has been started on hormone treatment immediately? This starves the cancer so the pain should begin to decrease very, very quickly. Don't despair; we have members here who are still living fairly normal lives years after a diagnosis like this - the hormones can control the cancer for a long time, and new treatments are being developed all the time.
The hospital may also suggest that he has early chemo; chemo cannot cure prostate cancer but it can make the hormone therapy much more effective. All chemo has risks but generally speaking, the chemo used in prostate cancer is one of the more gentle ones and some men on here have been able to continue working throughout.
Perhaps wait until you have a full understanding of your husband's diagnosis and treatment plan before you tell the children. It is hard to grasp the difference between an incurable illness and being terminally ill and it would be so frightening for them to get the wrong end of the stick. You can download the toolkit from this website or call the specialist nurses at the number above and they will send it to you through the post.