Skip to content
CA Service Management - 14.1
Documentation powered by DocOps

How to Modify Schema Using Web Screen Painter

Last update January 21, 2016

Use the Schema Designer of Web Screen Painter to modify the database schema of CA SDM. Schema Designer provides a graphical user interface to review and modify this schema. The following diagram shows how to modify the schema using Web Screen Painter:

Diagram depicting how to customize schema using WSP

Follow these steps:

  1. Considerations" class="conf-macro output-inline" data-hasbody="true" data-macro-name="sp-plaintextbody-link">Verify the Web Screen Painter Considerations.
  2. Open Schema Designer on WSP.
  3. Add a Table or Add a Column or Modify the Table or Column.
  4. Test Schema Modifications.
  5. (If necessary) Modify Site-Defined Columns After Publishing.

Verify the Web Screen Painter Considerations

Consider the following information before using Web Screen Painter:

  • You cannot use WSP to change the length of an existing column, and we strongly recommend that you do not use other tools to do so. Changes to the length of an existing column are not supported, and may cause other applications accessing the CA SDM database to fail.

    Important! Do not shorten a field or delete an existing field, because these actions could cause CA SDM to fail.
  • Be careful when adding columns to an existing table, because you can inadvertently exceed the record length capacity of the underlying database. Check the specifications for the database that you are using with CA SDM and make modifications within the limits of that database.
  • Publishing changes to the database schema could require limited or considerable downtime, depending on the changes you make and the capabilities of your underlying database.
  • If you are a new user of CA SDM, it is easier to make all of your changes during testing instead of waiting until you are in production.
  • Review general procedures you must complete before and after changing the database schema.
  • Use specific procedures to modify your schema. Most of these procedures are followed by an example of a change you might want to make to the standard database schema.

Open Schema Designer on WSP

To start working on the Schema Designer, ensure that you have WSP installed on the CA SDM server. For more information about the WSP installation, see Install Web Screen Painter.

Follow these steps:

  1. Log in to the following CA SDM server where WSP is installed, depending on your CA SDM configuration:
    • Conventional: Primary server
    • Advanced availability: Background server
  2. Start WSP using any of the following actions, depending on the operating system that is installed on the CA SDM server:
    • (Windows) From the Start menu, select Program Files, CA, CA SDM, Web Screen Painter.
    • (UNIX) Enter the command pdm_wsp with $NX_ROOT/bin in your path.
    The Web Screen Painter login window opens.
  3. Enter your login credentials.
  4. Select Tools, Schema Designer.
    The Schema Designer window opens. The left side of the Schema Designer window shows the CA SDM database in a tree format. The tables and columns are displayed by their Object Name. If the Display Name differs from the Object Name of the table or column, the Display Name is displayed in parentheses along with the Object Name.

Add a Table

Use the Schema Designer to add a table in the database.

