I would appreciate if some one can give the insight to this problem.
Problem statement: We have a variable record that we are exporting using FastExport. 1st 2 bytes are record length since the record is variable length. We are using TPT to solve the problem therefore we do not have to do SORT on mainframe or awk/cut process in unix.
Alternate Solution: Tried TPT with export and data connector consumer to write to a file in windows works perfectly there is no 1st 2bytes that is the length of the record and the EOR marker marks the end of record. Same script tried in mainframe with VB Variable block LRECL=100, the record length is not generated but EOR marker doesn't end the record after the data but continues until the LRECL length specified.
WINDOWS ( I would expect Unix to behave same too)
FRST_NM is VARCHAR(50) & LST_NM is VARCHAR(50)
SELECT FRST_NM || LST_NM FROM TABLEA
The 4 records above would show all in one line as below with . (. is a non display character hex 15) and this would continue until there are no more records or it has reached 96 bytes since the 1st 4 bytes is reserved for Variable block (LRECL 100 -4 = 96)
I would have expected to see the same in Mainframe that I see in windows. Any help is appreciated.
/* 1 */
/* 2 */
/* 3 */ DEFINE JOB CSV_PTY_UAT
/* 4 */ (
/* 5 */ DEFINE OPERATOR W_1_o_CSV_PTY_UAT
/* 6 */ TYPE DATACONNECTOR CONSUMER
/* 7 */ SCHEMA *
/* 8 */ ATTRIBUTES
/* 9 */ (
/* 10 */ VARCHAR FileName,
/* 11 */ VARCHAR Format,
/* 12 */ VARCHAR OpenMode,
/* 13 */ INTEGER BlockSize,
/* 14 */ INTEGER BufferSize,
/* 15 */ INTEGER RetentionPeriod,
/* 16 */ INTEGER RowsPerInstance,
/* 17 */ INTEGER SecondarySpace,
/* 18 */ INTEGER UnitCount,
/* 19 */ INTEGER VigilElapsedTime,
/* 20 */ INTEGER VigilWaitTime,
/* 21 */ INTEGER VolumeCount,
/* 22 */ VARCHAR AccessModuleName,
/* 23 */ VARCHAR AccessModuleInitStr,
/* 24 */ VARCHAR DirectoryPath,
/* 25 */ VARCHAR ExpirationDate,
/* 26 */ VARCHAR IndicatorMode,
/* 27 */ VARCHAR PrimarySpace,
/* 28 */ VARCHAR PrivateLogName,
/* 29 */ VARCHAR RecordFormat,
/* 30 */ VARCHAR RecordLength,
/* 31 */ VARCHAR SpaceUnit,
/* 32 */ VARCHAR TextDelimiter,
/* 33 */ VARCHAR VigilNoticeFileName,
/* 34 */ VARCHAR VigilStartTime,
/* 35 */ VARCHAR VigilStopTime,
/* 36 */ VARCHAR VolSerNumber,
/* 37 */ VARCHAR UnitType
/* 38 */ );
/* 39 */
/* 40 */ DEFINE SCHEMA W_0_s_CSV_PTY_UAT
/* 41 */ (
/* 42 */ NM VARCHAR(100)
/* 43 */
/* 44 */ );
/* 45 */
/* 46 */ DEFINE OPERATOR W_0_o_CSV_PTY_UAT
/* 47 */ TYPE EXPORT
/* 48 */ SCHEMA W_0_s_CSV_PTY_UAT
/* 49 */ ATTRIBUTES
/* 50 */ (
/* 51 */ VARCHAR UserName,
/* 52 */ VARCHAR UserPassword,
/* 53 */ VARCHAR SelectStmt,
/* 54 */ INTEGER BlockSize,
/* 55 */ INTEGER MaxSessions = 4,
/* 56 */ INTEGER MinSessions,
/* 57 */ INTEGER TenacityHours,
/* 58 */ INTEGER TenacitySleep,
/* 59 */ INTEGER MaxDecimalDigits,
/* 60 */ VARCHAR AccountID,
/* 61 */ VARCHAR DateForm,
/* 62 */ VARCHAR NotifyExit,
/* 63 */ VARCHAR NotifyExitIsDLL,
/* 64 */ VARCHAR NotifyLevel,
/* 65 */ VARCHAR NotifyMethod,
/* 66 */ VARCHAR NotifyString,
/* 67 */ VARCHAR PrivateLogName,
/* 68 */ VARCHAR TdpId,
/* 69 */ VARCHAR TraceLevel,
/* 70 */ VARCHAR WorkingDatabase
/* 71 */ );
/* 72 */
/* 73 */ APPLY
/* 74 */ TO OPERATOR
/* 75 */ (
/* 76 */ W_1_o_CSV_PTY_UAT
/* 77 */
/* 78 */ ATTRIBUTES
/* 79 */ (
/* 80 */ FileName = 'C:\temp\csv_xxx_uat.txt',
/* 81 */ Format = 'DELIMITED',
/* 82 */ OpenMode = 'Write',
/* 83 */ IndicatorMode = 'N',
/* 84 */ TextDelimiter = '|'
/* 85 */ )
/* 86 */ )
/* 87 */ SELECT * FROM OPERATOR
/* 88 */ (
/* 89 */ W_0_o_CSV_PTY_UAT
/* 90 */
/* 91 */ ATTRIBUTES
/* 92 */ (
/* 93 */ UserName = 'xxx',
/* 94 */ UserPassword = 'xxx',
/* 95 */ SelectStmt = 'SELECT FRST_NM || LST_NM FROM TABLEA;',
/* 96 */ TdpId = 'Tdp'
/* 97 */ )
/* 98 */ );
/* 99 */ );
A couple of things.
1. always provide the version of TPT you are using; we cannot offer any assistance with that information
2. FastExport will always write out a 2-byte length in front of all records (especially on non-mainframe platforms; I am not 100% certain whether this is true on mainframe) because FastExport can only retrieve data in one of the supported binary formats
3. FastExport cannot retrieve data in "delimited" (VARTEXT) mode.
4. TPT can write out exported data in "delimited" format but not with the Export operator unless the user goes through a lot of hurdles to CAST their SELECT statement so that all data is converted (by Teradata) to character format. The delimited data is wrtitten by the DataConnector operator and in "delimited" format there would be no 2-byte row length.
5. The normal (and easiest, but not necessarily the fastest) method of retrieving data into "delimited" format is to use the SQL Selector operator with "ReportMode" enabled. Again, the DC operator would actually write the delimited data.
If I can get a little more information (TPT version name, possibly even the DataConnector output for the job), that would be helpful.
However, from your script, you will not get what you expect. You cannot use the Export operator in the way you expect.
Thanks for your reply. I ran the same script in mainframe as well as windows, below are the versions
Teradata Parallel Transporter Version 13.10.00.04
Teradata Parallel Transporter DataConnector Version 13.10.00.04
Teradata Parallel Transporter Export Operator Version 13.10.00.03
On Windows : This is what came with Teradata trial version for windows.
Teradata Parallel Transporter Version 13.00.00.02
Teradata Parallel Transporter DataConnector Version 13.00.00.02
Teradata Parallel Transporter Export Operator Version 13.00.00.00
Point # 4 is what the above script does. It works well with windows. From TPT standpoint , there shouldn't be a difference as to how the data is written to a file because the script is same except ASCII & EBSIDIC due to platform difference.
Point #5 won't work because records are in millions.
For #4, mainframe I/O is always different from Unix and Windows. Mainframe datasets are record oriented. Not so on Unix and Windows.
For #5, the number of records does not limit the use of the Selector operator. However, overall performance might.
Just know that if you use the Export operator, you will most likely not get what you expect or want.
"FastExport will always write out a 2-byte length in front of all records (especially on non-mainframe platforms; I am not 100% certain whether this is true on mainframe) because FastExport can only retrieve data in one of the supported binary formats"
Happened to stop by here and found the above. This actually depends how you process/treat the selected columns. if you trim - concat - trim and cast to char(not varchar), you CAN remove the length-info of each record.