Define schema to load file source with additional derived columns

Tools & Utilities
Enthusiast

Define schema to load file source with additional derived columns

I am using TPT 14.10.05 on AIX.  I want to read a file and add some fields to

the row from job variables before loading to a table.

I am able to do this by creating the table within the TPT script and setting the

column defaults with the job variables,

but I can't add the derived columns in the producer output.

The TPT manuals say derived fields can be added to the SELECT statement of a

file read producer.  However, I can't seem to define/infer correct producer schema for reading the

file (without derived field) and then a different consumer schema (with derived field) for the table.

Below is a summary of the main objects (the site only allows uploads of images):

CREATE MULTISET TABLE DEV4_TEMP.W_TEST_LOADFILE,

 NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO (

FILE_INSTANCE_ID INTEGER DEFAULT NULL,

RECORD_TYPE VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT NULL)

NO PRIMARY INDEX;

-- File contains one VARCHAR column that matches table definition.

REPLACE VIEW DEV4_TEMP.W_TEST_READFILE AS

 LOCKING ROW FOR ACCESS SELECT

RECORD_TYPE

from DEV4_TEMP.W_TEST_LOADFILE;

-- Want to insert the column from the file and an additional column from a variable.

REPLACE VIEW DEV4_TEMP.W_TEST_INSERT AS

 LOCKING ROW FOR ACCESS SELECT

FILE_INSTANCE_ID,RECORD_TYPE

from DEV4_TEMP.W_TEST_LOADFILE;

STEP LOAD_WT2

(

  DEFINE SCHEMA LOAD_SCHEMA FROM TABLE DELIMITED @LoadView;

  APPLY @WorkTableInsert2 TO OPERATOR ($INSERTER())

 ,APPLY TO OPERATOR ($SCHEMAP ATTR( PrivateLogName = 'imp_SCHM', DumpFieldsWithTrans='Y', AllRecords='Yes', RecordCount=10))

  SELECT @FileInstance as FILE_INSTANCE_ID,RECORD_TYPE FROM OPERATOR($FILE_READER()) WHERE RECORD_TYPE=@RecordType;

);

--VARIABLES FILE

/* GLOBALS */

FileReaderFormat = 'Delimited'

,OpenMode = 'Read'

,FileReaderIndicatorMode='N'

,FileReaderTrimColumns='None'

,FileReaderAcceptExcessColumns='N'

,FileReaderAcceptMissingColumns='N'

,FileReaderTruncateColumnData='N'

,EnableScan='N'

,FileReaderRecordErrorFileName='./BadRecs_'||@FileName

,FileReaderRecordErrorVerbosity='High'

,FileReaderPrivateLogName = 'imp_FR'

,InserterPrivateLogName = 'imp_INS'

,DDLPrivateLogName = 'imp_DDL'

,FileReaderSkipRowsEveryFile='Y'

,FileReaderNullColumns='Y'

,SourceFormat='Delimited'

,RecordErrorFilePrefix='BadRecs_'

/* BATCH SPECIFIC */

,FileInstance='1'

,RecordType='Invoice'

,FileReaderSkipRows=0

,FileReaderTextDelimiter='TAB'

,TargetWorkingDatabase = 'DEV4_TEMP'

,TargetTable = 'W_TEST_LOADFILE'

,WorkTableLoad = @TargetWorkingDatabase||'.'||@TargetTable

,FileView = @TargetWorkingDatabase||'.W_TEST_READFILE'

,LoadView = @TargetWorkingDatabase||'.W_TEST_INSERT'

,WorkTableInsert2='INSERT INTO '||@LoadView||' (FILE_INSTANCE_ID, RECORD_TYPE) VALUES (:FILE_INSTANCE_ID, :RECORD_TYPE);'

,WorkTableCreate='CREATE MULTISET TABLE '||@WorkTableLoad||',

 NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO (

FILE_INSTANCE_ID INTEGER DEFAULT '||@FileInstance||',

RECORD_TYPE VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT '''||@RecordType||'''

)NO PRIMARY INDEX;'