question on embedding literals in load statements

[Ken]

Is there any way that a literal can be used for a field value in the
load statement?

The context is fixed-length record formats, db2 8.1 ese, aix 5.1. I'm
performing warehouse and mart loads within korn shell and python - and
everything's been working great - but now I want to add a batch
identifier to all the rows in each load. This identifier is
incremented for each load, but every row in a load file gets the same
value.

Right now the load process is generating a new copy of the data file
with an embedded batch_id prior to the actual db2 load. The problem
is speed and complexity - I'd rather not modify data files in the load
process. Since my extract, transform, and load processes are
completely independent it isn't practical to do this earlier in the
dataflow.

Any suggestions? Have I hopefully missed something?

Thanks in advance,

Ken

[Paul Vernon]

"Ken" <ke******@yahoo.com> wrote in message
news:ea*************************@posting.google.co m...

Is there any way that a literal can be used for a field value in the
load statement?
No
The context is fixed-length record formats, db2 8.1 ese, aix 5.1. I'm
performing warehouse and mart loads within korn shell and python - and
everything's been working great - but now I want to add a batch
identifier to all the rows in each load. This identifier is
incremented for each load, but every row in a load file gets the same
value.

However a hack does spring to mind.

create table load(.. your cols ...
, batch_id integer generated always as identity
(start with 1 maxvalue 1 minvalue 1 cycle no order)
)

then between each run, do this

alter table load alter column batch_id
restart with X set maxvalue X set minvalue X

where X is your new batch_id.
I've not tried the above myself, so it might mess up LOAD performance, but
I'd be interested to know.

The other option would be to load into a separate table for each batch, then
us a UNION ALL view to tie them together with an extra column identifying
the batch.

Regards
Paul Vernon
Business Intelligence, IBM Global Services