Versions Compared

Old Version 2

changes.mady.by.user Kevin Wu

Saved on

compared with

New Version Current

changes.mady.by.user Felicity Borrelli

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

This section describes the Shared Table profile. This profile enables workflow instances to share tables for lookups.Image Removed

...

Shared Table

...

The Shared Table profile configuration is opened by clicking on the New Configuration button in Desktop and selecting the Shared Table Profile option.Image Removed

...

The Shared Table profile dialog

The contents of the buttons in the button bar may change depending on which configuration type has been opened. The Shared Table profile uses the standard menu items and buttons that are visible for all configurations, and these are described in Build View.

The Shared Table profile dialog contains the following configurations:

The Shared Table profile dialog

The contents of the buttons in the button bar may change depending on which configuration type has been opened. The Shared Table profile uses the standard menu items and buttons that are visible for all configurations, and these are described in Build View.

The Shared Table profile dialog contains dialog contains the following configurations:

Setting

Description

Setting

Description

Database

Database

Click the Browse...button and select the Database profile you want to use. Any type of database that has been configured in a database profile can be used. See Database Profile for further information.

Release Timeout (seconds)

If this check box is selected, the table will be released when the entered number of seconds has passed since the workflows accessing the table were stopped. The entered number of seconds must be larger than 0.

If this check box is not selected, the table will stay available until the execution context is restarted.

Refresh Interval (seconds)

Select this check box in order to refresh the data in the table with the interval entered. The entered number of seconds must be larger than 0.

If this check box is not selected, the table will only be refreshed if the APL function tableRefreshShared is used. For more information regarding the function, see the section below, tableRefreshShared.

Note
title

Note!

The interval for checking if the table needs to be refreshed is 10 seconds, which is the minimum time before a new refresh is performed.

If a refresh fails, an error is generated in the system log, but the table is not cleared - the old data remains in the shared table. A new refresh is initiated every 10th second until the refresh has finished successfully.

Table Storage

Object

Select this option to set the Table Storage to Object. If you select this option, the shared tables are stored as Java objects on the JVM heap.

Note
title

Note!

If you have selected to use a profile with the CSV database type, this is the only option available if you have not configured properties using Advanced Connection Setup.

On Heap

Select this option to set the Table Storage to On Heap. If you select this option, the shared tables are stored in a compact format on the JVM heap. If you select On Heap, you must select an option for the Index Storage.

Note
title

Note!

If you have selected to use a profile with the CSV database type, this option is only available if you have configured properties for this using Advanced Connection Setup.

Off Heap

Select this option to set the Table Storage to Off Heap. If you select this option, the shared tables are stored in a compact format outside the JVM heap.

Note
title

Note!

You are required to set the JDK parameter in the relevant Execution Context pico configuration, for example:

Code Block
language
textthemeEclipse
$ mzsh topo set topo://container:<container>/pico:<pico>/obj:config.jvmargs \
'maxDirect:["-XX:MaxDirectMemorySize=4096M"]'

Note

title

Note!

If you have selected to use a profile with the CSV database type, this option is only available if you have configured properties for this using Advanced Connection Setup.

If you select Off Heap, you must select an option for the Index Storage.

Unsafe

Select this option to set the Table Storage to Unsafe. If you select this option, the shared tables are stored in a compact format. If you select Unsafe, you must select an option for the Index Storage.

Note
title

Note!

If you have selected to use a profile with the CSV database type, this option is only available if you have configured properties for this using Advanced Connection Setup.

Primitive Lookup

Select this option to set the Table Storage to Primitive Lookup. This provides simple lookup tables with a fast lookup function but they are limited to two columns of type Int/Long for the key (column 1) and type Short/Int/Long for the value (column 2). Lookup operations on Primitive Lookup tables are limited to the equals operation on column 1.

Note
title

Note!

If you use the  Primitive Lookup  option with a database profile that is configured for Oracle, using the Oracle column type NUMBER with a precision greater than 10 may cause errors.

Note

title

Note!

If you have selected to use a profile with the CSV database type, this option is only available if you have configured properties for this using Advanced Connection Setup.

Index Storage

Object

Select this option to set the Index Storage to Object. If you select this option, the index is stored as Java objects on the JVM heap. This option is only available if you have selected On Heap, Off Heap or Unsafe for Table Storage.

Pointer

Select this option to set the Index Storage to Pointer. If you select this option, the index is stored as pointers to the table data. This option is only available if you have selected On Heap, Off Heap or Unsafe for Table Storage.

Cached Long/Int Pointer

Select this option to set the Index Storage to Cached Long/Int Pointer. This option is only available if you have selected On Heap, Off Heap or Unsafe for Table Storage. For numeric index columns, the Cached Long/Int Pointer can be used for faster lookups, but at the cost of slightly higher memory consumption.

Variable Width Varchar Columns

Select this check box to enable variable width storage of varchar columns. This reduces memory usage for columns that are wide and of varying widths.

SQL Load Statement

SQL Load Statement

In this field, an SQL SELECT statement should be entered in order to create the contents of the table returned by the tableCreateShared APL function.

The following statement will return a table named MyTable with the columns key and value when the tableCreateShare function is used together with this profile.

Info
title

Example - SQL SELECT statement

Code Block

languagethemeEclipse

text

SELECT key,value FROM MyTable

If no data has been fetched from the database, SQL errors in the table lookup will cause runtime errors (workflow aborts). However, if data has already been fetched from the database then this data will be used. This will also be logged in the System Log.

Whenever possible, use values of the type long instead of the type date or string. This may improve performance.

Info
title

Example - Using SQL SELECT statement

Code Block

languagetextthemeEclipse

SELECT to_number(user_id) as USER_ID, 
 to_number(to_char(nvl(start_date,to_date(
	'19000101010101',
	'yyyymmddhh24miss')),
	'yyyymmddhh24miss')) as START_DATE,
 to_number(to_char(nvl(end_date, to_date(
	'99990101010101',
	'yyyymmddhh24miss')), 
	'yyyymmddhh24miss')) as END_DATE
 FROM MyTable

Table Indices

If you want to create an index for one or several columns of the shared table, these columns can be added in this field by clicking the Add button and add the columns for which you want to create an index. The index will start with 0 for the first column.

Note
title

Note!

An index will not be created unless there are at least five rows in the table.

Create indices in the Shared Table profile based on the data that is fetched from the database. Even if you look up multiple columns in APL, using one index instead of several may result in improved performance. 


title
Note

Note!

Some Database Management systems provide character column types so that you are not required to specify the column width (e g TEXT in PostgreSQL). If you use shared tables with such a column type, you cannot use the types On Heap, Off Heap or Unsafe within Table Storage.

...

This function returns a shared table that holds the result of the database query entered in the Shared Table profile.

Code Block
theme
languagetextEclipse
table tableCreateShared 
    ( string profileName 
	  @Deprecated boolean disableCommit )


Parameters:

Returned Value

Description

profileName

Name of the Shared Table profile you want to use.

disableCommit

(Deprecated from MZ 9.2.2.0)

An optional parameter to disable the commit statement from being performed at the end of every SQL transaction for this particular function. Setting this parameter to false will result in the commit statement being performed at the end of every SQL transaction for this particular function. By default, disableCommit is set to true unless otherwise changed via this parameter.

Info

It should be noted that on recent Oracle versions, the DBLink SQL transaction behavior has changed, where every single SQL statement for remote database transactions requires a commit or rollback statement in order to close a connection.

Returns

A table containing the columns stated with the SQL query in the stated Shared Table profile, that can be shared by several workflow instances.


title
Info

Example - Using the function tableCreateShared

This creates a shared table called myTable with the columns returned by the SQL query in the mySharedProfile Shared Table profile.

text
Code Block
language
themeEclipse
initialize {
  table myTable = tableCreateShared("Folder.mySharedProfile");
  }

...

This function can be used for refreshing the data for a shared table configured with a Shared Table profile. The table will be updated for all workflow instances that are using the table and running on the same EC.

language
Code Block
text
themeEclipse
table tableRefreshShared 
    ( string profileName 
	  boolean disableCommit )


Parameters:

Returned Value

Description

profileName

Name of the Shared Table profile you want to refresh data for.

disableCommit

(Deprecated from MZ 9.2.2.0)

An optional parameter to disable the commit statement from being performed at the end of every SQL transaction for this particular function. Setting this parameter to false will result in the commit statement being performed at the end of every SQL transaction for this particular function. By default, disableCommit is set to true unless otherwise changed via this parameter.

Info

It should be noted that on recent Oracle versions, the DBLink SQL transaction behavior has changed, where every single SQL statement for remote database transactions requires a commit or rollback statement in order to close a connection.

Returns

A refreshed shared table.


title
Info

Example - Using the function tableRefreshShared

This returns the shared table called myTable, which uses the mySharedProfile, with refreshed data.

Eclipse
Code Block
languagetext
theme
table myTable = tableRefreshShared("Folder.mySharedProfile");

...