File-AID/RDX extract file
During the extract process, File-AID/RDX writes all extracted data to the extract file. This is the file that File-AID/RDX uses in the load process to load all or only selected data.
In order to increase data security the File-AID/RDX extract file no longer includes DDL statements in readable form. The Db2 source environment information is now captured in a masked format.
The extract file has the following characteristics:
File organization
Partitioned (DSORG=PO) or sequential (DSORG=PS)
Record format
Variable-blocked (RECFM=VB)
Logical record length
Maximum LRECL determined by File-AID/RDX (default = 27994)
The first four bytes at the beginning of each record indicate the record’s type and the corresponding header record.
After a successful extract is performed, the extract file contains the following types of records:
- Product record 0;
- Header record H;
- Db2 create records C;
- MVS file create records E;
- Map records M;
- Relationship records R;
- Data records D;
- Db2 XML/LOB data records x;
- Order records O;
- Trailer records T.
Product Record — 0
The product record indicates the release of File-AID/RDX used to create the file. This is always the first record in the file. It also includes the DBMS type and version, Db2 image copy, sort, and compress indicators (C = compress all data, V = compress only Db2 variable column data, or blank = no compression), date format, data status (E = Encrypted (Masked), D = Disguised), as well as the Autocreate disabled?, D record supplement indicators, Db2 extract format Unicode or Conversion, and extract IMPLICITLY HIDDEN columns indicator. The format of the product record is shown in the following figure.
Product Record Layout
05 RCD-TYPE PIC X(1) VALUE '0'.
05 RCD-TYPE-NO PIC X(3) VALUE '000'.
05 PROD-LIT PIC X(13) VALUE 'File-AID/RDX'.
05 VERSION PIC X(2) VALUE '04'.
05 PERIOD1 PIC X(1) VALUE '.'.
05 RELEASE PIC X(2) VALUE '07'.
05 PERIOD2 PIC X(1) VALUE '.'.
05 MOD PIC X(2) VALUE '00'.
05 PERIOD3 PIC X(1) VALUE '.'.
05 SUBRELEASE PIC X(2) VALUE '00'.
05 FILLER PIC X(1).
05 TIMESTAMP PIC X(26).
05 DEC_IND PIC X(1).
05 FILLER PIC X(1).
05 DBMS-TYPE PIC X(8).
05 DBMS-VERSION PIC X(8).
05 FILLER PIC X(1).
05 IMAGE-COPY PIC X(1) VALUE_’Y’_or’N’
05 SORT_IND PIC X(1) VALUE_’Y’_or’N’
05 COMPRESS-IND PIC X(1) VALUE_’C’ or’V’
05 DATE_FORMAT PIC X(4)_VALUE_’ISO’,‘USA’,’JIS’,’LOC’,’EUR’, or ‘ISOM’
05 DATA_STATUS PIC X(1) VALUE_’E’ or’D’
05 AUTOC_DISABL PIC X(1) VALUE_’Y’
05 D_REC_SUPPLM PIC X(1) VALUE_’Y’_or’N’
05 DB2_FORMAT PIC X(1) VALUE_’1’_or’2’
05 GET_HIDDEN PIC X(1) VALUE_’Y’_or’N’
Header Record — H
A header record will be present for each object included in the extract process. The format of the header record is shown in the following figure.
Header Record Layout
05 RCD-TYPE PIC X(1) VALUE 'H'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 SUBSYS PIC X(4).
05 OBJECT_TYP PIC X(1).
05 OBJECT_NAME PIC X(52).
10 LOCATION PIC X(16).
10 CREATOR PIC X(8).
10 TBNAME PIC X(18).
10 FILLER PIC X(10).
05 RI-DEP-IND PIC X(1).
05 FILLER PIC X(1).
05 BPOOL PIC X(9).
05 FILLER PIC X(2).
05 SUPPL_SW PIC X(1)
05 CCSID PIC X(5)
05 OBJECT_NAME_LONG PIC X(272).
10 LOCATION PIC X(16).
10 CREATOR PIC X(128).
10 TBNAME PIC X(128).
05 ORIG_OBJ_NAME PIC X(272).
10 LOCATION PIC X(16).
10 CREATOR PIC X(128).
10 TBNAME PIC X(128).
The following is an explanation of the fields contained in the header layout:
RCD-TYPE
A value of ’H’ will be present in this field. The value ’H’ indicates that this is a header record.
RCD-TYPE-NO
A three-digit alphanumeric ID that identifies the record in the file. This field is referenced by create records and data records that contain the same three-digit alphanumeric ID in the RCD-TYPE-NO field.
SUBSYS
The Db2 subsystem on which the table name identified in the CREATOR and TBNAME field exists. Blank for MVS objects.
OBJECT_TYPE
Identifies the object type: D = Db2, M = MVS.
OBJECT_NAME
Identifies the object name. If object type is M the full MVS file name is listed. If object type is D the location, creator and table names are listed instead:
LOCATION | The location at which the Db2 table name identified in the CREATOR and TBNAME fields exists. |
CREATOR | The authorization ID of the owner of the table in the TBNAME field (blank, if Db2 long object name). |
TBNAME | The name of the table to which this header record corresponds (blank, if Db2 long object name). |
RI-DEP-IND
A value of 'Y' indicates that the Db2 table identified by the CREATOR and TBNAME fields is a dependent in a Db2 RI relationship.
BPOOL
Buffer pool where creator.tbname resides. Only used when table contains either LVCHAR or LVGRPH columns.
SUPPL_SW
If the D record supplement indicator is set to Y, the D record also includes a record number that ensures that data extracted from an MVS file without an index but with an XREF, will be loaded to the target in the sequence in which they were read.
CCSID
Identifies the CCSID for an MVS object.
OBJECT_NAME_LONG
Identifies the object name; if object type is M the full MVS file name is listed; if object type is D the location, creator and table names are listed instead:
LOCATION | The location at which the Db2 table name identified in the CREATOR and TBNAME fields exists. |
CREATOR | The authorization ID of the owner of the table in the TBNAME field. |
TBNAME | The name of the table to which this header record corresponds. |
ORIG_OBJ_NAME
Identifies the original object name if the extract request has been cloned. If object type is M the full MVS file name is listed; if object type is D the location, creator and table names are listed instead:
LOCATION | The location at which the Db2 table name identified in the CREATOR and TBNAME fields exists. |
CREATOR | The authorization ID of the owner of the table in the TBNAME field. |
TBNAME | The name of the table to which this header record corresponds. |
Base Table Record — B
(Obsolete - only applies to extracts created with File-AID/RDX Release 4.5 or earlier.)
A base table record will be present for each Db2 view included in the extract process. Each base table record will share a record-type number with a header record. Together, the header and base table records provide information about the view and its base table. The format of the base table record is shown in the following figure.
Base Table Record Layout
05 RCD-TYPE PIC X(1) VALUE 'B'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 SUBSYS PIC X(4).
05 LOCATION PIC X(16).
05 CREATOR PIC X(8).
05 TBNAME PIC X(18).
05 RI-DEP-IND PIC X(1).
05 FILLER PIC X(1).
05 BPOOL PIC X(9).
The following is an explanation of the fields contained in the base table record layout:
RCD-TYPE
A value of ’B’ will be present in this field. The value ’B’ indicates that this is a base table record.
RCD-TYPE-NO
A three-digit number that identifies the record in the file. This field is referenced by create records and data records that contain the same three-digit number in the RCD-TYPE-NO field.
SUBSYS
The Db2 subsystem on which the table name identified in the CREATOR and TBNAME field exists.
LOCATION
The location at which the table name identified in the CREATOR and TBNAME fields exists.
CREATOR
The authorization ID of the owner of the table in the TBNAME field.
TBNAME
The name of the table to which this base table record corresponds.
RI-DEP-IND
A value of ’Y’ indicates that the table identified by the CREATOR and TBNAME fields is a dependent in a Db2 RI relationship.
BPOOL
Buffer pool where creator.tbname resides. Only used when table contains either LVCHAR or LVGRPH columns.
Database Create Record — F
(Obsolete - only applies to extracts created with File-AID/RDX Release 4.5 or earlier.)
The database create record is generated when your extract includes Db2 objects. It contains Db2 DDL statements related to the extracted Db2 objects. The format of the create record is shown in the following figure.
Database Create Record Layout
05 RCD-TYPE PIC X(1) VALUE 'F'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 CREATE-TEXT PIC X(76).
The following is an explanation of the fields contained in the database create record layout:
RCD-TYPE
A value of 'F' will be present in this field. The value 'F' indicates that this is a database create record.
RCD-TYPE-NO
A three-digit sequence number that keeps track of the number of databases in the extract. For example, a value of '002' indicates it is the second database in the extract.
CREATE-TEXT
Contains the Db2 DDL required to create or alter databases.
Tablespace Create Record — G
(Obsolete - only applies to extracts created with File-AID/RDX Release 4.5 or earlier.)
The tablespace create record is generated when your extract includes Db2 objects. It contains Db2 DDL statements captured during extract processing to create and alter tablespaces that File-AID/RDX uses to generate load JCL. The format of the create record is shown in the following figure.
Tablespace Create Record Layout
05 RCD-TYPE PIC X(1) VALUE 'G'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 CREATE-TEXT PIC X(76).
The following is an explanation of the fields contained in the tablespace create record layout:
RCD-TYPE
A value of 'G' will be present in this field. The value 'G' indicates that this is a tablespace create record.
RCD-TYPE-NO
A three-digit sequence number that keeps track of the number of tablespaces in the extract. For example, a value of '002' indicates it is the second tablespace in the extract.
CREATE-TEXT
Contains the Db2 DDL required to create or alter tablespaces.
Db2 Source Environment Record — C
The Db2 Source Environment record is generated when you extract records from a Db2 object. It contains Db2 catalog information captured during extract processing to create and alter Db2 objects that File-AID/RDX uses to generate load JCL. The format of the Db2 Source Environment record is shown in the following figure.
In order to increase data security the File-AID/RDX extract file no longer includes DDL statements in readable form. The Db2 source environment information is now captured in a masked format.
Db2 Source Environment Record Layout
05 RCD-TYPE PIC X(1) VALUE 'C'.
05 RCD-TYPE-NO PIC X(3) VALUE '001'.
05 CREATE-ATTR BIN X(..).
The following is an explanation of the fields contained in the Db2 Source Environment record layout:
RCD-TYPE
A value of 'C' will be present in this field. The value 'C' indicates that this is a Db2 Source Environment record.
RCD-TYPE-NO
A value of '001' will be present in this field. The value '001' indicates that this is a Db2 Source Environment record.
CREATE-ATTR
Contains the Db2 Source Environment information for the extracted Db2 object in a masked format.
MVS File Create Record — E
The MVS File create record is generated for each extracted MVS object. It contains file allocation and filed mapping information captured during extract processing to create and alter objects that File-AID/RDX uses during the load process to support autocreate. The format of the MVS file create record is shown in the following figure.
MVS File Create Record Layout
05 RCD-TYPE PIC X(1) VALUE 'E'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 CREATE-ATTR BIN X(..).
The following is an explanation of the fields contained in the MVS file create record layout:
RCD-TYPE
A value of 'E' will be present in this field. The value 'E' indicates that this is a MVS file create record.
RCD-TYPE-NO
A three-digit alphanumeric ID that corresponds to the header record number indicating the object to which this record corresponds. For example, if a value of '002' is present, look at record type 'H002' to determine the object to which this record corresponds.
CREATE-ATTR
Contains the binary records for allocation information for the file, alternate indexes information, path definitions, and map data. All data is related to the object identified in the corresponding 'Hnnn' record.
View Create Record — V
(Obsolete - only applies to extracts created with File-AID/RDX Release 4.5 or earlier.)
The view create record is generated when you specify YES in the Views field in Extract File Specification Screen to capture all create view DDL related to the extracted Db2 objects. The format of the create record is shown in the following figure.
View Create Record Layout
05 RCD-TYPE PIC X(1) VALUE 'V'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 CREATE-TEXT PIC X(76).
The following is an explanation of the fields contained in the view create record layout:
RCD-TYPE
A value of 'V' will be present in this field. The value 'V' indicates that this is a view create record.
RCD-TYPE-NO
A three-digit alphanumeric that corresponds to the header record number indicating the view to which this record corresponds. For example, if a value of '002' is present, look at record type 'H002' to determine the view to which this record corresponds.
CREATE-TEXT
Contains the Db2 DDL required to create views. All DDL is related to the view identified in the corresponding 'Hnnn' record.
Alias Create Record — A
(Obsolete - only applies to extracts created with File-AID/RDX Release 4.5 or earlier.)
The alias create record is generated when you specify YES in the Alias field in Extract File Specification Screen to capture all create alias DDL related to the extracted Db2 objects. The format of the create record is shown in the following figure.
Alias Create Record Layout
05 RCD-TYPE PIC X(1) VALUE 'A'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 CREATE-TEXT PIC X(76).
The following is an explanation of the fields contained in the alias create record layout:
RCD-TYPE
A value of 'A' will be present in this field. The value 'A' indicates that this is a alias create record.
RCD-TYPE-NO
A three-digit sequence number that keeps track of the number of aliases in the extract. For example, a value of '002' indicates it is the second alias in the extract.
CREATE-TEXT
Contains the Db2 DDL required to create or alter aliases.
Synonym Create Record — S
(Obsolete - only applies to extracts created with File-AID/RDX Release 4.5 or earlier.)
The create record is generated when you specify YES in the Synonyms field in Extract File Specification Screen to capture all create synonym DDL related to the extracted Db2 objects. The format of the create record is shown in the following figure.
Synonym Create Record Layout
05 RCD-TYPE PIC X(1) VALUE 'S'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 CREATE-TEXT PIC X(76).
The following is an explanation of the fields contained in the synonym create record layout:
RCD-TYPE
A value of 'S' will be present in this field. The value 'S' indicates that this is a synonym create record.
RCD-TYPE-NO
A three-digit sequence number that keeps track of the number of synonyms in the extract. For example, a value of '002' indicates it is the second synonym in the extract.
CREATE-TEXT
Contains the Db2 DDL required to create or alter synonyms. All DDL is related to the table identified in the corresponding ’Hnnn’ record.
Map Record — M
(Obsolete - only applies to extracts created with File-AID/RDX Release 2.5.1 or earlier.) The map record is only generated when you specify NO in the Capture Table and Index DDL for the LOAD Auto Create feature field in the LOAD Related Processing Options pop-up window. The map record is similar to the create record except that the map record is only used during load processing for column mapping. The format of the map record is shown in the following figure.
Map Record Layout
05 RCD-TYPE PIC X(1) VALUE 'M'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 MAP-TEXT PIC X(76).
The following is an explanation of the fields contained in the map record layout:
RCD-TYPE
A value of 'M' will be present in this field. The value 'M' indicates that this is a map record.
RCD-TYPE-NO
A three-digit number that corresponds to the header record number indicating to which table this record corresponds. For example, if a value of '002' is present, look at record type 'H002' to determine to which table this record corresponds.
MAP-TEXT
Contains the Db2 DDL used during load processing for column mapping. All DDL is related to the table identified in the corresponding 'Hnnn' record.
Relationship Record — R
The relationship record is generated when your extract includes related objects. It contains the relationship information for the related objects when the extract was created. File-AID/RDX uses this information when disguising the extract using the Data Studio’s Data Privacy plugin of the Workbench for Eclipse. The format of the relationship record is shown in the following figure.
In order to increase data security of the File-AID/RDX extract file the relationship information is not in readable form.
Relationship Record Layout
05 RCD-TYPE PIC X(1) VALUE 'R'.
05 RCD-TYPE-NO PIC X(3) VALUE '001'.
05 CREATE-ATTR BIN X(..).
The following is an explanation of the fields contained in the relationship record layout:
RCD-TYPE
A value of 'R' will be present in this field. The value 'R' indicates that this is a relationship record.
RCD-TYPE-NO
A value of '001' will be present in this field. The value '001' indicates that this is a relationship record.
CREATE-ATTR
Contains the relationship information for the extracted Db2 objects in a masked format.
Db2 Selected Column Names Record — U
The Db2 Selected Column Names record is only generated for a Db2 single table extract with the selective column option. It describes the selected column names. The format of the Db2 Selected Column Names record is shown in the following figure.
The Db2 Selected Column Names information is captured in a masked format.
Db2 Selected Column Names Record Layout
05 RCD-TYPE PIC X(1) VALUE 'U'.
05 RCD-TYPE-NO PIC X(3) VALUE '001'.
05 U001_ENTRY BIN X(..).
The following is an explanation of the fields contained in the Db2 Selected Column Names record layout:
RCD-TYPE
A value of 'U' will be present in this field. The value 'U' indicates that this is a Db2 Selected Column Names record.
RCD-TYPE-NO
A value of '001' will be present in this field. The value '001' indicates that this is a Db2 Selected Column Names record.
U001_ENTRY
Contains the list of column names for the Db2 single table extract in a masked format.
Data Record — D
The data record contains the actual data extracted from Db2 and MVS objects. The format of the data record is shown in the following figure.
Data Record Layout
05 RCD-TYPE PIC X(1) VALUE 'D'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 ROW-DATA PIC X.
The following is an explanation of the fields contained in the data record layout:
RCD-TYPE
A value of 'D' will be present in this field. The value 'D' indicates that this is a data record.
RCD-TYPE-NO
A three-digit alphanumeric that corresponds to the header record number, indicating the table from which this record was extracted. For example, if a value of '002' was present, look at record type 'H002' to determine the table from which this record was extracted.
ROW-DATA
Contains the contents of the row. The length of this field depends on the length of the row. Data records are in compressed form if the extract file was created with the compression option (see also Compress).
If the D record supplement indicator in the product record (see Product Record — 0 ) is set to Y, the D record also includes a record number that ensures that data extracted from an MVS file without an index but with an XREF, will be loaded to the target in the sequence in which they were read.
XML/LOB Data Record — x
The XML/LOB data record contains the actual XML or LOB data extracted from Db2 objects. The format of the XML/LOB data record is shown in the following figure.
XML/LOB Data Record Layout
05 RCD-TYPE PIC X(1) VALUE 'x'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 ROW-DATA PIC X.
The following is an explanation of the fields contained in the XML/LOB data record layout:
RCD-TYPE
A value of 'x' will be present in this field. The value 'x' indicates that this is a XML or LOB data record.
RCD-TYPE-NO
A three-digit alphanumeric ID that corresponds to the header record number, indicating the table from which this record was extracted. For example, if a value of '002' was present, look at record type 'H002' to determine the table from which this record was extracted.
ROW-DATA
Contains the contents of the row. The length of this field depends on the length of the row. XML/LOB data records are in a masked format to provide data security.
Order Record — O
The order records are used to record the order in which objects were extracted. The format of the data record is shown in the following figure.
Order Record Layout
05 RCD-TYPE PIC X(1) VALUE 'O'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 NBR_ENTS PIC S9(9) COMP.
The following is an explanation of the fields contained in the order record layout:
RCD-TYPE
A value of 'O' will be present in this field. The value 'O' indicates that this is an order record.
RCD-TYPE-NO
A three-digit alphanumeric that corresponds to the header record number, indicating the table from which this record was extracted. For example, if a value of '002' was present, look at record type 'H002' to determine the object from which this record was extracted.
NBR_ENTS
The first ‘O’ record and the last ‘O’ record identify the total number of ‘O’ records in this extract in binary.
Trailer Record — T
A trailer record will be present for each header record in the extract. The format of the trailer record is shown in the following figure.
Trailer Record Layout
05 RCD-TYPE PIC X(1) VALUE 'T'.
05 RCD-TYPE-NO PIC X(3) VALUE 'nnn'.
05 SUBSYSTEM PIC X(4).
05 OBJECT_NAME PIC X(52).
10 LOCATION PIC X(16).
10 CREATOR PIC X(8).
10 TABLE-NAME PIC X(18).
10 FILLER PIC X(10).
05 ROW-COUNT PIC X(8).
05 OBJECT_TYP PIC X(1).
05 RCD_LENGTH PIC X(8).
05 NUM_PARTITIONS PIC X(1) VALUE 'Y'.
05 PARTKEYCOLNUM PIC X(1) VALUE 'Y'.
05 SECURITY_LABEL PIC X(1) VALUE 'Y'.
05 SPLIT_ROWS PIC X(1) VALUE 'Y'.
05 FILLER PIC X(9).
05 OBJNAME_DB2V8 PIC X(272).
10 LOCATION PIC X(16).
10 CREATOR PIC X(128).
10 TABLE-NAME PIC X(128).
05 XML_TOT PIC S9(09) COMP-5.
05 XML_MAX PIC S9(09) COMP-5.
The following is an explanation of the fields contained in the trailer record layout:
RCD-TYPE
A value of 'T' will be present. The value 'T' indicates that this is a trailer record.
RCD-TYPE-NO
A three-digit alphanumeric that corresponds to the header record number, indicating the object to which this record refers. For example, if a value of '002' was present, look at record type 'H002' to determine the object to which this record refers.
SUBSYSTEM
The Db2 subsystem in which the object corresponding to this record exists. Blank for MVS objects.
OBJECT_NAME
Identifies the object name; if object type is M the full MVS file name is listed; if object type is D the location, creator and table names are listed instead:
LOCATION | The location at which the table corresponding to this record exists. |
CREATOR | The authorization ID of the owner of the table corresponding to this record (blank, if Db2 long object name). |
TABLE-NAME | The name of the table to which this record corresponds (blank, if Db2 long object name). |
FILLER | Filler. |
ROW-COUNT
The number of rows extracted from the table that corresponds to this record.
OBJECT_TYPE
Identifies the object type: D = Db2, M = MVS.
RCD_LENGTH
The maximum record length extracted from the object that corresponds to this record.
OBJ_SIZE
The number of bytes (Kilobytes?) per object extracted from the object that corresponds to this record.
NUM_PARTITIONS
If the number of tablespace partitions is greater than 254, this indicator is set to Y (Autocreate disabled).
PARTKEYCOLNUM
If table controlled partitioning is used, this indicator is set to Y (Autocreate disabled).
SECURITY_LABEL
If the security_label is R, this indicator is set to Y (Autocreate disabled).
SPLIT_ROWS
If split rows is enabled, this indicator is set to Y (Autocreate disabled).
OBJNAME_DB2V8
Identifies the object name in its full length; if object type is M the full MVS file name is listed; if object type is D the location, creator and table names are listed instead:
LOCATION | The location at which the table corresponding to this record exists. |
CREATOR | The authorization ID of the owner of the table corresponding to this record (up to 128 characters). |
TABLE-NAME | The name of the table to which this record corresponds (up to 128 characters). |
XML_TOT
Total length in Kilobytes (in binary format) of all XML or LOB columns for the table that corresponds to this record.
XML_MAX
Maximum size in Kilobytes (in binary format) of an XML or LOB value for the table that corresponds to this record.
Work files
During an extract, File-AID/RDX creates some work files that it deletes after extract execution.
The work files have the following format::
Where prefix is set by the profile variable File Prefix, and * is either EXTCRIT or PARMS, and suffix is set by the profile variable File Suffix.
Periodically, you may want to manually delete any left-over work files from incomplete extracts that are no longer needed.