Re: convert LF's to SQL - LF's have Select/Omit

On 16 Apr 2013 12:07, TheBorg wrote:

I believe that the record format id problem was fixed with a PTF, but
no doubt Mr. Pence will have something to say about this topic.

There is not just a "record format id problem". The problems arose from a design flaw. All file creations should be performed by a single arbiter; done! That is QDBCRTFI. However all inputs should be validated\adjusted by that arbiter to ensure consistency; fail! The QDBCRTFI does not actually perform all the work necessary to ensure consistency. Thus regardless if DDS has done the wrong thing forever, it is deemed /correct/ and the SQL inputs will remain the same but must be masked to mimic the effects for the DDS compiler.

So the source of the problem is that both the DDS compiler and the SQL DDL processor generate the internal file and record format definitions, then effectively, merely passes them to the DB to effect their creation. If the DB create-file processor does not ensure consistency, or demand of those passing to it the creation-templates to provide consistent inputs, then such problems could persist. A couple PTFs fixed some known inconsistencies that were identified with certain data types and\or attributes of columns, as being different between DDS and SQL. In theory, new issues could come or old issues not yet reported may be found.

As for old issues eventually exposed... The last I recall, there was yet another detected difference [sometime in 2012; reported here], but the person reporting the issue to the service provider received a response from /IBM/ that indicated nothing would be done to correct the issue; i.e. without a demand by the user to move IBM to create an APAR, that IBM was unlikely to do again what was done with a couple past nearly identical issues, whereby because the DDS was setting incorrect stuff in the creation templates that the DB had /effectively/ incorrectly rolled into the hash.... would be masked in the SQL created request to make that SQL request effect the same bad, but now consistent hash value.

Because the SQL does not care about level identifiers, i.e. the SQL programs are unaffected by any changes to the value. Even generated random values for the levelId would not cause an issue, because the next time they re-create the file with that random RcdFmt LvlId, the SQL program will function according to the attributes of the columns, irrespective of the level identifier hash. FWiW, for several releases the FmtLvlId for an SQL VIEW was always blank; an issue that I /resolved/ at some point, which then led to many more difficulties :-( including exposing similar problems to what were found several releases later for the columns with date\time\timestamp data types. IIRC the resolution was to mask any data not relevant to the hash. But if ever there is data that is relevant to the hash which is different between DDS and SQL, there is a new or newly-found problem.

Because the SQL does not care about the hash is why the DDL created objects can be modified to generate different hash values than they had previously with no impact to SQL applications. However any RLA applications that depend on the hash to identify level check, if they had converted to use SQL for files, but had not yet changed their application(s) to use SQL for data access, then they are impacted by the "correction" to the hash.