a question on "High performance SQL"

[Jean-Marc Blaise]

Hi Serge,

I have a question on your exciting recent article on High Perf. SQL: why do
we have to describe datatype for include columns in an INSERT .... INCLUDE
.... statement - in your example, the columns come from the DATA CTE, so
datatype are well known ?

Secondly, you can create such SQL: WITH CTE AS (VALUES(1),(2)) SELECT * FROM
CTE; and DB2 does not need to have a datatype, but will choose an integer.
So why in some cases, no datatype is needed, in some cases, datatype are
needed; what I find "strange" is to have a "mix" kind of DDL in DML?

Thanks in advance & Best regards,

Jean-Marc

[Serge Rielau]

You are correct that the data type could be derived from the insert source.
Indeed development had many debates on the topic.
The reason to go with explicit typing was that the other types (i.e. the
columns of the insert target) are also explicitly typed.
Note that long term nothing is lost.
We could make the data type optional in the future.
Note that typing is not a DDL feature and type derivation is not a DML
feature. DML supports the CAST specifiction and DDL supports type
derivation in CREATE VIEW and CREATE TABLE AS. (CREATE VIEW in a way
supports both with typed views having explicit column types)
So the language is sufficiently flexible to roll with the punches and
still look consistent.

Here is some hypothetical SQL:
SELECT * FROM T AS Q(a1 INT, b1 DOUBLE)
CREATE VIEW v1(c1 INT, c2 DOUBLE) AS ....

Cheers
Serge
Cheers
Serge