'Where does the "9" come from - the "D" scale, perhaps?'

Yes, that's it. Play a D scale, and it goes

D E F# G A B C# D E ...
1 2 3   4 5 6 7   8 9 ....

So the '9' is an E. Why don't they just call it a 2? Because the 2, being so close to a D bass note, would be too dissonant.

To tell you the truth, we used to play a beautiful Welsh song, Lisa Lan (or was it Lon?) that called for a D9 chord. We found that if the accompanists played a D chord while the melody instruments played the C notes, then that was all the ninism you'd ever want. (I mean for that particular tune.)

Good luck with your song.