I'm doing a project migrate from ORACLE to DB2 .

in original code in ORACLE ,use following steps to insert data :
1.disable constraints(Primary key)
2.insert data into Table1
3.enable constraints(Primary key) EXCEPTIONS INTO Tlog (log table)
4.if dup_val_on_PK,process data in Table1 ,according records in Tlog
then enable constraints(PK)

but i don't know how to realize it in DB2 !
help me !

If this is DB2 on z/OS (mainframe), have a look at the CHECK DATA utility; there you can specify an exceptions table, just like in Oracle, by using the FOR EXCEPTIONS option. See http://publibz.boulder.ibm.com/cgi-b...20040209165609 for the v8 documentation on CHECK DATA.

to enable\disable constraints while insert\loading you can use set integrity command.

Rahul Singh

thanks for reply!


rahul_s80 & Peter.Vanroose:
can i use sql to do this operation?
I want to use in stored procedure?

Yes and no: utilities operate on tablespaces directly and therefore cannot be specified with SQL syntax which is only aware of tables (and views).

But there is a stored proc provided by IBM (I don't recall its name by heart) which allows calling utilities.

thanks !

in fact
in oracle,we do this operation copying data from table to table .

1.to save time for insert & update .
2.to ensure most of the data insert & update if there's some error data .

Do you have some suggestions ?

For what platform do you need suggestions?
I can only help you with z/OS details.

From the docs:

DSNUTILS and DSNUTILU

DSNUTILS is a DB2-supplied stored procedure for executing utilities from a local or remote application via an SQL CALL statement. The client application calls in DSNUTILS with appropriate parameters. DSNUTILS then analyzes them to create a SYSIN stream and allocate all necessary data sets. After the data sets are allocated, DSNUTILS calls DSNUTILB, which then executes the appropriate utility. The utility statements are then processed, and DSNUTILS retrieves the data (execution results) in the SYSPRINT file, puts the data in the SYSIBM.SYSPRINT temporary table, and then opens a cursor on the table and passes control back to the client application. The client application then fetches all rows from the result set.

DB2 for z/OS Version 8 also includes the new DSNUTILU stored procedure which provides the same functions as DSNUTILS, but allows the control cards to be specified in Unicode (UTF-8).

hi there,
i am not having much experience, but still .....
there is a SP sysproc.admin_cmd through which u can export, import, load data in the tables. But i suppose its available in db2 LUW Version 9.

db2 call sysproc.admin_cmd('export to c:\sales.del of del messages c:\export_sales.msg select * from sales')

db2 call sysproc.admin_cmd('import from c:\sales.del of del messages
c:\import_sales.msg insert into new_sales')

all exceptions will be stored in message files.

i think what u want to acheive can be easily acheived using set integrity command. but i havnt used it before and cant tell u how to use(u can even use exception tables there for all those insets that failed)

http://publib.boulder.ibm.com/infoce...n/r0000998.htm

Rahul Singh