The question refers to the WIPO Copyright Treaty of 1996, which is a subsidiary agreement to the Berne Convention of 1971. The United States is party to both agreements. One provision of the 1996 treaty is Article 11, which reads:
Contracting Parties shall provide adequate legal protection and effective legal remedies against the circumvention of effective technological measures that are used by authors in connection with the exercise of their rights under this Treaty or the Berne Convention and that restrict acts, in respect of their works, which are not authorized by the authors concerned or permitted by law.
The United States implemented this provision by passing the Digital Millennium Copyright Act, Pub. L. 105-304, in 1998. This statute added sections to the U.S. Code defining the scope of circumvention under Federal law, and establishing civil and criminal penalties (17 USC 12). By doing do, the United States complied with what it had agreed in the treaty.
Article 11 is an example of a treaty provision which is not "self-executing". It required legislation. It is too vague on its face to be enforceable directly, and is clearly addressed in text to what the Contracting Parties must do, as opposed to speaking to their citizens. Also, U.S. law has considered the creation of criminal liability to be the exclusive preserve of Congress when implementing a treaty, not something which the President and Senate can do on their own when ratifying. In the same way, Article 36 of the Berne Convention, the parent instrument, provides that each party "undertakes to adopt, in accordance with its constitution, the measures necessary to ensure the application of this Convention"; it is for the various countries to do this in whatever way best meets local needs and structures. Louis Henkin (Foreign Affairs and the Constitution, 1972, at 157, cited with approval in Hopson v. Kreps 622 F.2d 1375 (1980)) puts it like this:
If a treaty is not self-executing it is not the treaty but the implementing legislation that is effectively "law of the land."
(The allusion is to the "Supremacy Clause" in Article VI of the Constitution, which groups treaties as a source of the supreme law. The argument here is not that the DMCA is now part of the Constitution(!) but that the treaty text is an aid to understanding the domestic statute, instead of something that has the force of law on its own.)
Other treaties may be self-executing. The distinction was first made in the Supreme Court case of Foster v. Neilson, 27 U.S. 253 (1829), although not using modern language: it asserts that "when the terms of the stipulation import a contract, when either of the parties engage to perform a particular act, the treaty addresses itself to the Political, not the Judicial, Department, and the Legislature must execute the contract before it can become a rule for the Court." More recently, Medellin v. Texas, 522 U.S. 491 (2008) found that language like "undertakes to adopt" (or in that case, "undertakes to comply") is indicative of the document merely expressing the intentions of the parties, rather than being a species of legislative text that can be enforced in a domestic court.
What this adds up to is that if Congress were to legislate expressly and inconsistently with the WIPO Copyright Treaty - such as by repealing 17 USC 12 - then at the domestic level, courts would not be in a position to use the treaty to override it. This is addressed directly in Medellin, which argues that courts must respect the intention of the Senate in ratification, and the treaty text as it appears. If all signs point to it not being self-executing, then it is not. (Congressional reports will generally say whether they consider the treaty to be self-executing or not.) If it is not self-executing, but only an undertaking between governments, then an aggrieved DRM provider cannot point to it as a reason why they should prevail in court. Also, because the treaty at issue in Medellin contained provisions for the International Court of Justice to be involved in disputes, the implication is that this is the appropriate forum for if the treaty is breached. And Article 33 of the Berne Convention contains text of just this kind, saying that parties can bring their disagreements on "interpretation and application" before the ICJ.
Therefore, in the case of this treaty, another nation could haul the United States in front of the ICJ, on the basis that U.S. law no longer implemented Article 11 of the Copyright Treaty adequately. But there is no remedy in domestic courts, who simply look at the DMCA (or its absence). Non-compliance is a political problem, and it is Somebody Else's Problem as far as the courts are concerned.
If the DMCA were to be made ineffective by virtue of a constitutional amendment, rather than repealed in the ordinary way, then the situation is a little murkier, because the courts would have to do extra work in order to find the DMCA no longer enforceable. It would depend on the terms of the amendment and the circumstances of its adoption. Supposing it was the "Digital Rights Management Abolition Amendment", or something very explicitly on-point, then we are in the same basic situation.
Matters become more complex if the DMCA provisions were amended in a more subtle way (or ended up being implicitly altered by virtue of a constitutional amendment) because the intention of Congress becomes less clear-cut. If it is not clear that the purpose of the new law is indeed to go against the Copyright Treaty, then courts would likely try to find ways to read them compatibly. (Compare Cook v United States 288 U.S. 102 (1933), "A treaty will not be deemed to have been abrogated or modified by a later statute unless such purpose on the part of Congress has been clearly expressed.") The courts try not to undermine the foreign-policy position of the political branches. Suppose it appeared that Congress "wanted" the U.S. to remain party to the treaty, and also "wanted" domestic law to say certain things that arguably disagreed with the treaty obligations. Courts can use treaty texts and associated materials such as travaux préparatoires to interpret a statute implementing that treaty, and would try to make a compatible reading if at all possible. If Congress passes the "Go Ahead And Circumvent It, We Hate WIPO Now" Act then it's different from, say, a technical amendment in an omnibus bill that happens to undermine the present legal regime in some subtle way. And if the U.S. repudiated the Copyright Treaty at the same time as all this other activity, then that makes the policy situation much clearer.