Oppolzer - Informatik / Blog

Ok ... when I built my own ETL tool some years ago,
I was hurt by a nonzero SQL code on z/OS, when I forgot one time
to remove the semi-colon. I didn't care much, if it was a positive or negative SQL code.
For me, every non zero SQL code from PREPARE (that I don't know) is a problem ...
there are some exceptions, for example the warning, that there is no WHERE condition,
which has to be handled, of course.

Anyway: many thanks for the explanation how DSNTIAUL works.
I guess, it gets the position of the semicolon in the SQL statement text from SQLERRD(5),
as it is the case with other syntax errors? Not much DB2 developers know about this
facility, IMO; I learned about this in this mailing list, too, so I think it is a good idea to
talk about this from time to time.

Kind regards

Bernd




Am 15.01.2015 um 08:14 schrieb J.C:
> Since DB2 on z/OS provides an explicit sqlcode (+98) to indicate a terminating semi-colon, I
> would be really surprised if it didn't accept it.  And, no, positive sqlcodes are not "error"
> conditions.
>
> DSNTIAUL uses the sqlcode 98 to determine where statements end - unlike DSNTIAD and
> DSNTEP2 which trawl through the text looking for the semi-colon (or, if you have set it, your
> particular terminator).  The processing associated with this is the reason why you cannot
> create triggers which have a BEGIN / END construct using DSNTIAUL.
>
> Another difference between the DB2 flavours.
>
> But if you want a JDBC application that talks to DB2 LUW, you'll have to remove the
> semi-colons - as DB2 LUW doesn't accept them.
>
>
> J.C.
>
>
> On 13 Jan 2015 at 18:01, Bernd Oppolzer wrote:
>
>> IMO, you should change the Java app.
>>
>> I'm kind of surprised that DB2 on z/OS accepts the terminating semicolon;
>> I recall from older releases that this was not the case; when I did a dynamic
>> PREPARE and forgot to remove the semicolon, I got errors from DB2 on z/OS.
>>
>> The semicolon is used by programs like SPUFI, DSNTEP2 and other tools (DB visualizer)
>> as a SQL statement separator, but it is not part of the SQL statement, and has
>> to be removed, before it is sent to DB2 using dynamic PREPARE; this is what
>> should be standard processing, no matter what host language you use.
>>
>> HTH,
>> kind regards
>>
>> Bernd
>>