Re: SQLRPGLE create table -- RPG400-L

In: http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzakb/rzakbmstlfkeyw.htm
I copy below part of it.
_____________________________________________________________________________________________________

DATFMT(date-format)

The date-format parameter specifies the format for the date. The following table describes the valid date formats and their default separator values for physical file fields.

Format name Date format parameter Date format and separator Field length Example
Job Default *JOB^1 Month/Day/Year *MDY^1 mm/dd/yy 8 06/21/90
Day/Month/Year *DMY^1 dd/mm/yy 8 21/06/90
Year/Month/Day *YMD^1 yy/mm/dd 8 90/06/21
Julian *JUL^1 yy/ddd 6 90/172

Notes:

1. These DATFMTs are not valid for the date (L) type field. They are
only valid on logical file zoned, packed, or character types
having a physical file based on date type fields.
2. DATFMTs that do not have any "days" specified are implied to be
day 1 of the specified month.

______________________________________________________________________________________________________________

I was very surprised when I tried and it did accept *dmy as valid for a physical file.

Your advise to create a new SQL table and bulid alogical using DDS is great, I plan to use it
Thank you Chuck.

CRPence wrote:

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