Generate Insert/Update SQL for DataStage Oracle Connector Automatically (Updated)

CodeProject
Last year I published a post for automatically generating insert/update SQL used in DataStage Oracle connectors. I used Oracle 11g's new function LISTAGG(), which resulted in 2 major limitations.

1. It only applies to Oracle 11g or later.
2. LISTAGG has 4000-character length limit. So it doesn't work on wide tables with more than 100 columns.

Here is an updated version without using LISTAGG(), hence removing the limitations.

Insert SQL:

Update SQL:

DataStage Netezza Connector Sparse Lookup Error: Count field incorrect

CodeProject
Error Message:

Unexpected ODBC error occured. Reason: [SQLCODE=07002][Native=8] Count field incorrect (CC_NZStatement::executeSelect, file CC_NZStatement.cpp, line 137)

Cause:
 One or more fields in the data stream are used multiple times in the lookup SQL statement.

Work-around:
Duplicate the field(s) so that each is used exactly once.

Mimic Row Pattern Matching in Oracle 11g

CodeProject
Oracle 12c introduced MATCH_RECOGNIZE clause for row pattern matching. If you are still using Oracle 11g, you can use listagg() and regexp_like() to implement row pattern matching. For example, in order to find users with event A followed by event D (other events allowed in between),

User	Sequence	Event
1	1	A
1	2	D
1	3	C
1	4	B
2	1	C
2	2	B
2	3	A
3	1	A
3	2	C
3	3	D

you can use the following query:

SignalR Pushes ISD Applications to Next Level

CodeProject

Introduction

ISD applications, web applications in general, are built on top of HTTP, which uses a pull-based communication mechanism. All communication requests are initiated or pulled from clients. Web servers can only respond to the requests. They cannot initiate or push communications to clients. Put it another (simplified) way. ISD applications are a collection of server-side methods to be called from client-side. When you type in a URL in a browser, you call its Page_Load() method. When you click a button or make a selection in a dropdown list, you call their corresponding Button_Click() or DDL_SelectedIndexChanged() methods.

The limitation of pull-only communication makes it difficult to implement push-based functionality in ISD applications. For example, if several users try to modify the same record at the same time, only the first user can save his changes. When the others try to save their changes, they will be greeted with a popup message, "The record has been changed. Please refresh." It would be much better if,  after the first user saves his changes, the server immediately pushes out a notification to all other users, so that they don't waste any time or effort unnecessarily.

Solution

It is possible to mimic such push functionality within the current pull-only framework. For example, embed a Timer control in the EditRecord page, and check the record's timestamp or check-sum periodically. However, the implementation is extremely inefficient because of repeated unnecessary web and database traffics. An ideal solution is to call a client-side method from server-side as soon as the first user saves his changes. I am not talking about using RegisterStartupScript()to inject JavaScript code, which is executed only at the single client that initiates the request. What I suggest is to call a JavaScript method on ALL clients from server-side. Impossible? SignalR enables me to do just that.

SignalR is an ASP.NET library which allows bi-directional communication between server and client. In other words, it allows web applications to push content to clients by calling client code from servers, and vice versa. In the following demo project, I will show you how simple it is to implement a push notification in an ISD application.

Implementation

Step 1: Build a regular ISD application with required configuration

SignalR 2.0 is pretty picky on system configuration. Check its requirement page to make sure your server is supported, and your intended users have compatible browsers. To create the demo application, I used the following configurations:

• ISD v10.2.1
• VS 2012
• .NET 4.5
• Web site
• C#
• Develop under Windows 7
• Deploy to Windows Azure
• IIS Express

C# web site is just my own preference. VB web application should also work.

Step 2: Add SignalR library

Open the application in Visual Studio, add SignalR library via NuGet package management.

Step 3: Create a hub in server

Add the following 2 classes in App_Code\Shared folder.

Step 4: Define notification method in client

In ISD, add the following JavaScript code to the EditRecord page's prologue.
Pay attention to the version numbers of jQuery and SignalR. You might download a different version from mine. Change them accordingly.

Step 5: Call notification from server

