× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



On 22 May 2013 08:59, dale janus wrote:
In your DDL did you tell it a DEFAULT of current timestamp or some
such thing?
In your RPG did you use an index or view which omitted that column?
You have to do both if you're sticking with RLA.

So what I am understanding is that I can use these great DDL tools,
but if I don't use SQL, they don't work.

Not a case of "does not work", but a case of not doing what happens to be what is desired of some programmer in a specific situation.

The timestamp is defined as DEFAULT current. I am not using a view
or index, so the field is included. Therefore it defaults to 0001 01
01 etc.

I am very disappointed that I cannot use this new tools that we are
encouraged to use without taking extra steps of a view or filling
the field in RPG.

Yet if DDS had been used instead of DDL, and a default value had been established using DFT(value), the same issue occurs whereby the WRITE does not effect the default-value. Would that same disappointment be expressed in that scenario with the "old tools"? My point... the issue is with the RPG, not with the Database [file] or the SQL. See the following KB article; not sure what can be inferred from the last applicable release being listed as v5r4... I submitted feedback to inquire of any intended implication:
http://www-912.ibm.com/s_dir/SLKBase.nsf/1ac66549a21402188625680b0002037e/bd2cadb20504ce94862571fc0068f8ea?OpenDocument
"Document Number: 428709941
OS/400 Release: V5R1M0; V5R2M0; V5R3M0; V5R3M5; V5R4M0
_Document Title_
Does RPG Support the DFT Keyword in a PF?

_Document Description_
The keyword DFT can be used on the field in a PF. This keyword can be used by the DB2 database to determine what default value will be updated into a field at output time..

The HLL RPG/RPGLE does not use the same defaults that for [sic] defined by DB. It uses whatever is in the output buffer for that field/s when a output operation is done.

Example: The output operation WRITE. A PF has the keyword DFT defined on a date field. If doing a WRITE operation to the file, these fields are not used in the output operation. The High Level Language RPG will generate a default value for that field. In the case of a date field, the date format used will be 'DMY'. The information that is placed in the date field will be 01/01/40.
"

FWiW the transition to using SQL is best done by first changing to start using the non-DDL SQL statements to work with the data; i.e. using the SQL DML. Delay the change to using TABLE from using DDS PF until afterward, or do so only when necessary to resolve some problem; e.g. preventing bad decimal data being written to the file. If changing to TABLE for added function [that is not available in DDS], then the move to SQL for DML will effectively be a requirement; this is somewhat the case, for using CURRENT registers for default values.

I suppose the correct answer is don't use record level access, use
SQL.

Both the LF [which could be an SQL VIEW vs DDS LF] omitting the column or an OPNQRYF referencing a FORMAT() with that column omitted would enable the RLA to effect the current value being implicitly set by the DataBase.

Or an enhancement in RPG RLA that allows naming the fields which are being assigned a value, so the RPG can tell the DM\DB such that the DB can assign the default value for those fields not included in the WRITE.

The following may just describe such an enhancement.?:
http://archive.midrange.com/rpg400-l/200905/msg00104.html
"...
If you want to use the default value in RPG you have to use an externally described data structure with INZ(*EXTDFT)
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/rzasd/sc092508548.htm
...
"

I don't have enough time to learn everything, I must get this stuff
written by deadline. So I picked DDL and RDI as stuff to learn.

Thanks for helping me along my learning journey, another new thing
today.

Assuming the low-value for the TIMESTAMP is always undesirable on a HLL WRITE or INSERT, as a hardened rule, and the CURRENT TIMESTAMP value is desirable instead, then a very simple TRIGGER can be added to the TABLE very easily to ensure that effect:

create trigger the_file_BI /* <- trigger name; see below */
before insert
on the_file /* <- trigger name added _BI suffix to file name */
referencing new as N
for each row mode db2row
begin
if n.t = TIMESTAMP '0001-01-01 00:00:00.000000' then
set n.t = CURRENT TIMESTAMP ;
end if ;
end
;

Oddly the above trigger did not function as expected when tested... and I am having difficulties [due mostly to my user stg limit] in some test and debug, so I can not determine what is wrong presently. If I do not forget, I might followup with any change; the problem may be something specific to v5r3 vs an error in what I coded.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.