Gotcha #7 - AppDynamics and the Mysterious Case of the Vanishing Cookie

Not much of a mystery today in this article (the answer is alluded to in the title) but the process of identifying the problem, discovering why it was a problem, and then fixing it was quite a challenge.

Today's Gotcha is about Rational Performance Tester (v8.5.1.2 for anyone interested) and a vexing problem with successful test execution.

The problem originally arose when we discovered that our tests weren't always succeeding. After executing a schedule we would consistently have a large number of pages that wouldn't have 100% success status codes (located on the Server Health Detail view). There would be a few with 100% success rate, but consistently nearly every page was hovering between 94 and 98%. In some larger tests this would drop further, into the 80's or high 70's, but even a simple low volume test would have most pages at less than 100%.

To get a better handle on why and when these page errors were happening, I added a new counter to the default Page Throughput / Page Hit Rate graph (Add/Remove Performance Counters --> Page Performance Counter... --> Status Code Successes --> Percent Page Status Code Success [for Interval]).

Suddenly we could see this wavering line appear on the graph that would start off at 100%, dip sharply at the beginning of the test, before returning back up near 100% and stay fluctuating in the high 90% range.

Baffling when the very same test scripts in the past against the same application would run at 100% without trouble. Doubly-baffling when running the script as a standalone would work perfectly fine.

Following the Clues

The first clue came when we decided to rerun one of our test schedules and monitor the Test Execution of a user thread. Since the failures were reasonably consistent across all pages, chances are we might be able to catch it in the act.

After a couple of hours of watching successful page responses, we caught one. Right from the beginning of a new test script - a page failure. And then another, and another, and so on until every single page in that script failed in order. And the next script. And the next... but then the fourth script, for no apparent reason, began returning successful responses again.

Digging into the error, the only message we could find was a "Failed substitution ^csrfToken=.*$" message followed by a cascade of 500 - Server Error responses. The csrfToken appears in the HTTP Response header as a "Set-Cookie" value from the server (you can find an explanation of its purpose here). And for some reason, it wasn't being found.

Except... inspecting the response of that very first page - the csrfToken was right where it should be. Further digging into the application logs showed the same thing - on occasion our test would be sending an invalid csrfToken - the recorded token - which happens when the substitution couldn't be made.

But it was right there in the Response Header - right where we expected it to be. Except, not quite...

Smoking Gun

It looked like it was right where it was supposed to be - in among a half-dozen other cookies being set by the server. But a careful comparison with the original recording showed that it was 1 line down. There was a NEW Set-Cookie value that was inserted in the list just ahead of the csrfToken.

Inspecting and comparing successful test executions with failed ones showed that this new mysterious cookie only appeared in the failed tests, and wasn't to be found in the successful ones.

The server was randomly inserting a new cookie in its response... some of the time. And it was enough, by shifting the order of the Set-Cookie headers for RPT fail when trying to identify the csrfToken.

The culprit was a cookie called ADRUM_BTs and it looks like the following:

Set-Cookie: ADRUM_BTs="R:0|s:f"; Version=1; Max-Age=30; Expires=...date...; Path=/; Secure

There was no consistency in when or where it would appear. It would show up for a script execution or three, then disappear once more.

Solution

It turns out that we weren't the only ones to run the Mysterious Case of the Vanishing Cookie - other people had identified ADRUM_BTs as playing havoc with their scripting efforts.

The cookie in question belongs to AppDynamics End User Monitoring javascript package, a tool that can be used to monitor user behaviour and web application performance from the user's perspective. It was a new feature in the AppDynamics package that had been turned on in our Performance Testing environment recently to try and improve our measurement capabilities.

In the end, there was no way to remove this new cookie or adjust the system to consistently distribute it. In order to get our Performance Testing back up and running we had to remove all End-user Monitoring components - and suddenly our vanishing cookie was gone for the last time.

Gotcha #6 - Sometimes Naive Caching is Worse than None

There is a popular proverb about good intentions and the road to hell that is well known, but an alternate version of that proverb is phrased as "Hell is full of good meanings, but Heaven is full of good works". Unfortunately even good works can lead down the wrong path when they are undertaken with insufficient consideration for their impact.