The best time to push the notification is after changes have been saved, i.e. after the transaction is committed successfully in database. In Visual Studio, override CommitTransaction() in EditRecord's page class.

Demo page

That's all to add the push notification. Click here to open a demo page. Open it in 2 or more different browsers. Click "Save" button in one browser, and all other browsers will popup the notification. Of course, there are rooms for improvement. For example,

• Use a less invasive notification, e.g. toastr instead of alert.
• Send record ID as a parameter in the notification method. Show notification only if the ID matches the current one in editing.

Conclusion

SignalR enables web applications to push content from server to client. This new dimension in server-client communication can push your ISD applications to the next level.

Apply Balanced Optimization Principles in DataStage Job Design

CodeProject

DataStage v8.5 introduced an optional add-on: balanced optimizer. The optimizer can redesign parallel jobs to improve performance. It applies the following principles in the redesign.

Minimize I/O and data movement
Reduce the amount of source data read by the job by performing computations within the source database. Where possible, move processing of data to the database and avoid extracting data just to process it and write it back to the same database.
Maximize optimization within source or target databases
Make use of the highly developed optimizations that databases achieve by using local indexes, statistics, and other specialized features.
Maximize parallelism
Take advantage of default InfoSphere DataStage behavior when reading and writing databases: use parallel interfaces and pipe the data through the job, so that data flows from source to target without being written to intermediate destinations.

The key takeaway is to do as much as possible within databases. It seems to me that an experienced DataStage developer could do a much better job in applying these principles to optimize a job than a piece of software. So let me give it a try on a real-world production job.

The original design is shown in Fig. 1. All data processing and transformation are performed within DataStage server, including,

1. two slowly changing dimension stages,
2. five lookup stages, and
3. three transformer stages for adding timestamp columns.

Eight Oracle connector stages serve as data sources/targets only, reading 5 source tables and writing 3 target tables within the same database. No other tasks are performed within the connectors. This type of job is an ideal candidate for balanced optimization.

Figure 1: Original job design

The optimized job is shown in Fig. 2. A single Oracle connector stage replaces all 20 processing and data stages. The main data stream is bulk-loaded into a temp table (Fig. 3), and processed in after-SQL.

Figure 2: Redesigned job applying the principles of Balanced Optimization

Figure 3: Oracle connector configuration in the optimized job

Here are the after-SQL statements.

The performance gain is dramatic, dropping from ~30 seconds all the way down to ~3 seconds. In addition, I get two extra bonuses for free.

1. The original job is not transactional. If it failed in the middle, the target tables might be in inconsistent states with partial results. In order to recover from the job failure, I need to do some cleanup. The optimized job, on the other hand, is transactional. The update to the target tables is carried out in the after-SQL statements. If they fail, the target tables are automatically rolled back.
2. If the job fails because of data quality issues, such as lookup failure or unique constraint violation, it is not easy to find the offending rows in the original design. In the optimized design, all intermediate results are kept in the temp table within the same database as the target tables. It is fairly straightforward to pinpoint the offending rows by joining the temp and the target tables.

In conclusion, balanced optimization (BO) can greatly improve job performance, as well as simplify maintenance. However, it is not necessary to pay the additional license fee if the BO principles can been applied in job design by developers.

Scrollable Table Control with Fixed Header

CodeProject

Introduction

ISD provides a scrollable table control for displaying a lot of rows in a window with fixed height. It lacks, however, the most desirable feature of a scrollable table: a fixed table header. Here is a screenshot and a demo page. The previous versions of ISD do have a fixed-header scrollable table control. But it only works in IE, not in Chrome or Firefox.

A scrollable table is, in my opinion, not a good UI design in the first place. It significantly slows down initial page load. The issue (or the header) can be easily fixed. If your client or manager asks for a scrollable table, try your best to steer them away from it. If you can't, then follow these steps to implement a fixed-header scrollable table.

Implementation

Step 1: Prepare a built-in scrollable table

Add a built-in scrollable table control onto your page. Split the merged header cell above row buttons. This is an important step. Any merged cell will screw up column width calculation while fixing the header row.

