You cannot mix other Database Services Utility commands or SQL statements within the DATALOAD command and its subcommands. The input data file for this utility is a general-use programming interface. See "Programming Interface Information" for a definition of general-use programming interfaces.
| Authorization |
|---|
|
DB2 Server for VSE & VM authorization checking prevents you from loading a table if you do not have proper authority. You must have INSERT and SELECT privileges on the tables affected by the DATALOAD command. |
Use the following procedure as a standard method of constructing and implementing the DATALOAD command. Variations on this procedure appear throughout this chapter.
Assume that you have a separate (input) control file and data file. Your sequential access method (SAM) data file exists already, but you want to issue a DATALOAD command to insert rows into a certain table.
Proceed as follows for DB2 Server for VSE:
DATALOAD TABLE (table-name)
where table-name is the name of the table that you want to load with data.
column-name startpos-endpos data-type
where column-name is the name of the table column, startpos is the first character position in the input record, endpos is the last position in the input record, and data-type is the data format of the input values. The default data type is character (CHAR).
INFILE (ddname)
where ddname identifies the input file. Use the same ddname in a TLBL or DLBL statement, depending on whether the data is stored on tape or in a DASD file.
Proceed as follows for DB2 Server for VM:
DATALOAD TABLE (table-name)
where table-name is the name of the table.
column-name startpos-endpos data-type
where column-name is the name of the table column, startpos is the first character position in the input record, endpos is the last position in the input record, and data-type is the data format of the input values. The default data type is character (CHAR).
INFILE (ddname)
where ddname identifies the input file.
You can also insert rows into a table by using your workstation as a control file. To do so, follow the standard procedure given in (Input) Control File and Separate Data File for constructing a DATALOAD command, but enter the information in the following order:
The data to load need not be in a separate file; you can include it with the Database Services Utility commands in the (input) control file. Figure 18 shows a DATALOAD command with data following the INFILE subcommand.
Figure 18. DATALOAD Command with Embedded Data
DATALOAD TABLE (ACTIVITY)
ACTNO 1-3
ACTKWD 5-10
ACTDESC 12-31
INFILE (*)
190 MARKET MARKETING
200 CUSTOM CUSTOMER SUPPORT
25 RSRCH RESEARCH
55 TRAIN TRAINING
ENDDATA
|
The asterisk parameter (*) of the INFILE subcommand indicates that input data immediately follows. When such input data is included with a DATALOAD command, mark the end of it with an ENDDATA subcommand.
The ENDDATA subcommand is valid only when the previous Database Services Utility command statement processed is INFILE (*).
| Match TCI and Data Positions |
|---|
|
Enter records following an INFILE(*) subcommand so that the character positions of the data correspond to the startpos and endpos parameter specifications of the applicable TCI subcommand. The following example shows that data inserted in the ACTNO column is specified as occupying positions 1 through 3 of the input data records. Because INFILE begins at position 1, you can align position 1 of each data record under the I of INFILE. The INFILE does not have to begin in position 1, but because it does in this example, you can use it as a guide to position your data records.
*-----------* DATALOAD TABLE (ACTIVITY) *-----------------------*
| TCI | | ACTNO 1-3 | | STARTPOS and ENDPOS |
|Subcommands*----->| ACTKWD 5-10 |<----* parameters show where |
*-----------* | ACTDESC 12-31 | | ACTIVITY data is |
INFILE (*) | located in the input |
^ | records. |
| *-----------------------*
**------------------------------*
|This character marks position 1|
|of the following input records:|
**------------------------------*
|
V
190 MARKET MARKETING
200 CUSTOM CUSTOMER SUPPORT
25 RSRCH RESEARCH
55 TRAIN TRAINING .------------------------------.
ENDDATA <-----------*The ENDDATA subcommand follows|
|the last embedded data record.|
'------------------------------'
Use a column scale or ruler when entering DB2 Server for VM embedded data to align data fields. |
To load embedded data, follow the standard procedure in (Input) Control File and Separate Data File, but construct the INFILE subcommand as follows:
INFILE (*)
where (*) indicates that data follows immediately.
ENDDATA
You can store data in sequential files in different data formats. A file created using a file editor is usually stored in CHARACTER data format. When a file is produced by a program, it is possible for the program to store data in one or more of the following data formats: DECIMAL, FIXED, FLOAT, ZONED, CHARACTER, DATE, TIME, TIMESTAMP, or GRAPHIC data formats.
The Database Services Utility supports data stored in any of the previously mentioned data formats. When loading data, the utility automatically converts the input data to the data type of the particular column of the target table.
Sometimes, rather than using character data, an application program generates fixed-point binary, floating-point binary, or packed decimal data. You still use the utility to load the data into a table, but you need to specify that the input data is no longer CHAR data type. The TCI subcommand has the optional data type parameter for this purpose. The following example illustrates the use of the TCI subcommand's data type parameter FIXED, which indicates that the input data type is fixed-point binary.
DATALOAD TABLE (ACTIVITY)
ACTNO 1-3 FIXED
ACTKWD 4-9
ACTDESC 10-29
INFILE (NEWACT PDEV(DASD) BLKSZ) <------DB2 Server for VSE
INFILE (NEWACT) <------DB2 Server for VM
DATALOAD converts the fixed-point binary data in columns 1-3 to SMALLINT data type in the table because the corresponding column is defined as SMALLINT. The data you are loading can be only one or 2 bytes; DATALOAD cannot convert 4-byte fixed data to SMALLINT. If you have 4-byte data, the table column you load has to be defined as INTEGER.
For further qualifying information, see DATALOAD Data Conversion Summary.
When you use a separate data input file with the DATALOAD command, you need to define that file through JCL statements. Use the information in this section when you construct a job for a Database Services Utility command that requires a data definition name (ddname). See Figure 19 for an example of JCL statements that define an input data file.
Figure 19. Example of JCL Statements to Define an Input Data File
// JOB DBS Utility Dataload Example
// EXEC PROC=ARIS71PL
// DLBL NEWACT, 'ACT.DATA',0
// EXTENT SYS006,SQLWK1,1,0,57,76
// ASSGN SYS006,150
// EXEC PGM=ARIDBS,SIZE=AUTO
DATALOAD TABLE (ACTIVITY)
ACTNO 1-3 FIXED
ACTKWD 4-9
ACTDESC 10-29
INFILE (NEWACT PDEV(DASD) BLKSZ)
/*
/&
|
The DATALOAD command uses the ddname NEWACT, which refers to the input data file ACT.DATA in the DLBL statement.
When you use a separate data input file with the DATALOAD command, you need to define that file with a FILEDEF statement. Even if you want to type in a few rows of data from your terminal, you must use the FILEDEF statement to specify that the input is coming from your terminal. If the data is in a virtual reader file, you can use the FILEDEF to specify the spooled reader. The only situation where you do not need a supplementary FILEDEF statement is if the input data is in the control file.
Use the information in the following section when you construct the FILEDEF statement for the input data file.
In the CMS FILEDEF command that defines the Database Services Utility's DATALOAD command input data file, all record format specifications are supported except for carriage-control characters and undefined format. (Do not use A, M, or U in your RECFM specification.)
If you define CMS input files with variable-length spanned records (RECFM=VS or VBS), you must use the file mode number 4. For example:
FILEDEF ddname DISK filename filetype A4 (options
|When you are running DATALOAD under CMS 14 or earlier, and the |DATALOAD input data file contains records with more than 32,760 positions of |data, you must use variable-length, spanned records (RECFM=VS or VBS). |Specify as options only the RECFM and block size (BLOCK or BLKSIZE) parameters |in the FILEDEF command defining the data file. (The LRECL specification |does not apply and would be overridden if specified.) For spanned |record segments, use a maximum length of BLKSIZE-4.
|When you are running DATALOAD under CMS 15 or later, and the |DATALOAD input data file contains records with more than 32,760 and less than |65,536 positions of data, you can use fixed length, unblocked records (RECFM |F).
A sample FILEDEF command defining a CMS file for DATALOAD command processing is:
FILEDEF DBSFILE DISK DBSFILE DATA A (RECFM F LRECL 80
where DBSFILE is the name of the data input file as it is referred to in your program. For more information on FILEDEF parameters and options, see Appendix B, "FILEDEF Command Syntax and Notes".
| Use the Same File Definition for DATALOAD as for DATAUNLOAD |
|---|
|
If the input data file was created by DATAUNLOAD processing, then the CMS FILEDEF command that defines the DATALOAD input data file should be identical to the information in the FILEDEF command used when the file was created by DATAUNLOAD processing. |