Re: PF Compiled Files with Dictionaries vs. SQL-Created Tables

On 2/2/11 5:54 AM, Gary Thompson wrote:

You might want to know about relative performance of tables created
using SQL DDL vs DDS:
http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_Performance_DDS_SQL.pdf

I have an extensive understanding of that, and more. For example, I am even aware of and understand the means by which the data validation is implemented in the LIC "cursor" object; LIC internals, not published information. I also have years of experience supporting the DB2 for i, DB2/400, and its unnamed predecessor. Knowing that, perhaps readers can even trust that my recommendations have value beyond the casual observer\reader of some published information.

With regard to changing from DDS to DDL, the performance claims are highly overstated if only for failing to temper their claims with appropriate *warnings* for effecting such changes without full understanding of the implications. I have been in the position of supporting many who have made [small to massive] moves to DDL without full understanding of the consequences, based solely on some simplistic claim for [radically] improved performance. Those were unhappy customers, and so I make attempts to warn others against making possibly a big mistake by effecting change without both a "specific goal" and having first investigated to "understand what will be lost and what will be gained by the change." As alluded, some actually had to move back to DDS to "recover" from their changes; surely not a move to be made on a whim IMNSHO.

Regards, Chuck

CRPence on 02/01/2011 06:05 PM wrote:

And DDS even supports some features which SQL does not. I find
little value to change from DDS to use SQL DDL for existing
database *FILE objects, without some obvious need to access some
new feature provided by the TABLE; e.g. either data types supported
only in the SQL, or better integrity for numeric data. Without any
specific goal to be achieved by changing, what point is there in
change except change for the sake of change? That is, change
without justification is intuitively, not justified. Best to
understand what will be lost and what will be gained by the change,
to avoid remorse for having made the jump, and even more regrets
for having to move back in order to "recover" from the first
change. Changing "just because" to using SQL DDL versus DDS is, for
lack of a better word, daft.

<<SNIP>>