Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Bangalore
    Posts
    4

    Unanswered: Disabling all the constraints for a set of tables starting with a certain pattern

    I have two oracle Stored procedures which we use to disable/enable all the constraints. The disable procedure takes a pattern string as an input, and it disables all the constraints on the tables with the name starting with the input pattern. Then bulk loading of data is done using normal SQL calls(INSERT/UPDATE). Then enable procedure is called with two arguments - one is the pattern(same as the input for disable procedure) and the name of the table where errors would be populated. This procedure enables all the constraints, and if there are any exceptions, it stores these exceptions into the error table given. I am trying to migrate these two stored procedure into DB2.

    I have browsed the forum and found one post which suggests dropping all the constraints and recreating them once the loading is done. But the problem in my situation is that I don't know the list of constraints before hand - The list is prepared at the runtime by querying the system tables for all the constraints(SELECT * FROM SYSCAT.TABCONST WHERE TABNAME LIKE 'X%'). So, if I drop all the constraints, then how can I get the list of those constraints to create again?

    I have found SET INTEGRITY allows disabling constraint checking, but it allows only LOAD. It is not allowing INSERT/UPDATE on those tables in the CHECK PENDING state.

    I understand that there is no straightforward way to disable and enable the constraints here in DB2. Could anyone please point out how I can solve the above mentioned problem some way? Thanks a lot for anyone who takes the time to help me.

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: Disabling all the constraints for a set of tables starting with a certain pattern

    Hi,

    how often and why would you like to disable constraint checking in your databases?

    First of all, if you don't need constraints because all your data is safe, you would be better off discarding them once at for all.

    If you want the database to enforce constraints, simply don't turn them off.

    So much for the dos and don'ts. If you must go ahead anyway, consider recreating constraints from the system catalog. In most cases, this involves recreating possibly unique indexes, which basically is a join between some 3 syscat views if I remember right, in others you should find texts with statements to build up the constraint again. Please be aware in that case that you must recreate constraints using the same function paths and schemata, which probably boils down to recreate them with under the same SQL user.

    Johann
    Originally posted by siraj_java
    I have two oracle Stored procedures which we use to disable/enable all the constraints. The disable procedure takes a pattern string as an input, and it disables all the constraints on the tables with the name starting with the input pattern. Then bulk loading of data is done using normal SQL calls(INSERT/UPDATE). Then enable procedure is called with two arguments - one is the pattern(same as the input for disable procedure) and the name of the table where errors would be populated. This procedure enables all the constraints, and if there are any exceptions, it stores these exceptions into the error table given. I am trying to migrate these two stored procedure into DB2.

    I have browsed the forum and found one post which suggests dropping all the constraints and recreating them once the loading is done. But the problem in my situation is that I don't know the list of constraints before hand - The list is prepared at the runtime by querying the system tables for all the constraints(SELECT * FROM SYSCAT.TABCONST WHERE TABNAME LIKE 'X%'). So, if I drop all the constraints, then how can I get the list of those constraints to create again?

    I have found SET INTEGRITY allows disabling constraint checking, but it allows only LOAD. It is not allowing INSERT/UPDATE on those tables in the CHECK PENDING state.

    I understand that there is no straightforward way to disable and enable the constraints here in DB2. Could anyone please point out how I can solve the above mentioned problem some way? Thanks a lot for anyone who takes the time to help me.

  3. #3
    Join Date
    Dec 2003
    Location
    Bangalore
    Posts
    4

    Re: Disabling all the constraints for a set of tables starting with a certain pattern


    how often and why would you like to disable constraint checking in your databases?
    We are using the disable/enable procedures during bulk loading of data, which is a nightly batch. All constraints are turned off, then data is bulk loaded, and then constraints are turned on again, and the errors are logged into error table.


    First of all, if you don't need constraints because all your data is safe, you would be better off discarding them once at for all.
    If you want the database to enforce constraints, simply don't turn them off.
    We need the constraints anyway, after the data is inserted. Only during the time bulk load is happening, we want to turn off to avoid foreign key violations(data may be loaded in child first and then parent order), and for better performance.


    So much for the dos and don'ts. If you must go ahead anyway, consider recreating constraints from the system catalog. In most cases, this involves recreating possibly unique indexes, which basically is a join between some 3 syscat views if I remember right, in others you should find texts with statements to build up the constraint again. Please be aware in that case that you must recreate constraints using the same function paths and schemata, which probably boils down to recreate them with under the same SQL user.
    Are you saying that the constraints' list can be obtained even after all the constraints have been removed? If yes, please tell me how it can be done. I am not able to find this information easily.

  4. #4
    Join Date
    Apr 2003
    Posts
    191

    Re: Disabling all the constraints for a set of tables starting with a certain pattern

    Hi,

    better performance at the cost of less data security is a trade off and must be compared with the time it takes to recreate constraints. Loading child tables before parents is unnecessary. You can derive the parent/child hierarchy from SYSCAT.REFERENCES and load your data accordingly.

    If you delete constraints, their definition cannot be obtained from the system catalog any more, you need to do this before the deletion. You may want to check SYSCAT.KEYCOLUSE for this task, and join this to SYSCAT.REFERENCES to be able to reproduce the foreign key constraints.

    The constraint disable/enable feature may be a nice thing with Oracle, with DB2 you are better off if you replace your import scripts with db2 load commands, e.g. into a stageing table, and do batch inserts and updates from there.

    Johann
    Originally posted by siraj_java
    We are using the disable/enable procedures during bulk loading of data, which is a nightly batch. All constraints are turned off, then data is bulk loaded, and then constraints are turned on again, and the errors are logged into error table.


    We need the constraints anyway, after the data is inserted. Only during the time bulk load is happening, we want to turn off to avoid foreign key violations(data may be loaded in child first and then parent order), and for better performance.


    Are you saying that the constraints' list can be obtained even after all the constraints have been removed? If yes, please tell me how it can be done. I am not able to find this information easily.

  5. #5
    Join Date
    Dec 2003
    Location
    Bangalore
    Posts
    4
    Thanks. I was also considering LOAD utility. Thinking of restructuring our codebase to accommodate LOAD...Thanks again for your kind time...

Posting Permissions

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