× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. WDSCI-L
  3. October 2014

Re: Running SQL scripts



There is a small subset of IBM Data Studio which is shipped with RDi. Now
that IBM Data Studio itself is free, you can get the full features of the
product. The SQL running/editing/diagramming is relevant to IBM i. The
query tuning is not since it is geared to LUW DBW (not IBM i).

I am thinking about developing better integration with RDi eventually, so I
am following this thread with interest.

So if there was an easy way to get embedded SQL into an SQL script editor
in IBM Data Studio would this help write, test queries?



Regards,

Edmund (E.H.) Reinhardt
Technical Architect for Rational Developer for i



Phone: 1-905-413-3125 | Home: 1-905-854-6195
E-mail: edmund.reinhardt@xxxxxxxxxx
RDi YouTube: 8200 Warden Ave
www.youtube.com/user/IBMRational#g/c/62DF24D5BCD43501 Markham, ON L6G 1C7
Find me on: Canada






From: Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
To: "'Rational Developer for IBM i / Websphere Development Studio
Client for System i & iSeries'" <wdsci-l@xxxxxxxxxxxx>
Date: 30/10/2014 02:00 PM
Subject: Re: [WDSCI-L] Running SQL scripts
Sent by: "WDSCI-L" <wdsci-l-bounces@xxxxxxxxxxxx>



No, you're right... It is Data Studio

-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon
Hamberg
Sent: Thursday, October 30, 2014 1:59 PM
To: Rational Developer for IBM i / Websphere Development Studio Client for
System i & iSeries
Subject: Re: [WDSCI-L] Running SQL scripts

Yeah, Greg - are you sure it is Dev Studio? I thought it was Data Studio.

On 10/30/2014 12:49 PM, Greg Wilburn wrote:
I actually like it a lot better than Navigator. I have a "project"
defined. But based on what Vern is saying, I think I'm using the "Data
View" portion of RDi. After installing Dev Studio, the lines have blurred
a bit.

Greg

-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of Kurt
Anderson
Sent: Thursday, October 30, 2014 1:48 PM
To: Rational Developer for IBM i / Websphere Development Studio Client
for System i & iSeries
Subject: Re: [WDSCI-L] Running SQL scripts

This thread got me excited about running SQL in RDi. But it sounds like
not so much. Run SQL Scripts in System i Navigator is so annoying to get
to. Thankfully I only need it to test stored procedures and we don't use
very many.

Kurt Anderson
Sr. Programmer/Analyst ? Application Development, Service Delivery
Platform

-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon
Hamberg
Sent: Thursday, October 30, 2014 1:45 PM
To: Rational Developer for IBM i / Websphere Development Studio Client
for System i & iSeries
Subject: Re: [WDSCI-L] Running SQL scripts

Hi Greg

Data Studio is not an IBM i tool - it is an IBM-wide tool for the several
flavors of DB2, and it will not know what we want from IBM i messages - you
MIGHT get the code from there - have you by chance seen that?

I had tried a stand-alone install of DS some years ago - it was lacking
for what I feel I needed. By design, it does not recognize DDS-based PFs
and LFs - I understand, those are not SQL objects in some strict sense -
there has been a thread on that here.


Now that DS is kind-of bundled with RDi, I tried it again - it thoroughly
messed up my help text settings, taking them over, so that I couldn't get
at the help for RPG and all, at least not easily.

So I have no love for DS. I have also worked some with the Data
perspective - I don't believe it is Data Studio, as someone suggested in a
post here. I mean, why give us Data Studio if it's the same thing as RDi
already has available?

The stuff in Navigator does what I want more than anything in RDi - and
to be truthful, I'll go to good old STRSQL more than any GUI tool. It may
be ugly, every line is a separate variable, it doesn't prompt for newer
things - but it'll run everything, and most things I need are promptable.
The rest I have to know what the syntax is, and the Reference is good for
that (DOH!)

Navigator's interface has some rudimentary prompting - a kind of wizard
- and that won't give me the advanced, new stuff in a prompt, either,
that I know of.

