Re: SQL DDL Create Table Names -- MIDRANGE-L

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.