This post is about one such work - about the implementation of a caching strategy on a large platform that is widely used across the industry. The work itself is good, smart, and done with significant forethought. However under certain conditions, the choices made ended up creating a system worse than if nothing had been done at all. This post explores the choices, circumstances, and effect that implementing an intelligent - but naive - caching system had on one particular project and why the result ended up being worse than if it had never been.

The names of all organizations, people, and software involved have been withheld.

6 Days to Go-Live

A war room had been convened. The production deployment of a new Public Access System (PAS) had gone smoothly over the weekend and everything was prepared to turn the switch and make it accessible in less than a week. The public information campaign had already been underway for some time, advertising the date, and getting the word out that this new offering was forthcoming. However, validation of the deployment had hit a snag - one member of the QA team had noticed something wrong.

At a critical step while making a new purchase on the PAS, the system would appear to pause. After a long delay (45s) it would return with a technical error. Repeating this process several times in a row would produce the same error across multiple sessions, across multiple machines. And then it would go away and everything would work smoothly again.

Until it happened again about an hour later.

And again an hour after that.

And then it went away. Combinations of testing, several people trying to test it in a variety of ways, and using precisely the same data - or different data - it couldn't be reproduced.

Until the next morning, when the problem showed up anew and again the following hour, before disappearing once more.

So began an urgent search to understand the problem began, testing the system, combing the logs, reviewing all the code tracing and analytic tools at our disposal to isolate and resolve the problem - in production.

Underlying this effort was the question - why wasn't this problem seen in any other environment? Through the myriads of Dev and QA environments, through multiple test cycles and performance testing, this pattern of problems had never been encountered before. At times a similar issue would be seen in isolated circumstances immediately following the restart of an environment, but once it was initialized everything was good.

So why was it happening in production, and nowhere else?

Architecture

The PAS is a clustered three-tier system using RESTful services to communicate between the web server and the Back-end Application (BEA). The BEA contains all the business code required to verify, validate, and persist purchase information.

The PAS cluster contains 2 nodes, and the BEA cluster contains 8, separated into two group of 4 - one group dedicated to the PAS and the other for direct Intranet access. As a true RESTful interface, no session information is tracked between requests to the BEA cluster. The load balancer provides a true round-robin.

When the system is active, most of the processing load takes place on the BEA cluster, the PAS operates as little more than a view rendering layer. When a page request comes in to the PAS, it opens a connection to one of the BEA nodes - passes along the request - and waits for a response. In the case where the BEA node takes an abnormally long time, or if there is a failure and no response is generated - the connection has a 45s timeout. If it takes longer than 45s, the PAS would close the connection and display a technical error message encouraging the customer to call their local company representative to complete the purchase.

The Trail of Evidence
When the problem was first raised, there was a great deal of speculation as to the possible cause. Database issues, connection issues, misconfiguration, load balancer issues - all possible sources of this technical error, but all things that would need to be verified and eliminated.

There were a number of observations made about the system that were unusual or worth investigating - including hung database threads and the presence of a query in the AWR (query performance statistics) report that was known to be problematic in other environments. But in hindsight there was only one that would prove to be important to the actual cause: the problem only happened in the morning when full-time employees arrived for work and began using the BEA system directly via the Intranet.

Now the Intranet BEA nodes are physically separate from the ones used by the PAS, but they do share a database. The problem only occurred during the morning hours when load on the BEA nodes and the database were at their highest. Not high enough to cause a bottleneck or result in performance problems - but not insignificant either. More on this later.

The most important piece of information came from our enterprise performance monitoring tool that was installed on all production servers. By drilling down, identifying, and isolating a single request that failed we could examine the performance of the BEA code for that single request.

And what we discovered was a function call that took 24s that involved retrieving a large but critical piece of reference data from the database, and storing it in the application cache. By itself, not enough to cause a timeout and result in a technical error - but in a confluence of circumstances this piece of reference data was then being used for a large and complex set of calculations that took ~20s to complete and in this particular instance was just barely slow enough to cross the 45s timeout threshold and cause the technical error to be displayed. 

Now this isn't unusual - the median time for this particular load-and-store operation was about 15-18s, and once it was complete the data would be cached and any future accesses would take 1/10th of a second or less. But in the situation where it did have to retrieve the data from the database while the db was under significant load caused it to slow just enough to trigger the problem.

