More about Connect SQL rules

Use Connect SQL rules with an external database. Except in unusual situations, use the Obj-Browse method or Obj-List-View method rather than Connect SQL rules and RDB methods for the PegaRULES database.

When you need to access objects in a PegaRULES database, the RDB- methods and Connect SQL rules may not work, because not all the properties in the PegaRULES database are distinct database columns. Use the Obj-Open and Obj-Save methods, not the RDB- methods, with the Pega Platform database.

Debugging

Use the Clipboard tool to see the final SQL statement sent to the database and the results. Use the Tracer tool with breakpoints to pause processing before or after the RDB method executes.

For advanced and detailed debugging, use the DB Trace feature of the Performance tool.

Using views for SQL involving multiple tables

As a best practice, restrict the SQL statements to operate on a single table, aligned with a database table value and the Applies To key part of the Connect SQL rule.

If your SQL needs to operate on multiple tables, consider implementing a view within the database that presents the columns. This approach places some responsibility for good database design and performance with the database administrator and database software.

Testing connectivity

After you save this rule, you can click Test Connectivity to confirm that Pega Platform can find, log into, and communicate with the database. This button does not cause the SQL to be processed and does not alter the contents of the database.

The system presents test results in a separate window, identifying the parameters used in the test, the steps attempted, and the outcome of each step.

How this rule works

At runtime, the system interprets the keywords in the context of your clipboard, Database Table and Database information to form the final SQL. It then executes the SQL using standard database access capabilities.

Each operation involves the SQL statement on one tab of the rule. To determine which database to access, the system uses this search algorithm:

  1. Uses the database table associated with the Class of a {Class } directive in the source SQL statement.
  2. When the SQL contains no {Class} directive but contains a {Table} directive, searches through Data-Admin-DB-Table instances for a match on the table name, and uses the database identified in the referenced Data-Admin-DB-Name.
  3. Uses the database table associated with the Applies To key part of the Connect SQL rule.
Note: Algorithm 2. can produce uncertain results if two tables in separate databases have the same name. Use {Class} rather than {Table} when feasible.

Data retrieved from a relational database is saved in clipboard pages.

Your activity can perform two or more RDB methods, for example first to open one record (row) in the database and later to save the record with updated values. The activity can first execute the RDB-Open method, referencing the first instance. The row data is then on the clipboard; the activity can modify the data (using Property-Set and other methods). The same activity can then execute the RDB-Save method, to store the updated row into the database.

Each RDB-Delete and RDB-Save method execution commits the changes to the external database. You cannot combine the database commit operations of multiple statements.

Notes on date and time values

In its internal PegaRULES database, Pega Platform stores date and time information adjusted to GMT time, based on the time zone of the server. The PegaRULES database contains few dates without a time and zone, and few times without a zone and a date. This approach facilitates date and time comparisons and arithmetic regardless of the time zone of users or external systems that interact with your system. See Understanding the Date, TimeOfDay, and DateTime property types.

If the external database accessed through Connect SQL rules contains dates without a time zone, or times without a date and time zone, analyze these values carefully. Use caution before making comparisons of external dates and times with date/time values within Pega Platform.

For example, the Oracle DATE data type requires a time. If you store a Pega Platform property of mode Date (which has no time) into an Oracle DATE column, the time portion of the value is defaulted to 00:00:00 GMT, which may not be appropriate. As a workaround, store Date values into Oracle columns of data type VARCHAR.

Debugging SQL and SQL Performance

You can use the DB Trace feature of the Performance tool to view in depth the SQL operations of a Connect SQL rule.

Limitations

The database you reference in the Connect SQL rule must be the default database of the database user account identified in the Database data instance.

Connect SQL rules can access an IBM Db2/390 database, with two restrictions:

  • Do not execute stored procedures
  • Do not use the SELECT AS statement with a column name

Connect SQL rules can access an IBM Db2 Universal Database (UDB) database, except for database columns with a DATE data type. Use syntax similar to the following as a workaround: 

SELECT TIMESTAMP(RPT_DATE, '00.00.00') AS ".RPT_DATE"
Note: SQL connectors cannot process columns of data type ARRAY, IMAGE, or CLOB (Character Large Object).

Microsoft does not supply a JDBC driver for Microsoft Access databases, although third-party facilities are available. As a workaround, some Pega Platform applications have converted Microsoft Access databases to SQL Server databases, or exported the data to CSV files that are then imported using Service File rules.

Standard rules

Standard Connect SQL rules with Assign- as the Applies To key part support the service-level agreement operations of the Pega-ProCom agent. Other standard Connect SQL rules support operation of the Automated Test facility and usage reporting.

Your system also contains a single standard Connect SQL rule named PegaSample-Task.CollectData. This rule is marked as unavailable and is not part of a working example. It is provided to illustrate the rule form.