Follow these steps:

  1. Select Edit, Add Table.
    The Add New Table dialog opens.
  2. Enter the table name in the New Table Name field and click OK. Ensure that you begin the name of a site-defined table with the letter z to prevent conflict with possible future standard tables.
    WSP adds a z to the beginning of the table name if you do not add.
  3. Complete the following fields, as appropriate:
    • Name
      (Read-only) Specifies the object name of the table. For example, the object name of the cr table is cr.
    • Display Name
      Specifies the user-friendly name of the table. For example, the Display Name of the cr table is Request. You can change the Display Name of a table by entering a new name in this field.
    • Schema Name
      (Read-only for standard tables) Specifies the name used to refer to the table in CA SDM utilities, such as pdm_userload. For site-defined tables, Schema Name defaults to the Object Name. You can change the Schema Name by entering a new value in this field.
    • DBMS Name
      Specifies the name used to refer to the table in the physical DBMS. This field is read-only for all tables. For site-defined tables, it is always the same as Schema Name.
    • Default Display Field (common name)
      Specifies the column displayed on the UI for a field that references this table. For example, the assignee field of a request is a reference to the Contact table. Because the common name of the Contact table is combo_name (last, first middle), the combo name of the referenced contact displays as assignee. You cannot change the value of common name.
    • Foreign Key Field (rel attr)
      Specifies the column stored in the database for a field that references this table. For example, the assignee field of a request is a reference to the Contact table. Because the rel attr of the Contact table is id, the assignee column in a Request contains the id of the referenced contact. You cannot change the value of rel attr.
    • Function Group
      Specifies the name of the group that controls the level of access that users have to records in this table. Each access type of a contact specifies whether they have read, modify, or no access to data in tables in each function group. You can change the value of rel attr by selecting a new value from the drop-down list.
    Important! The Schema Designer includes an Advanced tab. Information on this tab is intended for CA Technologies Support and field representatives. You will not need to work with this tab for most uses of the Schema Designer, and it will not be discussed further in this document.

    The Advanced tab of the Schema Designer column dialog shows information meaningful only to persons with internals knowledge of CA SDM. Its contents vary with the column type. We recommend that you modify the values in this tab only on direction from a CA Technologies employee. It contains the following fields if a table is selected:

    • Active Triggers
      Enter the triggers currently active in the Object Engine for the column. A trigger is a small program that runs under the direction of the Object Engine at certain times when a column is modified. The active trigger list includes both standard and site-defined triggers (so that site-defined triggers are listed both in the active list and in the site-defined list).
    • Site-Defined Triggers
      Enter the triggers that have been installed at your site. For example, these may be triggers that have been activated as a result of installing a CA SDM option, or triggers that have been written for your site by CA Technical Services. The active trigger list includes both standard and site-defined triggers (so that site-defined triggers are listed both in the active list and in the site-defined list).
    • LRel Specification
      Enter the specification of a many-to-many relationship between two tables, in the form:
      table1 column1 <> table2 column2
      This shows that the LREL is a many-to-many relationship between table1 and table2, with virtual column column1 containing the relationship in table1, and virtual column column2 containing the relationship in table2.
    • Active xRel Query Information
      Enter the active specification of the query that defines a BREL or QREL virtual column. The specification is in Object Engine Majic format.
    • Site-Defined xRel Query Information
      Modify the query associated with a BREL or QREL. It is recommended that you enter data into this field only under the direction of a CA employee.
    • Active Derived Expression
      Enter the active specification of the expression that the Object Engine uses to construct the value of a DERIVED virtual column.
    • Site-Defined xRel Query Information
      Modify the expression associated with a DERIVED column. It is recommended that you enter data into this field only under the direction of a CA employee.

    The Domsets tab of the Schema Designer Table dialog contains the following:

    • MLIST_DYNAMIC
      A dynamic domset with no WHERE clause.
    • MLIST_STATIC
      A static domset with no WHERE clause.
    • RLIST_DYNAMIC
      A dynamic domset with a WHERE clause defined in the STANDARD_LISTS section of the FACTORY statement.
    • RLIST_STATIC
      A static domset with a WHERE clause defined in the STANDARD_LISTS section of the FACTORY statement.

    If you double-click one of the Domsets, the Properties dialog opens and contains the following fields:

    • domset_name
    • fetch_columns
    • max_fetch
    • sort columns (This is the only editable field)
    • volatility
    • where
  4. Do one of the following to save the table:
    • If you are working on the test system, select File, Save.
    • If you are working on the production system, select File, Save and set to Test Mode.
      This selection saves your changes in the database, and creates a file (wsptest.mods) on the server defining your changes to the Object Engine. This file is stored in the site/mods/majic subdirectory of your CA SDM installation directory. After creating the wsptest.mods file, WSP causes its Object Engine to recycle so that it will use the new changes. This may take from a few seconds to a couple of minutes, depending on the complexity of your schema.
      A message is prompted. Click Yes to continue. The wsptest.mods file affects only the Object Engine designated by the wsp_domsrvr option. Other Object Engines on the same server do not process this file, and the file is not distributed to other servers. In addition, new tables and columns in Test mode are defined to the Object Engine as local objects. This means that the Object Engine knows about them and you can use them on web forms. However, they do not exist in the database, and do not affect other users. Typical CA SDM users do not use WSP Object Engine, so they are unaffected by the schema modifications you are testing.
    The table is added.

Add a Column

Use the Schema Designer to add a column in the database.

Follow these steps:

  1. Select the table for which you want to add a column (or select any of its existing columns).
  2. Select Edit, Add Column.
    The Add New Column dialog opens.
  3. Enter the column name in the New Column Name field and click OK. Ensure that you begin the names of a column with the letter z to prevent conflict with possible future standard columns.
    WSP verifies that you added the prefix, but adds a z to the beginning of the column name if necessary.
  4. Complete the following fields as appropriate:
    • Name
      (Display-only) Specifies the object name of the column. For example, the object name of the Contact alt_phone column is alt_phone.
    • Display Name
      Specifies the user-friendly name of the column. You can change the Display Name of a column by entering another name in this field. For example, the display name of the Contact alt_phone column is alternative phone.
    • Schema Name
      (Read-only for standard tables) Specifies the name used to refer to the column in CA SDM utilities, such as pdm_userload. For site-defined tables, Schema Name defaults to the Object Name. You can change the Schema Name by entering another value in this field.
    • DBMS Name
      (Read-only for all tables) Specifies the name used to refer to the table in the physical DBMS. For site-defined tables, the DBMS Name equals the same as Schema Name.
    • Description
      Provides a brief description of the column.
    • Field Type
      (Read-only for all standard columns in standard tables, and saved site-defined columns) Specifies the data type of the column. You can specify or change the field type of new site-defined columns by selecting a value from the drop-down. The following list describes the available Field Types:
      • INTEGER
        Indicates a numeric value.
      • STRING
        Indicates a text string. The String Length field indicates the number of characters in a string.
      • DATE
        Indicates a date and time. The integer value stored in the database contains the number of seconds since midnight on January 1, 1970.
      • DURATION
        Indicates a period of time. The value stored in the database is an integer containing a number of seconds.
      • DOUBLE
        Indicates a real (floating point) number.
      • SREL
        Indicates a foreign key reference to another table. The SREL Table field specifies the referenced table. The value stored in the database is the rel attr of the referenced table, which can be either an integer or a string. The value displayed in the product is the common name of the referenced table row. For information on setting SREL attributes with foreign key values, see CA Service Desk Manager Reference Commands.
      • BREL
        Indicates a virtual column representing the set of all objects with an SREL to this table. It exists only in the Object Engine and is not physically stored in the database. Select this field type only on direction from a CA Technologies employee.
      • QREL
        Indicates a virtual column representing a set of objects selected by the where clause on the Advanced tab. It exists only in the Object Engine and is not physically stored in the database. Select this field type only on direction from a CA Technologies employee.
      • DERIVED
        Indicates a virtual column constructed by the Object Engine from the values of other columns, under the direction of a formula specified on the Advanced tab. It exists only in the Object Engine and is not physically stored in the database. Select this field type only on direction from a CA Technologies employee.
    • String Length
      The length of a string column. This field is blank for non-string columns. It is read-only for all standard columns, and for site-defined columns that have been saved. You can specify or change the length of new site-defined STRING columns by entering an integer between 1 and 32767 in this field.
    • SRel Table
      The table referenced by an SREL column. This field is blank for non-SREL columns. It is read-only for all standard columns, and for site-defined columns that have been saved. You can specify the table referenced by a new site-defined SREL by selecting it from the drop-down list.
    • On New Default
      The default value assigned to this column when a new row of the table is defined. It should be a value appropriate to the field type. Some keyword values are available for particular field types:
      • NOW
        Specifies the current date and time for a DATE column.
      • USER
        Specifies the active user for an SREL to the Contact table.
    • On Save Set
      The value assigned to this column when a row of the table is updated. It should be a value appropriate to the field type. Some keyword values are available for particular field types:
      • NOW
        Specifies the current date and time for a DATE column.
      • USER
        Specifies the active user for an SREL to the Contact table.
    • Required
      When checked, this option indicates that a value must be supplied for the column before a row of the table containing it can be saved. You can set this option for both standard and site-defined columns, and you can disable an option that you have set. However, you cannot turn off the option of a standard column unless it was set by your site.
    • Updatable only for new record
      When checked, this option indicates that a value for this column can be provided only when a row of its table is initially created, and cannot thereafter be changed. You can set this option for both standard and site-defined columns, and you can disable an option that you have set. However, you cannot turn off the option of a standard column unless it was set by your site.
    • Key for pdm_userload
      When checked, this option indicates that this column is one of the columns tested by pdm_userload to determine whether or not its input is an update to an existing row. This option is available only for STRING columns. It is read only for all columns in standard tables.
    • DBMS Index Options
      These options specify characteristics of a column that is an index of the physical DBMS. They are available only for columns in site-defined tables.
      • Unique
        Specifies that the column is unique within the table and that no two rows have the same value for the column.
      • Ascending
        Specifies that the DBMS index is listed in ascending sequence by this column. Mutually exclusive with Descending.
      • Descending
        Specifies that the DBMS index is listed in descending sequence by this column. Mutually exclusive with Ascending.
    Important! The Schema Designer includes an Advanced tab. Information on this tab is intended for CA Technologies Support and field representatives. You will not need to work with this tab for most uses of the Schema Designer, and it will not be discussed further in this document.

    The Advanced tab of the Schema Designer column dialog shows information meaningful only to persons with internals knowledge of CA SDM. Its contents vary with the column type. We recommend that you modify the values in this tab only on direction from a CA Technologies employee. It contains the following fields if a table is selected:

    • Active Triggers
      Enter the triggers currently active in the Object Engine for the column. A trigger is a small program that runs under the direction of the Object Engine at certain times when a column is modified. The active trigger list includes both standard and site-defined triggers (so that site-defined triggers are listed both in the active list and in the site-defined list).
    • Site-Defined Triggers
      Enter the triggers that have been installed at your site. For example, these may be triggers that have been activated as a result of installing a CA SDM option, or triggers that have been written for your site by CA Technical Services. The active trigger list includes both standard and site-defined triggers (so that site-defined triggers are listed both in the active list and in the site-defined list).
    • LRel Specification
      Enter the specification of a many-to-many relationship between two tables, in the form:
      table1 column1 <> table2 column2
      This shows that the LREL is a many-to-many relationship between table1 and table2, with virtual column column1 containing the relationship in table1, and virtual column column2 containing the relationship in table2.
    • Active xRel Query Information
      Enter the active specification of the query that defines a BREL or QREL virtual column. The specification is in Object Engine Majic format.
    • Site-Defined xRel Query Information
      Modify the query associated with a BREL or QREL. It is recommended that you enter data into this field only under the direction of a CA employee.
    • Active Derived Expression
      Enter the active specification of the expression that the Object Engine uses to construct the value of a DERIVED virtual column.
    • Site-Defined xRel Query Information
      Modify the expression associated with a DERIVED column. It is recommended that you enter data into this field only under the direction of a CA employee.

    The Domsets tab of the Schema Designer Table dialog contains the following:

    • MLIST_DYNAMIC
      A dynamic domset with no WHERE clause.
    • MLIST_STATIC
      A static domset with no WHERE clause.
    • RLIST_DYNAMIC
      A dynamic domset with a WHERE clause defined in the STANDARD_LISTS section of the FACTORY statement.
    • RLIST_STATIC
      A static domset with a WHERE clause defined in the STANDARD_LISTS section of the FACTORY statement.

    If you double-click one of the Domsets, the Properties dialog opens and contains the following fields:

    • domset_name
    • fetch_columns
    • max_fetch
    • sort columns (This is the only editable field)
    • volatility
    • where

  5. Do one of the following to save the column:
    • If you are working on the test system, select File, Save.
    • If you are working on the production system, select File, Save and set to Test Mode.
      This selection saves your changes in the database, and creates a file (wsptest.mods) on the server defining your changes to the Object Engine. This file is stored in the site/mods/majic subdirectory of your CA SDM installation directory. After creating the wsptest.mods file, WSP causes its Object Engine to recycle so that it will use the new changes. This may take from a few seconds to a couple of minutes, depending on the complexity of your schema.
      A message is prompted. Click Yes to continue. The wsptest.mods file affects only the Object Engine designated by the wsp_domsrvr option. Other Object Engines on the same server do not process this file, and the file is not distributed to other servers. In addition, new tables and columns in Test mode are defined to the Object Engine as local objects. This means that the Object Engine knows about them and you can use them on web forms. However, they do not exist in the database, and do not affect other users. Typical CA SDM users do not use WSP Object Engine, so they are unaffected by the schema modifications you are testing.
    The column is added to the table.