But why was it happening at all? Why wasn't it happening elsewhere? And why wasn't it discovered during Performance Testing.

Unexpected Caching

The answer came in a conference call to the vendor. The cache on the BEA system operated as follows:

1. Objects in the cache are marked as stale at intervals of 15 minutes if they have not been accessed during that timeframe.
2. If an object was already marked as stale when the interval arrived, it would be evicted from the cache.
3. Every 4th interval (once per hour) all objects in the cache would be evicted regardless of their state.

This cache interval was configurable to a maximum of 30 minute intervals, extending the hourly global eviction from 1 hour to 2 hours - but no further. This means that every hour all reference data would be purged from the BEA system's memory and need to be reloaded the next time it was accessed... on each node.

The Core Problem

In order for the problem to occur, the following had to happen:

1. The BEA's cache was empty on the nodes that were used by the PAS.
2. The other Intranet BEA nodes had to be actively used by a large number of concurrent users to put sufficient load on the database to cause a retrieval delay for the large amount of reference data.
3. The PAS had to be inactive, with only 1 or 2 users online performing a new purchase. Other operations would cause the reference data to be loaded but not perform the complex calculations that would cause the response delay to exceed 45s.

An unlikely scenario it seems, right?

Except...

1. The BEA cache would be emptied on the hour, every hour.
2. The Intranet BEA nodes were being used by employees for their normal job, and would be heavily used every weekday for several hours during the morning.
3. The PAS was a limited-scope rollout, the only functions that it performed were New Purchase, and Continue a Saved Purchase.
4. The PAS was so new, that the initial expected load was only a handful of users per day. So the likelihood of having only 1-2 users performing a new purchase was very high.

These circumstances meant that it was very likely that any user of the system would run into the situation where all three conditions were met and cause their purchase attempt to fail.

This problem was further compounded by the round-robin load balancer, and the fact that the reference data needed to be loaded on each node separately. This means that not only was a user likely to encounter the technical error message, but if they attempted to retry their purchase their next page request would be directed to a different BEA node whose cache was still empty resulting in the same error message again.

The expected result is that for a majority of potential customers, each would need to retry their purchase five times before successfully completing it.

Why Wasn't This Caught?

The simple answer is the other environments were not busy enough. Dev, QA, Staging... all of these environments did not have sufficient database usage to cause enough delay to cross the 45s delay threshold. In addition, these environments typically were not configured in the dual-cluster architecture of production, so any activity directly on a BEA node would cause the reference data to be loaded into cache. The chances of a PAS new purchase occurring with an active database were essentially non-existent.

The exception was the Performance Testing environment which did have a fully configured production system, and sufficient database traffic during a test to cause the problem. The reason it wasn't discovered was that the PAS traffic was too high. Even a half-dozen simultaneous users was sufficient to cache the reference data across all the BEA nodes - and even if the error was observed near the beginning of the test once or twice, a few minor functional errors are not unexpected during a performance test and the absence of reoccurence over the next hour would be simply dismissed as an anomaly.

As soon as the circumstances of the problem were discovered, it became a simple matter to intentionally reproduce in Performance Test by adding load to the Intranet BEA nodes and manually attempting to test a new purchase.

Why Did This Happen?

This problem is caused directly by the implementation of a naive caching framework on the BEA system. The purpose behind clearing the cache regularly would be to ensure that reference data (if updated) could only be stale for a certain amount of time before being refreshed. The assumption was that the cache could be cleared every hour without causing a problem, which ended up not being the case. The BEA system had no facility with which to preload reference data, no functionality allowing it to be refreshed periodically except through eviction and reload on-demand, and no ability to persist reference data in a long-term no-expiry cache.

The reason this was more than just a normal problem, is that by implementing a limited cache the system was successfully able to mask the existence of the problem from developers, qa, and performance testing. The naive caching strategy allowed it to work well under load most of the time, and only appear as a problem in very specific circumstances.

Had there been no cache on the BEA system, this problem would have become abundantly clear in performance testing by resulting in terrible performance for that operation and also causing the error rate to approach 100%. But by masking the issue it managed to survive until mere days before go-live when it was discovered entirely by accident.

The Solution

