AW: easier SQL statements in CL

Additional information about the most recent enhancements in DB2 for i and
SQL can be found here:

1. Mike Cains Blog
http://db2fori.blogspot.de/2012/04/db2-wiki-refresher.html

2. DeveloperWorks - IBM i Technology Updates
2.1. DB2 for i Functional Enhancements

https://www.ibm.com/developerworks/mydeveloperworks/wikis/home?lang=en#/wiki
/IBM%20i%20Technology%20Updates/page/DB2%20for%20i%20Functional%20Enhancemen
ts

2.2. Run SQL (RUNSQL) - new command

https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/IBM%20i%
20Technology%20Updates/page/Run%20SQL%20%28RUNSQL%29%20-%20new%20command?lan
g=en


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: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jim Oberholtzer
Gesendet: Friday, 11.5 2012 23:34
An: Midrange Systems Technical Discussion
Betreff: Re: easier SQL statements in CL

Last week at COMMON's Annual Meeting Kent Milligan said IBM was getting
ready to (or maybe already has) release a RUNSQL command. It will be
available via PTF for V7 and V6. I don't have many details beyond, it will
not be able to bring back a result set directly. You'll have to populate a
temp file and process from there.

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On 5/11/2012 3:45 PM, Phil McCullough wrote:

Anyone think there is an IBM requirement buried in here?

Phil

On May 11, 2012, at 12:41 PM, "Gary Thompson"<gthompson@xxxxxxxxxxx>

wrote:

Got me!

-----Original Message-----
From:midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Monnier, Gary
Sent: Friday, May 11, 2012 1:37 PM
To: Midrange Systems Technical Discussion
Subject: RE: easier SQL statements in CL

My point exactly.

Gary Monnier


-----Original Message-----
From:midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Gary Thompson
Sent: Friday, May 11, 2012 12:33 PM
To: Midrange Systems Technical Discussion
Subject: RE: easier SQL statements in CL

SQLCLLE ??? - does not appear as an option on our V7R1 box...

-----Original Message-----
From:midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Monnier, Gary
Sent: Friday, May 11, 2012 1:28 PM
To: Midrange Systems Technical Discussion
Subject: RE: easier SQL statements in CL

Have you ever heard of an SQLCLLE source type?

Gary Monnier


-----Original Message-----
From:midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Friday, May 11, 2012 12:21 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: easier SQL statements in CL

None of these suggestions let me keep the SQL statements WITHIN the

one CL with the RUNSQLSTM command.

Is there a way to do this?

Is it possible to copy data records from within the CL member (as in

my OP) into a qtemp source member and use RUNSQLSTM to run that?

-----Original Message-----
From:midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Loek Maartens
Sent: Friday, May 11, 2012 2:06 PM To:midrange-l@xxxxxxxxxxxx
Subject: Re: easier SQL statements in CL

Hi Joel,

Maybe you mean like in RUNSQLSTM ?

Kind regards,

Loek Maartens.


"Stone, Joel"<Joel.Stone@xxxxxxxxxx> wrote in

messagenews:0B57EB3973113A47A96A7FD995D2B6E025A35895@xxxxxxxxxxxxxxxxxxxxx..
.

There MUST be a better method of coding SQL in a CL source member.


Here is what an SQL stmt might look like that I created today:

PGM
DCL&SQLCMD type(*CHAR) len(1000)
chgvar&sqlCmd ('create table qtemp/sqltemp1' +
*tcat ' (fld1 char(1),' +
*tcat ' fld2 char(1),' +
*tcat ' fld3 char(1),' +
*tcat ' clientN numeric(6,0))')

RUNSQL(&SQLCMD)


I recall that others like to create a string with the MSG commands.

Is it possible to code the straight SQL statements with no
tricks in a CL source member (like in the old punch-card days to
suck in a few data
records?):)

Any method would be great if I can skip all the *tcats, and quotes

mess.

In the example below, is there a simple way to copy everything
between the "/*" to a source member and run that?

Or any other method to keep the CL together with the SQL
commands in the same member - without using quotes or tcat's.


Something like:


PGM
DCL&SQLCMD type(*CHAR) len(1000)

//start SQL
/*

create table qtemp/sqltemp1 (fld1 char(1) ---
fld2 char(1) |
fld2 char(1) |----

would

like to run this nice readable SQL command in CL without quotes and

tcat's

client numeric(6,0)) ___|

/*

RUNSQL




Thanks!

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