Loading data into Db2

About this task

The Load data into Db2 option allows you to view the report data in a format you prefer. It also allows you to load data into Db2 tables for replication purposes. The data created in the report is written to a file in Db2 LOAD format. Log Analysis Tool also generates the necessary DDL and LOAD format JCL to view or replicate your data.

You can run this option as many times as you want against the same report data, specifying different options for each run. As long as the input (ROWDATA) does not change, the data is available. If you run another report, then this will modify the contents of the ROWDATA input data set, and the load data option will only be available against that new set of data.

Important: Db2 imposes a maximum limit to the amount of information allowed in a row in the target table. This limit is 32k. If the amount of data that you have is near that limit, you must also take into account that Log Analysis Tool includes the table name and table owner of each row for identification purposes.

Procedure

From the Db2 Log Analysis Tool - Classic Menu, select option 3, Load data into Db2.

The Load data into Db2 panel is displayed, as shown in the following figure.

Figure 1. Load data into Db2 panel

V3.5.0------------------- Load Data into Db2 ------------------- SC01/SS1A    
 Command ===>                                                                   
                                                                    More:     + 
                                                                                
 *Action................... E   (E - Edit, S - Submit)                          
  Job Identifier........... ASKTEST                                               
 *General report data...... N   (Y/N)                                           
 *Delimited output......... N   (Y - Yes, N - No, S - Specify options)          
 *Fixed external format.... N   (Y - Yes, N - No)                               
 *Header................... N   (Y - Yes, N - No, S - Specify options)          
 *Change Flag.............. N   (Y/N)                                           
 *Data Only................ N   (Y/N)
 *Log...................... N   (Y/N)                                           
 *Audit mode............... N   (Y - Yes, N - No, S - Specify options)          
 *LOAD specification....... A   (A - All, B - Insert, C - Delete,               
                                 D - Update post, E - Update pre)               
  LOB PDS name.............                                                     
 *Split load files......... N   (Y/N)                                           
 *Split load cards......... N   (Y/N)                                           
 *Modify split LOADFILE DSN N   (Y/N)                                           
 *Edit column order........ N   (Y/N)   
 *Expert options........... N   (Y/N)
 *Include load step in JCL. N   (Y/N)
 *Source/target tables..... Y   (Y/N)
 *Create target tables..... N   (Y/N)