The permanent solution will be to modify the BEA system to build a more sensible multi-use caching strategy with the ability to preload and periodically refresh reference data independent of a user request. Temporarily it has been resolved with an automated process that makes an appropriate service request to each BEA node every minute that triggers the reference data reload if it has been evicted in an attempt to complete that reload before a user encounters it.

Gotcha #5 - Network Performance, CSS, and HTML5

Today's article comes courtesy of understanding your customer's environment, and that emphasizing style over substance can break the bank.

Our story starts with an urgent request for assistance from a system administrator, a minor application update has resulted in a nearly five-fold increase in total network traffic at a remote office. On Monday morning, when the morning shift arrived network traffic began to spike. It grew, and grew, far beyond normal traffic levels for the office and stayed there, night and day, until the last shift signed off on Friday evening.



Think about this for a moment.

The application's network traffic did not increase five-fold. The entire office's network traffic consumption increased from ~2Mbps (24-hour average) to nearly 9Mbps (24-hour average) with peaks topping out at 10Mbps.

The first stage is always denial.

How could a minor update to a server-based web application that mostly consisted of a couple minor bug fixes, one new summary screen, and some UI tweaks be the problem? It must be something else, there isn't anything new or unusual in the application update.

Well...

Step 1: Isolating the Problem
Easy enough to do, by using more detailed reports we could see the initial spike in unusual network activity began at 10am on the Friday before, the same time as the outage window when the application update was deployed.

Also, there were no other maintenance activities ongoing at that time, no file transfers, and no other system updates scheduled or otherwise.

Hmm...

Step 2: Reproducing the Issue
To confirm, we conducted a test where all instances of the application on every workstation on the site was closed, and by so doing network traffic returned to its normal (low) stable state.

One by one, each workstation re-loaded the application and by time the last workstation was reconnected the network traffic was back at its five-fold peak. It was during this process of re-loading that the system administrator noticed something unusual. A new UI feature had been added to some screens, when a specific piece of data was in a warning state - the text box would flash orange. And every time a new screen loaded that happened to display one of these flashing orange boxes, network traffic would spike sharply.

In the end, the system administrator noticed 10 out of more than 100 workstations happened to be displaying this flashing orange box. Returning to shut down just these 10 flashing displays suddenly dropped network traffic back to normal once more. When they were re-loaded, network traffic would spike once more.

Step 3: Cause and Solution
So how can a simple CSS and HTML5 flashing text box on 10 screen possibly start pushing 4 times the total network traffic of an office of more than 100? The technique to highlight a text box had been used elsewhere in the application already, and the style control was rendered entirely in-browser with no additional server requests being made.

How can a box no larger than 400px by 300px with a lovely fade-in/fade-out HTML5 animation possibly consume 800Kbps per screen?

The Epiphany

Animated. For the first time in this application, the developer (to satisfy a requested requirement) used a simple animation cycle to flash the control orange on a 4-second cycle. It was a lovely effect, understated, but unmistakable and was easily visible when displayed on a large screen from across the room.

What wasn't clear however, was that each workstation did not use a browser to view the application. Instead, each workstation used a Remote Desktop (RDP) session to a centralized (offsite) server which then connected to the application in a browser window.

Instead of being rendered in a local browser session and consuming no network resources at all, the beautifully animated flashing textbox was being rendered and streamed as uncompressed full-motion video over the network to the site office.

10 workstations streaming uncompressed video for 24-hours a day, 5 days a week.

Step 4: Resolution
In the end the problem was easy to solve. Rip out the animation, and using a simple steady-state orange highlight to communicate the information.

But it was an important lesson. You must be aware of how your customer is using the tools that you build, and how the simplest of design choices can cause a major impact under the wrong circumstances.

Gotcha #4 - WPF - Restore Events and Media Elements

Today's article comes courtesy of assisting Bill Harris' (Dubious Quality) work on GridIron Solitaire. The original problem comes from identifying a bug that sound effects were not playing when a system returns from sleep mode if the game had been left open.

Some basic details on the application: GridIron Solitaire uses Windows Presentation Foundation (WPF) for its UI framework, it has been developed in VisualBasic 2010, and sound effects are played by using System.Windows.Controls.MediaElement class.

