Versions Compared

Old Version 2

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user Chee Hove Teng

Saved on

Key

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

The Database Bulk functions enable you to bundle multiple queries into one SQL statement. This significantly improves the throughput due to reduced need of context switching and network traversals. The functions are intended for batch usage only. 

All functions without the parameter disableCommit=false will not be auto commit after the function has executed.

Making an SQL Query

When making an SQL query it must follow a certain format and the order of the columns in the query must remain the same in the statement as in the where_clause.

...

If the where_clause in the SQL statement contains a range condition instead of an exact lookup key, the intended result cannot directly be associated back to the UDRs. Instead a result based on the significant columns will be returned. The result rows matching the range condition must be associated back to the UDRs using APL code. For further information, see the lookup query example below.

The following functions for Database Bulk described here are:

Table of Contents
maxLevel1

sqlBulkPrepareStatement

This function prepares the the query. A JDBC connection is created and the bulkSize is set.

...

ParameterDescription

dbProfile

Name of the database where the table resides

statement

Statement to send to the database

static_where_clause

The static_where_clause of the SQL statement stays the same throughout the batch mode execution

where_clause

The where_clause of the SQL statement principal.

The where_clause columns that are to be considered significant must correspond with the columns in the select statement. For example, when statement reads "select A, B and C from X", where A and B is significant, the where_clause must begin with "A=? and B=?".

bulkSize

The size of the bundled lookup SQL query, that is the number of UDRs with a unique UDR key to be bundled. The ideal bulkSize depends on database table characteristics such as structure and number of rows.

significantColumns

This parameter indicates which columns in the select statement are significant to tie a result row to its context (UDR).

All columns to be used as significant columns must be included in SQL search condition, and they must be used in the same order as in the search condition. For example, when using an incoming UDR as context and a search condition matching its IMSI field, using significant columns = 1 ties all matching rows to this UDR, namely, all rows with the same IMSI as that carried by the UDR.

totalColumns

The total number of columns in the statement

timeout

The least time interval, in milliseconds, the workflow will allow before a database bulk lookup will be done

disableCommit

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 to be performed at the end of every SQL transaction for this particular function. By default, will have the system has the disableCommit set to true unless otherwise changed via this parameter.

Info

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


Returns:

The returned object is a “Prepared Statement” object containing the added parameters, a prepared result list and a maintained contexts list. This object is a mandatory parameter to the sqlBulkExecuteQuery() function and is needed for the execution of the SQL query.

...

Info
titleExample - Lookup query

Lookup query with one of the where_clause conditions without an exact lookup key.

To prepare a query:

Code Block
SELECT Column1, Column2, Column3 FROM Test_Table
WHERE Column1=? AND Column2=? AND Column3 <=?;

The call to the function and the values for the above example:

Code Block
significantColumns = 2
totalColumns = 3
where_clause = "Column1=? and Column2=? and Column3 <=?"
static_where_clause = null
bulksize = 100
dbprofile = "myFolder.myProfile"
statement = "select Column1, Column2, Column3 from Test_Table"

The call to the APL function:

Code Block
initialize {
    any pStatement = sqlBulkPrepareStatement("myFolder.myProfile", 
    "select Column1, Column2, Column3 from Test_Table",
    null, "Column1=? and Column2=? and Column3 <=?",
    100, 2,3);
}


sqlBulkExecuteQuery

When a UDR enters sqlBulkExecuteQuery the first met criteria of bulkSize or timeout triggers the database bulk lookup.

...

ParameterDescription

ps

A preparedStatement object returned by the sqlBulkPrepareStatement(), containing among others a result list and a maintained contexts list.

Returns:

A list containing objects that in their turn contain a context object and a list with results.

sqlBulkResultGetContext

The function finds the context object from one result object in the list returned either by the sqlBulkExecuteQuery() or the sqlBulkDrain() functions.

...

ParameterDescription

presult

A result object from the list returned by the sqlBulkExecuteQuery() or the sqlBulkDrain() functions

Returns:

The context object associated with the presult parameter

sqlBulkResultGetSize

The function states the size of a result object in the list returned from the sqlBulkExecuteQuery() or the sqlBulkDrain() functions.

...

ParameterDescription

presult

A result object from the list returned by the sqlBulkExecuteQuery() or the sqlBulkDrain() functions

Returns:

An integer representing the size of the result

sqlBulkResultGetValue

The function gets a result value from a result object in the list returned by either the sqlBulkExecuteQuery() or the sqlBulkDrain() functions.

...

ParameterDescription

presult

A result object from the list returned by the sqlBulkExecuteQuery() or the sqlBulkDrain() functions

index

The index of the result table

column

The column number

Returns:

The value of the result object

sqlBulkElapsedNanoTime

This function accumulates the time of the execution of the database queries that is end time of execution minus start time of execution.

...

ParameterDescription

Returns:

A long integer representing the accumulated nanoseconds.

sqlBulkClose

The function closes down the database connection as well as the SQL statement. If the sqlBulkClose is not called the connection will be closed when the workflow stops.

...

ParameterDescription

ps

A preparedStatement object returned by the sqlBulkPrepareStatement(), containing among others a result list and a maintained contexts list

Returns:

Nothing

Example Workflow

The example shows a basic workflow where a Database Bulk lookup with a range condition is performed.

 Image RemovedImage Added

Example workflow

Prerequisites

To use the sqlBulk related APL commands there must be a database profile configuration prepared. For further information, see 8.5 see Database Profile in the Desktop in the Desktop user's guide.

Image Removed Image Added

Database profile example

Decoder_1 Agent

The incoming UDRs contains name and number to 10 people.

Code Block
Katerine,Stenberg,0046123456
Kalle,Andersson,0046789123
Mia,Karlsson,0046999111
Karina,Hansson,0046222333
PerErik,Larsson,0046111999
Mikael,Grenkvist,0046333444
Jonas,Bergsten,0046555666
Petra,Sjoberg,0046777888
Karl,Kvistgren,0046444555
FiaLotta,Bergman,0046666222

Analysis_1 Agent

The Analysis agent does the database bulk lookup. In this case, the number of calls made by the people listed in the incoming UDRs.

...

The database lookups are in this example done in a database table with a content shown here:

Database Table

Encoder_1 Agent

When the encoder has encoded the information from the analysis agent the result sent to the disk out agent is shown here:

...