Re: Running SQL scripts

System naming has been essential in our ERP system environment because of the need to reference several libraries in a single ERP instance (Production, Testing and Development). We have the vendors libraries and our custom development libraries so there's separation by vendor recommendation. To further complicate this our environments may be on the same server with different library names (Dev & Test) or on an isolated server (Prod). To accommodate the usage of library lists without the need to fully qualify the library, we use Job Descriptions with Initial Library List values to link the correct libraries with the proper environment. Doing so ensures that I get the correct libraries appropriate to the environment I'm working in (Dev, Test & Prod). At least, this works for us as "one size" does not always fit all :(

-----Original Message-----
From: Vernon Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxx]
Sent: Friday, October 31, 2014 2:34 PM
To: Rational Developer for IBM i / Websphere Development Studio Client for System i & iSeries
Subject: Re: [WDSCI-L] Running SQL scripts

Daron, looks as if I'll have to take another look - was probably using *SQL naming.

But maybe what I refer to is the list of tables in a library - SQL statements work fine, no problem there, might be just the list of things that show tables and indexes and views and not PFs or LFs - will confirm.

OK, I tried the Database Developer perspective - It does list all PFs - not sure about those created using CRTPF with RCDLEN.

I saw views - these were the LFs created using DDS.

There were no apparent indexes listed, even if there are key fields in an LF. This doesn't surprise me - there is no specific SQL index object in that case. And there are usually no constraints, so those do not show up either.

This is better than I remember, however - thx for the pushback!

Vern

On 10/31/2014 1:03 PM, Daron Whitehouse wrote:

Vern / Edmund,

What does IBM Data Studio not find valid about DDS objects? The Objects are visible to DS when the DB2 for i connection is defined as using "naming=system" (We use an older ERP system with all DDS; however, all new development is in DDL. So we have a mix). I haven't had much opportunity to fully explore IBM DS v4.1 with RDi v9.1, but have used the Data Perspective in RDi 8.5 (actually RBD v8.5) in this manner with much success. Albeit, I do often times open the .sql file using my system editor (cwbundbs.exe) to create UDF's and Procedures. We also use RTC so we use a Data Project for our SQL components.

Regards,

Daron



Daron Whitehouse
Systems and Programming Manager
Madico Inc
64 Industrial Parkway
Woburn,MA 01801
781-935-7850 X-196
dwhitehouse@xxxxxxxxxx
www.madico.com





Notice of Confidentiality

This e-mail and any attachments thereto are intended only for use by the addressee(s) named herein and may be proprietary and/or legally privileged. If you are not the intended recipient of this e-mail, you are hereby notified that any dissemination, distribution or copying of this email, and any attachments thereto, without the prior written permission of the sender is strictly prohibited. If you receive this email in error, please immediately telephone or email the sender and permanently delete the original copy and any copy of this mail, and any printout thereof.

The contents of an attachment to this email may contain software viruses that could damage your own computer system. While Madico has taken every reasonable precaution to minimize this risk, we cannot accept liability for any damage that you sustain as a result of software viruses. You should carry out your own virus checks before opening any attachment.





-----Original Message-----
From: Edmund Reinhardt [mailto:edmund.reinhardt@xxxxxxxxxx]
Sent: Thursday, October 30, 2014 3:41 PM
To: Rational Developer for IBM i / Websphere Development Studio Client
for System i & iSeries
Subject: Re: [WDSCI-L] Running SQL scripts


Vern,
I don't see the IBM Data Studio, which is written by the common DB2 team that does not know IBM i, ever supporting DDS objects.

From what I hear from Dan Cruickshank, it is a good idea to front end
DDS

PF with an SQL view and then you can modernize at your leisure.



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: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
To: Rational Developer for IBM i / Websphere Development Studio
Client for System i & iSeries <wdsci-l@xxxxxxxxxxxx>
Date: 30/10/2014 03:14 PM
Subject: Re: [WDSCI-L] Running SQL scripts
Sent by: "WDSCI-L" <wdsci-l-bounces@xxxxxxxxxxxx>



This is very nice stuff to dream about - the Data perspective and Data Studio were always kind of a fill-in to allow testing, but not connected as you describe here - so they are not all that much better than Navigator's tooling, to some of our eyes.

Would an RFE with many votes put this closer to the nearer term?

One of the other matters might include recognition of DDS-based objects as valid to use in SQL - Data Studio doesn't see them, as I recall - I'm not sure about the Data perspective. This question applies to the idea of some good prompting.

Vern

On 10/30/2014 1:35 PM, Edmund Reinhardt wrote:

This is my current dream, which I do not have any resources to
implement

in

the near term, so please don't have specific expectations, but you
can validate that my plan is on track.

When going from RDi editor to SQL editor, the developer is prompted
for each :variable to give a literal.
Comments will be inserted into the SQL editor, documenting the
:variable

to

literal mapping.
When going back from the SQL editor to the RDi editor, the literals
would be substituted by the mapped variable, for unmapped literals,
the developer will be prompted if they want to replace with more
:variable names



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: Buck Calabro <kc2hiz@xxxxxxxxx>
To: wdsci-l@xxxxxxxxxxxx
Date: 30/10/2014 02:30 PM
Subject: Re: [WDSCI-L] Running SQL scripts
Sent by: "WDSCI-L" <wdsci-l-bounces@xxxxxxxxxxxx>



On 10/30/2014 2:10 PM, Edmund Reinhardt wrote:

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?

Mad utopian dreamer alert!

Given:
select SYSTEM_COLUMN_NAME, DATA_TYPE, "LENGTH", NUMERIC_SCALE,
COLUMN_TEXT, IS_NULLABLE, "CCSID", SYSTEM_TABLE_NAME,
SYSTEM_TABLE_SCHEMA, COLNO
from SYSCOLUMNS
where SYSTEM_TABLE_NAME = :REF_FILE and
SYSTEM_TABLE_SCHEMA = :REF_LIBR;

I would love to have the test/debug tool/IDE be able to 'declare'
variables :REF_FILE and :REF_LIBR as well as populate them with
values and then execute this code as-is. Once my SQL is working
properly, I could then cut / paste it back into my HLL as-is without
having to remove literals and replace them with host variables.

Similar to the ability SDA has to populate fields for testing (STRSDA
option 3).

--
--buck

'I had nothing to offer anybody except my own confusion' - Jack
Kerouac
--
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.