Modify the Table or Column

To modify information about a table or column, click table or column on the Schema Designer, and enter the new information in the appropriate fields. The information you can modify depends on the status of the table or column:

  • Standard Tables
    Lets you modify the Display Name, Description, and Function Group fields.
  • Standard Columns
    Lets you modify Display Name, Description fields, the On New Default Value, and the On Save Set value. In addition, if the check boxes for Required or Updatable only for new record are not selected, you can select them. You cannot remove these options if they are set by default. However, you can reverse your own changes.
  • Site-Defined Table
    If the table is not published, you can modify all fields, except Name, which cannot be changed after the new table has been saved. After a site-defined table has been published, you can modify only the Display Name, Description, and Function Group fields.
  • Site-Defined Column
    If the column is published, you can modify all fields, except Name, which cannot be changed after the new column has been saved. After a site-defined column has been published, you can modify only the Display Name and Description fields, the On New Default Value, the On Save Set value, and the check box for Required, Updateable only for new record, Key for pdm_userload, and the DBMS index options.

Test Schema Modifications

You can test your schema modifications and create, update, and view web forms using them before making any changes to the physical database. Putting schema changes in Test mode defines them to the Object Engine, but does not physically store their data in the database. Because putting schema modifications in Test mode has the potential of impacting other users, this option is available only if your installation has installed the wsp_domsrvr and wsp_webengine options to dedicate an Object Engine to WSP.

