Date, Time, Timestap updatesin nCICS

[Graham Hobbs]

Hello,
Have searched for an explicit answer to my COBOL/CICS programming
problem.

- on a CICS screen a user enters a key to select a row which includes
Date, Time and Timestamp columns plus other columns
- all go on screen for user update (all updatable except key)
- when changes are made user presses F2 all changed columns are edited
- if all are clean then the row is updated
- if not all clean then the row is not updated
- very straightforward!

.. however, BEFORE ATTEMPTING THE ROW UPDATE, my edits are e.g:
- a numeric field is edited by IF FIELDA IS NUMERIC .. very easy
- if a Date field it is edited by .. what? .. same question for Time
and Timestamp fields

Have looked at the DB2 functions related to these data types but
nothing stands out.

I don't think it's appropriate to code reams of COBOL (maybe it is?)
so am looking for some DB2 function that will validate these columns
before going to the actual row update step.
Is there a ROT for this?

Help appreciated, thanks,
Graham Hobbs

[TheBoss]

Graham Hobbs <gho...@cdpwise.net> wrote in
news:s8h7d7hrcd04upb7n...@4ax.com:

You didn't provide versions or even platform, so this might or might not
work, but at least it should give you some ideas:
< http://ibmmainframes.com/about23946.html >

HTH.

Cheers!

--
Jeroen

[TheBoss]

TheBoss <The...@invalid.nl> wrote in
news:Xns9FAC78A126...@194.109.133.133:

An alternative date validation (outside DB2) can be found here:
< http://www.csis.ul.ie/cobol/examples/default.htm >
Go to the section "CALLing sub-programs" and look for the following
programs:
1. DateDriver.cbl
2. ValiDate.cbl

Cheers!

--
Jeroen

[Graham Hobbs]

---
Hi there TheBoss,
Excellent references - I missed them!
Seems like there is no 'one liner' DB2 function to do what I want so
am doing thinking - am not a guru with DB2.
I thought one could just 'hand DB2 a field' asking if it is a valid
date, time or timestamp field - not that simple! sysibm.sysdummy1
might offer a way - will give it a try.
Sorry, forgot the platform bit - developing CICS/DB2 pgms on a Windows
XP platform with DB2 V9.1.0 but pgms are heading for any z/OS platform
that will buy the pgms:-)
Will take the liberty of posing another question later if you don't
mind..
cheers very much,
Graham

[Graham Hobbs]

On 28 Nov 2011 23:18:05 GMT, TheBoss <The...@invalid.nl> wrote:

------
Hi Jeroen,
Sorry to be so longwinded but unless I'm missing something, reading
tells me the sysibm.sysdummy1 route provides a neat answer, almost
like IBM has forethought this matter and provided this little incore
table for purposes such as mine - except I can't make it work and am
hoping you can help, please.

.. my table COBOL DCLGEN is ..

EXEC SQL DECLARE DTTS TABLE
ACHAR CHAR(5) NOT NULL,
ADATE DATE,
ATIME TIME,
ATIMESTAMP TIMESTAMP
END-EXEC.
01 DTTS.
05 ACHAR PIC X(5).
05 ADATE PIC X(10).
05 ATIME PIC X(8).
05 ATIMESTAMP PIC X(26).

.. so using one of the refs you pointed to, in my COBOL pgm I issue ..

EXEC SQL
SELECT 1
FROM SYSIBM.SYSDUMMY1
WHERE :ADATE < CURRENT_DATE
END-EXEC.

.. and the compile says ..

SQL0029N INTO clause required.
Explanation:
Non-cursor SELECT or VALUES statements embedded in an application
program must have an INTO clause to denote where the results of the
statement are to be placed. Dynamic SELECT statements do not permit
the INTO clause.
User response:
Add the INTO clause to the SELECT or VALUES statement and precompile
the application program again.

.. so I change the WHERE clause to ..

WHERE :ADATE < CURRENT_DATE INTO SYSIBM.SYSDUMMY1

.. and I get ..

