Multi-Row Insert Dynamic Syntax in DB2V10 z/OS

Philip Sevetson

Multi-Row Insert Dynamic Syntax in DB2V10 z/OS
Does anyone know why direct multi-row INSERT isn't part of DB2V10? And whether they put it into DB2V11? It's in LUW and it's very convenient.

This:
INSERT INTO tablename(column1)
VALUES (value1),
(value2);
Doesn't work in v10 z/OS. The error is:

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES
MINUTE HOURS
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 162 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF'
X'000000A2' X'000001F6' SQL DIAGNOSTIC INFORMATION

Daniel Luksetich

Multi-Row Insert Dynamic Syntax in DB2V10 z/OS
(in response to Philip Sevetson)


What you have there is an insert with a fullselect, which happens to be
a VALUES statement. The VALUES statement does not exist in DB2 10 or 11
for z/OS, except in limited fashion within the context of a trigger. The
alternative is a full select containing multiple blocks of a UNION ALL.

Will the VALUES statement be included in the 11+1 version of DB2 for
z/OS. I certainly hope so.

Thanks,

Dan

On 02.17.2015 13:59, Sevetson, Phil wrote:

> Does anyone know why direct multi-row INSERT isn't part of DB2V10? And whether they put it into DB2V11? It's in LUW and it's very convenient.
>
> This:
>
> INSERT INTO tablename(column1)
>
> VALUES (value1),
>
> (value2);
>
> Doesn't work in v10 z/OS. The error is:
>
> DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT
>
> BE LEGAL ARE: AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES
>
> MINUTE HOURS
>
> DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
>
> DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
>
> DSNT416I SQLERRD = 3 0 0 -1 162 502 SQL DIAGNOSTIC INFORMATION
>
> DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF'
>
> X'000000A2' X'000001F6' SQL DIAGNOSTIC INFORMATION
>
> -----End Original Message-----


Links:
------
[1] http://www.idug.org/p/fo/st/post=168595&anc=p168595#p168595
[2] http://www.idug.org/p/fo/si/topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.idug.org/p/cm/ld/fid=551
[5] http://www.idug.org/p/cm/ld/fid=2

Philip Sevetson

Multi-Row Insert Dynamic Syntax in DB2V10 z/OS
(in response to Daniel Luksetich)
Dan,
I’m not sure I understand you. VALUES works fine as long as you’re only INSERTing one row. How is this consistent with what you’ve said about it?

