[v9 z/OS] DSNTIAUL and CTE

Philip Sevetson

[v9 z/OS] DSNTIAUL and CTE
I've been in the habit, for some years, of writing SQL which generated such things as ALTER TABLE/ALTER PARTITION statements, by using DSNTIAUL and writing to a sequential file.

Today, however, I seem to have stumbled across a problem. I created a new statement, using sequential and dependent Common Table Expressions (WITH tablename AS fullselect), with later expressions referring to the earlier ones. I debugged it using QMF and got a working solution which wrote out correctly sequenced ALTER statements to change the limitkeys on a date-partitioned tablespace.

Then I ran it in DSNTIAUL, and got invalid blocksizes and empty result sets. Nothing I changed made any difference.

Question for the gallery, please; does anyone have any reason to believe that DSNTIAUL would _not_ be able to handle Common Table Expressions? Because that's what I think I'm seeing. If I run the SQL in DSNTEP2, or SPUFI, or QMF... no problem. If I run it in DSNTIAUL... no results.

--Phil Sevetson

Raymond Bell

[v9 z/OS] DSNTIAUL and CTE
(in response to Philip Sevetson)
Phil,

Could it be because DSNTIAUL takes your Sysin line and prefixes 'Select * from' to it? So if your SQL starts with 'With tablename AS' then you effectively create an invalid SQL statement.

Could be wrong. It's been (and is being) a long day...

Cheers,


Raymond

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 18 July 2011 16:24
To: '[login to unmask email]'
Subject: [DB2-L] - [v9 z/OS] DSNTIAUL and CTE

I've been in the habit, for some years, of writing SQL which generated such things as ALTER TABLE/ALTER PARTITION statements, by using DSNTIAUL and writing to a sequential file.

Today, however, I seem to have stumbled across a problem. I created a new statement, using sequential and dependent Common Table Expressions (WITH tablename AS fullselect), with later expressions referring to the earlier ones. I debugged it using QMF and got a working solution which wrote out correctly sequenced ALTER statements to change the limitkeys on a date-partitioned tablespace.

Then I ran it in DSNTIAUL, and got invalid blocksizes and empty result sets. Nothing I changed made any difference.

Question for the gallery, please; does anyone have any reason to believe that DSNTIAUL would _not_ be able to handle Common Table Expressions? Because that's what I think I'm seeing. If I run the SQL in DSNTEP2, or SPUFI, or QMF... no problem. If I run it in DSNTIAUL... no results.

--Phil Sevetson

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

bernd oppolzer

[v9 z/OS] DSNTIAUL and CTE
(in response to Philip Sevetson)
I could imagine that DSNTIAUL checks the first word of the SQL statement
of the input to see if it is a valid (that is: known to DSNTIAUL) SQL
statement.
And the SELECT statements with CTEs start with WITH, which is uncommon
for "classical" SQL statements.

