Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: problem in migration from ORACLE to DB2

    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 !

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    to enable\disable constraints while insert\loading you can use set integrity command.

    Rahul Singh

  4. #4
    Join Date
    Aug 2006
    Posts
    3
    thanks for reply!


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

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by beforeafter
    can i use sql to do this operation?
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Aug 2006
    Posts
    3

    Smile

    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 ?

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •