topic Re: SAS Bulk Load troubles in SAS Programming

SAS Bulk Load troubles

Binks_no_Sake — Sun, 16 Sep 2018 11:50:44 GMT

Hi, i'm trying a new bulkload setup for old jobs after migration whole environment from z/Os to Linux.

The jobs creates work table from from db2 table which are afterwards written in different lib on db2

Old bulkload looked like this:

DATA DW_1.Tdw111

   (BULKLOAD        = YES
     BL_DB2TBLXST    = YES
     BL_DB2LDEXT     = GENONLY
     BL_DB2IN        = "&Outputfiles..Tdw111.TEXT"
     BL_DB2REC       = "&Outputfiles..Tdw111.DATA"
     BL_DB2RECSP     = &DB2RecspTdw111
     BL_DB2DEVT_PERM = &DB2DevtPerm
     BL_DB2UNITCOUNT = 50 );
    SET DW_1.tdw111(OBS=0)
        WORK.tdw111;
RUN;

When i was testing new one, i simplified creation of the work table but still gets the same data as before but i used no extra options, to see if and how it's working:

DATA DW_1.Tdw111

   (BULKLOAD        = YES)
         SET DW_1.tdw111(OBS=0)
        WORK.tdw111;
RUN;

No luck. Had to ask DB2 team to empty the target table as i had no auth to access so afterwards i used the same data step but without "DW_1.tdw111(OBS=0)".
Still no luck and was getting error, that Db2 table is opened for output but isn't supporting REPLACE.

So tried:

proc append base = DW_1.Tdw111
     (BULKLOAD=YES
      DBCOMMIT=100000
      BL_DATAFILE=
"/*random pathway*/BL_TDW111_VNT_LL.IXF"
      BL_LOG=
"/*random pathway*/BL_TDW111.log"
     )
     data = WORK.Tdw111(obs=1000000) force;
RUN;

Bulkload worked, created *.ixf file and log but couldn't write it in the target table with:

ERROR: Bulk loader error: SQL3088N The source column specified to be loaded into database column "END_INTENT" is not compatible
with the database column, but the database column is not nullable.

I can't change the formats of source or target tables, so i tried modify via proc sql create table, with not null but still nothing.

Would you have any suggestions either for the bulkload data step or how to deal with the last error written?
Thank you.

Martin

Re: SAS Bulk Load troubles

ChrisNZ — Mon, 17 Sep 2018 05:18:05 GMT

Not too sure I totally understand the issue, but maybe this would do what you want

DATA DW_1.Tdw111 (BULKLOAD = YES);
  if 0 then set DW_1.tdw111;
  set WORK.TDW111;
 RUN;

rather than

DATA DW_1.Tdw111 (BULKLOAD = YES);
  SET DW_1.tdw111(OBS=0)
      WORK.tdw111;
 RUN;

Re: SAS Bulk Load troubles

Tom — Mon, 17 Sep 2018 06:22:56 GMT

Your first attempt was missing the semi-colon to end the DATA statement. That will cause a lot of trouble.

The error message is saying your work dataset has an incompatible variable. So it would seem that the problem is in the previous step that created the work dataset, not in the step that is loading the data.

Re: SAS Bulk Load troubles

Binks_no_Sake — Tue, 18 Sep 2018 05:11:37 GMT

Hi, thanks for the input, but that piece of code actually works just fine, it takes formats from target table with zero obs and then loads data from work table.

NOTE: There were 0 observations read from the data set DW_1.Tdw111.
NOTE: There were 100000 observations read from the data set WORK.TDW111
NOTE: The data set WORK.TDW111 has 100000 observations and 21 variable
NOTE: Compressing data set WORK.TDW111 decreased size by 26.53 percent
Compressed is 180 pages; un-compressed would require 245 pages.
The SAS System

Re: SAS Bulk Load troubles

Binks_no_Sake — Tue, 18 Sep 2018 05:21:49 GMT

Hi, the missing semicolon was just typo when i wrote this post.

Managed to get pass the errors i had with this statement:

DATA work.Tdw111 ;
  SET DW_1.tdw111(OBS=0)
      WORK.tdw111;
 RUN;

Bulk load have ran with obs=1000,

but when i've raise the limit to 100 000, i've hit another wall.

NOTE: 100000 rows were inserted into DW_1.Tdw111
ERROR: Bulk loader error: SQL3006C An I/O error occurred while opening the messsage file.

Would you have any ideas?

Thanks a lot

Re: SAS Bulk Load troubles

Binks_no_Sake — Tue, 18 Sep 2018 05:34:20 GMT

Well, i've hit the work early so i could lower the possibility of timeouts, but i'm getting the same error even with obs=10