To put schema changes in Test mode, select Save and set to Test Mode from the File menu. This selection saves your changes in the database, and creates a file on the server (where you are logged in) defining your changes to the Object Engine. This file is called wsptest.mods, and is stored in the site/mods/majic subdirectory of your CA SDM installation directory.

After creating the wsptest.mods file, WSP causes the Object Engine to recycle so that it will use the new changes. This may take from a few seconds to a couple of minutes, depending on the complexity of your schema.

Once the Object Engine has completed recycling, your schema modifications are available, and can be used and test on web forms created with WSP. Data in table and columns in test mode is saved only in internal storage in the WSP Object Engine. It cannot be seen by, and does not affect, other users of the system.

Publish Schema Modifications

After you are satisfied with your schema modifications, you can make them available to all users by publishing them. WSP stores your new or updated tables and columns in the wsptbl and wspcol tables of the database, respectively.

Follow these steps:

  1. Create or update files describing the modified schema to the Object Engine and to CA SDM utility programs. WSP creates the following files on the web engine designated by the wsp_webengine option (which defaults to web:local):
    • wsp.mods
      Describes all Web Screen Painter-maintained schema changes to the Object Engine.
    • wsp_schema.sch
      Describes all Web Screen Painter-maintained tables and columns.
    • wsp_index.sch
      Describes DBMS indexes for Web Screen Painter-maintained tables.
    • wsp.altercol
      Names new columns created by WSP but not yet defined to the DBMS.
    • wsp.altertbl
      Names new tables created by WSP but not yet defined to the DBMS. In addition, WSP distributes the wsp.mods file to all CA SDM servers with an Object Engine.
  2. Select File, Save and Publish.
    The necessary files are created on the CA SDM servers, but does not recycle any of them. Thus, the new files have no immediate impact. However, after the files are created, they will be used the next time CA SDM services are recycled.
  3. If you are using the conventional configuration, complete the following steps:
    • Shut down the CA SDM services on the primary server and run the following command:

      pdm_publish
      

      This command modifies the physical DBMS to contain information about the new schema.

      Important! Running pdm_publish process has a significant impact on other users. Ensure that you carefully plan the publishing of the schema changes. We recommend you use CA SDM Change Orders to schedule and obtain approval for your planned schema publication.
  4. If you are using the advanced availability configuration, complete the following steps:
    1. Execute the following command on the background server to notify all active users using Support Automation to save their work:

      sa_server_notifier [-h] | [-q seconds] | [-c]
      
      • -h
        Displays the help page.
      • -q seconds
        This option notifies a local server (background) to quiesce in a specified time interval. This interval is the number of seconds before the server goes offline. This option cannot be used for a standby server or application server.
      • -c
        This option cancels a previously sent quiesce request.
      A pop-up message is displayed to all the active users using Support Automation on the background server. This message notifies the users about the server shutdown and the scheduled time left for the shutdown. The users must save their work and logout within that scheduled time.
    2. Shut down the CA SDM services on the background server.

      Important! Do not restart the CA SDM services on standby or application servers after "save and publish" is performed from WSP. This action corrupts the advanced availability configuration. If the CA SDM services on standby or application servers are stopped and you want to start the services, run the pdm_server_control -v command on the servers to suppress the version control before starting the CA SDM services.
      Important! If the background server fails during the publishing activity, ensure that you recover the WSP changes. For more information, see the Recover WSP Changes During Background Server Failure topic.
    3. Execute the following command on the standby server that you wish to promote as the new background server:

      pdm_server_control  -b
      
      • -b
        Notifies a local standby server to become the background server. The standby server must already be running. If the server is not running, it is started but no failover is performed; to start a failover, run the command again.
      The background server shuts down automatically and the standby server is promoted as the new background server. This change does not affect the end-user sessions. The in-progress updates (if any) are stored and delayed, until the new background server comes online.
    4. Run the following command on the original background server (now the standby server) to update the DBMS with the schema changes:

      pdm_publish
      

      The pdm_publish command creates a control file that causes the next CA SDM startup to suppress synchronizing the standby server with the background server. This action is necessary to preserve the schema file changes made by pdm_publish. This command optionally performs the second fail-over after successful publishing of the schema changes. The following message is prompted to the user at the end of successful publishing:

      Do you want pdm_publish to start CA Service Desk Manager in this standby server and perform fail-over(Y/N)?
      
      • If you enter Y, pdm_publish starts the CA SDM services on the standby server and performs fail-over automatically. Skip to step g to apply the schema changes on all the application servers.
      • If you enter N, go to Step e.
    5. Start CA SDM services on the standby server (original background server).
      The startup detects the control file that is created by pdm_publish, but does not synchronize the standby server with the background server. This lack of synchronization preserves the changes made by pdm_publish for this startup.

      Important! Ensure that you follow these directions exactly, as the failure to failover to the original background server after a pdm_publish results in corrupted services.
    6. Run the following command on the standby server (original background server) to make it the background server again:

      pdm_server_control  -b 
      

      This command also deletes the control file, so that version control works normally when this server again becomes a standby server.

    7. Execute the following command on the application servers:

      pdm_server_control -q interval -s server_name
      
      • -q interval -s server_name
        Notifies a local or remote application server to quiesce in a specified time interval.  This interval is the number of seconds before the server goes offline. When using this option without a server_name, the local server is notified to quiesce. This option cannot be used for a background or a standby server.
      A pop-up message is displayed to all the active users on the specified application server. This message notifies the users about the server shutdown and the scheduled time left for the shutdown. The users must save their work and logout within that scheduled time. The users log on to the updated application server to resume their work.
    8. Restart all standby servers.

