Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Jul 18
    2011

    [v9 z/OS] DSNTIAUL and CTE

    Philip Sevetson
    [FISA/City of New York]
    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
    [Lloyds Banking Group]
    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
    [oppolzer]
    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
    [HP]

    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
    [FISA/City of New York]
    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
    [FISA/City of New York]
    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
    [Lloyds Banking Group]
    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
    [Themis Inc.]
    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
    [HP]

    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
    [FISA/City of New York]
    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
    [FISA/City of New York]
    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
    [FISA/City of New York]
    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
    [FISA/City of New York]
    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
    [FISA/City of New York]
    ...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
    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
    [oppolzer]
    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
    >

    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact