The forum will be down for maintenance at some point from Friday, November 16 at 19:00 EDT until Sunday, November 18 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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.

asked 13 Oct, 17:25

Breck%20Carter's gravatar image

Breck Carter
28.1k477636916
accept rate: 20%


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
permanent link

answered 14 Oct, 04:22

John%20Smirnios's gravatar image

John Smirnios
9.5k381121
accept rate: 39%

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?

(15 Oct, 05:39) Volker Barth
Replies hidden

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.

(15 Oct, 07:12) John Smirnios
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×16

question asked: 13 Oct, 17:25

question was seen: 71 times

last updated: 15 Oct, 07:12