From: Daniel Luksetich [mailto:[login to unmask email]
Sent: Tuesday, February 17, 2015 4:10 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Multi-Row Insert Dynamic Syntax in DB2V10 z/OS


What you have there is an insert with a fullselect, which happens to be a VALUES statement. The VALUES statement does not exist in DB2 10 or 11 for z/OS, except in limited fashion within the context of a trigger. The alternative is a full select containing multiple blocks of a UNION ALL.

Will the VALUES statement be included in the 11+1 version of DB2 for z/OS. I certainly hope so.

Thanks,

Dan




On 02.17.2015 13:59, Sevetson, Phil wrote:
Does anyone know why direct multi-row INSERT isn’t part of DB2V10? And whether they put it into DB2V11? It’s in LUW and it’s very convenient.

This:
INSERT INTO tablename(column1)
VALUES (value1),
(value2);
Doesn’t work in v10 z/OS. The error is:

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES
MINUTE HOURS
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 3 0 0 -1 162 502 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000003' X'00000000' X'00000000' X'FFFFFFFF'
X'000000A2' X'000001F6' SQL DIAGNOSTIC INFORMATION


-----End Original Message-----

-----End Original Message-----

Peter Vanroose

RE: Multi-Row Insert Dynamic Syntax in DB2V10 z/OS
(in response to Philip Sevetson)

DB2 LUW has a "VALUES" *statement*, which is more general than the VALUES *clause* of the INSERT statement.

For example, the following is a valid (full) statement in DB2 LUW:

VALUES (current date);

Strangely enough, DB2 LUW is the only RDBMS (afaik) which supports this statement. Is it standard (ISO) SQL?

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/html/enindex.html


In Reply to Philip Sevetson:

I’m not sure I understand you. VALUES works fine as long as you’re only INSERTing one row. How is this consistent with what you’ve said about it?

Daniel Luksetich

Multi-Row Insert Dynamic Syntax in DB2V10 z/OS
(in response to Peter Vanroose)
That’s it!

From: Peter Vanroose [mailto:[login to unmask email]
Sent: Tuesday, February 17, 2015 5:04 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Multi-Row Insert Dynamic Syntax in DB2V10 z/OS

DB2 LUW has a "VALUES" *statement*, which is more general than the VALUES *clause* of the INSERT statement.
For example, the following is a valid (full) statement in DB2 LUW:
VALUES (current date);
Strangely enough, DB2 LUW is the only RDBMS (afaik) which supports this statement. Is it standard (ISO) SQL?
-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/html/enindex.html

In Reply to Philip Sevetson:
I’m not sure I understand you. VALUES works fine as long as you’re only INSERTing one row. How is this consistent with what you’ve said about it?

-----End Original Message-----

Jørn Thyssen

RE: Multi-Row Insert Dynamic Syntax in DB2V10 z/OS
(in response to Philip Sevetson)

Hi Philip,

Is it possible for your programs to use the normal multi-row INSERT?

INSERT INTO tablename FOR :hv ROWS
VALUES( :value_array) ATOMIC;

 

Best regards,

Jørn Thyssen

Works for IBM Denmark. Views are personal. 

Philip Sevetson

Multi-Row Insert Dynamic Syntax in DB2V10 z/OS
(in response to Jørn Thyssen)
Not programs. SPUFI.

From: Jørn Thyssen [mailto:[login to unmask email]
Sent: Wednesday, February 18, 2015 7:47 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Multi-Row Insert Dynamic Syntax in DB2V10 z/OS


Hi Philip,

Is it possible for your programs to use the normal multi-row INSERT?

INSERT INTO tablename FOR :hv ROWS
VALUES( :value_array) ATOMIC;



Best regards,

Jørn Thyssen

Works for IBM Denmark. Views are personal.

-----End Original Message-----

Philip Sevetson

Multi-Row Insert Dynamic Syntax in DB2V10 z/OS
(in response to Daniel Luksetich)
Ah. Dan, Peter, thanks for the clarification.

From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Tuesday, February 17, 2015 7:25 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Multi-Row Insert Dynamic Syntax in DB2V10 z/OS

That’s it!

From: Peter Vanroose [mailto:[login to unmask email]
Sent: Tuesday, February 17, 2015 5:04 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Multi-Row Insert Dynamic Syntax in DB2V10 z/OS


DB2 LUW has a "VALUES" *statement*, which is more general than the VALUES *clause* of the INSERT statement.

For example, the following is a valid (full) statement in DB2 LUW:

VALUES (current date);

Strangely enough, DB2 LUW is the only RDBMS (afaik) which supports this statement. Is it standard (ISO) SQL?

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/html/enindex.html

In Reply to Philip Sevetson:
I’m not sure I understand you. VALUES works fine as long as you’re only INSERTing one row. How is this consistent with what you’ve said about it?

-----End Original Message-----

-----End Original Message-----

Phil Grainger

Multi-Row Insert Dynamic Syntax in DB2V10 z/OS
(in response to Philip Sevetson)
I don’t think a multi-row insert can also be dynamic – the inserted rows have to be in a host variable array

________________________________

Phil Grainger

Lead Product Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[BMC Software|Bring IT to Life]http://www.bmc.com





From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 18 February 2015 14:54
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Multi-Row Insert Dynamic Syntax in DB2V10 z/OS

Not programs. SPUFI.

From: Jørn Thyssen [mailto:[login to unmask email]
Sent: Wednesday, February 18, 2015 7:47 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Multi-Row Insert Dynamic Syntax in DB2V10 z/OS


Hi Philip,

Is it possible for your programs to use the normal multi-row INSERT?

INSERT INTO tablename FOR :hv ROWS
VALUES( :value_array) ATOMIC;



Best regards,

Jørn Thyssen

Works for IBM Denmark. Views are personal.

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)

Peter Vanroose

RE: Multi-Row Insert Dynamic Syntax in DB2V10 z/OS
(in response to Philip Sevetson)

In Reply to Philip Sevetson:

Not programs. SPUFI.


In that case it will have to be multi-row multi-insert statements, instead of a multi-row single-insert.
Not really a big difference, I guess, for a SPUFI massive insert.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/html/enindex.html

Philip Sevetson

Multi-Row Insert Dynamic Syntax in DB2V10 z/OS
(in response to Peter Vanroose)
Yes, that was our conclusion as well.

From: Peter Vanroose [mailto:[login to unmask email]
Sent: Wednesday, February 18, 2015 4:51 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Multi-Row Insert Dynamic Syntax in DB2V10 z/OS


In Reply to Philip Sevetson:
Not programs. SPUFI.

In that case it will have to be multi-row multi-insert statements, instead of a multi-row single-insert.
Not really a big difference, I guess, for a SPUFI massive insert.

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/html/enindex.html

-----End Original Message-----