MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » March 2014

Re: table not journaled



fixed

On 06-Mar-2014 15:42 -0800, Hoteltravelfundotcom wrote:

On Thu, Mar 6, 2014 at 6:28 PM, CRPence wrote:

On 06-Mar-2014 15:14 -0800, Hoteltravelfundotcom wrote:
I created a table to be used as a Data Warehouse table to our
reporting tool or even on the i. This is going to be refreshed
nightly. It will be used strictly in a reporting mode. Do we
need to be concerned about the Not Journal status?

Message ID: SQL7905
Message: Table DTAWHSO in PROJ_T1 created but was not journaled.
<<SNIP>>
Recovery: The table was created, but until the table is
journaled, COMMIT(*CHG), COMMIT(*CS), COMMIT(*RS), COMMIT(*RR),
COMMIT(*UR), and COMMIT(*ALL) will not be allowed for table
DTAWHSO.

The "Recovery" explains whether a concern is warranted; i.e. if an
isolation level other than NC [no-commit] is required, for any
reason when accessing the file [data; i.e. effectively just for
DML, including SELECT], then the data access will fail [with
SQL7008]. To ensure a SELECT does not operate with a
commitment-control\isolation level other than *NC, then use\append
the WITH NC clause to the request.


that WITH NC goes before the NOT NULL WITH DEFAULT)

am getting this error"

NC in *LIBL type *SQLUDT not found.

The implication of the above seems to be that an attempt was made to place the WITH NC clause within a column definition of the DDL CREATE TABLE statement.?

The WITH NC clause is appended to the end of a SQL SELECT statement [or another DML statement; e.g. UPDATE, INSERT, or DELETE]. Entirely different and unrelated to use of the SQL DDL.

As noted in the message Recovery, the TABLE was already created. The message as a warning, is as a prelude to eventual activity, along with the acknowlegement that the file was created without journaling enabled. What is referred to further-on in the Recovery text about COMMIT() specifications, is with regard to eventual activity *after* the TABLE had been created [without journaling enabled]. As I had alluded, the reference to the isolation-level was regarding the DML; i.e. regarding access to the data, not [creating] the TABLE.

If choosing to interpret the message instead as an issue with the file, then rather than re-creating the TABLE, the recovery is to issue the Start Journaling Physical File (STRJRNPF) against the TABLE that was created. That recovery is described later in the SQL7905 Recovery text, but that text was omitted from the SQL7905 quoted in the OP.

A TABLE is either created with automatic journaling effected, or not. When not, the SQL7905 is issued to warn that access to the data using isolation other than *NC will fail. As a warning, the message indicates that the automatic journaling was not effected per lack of one of:
a) a QSQJRN *JRN object in the library (SCHEMA) into which the SQL TABLE was created
b) a prior STRJRNLIB of that library, that includes the database *FILE
c) a QDFTJRN data area in that library that include the database *FILE object

If any one of those automated means to effect journaling was intended but was overlooked, then instead of STRJRNPF as recovery, the recovery could instead be DROP TABLE followed by a new CREATE TABLE, but *after* one of the above-noted options a), b), and c) is enabled before re-creating the TABLE. That is to suggest, that if the journaling is desirable, either for logging or to allow any isolation-level [AKA commitment-control-level] other than *NC, then effect either automatic journaling or use the Start Journal Physical File (STRJRNPF) after the CREATE TABLE.






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact