One gotcha. SQL will generate lines that are longer than 80 characters but
RUNSQLSTM will not read them.
Anything that is longer than 80 characters has to be fixed.
With release 6.1 this restriction does not longer exist.
That means SQL scripts can be coded over the 80th column.
To run the SQL script stored in a source physical file member where the row
length is longer than 80, you need to specify the enhanced length in the
option source margins/Right margin. Default is 80, but can be enhanced up to
32754.
RUNSQLSTM SRCFILE(MYSRCFILE/QSQLDDL)
SRCMBR(MYSQLSRC)
COMMIT(*NONE)
MARGINS(132)
DFTRDBCOL(MYSCHEMA)
BTW with release 6.1 RUNSQLSTM can also execute SQL statements stored in IFS
files.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Alan Campin
Gesendet: Thursday, 30. April 2009 00:23
An: RPG programming on the IBM i / System i
Betreff: Re: Confused about DDL and DDS
Always use Operations Navigator, Database, Schema, Right Mouse and select
New table to create tables.
This gives you a GUI interface to create the table. When you are finished,
you just do a right mouse on the table name and say generate SQL and it will
generate the SQL for you. You only need to clean it up. You never want to
code this stuff by hand. Way too much work.
Here is a script that I put into a source member. If you have V6R1 you can
save your script to the database. Before you need to cut and paste.
One gotcha. SQL will generate lines that are longer than 80 characters but
RUNSQLSTM will not read them. Anything that is longer than 80 characters has
to be fixed.
The headers are for my make tool.
A couple of things to note here.
1. Notice that the file name is created as the Short Name and then the
RENAME statements convert to the long name. This gives you the proper member
names and you still have the short names. If I am creating a table in SQL I
always give it a everything short and long names. SQL uses the long. RPG
programs use the short if doing file I/O.
Note that 99% of this code was generated by the Generate SQL. You just need
to change the CREATE TABLE name and add the renames. Everything else is
pretty well done.
/* *_> CNLLSTSPLF SRCFILE(@2/@1) SRCMBR(@3)
*/
/* *_> DLTF FILE(@5/@4)
*/
/* *_> RUNSQLSTM SRCFILE(@2/@1) SRCMBR(@3) +
*/
/* *_> DFTRDBCOL(@5) CLOSQLCSR(*ENDMOD) ERRLVL(20)
*/
/*
*----------------------------------------------------------------------*/
/* * SQL File...: CITIESMAST Project.....:
*/
/* * Long Name..: CITIES_MASTER
*/
/* * Legacy Name: CITIES
*/
/* * Author.....: A. Campin Date written: 04/24/2009
*/
/* * Purpose....: Define Cities.
*/
/*
*----------------------------------------------------------------------*/
CREATE TABLE CITIESMAST (
ID FOR COLUMN CIID INTEGER
GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER
CACHE 20 ),
STATE_ID FOR COLUMN CIST INTEGER,
CITY_CODE FOR COLUMN CICITY CHAR(3) CCSID 37 NOT NULL DEFAULT
'',
NAME FOR COLUMN CINAME CHAR(15) CCSID 37 NOT NULL
DEFAULT '',
MILEMAKER_NAME FOR COLUMN CIMMNM CHAR(18) CCSID 37 NOT NULL
DEFAULT '',
MILEMAKER_SPLC_CODE FOR COLUMN CISPLC INTEGER DEFAULT 0,
TIMEZONE FOR COLUMN CITIME CHAR(1) CCSID 37 NOT NULL DEFAULT
'',
ADDED_TIMESTAMP FOR COLUMN CIADDTS TIMESTAMP DEFAULT CURRENT
TIMESTAMP,
ADDED_USER FOR COLUMN CIADDUSER CHAR(18) CCSID 37 NOT NULL
DEFAULT USER,
UPDATED_TIMESTAMP FOR COLUMN CIUPDTS TIMESTAMP DEFAULT CURRENT
TIMESTAMP,
UPDATED_USER FOR COLUMN CIUPDUSER CHAR(18) CCSID 37 NOT NULL
DEFAULT USER,
PRIMARY KEY( ID ) )
RCDFMT RCITIESM;
RENAME CITIESMAST TO CITIES_MASTER;
RENAME CITIES_MASTER TO SYSTEM NAME CITIESMAST;
ALTER TABLE CITIES_MASTER
ADD CONSTRAINT CON_CITIESMAST_CICITY_00001
CHECK(CITY_CODE <> ' ');
ALTER TABLE CITIES_MASTER
ADD CONSTRAINT CON_CITIESMAST_CINAME_00001
CHECK(NAME <> ' ');
ALTER TABLE CITIES_MASTER
ADD CONSTRAINT CON_CITIESMAST_CIMMNM_00001
CHECK(MILEMAKER_NAME <> ' ');
ALTER TABLE CITIES_MASTER
ADD CONSTRAINT CON_CITIESMAST_CISPLC_00001
CHECK(MILEMAKER_SPLC_CODE <> ' ');
ALTER TABLE CITIES_MASTER
ADD CONSTRAINT CON_CITIESMAST_CITIME_00001
CHECK(TIMEZONE IN('E','C','M','P'));
ALTER TABLE CITIES_MASTER
ADD CONSTRAINT FOR_CITIESMAST_CIST_00001
FOREIGN KEY(STATE_ID)
REFERENCES STATE_MASTER(ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
LABEL ON TABLE CITIES_MASTER
IS 'Cities Master file' ;
LABEL ON COLUMN CITIES_MASTER (
ID IS 'Id',
STATE_ID IS 'State Id',
CITY_CODE IS 'City Code',
NAME IS 'City Name',
MILEMAKER_NAME IS 'Milemaker Name',
MILEMAKER_SPLC_CODE IS 'Milemaker SPLC Code',
TIMEZONE IS 'Timezone',
ADDED_TIMESTAMP IS 'Added Timestamp',
ADDED_USER IS 'Added User',
UPDATED_TIMESTAMP IS 'Updated Timestamp',
UPDATED_USER IS 'Updated User');
LABEL ON COLUMN CITIES_MASTER (
ID TEXT IS 'Id',
STATE_ID TEXT IS 'State Id',
CITY_CODE TEXT IS 'City Code',
NAME TEXT IS 'City Name',
MILEMAKER_NAME TEXT IS 'Milemaker Name',
MILEMAKER_SPLC_CODE TEXT IS 'Milemaker SPLC Code',
TIMEZONE TEXT IS 'Timezone',
ADDED_TIMESTAMP TEXT IS 'Added Timestamp',
ADDED_USER TEXT IS 'Added User',
UPDATED_TIMESTAMP TEXT IS 'Updated Timestamp',
UPDATED_USER TEXT IS 'Updated User');
On Wed, Apr 29, 2009 at 3:43 PM, Raul A. Jager W. <raul@xxxxxxxxxx> wrote:
RUNSQLSTM will ask you for the source member
Tom Deskevich escribió:
Ok, how do you 'run' that then to actually create the table?
I am assuming you are using SQL type in PDM for documentation purposes
only.
It is not listed when you press help to see all the types.
Then when you want to do the equivalent of CHGPF to add or change a
column
in the table , you do an ALTER TABLE?
Tom Deskevich
Infocon Corporation
Phone 814-472-6066
Fax 814-472-5019
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Loyd Goodbar
Sent: Wednesday, April 29, 2009 3:04 PM
To: RPG programming on the IBM i / System i
Subject: Re: Confused about DDL and DDS
Yes, DDL is "just" text. I use the "SQL" type in PDM (well, Aldon does
when
I create the member).
Here is an excerpt from a table DDL:
create table fsicapture (
capturedate for column capdate date not null with default,
isshipday decimal(1,0) not null with default,
...
budget decimal(12,2) not null with default,
primary key (capturedate)
)
rcdfmt fsicapturr
;
label on table fsicapture is 'Forecast sales inventory graph capture';
label on column fsicapture (
capturedate text is 'Capture date',
isshipday text is 'Is ship day 1=yes',
...
budget text is 'Sales budget for month'
);
label on column fsicapture (
capturedate is 'Capture Date',
isshipday is 'Shipping Day 1=Yes 0=No',
...
budget is 'Sales Budget'
);
alter table fsicapture
add constraint check_isshipday check(isshipday in (0,1))
;
HTH,
Loyd
On Wed, Apr 29, 2009 at 3:51 PM, Tom Deskevich <
thomas.l.deskevich@xxxxxxxxxxxxx> wrote:
I have been searching through the archives and the web.
I want to create a data base file using DDL. Is DDL just a text file
created
with PDM with a CREATE statement in it? I did not see any examples
anywhere
except CREATE statements. I never saw the equivalent of DDS for the
CREATE
to look at.
I do not see a source type DDL in PDM.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
midrange.com