Recover WSP Changes During Background Server Failure

You can recover the MDB schema changes when the background server fails during the publishing activity.

Important! We recommend you not to perform the recovery steps directly on the production environment. Ensure that you first validate them on the test or development environment.

Follow these steps:

  • If the background server has crashed before publishing, the last saved schema changes are preserved in MDB. You log in to the new background server and resume your publishing tasks.
  • If the background server crashed after publishing, perform the following actions:
    1. Stop CA SDM services on the crashed background server.
    2. Choose a standby server that you want to promote as the new background server.
    3. Copy the following files from crashed background server to the same location on the standby server:
      • "$NX_ROOT$/site/mods/majic/wsp.mods"
      • "$NX_ROOT$/site/mods/wsp.altertbl"
      • "$NX_ROOT$/site/mods/wsp.altercol"
      • "$NX_ROOT$/site/mods/wsp_index.sch"
      • "$NX_ROOT$/site/mods/wsp_schema.sch
    4. Run the following command on the standby server to publish the schema changes and to perform the automatic fail-over:

      pdm_publish
      
    5. Select Y on the message prompt that appears after successful publishing of schema changes.
      The CA SDM services are started on the standby server.

      Note: If automatic fail-over does not occur, run the pdm_server_control -b command from the standby server to promote it as the new background server.
    6. Quiesce and recycle each application server. Restart all standby servers. For more information, see the Publish Schema Modifications topic.

