Help With DB2 Load Utility Parameters

[jtrapat1]

I'm trying to run this load against DB2 from a command line:
----------------------------------
db2 "load from APPROPS2001.TXT of asc method L (22 25,1 20,54 58,63 65,59 61,76 76,62 62,84 103,164 183,424 435,460 471,82 82) messages msgs.txt insert into nysa.bp10t_appropriation (BUDGET_YR, APPR_ID, AGENCY_CODE, FUND_TYPE, PROG_CODE, OBJECT_CODE, PURPOSE_CODE, ADJ_FUND_AMT, DOB_REQ_AMT, DOB_REQ_FTE, CUR_FTE, EDIT_USERID)"
--------------------------------------
Is there any way I can specify in my load statement for DB2 to not load the rows where EDIT_USERID is equal to a certain value?
I'd like to reject the rows where EDIT_USERID is equal to 'D'.
Can I do this in the same statement?

Also, when I ran this load the first time, it put db2 in a logging state and wouldn't let anyone make changes to the table until it performed a backup.
Do I have any alternatives to avoid this problem?
Can I specify an exception table where I can put my rejected records so that it doesn't cause this problem for everyone?
- does anyone have any syntax for this?

Thanks In Advance.
John

 

[tamrev]

As far as ur question on DB2 in a logging state, I would say that LOAD utility will lock the entire TABLESPACE it uses and will not allow any access to that.

 

[gregsimpson]

John,

the load can put the table space in which the target table resides in on of 3 pending states or can set the table to a pending state.

If the load fails during the load or build stage the table space will be left in load pending.

If the load fails during the the delete stage(deletes violating rows from unique constraints) the table space will be left in delete pending.

From your description of your problem in your case I believe your table spaces have been put in a BACKUP PENDING state.

This state forces the DBA to take a back up of the database or table space the target table for the load resides in.

This BACKUP PENDING has happened due to your table being loaded with the COPY NO command option and archival logging being switched on.

If you use the COPY YES option you won't get this problem.

You do have a way of capturing rejected rows as long as you are loading from either DEL or ASC filetypes. For this you need to use the DUMPFILE file type modifier.

example

LOAD FROM .... of DEL ... MODIFIED BY DUMPFILE=c:\dumper.del

Below is a load example which will give you a few more parameters to investigate

LOAD FROM employee.del OF DEL
MODIFIED BY COLDEL| CHARDEL*
SAVECOUNT 100
MESSAGES msgs.txt
TEMPFILES PATH d:\tempdb
INSERT INTO Employee FOR EXCEPTION Employee_exception
STATISTICS YES AND INDEXES ALL
COPY NO
INDEXING MODE INCREMENTAL

They are not all mandatory parameters but should point you in the right direction.

AS regards rejecting you rows where EDIT_USERID is equal to 'D'.

There are a few options.

DELETE these rows out after the load.

Perhaps you could try inserting into a view with the view defined as EDIT_USERID not = D. Maybe then these rows would be put to the DUMPFILE, using the option mentioned above. This is just an idea as I'm not sure if you can load into views, I've never tried it.

Alternatively you could look into using a CHECK constraint on this field, not allowing it to be 'D'. This would mean the load will put the table into a CHECK PENDING STATE.

You could then issue something along the lines of

SET INTEGRITY FOR Employee IMMEDIATE CHECKED FOR EXCEPTION IN Employee USE Employee_Exception.

This should generate a SQL3602W message and move the offending rows into the exception table.

Of course the best way would be to remove these records from the file before load and then the EDIT_USERID is equal to 'D' type processing doesn't even come in to play.

I hope this helps.

Cheers
Greg

 

[scottrisi]

To cause to LOAD not to place the table in BACKUP PENDING,
you can use the parameter NONRECOVERABLE instead of COPY YES/NO in the LOAD command. I know this works in version 7 and it may work in version 6 (UNIX).
Version 8 will only lock the table during a LOAD rather than the entire tablespace.