WhereScape RED provides the Multiple SQL database feature to enable tables to be distributed across multiple databases, instead of only a single database—refer to Distributing Tables across Multiple SQL Server Databases for details.
Update procedures for Table objects are stored in the RED repository meta database. However, if required, the location of stored procedures can be changed and also be distributed across different Targets.
The following features in WhereScape RED are used for setting the location of stored procedures associated to a Table object in RED.

Connection Target Settings

The Create procedures in same database as table option in the Target Settings tab of the Connection Properties window, enables you to specify if the procedures associated with new Table objects are created in the same Target location as the Tables.

This option is not selected (OFF) by default, e.g. the table's stored procedures are created in the RED repository meta database. If the Table is created in a different Target location (e.g. distinct schema/database in the same SQL Server instance), you can turn ON this option, if you want the stored procedures to also be located in the same Target.

Once this option is set, all new Table objects (created in the defined Target locations) will also have their generated procedures stored in the same Target as the Table objects.

Note

  • This setting only applies for SQL server Target databases. For new RED repositories, this option is ON by default (e.g. procedures are created in the same location as the Table object). New or regenerated procedures are created in the specified location.
  • Changing this setting has no impact on existing stored procedures. You must Rebuild or Regenerate the stored procedures for the changes to take effect.
  • If the Target location of a Table object is changed, then both the Re-creation of the Table object (as in previous versions of RED) and the Regeneration of the Procedure object is required for both objects to be moved.

Procedure Properties

The Connection Name and Target Name fields in the Procedure Properties window, enables you to change the connection against which the Procedure or Function is executed and the Target where the update Procedure or Function is stored.

An option to Lock the Storage Location can also be set, if you want to retain the current location of the procedure, e.g. no changes will occur if the Target location of the procedure is manually changed and a Rebuild or Regenerate operation is performed via the Table's Properties window or from the Table's right click Code context menu.

Procedure Placeholders

The following procedure placeholders are used for moving procedures to the required target database location(s):

  1. [SCHEMA.procedure_name].procedure_name  = resolves to  schema.procedure_name  during the compilation process of the procedure.
    This placeholder is used in the CREATE PROCEDURE statement to resolve the schema location for the procedure.
  2. [PROCEDUREOWNER].[procedure_name]  = resolves to  database.schema.procedure_name  during the compilation process of the procedure.
    This placeholder is used to fully qualify procedures, such as GET_KEY_procedure from other databases.
  3. [METABASE].wsl_callable_routine  = resolves to  database.schema.wsl_callable_routine  during the compilation process of the procedure.
    This placeholder is used for each callable routine to always fully qualify to the metadata database and schema.

Moving Existing Procedures

The steps for moving modified and non-modified procedures are outlined below:

Non-modified

In this case, an existing procedure generated by RED is being moved to another Target location.

  1. After changing the Target location of the stored procedure, you must regenerate the procedure using the Code > Regenerate update_procedure option from the Table object's right click context menu.
  2. Check the definition of the regenerated procedure to ensure that it is updated to include the required procedure placeholders and Table mark-ups.

Modified

In this case, a user created procedure, or a modified RED procedure is being moved to another Target location.

Applying the procedure and table mark-ups in the procedure

  1. Open the modified procedure via the procedure editor window.
  2. Edit the procedure as follows:
    • Change the CREATE PROCEDURE syntax:

      CREATE PROCEDURE  [SCHEMA.procedure_name].procedure_name

    • Ensure that all referenced procedures have the procedure owner mark-up:

      [PROCEDUREOWNER].[referenced_procedure_name]

    • Ensure that all source table references have the table owner mark-up:

      [TABLEOWNER].[table name]

    • Ensure all calls to metadata procedures and functions have the metadata database mark-up:

      Exec  [METABASE].WsWrkAudit

  3. Save and compile any updates to the procedure.

Moving the procedure to the new database

  1. Open the Procedure Properties of the modified procedure and set the Connection Name and Target Name fields to the appropriate connection and Target location for the stored procedure, and then click OK.
  2. RED prompts if you want to proceed with the change in storage location, click Yes to proceed.

    Note

    RED drops the procedure from the original database and creates it in the new Target location.

Repeat the same steps to move other modified procedures to another Target location.

Note

  • When upgrading to version 8.3.1.0, the default connection and default target attributes for SQL Server procedure objects are cleared, to reflect that procedures are currently stored in the RED repository meta database.
  • If the Target location of a Procedure is manually changed in the Procedure Properties, then regenerating or rebuilding the Procedure reverts its location back to the RED repository meta database or to the Table object's storage location (depending on whether the Target storage option is set in the Connection Properties).
  • If you want to set the storage location of a Procedure to something other than your connection default Target, then set the Lock Storage Location option in the Procedure Properties to override the connection default Target.


  • No labels