I used the same approach in a similar program I wrote myself, and I ran
into
the same problem :-(

I have still on my personal to-do-list to fix it :-(

Kind regards

Bernd



Am 18.07.2011 17:24, schrieb Sevetson, Phil:
>
> I've been in the habit, for some years, of writing SQL which generated
> such things as ALTER TABLE/ALTER PARTITION statements, by using
> DSNTIAUL and writing to a sequential file.
>
> Today, however, I seem to have stumbled across a problem. I created a
> new statement, using sequential and dependent Common Table Expressions
> (WITH tablename AS fullselect), with later expressions referring to
> the earlier ones. I debugged it using QMF and got a working solution
> which wrote out correctly sequenced ALTER statements to change the
> limitkeys on a date-partitioned tablespace.
>
> Then I ran it in DSNTIAUL, and got invalid blocksizes and empty result
> sets. Nothing I changed made any difference.
>
> Question for the gallery, please; does anyone have any reason to
> believe that DSNTIAUL would _/not/_ be able to handle Common Table
> Expressions? Because that's what I think I'm seeing. If I run the
> SQL in DSNTEP2, or SPUFI, or QMF... no problem. If I run it in
> DSNTIAUL... no results.
>
> --Phil Sevetson
>
>

Pete Woodman

RE: [v9 z/OS] DSNTIAUL and CTE
(in response to Philip Sevetson)

Phil,

 

I believe that the problem is due to the SQL code +347 (Infinite loop) ... try adding:

WITH RPL (LEVEL ....

SELECT 1, .....

UNION ALL

SELECT LEVEL + 1, ......

....

AND LEVEL < 10000)

 

I've used this with DSNTIAUL and it works OK. Not quite sure why DSNTIAUL fails even with TOLWARN(YES) tho!

Philip Sevetson

[v9 z/OS] DSNTIAUL and CTE
(in response to Raymond Bell)
Raymond,
I'm running with PARM('SQL') so it shouldn't be doing that.

Looks like Pete may have identified the problem in the previous post - now I have to create a recursive SQL which doesn't trip the "infinite loop" flag to check his hypothesis.

--Phil

________________________________
From: Bell, Raymond [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 11:32 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE

Phil,

Could it be because DSNTIAUL takes your Sysin line and prefixes 'Select * from' to it? So if your SQL starts with 'With tablename AS' then you effectively create an invalid SQL statement.

Could be wrong. It's been (and is being) a long day...

Cheers,


Raymond

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 18 July 2011 16:24
To: '[login to unmask email]'
Subject: [DB2-L] - [v9 z/OS] DSNTIAUL and CTE

I've been in the habit, for some years, of writing SQL which generated such things as ALTER TABLE/ALTER PARTITION statements, by using DSNTIAUL and writing to a sequential file.

Today, however, I seem to have stumbled across a problem. I created a new statement, using sequential and dependent Common Table Expressions (WITH tablename AS fullselect), with later expressions referring to the earlier ones. I debugged it using QMF and got a working solution which wrote out correctly sequenced ALTER statements to change the limitkeys on a date-partitioned tablespace.

Then I ran it in DSNTIAUL, and got invalid blocksizes and empty result sets. Nothing I changed made any difference.

Question for the gallery, please; does anyone have any reason to believe that DSNTIAUL would _not_ be able to handle Common Table Expressions? Because that's what I think I'm seeing. If I run the SQL in DSNTEP2, or SPUFI, or QMF... no problem. If I run it in DSNTIAUL... no results.

--Phil Sevetson

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

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

Philip Sevetson

[v9 z/OS] DSNTIAUL and CTE
(in response to Pete Woodman)
Pete,
This didn't fix my CTE, I'm still getting the +347 -- This is the CTE piece I'm getting the warning on:

WITH
LIMKEYS ( ARBITRARY_LIMIT
, LPART
, PREV_LPART
, LIMIT_KEY
, PREV_LIMIT_KEY
)
AS
(
SELECT 1
, TGT_TS.PARTITIONS
, 0
, DATE(LAST_DAY(CURRENT DATE))
, CURRENT DATE
FROM TGT_TS
UNION ALL
SELECT ARBITRARY_LIMIT + 1
, LPART - 1
, LPART
, LAST_DAY(LIMIT_KEY - 1 MONTH)
, LIMIT_KEY
FROM LIMKEYS
, TGT_TBL
WHERE LPART > 1
AND ARBITRARY_LIMIT < 4097
),

And you're right, the other processors run it. DSNTIAUL doesn't. Dammit.
________________________________
From: Pete Woodman [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 11:55 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE


Phil,



I believe that the problem is due to the SQL code +347 (Infinite loop) ... try adding:

WITH RPL (LEVEL ....

SELECT 1, .....

UNION ALL

SELECT LEVEL + 1, ......

....

AND LEVEL < 10000)



I've used this with DSNTIAUL and it works OK. Not quite sure why DSNTIAUL fails even with TOLWARN(YES) tho!

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

Raymond Bell

[v9 z/OS] DSNTIAUL and CTE
(in response to Philip Sevetson)
Of course you were - silly me. How else were you going to get a multi-line CTE to feed into DSNTIAUL?

I'll go back to my long day now...


Raymond

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 18 July 2011 17:08
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE

Raymond,
I'm running with PARM('SQL') so it shouldn't be doing that.

Looks like Pete may have identified the problem in the previous post - now I have to create a recursive SQL which doesn't trip the "infinite loop" flag to check his hypothesis.

--Phil

________________________________
From: Bell, Raymond [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 11:32 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE

Phil,

Could it be because DSNTIAUL takes your Sysin line and prefixes 'Select * from' to it? So if your SQL starts with 'With tablename AS' then you effectively create an invalid SQL statement.

Could be wrong. It's been (and is being) a long day...

Cheers,


Raymond

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 18 July 2011 16:24
To: '[login to unmask email]'
Subject: [DB2-L] - [v9 z/OS] DSNTIAUL and CTE

I've been in the habit, for some years, of writing SQL which generated such things as ALTER TABLE/ALTER PARTITION statements, by using DSNTIAUL and writing to a sequential file.

Today, however, I seem to have stumbled across a problem. I created a new statement, using sequential and dependent Common Table Expressions (WITH tablename AS fullselect), with later expressions referring to the earlier ones. I debugged it using QMF and got a working solution which wrote out correctly sequenced ALTER statements to change the limitkeys on a date-partitioned tablespace.

Then I ran it in DSNTIAUL, and got invalid blocksizes and empty result sets. Nothing I changed made any difference.

Question for the gallery, please; does anyone have any reason to believe that DSNTIAUL would _not_ be able to handle Common Table Expressions? Because that's what I think I'm seeing. If I run the SQL in DSNTEP2, or SPUFI, or QMF... no problem. If I run it in DSNTIAUL... no results.

--Phil Sevetson

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

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

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

David Simpson

[v9 z/OS] DSNTIAUL and CTE
(in response to Philip Sevetson)
Looks like you don't have a join predicate on the second half of your
CTE (TGT_TBL to LIMITKEYS)... is this what you want? This may be the
reason for the +347 even though you have the loop mitigation technique
coded.



From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 11:08 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE



Pete,

This didn't fix my CTE, I'm still getting the +347 -- This is the CTE
piece I'm getting the warning on:



WITH

LIMKEYS ( ARBITRARY_LIMIT

, LPART

, PREV_LPART

, LIMIT_KEY

, PREV_LIMIT_KEY

)

AS

(

SELECT 1

, TGT_TS.PARTITIONS

, 0

, DATE(LAST_DAY(CURRENT DATE))

, CURRENT DATE

FROM TGT_TS

UNION ALL

SELECT ARBITRARY_LIMIT + 1

, LPART - 1

, LPART

, LAST_DAY(LIMIT_KEY - 1 MONTH)

, LIMIT_KEY

FROM LIMKEYS

, TGT_TBL

WHERE LPART > 1

AND ARBITRARY_LIMIT < 4097

),



And you're right, the other processors run it. DSNTIAUL doesn't.
Dammit.

________________________________

From: Pete Woodman [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 11:55 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE



Phil,



I believe that the problem is due to the SQL code +347 (Infinite loop)
... try adding:

WITH RPL (LEVEL ....

SELECT 1, .....

UNION ALL

SELECT LEVEL + 1, ......

....

AND LEVEL < 10000)



I've used this with DSNTIAUL and it works OK. Not quite sure why
DSNTIAUL fails even with TOLWARN(YES) tho!



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



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

Pete Woodman

RE: [v9 z/OS] DSNTIAUL and CTE
(in response to Philip Sevetson)

Phil,

I've used this method more than once. The key will be to remove the +497 using Spufi etc, then run it in DSTIAUL.

I can't see why your SQL is getting the +497 tho!

Philip Sevetson

[v9 z/OS] DSNTIAUL and CTE
(in response to David Simpson)
David - yeah, TGT_TBL identifies a single row from SYSIBM.SYSTABLES.

I'm posting the whole thing here; don't anyone break your brains, please, this took me a few hours to initially code and debug enough that QMF would run it.

WITH TGT_TBL
AS
(
SELECT 'CR8OR1' CREATOR
, 'PARTITIONED_TABLE_1' NAME
, CHAR(TB.TSNAME,8) TSNAME
, CHAR(TB.DBNAME,8) DBNAME
FROM SYSIBM.SYSTABLES TB
WHERE CREATOR = 'CR8OR1'
AND NAME = 'PARTITONED_TABLE_1'
),

TGT_TS
AS
(
SELECT PARTITIONS
FROM SYSIBM.SYSTABLESPACE TS,
TGT_TBL
WHERE TS.NAME = TGT_TBL.TSNAME
AND TS.DBNAME = TGT_TBL.DBNAME
AND PARTITIONS > 0
FETCH FIRST 1 ROW ONLY
),

LIMKEYS ( LEVEL
, LPART
, PREV_LPART
, LIMIT_KEY
, PREV_LIMIT_KEY
)
AS
(
SELECT 1
, TGT_TS.PARTITIONS
, 0
, DATE(LAST_DAY(CURRENT DATE))
, CURRENT DATE
FROM TGT_TS
UNION ALL
SELECT LEVEL + 1
, LPART - 1
, LPART
, LAST_DAY(LIMIT_KEY - 1 MONTH)
, LIMIT_KEY
FROM LIMKEYS
, TGT_TBL
WHERE LPART > 1
AND LEVEL < 4097
),

LIMKEYS2
AS
(
SELECT LK.LPART, LK.LIMIT_KEY, PT.PARTITION
FROM LIMKEYS LK
, SYSIBM.SYSTABLEPART PT
, TGT_TBL
WHERE PT.LOGICAL_PART = LK.LPART
AND TGT_TBL.TSNAME = PT.TSNAME
AND TGT_TBL.DBNAME = PT.DBNAME
),

LIMKEYS3
AS
(
SELECT LIMKEYS2.LPART
, 'ALTER TABLE '
CONCAT TGT_TBL.CREATOR
CONCAT '.'
CONCAT TGT_TBL.NAME AS LINE1
, ' ALTER PARTITION '
CONCAT CHAR(LIMKEYS2.PARTITION)
CONCAT 'ENDING AT ('''
CONCAT CHAR(LIMKEYS2.LIMIT_KEY)
CONCAT ''');' AS LINE2
FROM LIMKEYS2
, TGT_TBL
, TGT_TS
WHERE LIMKEYS2.LPART < TGT_TS.PARTITIONS
UNION
SELECT LIMKEYS2.LPART
, 'ALTER TABLE '
CONCAT TGT_TBL.CREATOR
CONCAT '.'
CONCAT TGT_TBL.NAME AS LINE1
, ' ALTER PARTITION '
CONCAT CHAR(LIMKEYS2.PARTITION)
CONCAT 'ENDING AT (MAXVALUE);' AS LINE2
FROM LIMKEYS2
, TGT_TBL
, TGT_TS
WHERE LIMKEYS2.LPART = TGT_TS.PARTITIONS
ORDER BY 1 DESC
),

LIMKEYS4
AS
(
SELECT LPART
, '1' LINENUM
, LINE1 "DDL"
FROM LIMKEYS3
UNION
SELECT LPART
, '2' LINENUM
, LINE2 "DDL"
FROM LIMKEYS3
ORDER BY 1 DESC, 2
)

SELECT CHAR("DDL",80)
FROM LIMKEYS4;


________________________________
From: David Simpson [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 12:12 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE

Looks like you don't have a join predicate on the second half of your CTE (TGT_TBL to LIMITKEYS)... is this what you want? This may be the reason for the +347 even though you have the loop mitigation technique coded.

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 11:08 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE

Pete,
This didn't fix my CTE, I'm still getting the +347 -- This is the CTE piece I'm getting the warning on:

WITH
LIMKEYS ( ARBITRARY_LIMIT
, LPART
, PREV_LPART
, LIMIT_KEY
, PREV_LIMIT_KEY
)
AS
(
SELECT 1
, TGT_TS.PARTITIONS
, 0
, DATE(LAST_DAY(CURRENT DATE))
, CURRENT DATE
FROM TGT_TS
UNION ALL
SELECT ARBITRARY_LIMIT + 1
, LPART - 1
, LPART
, LAST_DAY(LIMIT_KEY - 1 MONTH)
, LIMIT_KEY
FROM LIMKEYS
, TGT_TBL
WHERE LPART > 1
AND ARBITRARY_LIMIT < 4097
),

And you're right, the other processors run it. DSNTIAUL doesn't. Dammit.
________________________________
From: Pete Woodman [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 11:55 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE


Phil,



I believe that the problem is due to the SQL code +347 (Infinite loop) ... try adding:

WITH RPL (LEVEL ....

SELECT 1, .....

UNION ALL

SELECT LEVEL + 1, ......

....

AND LEVEL < 10000)



I've used this with DSNTIAUL and it works OK. Not quite sure why DSNTIAUL fails even with TOLWARN(YES) tho!

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

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

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

Philip Sevetson

[v9 z/OS] DSNTIAUL and CTE
(in response to Pete Woodman)
Now it's not getting _any_ errors; the loop mitigator works in the version I posted, SQLCode=0 in SPUFI output.

________________________________
From: Pete Woodman [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 12:18 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE


Phil,

I've used this method more than once. The key will be to remove the +497 using Spufi etc, then run it in DSTIAUL.

I can't see why your SQL is getting the +497 tho!

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

Philip Sevetson

[v9 z/OS] DSNTIAUL and CTE
(in response to Philip Sevetson)
Whoops. SQLCODE=+100. And still no results from DSNTIAUL

________________________________
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 12:23 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE

Now it's not getting _any_ errors; the loop mitigator works in the version I posted, SQLCode=0 in SPUFI output.

________________________________
From: Pete Woodman [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 12:18 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE


Phil,

I've used this method more than once. The key will be to remove the +497 using Spufi etc, then run it in DSTIAUL.

I can't see why your SQL is getting the +497 tho!

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

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

Philip Sevetson

[v9 z/OS] DSNTIAUL and CTE -- FIXED
(in response to Philip Sevetson)
Okay, Guys? The fix worked. Problem was that I was running JCL with an unfixed version of the SQL embedded. Thanks, Pete.

David, Raymond, apologies for not having doublechecked my JCL.

--Phil

________________________________
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 12:24 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE

Whoops. SQLCODE=+100. And still no results from DSNTIAUL

________________________________
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 12:23 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE

Now it's not getting _any_ errors; the loop mitigator works in the version I posted, SQLCode=0 in SPUFI output.

________________________________
From: Pete Woodman [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 12:18 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE


Phil,

I've used this method more than once. The key will be to remove the +497 using Spufi etc, then run it in DSTIAUL.

I can't see why your SQL is getting the +497 tho!

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

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

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

Philip Sevetson

[v9 z/OS] DSNTIAUL and CTE -- FIXED
(in response to Philip Sevetson)
...and for anyone who's been following along on the sidelines, my original solution writes 80 bytes plus a null indicator. The fix for that, to eliminate the null indicator, is to change the final select to the following:

SELECT CHAR(COALESCE("DDL",REPEAT(' ',80)),80)
FROM (previous CTE)

________________________________
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 12:27 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE -- FIXED

Okay, Guys? The fix worked. Problem was that I was running JCL with an unfixed version of the SQL embedded. Thanks, Pete.

David, Raymond, apologies for not having doublechecked my JCL.

--Phil

________________________________
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 12:24 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE

Whoops. SQLCODE=+100. And still no results from DSNTIAUL

________________________________
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 12:23 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE

Now it's not getting _any_ errors; the loop mitigator works in the version I posted, SQLCode=0 in SPUFI output.

________________________________
From: Pete Woodman [mailto:[login to unmask email]
Sent: Monday, July 18, 2011 12:18 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [v9 z/OS] DSNTIAUL and CTE


Phil,

I've used this method more than once. The key will be to remove the +497 using Spufi etc, then run it in DSTIAUL.

I can't see why your SQL is getting the +497 tho!

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

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

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

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

James Campbell

[v9 z/OS] DSNTIAUL and CTE
(in response to bernd oppolzer)
Just for the record, DSNTIAUL only checks for statements that need to be executed by static
SQL. Everything else it just PREPAREs and then checks the SQLDA - if there are no output
columns it EXECUTEs the statement; otherwise it is OPEN, FETCH,... CLOSE.

It doesn't even search for the end of SQL statements - it depends on the SQLCODE +98 on
the prepare to tell it where one statement ends and the next starts. (Don't use DSNTIAUL to
CREATE VIEWs. The entire SQL text, including all text past the semi-colon, is stored in
SYSVIEWS.TEXT.)

James Campbell

On 18 Jul 2011 at 17:51, Bernd Oppolzer wrote:

>
> I could imagine that DSNTIAUL checks the first word of the SQL statement
> of the input to see if it is a valid (that is: known to DSNTIAUL) SQL statement.
> And the SELECT statements with CTEs start with WITH, which is uncommon
> for "classical" SQL statements.
>
> I used the same approach in a similar program I wrote myself, and I ran into
> the same problem :-(
>
> I have still on my personal to-do-list to fix it :-(
>
> Kind regards
>
> Bernd
>
<rest snipped>

bernd oppolzer

[v9 z/OS] DSNTIAUL and CTE
(in response to James Campbell)
Thank you, very good.

When I designed my program, I had the need to know if the
statement is SELECT or UPDATE / INSERT / DELETE even before
issuing the PREPARE, so I checked the first word. This ran fine until
the CTEs appeared :-(

Kind regards

Bernd



Am 21.07.2011 01:20, schrieb James Campbell:
> Just for the record, DSNTIAUL only checks for statements that need to be executed by static
> SQL. Everything else it just PREPAREs and then checks the SQLDA - if there are no output
> columns it EXECUTEs the statement; otherwise it is OPEN, FETCH,... CLOSE.
>
> It doesn't even search for the end of SQL statements - it depends on the SQLCODE +98 on
> the prepare to tell it where one statement ends and the next starts. (Don't use DSNTIAUL to
> CREATE VIEWs. The entire SQL text, including all text past the semi-colon, is stored in
> SYSVIEWS.TEXT.)
>
> James Campbell
>