Re: SQL DDL Create Table Names

Mark
check out this web site.

http://www.mcpressonline.com/mc?1@xxxxxxxxxxxxxxxxx@.6b4f0686



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill
midrange-l-bounces+alanshore=nbty.com@xxxxxxxxxxxx wrote on 05/31/2007
08:14:38 PM:

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.