All this is problematic, I agree - and I just have learned to use the
green-screen bit, where I get the SQL* message displayed immediately and
can use F1 to see the details.

As to whether SQL7029 is "flat out wrong", I cannot agree - I think it is
completely correct - V_SHIPVIA is NOT valid for use in that position, for
the reason you gave. The 2nd-level text clearly states what you learned by
"trial and error" - MSGIDs are our friends, man!

HTH
Vern

On 10/30/2014 12:17 PM, Greg Wilburn wrote:
This is a bit of topic for this forum, but I can see why many still use
DDS.

I'm saying that the SQL error codes and their descriptions are often
"flat out wrong" when it comes to the real problem.

Example:

CREATE OR REPLACE VIEW V_SHIP_VIA FOR SYSTEM NAME V_SHIPVIA
(SHIP_VIA_CODE FOR SHPV_CODE,
SHIP_VIA_DESC FOR SHPV_DESC)
AS
(SELECT SUBSTR(RFSLC,1,3) AS SHIP_VIA_CODE,
SUBSTR(RFDTA, 1, 30) AS SHIP_VIA_DESC
FROM ASTDTA.REFERRF
WHERE RFCAT = '0022' AND RFSQ2 = 1 ); This statement yields
error:

[SQL7029] New name V_SHIPVIA is not valid.

DSPMSGD...
Display Formatted Message Text
System:
TBFPWR7
Message ID . . . . . . . . . : SQL7029
Message file . . . . . . . . : QSQLMSG
Library . . . . . . . . . : QSYS


Message . . . . : New name &3 is not valid.
Cause . . . . . : An attempt was made to rename &1 in &2, but the new
name
is not valid. Both the new name and the new system name are valid
system
names. If both names are specified in the RENAME statement, only the
name
following SYSTEM NAME can be a valid system name.
Recovery . . . : Do one of the following and try the request again:
-- Specify either the new name or the new system name.
-- Change the first name to be a name that is not valid as a
system name.

The new name is most certainly valid.
What I discovered (by trial and error) is that assigning the system name
for a view where the original name is less that 10 characters, is not
valid.

The issue with RDi is that it doesn't provide the message description -
navigator does. If RDi is a "development tool" with "development studio"
added in, it should AT LEAST yield the message description.

Greg

-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of
Vernon Hamberg
Sent: Thursday, October 30, 2014 12:58 PM
To: Rational Developer for IBM i / Websphere Development Studio Client
for System i & iSeries
Subject: Re: [WDSCI-L] Running SQL scripts

SQL errors on IBM i usually have an SQL code (the variable SQLCOD in
RPG). This number is part of an error message, so if the code is 100,
you can see more by putting the error code after SQL to build a message
ID.

To see more about SQL code 100 you would do this -

DSPMSGD SQL0100 QSQLMSG

The manual on SQL messages and codes has this instruction -

"Take the absolute value of the SQLCODE, then append it to the letters
″SQL″ (for SQLCODEs less than 10 000) or the letters ″SQ″ (for
SQLCODEs greater than or equal to 10 000) to determine the message
identifier."

HTH
Vern

On 10/30/2014 10:01 AM, Gary Thompson wrote:
Greg,

I often find myself at that frustrating point, looking at an SQL error
and not getting it.
Happened yesterday when someone asked for help debugging an SQL
statement.
Took way too much time to get the answer, even though the error
identified an "invalid" component.
I like your 'upstream or downstream' comment . . .

Error handling, to me, is the toughest game in town, and don't
remember ever having a good idea of how to make any SQL error message
'better'.
Frankly, I think the issue is more in my attitude, focus and
experience.

I would not try comparing SQL error handling with a high level
language because HLL errors are, to me, easier to trap because they
mainly relate to 'one' operation.
For example, I don't recall being stumped by SQL errors in the FROM
clause, which is one part of an SQL statement that I can relate to an
HLL program.
To me, an SQL statement is more like an HLL routine.


