On 21-Oct-2011 14:56 , Raul A. Jager W. wrote:
The date field is defined like this: <<trimmed in quoted reply>>
Tipo Long
Campo Datos Campo
FSOLP DATE 8
Formato de Fecha . . . . . . . . . . . . : *DMY
Separador de fecha . . . . . . . . . . . : '/'
Using DDS I can do it using "DATFMT(*DMY)", I just tried, and it did
work even after reading in the manual a note that says this format
is not valid for physical files.
Not sure what manual might have suggested that, but obviously such "a
note" would be in conflict even with the subject "DATFMT (Date Format)
Keyword for Physical and Logical Files", plus the documentation under
that subject heading in the DDS Reference. And, contradict both my
experience and all Infocenter documentation I have seen. The DATFMT is
documented as one of the valid keywords in the Physical file and Logical
file DDS [with some additional supported LF date format options since
v4r4], for sure as far back as v3r2:
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/QBKAUI02/2.4.13?DT=19960328091327
In SQL I could not get this format, I wonder if it is a good idea to
rebuild the file using something that may be outdated, or it is a
better option to change and recompile everything.
If the file is referenced by non-SQL whereby the Record Format Level
Identifier is relevant for implementing Level Check [LVLCHK], then the
physical file can be recreated as a SQL TABLE with *ISO as the default
[and the only allowed] date formatting specification, *if* the original
PF would also be replaced by an LF. That might sound confusing, but
hopefully the following text and example can clarify...
Consider the following example as a means to replace a physical with
a DDS logical file for non-SQL access. A DDS physical file with [for
this example] the one date data type field named L defined with both of
the DATFMT(*DMY) DATSEP('/') DDS Functions represents the DDS PF to be
"replaced" by both an SQL TABLE and a DDS LF. The following sample
batch CL stream source code would be submitted using the SBMDBJOB
command, after adjusting the JOBD() parameter value to name a job
description object which has a valid explicitly named user profile
instead of the special value USER(*RQD):
<code>
//BCHJOB dmy_tst msgq(*usrprf) jobd(user_named) jobq(*jobd) endsev(41)
chgjob job(*) splfacn(*keep)
dltf dmyold
dltf dmy
crtpf dmy srcfile(ddspf) /* original DDS PF */
runsqlstm srcfile(sqlpf) srcmbr(iso) commit(*none) errlvl(30)
rnmobj dmy *file dmyold /* rename old PF to crtlf by same name */
crtlf dmy srcfile(ddslf) /* original PF replaced by a DDS LF */
dspffd iso /* new TABLE replacing DDS PF, but a new name */
dspffd dmyold /* original DDS PF, previously renamed */
dspffd dmy /* new LF replacing DDS PF */
//data file(ddspf) filetype(*src) endchar('//')
R LDMY
L L DATFMT(*DMY) DATSEP('/')
//
//data file(sqlpf) filetype(*src) endchar('//')
drop table ldmy /* remove DROP if RcdFmt clause enabled */
;
drop table iso
;
create table ldmy /* create TABLE ISO if RcdFmt clause enabled */
( L DATE not null with default
) /* rcdfmt ldmy */
;
rename table ldmy to system name iso
/* remove RENAME statement if RcdFmt clause enabled */
;
//
//data file(ddslf) filetype(*src) endchar('//')
R LDMY PFILE(ISO)
L L DATFMT(*DMY) DATSEP('/')
//
//ENDBCHJOB
! Expected output:
! SQL TABLE ISO RcdFmt LvlId:22C7758521AE4 RcdLen:10 DatFmt(*ISO)
! DDS PF DMYOLD RcdFmt LvlId:240E9CB5018F7 RcdLen:08 DatFmt(*DMY)
! DDS LF DMY RcdFmt LvlId:240E9CB5018F7 RcdLen:08 DatFmt(*DMY)
</code>
The new SQL TABLE can not replace the old DDS PF for Record Level
Access [and other non-SQL access] because the RcdFmt LvlId stored in the
compiled objects will cause a level check; i.e. without use of the
discouraged technique of LVLCHK(*NO).
Because the new DDS LF has a matching Record Format Level Identifier
to the old DDS PF, that logical file can replace the original PF for all
non-SQL accesses. After backup and copying the data from the DDS PF
DMYOLD to the SQL TABLE ISO, the old file can be deleted. Coded and ad
hoc SQL requests would be directed to the new SQL TABLE. And new
non-SQL code could access either the new LF that replaced the old PF [of
the same name; just as old code would] or the new SQL TABLE [having the
new name].
Regards, Chuck
midrange.com