SQL0104N An unexpected token "INTO" was found following
"00012 < CURRENT_DATE". Expected tokens may include: ".".
SQLSTATE=42601
SQL0104NAn unexpected token token was found following text. Expected
tokens may include: token-list.
Explanation:
A syntax error in the SQL statement or the input command string for
the SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text text. The text field indicates the 20 characters of
the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.
As an aid, a partial list of valid tokens is provided in the SQLERRM
field of the SQLCA as token-list. This list assumes the statement is
correct to that point.
The statement cannot be processed.
User response:
Examine and correct the statement in the area of the specified token.
sqlcode: -104
sqlstate: 42601

.. I examined my SQL but don't know where I'm going wrong.
Any chance you can help would be appreciated,
thanks,
Graham

[Lennart Jonsson]

On 2011-11-29 18:08, Graham Hobbs wrote:
[...]

> .. so using one of the refs you pointed to, in my COBOL pgm I issue ..
>
> EXEC SQL
> SELECT 1
> FROM SYSIBM.SYSDUMMY1
> WHERE :ADATE < CURRENT_DATE
> END-EXEC.
>
> .. and the compile says ..
>
> SQL0029N INTO clause required.
> Explanation:
> Non-cursor SELECT or VALUES statements embedded in an application
> program must have an INTO clause to denote where the results of the
> statement are to be placed. Dynamic SELECT statements do not permit
> the INTO clause.
> User response:
> Add the INTO clause to the SELECT or VALUES statement and precompile
> the application program again.
>
> .. so I change the WHERE clause to ..
>
> WHERE :ADATE < CURRENT_DATE INTO SYSIBM.SYSDUMMY1
>

Just guessing, but shouldn't that be:

EXEC SQL
SELECT 1 INTO :A_VARIABLE

FROM SYSIBM.SYSDUMMY1
WHERE :ADATE < CURRENT_DATE
END-EXEC.

?

/Lennart

[CRPence]

A given date less than current date... Really? I know the example
was taken almost directly from a prior offered link, but that is some
very specific logic included in an apparent attempt merely to "validate"
a[n unspecified bounds] date string.

> .. so I change <<SNIP>>

>
> .. I examined my SQL but don't know where I'm going wrong.
> Any chance you can help would be appreciated,

The first error suggests the dummy table usage is unnecessary; i.e.
the VALUES INTO can be used instead. Given variable "r" is defined as
an integer with value of zero, and the ten-byte character variable with
a value that is supposed to represent a date is named CharDate, then try:

EXEC SQL
VALUES days( date( :CharDate ) ) INTO :r
END-EXEC.

Test SQLSTATE for 01534 [or whatever other date\time related errors
are possible, or not equal to zeroes], or test to ensure that "r>0" is
true to confirm that the date string was a valid value according to the
allowed date-string representations. FWiW, I believe that if an
indicator is added to the host variable, the value would be negative
one; i.e. given use of "INTO :r :i" instead of just "INTO :r" as shown,
then the test for a valid date value could be "if i>=0".?

The examples given are presumed to function the same as [they
functioned as tested, though in RPG instead of COBOL, using] the DB2 for i.

This variation could give more details about the value beyond
validity, such as if past, present, or future:

VALUES sign(days(:CinpDate)-days(current_date)) into :r :i

This variation could ensure consistent formatting of the date value,
if the value were used for something other than insert\storage in the
database::

VALUES char( date( :CinpDate ), ISO ) INTO :CoutDate :i

This variation could narrow output to effective off\on, such that a
null indicator is moot, but for which validation is any valid *past* date:

VALUES case when date( :CinpDate ) < current_date then 1
else 0
end INTO :r

Regards, Chuck

[TheBoss]

CRPence <CRP...@vnet.ibm.com> wrote in
news:jb3c84$k7c$1...@speranza.aioe.org:

No, it is just a clever way to validate that a certain variable is a
valid date (or time or datetime); boundaries are not relevant at all.
The SYSIBM.SYSDUMMY1 view isn't a real table/view, but a dummy one that
resides in memory and makes it possible to perform arithmetic using
special registers like "CURRENT_DATE" (and "CURRENT_TIME" /
"CURRENT_TIMESTAMP") in SQL.
In this case it is not even relevant which specific computation is done,
as long as it (implicitly) contains a comparison of the host-variable to
be validated with a variable/value known to be a correct date, time or
datetime. This comparison fails on a syntax-error (SQL-code "-181" in
DB2 for zOS) when the host-variable isn't a valid date (or time or
datetime). This means that the OP should catch the error (as said: -181
for DB2 zOS, not sure about LUW, but this should be easy to find out).

