It's now a piece of paper and a plastic card the size of an ATM card.
I rarely see ads asking specifically for certifiable programmers, as most people who actually are certified know the value of the certification based on the difficulty of the exam, which is quite low.

There are 2 (valid) reasons to go for SCJP certification:
1) studying for it will improve your knowledge of Java, especially the more obscure areas (things you never knew worked like they actually do).
2) it is a prerequisite for more useful exams like SCBCD and SCDJWS.

In general, being SCJP certifiable won't land you a job but MAY get your resume past the first screening and marked as potentially interesting for a job interview.
From there you're on your own.

Given the cost of the exam (€240 plus what you spend on books and stuff) there's little reason to not go for it.