Step 2: Download JavaScript libraries

ISD has added out-of-box jQuery support in latest versions. If you still use old versions without jQuery, you can download it from jQuery website, and include it in master pages.

Another library is the jQuery Scrollable Table Plugin. Download it from its website, and put it under your project's root folder.

Step 3: Modify Styles.css

Add the following classes to the Styles.css file.

Step 4: Add JavaScript code to page prologue

Add the following JavaScript code to the page prologue.

Build and run.

Conclusion

Here is what you get on screen, and a demo page. Please note the long delay (~10 seconds) at initial page load, during which the table header appears not fixed. This endorses my previous suggestion: avoid scrollable tables as much as you can.

DataStage Batch Sparse Lookup

CodeProject

Introduction

DataStage sparse lookup is considered an expensive operation because of a round-trip database query for each incoming row. It is appropriate if the following 2 conditions met.

1. The size of reference table is huge, i.e. more than millions of rows. If the reference table is small enough to fit into memory entirely, normal lookup is a better choice.
2. The number of input rows is less than 1% of the reference table. Otherwise, use a Join stage.

Is it possible to speedup sparse lookup by sending queries to database in batches of 10, 20 or 50 rows? In other words, instead of sending the following SQL to database for each incoming row,

SELECT some_columns FROM my_table WHERE my_table.id_col = orchestrate.row_value

can we send one query for multiple rows like this?

 SELECT some_columns FROM my_table WHERE my_table.id_col in (orchestrate.row_value_list)

Solution

In order to make the 2nd query work, we need 2 tricks. First, we need to concatenate values from multiple rows into a single string, separated by a delimiter (e.g. comma). I am not sure how to do this in DataStage v8.1 or earlier. Not impossible, but rather complicated. Since v8.5, the Transformer stage has loop capability, which makes the task of concatenating multiple rows much easier.

The 2nd trick is to, at database side, split the value list from a comma-delimited string into an array. If we simply plug the original string into the 2nd query, the database will interpret it literally as a single value. If only there is a standard SQL function equivalent to string.split() in C# or Java. Different databases use their own tricks to achieve string.split(). I will use Oracle in my example implementation.

Implementation

Job overview

The example implementation generates 50k rows using a Row Generator stage. Each row has a key column and a value column. The rows are duplicated in Transformer_25. One copy is branched to Transformer_7, where multiple rows of the keys are concatenated. The number of rows in each concatenation is set by a job parameter, #BATCH_SIZE#. The concatenated keys are then sent to Lookup_0 for sparse lookup against an Oracle table with 5m rows. The lookup results are merged back to the original stream in Lookup_16.

Concatenate multiple rows in a Transformer loop

Define the following stage variables and loop condition to concatenate multiple rows.

Variable	Data Type	Derivation
BatchCount	Integer	IF IsLast THEN 1 ELSE BatchCount + 1
IsLast	Bit	LastRow() or BatchCount = BATCH_SIZE
FinalList	String(4000)	IF IsLast THEN TempList : DecimalToString(DSLink21.NUM_KEY,"suppress_zero") ELSE ""
TempList	String(4000)	IF IsLast THEN "" ELSE TempList : DecimalToString(DSLink21.NUM_KEY, "suppress_zero") : ","

Loop condition: @ITERATION = 1 and IsLast

Batch sparse lookup

The concatenated keys need to be split in the Oracle connector. Spliting a comma-delimited string in Oracle can be done using reqexp_substr() function and recursive query. For example,

SELECT regexp_substr('A,B,C', '[^,]+', 1, level) from dual connect by level <= regexp_count('A,B,C', ',') + 1

This is how to setup the query in the Oracle connector stage.

Test run

A test run with BATCH_SIZE of 50 is shown below. DSLink4 indicated that 1,000 queries, instead of 50,000, were sent to the database.

Performance Evaluation

Another job (shown below) using regular sparse lookup was compare the performance of batch sparse lookup.

The result of the comparison is summarized in the chart below. Batch sparse lookup can cut down job running time by ~75%. The most effective batch size is between 20 to 50.