Re: Storing source as DDL instead of DDS -- MIDRANGE-L

Hi, Mike:

Instead of ALTER TABLE, use the newer "CREATE OR REPLACE" syntax, so you can just edit the original source, adding any new fields, and then re-issue the RUNSQLSTM command. For example:

CREATE OR REPLACE TABLE ISS/ABCD (
AKEY CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
ADATA CHAR(40) CCSID 37 NOT NULL DEFAULT '' ,
ADATA2 CHAR(50) CCSID 37 NOT NULL DEFAULT '' ,
PRIMARY KEY( AKEY )
)
RCDFMT ARECFM ;

Then, after modifications, it might look like:

CREATE OR REPLACE TABLE ISS/ABCD (
AKEY CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,
ADATA CHAR(40) CCSID 37 NOT NULL DEFAULT '' ,
ADATA2 CHAR(50) CCSID 37 NOT NULL DEFAULT '' ,
ADATA3 CHAR(60) CCSID 37 NOT NULL DEFAULT '' ,
ADATA4 CHAR(70) CCSID 37 NOT NULL DEFAULT '' ,
PRIMARY KEY( AKEY )
)
RCDFMT ARECFMT ;

Now, you can just edit the source, and make the desired changes, no more having to learn the syntax of "ALTER TABLE"...

And it behaves very much like when you just changed the DDS source and then issued CHGPF to apply the changes.

Much better from a change management and version control perspective -- one object, one source.

Hope this helps,

Mark S. Waterbury

> On 2/24/2017 3:12 PM, Mike Cunningham wrote:

For those storing the source. How do you deal with the addition of new fields (columns)? Since you have to use ALTER TABLE to add a column, the source for adding a field to an existing file is completely different from the original creation of the field at file creation time. Do you save the original CREATE TABLE and all subsequent ALTER TABLE commands in the script so you can recreate the table if needed? We only have 6 tables created as DDL based and use the GUI to make any changes and then after each change extract the source using the GUI and save it. The opposite of what we do with DDS. Instead of change the source change the table, its change the table then change the source.

And is there some way I have not found to insert a new field (column) into the middle of an existing list of fields like a could do using DDS and CHGPF? I know it does not make any different from the data storage application perspective but when looking at the table with query or dfu it sure is nice have all related fields next to each other. Like if I needed to add a third line of mailing address putting it after the existing two lines of address instead of at the end of the file

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark S Waterbury
Sent: Friday, February 24, 2017 3:00 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Storing source as DDL instead of DDS

Paul:

If I were doing this,I would create a source physical file anmed QSQLSRC or QSQLDDLSRC or similar,and keep the source in there. Then, you can create a PDM user-defined option "RU" to invoke the RUNSQLSTM command to create the object from that source ... e.g.:

RU RUNSQLSTM SRCFILE(&L/&F) SRCMBR(&N) COMMIT(*NONE) NAMING(*SYS)

Since it is a "script" you could create several objects from within the same script source member. But, I would suggest you not do that -- instead, create just one object from each source member... that way, when you issue:

DSPOBJD OBJ(library/object) OBJTYPE(*FILE) DETAIL(*SERVICE)

the object will show the correct source member from which it was created.

(If you use RDi or some other PC-based tools to create using a streamfile in the IFS, or a file on your PC,you do not get that benefit.)

Hope that helps,

Mark S. Waterbury

> On 2/24/2017 2:48 PM, Steinmetz, Paul wrote:

I will re ask the question.

1) If using DDL where is the source stored, source member, IFS, or not all?
The reason I ask, is we use utilities (Total/400, etc) that rely on source being stored in a source member.

2) At this time, our two major apps do not use or support DDL, thus out of the question for us, as of now.
Tomorrow could be another story.

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Vernon Hamberg
Sent: Friday, February 24, 2017 2:40 PM
To: Midrange Systems Technical Discussion
Subject: Re: Storing source as DDL instead of DDS

Paul

Is everyone missing the point of your question? Or are you asking something different?

The subject is about storing source, NOT converting from DDS to DDL - is that right?

It is possible to use source in the IFS for ILE compile commands. I really do not think this is what this is about. Source stored in the IFS is in stream files - tables created with DDL are not treated like stream files, so far as I know.

So I'm very confused by the question AND by the responses, that seem to have missed the target completely.

Elucidate, please!!!

Vern

On 2/24/2017 10:41 AM, Steinmetz, Paul wrote:

I was at a new Pennsylvania IBM users group yesterday.
Name TBD, looking for new members.

It was mentioned about storing source as DDL instead of DDS.

Anyone doing this?
How is done?
Pros/cons?
Is there a migration utitlity?

Thank You
_____
Paul Steinmetz
IBM i Systems Administrator

Pencor Services, Inc.
462 Delaware Ave
Palmerton Pa 18071

610-826-9117 work
610-826-9188 fax
610-349-0913 cell
610-377-6012 home

psteinmetz@xxxxxxxxxx
http://www.pencor.com/

--
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.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
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.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD

________________________________
This email may contain confidential information about a Pennsylvania College of Technology student. It is intended solely for the use of the recipient. This email may contain information that is considered an “educational record” subject to the protections of the Family Educational Rights and Privacy Act Regulations. The regulations may be found at 34 C.F.R. Part 99 for your reference. The recipient may only use or disclose the information in accordance with the requirements of the Federal Educational Rights and Privacy Act Regulations. If you have received this transmission in error, please notify the sender immediately and permanently delete the email.