The problem in this case was that sound effects that had been initialized prior to the system enter sleep mode did not continue playing after the system returned to an active state. Sounds initialized after the system returned to an active state continued to play fine. When first presented with this problem it did not seem to be a terribly difficult one to solve, nor an uncommon one to encounter. There are many games released by major studios that have difficulty handling system sleep and restore, and even minimization or alt-tab window switching. The most recent experience I have had with these problems include Civilization V in which I have encountered alt-tab artifacts (game remains visible in the background), as well as restore from minimization (texture corruption) and restore from sleep (fatal crash) problems.

The first line of investigation that we followed was the possibility that the MediaElement was failing to play after a restore, so we constructed a plan to catch the MediaElement.MediaFailed event and reset the MediaElement in that instance. On the positive side this approach solved an unrelated application crash problem caused by a missing media file, but it was soon determined that the MediaFailed event was not being fired when the sounds failed to play after a system restore.

PowerModeChanged Event Solution #1
Further research revealed that a MediaElement's Source property becomes invalidated during the sleep/restore cycle, and that resetting the Source property and restarting the MediaElement. One of the suggestions was to catch the PowerModeChanged Event and check for PowerModes.Suspend and PowerModes.Resume states, any suspend/resume code needed by your application can be performed in this block. The resulting code catches the PowerModeChanged Event, and on Resume resets every MediaElement Source property to its correct value.

Private Sub SystemEvents_PowerModeChanged(ByVal sender As Object, ByVal e As PowerModeChangedEventArgs)
    Select Case e.Mode
        Case PowerModes.Resume
            Reinitialize_Sounds()

        Case PowerModes.StatusChange

        Case PowerModes.Suspend
            BackgroundEffectALoopPlayer.Pause()
            BackgroundEffectBPlayer.Pause()
    End Select
End Sub

Private Sub Reinitialize_Sounds()
    SoundEffectA.Source = Nothing
    SoundEffectA.Source = New Uri("Resources/SoundEffectA.mp3", UriKind.Relative)
    '...etc...
End Sub

The effect of this code seemed to be satisfactory at first. Although it slowed down the time for the application to restore from a suspended state, it wasn't a particular problem as additional processing time required to restore from a suspended state is normally accepted and expected. A minor issue was that ongoing sounds would not restart until the next time the code required them to be played, which was easily resolved by programmatically restarting ongoing sounds within the restore code.

However, it was only observed later - and generally on lower-end systems - that in some cases a few sound effects were not being restarted after restore. An additional confounding factor was that the sound effects that were not returning were not consistent, an apparent random selection of 2 or 3 would not resume and this effect was not reproducible in debug mode.

Application_Activated Event Solution #2
Considering the possibility that the PowerModeChanged Event was not firing correctly, or that there was some other conflict regarding MediaElement objects that was occurring after the PowerModeChanged Event the proposed alternative was to move the restore code into an Application_Activated Event handler. The Application_Activated Event is fired on a different but related and less-specific set of conditions to the PowerModeChanged Event. The theory was that perhaps the systems where the continuing sound failure was being observed were tablet and netbook systems which may be impacting the events being fired.

Once the restore code was transferred and redeployed, the same issues were observed occurring in precisely the same circumstances as the PowerModeChanged Event solution.

Brute-force Solution #3
In an attempt to find any way past the problem, Bill attempted to brute-force the solution by forcing every sound to reload the Source property every time that it was activated. Although the performance of this approach was going to be unacceptable, the lag time for loading a sound every time it is played would be noticeable to the player, as a debugging approach it was reasonable.

The result: slow, but successful. Despite every sound effect suffering from noticeable lag, after a suspend/restore cycle every sound effect returned and played correctly on every test system configuration.

Problem Identification
It was at this point that the cause of the problem was identified. The process that restores an application from a suspended state causes MediaElement sources to be invalidated. The problem with using either the PowerModeChanged Event or the Application_Activated Event for setting the source property, is that the event handlers are operating on a different thread than the application restore process and these threads are entering into a race condition.

In the first two solutions, on slower systems a few of the lighter-weight sound effects were being loaded into their MediaElement objects before the application restore process that invalidates the MediaElement sources was completed. As a result a few of the MediaElement sources were being invalidated after they had already been corrected - causing a few of the sound effects to fail in an inconsistent and unpredictable manner.

