Examples

The following scenarios illustrate how to extract test matching data from the following sources:
tdm481
The following scenarios illustrate how to extract test matching data from the following sources:
  • A single record file
  • A multi-record file
  • An IMS database containing customer data.
Example 1 – Extract from a File with One Record Type
In this scenario, a file with a single record type has copybook layout seen in Appendix A.This copybook is parsed to create an AFL named SINGLE_FILE_zOS.AFL.DM.txt (see Appendix G).
  1. In Datamaker, navigate to
    Project
    and
    Register
  2. Register an AFL in Project TestMatchExtract, Version SingleFile.
  3. Run a profiling job.
  4. Set up a transformation map to describe the fields to extract.
  5. Create a transformation map with a
    ZOS
    DBMS.
    Note:
    Because the file has only one record type, there are no parent-child relationships. No parent-child relationships indicate that the TESTMARTKEY function is not used. Do not select the
    Ordered
    option when you create the map.
The fields that you select for extract depend on an analysis of the sampling results./ The selected fields also depend don the application where the file is used.
In this example, REC1_ALPHA and REC1_ACCUM are key fields. These fields have a TESTMARTDATA selected rather than TESTMARTKEY. TESTMARTKEY only affects the output from the extract job if the AFL contains parent-child relationships. REC1_STATUS is a single-byte field which can contain non-display characters, for this reason the TESTMARTDATA function is given a parameter value of "HEX".
Once the data extract fields are specified, exported the transformation map with a type of CSV-ZOS.  Transfer the map to z/OS for input to the data extract program.
Outputs If
TARGETDMBS=SQLSERVER
The outputs from the extract program include a table definition (output to DDname TABS):
CREATE TABLE
[TEST].[dbo].[GTTM_REC1_RECORD](
[REC1_RECORD_REC1_ALPHA] [char](14) NULL,
[REC1_RECORD_REC1_ACCUM] [decimal](3,0) NULL,
[REC1_RECORD_REC1_STATUS] [char](2) NULL,
[REC1_RECORD_REC1_STATE] [char](2) NULL,
[REC1_RECORD_REC1_DOB] [date] NULL,
[REC1_RECORD_REC1_SEX] [char](1) NULL,
[REC1_RECORD_REC1_MARITAL] [char](1) NULL,
[REC1_RECORD_REC1_OCCUP_CD] [char](5) NULL,
[REC1_RECORD_REC1_EDUC_LVL] [char](2) NULL,
[REC1_RECORD_REC1_INC_CLASS] [char](5) NULL,
[REC1_RECORD_REC1_OWN_RENT_CD] [char](1) NULL
)
A BCP command file (output to DDname CARDS):-
bcp "TEST.dbo.GTTM_REC1_RECORD" in "SYSREC01.dat" -f "CARDS01.txt" -e "ERROR01.txt" -q -T
A BCP format file (output to DDname CARDS01):-
9.0
11
1 SQLCHAR 0 14 "\t" 1 REC1_RECORD_REC1_ALPHA
Lat-in1_General_CI_AS
2 SQLCHAR 0 41 "\t" 2 REC1_RECORD_REC1_ACCUM
Lat-in1_General_CI_AS
3 SQLCHAR 0 2 "\t" 3 REC1_RECORD_REC1_STATUS
Lat-in1_General_CI_AS
4 SQLCHAR 0 2 "\t" 4 REC1_RECORD_REC1_STATE
Lat-in1_General_CI_AS
5 SQLCHAR 0 11 "\t" 5 REC1_RECORD_REC1_DOB
Lat-in1_General_CI_AS
6 SQLCHAR 0 1 "\t" 6 REC1_RECORD_REC1_SEX
Lat-in1_General_CI_AS
7 SQLCHAR 0 1 "\t" 7 REC1_RECORD_REC1_MARITAL
Lat-in1_General_CI_AS
8 SQLCHAR 0 5 "\t" 8 REC1_RECORD_REC1_OCCUP_CD
Lat-in1_General_CI_AS
9 SQLCHAR 0 2 "\t" 9 REC1_RECORD_REC1_EDUC_LVL
Lat-in1_General_CI_AS
10 SQLCHAR 0 5 "\t" 10 REC1_RECORD_REC1_INC_CLASS
Lat-in1_General_CI_AS
11 SQLCHAR 0 1 "\t\r\n" 11 REC1_RECORD_REC1_OWN_RENT_CD
Lat-in1_General_CI_AS
A data file (output to DDname SYSREC01), is not reproduced here.
Output If
TARGETDMBS=ORACLE
The outputs from the extract program include a table definition (output to DDname TABS):
CREATE TABLE TESTM.GTTM_REC1_RECORD ( REC1_RECORD_REC1_ALPHA CHAR (00014) , REC1_RECORD_REC1_ACCUM DECIMAL ( 3, 0) , REC1_RECORD_REC1_STATUS CHAR (00002) , REC1_RECORD_REC1_STATE CHAR (00002) , REC1_RECORD_REC1_DOB DATE , REC1_RECORD_REC1_SEX CHAR (00001) , REC1_RECORD_REC1_MARITAL CHAR (00001) , REC1_RECORD_REC1_OCCUP_CD CHAR (00005) , REC1_RECORD_REC1_EDUC_LVL CHAR (00002) , REC1_RECORD_REC1_INC_CLASS CHAR (00005) , REC1_RECORD_REC1_OWN_RENT_CD CHAR (00001) );
A SQLLDR command file (output to DDname CARDS):-
LOAD DATA
CHARACTERSET WE8EBCDIC500 BYTEORDER BIG
INFILE 'C:\FILE\PREFIX\SYSREC01.dat' "VAR 5"
BADFILE 'C:\ERR\PREFIX\SYSREC01.bad'
DISCARDFILE 'C:\ERR\PREFIX\SYSREC01.dsc'
REPLACE
INTO TABLE TESTM.GTTM_REC1_RECORD
WHEN (1:2) = '01'
(
REC1_RECORD_REC1_ALPHA POSITION (00003:00016)
CHAR
NULLIF(00017)='?' ,
REC1_RECORD_REC1_ACCUM POSITION (00018:00019)
DECIMAL ( 3, 0)
NULLIF(00020)='?' ,
REC1_RECORD_REC1_STATUS POSITION (00021:00022)
CHAR
NULLIF(00023)='?' ,
REC1_RECORD_REC1_STATE POSITION (00024:00025)
CHAR
NULLIF(00026)='?' ,
REC1_RECORD_REC1_DOB POSITION (00027:00036)
DATE "YYYY-MM-DD"
NULLIF(00037)='?' ,
REC1_RECORD_REC1_SEX POSITION (00038:00038)
CHAR
NULLIF(00039)='?' ,
REC1_RECORD_REC1_MARITAL POSITION (00040:00040)
CHAR
NULLIF(00041)='?' ,
REC1_RECORD_REC1_OCCUP_CD POSITION (00042:00046)
CHAR
NULLIF(00047)='?' ,
REC1_RECORD_REC1_EDUC_LVL POSITION (00048:00049)
CHAR
NULLIF(00050)='?' ,
REC1_RECORD_REC1_INC_CLASS POSITION (00051:00055)
CHAR
NULLIF(00056)='?' ,
REC1_RECORD_REC1_OWN_RENT_CD POSITION (00057:00057)
CHAR
NULLIF(00058)='?'
)
A data file (output to DDname SYSREC01), not reproduced here.
Example 2 – Extract from a File with Multiple Record Types
In this scenario, a file has three record types. The cookbook type layouts of the types are noted in Appendix C. In this case, the file is a VSAM KSDS. The file might also be a flat file with the same record types.
The copybooks are parsed to create an AFL named MULTI_FILE.AFL.DM.txt (see Appendix H).
Because the file contains multiple record types, edit the AFL to record the record type conditions. Use the copybook editor to edit the AFL.
  1. In Datamaker, navigate to
    Project
    and
    Register
  2. Register an AFL in Project TestMatchExtract, Version MultiFile.
  3. Run a profiling job.
    The transformation map can now be set up to describe the fields to extract.
  4. Create a transformation map with a
    ZOS
    DBMS to describe the fields to extract.
    Note:
    Because the file has only one record type, there are no parent-child relationships. The lack of parent-child relationships indicate that the TESTMARTKEY function is not used. Do not select the
    Ordered
    option when you create the map.