The reason for using SYSIBM.SYSDUMMY1 is that it is compatible between
platforms (at least for DB2 zOS vs. DB2 LUW, not sure about DB2/400).
I'm not so sure this is true for VALUES and the DAYS- and DATE-
functions.

Cheers!

--
Jeroen

[TheBoss]

Lennart Jonsson <erik.lenna...@gmail.com> wrote in news:jb37bc$sga$1
@dont-email.me:

Yes that's correct.
The INTO-clause is only neccessary when using embedded SQL, which is the
case here.

Cheers!

--
Jeroen

[CRPence]

On 29-Nov-2011 15:33 , TheBoss wrote:

> CRPence<CRP...@vnet.ibm.com> wrote:
>
>> On 29-Nov-2011 09:08 , Graham Hobbs wrote:
>>
>> A given date less than current date... Really? I know the example
>> was taken almost directly from a prior offered link, but that is
>> some very specific logic included in an apparent attempt merely to
>> "validate" a[n unspecified bounds] date string.
>>
>
> No, it is just a clever way to validate that a certain variable is a
> valid date (or time or datetime); boundaries are not relevant at
> all.

I understand the concept. The chosen statement however, implies
something different to anyone who might review\read the coded SQL; even
with a visibly coded response to any SQL errors that might follow the
EXEC SQL. That was my point.... per "Really?", as in, why use something
so confusing?

> The SYSIBM.SYSDUMMY1 view isn't a real table/view, but a dummy
> one that resides in memory and makes it possible to perform
> arithmetic using special registers like "CURRENT_DATE" (and
> "CURRENT_TIME" / "CURRENT_TIMESTAMP") in SQL.

The DB2 for i provides and uses the actual TABLE, as does DB2 for z
from what I recall. Though given that description, I infer that perhaps
some DB2 might just rewrite such SELECT INTO statements as a VALUES INTO
instead, which could conceptually be described in the same way.?
Interesting, but I have never seen any documentation to support that
effect. The DB2 for i I know [and AFaIK still] performs the actual
query of the TABLE when\as requested, but no longer uses a dummy table
[or view] to implement the VALUES INTO statement several years and
releases earlier.

> In this case it is not even relevant which specific computation is
> done, as long as it (implicitly) contains a comparison of the
> host-variable to be validated with a variable/value known to be a
> correct date, time or datetime.

Yep. That is why the DATE() [cast] scalar of a 10-byte character
string could do the same. And without any comparison to, nor any reason
to evaluate, the CURRENT DATE special register.

> This comparison fails on a syntax-error (SQL-code "-181" in DB2 for
> zOS) when the host-variable isn't a valid date (or time or datetime).
> This means that the OP should catch the error (as said: -181 for DB2
> zOS, not sure about LUW, but this should be easy to find out).

Just seemed to me that anyone asking such novice questions about the
SQL would not be so clear about both ignoring the result [in the host
variable] and deferring to the SQLstate because SQLSTATE values would be
consistent due to standards; noting the reference above, to SQLcode
instead.?

This is also where using the comparison is ugly IMO, since a valid
date can effect one of two results for SQLcode\SQLstate, either non-zero
or zero. A valid date can effect either an SQLcode=100 [for a valid
date of today or future] or the SQLcode=0 [for any valid past date]
along with the selected integer value updated\into the host variable. I
would expect that someone asking such basic questions about the SQL
might just add a [presumably wrong\undesirable] test like "SQLcode<>0"
after the SELECT INTO. Similarly, the SQLstate.

Even if using SELECT versus VALUES INTO, dropping the WHERE clause
entirely would IMO be much prettier for a general validation. Using a
statement like either of SELECT DATE(:CharDate) INTO or SELECT
DAYS(:CharDate) INTO, each eliminates the confusing predicate and gives
either a valid result with a zero sqlcode\sqlstate or an error; plus,
AFaIK, an indicator variable could be used instead\additionally to test
validity, something which is not a consistent option for the "SELECT 1"
for the same variations on effects for the :HV.

