Re: SQL DDL Create Table Names

["rick baird" <rick.baird@xxxxxxxxx>]

woo hoo!  thanks for that Mark.  It was a pain.

On 5/31/07, M. Lazarus <mlazarus@xxxxxxxx> wrote:
> 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.