Deferring DDL execution

The purpose of this DDL (Data Definition Language) feature is to provide flexibility to a database administrator (DBA) or IBM® Content Manager administrator. The administrator can defer the execution of data modeling modifications made in the IBM Content Manager library server. These modifications include:
  • Creating, altering, or deleting an item type or component type.
  • Defining, updating, or dropping indexes and text search indexes, foreign keys, and views.

Actions that merely change table data do not cause DDL entries, and cannot be deferred. These actions include adding a row (such as adding an item) or changing a row value (such as changing an attribute definition).

By enabling this feature, the IBM Content Manager administrator will be able to tailor DBA aspects of data modeling operations. For example, the administrator can define the tablespace in which user component tables, indexes, or views should be created.

When the IBM Content Manager administrator chooses to save the DDL entries for later execution, the IBM Content Manager data modeling operation behaves as follows:

  1. The IBM Content Manager administrator creates an item type or component type using the system administration client or the Java™ API.
  2. The definition, or metadata, of the item type and component type is stored in IBM Content Manager system tables.
  3. IBM Content Manager generates three scripts that contain all database operations for that item type or component type. The scripts are named the same as the item type.
    • ItemType script: This is the main script that connects to the database. It invokes two other scripts, one with SQL DDL commands, another with Db2® Text Information Extender or Db2 Net Search Extender commands. This main script has a .bat extension on Windows™ and a .sh extension on Linux® and AIX®.
    • ItemType.DDL: This script contains DDL statements to create the user component tables, corresponding indexes, and views in the database.
    • ItemType_TIE script: This script contains Db2 Text Information Extender and Db2 Net Search Extender drop indexes commands. This script has a .bat extension on Windows and a .sh extension on Linux and AIX.

    ItemType.log: This file is created after the scripts are executed. It contains the output of the database operations.

  4. After executing the scripts, the item type or component type is ready for create, retrieve, update, and delete (CRUD) operations. This feature applies to the IBM Content Manager library server with DB2 UDB on the workstation.

Enabling the deferred DDL execution feature

About this task

The script is located in the IBMCMROOT/config directory and enables or disables the deferred DDL execution feature. By default, this feature is disabled.

AIX

GenerateDDL.sh dbname userid password schema [1 | 0]

Windows

GenerateDDL.bat dbname userid password schema [1 | 0]
dbname
IBM Content Manager library server database name
userid
User ID with Db2 administrator authority for this database
password
Password for the user ID
schema
IBM Content Manager library server database schema
1 or 0
1 enables and 0 disables the feature

Example: GenerateDDL.bat icmnlsdb icmadmin password icmadmin 1.

This feature can be enabled and disabled at any time. That is, after the feature is enabled, all data modeling operations for new and existing item types are saved in scripts. When the feature is disabled, data modeling operations for new and existing item types are applied immediately.

Scripts to create an item type

About this task

An IBM Content Manager administrator executes an IBM Content Manager data modeling operation, such as creating an item type MyBook, either using the system administration client or calling API. When the deferred DDL feature is enabled, the following scripts are created as a result of a data modeling operation:

MyBook.bat
This script executes the following operations:
  1. Connect to the database.
  2. Invoke the Db2 Text Information Extender or Db2 Net Search Extender script to drop the Db2 Text Information Extender or Db2 Net Search Extender indexes.
  3. Invoke the DDL script.
  4. Rename the DDL script to ItemType.DDL.SAVE and delete the DDL script.
  5. Call RebuildCompTypeForDDL for each component that was modified or created.
  6. Invoke Db2 Text Search commands to add/alter text search indexes.
Attention: On AIX, the script has a .sh extension.
MyBook.DDL
Contains the DDL commands. It is invoked by the MyBook.bat or MyBook.sh script.
MyBook_TIE.bat
Contains the text search drop index command. It is invoked by MyBook.bat.
Attention: On AIX, the script has a .sh extension.
RebuildCompTypeForDDL.class
This class file is deployed in the IBMCMROOT/config directory and is called by the item type main script to generate the access modules. The input parameters are passed by the calling script.

Script Directory Location

All scripts created as a result of the DDL deferred execution feature are placed in the following location:
  • On AIX $IBMCMADM_HOME/cmgmt/ls/DBName/UserDDL
  • On Windows $IBMCMROOT/cmgmt/ls/DBName/UserDDL
where
  • dbname is the IBM Content Manager library server database name.
  • UserDDL is the directory created by IBM Content Manager when the deferred DDL execution feature is enabled.

Executing the scripts

