JR35566: WANT TO ADD 'EXTERNAL' FLAG TO NUMERIC COLUMN DEFINITION (OR LEA VE BLANK) SO NUMERIC VALUES IN FIFO FILE ARE STRING TYPE

APAR status

• Closed as fixed if next.

Error description

• Customer is using the Oracle Enterprise stage to load data into
  Oracle. They are using a generic job to load data using a
  schema file and the 'Load' option that will use the sqlldr
  APPEND option.
  
  In the FIFO file that is created, numeric data types are
  stored in binary and are therefore not human readable.
  Depending upon which PX Oracle EE patch is installed, numeric
  data types are flagged as being 'PACKED DECIMAL' in the control
  file.
  
  Is there a way to add the flag 'EXTERNAL' to the numeric column
  definition (or leave it blank) so that the numeric values in
  the FIFO file are string type? Basically, the customer wants
  to process the resulting BAD file with DataStage (or pass in
  back to the user), and if the numeric type columns are in
  binary, it is difficult for the end-use to read. The BAD file
  contains records in the format of the input file into the sql
  loader.
  

Local fix

Problem summary

• ****************************************************************
  USERS AFFECTED:
  Users of Oracle EE Operator loading with the SQL*Loader Oracle
  utility
  ****************************************************************
  PROBLEM DESCRIPTION:
  The load delimited option that was available with release 7.5.n
  is no longer supported in the latest release. However, the PX
  Oracle EE documentation still shows the
  APT_ORACLE_LOAD_DELIMITED environment variable.  The PX
  documentation needs to be updated to reflect this change
  ****************************************************************
  RECOMMENDATION:
  Reading the BAD file with DataStage is not supported. This file
  is actually generated by the SQL*Loader Oracle utility and is
  designed to be read by SQL*Loader.  The customer can copy all
  records to a file and then in a second job, compare all the
  records to the records that were successfully loaded using an
  Oracle lookup stage. This is a possible work-around to the
  feature they're looking for.  Another work-around would be to
  use Oracle EE upsert mode.
  
  ****************************************************************
  

Problem conclusion

Temporary fix

Comments

• We need a technote to indicate that APT_ORACLE_LOAD_DELIMITED
  environment variable is no longer supported.
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels

• R810 PSY

     UP