On 07-Nov-2015 14:15 -0600, John R. Smith, Jr. wrote:
The IFS file is a fixed format file (except for the block data as
shown below) with no delimiters. The first 25 bytes of each record
are the same layout. Starting with byte 26 for a max of 2000 bytes
(most formats only using about 100 of the 2000 bytes) is the block
data.

<<SNIPped data samples showing Data Format (DTAFMT) of *FIXED
and that there are five effective Record Formats represented>>

Here is the layout of both the IFS file and the DB2 file except for
the block data field which, just like seen above, is variable length
depending on the type.

Store - 3
Register - 2
Transaction - 2
Timestamp - 14 (YYYYMMDDHHMMSS)
RecordType - 4
BlockData - 2000


Although the IFS is variable length block data, when stored in the
DB2, the block data field will be padded with blanks to fill the 2000
bytes.

So the requirement does not include further describing the BlockData as individual columns; that data can remain as the one column of long\undescribed data.?


I need to keep the "key" fields in the DB2 for sorting, grouping,
and easier research. Also, since this file will be millions of
records, I don't want to copy the IFS file to a single field DB2 file
and then copy it to another file that contains the keys that I need.

I hope that further answers the questions that have been asked by
everyone trying to help.


If the BlockData need only remain as one long blank-padded column, then the following is a command-line script to effect that:

<code>

crtsrcpf qtemp/src rcdlen(92) mbr(fdf)

ovrdbf stdout qtemp/src mbr(fdf) ovrscope(*calllvl)

qsh cmd('echo STORE 001 003 0 ;
echo REGISTER 004 005 0 ;
echo TRANSACTN 006 007 0 ;
echo TIMESTAMP 008 021 0 ;
echo RECORDTYP 022 025 0 ;
echo BLOCKDATA 026 2025 0 ;
echo *END')

dltovr stdout

runsql
'create table qtemp/imported
( Store char ( 3) not null with default
, Register char ( 2) not null with default
, "TRANSACTION" for TRANSACTN
char ( 2) not null with default
, "TIMESTAMP" char ( 14) not null with default
, RecordType for RECORDTYP
char ( 4) not null with default
, BlockData char ( 2000) not null with default
)' commit(*none) naming(*sys)

CPYFRMIMPF FROMSTMF( varFmtInput ) TOFILE(qtemp/imported)
DtaFmt(*FIXED) RmvBlank(*NONE) FldDfnFile(qtemp/src fdf)
rcddlm(*CRLF) mbropt(*replace)

runqry *n qtemp/imported /* just to review the effect */

</code>

If the BlockData need only remain as one long column, but ideally should be trimmed of the trailing blanks, then change the definition of BLOCKDATA in the above CREATE TABLE of the RUNSQL request to be VARCHAR [instead of CHAR], and then revise the import request to use RMVBLANK(*TRAILING) [instead of RmvBlank(*NONE)]

If the BlockData should be represented in different formats in either a Multi-Format Physical File (MFPF) [as effectively only supported by Query/400 for /query/ reporting] or each row of the distinct /formats/ stored in a separate SQL TABLE, or and if even possible that each row is stored in a DDS described PF over which a Multi-Format Logical File logically re-describes the physical data, then providing further input on the issue requires sharing of the DDL that would represents each of the five /formats/. The column of non-described can be _presented as_ though columns using a SQL query [optionally encapsulated in a VIEW or several, and more sophisticated than an LF], but the ability to update the data can be more complicated than with a DDS LF; if even the data must be updated vs referenced always\only for read-only.


This thread ...

Follow-Ups:
Replies:

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

This mailing list archive is Copyright 1997-2019 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].