Re: GENERATE_SQL(): Was: Learning SEU, Source members . . .

Justin,

I would say that many people keep a copy of the SQL object as a source
member. That way if per any reason you lose yur SQL object you can always
recreate it from the source member.

I prefer to indetify SQL members as SQL, but iin fact RUNSQLSTM will accept
any kind of source member type (even RPG!! :-) )

HTH,

Luis Rodríguez

Luis Rodriguez

--


On Mon, Oct 19, 2015 at 11:45 AM, Justin Dearing <zippy1981@xxxxxxxxx>
wrote:

Ok new thread now that I've had some time to digest.

On Thu, Oct 15, 2015 at 3:24 PM John Yeung <gallium.arsenide@xxxxxxxxx>
wrote:

- Go through a STRPDM tutorial for SQL just to get familar with

the

mechanics.
- Do the next DB schema design project with SEU and RUNSQLSTM

Not sure how PDM relates to SQL, or how SEU relates to RUNSQLSTM; but
yeah, I guess if you're just exploring and you don't know a lick of
RPG or CL then sure, knock yourself out.

The particular RPG programmer I deal with at the moment stores DDL SQL in
source members in a source file called QSQLSRC. The file has the same

name

as the table. He edits the file with SEU and executes with RUNSQLSTMT. I
assumed this was a common practice, especially since QSYS2.GENERATE_SQL
lets you create the source member after the fact. So am I just learning a
pattern that this particular client uses, or am I learning a pattern that
I'll find elsewhere as I deal with different RPG programmers from

different

shops?

Second question, this particular RPG programmer uses TXT source members

to

store SQL. Would SQLC or some other format be better? I can't find the
developer works page that tells me what all the source member types are
for.

Thirdly, I've been trying to generate source members like this:

CALL QSYS2.GENERATE_SQL(
'TBL', 'TBLLIB', 'TABLE',
'QSQLSRC', 'TBLLIB', 'TBLSOURCEMBR',
STATEMENT_FORMATTING_OPTION => 1,
REPLACE_OPTION => 1,
CREATE_OR_REPLACE_OPTION => 1,
CONSTRAINT_OPTION => 1,
DROP_OPTION => 1,
NAMING_OPTION => 'SQL',
QUALIFIED_NAME_OPTION => 0
);

I do that in SQL Workbench, and if I mess up a parameter, like specify a
table that doesn't exist, I don't get a useful error message. The helpful
error message is in the the joblog, that I get with SELECT * FROM
table(QSYS2.JOBLOG_INFO('*')) a; Is there a way to get the

ORDINAL_POSITION

or MESSAGE_ID of a failed sql statement so I can look up the message that
way? All I get back now is "[SQL0443] QSQGNDDL ERROR [SQL State=58004, DB
Errorcode=-443]". I guess I could get the timestamp as a variable before

I

send the statement and then put that in the WHERE clause.
--
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.