DB2: SELECT statements do not support DB2-specific ending clauses

[GabrielBoehme13]

Search before asking

• I searched the issues and found no similar issues.

What Happened

The DB2 SELECT queries I support include clauses to ensure that they are treated as read-only, and to allow uncommitted reads. These clauses are not currently recognized by SQLFluff as valid DB2 SQL.

See IBM's documentation for more details on the possible ending clauses for DB2 select statements. The specific unsupported clauses I'm providing examples for below are read-only-clause and isolation-clause.

Expected Behaviour

DB2 queries which specify these clauses should be parsed as valid DB2 SQL.

Observed Behaviour

SQLFluff reports Found unparsable section when the SQL includes a read-only-clause and/or an isolation-clause.

The reported location of the parsing error changes depending on whether or not the query also includes an order by clause.

How to reproduce

Save a file called test.sql containing the following valid DB2 SELECT statements:

select 0 as test 
from sysibm.sysdummy1
for read only;

select 0 as test
from sysibm.sysdummy1
order by test
for read only;

select 0 as test
from sysibm.sysdummy1
with ur;

select 0 as test
from sysibm.sysdummy1
order by test
with ur;

select 0 as test
from sysibm.sysdummy1
for read only
with ur;

select 0 as test
from sysibm.sysdummy1
order by test
for read only
with ur;

In the folder where you saved that file, run the following command line:

sqlfluff lint --dialect db2 --rules ST01 test.sql

It will produce the following output:

== [test.sql] FAIL
L:   3 | P:   5 |  PRS | Line 3, Position 5: Found unparsable section: 'read
                       | only'
L:   8 | P:   1 |  PRS | Line 8, Position 1: Found unparsable section: 'for read
                       | only'
L:  12 | P:   6 |  PRS | Line 12, Position 6: Found unparsable section: 'ur'
L:  17 | P:   1 |  PRS | Line 17, Position 1: Found unparsable section: 'with ur'
L:  21 | P:   5 |  PRS | Line 21, Position 5: Found unparsable section: 'read
                       | only\nwith ur'
L:  27 | P:   1 |  PRS | Line 27, Position 1: Found unparsable section: 'for read
                       | only\nwith ur'
WARNING: Parsing errors found and dialect is set to 'db2'. Have you configured your dialect correctly?
All Finished!

Dialect

DB2

Version

SQLFluff version 3.0.6
Python version 3.10.5

Configuration

# my "How to reproduce" section above does not require a configuration file to be provided
# the relevant options are specified on the command line

Are you willing to work on and submit a PR to address the issue?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct