Using IGNOREFIELDS with the VALUE option to convert data

This topic contains examples that illustrate several uses of the IGNOREFIELDS option. Alternatively, you can use the SKIPFIELDS option to achieve the same results.

In the following examples, you tell BMC AMI Load to ignore a field in the input data by giving the field a name that is not a column of the table. In these examples, the name is TEMP01. BMC AMI Load uses that field in a table column value clause, along with SQL functions, to convert the field to the desired value for input in the load.

Important

For the assumptions made in this example, see Assumptions made in the syntax examples.

TIMESTAMP to CHAR

This example tells BMC AMI Load to ignore the TIMESTAMP field, convert the data to character data, and load it into a CHAR column. COL01 is defined in the table as CHAR(40).

//SYSREC    DD *
2000-07-31-10.30.15.610208
//SYSIN     DD *
  LOAD DATA INDDN(SYSREC)
  RESUME YES
  INTO TABLE NGT.TABLE
  IGNOREFIELDS YES
  (TEMP01 POSITION(1:28) CHAR,
   COL01 VALUE(CHAR(DATE(TIMESTAMP(TEMP01))))
  )

SUBSTR

This example demonstrates the use of SUBSTR.

//SYSREC    DD *
1234567
//SYSIN     DD *
LOAD DATA INDDN(SYSREC)
RESUME YES
INTO TABLE NGT.TABLE
IGNOREFIELDS YES
(TEMP01 POSITION(1:5) CHAR,
COL01 VALUE(SUBSTR(TEMP01,1,4))
)

TIMESTAMP to TIME

This example tells BMC AMI Load to ignore the TIMESTAMP field and load the data into a TIME column. COL02 is defined in the table as TIME.

//SYSREC    DD *
12009-08-03-09.02.15.610208
//SYSIN     DD *
  LOAD DATA INDDN(SYSREC)
  RESUME YES
  INTO TABLE NGT.TABLE
  IGNOREFIELDS YES
  (COL01 POSITION(1:1) CHAR,
   TEMP02 POSITION(2:29) CHAR,
   COL02 VALUE(TIME(TIMESTAMP(TEMP02)))
  )

TIMESTAMP to INTEGER

This example tells BMC AMI Load to ignore the TIMESTAMP field, convert the data to numeric data, and load it into an INTEGER column. COL02 is defined in the table as INTEGER.

//SYSREC    DD *
12009-08-03-09.04.17.610208
//SYSIN     DD *
LOAD DATA INDDN(SYSREC)
RESUME YES
INTO TABLE NGT.TABLE
IGNOREFIELDS YES
(COL01 POSITION(1:1) CHAR,
  TEMP02 POSITION(2:29) CHAR,
  COL02 VALUE(DAYS(TIMESTAMP(TEMP02)))
)

FLOAT to REAL

This example tells BMC AMI Load to ignore the FLOAT field and load the data into a REAL field. COL02 is defined in the table as REAL.

//SYSREC    DD *
+0.234000038E+01
//SYSIN     DD *
LOAD DATA INDDN(SYSREC)
RESUME YES
INTO TABLE NGT.TABLE
IGNOREFIELDS YES
(COL01 POSITION(2:2) CHAR,
  TEMP02 POSITION(1:17) CHAR,
  COL02 VALUE(REAL(TEMP02))
)

FLOAT to DECIMAL

This example tells BMC AMI Load to ignore the FLOAT field, convert the data to decimal format, and load it into a DECIMAL field. COL02 is defined in the table as DECIMAL(7,4).

//SYSREC    DD *
+0.345600038E+01
//SYSIN     DD *
LOAD DATA INDDN(SYSREC)
RESUME YES
INTO TABLE NGT.TABLE
IGNOREFIELDS YES
(COL01 POSITION(2:2) CHAR,
  TEMP02 POSITION(1:17) CHAR,
  COL02 VALUE(FLOAT(TEMP02))
)

FLOAT to DECIMAL to CHAR

This example tells BMC AMI Load to ignore the FLOAT field, convert the data first to DECIMAL(7,4) then to character data, and load it into a CHAR column. COL01 is defined in the table as CHAR.

//SYSREC    DD *
+0.345600038E+01
//SYSIN     DD *
LOAD DATA INDDN(SYSREC)
RESUME YES
INTO TABLE NGT.TABLE
IGNOREFIELDS YES
(
  TEMP02 POSITION(1:17) CHAR,
  COL01 VALUE(CHAR(DECIMAL(FLOAT(TEMP02),7,4)))
)