SEO

Is there way of telling the load utility that if it finds spaces in the
input file, where it expects an integer, to insert a zero?

Currently I am getting an error.

SQL3116W The field value in row "F0-1" and column "46" is missing, but the
target column is not nullable.

TIA

--
Steve

Using default values in the table , and complementing it with
USEDEFAULTS keyword in the load command should get your work done..

"Arun Srinivasan" <arunrocks@gmail.com> wrote in message
news:99f01711-54ae-42cb-9ac0-707e2b9fbdd6@s8g2000prg.googlegroups.com...
> Using default values in the table , and complementing it with
> USEDEFAULTS keyword in the load command should get your work done..



Arun,

Thanks for that but my columns all have defaults

create table poa_ytd (
pot_code varchar(2) default ' ' not null,
pot_type smallint default 0 not null,
pot_number varchar(8) default ' ' not null,
pot_unit varchar(3) default ' ' not null,
pot_payroll_unit varchar(3) default ' ' not null,
pot_badge varchar(5) default ' ' not null,
pot_offdate integer default 0 not null,
pot_offcode varchar(2) default ' ' not null,
pot_oc_sections varchar(12) default ' ' not null,
pot_accused_dob integer default 0 not null,
pot_court_loc smallint default 0 not null,
pot_court_timecode smallint default 0 not null,

and my load script says

load from xx of asc modified by striptblanks
usedefaults
method L(

So I don't think that has worked. I still get

SQL3116W The field value in row "F0-1" and column "46" is missing, but the
target column is not nullable.

I think I may have to write a real program to load the data.

Shame this isn't an oracle database. this would be trivial in sqlldr.

--
Steve

Steve Rainbird wrote:

> Thanks for that but my columns all have defaults
>
> create table poa_ytd (
> pot_code varchar(2) default ' ' not null,
> pot_type smallint default 0 not null,
> pot_number varchar(8) default ' ' not null,
> pot_unit varchar(3) default ' ' not null,
> pot_payroll_unit varchar(3) default ' ' not null,
> pot_badge varchar(5) default ' ' not null,
> pot_offdate integer default 0 not null,
> pot_offcode varchar(2) default ' ' not null,
> pot_oc_sections varchar(12) default ' ' not null,
> pot_accused_dob integer default 0 not null,
> pot_court_loc smallint default 0 not null,
> pot_court_timecode smallint default 0 not null,
>
> and my load script says
>
> load from xx of asc modified by striptblanks
> usedefaults
> method L(
>
> So I don't think that has worked. I still get
>
> SQL3116W The field value in row "F0-1" and column "46" is missing, but the
> target column is not nullable.
>

With USEDEFAULTS you have to make sure that DB2 thinks the data in the
column in your file is a NULL in order to use the default value.

With method L, you can use null indicators in your input file to
indicate that a column is a NULL. This is required if you want a
char/varchar column to have a null (or default) value;

For other data types (i.e., everything other than char/varchar)
the DB2 will interpret the value of the field as a NULL if there
is a null indicator OR if the column data is entirely space characters.