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.
midrange.com