>
>> The first error suggests the dummy table usage is unnecessary;

>> i.e. the VALUES INTO can be used instead. <<SNIP>>

>
> The reason for using SYSIBM.SYSDUMMY1 is that it is compatible
> between platforms (at least for DB2 zOS vs. DB2 LUW, not sure about
> DB2/400). I'm not so sure this is true for VALUES and the DAYS- and
> DATE- functions.
>

The VALUES INTO statement and those scalars exist for every member of
the DB2 family according to any [even several years old] documentation I
have seen.

Regards, Chuck

[TheBoss]

CRPence <CRP...@vnet.ibm.com> wrote in news:jb5vpr$rvu$1
@speranza.aioe.org:

> On 29-Nov-2011 15:33 , TheBoss wrote:
>> CRPence<CRP...@vnet.ibm.com> wrote:
>>
>>> On 29-Nov-2011 09:08 , Graham Hobbs wrote:
>>>
>>> A given date less than current date... Really? I know the example
>>> was taken almost directly from a prior offered link, but that is
>>> some very specific logic included in an apparent attempt merely to
>>> "validate" a[n unspecified bounds] date string.
>>>
>>
>> No, it is just a clever way to validate that a certain variable is a
>> valid date (or time or datetime); boundaries are not relevant at
>> all.
>
> I understand the concept. The chosen statement however, implies
> something different to anyone who might review\read the coded SQL;
> even with a visibly coded response to any SQL errors that might follow
> the EXEC SQL. That was my point.... per "Really?", as in, why use
> something so confusing?

I started working with DB2 over 20 years ago on the mainframe, and even
before that I worked with Cobol and CICS (and DL/1), also on the mainframe.
Since I started working with DB2 LUW, about 8 years now, I haven't seen
Cobol nor CICS anymore, so I assumed the OP was asking about DB2 for zOS.
In that environment, using SYSIBM.SYSDUMMY1 is basic stuff, which already
was introduced in one of the first releases (2.2 or 2.3 I guess).

>
>> The SYSIBM.SYSDUMMY1 view isn't a real table/view, but a dummy
>> one that resides in memory and makes it possible to perform
>> arithmetic using special registers like "CURRENT_DATE" (and
>> "CURRENT_TIME" / "CURRENT_TIMESTAMP") in SQL.
>
> The DB2 for i provides and uses the actual TABLE, as does DB2 for z

> from what I recall./

Nope, there is no 'physical' SYSDUMMY1 table, you can't drop.create it and
there are no VSAM-files defined for it; it's just virtual in memory.

> /Though given that description, I infer that perhaps

> some DB2 might just rewrite such SELECT INTO statements as a VALUES INTO
> instead, which could conceptually be described in the same way.?
> Interesting, but I have never seen any documentation to support that
> effect. The DB2 for i I know [and AFaIK still] performs the actual
> query of the TABLE when\as requested, but no longer uses a dummy table
> [or view] to implement the VALUES INTO statement several years and
> releases earlier.

Not so for zOS, I'm afraid, see below.

While in general i agree with your sentiment, here's some reasons for my
doubt on using the VALUES solution in a z/OS environment:

1.
<q>
Hello Serge

I've checked that DB2 OS/390 8.1.5 accepts WITH clause but doesn't accept
VALUES clause in the context I need.
</q>
http://bytes.com/topic/db2/answers/822174-rewrite-luw-query-work-z-os

2.
Quoting well-known DB2 consultant Robert Catterall:
<q>
DB2 for z/OS does have a VALUES statement, but on that platform it's a
means
of invoking a UDF from a trigger (and VALUES in a DB2 for z/OS environment
can only be used in the triggered action of a trigger). For DB2 for LUW,
VALUES is a form of query.
</q>
Acknowledged by Peter Vanroose:
<q>
Unfortunately, indeed, with DB2 for z/OS (and still in version 9!) you need
the nonstandard
SELECT expr FROM SYSIBM.SYSDUMMY1
to mimic a one-row VALUES statement.
According to the SQL Reference for DB2 9 (at p. 1521), the VALUES statement
is only available as the body of a trigger.
</q>
http://www.idug.org/p/fo/et/thread=34281