The following script example uses a sample item type named MyBook.
Important: Ensure that the path for IBMCMROOT\lib\db2jcc4.jar is in your CLASSPATH.
  1. The script created is:
    • MyBook.bat (on Windows)
    • MyBook.sh (on AIX)

    Usage is: MyBook.bat dbhost dbport dbname userid password schema

    where:
    dbhost
    IBM Content Manager library server database host name.
    dbport
    IBM Content Manager library server database port number.
    dbname
    IBM Content Manager library server database name.
    schema
    IBM Content Manager library server database schema.
  2. The script can be executed after each data modeling operation. For example:
    1. Define an item type.
    2. Run the script for this item type.
    3. The component is now ready for CRUD operations.
  3. Optionally, the IBM Content Manager administrator can execute an n number of data modeling operations. Each data modeling operation is appended to the script and DDL file and all the operations are applied to the database when the script is run. For example:
    1. Define an item type.
    2. Modify the component by adding a new attribute.
    3. Modify the component by setting it to be text searchable.
    4. Add a new index to the component.
    5. Create a new view on the component.
    6. Run the script.
    7. The item type is ready for CRUD (create, retrieve, update, and delete) operations.
  4. After the script is executed, the DDL file is automatically deleted (a copy is saved as script.DDL.SAVE).
  5. When a new data modeling operation is applied on that item type, a new set of script files is created.
Recommendation: After running a script, delete the ItemType and ItemType_TIE scripts.

Examining the logs

After executing the script, a log file is created with the output of the DDL and text search commands. Some SQL DDL drop statements are expected to show errors. For example, the drop command of a VIEW that precedes the CREATE statement of the same VIEW can show an error.

Re-running the script

If you must re-run the script, you must first rename the saved copy of the DDL to its original name. For example, rename ItemType.DDL.SAVE to ItemType.DDL.

Changing library server configurations when DDL deferred execution feature is enabled

About this task

Updating the following library server configuration parameters causes IBM Content Manager to drop and re-create the views. An update also causes IBM Content Manager to regenerate the access modules for all component types defined in IBM Content Manager, including the system-defined components such as document parts.
  • Public Access Enabled
  • ACL binding level
  • Library ACL name

When changing any of these settings with the DDL deferred execution feature enabled, IBM Content Manager generates a script for each item type that is currently defined in the IBM Content Manager database. Scripts are generated for both user-defined and system-defined item types.

Make sure that you execute all the scripts that were generated as a result of changing the library server settings, or CRUD operations will fail. The execution of the scripts does not need to follow any particular order.

Allowing IBM Content Manager administrators without DB2 dbadm privilege to create the definition of data model objects

About this task

The deferred DDL execution feature enables IBM Content Manager users without the Db2 dbadm privilege to create the definition of data modeling objects. This action is possible because you first define the data modeling objects and save these definitions into a file. The actual execution of the scripts requires a user ID with the Db2 dbadm privilege. Follow these steps:

  1. On the server where the library server is installed, go to the IBMCMROOT\bind directory.
  2. Connect to the library server database as a Db2 administrator.
  3. Open a Db2 command prompt.
  4. At the Db2 command prompt, run the following commands, replacing ICMCRLSDBSCHEMA with the library server schema name:
    bind icmplscp.bnd QUALIFIER ICMCRLSDBSCHEMA DYNAMICRULES 
BIND DATETIME ISO BLOCKING ALL 
bind icmplsti.bnd QUALIFIER ICMCRLSDBSCHEMA DYNAMICRULES 
BIND DATETIME ISO BLOCKING ALL
bind icmplsiv.bnd QUALIFIER ICMCRLSDBSCHEMA DYNAMICRULES 
BIND DATETIME ISO BLOCKING ALL
bind icmplscv.bnd QUALIFIER ICMCRLSDBSCHEMA DYNAMICRULES 
BIND DATETIME ISO BLOCKING ALL
bind icmplssc.bnd QUALIFIER ICMCRLSDBSCHEMA DYNAMICRULES 
BIND DATETIME ISO BLOCKING ALL
  5. Ensure that the IBM Content Manager user executing a data modeling operation using the system administration client is a system user.

The user executing the DDL script must have dbadm privilege. This scenario only works when the deferred DDL execution feature is enabled.

When disabling the deferred DDL execution feature, the following sequence of commands must be executed:

  1. On the server where the library server is installed, go to the IBMCMROOT\bind directory.
  2. Connect to the library server database as a Db2 administrator.
  3. Open a Db2 command prompt.
  4. At the Db2 command prompt, run the following commands, replacing ICMCRLSDBSCHEMA with the library server schema name:
    bind icmplscp.bnd QUALIFIER ICMCRLSDBSCHEMA DATETIME ISO 
BLOCKING ALL
bind icmplsti.bnd QUALIFIER ICMCRLSDBSCHEMA DATETIME ISO 
BLOCKING AL
bind icmplsiv.bnd QUALIFIER ICMCRLSDBSCHEMA DATETIME ISO 
BLOCKING ALL
bind icmplscv.bnd QUALIFIER ICMCRLSDBSCHEMA DATETIME ISO 
BLOCKING ALL
bind icmplssc.bnd QUALIFIER ICMCRLSDBSCHEMA DATETIME ISO 
BLOCKING ALL