Create the transformation map with a
ZOS
DBMS.The file has multiple record types, but no parent-child relationships are added. Because the file is a VSAM KSDS, each record that relates to a customer contains the customer key. In this case, do not use the TESTMARTKEY function, and do not select the
Ordered
option when you create the map.
The fields to select for extract depend on analysis of the sampling results. The field selected also depends on the application in which the file is used.
In this example, each record has key fields RECn_ALPHA and RECn_ACCUM (where n = 1, 2 or 3). These fields are included in the extract to join data items from different records for the same customer.
Once the data extract fields are specified, exported the transformation map with a type of CSV-ZOS. Transfer the map to z/OS for input to the data extract program.
Outputs If
TARGETDMBS=SQLSERVER
The outputs from the extract program include a three table definitions (output to DDname TABS):-
CREATE TABLE
[TESTM].[dbo].[GTTM_REC1_RECORD](
[REC1_RECORD_REC1_ALPHA] [char](14) NULL,
[REC1_RECORD_REC1_ACCUM] [decimal](3,0) NULL,
[REC1_RECORD_REC1_STATUS] [char](2) NULL,
[REC1_RECORD_REC1_STATE] [char](2) NULL,
[REC1_RECORD_REC1_DOB] [date] NULL,
[REC1_RECORD_REC1_SEX] [char](1) NULL,
[REC1_RECORD_REC1_MARITAL] [char](1) NULL,
[REC1_RECORD_REC1_OCCUP_CD] [char](5) NULL,
[REC1_RECORD_REC1_EDUC_LVL] [char](2) NULL,
[REC1_RECORD_REC1_OWN_RENT_CD] [char](1) NULL
)
CREATE TABLE
[TESTM].[dbo].[GTTM_REC2_RECORD](
[REC2_RECORD_REC2_ALPHA] [char](14) NULL,
[REC2_RECORD_REC2_ACCUM] [decimal](3,0) NULL,
[REC2_RECORD_REC2_EFF_DT] [date] NULL,
[REC2_RECORD_REC2_EXP_DT] [date] NULL,
[REC2_RECORD_REC2_STATE] [char](2) NULL
)
CREATE TABLE
[TESTM].[dbo].[GTTM_REC3_RECORD](
[REC3_RECORD_REC3_ALPHA] [char](14) NULL,
[REC3_RECORD_REC3_ACCUM] [decimal](3,0) NULL,
[REC3_RECORD_REC3_ACCT_INST] [decimal](5,0) NULL,
[REC3_RECORD_REC3_APPL] [decimal](2,0) NULL,
[REC3_RECORD_REC3_BRANCH] [decimal](5,0) NULL,
[REC3_RECORD_REC3_CLASS] [decimal](3,0) NULL,
[REC3_RECORD_REC3_ACCT] [decimal](21,0) NULL
)
A BCP command file (output to DDname CARDS):-
bcp "TESTM.dbo.GTTM_REC1_RECORD" in "SYSREC01.dat" -f "CARDS01.txt" -e "ERROR01.txt" -q -T
bcp "TESTM.dbo.GTTM_REC2_RECORD" in "SYSREC02.dat" -f "CARDS02.txt" -e "ERROR02.txt" -q -T
bcp "TESTM.dbo.GTTM_REC3_RECORD" in "SYSREC03.dat" -f "CARDS03.txt" -e "ERROR03.txt" -q -T
Three BCP format files (output to DDnames CARDS01, CARDS02 and CARDS03):
9.0
11
1 SQLCHAR 0 14 "\t" 1 REC1_RECORD_REC1_ALPHA
Latin1_General_CI_AS
2 SQLCHAR 0 41 "\t" 2 REC1_RECORD_REC1_ACCUM
Latin1_General_CI_AS
3 SQLCHAR 0 2 "\t" 3 REC1_RECORD_REC1_STATUS
Latin1_General_CI_AS
4 SQLCHAR 0 2 "\t" 4 REC1_RECORD_REC1_STATE
Latin1_General_CI_AS
5 SQLCHAR 0 11 "\t" 5 REC1_RECORD_REC1_DOB
Latin1_General_CI_AS
6 SQLCHAR 0 1 "\t" 6 REC1_RECORD_REC1_SEX
Latin1_General_CI_AS
7 SQLCHAR 0 1 "\t" 7 REC1_RECORD_REC1_MARITAL
Latin1_General_CI_AS
8 SQLCHAR 0 5 "\t" 8 REC1_RECORD_REC1_OCCUP_CD
Latin1_General_CI_AS
9 SQLCHAR 0 2 "\t" 9 REC1_RECORD_REC1_EDUC_LVL
Latin1_General_CI_AS
10 SQLCHAR 0 5 "\t" 10 REC1_RECORD_REC1_INC_CLASS
Latin1_General_CI_AS
11 SQLCHAR 0 1 "\t\r\n" 11 REC1_RECORD_REC1_OWN_RENT_CD
Latin1_General_CI_AS
9.0
5
1 SQLCHAR 0 14 "\t" 1 REC2_RECORD_REC2_ALPHA
Latin1_General_CI_AS
2 SQLCHAR 0 41 "\t" 2 REC2_RECORD_REC2_ACCUM
Latin1_General_CI_AS
3 SQLCHAR 0 11 "\t" 3 REC2_RECORD_REC2_EFF_DT
Latin1_General_CI_AS
4 SQLCHAR 0 11 "\t" 4 REC2_RECORD_REC2_EXP_DT
Latin1_General_CI_AS
5 SQLCHAR 0 2 "\t\r\n" 5 REC2_RECORD_REC2_STATE
Latin1_General_CI_AS
9.0
7
1 SQLCHAR 0 14 "\t" 1 REC3_RECORD_REC3_ALPHA
Latin1_General_CI_AS
2 SQLCHAR 0 41 "\t" 2 REC3_RECORD_REC3_ACCUM
Latin1_General_CI_AS
3 SQLCHAR 0 41 "\t" 3 REC3_RECORD_REC3_ACCT_INST
Latin1_General_CI_AS
4 SQLCHAR 0 41 "\t" 4 REC3_RECORD_REC3_APPL
Latin1_General_CI_AS
5 SQLCHAR 0 41 "\t" 5 REC3_RECORD_REC3_BRANCH
Latin1_General_CI_AS
6 SQLCHAR 0 41 "\t" 6 REC3_RECORD_REC3_CLASS
Latin1_General_CI_AS
7 SQLCHAR 0 41 "\t\r\n" 7 REC3_RECORD_REC3_ACCT
Latin1_General_CI_AS
Three data files (output to DDnames SYSREC01, SYSREC02 and SYSREC03), not reproduced here.
Outputs If
TARGETDMBS=ORACLE
The outputs from the extract program include three table definitions (output to DDname TABS):-
CREATE TABLE TESTM.GTTM_REC1_RECORD (
REC1_RECORD_REC1_ALPHA
CHAR (00014) ,
REC1_RECORD_REC1_ACCUM
DECIMAL ( 3, 0) ,
REC1_RECORD_REC1_STATUS
CHAR (00002) ,
REC1_RECORD_REC1_STATE
CHAR (00002) ,
REC1_RECORD_REC1_DOB
DATE ,
REC1_RECORD_REC1_SEX
CHAR (00001) ,
REC1_RECORD_REC1_MARITAL
CHAR (00001) ,
REC1_RECORD_REC1_OCCUP_CD
CHAR (00005) ,
REC1_RECORD_REC1_EDUC_LVL
CHAR (00002) ,
REC1_RECORD_REC1_OWN_RENT_CD
CHAR (00001)
);
CREATE TABLE TESTM.GTTM_REC2_RECORD (
REC2_RECORD_REC2_ALPHA
CHAR (00014) ,
REC2_RECORD_REC2_ACCUM
DECIMAL ( 3, 0) ,
REC2_RECORD_REC2_EFF_DT
DATE ,
REC2_RECORD_REC2_EXP_DT
DATE ,
REC2_RECORD_REC2_STATE
CHAR (00002)
);
CREATE TABLE TESTM.GTTM_REC3_RECORD (
REC3_RECORD_REC3_ALPHA
CHAR (00014) ,
REC3_RECORD_REC3_ACCUM
DECIMAL ( 3, 0) ,
REC3_RECORD_REC3_ACCT_INST
DECIMAL ( 5, 0) ,
REC3_RECORD_REC3_APPL
DECIMAL ( 2, 0) ,
REC3_RECORD_REC3_BRANCH
DECIMAL ( 5, 0) ,
REC3_RECORD_REC3_CLASS
DECIMAL ( 3, 0) ,
REC3_RECORD_REC3_ACCT
DECIMAL (21, 0)
);
A SQLLDR command file (output to DDname CARDS):-
LOAD DATA
CHARACTERSET WE8EBCDIC500 BYTEORDER BIG
INFILE 'C:\FILE\PREFIX\SYSREC01.dat' "VAR 5"
BADFILE 'C:\ERR\PREFIX\SYSREC01.bad'
DISCARDFILE 'C:\ERR\PREFIX\SYSREC01.dsc'
INFILE 'C:\FILE\PREFIX\SYSREC02.dat' "VAR 5"
BADFILE 'C:\ERR\PREFIX\SYSREC02.bad'
DISCARDFILE 'C:\ERR\PREFIX\SYSREC02.dsc'
INFILE 'C:\FILE\PREFIX\SYSREC03.dat' "VAR 5"
BADFILE 'C:\ERR\PREFIX\SYSREC03.bad'
DISCARDFILE 'C:\ERR\PREFIX\SYSREC03.dsc'
REPLACE
INTO TABLE TESTM.GTTM_REC1_RECORD
WHEN (1:2) = '01'
(
REC1_RECORD_REC1_ALPHA POSITION (00003:00016)
CHAR
NULLIF(00017)='?' ,
REC1_RECORD_REC1_ACCUM POSITION (00018:00019)
DECIMAL ( 3, 0)
NULLIF(00020)='?' ,
REC1_RECORD_REC1_STATUS POSITION (00021:00022)
CHAR
NULLIF(00023)='?' ,
REC1_RECORD_REC1_STATE POSITION (00024:00025)
CHAR
NULLIF(00026)='?' ,
REC1_RECORD_REC1_DOB POSITION (00027:00036)
DATE "YYYY-MM-DD"
NULLIF(00037)='?' ,
REC1_RECORD_REC1_SEX POSITION (00038:00038)
CHAR
NULLIF(00039)='?' ,
REC1_RECORD_REC1_MARITAL POSITION (00040:00040)
CHAR
NULLIF(00041)='?' ,
REC1_RECORD_REC1_OCCUP_CD POSITION (00042:00046)
CHAR
NULLIF(00047)='?' ,
REC1_RECORD_REC1_EDUC_LVL POSITION (00048:00049)
CHAR
NULLIF(00050)='?' ,
REC1_RECORD_REC1_OWN_RENT_CD POSITION (00051:00051)
CHAR
NULLIF(00052)='?'
)
INTO TABLE TESTM.GTTM_REC2_RECORD
WHEN (1:2) = '02'
(
REC2_RECORD_REC2_ALPHA POSITION (00003:00016)
CHAR
NULLIF(00017)='?' ,
REC2_RECORD_REC2_ACCUM POSITION (00018:00019)
DECIMAL ( 3, 0)
NULLIF(00020)='?' ,
REC2_RECORD_REC2_EFF_DT POSITION (00021:00030)
DATE "YYYY-MM-DD"
NULLIF(00031)='?' ,
REC2_RECORD_REC2_EXP_DT POSITION (00032:00041)
DATE "YYYY-MM-DD"
NULLIF(00042)='?' ,
REC2_RECORD_REC2_STATE POSITION (00043:00044)
CHAR
NULLIF(00045)='?'
)
INTO TABLE TESTM.GTTM_REC3_RECORD
WHEN (1:2) = '03'
(
REC3_RECORD_REC3_ALPHA POSITION (00003:00016)
CHAR
NULLIF(00017)='?' ,
REC3_RECORD_REC3_ACCUM POSITION (00018:00019)
DECIMAL ( 3, 0)
NULLIF(00020)='?' ,
REC3_RECORD_REC3_ACCT_INST POSITION (00021:00023)
DECIMAL ( 5, 0)
NULLIF(00024)='?' ,
REC3_RECORD_REC3_APPL POSITION (00025:00026)
DECIMAL ( 2, 0)
NULLIF(00027)='?' ,
REC3_RECORD_REC3_BRANCH POSITION (00028:00030)
DECIMAL ( 5, 0)
NULLIF(00031)='?' ,
REC3_RECORD_REC3_CLASS POSITION (00032:00033)
DECIMAL ( 3, 0)
NULLIF(00034)='?' ,
REC3_RECORD_REC3_ACCT POSITION (00035:00045)
DECIMAL (21, 0)
NULLIF(00046)='?'
)
Three data files (output to DDnames SYSREC01, SYSREC02 and SYSREC03), not reproduced here.
Example 3 – Extract from an IMS Database
In this scenario, an IMS database has three segment types. The copybook type layout is noted in in Appendix D, E, and F. SEG1 is the root segment with a key given by SEG1-CUST-KEY. SEG2 and SEG3 are children of SEG1. This example uses a  simple database).
The copybooks are parsed to create an AFL named IMS.AFL.DM.txt (see Appendix I). The
IMS
button is checked when the parser is run. The parser prefixes each segment layout with an eight character field named SEGNAME.
Because the file contains multiple record/segment types, edit the AFL to record the record type conditions. Use the copybook editor to edit the AFL. Use the SEGNAME field in the record conditions. The data extract program runs against a flat file that contains segment data. Prefix each record in the file with an eight character field to hold the segment name.
Unlike the earlier VSAM KSDS example, in this case there is no key on each record/segment to join different segments for a given customer. The key is only present on the root segment. To specify the segment hierarchy, set parent records for all segments other than the root.
  1. In Datamaker, navigate to
    Project
    and
    Register.
  2. Register an AFL in Project TestMatchExtract, Version IMS.
  3. Run a profiling job (not shown here)
  4. Set up the transformation map to describe the fields to extract.
  5. Create a transformation map with a
    ZOS
    DBMS