On-demand Resource Loading Solution #4
The final solution is to enforce on-demand resource loading and caching whenever a MediaElement is required to play a sound effect, moving the responsibility for checking the existence of a media source from the pre-loader to the code that plays the MediaElement itself.

As a result the PowerModeChanged Event handler is being used to nullify (set to Nothing) all MediaElement sources when a suspend event occurs. Then every time a MediaElement is about to be played the code first rechecks its source to ensure that it is not null, and reloads the source if it does not exist before playing it.

Private Sub SystemEvents_PowerModeChanged(ByVal sender As Object, ByVal e As PowerModeChangedEventArgs)
    Select Case e.Mode
        Case PowerModes.Resume

        Case PowerModes.StatusChange

        Case PowerModes.Suspend
            SoundEffectA.Source = Nothing
            SoundEffectB.Source = Nothing
            '...etc...
    End Select
End Sub

'In sound effect Play code:
If SoundEffectA.Source = Nothing Then
    SoundEffectA.Source = New Uri("Resources/SoundEffectA.mp3", UriKind.Relative)
End If
SoundEffectA.Play()

The final effect is that sound effect failure has been eliminated, and a slight lag occurs the first time each sound effect is played immediately after a system restore, but returns to optimal performance soon after once each sound effect has been re-cached.

Gotcha - Teradata Nested Views & Functions

Here's another interesting "gotcha" involving Teradata v13.1 and how it handles metadata for views, this time when using multiple layered views and a custom function. We encountered this error when creating a summary-style view in our data warehouse to push data aggregation into the database layer instead of building the query in Cognos Framework Manager.

The exact Teradata error that we received while attempting to create a new View was:

REPLACE VIEW Failed. 3822: Cannot resolve column 'DATE_ENROLLED'. Specify table or view.

A very generic error message, but what is unique about this particular column in the View is that it is the only one that is being operated on by a function.

The structure of the View query is (very abbreviated) as follows:

REPLACE VIEW view_db.new_view
(DAY_KEY)AS
SELECT
    COMMON_DB.CONVERT_TIMESTAMP_TO_KEY(DATE_ENROLLED) AS DAY_KEY
FROM
view_db.person

The query by itself runs without any problems and returns the correct results. But as soon as I put it into a CREATE or REPLACE VIEW statement it failed. Please note that the source of the query (view_db.person) is a View as well that merges records together from a source table (data_db.person) to produce an accurate list of current people.

Now during our investigation we discovered that replacing the source of the query (view_db.person) by referencing the source table of that view (data_db.person) we did not see the error anymore. The column name was unaffected because DATE_ENROLLED is a field in both the View and Table that is unmodified. However this was not a solution because it defeated the purpose of building the view_db.person View in the first place, and shoe-horning the query into a sub-select would be very complex and nearly unmaintainable.

So the structure of our new view and the column source was structured as follows:

view_db.new_view [DAY_KEY]
  - COMMON_DB.CONVERT_TIMESTAMP (function)
      - view_db.person [DATE_ENROLLED]
          - data_db.person [DATE_ENROLLED]

The Solution

We suspect that the Teradata database is encountering problems resolving the source of the DATE_ENROLLED column because of the identical naming between the view_db.person and the data_db.person which is confounded by applying the function call.

We were able to resolve the issue by creating a special sub-select query on the view_db.person that has the sole purpose of renaming the DATE_ENROLLED column.

The resulting fixed REPLACE VIEW statement is as follows:

REPLACE VIEW view_db.new_view
(DAY_KEY)
AS
SELECT
  COMMON_DB.CONVERT_TIMESTAMP_TO_KEY(DATE_ENROLLED_VIEW) AS DAY_KEY
FROM
  (SELECT DATE_ENROLLED AS DATE_ENROLLED_VIEW FROM view_db.person) a

This allowed the DATE_ENROLLED column to be correctly resolved and the view to be created successfully.

Gotcha - Teradata Views

Encountered another interesting "gotcha" again involving Teradata v13.1 and how it handles metadata for views. We encountered this issue within Cognos Framework Manager v10.1.1 when attempting to use a view created in Teradata as a query subject.

The exact Cognos error that we received was:

