Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

In SAS 9.1, using bulkload=yes to insert new rows into an already
existing DB2 table may result in either unexpected results or an S0C4
abend.

Unexpected results may occur when the input data set has the same number
of columns as the existing DB2 table but the order of the columns are
different.  If the data types of the columns in the same position are
compatible, wrong data will be loaded for the columns that are in the
wrong position.  The data is added in the order that the columns appear
in the SAS data set rather than by matching variable names.  If the data
types of the columns are different, an error will occur.

So if the DB2 table had 3 numeric variables called COL1, COL2, COL3 in
that order and the SAS data set had the same 3 numeric variables but
instead in the order COL3, COL2, COL1 the wrong values would be inserted
into COL1 and COL3.  COL1 would get the values from the variable COL3
while COL3 would get it's values from the variable COL1.  COL2 would be
fine since in both it's the second variable.

If the SAS data set has less columns than the DB2 table SAS will fail
with an S0C4 abend.  The circumvention for any table except one that
contains an identity column or one where the DB2 column is defined as
NOT NULL or NOT NULL WITH DEFAULT is to define the same number of
columns in the SAS data set as what exists in the DB2 table.  For the
tables with identity columns or NOT NULL columns, the bulkload can not
be used.  Another approach must be used to insert the rows.  Remember
that the order of the columns need to match to avoid the first issue.

If the SAS data set has more columns than the DB2 table, SAS will also
terminate with an S0C4 abend.  The circumvention is to use a DROP= or
KEEP= data set option for the SAS data set only keeping those columns
that are needed.  Remember that the order of the columns need to match
to avoid the first problem.


A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

http://www.sas.com/techsup/download/hotfix/e9_sbcs_prod_list.html#011460

For customers running SAS with Asian Language Support (DBCS), this
fix should be downloaded from:

http://www.sas.com/techsup/download/hotfix/e9_dbcs_prod_list.html#011460


A Technical Support hot fix for SAS 9.1 (TS1M0) for this
issue is available at:

http://www.sas.com/techsup/download/hotfix/b9_sbcs_prod_list.html#011460


After applying the hotfix, the circumvention for the 1st issue and for
the 2nd issue (except in the case of an identity column or where a field
is defined as not null) are no longer needed.  However, if your SAS data
set has more variables than the DB2 table, you will still need to use
the DROP= or KEEP= data set options. Otherwise the following errors,
notes, and warning will occur:

ERROR: The number of columns of the input dataset exceeds that of the
       target table. Drop the column/s in excess before running the job
       again.
NOTE: The data step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set DB2LIB.TEST may be incomplete.  When this step was
         stopped there were 0 observations and 4 variables.
ERROR: Error prior to running the load utility. See previous errors
       messages for the step.

Operating System and Release Information

A fix for SAS 9.1 (9.1 TS1M0) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at :