-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of Greg
Wilburn
Sent: Thursday, October 30, 2014 8:11 AM
To: 'Rational Developer for IBM i / Websphere Development Studio Client
for System i & iSeries'
Subject: Re: [WDSCI-L] Running SQL scripts

Ken,

Thanks... this is my first crack at SQL and while I like what it can
do, I really despise the lack of "real information" when an error occurs.
Most of the time the error message (even in Navigator) is useless...
missing a comma here or there, indicating that something is "invalid" when
there's an issue upstream or downstream.

Would be nice if there was better syntax checking like in a high level
programming language.

Greg

-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of Ken
Killian
Sent: Thursday, October 30, 2014 8:14 AM
To: Rational Developer for IBM i / Websphere Development Studio
Client for System i & iSeries
Subject: Re: [WDSCI-L] Running SQL scripts

Greg,

Sadly no...

RDI Script is an Addition/Complementary to running SQL Scripts, not a
"REPLACEMENT"...
<Big Frown>

I love the ability to test stored procedures from within RDi with the
ability to debug! <smile>

But, at least it gives error feedback, although no highlighting. It has
forced me to learn SQL Better. And when I get stuck, I OFTEN have to resort
back to good old "Run SQL Scripts" inside of Navigator...

Except for testing stored procedures, I often use SquirrelSQL.org.
Because it has bookmarks for commonly run SQL Statements that I run every
day/Week. And it also remembers my last 100 SQL Statements. Which is very
handy at times.

I really want to use Data Studio more... But, I guess it's navigation
drive me personally nuts. Writing and running SQL Scripts should be a
SIMPLE, not complicated. I guess I need to use it more myself...

In the Script Writer, you can press F5=Run Sql Script! Very nice! And
you can highlight statement to run that single statement! Also very nice!

But, when I try to use the "SQL Query Builder" for a single SQL
Statement. I cannot use F5 to run... <frown> Very frustrating. But, it has
awesome Code Assist for Fields & Tables! And I can build sql statement
visually, sort of...

Data Studio also does NOT have the Visual Explain or the Index
Advisor. Once again you have to go back Navigator. Since IBM does NOT
know how to use "Code Reuse" across their tool platform. <Big Frown>

So, sadly I only use Data-Studio for testing stored-Procedures. Any
SQL I use SquirrelSQL.org or Navigator or good old Green Screen
SQL... <disappointment>

RDi cannot NOT be beat for RPGLE development however!!! <Joy Joy>

-Ken Killian-

-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of Greg
Wilburn
Sent: Wednesday, October 29, 2014 3:56 PM
To: 'WDSCI-L@xxxxxxxxxxxx'
Subject: [WDSCI-L] Running SQL scripts

I've installed the Developer Studio and have been creating/editing SQL
scripts (mostly creating views). I like this a bit better than simply
running them through Navigator, but when a script fails it doesn't
highlight the problem area like Navigator does.

Is there any way to change this?
--
This is the Rational Developer for IBM i / Websphere Development
Studio Client for System i & iSeries (WDSCI-L) mailing list To post
a message email: WDSCI-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/wdsci-l.
--
This is the Rational Developer for IBM i / Websphere Development
Studio Client for System i & iSeries (WDSCI-L) mailing list To post
a message email: WDSCI-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/wdsci-l.
--
This is the Rational Developer for IBM i / Websphere Development
Studio Client for System i & iSeries (WDSCI-L) mailing list To post
a message email: WDSCI-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/wdsci-l.
--
This is the Rational Developer for IBM i / Websphere Development Studio
Client for System i & iSeries (WDSCI-L) mailing list To post a message
email: WDSCI-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/wdsci-l.

--
This is the Rational Developer for IBM i / Websphere Development Studio
Client for System i & iSeries (WDSCI-L) mailing list
To post a message email: WDSCI-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/wdsci-l.
--
This is the Rational Developer for IBM i / Websphere Development Studio
Client for System i & iSeries (WDSCI-L) mailing list
To post a message email: WDSCI-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/wdsci-l.

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.