RQP-DEF-0177 An error occurred while performing operation 'sqlScrollBulkFetch' status='-9'.
UDQ-SQL-0107 A general exception has occurred during the operation "fetch".
[Teradata][ODBC Teradata Driver][Teradata Database] Internal error: Please do not resubmit the last request. SubCode, CrashCode:

After running a UDA trace and a Teradata ODBC driver trace and reviewing the log files we discovered a statement that was causing the error message:

HELP COLUMN "DB_NAME"."VIEW_NAME"."PK_ID_FIELD_NAME"

Running this query manually on the database gave a more detailed, but still obscure error message:

HELP Failed. 3610: Internal error: Please do not resubmit the last request. SubCode, CrashCode:

The view itself that we were debugging was extremely complex, but after some experimentation I was able to produce the following simple view definition that still caused the error.

CREATE VIEW DB_NAME.VIEW_NAME AS
SELECT
T1.FIELD1,T2.PK_ID_FIELD_NAME
FROM
DB_NAME.PARENT_TABLE T1,
DB_NAME.CHILD_TABLE T2
WHERE T1.FK_ID_FIELD_NAME = T2.PK_ID_FIELD_NAME
;

Simple right? Gotcha #2 is that this error only appeared on 2 of our 3 environments, Development and UAT showed this issue, but our SystemTest environment worked without a problem.

We were able to devise a temporary workaround, because the HELP query specifically identified a problem with the PK_ID_FIELD_NAME on the CHILD_TABLE we were able to replace it by using the FK_ID_FIELD_NAME on the PARENT_TABLE which fixed the error message. However this was not a solution to the problem, because logically retrieving the primary key of a joined table in a view should NOT cause a problem.

The Solution

The exact reason for why this problem was happening on 2 out of 3 of our systems is still unknown, we suspect there is corrupt or missing column metadata that was causing the inconsistency. Nevertheless we did find a solution to the problem.

The problem was resolved by explicitly naming the view's columns in the view definition. For whatever reason, this bypassed the metadata error and allowed the view to be used in both Cognos and Teradata SQL Assistant. Below is the fixed view definition with the changes highlighted in green:

CREATE VIEW DB_NAME.VIEW_NAME (FIELD1, PK_ID_FIELD_NAME) AS
SELECT
T1.FIELD1,T2.PK_ID_FIELD_NAME
FROM
DB_NAME.PARENT_TABLE T1,
DB_NAME.CHILD_TABLE T2
WHERE T1.FK_ID_FIELD_NAME = T2.PK_ID_FIELD_NAME
;

This allowed the HELP COLUMN metadata to be generated correctly for the view and fixed this issue without having to restructure the view query itself.

Gotcha: Teradata Union Queries

Encountered an interesting "gotcha" today involving Teradata v13.1 and how it handles data types with UNION queries. The context of this discovery was made within Cognos Report Studio v10.1.1 but we discovered it was a global issue within the generated SQL, not anything native to Cognos itself.

The problem was to do with using hard-coded string values within a UNION statement. If the string value is in the first component of a UNION then Teradata will truncate any column joined to that string via the UNION to the length of the string.

Here is my example situation:

Table foo:
foo_id INTEGER Primary Key
foo_name VARCHAR(15)

Table bar:
bar_id INTEGER Primary Key
name VARCHAR(100)

SELECT 'a-string' as name, foo_id FROM foo
UNION
SELECT name, bar_id FROM bar

Teradata will auto-detect the data type of 'a-string' as a VARCHAR(8) and will then cast bar.name as a VARCHAR(8) which will cause it to truncate anything beyond the first 8 characters. So a value of 'Hello World!' within the column bar.name will display as 'Hello Wo' in the above query.

This truncation does not take place if we replace 'a-string' with the column foo_name which is a VARCHAR(15). If we use foo_name instead, Teradata will correctly detect the largest data type from both sides of the query (VARCHAR(15) and VARCHAR(100)) and will cast all values as a VARCHAR(100) to prevent truncation.

In order to workaround this issue we must explicitly cast the hardcoded string 'a-string' as a datatype sufficiently long to contain any joined data. In this case CAST('a-string' AS VARCHAR(100)) is sufficient to resolve the truncation issue.