RE: CRTDUPOBJ vs SQL CREATE TABLE LIKE

From: R Bruce Hoffman

On Wed, 2006-09-27 at 10:53 -0500, Joe Pluta wrote:

I don't think the premise is incorrect at all.  In general, SQL on i5/OS

has

been pretty good about playing well with DDS.  And format level is

something

that, while it's a "DDS thing", there's no reason SQL shouldn't honor

it.

But SQL doesn't "honor" it... if the receiving field is compatible, the
data is retrieved, regardless of the format level id. There isn't a
level check in SQL. This is one reason why we don't use select * in
embedded SQL.

I don't understand your point, Bruce.  When SQL creates a table in i5/OS, it
assigns a format level ID.  Since format level ID doesn't have an SQL
equivalent, the most logical course would be to assign the format level ID
using the same algorithm that standard DDS does.

Whether SQL uses the ID or not is inconsequential.  If SQL generates the ID,
it should be consistent with DDS.  I'd rather it didn't assign one at all
rather than create one that didn't match the existing algorithm.

And from Charles' experimentation, it would seem that my hypothesis is borne
out; if you set the appropriate values on the CREATE, you may indeed be able
to get SQL to generate an identical format level ID.

This, unfortunately, won't much help Jeff in his particular case because SQL
doesn't support the edit code, and it looks as though the edit code is used
as part of the algorithm.

To summarize: based on Charles' post it seems that you may be able to
generate SQL tables with matching format level IDs, provided you're not
using any DDS features that SQL doesn't support, such as edit codes, and you
use the correct keywords on the CREATE.  But this is still a little shaky,
and Jeff is running into some other issues.

Joe