What are the rules for WITH ... LOGGING inside BEGIN PARALLEL WORK?

[Breck_Carter]

Is WITH FILE NAME LOGGING supported for LOAD TABLE inside BEGIN PARALLEL WORK?

Is it actually required?

Is it the default?

The BEGIN PARALLEL WORK statement shows LOAD TABLE statements without any WITH ... LOGGING clauses:

BEGIN PARALLEL WORK
    LOAD TABLE dba.Part
        FROM 'D:\\\\data\\\\part.tbl'
        FORMAT 'ASCII'
        QUOTES OFF ESCAPES ON STRIP OFF HEXADECIMAL OFF
        DELIMITED BY '|'
        ORDER OFF;
    LOAD TABLE dba.Supplier
        FROM 'D:\\\\data\\\\supplier.tbl'
        FORMAT 'ASCII'
        QUOTES OFF ESCAPES ON STRIP OFF HEXADECIMAL OFF
        DELIMITED BY '|'
        ORDER OFF;
    LOAD TABLE dba.Partsupp
        FROM 'D:\\\\data\\\\partsupp.tbl'
        FORMAT 'ASCII'
        QUOTES OFF ESCAPES ON STRIP OFF HEXADECIMAL OFF
        DELIMITED BY '|'
        ORDER OFF;
END PARALLEL WORK;

However, the Help for LOAD TABLE statement says WITH ROW LOGGING is the default but is not allowed inside BEGIN PARALLEL WORK:

WITH FILE NAME LOGGING clause
...
When you do not specify a logging level in the LOAD TABLE statement, 
WITH ROW LOGGING is the default level when specifying:
FROM filename-expression
USING FILE filename-expression
...
WITH ROW LOGGING clause
...
When the LOAD TABLE statement is inside the BEGIN PARALLEL WORK statement, 
this clause is not supported.
...
WITH CONTENT LOGGING clause
...
When the LOAD TABLE statement is inside the BEGIN PARALLEL WORK statement, 
this clause is not supported.

[johnsmirnios · Accepted solution]

The documentation is incorrect. The "WITH FILE NAME LOGGING clause" section should say "When you do not specify a logging level in the LOAD TABLE statement, WITH FILE NAME LOGGING is the default level when specifying ..."

I think we've chosen a rather confusing way to describe defaults here. As far as BEGIN PARALLEL WORK and LOAD TABLE go:

• Only FROM or USING FILE is supported
• Only WITH FILE NAME LOGGING is supported
• WITH CHECKPOINT ON is not supported
• If the current connection has wait_for_commit = 'On', the statement is not allowed
• Only base tables and global temporary tables with shared access are allowed
• Tables with a text index are not supported
• Specifying the same table more than once within the parallel block is not allowed

[VolkerBarth · Accepted solution]

Only WITH FILE NAME LOGGING is supported

So I conclude that this is also the default for LOAD TABLE within BEGIN PARALLEL WORK and does not need to be specified, right?

[johnsmirnios · Accepted solution]

Yes. WITH FILE NAME LOGGING is always the default except, IIRC, when replication is involved. Otherwise, remote clients would need to have that input file present in order to replay the LOAD operation.