Re: SQL DDL Create Table Names

Rick,

V5R4 has a FORMAT keyword on the CREATE, eliminating the RENAME gyration.

-mark

At 5/31/07 08:14 PM, you wrote:
>A helpful hint, for those who are using 'create table' for the first time.
>
>Since SQL doesn't know what a record format is, when using the create
>table DDL to build physicals it names it the same as the table.
>
>You have two choices when using them natively in RPG, since the record
>format can't be the same as the file name.
>
>You can rename the record format on all the F' specs in the RPG
>programs that use them, or you can do this little trick, creating the
>table as the record format name and then renaming it - the record
>format stays the same.
>
>My SQL create table source look like this:
>
>-- create table (name is the record format name, not table)
>
>create table
> mylib/myfiler
> (
>
>fld_one for column MFFLD1 character(7) not null default ' ',
>fld_two for column MFFLD2 character(20) not null default ' ',
>
>primary key(
> MFFLD1
> )
> ) ;
>
>--- rename table so that record name is different than table name
>
>rename table mylib/myfiler
>to system name myfile;
>
>--- text on table
>
>label on table
> mylib/myfile is 'my table description ' ;
>
>--- set column headings
>
>label on column
>
> (
>MFFLD1 is 'Field One ' ,
>MFFLD2 is 'Field Two '
> ) ;
>
>--- set text (optional, it will use colhdg if not included)
>
>label on column
> mylib/myfile
>
> (
>MFFLD1 text is 'Field One ' ,
>MFFLD2 text is 'Field Two '
> ) ;
>
>
>On 5/31/07, Peter Dow (ML) <maillist@xxxxxxxxxxxxxxx> wrote:
> > Hi Jerry,
> >
> > CRTPF FILE(mylib/myfile) SRCMBR(myfile)
> > CRTDUPOBJ OBJ(myfile) FROMLIB(mylib) OBJTYPE(*FILE) TOLIB(mylib)
> > NEWOBJ(newfile)
> >
> > is pretty much the same as
> >
> > CREATE TABLE mylib/myfile (...fields...)
> > CREATE TABLE mylib/newfile LIKE mylib/myfile
> >
> > I'm probably being dense, but is there some reason why you would rather do
> > CRTPF FILE(mylib/myfile) SRCMBR(myfile)
> > CRTPF FILE(mylib/newfile) SRCMBR(myfile)
> >
> > *Peter Dow* /
> > Dow Software Services, Inc.
> > 909 793-9050
> > pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /
> >
> > Jerry Adams wrote:
> > > Elvis,
> > >
> > > Thanks for the link. Though I hadn't checked that one out ahead of
> > > time, I had gone to a couple of others and some printed (gasp!) material
> > > beforehand.
> > >
> > >
> > > It may be that I am either not understanding the response or that I
> > > didn't make myself clear. Which, in either case, is my fault. So let's
> > > see if I can do at least a little better.
> > >
> > >
> > > We can give a DDS source member any name, FILEJ, for example. On the
> > > CRTPF or CHGPF commands there is a FILE parameter and the value used,
> > > JFILE, for example, points to (or creates) a specific file named JFILE
> > > based upon the source member (FILEJ) provided. I.e., while usually the
> > > DDS source member and physical file have the same names, it ain't
> > > necessary; the name of the file to be created (or changed) is not
> > > inherent inside the DDS source member.
> > >
> > >
> > > Now, if I understood your example and the other reading, "newTable" is
> > > the actual name of the table that will be created. There is no way that
> > > I have seen/found to use this same source member to create "newTableX".
> > > Or is there? If not, I'll have to stick with DDS for these mirror-image
> > > tables and use SQL for the unique tables (new or old).
> > >
> > >
> > > * Jerry C. Adams
> > > *IBM System i5/iSeries Programmer/Analyst
> > > B&W Wholesale Distributors, Inc.* *
> > > voice
> > > 615.995.7024
> > > fax
> > > 615.995.1201
> > > email
> > > jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>
> > >
> > >
> > >
> > > Elvis Budimlic wrote:
> > >
> > >> Another way is to use CREATE TABLE in a "field reference file" context:
> > >>
> > >> CREATE TABLE newTable as
> > >> (SELECT field1, field3, field17 FROM fldRfcTbl)
> > >> WITH NO DATA
> > >>
> > >> There are number of other parms on CREATE TABLE to investigate (i.e.
> > >> INCLUDING IDENTITY COLUMN ATTRIBUTES or starting with V5R4 new RCDFMT
> > >> keyword).
> > >>
> > >> Check them all out here:
> > >>
> > >>
> http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmsthcta
> > >> bl.htm
> > >>
> > >> HTH, Elvis
> > >>
> > >> Celebrating 10-Years of SQL Performance Excellence
> > >> http://centerfieldtechnology.com/training.asp
> > >>
> > >> -----Original Message-----
> > >> Subject: SQL DDL Create Table Names
> > >>
> > >> I just started using DDL to define tables so this is certainly a newbie
> > >> question.
> > >>
> > >>
> > >> I have a lot of tables with exactly the same layout, but different
> > >> names. Is there a way to override the file name on the CREATE statement
> > >> [CREATE TABLE ARMAST ( , for example] so that I can use the same source
> > >> member to create different files? Even files that still use the S/36
> > >> group file id (such as A.ARMAST)? Same question as it relates to the
> > >> ALTER TABLE.
> > >>
> > >>
> > >> I saw in the RUNSQLSTM command the DFTRDBCOL parameter to tell it the
> > >> name of the schema (library), but I didn't see anything about the table
> > >> name.
> > >>
> > >>
> > >>
> > >
> > >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> >
>--
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@xxxxxxxxxxxx
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>or email: MIDRANGE-L-request@xxxxxxxxxxxx
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.