3.
Quoting Tonkuma, who posted this only 3 days ago:
<q>
Multi row constructor "VALUES (...) , (...) , ..." is not supported on DB2
for z/OS.
So, please try to replace VALUES clause with "SELECT ... sysibm.sysdummy1
UNION ALL ...".
</q>
http://www.dbforums.com/db2/1672146-db2-ver-9-1-z-os-predicate-multiple-
columns.html

On the other hand, all this is now moot, since the OP has stated that he is
not using DB2 zOS but DB2 LUW...

Cheers!

--
Jeroen

[CRPence]

On 30-Nov-2011 14:15 , TheBoss wrote:
> here's some reasons for my doubt on using the VALUES solution in a
> z/OS environment:

What is sometimes referred to as the VALUES statement [what is a
variant of the VALUES clause of the INSERT INTO statement] is not the
same as what is called the VALUES INTO statement. The former is a means
to effect generation of row values [and columns, as an effective
temporary table], whereas the latter is the embedded SQL equivalent to
the SQL SET statement where each is used to assign an expression to a
variable.

I infer from the referenced link that the older variant of DB2 for z
[V8] being discussed, is missing the ability to use the VALUES() to
generate row values.

I have read docs for DB2/z which mention the "VALUES INTO statement".
I can not verify presently nor give any links, because I can find only
dead links from IBM for a SQL reference and poorly designed or poorly
functioning scripts which do not complete or take a very long time to
complete in my attempts to view any z-related docs on the web.

Addendum... I was persistent, having delayed posting the above, and I
since found the DB/z v8 statement syntax diagram showing an expression
can be evaluated into a host variable using the VALUES INTO statement:

_UDB for z/OS Version 8_
DrillDown: DB2 UDB for z/OS Version 8->DB2 reference information->DB2
SQL->Statements

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/rvali.htm
"
*VALUES INTO*

The VALUES INTO statement assigns one or more values to host variables.
Invocation

This statement can only be embedded in an application program. It is an
executable statement that cannot be dynamically prepared.
"

Regards, Chuck

[Graham Hobbs]

------
Folks,
Thankyou for the help.
From the CICS screen where users can change any field, I have edit
paragraphs. If ALL the edits are valid then
EXEC SQL UPDATE DTTS SET
ADATE
= :ADATE
,ATIME
= :ATIME
.. missed a couple of fields
,TOTAL_CARS
= :TOTAL-CARS
,TCHA
= :TCHA
WHERE CURRENT OF DTTS_U1_KYA
END-EXEC
else
return error msg about the highest error on the screen to the user.

Because my pgm will run on different platforms and different DB2's and
given what I am hearing/reading about 'differences', I've concluded
that it is better to change the logic of the pgm so that date, time
and timestamp -180 etc tests get done as a consequence of the above
SQL while all the other edits will precede it.

Is fractionally untidy in that edits are now in two areas of the pgm.
Unforutnately depending on where fields are on screen, if ADATE and
TCHA are in error, if ADATE is higher on screen, TCHA will be the
error msg the user receives (screen only has 1 msg line) - in other
words the user will not get the highest error msg first. Hope I'm
making sense here!

Again thanks for the help, I learned much.
Graham
.. after I penned and before posting the above, I see more posts ..
you guys are so way above my league:-)

[Graham Hobbs]

On Wed, 30 Nov 2011 22:54:44 -0500, Graham Hobbs <gho...@cdpwise.net>
wrote:

---
Reply to me ..
Well - choke on that idea:-(.
Waiting for the -180 thru -187 from the above UPDATE is fine except
when there are two or more of these DTTS (date, time, timestamp)
columns - how can I tell which one or more has gone wrong .. so ..
back to what has been suggested before. Hope I can make it work.
.. suppose I could use separate UPDATE SQL's.

[CRPence]

On 01-Dec-2011 19:41 , Graham Hobbs wrote:

>> pgm. Unfortunately depending on where fields are on screen, if

>> ADATE and TCHA are in error, if ADATE is higher on screen, TCHA
>> will be the error msg the user receives (screen only has 1 msg
>> line) - in other words the user will not get the highest error msg
>> first. Hope I'm making sense here!
>>
>

> Well - choke on that idea :-(

> Waiting for the -180 thru -187 from the above UPDATE is fine except
> when there are two or more of these DTTS (date, time, timestamp)
> columns - how can I tell which one or more has gone wrong

GET DIAGNOSTICS might help. And effectively forces use of SQLSTATE
instead of the less desirable SQLCODE, which should probably be done
anyhow.?

> so .. back to what has been suggested before. Hope I can make it
> work. ..

Other than validation being performed separately, then performed
again [but presumably never failing] in the UPDATE, the approach both
seems reasonable and should be available\functional as a SELECT INTO or
VALUES INTO.

> suppose I could use separate UPDATE SQL's.

The UPDATE WHERE CURRENT OF could be repeated for each SET; i.e.
against each column in whatever order is desired.? With each UPDATE the
code would know which one column gave rise to a data error.



One alternative that, like the original idea allows moving the
validation to where desirable, might involve something like... The
validation of multiple values could be moved into SQL stored procedure
routine(s) where an OUT [or INOUT] parameter could identify the first
in-error, of several ordered IN parameters that are to be validated:
exec sql call tst_parms(:whichInParmIsBad, :adate, :atime, ...)

As probably less desirable alternatives, the validation might be able
to be moved into CHECK CONSTRAINTS which are added with names that
identify the column. Or moved into an UPDATE TRIGGER routine which
provides custom diagnostics. Either or both could protect the TABLE
from activity outside the application. Or TRIGGER routine(s)
established on an identical twin\sibling TABLE which is used only for a
means to perform validation, could disallow all I\O activity and
identify specific column value errors with custom diagnostics, without
any impact to I\O on the original TABLE.

Regards, Chuck

[Graham Hobbs]

On Thu, 01 Dec 2011 21:43:17 -0800, CRPence <CRP...@vnet.ibm.com>
wrote:

---
Chuck,
More to think about now:-)!
Isn't the individual UPDATE method inefficient?
That said I would not be expecting two many programs having reams of
DTTS columns if ANY. Reason I say this is I expect to receive DCLGEN's
from folk and generate pgms therefrom. Is important for me to have
'things right'. Am consoled that DTTS are likely low usage columns and
even if present are likely not required to be updatable - gotta be
prepared though.
Will think more of your other ideas although starting to look too
complex .. if I send pgms to people simplicity has to be the norm.
Again, though, many thanks,
Graham

[CRPence]

On 02-Dec-2011 19:33 , Graham Hobbs wrote:
> Isn't the individual UPDATE method inefficient?

Yes, multiple UPDATE Table SET Column=:HV WHERE CURRENT OF versus
just one UPDATE with multiple SET requests surely is inefficient. But
inefficient relative to what instead, and as compared to the ease of
implementing in some other fashion, are both valid considerations.

That was mentioned only as an affirmative response to the comment
supposing "I could use separate UPDATE SQL" statements\requests; i.e.
not necessarily a recommendation.

> That said I would not be expecting too many programs having reams

> of DTTS columns if ANY. Reason I say this is I expect to receive
> DCLGEN's from folk and generate pgms therefrom. Is important for me
> to have 'things right'. Am consoled that DTTS are likely low usage
> columns and even if present are likely not required to be updatable
> - gotta be prepared though.

I have never so tightly coupled a database operation to interaction
with a screen [none that I can recall anyhow], nor have I used multiple
UPDATE versus just one against a FETCHed row; I can not be sure how much
of an impact the multiple UPDATEs would be, on any of the DB2 variants,
other than to know the row lock would be held longer and logging
presumably 'larger'.

IMO just doing one of the VALUES INTO or SELECT INTO wherever
validation is already being done on the other non-DTTS inputs, is
probably the best implementation; my preference being the former.
Recall that in response to "back to what has been suggested before. Hope
I can make it work" I responded with that "approach both seems

reasonable and should be available\functional as a SELECT INTO or VALUES

INTO." Though I alluded that the WHERE clause could be eliminated, and
that testing the SQLSTATE is [according to DB2 documentation] more
appropriate than testing the SQLCODE.

> Will think more of your other ideas although starting to look too
> complex .. if I send pgms to people simplicity has to be the norm.

If there were specific requirements [none have been stated AFaIK]
about the Date\Time\Timestamp formats [e.g. ISO-only, thus disregarding
any localization preferences for internationalization of the app] on
inputs, then there are very efficient and simple algorithms to validate
the character strings as dates [without the SQL]. The actual elements
of DTTS are very specific and limited; e.g. the element of year is 1 to
9999, of month 1 to 12, and of day 1 to 31 for some months or 1 to 30
for some other months or 1 to 28 for one month depending on the year in
which 29 might be allowed:
https://groups.google.com/d/msg/it.comp.as400/jhGBbtSspWI/W6Uem90pLjcJ

I did not look at the .cbl sources TheBoss made reference-to in an
earlier reply, but I would expect they should be so simple, if there was
a known [limited set of] formatting for the inputs.? Recomposed as an
ISO date string after validation, would ensure no error on the SET for
the UPDATE.

Regards, Chuck

[Graham Hobbs]

On Sat, 03 Dec 2011 11:36:23 -0800, CRPence <CRP...@vnet.ibm.com>
wrote:

------
Here's my take (latest, that is:-) ..
1. I agree - keep all the edits together.
2. Will experiment with the VALUES and/or SELECT INTO, see if I can
make it/them work - will research SQLSTATE.
3. One problem I've just hit is that much of the chat on this topic
has been lost because my NewsReader doesn't keep them long enough - is
OK have made some notes.
4. Next - your composite statement about the 'digit ranges' involved
make it all seem easier to do in COBOL where I am comfortable. I
looked at a couple of the COBOL refs and they were decent.
5. Is fairly apparent that 2. is the favourite - will let you know how
I make out.
I followed your ref (thru the Spanish too!), made for some deep
reading.
cheers,
Graham
..btw
if I have trouble with my 'sql code' you should know I'n not shy ro
ask:;))

