...you don't get it, you destroy it. There will be a plot based mini-game in which you destroy the red Shin-Ra 'leader' submarine. After this, you will have access to a submarine of your own. If you go underwater with it you will find the Red leader submarine underwater. If you go near it a cut scene will happen in which you retrieve the Huge Materia from it...this is all you can do that involves the Red submarine.