CRPence | 31 Oct 23:06 2014
Picon

Re: Running SQL scripts

On 31-Oct-2014 13:33 -0500, Vernon Hamberg wrote:
>  On 10/31/2014 1:03 PM, Daron Whitehouse wrote:
>>
>> 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"  <<SNIP>>
>>
> Daron, looks as if I'll have to take another look - was probably
> using *SQL naming.

   The SQL Naming OPTION should be quite near immaterial, because the 
SQL Catalog VIEWs do not change with a change to that option.  What *SYS 
naming would achieve is a CURRENT_SCHEMA='*LIBL' for which the list of 
objects would include more than just those in the library named the same 
as the Authorization Identifier [if defaulted] or the one library name 
that is the CURRENT SCHEMA.

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

   The definitions of the respective SQL Catalog VIEW will be telling; 
SYSTABLES probably.  Display File Description (DSPFD) of that VIEW 
database *FILE object, and a predicate effective asking DBXREL='Y' in 
the WHERE clause would produce a list that precludes program-described 
files [and multi-format logical files].
(Continue reading)

MichaelQuigley | 31 Oct 13:41 2014

Re: Running SQL scripts

Edmund,

Yes! This would actually be a great solution to the RFE request--at least 
to my thinking. As would support for more than just RPG--i.e., COBOL would 
be of very beneficial, too. I added a comment to the RFE referencing this 
thread.

As for Visual Explain, I rarely let something go to production without 
running it through visual explain. Authority would only be an issue in a 
production environment. 

<soapbox warning!> 

Going to SQL doesn't change the requirements for a separate development 
environment where programmers have authority to do anything needed to 
fully test and optimize a system. It seems that some folks think SQL is a 
substitute for it. I think such logic is foolish and lacking full thought.

</soapbox warning!>

Thanks,
Michael Quigley
Computer Services
The Way International
www.TheWay.org

"WDSCI-L" <wdsci-l-bounces@...> wrote on 10/30/2014
03:50:12 PM:
> ----- Message from Edmund Reinhardt <edmund.reinhardt@...> on
> Thu, 30 Oct 2014 15:38:57 -0400 -----
(Continue reading)

Buck Calabro | 30 Oct 21:16 2014
Picon

Re: Running SQL scripts

On 10/30/2014 3:38 PM, Edmund Reinhardt wrote:

> Regarding the RFE on visual explain.
> http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=11086
> When I was discussing this with Mark Anderson he didn't think that
> developers had the authority or interest or expertise to use visual explain
> to optimize queries beyond noticing the need to index certain tables.  But
> I see 46 votes on this RFE.  Are developers making active use of Visual
> Explain?

Yes.  I work in a small group, and I'm the one who has to optimise SQL
performance so I have a great deal of interest in understanding Visual
Explain.  Also, because I'm in a small group, authority isn't a problem.

-- 
  --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@...
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@...
Before posting, please take a moment to review the archives
at http://archive.midrange.com/wdsci-l.

MichaelQuigley | 30 Oct 20:25 2014

Re: Running SQL scripts

Edmund,

I think you're definitely on the right track. Should someone open an RFE 
to help get some resources allocated to this? (At this point, I'm almost 
hesitant to add any more RFEs as I feel like I'm spreading my votes to 
thin. i.e., The "I want it all and I want it now" syndrome.)

I'm very thankful for all you and the rest of the RDi team do,

Michael

"WDSCI-L" <wdsci-l-bounces@...> wrote on 10/30/2014
02:37:52 PM:
> ----- Message from Edmund Reinhardt <edmund.reinhardt@...> on
> Thu, 30 Oct 2014 14:35:47 -0400 -----
> 
> To:
> 
> Rational Developer for IBM i / Websphere Development Studio Client 
> for System i & iSeries <wdsci-l@...>
> 
> Subject:
> 
> Re: [WDSCI-L] Running SQL scripts
> 
> 
> 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.
(Continue reading)

Buck Calabro | 30 Oct 20:10 2014
Picon

Re: Running SQL scripts

On 10/30/2014 2:56 PM, Jon Everton wrote:

> Create a stored procedure using input parameters with the table and schema
> name. Use the SQL function REPLACE to put your variables into the SQL
> statement. Send me an email and I'll send you an example if this rather poor
> explanation confuses you.

That's clear Jon, thanks.  This thread is morphing into a discussion on
integrating Data Studio with RDi.  Imagine you have an existing SQLRPGLE
program with a DECLARE CURSOR as below.  You'd like to make some changes
to that SQL statement, what does one do?

Rectangle select, copy, tab to Data Studio, paste.  Replace the host
variables with literals, test.  Edit, test, repeat.  When happy with the
new SQL statement, copy in Data Studio, tab to RDi, select, paste.
Adjust formatting.  Save, compile, test.  Forgot to replace literals,
wonder why program doesn't work.  Replace literals with host variables.
 Save, compile, test.

It's better than a sharp stick in the eye, but that's a low bar :-)

> date: Thu, 30 Oct 2014 14:30:16 -0400
> from: Buck Calabro <kc2hiz@...>
> subject: Re: [WDSCI-L] Running SQL scripts
> 
> 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 
(Continue reading)

Buck Calabro | 30 Oct 20:03 2014
Picon

Re: Running SQL scripts

On 10/30/2014 2: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.

Very much understood.

> 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

Seems reasonable to me!

-- 
  --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@...
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@...
(Continue reading)

Buck Calabro | 30 Oct 19:30 2014
Picon

Re: Running SQL scripts

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
(Continue reading)

MichaelQuigley | 30 Oct 18:30 2014

Re: Maintaining a printer file with RDi...

Robert,

At the top of the Design page under 'Report Controls', you have to select 
the 'Design reports' radio button, then the 'Report' tab to the right. 
Over toward the right you can set the 'Page size.'

HTH,

Michael Quigley
Computer Services
The Way International
www.TheWay.org

"WDSCI-L" <wdsci-l-bounces@...> wrote on 10/30/2014
12:55:57 PM:
> ----- Message from Robert Rogerson <rrogerson@...> on Thu, 
> 30 Oct 2014 12:44:13 -0400 -----
> 
> To:
> 
> Websphere Development Studio Client for iSeries <wdsci-l@...>
> 
> Subject:
> 
> [WDSCI-L] Maintaining a printer file with RDi...
> 
> Hi All,
> 
> This is the first time I've tried to maintain a printer file in RDi.
> 
(Continue reading)

Robert Rogerson | 30 Oct 17:44 2014

Maintaining a printer file with RDi...

Hi All,

This is the first time I've tried to maintain a printer file in RDi.

The output extends to 230 characters wide but the canvas (in RDi) only 
shows 132 characters.

Does anyone know how to change the width of the canvas?

Thanks,

Rob
--

-- 
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@...
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@...
Before posting, please take a moment to review the archives
at http://archive.midrange.com/wdsci-l.

Greg Wilburn | 29 Oct 20:56 2014

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@...
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@...
Before posting, please take a moment to review the archives
at http://archive.midrange.com/wdsci-l.

Buck Calabro | 27 Oct 19:21 2014
Picon

Re: Library list issue

On 10/27/2014 11:13 AM, RPG List wrote:
> I was able to get it it run but I had to run it out of my own library that is in the job description 

I've been dealing with work management on the system for so long, I
forget that not everyone is as familiar as I am.  Sorry.

Think of the connexion between RDi and IBM i as a batch job (because it
is!)  When that batch job starts, it works exactly like every other
batch job on the system.  It starts running with your user profile,
which points to a job description, which points to a library list.  That
library list is what every batch job will use - including the RDi server
job.

If your job description doesn't have your library in it, then you
probably do one of three things when you sign on to an interactive job:
1) ADDLIBLE MYLIB
2) CALL SOMEPGM which does an ADDLIBLE MYLIB
3) Your user profile has an initial program which does an ADDLIBLE MYLIB

Interactive jobs are the only ones which call the initial program
specified in your user profile.  Batch jobs ignore this parameter.

So... having to qualify the CALL MYLIB/MYPGM in the RDi initial command
expected unless your job description takes care of it for you.  Which we
already know it does not, because you noted that you need to add it
every time you start RDi.

--

-- 
  --buck

(Continue reading)


Gmane