[CRPence]

On 03-Dec-2011 16:13 , Graham Hobbs wrote:
> 3. One problem I've just hit is that much of the chat on this topic
> has been lost because my NewsReader doesn't keep them long enough
> - is OK have made some notes.

That might be the UseNet NewsServer provided by your ISP.?
Presumably the NewsReader could be configured both to download all of
the messages for offline access, and to maintain the local copy of those
messages irrespective of any unnecessarily rapid expiration of messages
occurring at the server; capability found under some retention and\or
storage settings for the account\server.? Regardless, ...

Google also can help with that; for a backup, or as an alternative
means to access the discussions\forum:
https://groups.google.com/d/topic/comp.databases.ibm-db2/DK-XapHKP8U/discussion
After visiting, the link shown in "recently viewed" can be
dragged\dropped into the Favorites.

And there is a [one of a few] _free_ UseNet NNTP news providers
news.aioe.org where all of the messages on this NewsGroup from this
thread are still unexpired. Add that server as an account in your
NewsReader, issue a Subscribe request to download the list of groups,
and then add this group. Note that free providers will have a much more
limited selection of UseNet groups from which to choose, and other
restrictions [number of posts, or free to read but pay to post, number
of connections, etc.] may apply.

But as someone on this group recently bemoaned [that was the
impression I had from their post], that NNTP is [and IMO, sadly] going
the way of the Dodo bird. For some reason people actually like\prefer
web pages instead of client-based interfaces to communicate. While
there are various advantages, as I commented in response to a warning
message about the demise of the server in a hosted DB2-related group,
i.e. the NewsServer news.software.ibm.com, [paraphrased] "I'd rather
drink gasoline than switch from a NNTP client NewsReader access to
web\browser-based access of public discussions\forums." That holds, at
least until some available browser-based access has the look, feel, and
performance matching Thunderbird [or similar] client non-browser-based
software.

> I followed your ref (thru the Spanish too!), made for some deep
> reading.

Italian actually :-) Not to imply I know Italian, because I do not.
The column names and date-format-strings are better understood, knowing
that Anno=Year, Giorno=Day, and Mese=Month. Sorry... I forgot to
mention that, plus, that much of the text prior to the example SELECT
query was at least somewhat specific to the DB2 for i embedded SQL. The
logic in the WHERE clause was really all that was relevant; i.e. as an
example of the algorithm to validate the digit elements of a date string.

Regards, Chuck