Revert Schema Modifications

If you change your mind about your schema modifications after putting them in test mode, you can revert back to the published, version of the schema. Reverting schema modifications has the potential of impacting other users. For this reason, this option is available only if your installation has installed both the wsp_domsrvr and wsp_webengine options to dedicate an Object Engine and a Web Engine to WSP.

Follow these steps:

Select File, Revert Test Mode.
WSP deletes the wsptest.mods file, causing WSP Object Engine to revert its schema back to the published version.
After deleting the wsptest.mods file, WSP causes its Object Engine to recycle so that it can rebuild its internal schema. This may take from a few seconds to a couple of minutes, depending on the complexity of your schema.
After the Object Engine has completed recycling, the active schema is back to its published version.

Note: Web forms modified to work with the new schema are not automatically reverted, and may not work correctly when used with the published schema.

Modify Site-Defined Columns after Publishing

After site-defined schema modifications are published, WSP treats them similarly to the standard schema and restricts further changes. You can delete a site-defined column or can change the length of a site-defined string column by manually updating the DBMS and schema external to WSP. Then you run the pdm_wspupd script to update the database wspcol table to synchronize WSP with the external changes.

Follow these steps:

  1. Log in to the following server, depending upon your CA SDM configuration:
    • Conventional: Primary server
    • Advanced availability: Background server
  2. Find the site/mods subdirectory in your CA SDM installation directory.

    Tip: Create a backup of the wsp_schema.sch file, at a location other than the site/mods subdirectory, to avoid processing the duplicate files.

  3. Edit the wsp_schema.sch file to delete unwanted site-defined columns or change the length of site-defined STRING columns. These updates are the only changes supported by this procedure. You can use any standard text editor to edit the wsp_schema.sch file.

    Important! If any of the index options (such as, UNIQUE) were specified for deleting a column, edit the wsp_index.sch file and remove references to the column. If the column was the only indexed column in the table, remove all references to the table from wsp_index.sch.
  4. Edit majic/wsp.mods file with the same changes made to wsp_schema.sch:
    • Delete unwanted site-defined columns.
    • Change the length of site-defined STRING columns.
  5. Enter the following command using the command prompt:

    pdm_wspupd
    

    The pdm_wspupd script reads wsp_schema.sch and compares it with the wspcol table in the database, writing a line to the console for any differences. For example, see the following output:

    PDM_WSPUPD - Update wspcol table from wsp_schema.sch
    Reading wsp_schema.sch to for current DBMS information...
    Reading wspcol table for WSP schema information...
    String column zSalesOrg.description length changed from 350 to 400
    Column zSalesOrg.sym not found in wsp_schema.sch - deleting wspcol row
    pdm_wspupd found 1 WSP-maintained column(s) to update and 1 to delete. Please verify that your DBMS has been manually updated to correspond to wsp_schema.sch, then reply Y to update wspcol or anything else to cancel.
    
  6. Verify that the changes found by pdm_wspupd correspond exactly to the changes you made to wsp_schema.sch. If they match, type Y to confirm the changes.
    After you confirm the update, the script uses standard CA SDM utilities to update the wspcol table. Then, Schema Designer shows your changes.
  7. Stop the CA SDM servers.
  8. Using the appropriate utility for your DBMS, alter the DBMS definition of the columns you changed:
    • Delete any columns from the database that you deleted from wsp_schema.sch.
    • Change the database length of any string columns you changed in wsp_schema.sch.
    Take care that the changes you make to the DBMS correspond exactly to the changes you made to wsp_schema.sch.
  9. Publish Schema Modifications.
  10. Start the CA SDM servers.
Was this helpful?

Please log in to post comments.

  1. Anonymous
    2015-10-28 05:32

    A link to this page in the French language:

    Modification d'un schéma à l'aide du concepteur Web

  2. Karen Matoke
    2016-06-06 07:32

    Please add "pdm_schema.log" file in step #1 of the section titled "Publish Schema Modifications". Please describe the purpose and warnings not to delete it.