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.
midrange.com