Select the desired options and press PF3 to process. Log Analysis Tool generates the JCL to load the data into the target table and saves it in a file named LOADFILE and/or LOADFDRP.
Submit the generated JCL to load the data into the target table and query it using any standard methods.
- Load data into Db2 panel fields:
  Action
  
  Type E to edit the generated JCL, or S to submit the JCL directly to generate the report.
  
  Job Identifier
  
  This is the same job identifier as used in general/detail reports and is an eight-character maximum field with which data set names for different reports can be identified.
  
  General report data
  - Type N to indicate that the data is strictly detail report data. This is the default.
  - Type Y to indicate if the data you wish to load is strictly general report data. You are loading data into a Db2 table which can be generated from the general report only. No detail report run is necessary. Only header fields can therefore be loaded when selecting this option. No row data is available. You might want to load general report data if you only require audit type information, and do not need to track actual data changes to rows.
    There is no concept of pre- or post-row images using general report data. There is only one row per action indicating such things as who did the action, what the action was (update/delete/insert), on what table the action took place, and so on.
    
    The header fields loaded are those you specify using the "Header=S" field setting. When set to Y, you:
    
    cannot use Audit Mode
    
    must include the header
    
    must not be data only
    
    the Change Flag is ignored
    
    load specifications are relevant, but not for pre- or post-update row images. There are no pre- or post-row images here, so including either will get you all updates. Excluding both will omit all updates.
  Delimited output
  Type a Y (yes), an N (no), or an S (specify options) to indicate you wish to create a load file in delimited format. When creating a delimited output file, the data is written using an external format which means the data must be formatted. If the "Skip formatting (when possible)" option is used in conjunction with this option, formatting is still performed. When using this option, you may customize the column delimiter, string delimiter (or character delimiter), and the field delimiter (the value used to "quote" the column names in the when clause of the load cards). These delimiters can be changed anytime by specifying S for this option. In this case, another panel is displayed for the delimiter settings. When set to Y, you:
  - cannot load General report data
  - cannot specify the Change Flag
  - cannot use Audit Mode
  - cannot use CHAR data in hex
  - must choose Table Creator and Table Name header fields, unless choosing to also split the load files
  Fixed external format
  - Type Y to indicate a fixed external format. This setting creates a fixed column length output file in external format with load cards so the Details Report data can be loaded into your other DBMS.
  - Type N to indicate that there is not a fixed external format.
  Header
  
  Type a Y (yes), an N (no), or an S (specify headers) for header inclusion in the target table. Includes data such as database, time, and so on. If you specify Y all specified headers are included in the target table. If you specify N and Data Only is also N, then two column headers (Row action and status) are included to identify the row. This field cannot be set to Y if Data Only is also set to Y. Setting this field to N with Data Only set to Y means no column headers are included. When set to Y, the headers previously specified will be used. If you have not yet specified your headers, type S at least one time to do so. These specified headers can be changed anytime by specifying S. In this case, another panel is displayed for your header settings. See Customizing headers during LOAD for more information.
  
  Change Flag
  
  Type a Y (yes) or an N (no) to indicate whether you want each column preceded by a change indicator in the target table. The change indicator tells whether or not the column value has changed between the pre-change and post-change row, and is represented by a C in the generated column (LAT_CI_nnnnn) preceding the column if a change occurred. If no change occurred, this column is left blank.
  
  Data Only
  
  Type a Y to indicate that you want to load only the column data into a target table. The LOAD cards are generated such that no Log Analysis Tool information is included in the loaded data. This means that there will be no indication of action types (update/insert/delete) or row status (pre or post row image). This field is mutually exclusive with the Header and Change flag fields. Setting this field to Y is useful if data propagation is the desired result. If set to Y, it is most likely that the LOAD specification field would be set to 1, to include only INSERT type records. See the help for the LOAD specification field for more information.
  Attention: Though not available from the panel, there are extra parameters available to further control the output/format of the load file contents. See "Advanced LOADFILE creation" in this document or the tutorial topic "Load data into Db2" for more information.
  
  Log
  
  This field determines the setting for the LOG parameter in the LOAD utility. Valid values are:
  
  Y
  
  LOG YES. Db2 logs the loaded records and the table space is not placed in COPY PENDING status.
  
  N
  
  LOG NO. No logging is done, and the table space is placed in COPY PENDING status.
  
  Audit Mode
  This field determines if you are running in a special mode called audit mode.
  - Type Y to indicate that you will be running in audit mode.
  - Type N to indicate that you will not be running in audit mode.
  - Type S to indicate that you want to specify the audit mode parameters.
  If you specify S, more panels will be displayed for your audit mode specifications. After you finish your specifications, you will be returned to this panel with the Audit Mode field set to Y, and you should reset this field to either Y (use audit mode) or N (bypass audit mode).
  Important:
  
  If you request audit mode (field set to Y), fields Header, Change Flag, and Data Only are ignored. Your audit mode specifications determine the layout of the audit table. See the tutorial for more information about running in audit mode.
  
  If you request audit mode (field set to Y), and there is LOB/XML data present, only the locator values will be saved, which are of little use. The actual LOB/XML data cannot be audited this way. See the tutorial for more information about LOB/XML data.
  Load Specification
  
  This field indicates which records from the detail report process will be used in the LOAD utility. The letters indicate the records that will be included:
  
  A
  
  - All records are included.
  
  B
  
  - INSERT records (post-change) are included.
  
  C
  
  - DELETE records (pre-change) are included.
  
  D
  
  - UPDATE post-change records are included.
  
  E
  
  - UPDATE pre-change records are included.
  
  Any combination of these record types can be requested. Simply include the letters as needed in the field provided. For example, BC means include INSERTs and DELETEs only.
  
  LOB PDS name
  
  See "LOB PDS name" in Generate details.
  
  Split load files
  
  This field indicates whether the LOAD files will be split into one file for each table or if there will be one LOAD file for all the tables. The LOAD card information will be in one file regardless of whether the LOAD files are split. The DD LOADFILE and DD LOADFDRP specify the LOAD file if there is no splitting and dynamic allocation has been overridden.
  The DD Ldddtttt specifies the LOAD file for each of the tables if the tables are to be split and dynamic allocation has been overridden. The 'ddd' of the DD name is replaced with the last three hexadecimal digits of the table's database ID. The 'tttt' will be replaced with the hexadecimal digits of the table's ID. For example, a table with a DBID of '4A20' and an OBID of '12BA' would be specified with the DD LA2012BA. When dynamic allocation is overridden and the LOAD files are to be split, it is up to the user to provide the DD for all tables requiring a LOAD file. An error occurs if this is not done.
  
  In the case where dynamic allocation is not overridden, the data set for the split LOAD files is renamed using the same Ldddtttt format. For example, if no data set prefix is specified and the job identifier is TEST01, the data set name will be USERID.Ldddtttt.TEST01 for each table.
  Important: When LOAD files are not split, the generated JCL has a step to LOAD the data into the specified tables using the single LOAD file and the LOAD card. When the LOAD files are split, the generated JCL does not attempt to run a LOAD and instead just creates the LOAD card and the various LOAD files.
  
  Split load cards
  
  This field indicates whether the LOAD cards will be split into one file for each table or if there will be one LOAD card file for all the tables. The DD LOADCARD specify the LOAD cards if there is no splitting and dynamic allocation has been overridden.
  The DD Cdddtttt will specify the LOAD file for each of the tables if the tables are to be split and dynamic allocation has been overridden. The ddd of the DD name will be replaced with the last three hexadecimal digits of the table's database ID. The tttt will be replaced with the hexadecimal digits of the table's ID. For example, a table with a DBID of '4A20' and an OBID of '12BA' would be specified with the DD CA2012BA. When dynamic allocation is overridden and the LOAD cards are to be split, it is up to the user to provide the DD for all tables requiring a LOAD card. An error will occur if this is not done.
  
  In the case where dynamic allocation is not overridden, the dataset for the split LOAD cards will be named using the same dddtttt format. For example, if no data set prefix is specified and the job identifier is TEST01, the dataset name will be USERID.Cdddtttt.TEST01 for each table. When LOAD files are not split, the generated JCL will have a step to LOAD the data into the specified tables using the single LOAD file and the LOAD card. You cannot choose to split LOAD cards without choosing to split the LOAD files.
  
  Modify split LOADFILE DSN
  
  Enter Y in this field to modify data set node names for the split load files and the load cards. This option is only valid if Split load files is set to Y. When you press Enter, a panel will display that allows you to modify the data set node names. For more information, see Modifying split LOADFILE data set names.
  
  Edit column order
  
  This field indicates whether the column ordering for one or more tables in the LOAD should be changed. By default, the columns in the LOAD are ordered the same as the ordering of the table that the data represents. A value of Y indicates that columns should be reordered or excluded for at least one of the tables. A panel will display allowing for the reordering to be specified.
  
  Expert options
  
  This field indicates that you want greater control over how the LOAD file is created. These parameters are provided in case the default generation of the LOADFILE does not meet your specific needs. To use this option, expert knowledge of your data is required. A value of Y indicates the 'LOAD expert options' panel should be displayed.
  
  Include load step in JCL
  
  Specify Y or N to indicate whether you want to add the load step (LOADFILE) in the JCL that is generated. The default is N. Depending on other parameters chosen, you may not be able to specify a Y.
  
  Source/target tables
  
  Set this field to Y to specify the source/target table owner and table name. The Source/target tables specification panel is displayed. Enter the values for the fields. Do not specify the duplicate table names. For more information, see Specifying Source/target tables.
  Enter N to skip the specification of any source/target tables. When you don’t specify a source or target table, all the tables reported in the detail report are included. It also means that the target tables are identical to the source tables.
  
  Create target tables
  
  Set this field to Y to create target tables in the target subsystem (SSID). The Create target tables panel is displayed. Enter the target database and tablespace name fields. Make sure the table doesn’t already exist in the target subsystem. For more information, see Creating target tables.
  Enter N if you don’t want to create any target tables.