Dave's Adventures in Business Intelligence

Five easy steps to crash your Teradata system:

• Step 1: Upgrade to Teradata version 13
• Step 2: Recognize that with this version a “distinct” query no longer returns sorted results
• Step 3: On the advice of Teradata, reconfigure your box with the “regression” parameter that makes distinct queries behave the way they did in 6.2
• Step 4: Send a Business Objects LOV query to the database that includes a DISTINCT keyword and a where clause with a couple of constant values
• Step 5: Watch the system reboot

That’s about what happened to us a few days ago. It wasn’t pretty. It took a long time to get our production box upgraded (and this after seeing development and Q/A roll through the upgrades with flying colors). Once the upgrade was finally completed, we had catch-up work as far as batch processing to do. Once that was complete the users got back into the system… only to see it sporadically reboot.

With a personal computer or laptop, a sporadic reboot is often a loose connection or faulty piece of hardware. We had not experienced anything like this on our database servers. Ultimately someone figured out that the following query was at fault:

select DISTINCT table.column FROM table WHERE table.column in ('A','B')

That’s a fairly innocuous query, isn’t it? At first someone thought the table was corrupt. Nope, it checks out fine. Next someone suggested that the data in the table was bad. Nope, I can query it just fine. Then we thought maybe the fact that there were some special characters in the where clause was the problem. Nope, they work fine too. Finally it was narrowed all the way down to the fact that we had a DISTINCT clause with the where clause and the regression parameter set on our database.

Once we had identified the specific cause, there were three factors to consider. First, we needed the where clause on the LOV in order to deliver the proper list of choices to the business. So that could not really change. The Teradata DBA team had set a parameter to make TD13 work like TD6.2 as far as providing a “distinct” and sorted LOV result. Could we work with that instead? If I removed the “distinct” from the LOV query then Teradata no longer rebooted. However, the results of the LOV were no longer sorted, and that presented a new challenge.

Universe Parameters To The Rescue

There are a couple of universe parameters that were used to solve this issue. Side note: it was back in 6.0 (I believe) that the ability to override universe parameter settings within the Designer application first appeared. Prior to that version any parameter changes were applied via a configuration file. Any changes made then affected every universe that used that parameter file (there was a different one for each database). Today I can change individual universes, which is good. So what changes did I make?

Universe Parameter: DISTINCT

The first change was to change the setting for the DISTINCT parameter. By default this parameter value is set to DISTINCT, which seems redundant. 🙂 It essentially becomes DISTINCT=DISTINCT. However there is another option, that being GROUPBY. Note that there are no underscores or spaces in that phrase. With the setting updated to DISTINCT=GROUPBY my LOV queries no longer included the “distinct” keyword at the top of the query. Instead of this:

select DISTINCT table.column from table

I see this:

select table.column from table group by 1

There is another parameter that controls whether I see group by 1 or group by table.column and that depends on whether a database supports that syntax or not.

This change solved the first half of the issue. Without a “distinct” the LOV queries would no longer cause the server to reboot. This was certainly a positive step. However, making this change had an important side effect: the LOV results were unique because of the GROUP BY clause, but they were no longer sorted.

Universe Parameter: FORCE_SORTED_LOV

The second parameter that I changed was FORCE_SORTED_LOV. The default value is No and I changed it to Yes instead. The theory behind this parameter is simple: it would force the LOV results to be sorted. Before I changed the parameter my LOV query looked like this:

select table.column from table group by 1

After applying this change, my LOV query looked like this:

select table.column from table group by 1

Hm. 😯 Not what I expected. I was looking for an ORDER BY clause at the end of the query and I was not seeing one.

I went ahead and saved the universe and exported it to the Q/A system. We ran a few test queries (including one using the problem LOV definition mentioned above) and everything worked fine. Short LOV results were sorted. Long LOV result sets were also sorted, and they were still “paged” like I expected them to be. It still bothered me that I wasn’t seeing an ORDER BY clause in the LOV query definition, but obviously the sort was happening somewhere. We speculated that perhaps the Web Intelligence server was applying the sort which raised some concerns. LOV performance definitely suffered during the upgrade from 6.5 to XI and I didn’t want to introduce anything that would cause it to degrade further.

Fortunately one of the Teradata DBA team found the LOV queries in the logs, and despite the fact that no ORDER BY was showing up in the SQL when I viewed it via Designer, it was really there by the time Teradata got the query request.

Wrap Up

Ultimately the two universe parameters (DISTINCT and FORCE_SORTED_LOV) were used to change how LOV queries were generated, which allowed the Teradata DBA team to revert back to a standard version 13 installation (without the regression parameter turned on). I imagine that the Teradata engineering team is busy working on the bug and it will be fixed soon, so if you are using Teradata and looking at a version 13 upgrade at some point later this year you probably won’t encounter the same issue.

But it’s nice to know that we can help solve the issue by changing a few parameters on the universe side.