Re: Sql Table Error constraint?

On 09-Feb-2015 08:40 -0600, RPG List wrote:

I created a SQL table that when I try to add a record to it, I'm
getting the following error:

The journal specified is used by commitment definition QDBCMTDFN.

Seems unrelated to RPG; a topic more appropriate for a forum not dedicated to a specific language [other than a forum dedicated to the SQL] such as the HLL RPG.

An error is most appropriately identified with a message Identifier (MSGID). Why expect the reader to figure out what that msg is, just to help you? To search for known issues [for which a corrective or preventive fix might exist], only untranslated _tokens_ such as Message Identifiers are useful. That is because the message /text/ gets translated into numerous languages, and the text can even change from one release to another despite being the same installed National Language Version (NLV). Thus the knowing\sharing of only the text of the message can approach being useless, with regard to enabling someone [the readers of the topic] to assist whoever posts the inquiry.

Beyond that, the context for the [error] message is helpful to the readers [in finding a matching problem description]; the failing request [in this case the actual SQL failing statement, which can be inferred to be an INSERT] and the details of the messages, and while the [failing] SQL request is not in the joblog and may need to be shown as what is copied from source and\or debug, the logged messages are generally visible with further contextual details [other than the request that preceded the messaging] given they are spooled using the LOG(4 0 *SECLVL). Even without the message identifier, showing the second level text to include the Cause and Recovery might have revealed something to the reader that allows them to offer what might be a worthwhile reply [rather than simply implying that too little information has been offered].

I specifically set commitment control to *NONE when I created the
table.

If the error is during an INSERT, what was the COMMIT() specification during CREATE TABLE may be unrelated. However I almost always commit all the Data Definition Language (DDL) statements before ever starting any Data Management Language (DML) statements, if the DDL ran with isolation other than *NONE.

The only field in the file that has a reference elsewhere is in the
pohdr header which has a constraint:

PoShpTo Numeric(7)
References PShipTo(ShipIndex),

The Pohdr file has commitment control set to *NONE as well.

The database file [SQL TABLE] can not be properly described as having a CmtCtl setting. The commitment control level is an environment setting, not an attribute of the file; the CmtCtl [or isolation] level being used is specific to the DDL statements or the DML statements being issued.

What am I missing?

FWiW: Referential Integrity (RI) is implemented with implicit commitment control, irrespective of the COMMIT() [Isolation Level] established for the SQL statements being processed [by a dynamic statement interpreter]. The REFERENCES clause in a CREATE TABLE needs to establish RI. That implicit CmtCtl runs under the System-Defined Commit Definition (CMTDFN) named QDBCMTDFN.

Probably best to create a script of self-contained SQL requests [i.e. no reliance on previously-created user-defined objects], that when run [e.g. with particular specifications on RUNSQLSTM], will create the objects and will re-create the error at the [last included] INSERT statement. Then anyone else with access to a system can issue the same Run SQL Statements request using the same source would be likely to encounter the same error and perhaps also be able to explain why the error is [in]correct. In this particular scenario, because the SQL can implicitly journal, such a script should include establishing the journaling environment equivalent to the failing scenario