Content Platform Engine, Version 5.2.1            

createDBviews

The createDBviews command recreates case-sensitive database views for all local queues, rosters, and event logs for the current isolated region. Database views are automatically created whenever a transfer occurs.

Database views provide an alternative to using the Process Java™ API to query queues, rosters, and event logs; this alternative involves the use of standard database query tools and can significantly improve performance when querying large numbers of records.

The createDBviews command has two options:

To recreate non-case-sensitive database views, use the createDBviewsCI command.

Syntax

createDBviews <choice><workspaceid><filename>

Parameters

Table 1. Parameters and description for the createDBviews command
Parameter Description
choice Whether to create a script or create the views. Enter "s" to create a script that can be run later using standard database tools. A text file, which describes the views, is also created. Enter "v" to create views immediately.
workspaceid Workspace ID of workspace from which to retrieve the information.
filename Name (and path) of the resulting script and text files, without an extension. (The extensions .SQL and .TXT are automatically appended to the name you choose.) By default, the files are created in the directory from which you are running vwtool. This command option only applies if you enter "s" for the choice.

Use the vwtool utility interactive mode to enter this command and parameters. See Tips for using vwtool for information about entering parameters and other common actions.

The naming convention for database views is:
 VWV<type><isolated_region>_<classname>

where

Table 2. Parameters and description for database views naming convention
Parameter Description
type Indicates queues (Q), rosters (R), or event logs (L).
isolated_region The isolated region in which the views are created.
classname The name of the roster, queue, or event log.
Important: Case Analyzer determines the workflow system tables it needs to access by querying for all views that start with "VWV". To avoid confusion, do not use this prefix when creating views other than those created during a transfer or by the createDBviews command.

For data fields, the command uses the names as you specified them for the workflow system rather than their physical database names. Therefore, the resulting script might contain syntax errors if the alias is not a valid database field name.

Tip: The maximum length of a queue, roster, or event log database view name is 30 characters for SQL Server and DB2, and 30 bytes for Oracle, including the system-assigned prefix described above. Therefore, if you specify a name that is longer than 23 characters, the corresponding view name is truncated. If you create another item with the same name for the first 19 characters and differentiated by the final few characters, that view name is also truncated and the end of the name is an internally generated number (0001, 0002, and so on). If your applications use the database view for queries, it is easier to determine which view to query if you use shorter names.

When using the views to perform queries, you must enclose all user-defined field names in quotation marks. In addition, queries on event log views require that you enclose all field names in quotation marks. See the examples below.

Table 3. Query examples
Query type Query example
Queue or roster query select F_WobNum, F_WorkSpaceId, "userfieldname1", "userfieldname2" from f_sw.VWVQ94_testmodswp;
Event log query select "F_WobNum", "F_WorkSpaceId", "userfieldname1", "userfieldname2" from f_sw.vwvl94_testparentlog;

The Oracle database views are created with a 'read only' option. You must manually modify the permissions on views for SQL Server and DB2.



Last updated: October 2015
bpfvl006.htm

© Copyright IBM Corporation 2015.