The words are about the day of judgement or day of wrath, and the motif when used by composers symbolized death, fate, etc.
Rachmaninov used the dies irae motif probably more than any other composer, almost as a sort of leitmotif for fate and death, reoccurring throughout his works, if you will.
It is beyond the scope of this thread to go through too many of the numerous Rachmaninov quotations, and I don't deceive myself into believing anyone will be interested. In this post I'll mention two, and perhaps in the future, others. I'll start with the Isle of the Dead, an earlier work from 1908 and end with the Symphonic Dances, composed in 1940 and Rachmaninov's last composition before his death.
The Isle of the Dead
Score: http://javanese.imslp.info/files/imglnks/usimg/6/6e/IMSLP112800-SIBLEY1802.15851.b564-39087009472780score.pdf
This piece was inspired by a black-and-white version of the Arnold Böcklin painting the composer saw while in Paris. The Isle of the Dead was mass produced and loved in contemporary society at the time, in both black-and-white and color versions. Rachmaninov later saw a color version and did not quite like it as much, saying he could never have found the inspiration to compose his piece if he had seen a colored painting.
Immediately striking is the unusual 5/8 meter of the piece, evoking the sensation of rocking, as a boat in a river.
At 1:01 (page 5 in score) listen for the french horn, as it introduces the dies irae for the first time, as if a distant call through the fog (the 4th note is dropped a 5th-interval here instead of the third in the original melody).
The 4-note dies irae motif features heavily throughout the whole piece and you can listen for it. In this piece the 3rd note is mostly broken and played twice so listen for 5 notes.
Symphonic Dances
The score is not public domain yet for this piece.
First movement:
Rachmaninov composed this piece as a sort of swan song. It was intended to be danced to, as ballet.
The saxophone was originally invented for use in the classical orchestra and you can hear an alto saxophone in the beautiful melodic central section of this first movement.
At 7:20, the middle section ends with a variant and quotation of the dies irae.
Things start winding down at 10:16, signaling the impending end, and the transcendent coda begins at 10:37, with the strings sublimely and contentedly singing the dies irae again (10:41) before the movement dies in the same manner as it began. This symbolizes an acceptance and triumph over fate, a common theme in Rachmaninov.
In this particular recording the conductor took it quite slow so there's almost a feeling of reminiscence. I prefer a faster interpretation, where it's as if one is happily looking back at a full and long life. Compare
(10:29ish) or Mariss Jansons.
Rachmaninov, Walt Disney, Horowitz