The file has multiple record/segment types, and we added parent-child relationships. Because the TESTMARTKEY function is used to tag key fields, the map is created with the
Ordered
option. The TESTMARTKEY fields are saved as the first fields in the map.
Which fields you select for extract depends on an analysis of the sampling results. The fields selected also depend on application in which the file is used.
SEG1 is the root segment and has a key that is made up of SEG1_ALPHA and SEG1_ACCUM. These items are selected for extract with the TESTMARTKEY function. This extract means that these values from the root segment are included in data extract rows for dependent segments.
Outputs If
TARGETDMBS=SQLSERVER
The outputs from the extract program include a three table definitions (output to DDname TABS):
CREATE TABLE
[TESTM].[dbo].[GTTM_SEG1](
[SEG1_SEG1_ALPHA] [char](14) NULL,
[SEG1_SEG1_ACCUM] [decimal](3,0) NULL,
[SEG1_SEG1_STATUS] [char](2) NULL,
[SEG1_SEG1_STATE] [char](2) NULL,
[SEG1_SEG1_DOB] [decimal](9,0) NULL,
[SEG1_SEG1_SEX] [char](1) NULL,
[SEG1_SEG1_MARITAL] [char](1) NULL,
[SEG1_SEG1_OCCUP_CD] [char](5) NULL,
[SEG1_SEG1_EDUC_LVL] [char](2) NULL,
[SEG1_SEG1_OWN_RENT_CD] [char](1) NULL
)
CREATE TABLE
[TESTM].[dbo].[GTTM_SEG2](
[SEG1_SEG1_ALPHA] [char](14) NULL,
[SEG1_SEG1_ACCUM] [decimal](3,0) NULL,
[SEG2_SEG2_EFF_DT] [decimal](9,0) NULL,
[SEG2_SEG2_EXP_DT] [decimal](9,0) NULL,
[SEG2_SEG2_STATE] [char](2) NULL
)
CREATE TABLE
[TESTM].[dbo].[GTTM_SEG3](
[SEG1_SEG1_ALPHA] [char](14) NULL,
[SEG1_SEG1_ACCUM] [decimal](3,0) NULL,
[SEG3_SEG3_ACCT_INST] [decimal](5,0) NULL
[SEG3_SEG3_APPL] [decimal](2,0) NULL,
[SEG3_SEG3_BRANCH] [decimal](5,0) NULL,
[SEG3_SEG3_CLASS] [decimal](3,0) NULL
)
A BCP command file (output to DDname CARDS):
bcp "TESTM.dbo.GTTM_SEG1" in "SYSREC01.dat" -f "CARDS01.txt" -e "ERROR01.txt" -q -T
bcp "TESTM.dbo.GTTM_SEG2" in "SYSREC02.dat" -f "CARDS02.txt" -e "ERROR02.txt" -q -T
bcp "TESTM.dbo.GTTM_SEG3" in "SYSREC03.dat" -f "CARDS03.txt" -e "ERROR03.txt" -q –T
Three BCP format files (output to DDnames CARDS01, CARDS02 and CARDS03):-
9.0
10
1 SQLCHAR 0 14 "\t" 1 SEG1_SEG1_ALPHA
Latin1_General_CI_AS
2 SQLCHAR 0 41 "\t" 2 SEG1_SEG1_ACCUM
Latin1_General_CI_AS
3 SQLCHAR 0 2 "\t" 3 SEG1_SEG1_STATUS
Latin1_General_CI_AS
4 SQLCHAR 0 2 "\t" 4 SEG1_SEG1_STATE
Latin1_General_CI_AS
5 SQLCHAR 0 41 "\t" 5 SEG1_SEG1_DOB
Latin1_General_CI_AS
6 SQLCHAR 0 1 "\t" 6 SEG1_SEG1_SEX
Latin1_General_CI_AS
7 SQLCHAR 0 1 "\t" 7 SEG1_SEG1_MARITAL
Latin1_General_CI_AS
8 SQLCHAR 0 5 "\t" 8 SEG1_SEG1_OCCUP_CD
Latin1_General_CI_AS
9 SQLCHAR 0 2 "\t" 9 SEG1_SEG1_EDUC_LVL
Latin1_General_CI_AS
10 SQLCHAR 0 1 "\t\r\n" 10 SEG1_SEG1_OWN_RENT_CD
Latin1_General_CI_AS
9.0
5
1 SQLCHAR 0 14 "\t" 1 SEG1_SEG1_ALPHA
Latin1_General_CI_AS
2 SQLCHAR 0 41 "\t" 2 SEG1_SEG1_ACCUM
Latin1_General_CI_AS
3 SQLCHAR 0 41 "\t" 3 SEG2_SEG2_EFF_DT
Latin1_General_CI_AS
4 SQLCHAR 0 41 "\t" 4 SEG2_SEG2_EXP_DT
Latin1_General_CI_AS
5 SQLCHAR 0 2 "\t\r\n" 5 SEG2_SEG2_STATE
Latin1_General_CI_AS
9.0
6
1 SQLCHAR 0 14 "\t" 1 SEG1_SEG1_ALPHA
Latin1_General_CI_AS
2 SQLCHAR 0 41 "\t" 2 SEG1_SEG1_ACCUM
Latin1_General_CI_AS
3 SQLCHAR 0 41 "\t" 3 SEG3_SEG3_ACCT_INST
Latin1_General_CI_AS
4 SQLCHAR 0 41 "\t" 4 SEG3_SEG3_APPL
Latin1_General_CI_AS
5 SQLCHAR 0 41 "\t" 5 SEG3_SEG3_BRANCH
Latin1_General_CI_AS
6 SQLCHAR 0 41 "\t\r\n" 6 SEG3_SEG3_CLASS
Latin1_General_CI_AS
Three data files (output to DDnames SYSREC01, SYSREC02 and SYSREC03), not reproduced here.
Outputs If
TARGETDMBS=ORACLE
CREATE TABLE TESTM.GTTM_SEG1 (
SEG1_SEG1_ALPHA
CHAR (00014) ,
SEG1_SEG1_ACCUM
DECIMAL ( 3, 0) ,
SEG1_SEG1_STATUS
CHAR (00002) ,
SEG1_SEG1_STATE
CHAR (00002) ,
SEG1_SEG1_DOB
DECIMAL ( 9, 0) ,
SEG1_SEG1_SEX
CHAR (00001) ,
SEG1_SEG1_MARITAL
CHAR (00001) ,
SEG1_SEG1_OCCUP_CD
CHAR (00005) ,
SEG1_SEG1_EDUC_LVL
CHAR (00002) ,
SEG1_SEG1_OWN_RENT_CD
CHAR (00001)
);
CREATE TABLE TESTM.GTTM_SEG2 (
SEG1_SEG1_ALPHA
CHAR (00014) ,
SEG1_SEG1_ACCUM
DECIMAL ( 3, 0) ,
SEG2_SEG2_EFF_DT
DECIMAL ( 9, 0) ,
SEG2_SEG2_EXP_DT
DECIMAL ( 9, 0) ,
SEG2_SEG2_STATE
CHAR (00002)
);
CREATE TABLE TESTM.GTTM_SEG3 (
SEG1_SEG1_ALPHA
CHAR (00014) ,
SEG1_SEG1_ACCUM
DECIMAL ( 3, 0) ,
SEG3_SEG3_ACCT_INST
DECIMAL ( 5, 0) ,
SEG3_SEG3_APPL
DECIMAL ( 2, 0) ,
SEG3_SEG3_BRANCH
DECIMAL ( 5, 0) ,
SEG3_SEG3_CLASS
DECIMAL ( 3, 0)
);
A SQLLDR command file (output to DDname CARDS):-
LOAD DATA
CHARACTERSET WE8EBCDIC500 BYTEORDER BIG
INFILE 'C:\FILE\PREFIX\SYSREC01.dat' "VAR 5"
BADFILE 'C:\ERR\PREFIX\SYSREC01.bad'
DISCARDFILE 'C:\ERR\PREFIX\SYSREC01.dsc'
INFILE 'C:\FILE\PREFIX\SYSREC02.dat' "VAR 5"
BADFILE 'C:\ERR\PREFIX\SYSREC02.bad'
DISCARDFILE 'C:\ERR\PREFIX\SYSREC02.dsc'
INFILE 'C:\FILE\PREFIX\SYSREC03.dat' "VAR 5"
BADFILE 'C:\ERR\PREFIX\SYSREC03.bad'
DISCARDFILE 'C:\ERR\PREFIX\SYSREC03.dsc'
REPLACE
INTO TABLE TESTM.GTTM_SEG1
WHEN (1:2) = '01'
(
SEG1_SEG1_ALPHA POSITION (00003:00016)
CHAR
NULLIF(00017)='?' ,
SEG1_SEG1_ACCUM POSITION (00018:00019)
DECIMAL ( 3, 0)
NULLIF(00020)='?' ,
SEG1_SEG1_STATUS POSITION (00021:00022)
CHAR
NULLIF(00023)='?' ,
SEG1_SEG1_STATE POSITION (00024:00025)
CHAR
NULLIF(00026)='?' ,
SEG1_SEG1_DOB POSITION (00027:00031)
DECIMAL ( 9, 0)
NULLIF(00032)='?' ,
SEG1_SEG1_SEX POSITION (00033:00033)
CHAR
NULLIF(00034)='?' ,
SEG1_SEG1_MARITAL POSITION (00035:00035)
CHAR
NULLIF(00036)='?' ,
SEG1_SEG1_OCCUP_CD POSITION (00037:00041)
CHAR
NULLIF(00042)='?' ,
SEG1_SEG1_EDUC_LVL POSITION (00043:00044)
CHAR
NULLIF(00045)='?' ,
SEG1_SEG1_OWN_RENT_CD POSITION (00046:00046)
CHAR
NULLIF(00047)='?'
)
INTO TABLE TESTM.GTTM_SEG2
WHEN (1:2) = '02'
(
SEG1_SEG1_ALPHA POSITION (00003:00016)
CHAR
NULLIF(00017)='?' ,
SEG1_SEG1_ACCUM POSITION (00018:00019)
DECIMAL ( 3, 0)
NULLIF(00020)='?' ,
SEG2_SEG2_EFF_DT POSITION (00021:00025)
DECIMAL ( 9, 0)
NULLIF(00026)='?' ,
SEG2_SEG2_EXP_DT POSITION (00027:00031)
DECIMAL ( 9, 0)
NULLIF(00032)='?' ,
SEG2_SEG2_STATE POSITION (00033:00034)
CHAR
NULLIF(00035)='?'
)
INTO TABLE TESTM.GTTM_SEG3
WHEN (1:2) = '03'
(
SEG1_SEG1_ALPHA POSITION (00003:00016)
CHAR
NULLIF(00017)='?' ,
SEG1_SEG1_ACCUM POSITION (00018:00019)
DECIMAL ( 3, 0)
NULLIF(00020)='?' ,
SEG3_SEG3_ACCT_INST POSITION (00021:00023)
DECIMAL ( 5, 0)
NULLIF(00024)='?' ,
SEG3_SEG3_APPL POSITION (00025:00026)
DECIMAL ( 2, 0)
NULLIF(00027)='?' ,
SEG3_SEG3_BRANCH POSITION (00028:00030)
DECIMAL ( 5, 0)
NULLIF(00031)='?' ,
SEG3_SEG3_CLASS POSITION (00032:00033)
DECIMAL ( 3, 0)
NULLIF(00034)='?'
)
Three data files (output to DDnames SYSREC01, SYSREC02 and SYSREC03), not reproduced here.