Content Platform Engine, Version 5.2.1            

createDBviewsCI

The createDBviewsCI command creates database views that are not case-sensitive for all local queues, rosters, and event logs for the current isolated region.

The createDBviewsCI command has two options:

To recreate database views that are case-sensitive, use the createDBviews command.

If the advanced property DBCREATEPEVIEWS is set, these non-case-sensitive database views are created automatically 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.

Syntax

createDBviewsCI <choice><workspaceid><filename>

Parameters

Table 1. Parameters and description for the createDBviewsCI command
Parameter Description
choice Specifies 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 the 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.

The naming convention for the non-case-sensitive database view is:

PEV<type><isolated_region>_<classname>

where

Table 2. Parameters and description for the non-case-sensitive 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 database tables it needs to access by querying for all views that start with "VWV" (case-sensitive database views). To avoid confusion, do not use this prefix when creating views other than those created during a transfer or by the createDBviews command. The non-case-sensitive database views beginning with PEV are not used by Case Analyzer. Do not use the PEV prefix when creating views as well.

For data fields, the command uses the names as you specified them for the workflow system rather than the physical database names. Therefore, the resulting script might contain syntax errors if the alias is not a valid database column 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.

Database views that are not case-sensitive are defined without quoted identifiers. Hence, casing is not important when querying the database. 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.PEVQ94_testmodswp;
Event log query select F_WobNum, F_WorkSpaceId, userfieldname1, userfieldname2 from f_sw.PEVL94_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
bpfvl007.htm

© Copyright IBM Corporation 2015.