Feb 26 2010

Want To Crash Teradata? Give It Some LOV…

Categories: Universe Design Dave Rathbun @ 9:27 am

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.

13 Responses to “Want To Crash Teradata? Give It Some LOV…”

  1. Comment by Norm

    Dave,
    Great summary. I’ll pass this along to out DBA for future reference (and testing – just to be sure).

    BTW – In the paragraph before Wrap Up I think you meant to say no ORDER BY was showing up in the SQL.

  2. Comment by Yoav

    Hi Dave,

    Quite a story, I can only say that my production environment crushed a few weeks ago, why?

    I used the import wizard in order to transfer the production to test, while the import wizard was driving his miles it unexpectedly was terminated in the middle of the transaction and soon I found that all the universes in the Prod environment where missingā€¦
    How could that happen in a copy past tool?
    After speaking with the all mighty Sap tech support I found out that when import wizard
    Is interrupted during the import the universes are cut to the target environmentā€¦.

    A few walls were broken and after 12 hours of investigating the problem we distributed updated universe versions that where saved in our backupā€¦

    Conclusion: donā€™t import between environments when the content is big, just to a biar fileā€¦

    P.S

    What is difference to between sort in the LOV to using the parameter FORCE_SORTED_LOV ?

    Thanks

    Yoav

  3. Comment by Dave Rathbun

    Hi, Yoav, that’s quite a story. šŸ˜Æ

    The main difference between sorting an LOV and the FORCE_SORTED_LOV is that the second choice does every LOV at once. In order to apply a sort on each query, I would have to open each query and apply a sort. It’s much faster with the universe parameter.

  4. Comment by Thani

    Hi Dave,

    By changing this Universe parameter DISTINCT=GROUPBY, will this not affect the report queries as well or just to LOV query.
    And how does the ā€œRetrieve duplicate rowsā€ WebI property will work by un-checking it?

    Thanks.

  5. Comment by Yoav

    Thanks for the clarification,

    The parameter will be global and you canā€™t exclude a specific LOV object out of it, right ?

    Yoav

  6. Comment by Dave Rathbun

    I am not aware of a method to exclude any LOV from this sort parameter.

  7. Comment by Dave Rathbun

    Thani, welcome and thank you for your comment. The parameter does, in fact, change the behavior of the “retrieve duplicate rows” option. It will also use a GROUP BY rather than a DISTINCT. The net result should be the same.

  8. Comment by Dave Rathbun

    We have run into a snag, of course. Some of the LOV definitions in another universe use the ability to “sort by” an object that does not exist in the select clause. This results in invalid SQL once the distinct is removed and the group by is added because the additional column does not show up in the group by clause…

    No solution yet.

  9. Comment by Kuldeep

    Dave, Just wanted to know if you ever tried connecting BO to Netezza, If yes Do you plan to write on this topic.

  10. Comment by Dave Rathbun

    Hi, Kuldeep, thank you for your question. To this point in my career I have never managed to work with Netezza. Given that, I don’t expect to write much if anything about it.

  11. Comment by Boris

    Dave — interesting problem. Can you please post the exact system information:
    DBS version (including e-fix) and OS
    ODBC version and OS
    BOXI version and OS
    This may help everyone!

  12. Comment by Dave Rathbun

    Hi, Boris, and thanks for your question. I understand why you asked šŸ™‚ but I can tell you that the BOXI version is of no consequence as we were able to cause the exact same issue by running the SQL via SQL Assistant. The major version for Teradata was 13; I don’t know what fix level we were using at the time.

  13. Comment by JK

    Dave,
    Being an old ‘Teradactyl’, your article was interesting. The DISTINCT option (within TD) internally processes the data differently than the GROUP BY and in the process does an internal sort before it removes duplicate values. With the GROUP BY, it will remove all the duplicats on each unit of parallelism before further processing and the result set is not in any order – as per Relational Theory;-) I know from a BI tool perspective this might seem to be a ‘neat feature’ but in terms of an underlying DBMS, it should not be taken for granted – i.e. a sorted order result set, unless it is specified in an ORDR BY clause. It appears in TD13 that Teradata finally ‘fixed’ the differences in processing and used the internal logic of the GROUP BY. B-T-B, GROUP BY is ‘usually’ a lot faster internally and the final sort has a very low overhead as that is done in its parallelism result processiing logic. So if you are on earlier releases, the GROUP BY will generally be faster – BUT – you must use the ORDER BY if you want a sorted LOV!

    P.S. – beware of using ‘internal TD option settings’ that support a previous release functionality – they tend to be dropped/removed at later dates (FYI)