RE: BI / Reporting tools -- MIDRANGE-L

Indexes are certainly an integral part of SQL performance, however there
could be a significant work management issue as well. If the SQL server
jobs are struggling to get resources they will be slow and can easily make
the system look very slow as well. A system that is memory constrained
will not perform nearly as well as one that is properly set up. It's the
totality of the set up that counts, not just one factor.

We've discussed how to set up the QZDASOINIT and QSQRVR jobs several times
in the recent past.

Jamal, if you would like to, you may contact me privately and I'll point out
what you need to do, if you can't find it in the archives.

--
Jim Oberholtzer
Agile Technology Architects

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Vernon Hamberg
Sent: Sunday, April 17, 2016 2:03 PM
To: Midrange Systems Technical Discussion
Subject: Re: BI / Reporting tools

Hi Jamal

Well, the integrated database is DB2 for IBM i - it is one of the 3
varieties of DB2 - LUW is for Linux/Unix/Windows - Z for mainframes (IBM
z/OS). You can see all of these at this page -

http://www-01.ibm.com/software/data/db2/

As to large tables and taking a long time to process - that is a sure sign
that you don't have enough appropriate indexes.

Tools like Web Query, SEUEL, etc., are going to use SQL, so you will need
the right indexes to let SQL find the optimal path to the data.

HTH
Vern

On 4/17/2016 10:17 AM, Aljahmi, Jamal wrote:

Thanks Jim and Kathryn on your input. I'm no AS400 expert but we need to

provide better reporting until we migrate to a newer system.

One issue I run into as the conflicting information I get on what we have.

Some say it's DB2 other say it's a file base system in a DB2 environment.
Also, these files are large and in some cases 80M plus records, which take
a long time to process. So, in saying that I'm not sure if there is
limitation in using the DB2 web query or other tools.

Jamal

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
midrange-l-request@xxxxxxxxxxxx
Sent: Saturday, April 16, 2016 1:00 PM
To: midrange-l@xxxxxxxxxxxx
Subject: MIDRANGE-L Digest, Vol 15, Issue 636

Send MIDRANGE-L mailing list submissions to
midrange-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/midrange-l
or, via email, send a message with subject or body 'help' to
midrange-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
midrange-l-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific than

"Re: Contents of MIDRANGE-L digest..."

*** NOTE: When replying to this digest message, PLEASE remove all text

unrelated to your reply and change the subject line so it is meaningful.

Today's Topics:

1. Re: BI / Reporting tools (Jim It)
2. swapping network info for 2 iseries (tim)
3. Re: IFS "brick wall" limit on subdirectories (DrFranken)
4. Re: BI / Reporting tools (Kathryn Steinbrink)
5. Re: IFS "brick wall" limit on subdirectories (Vernon Hamberg)

----------------------------------------------------------------------

message: 1
date: Fri, 15 Apr 2016 23:32:28 +0000
from: Jim It <jim_it@xxxxxxxxxxx>
subject: Re: BI / Reporting tools

Jamal,

Two things.

1.) You didn't mention if your iSeries target is your production system or

not.

2.) Generally, iSeries applications are legacy applications and do not

have common database relations.

There is no such tool which exists for legacy (dds) based databases which

allows the user to easily make reports without understanding the underlying
query engine design.

The IBM WebQuery product could do it, with the templating done by an

experienced DB2 for i DBE, but $ per core makes cost too prohibitive for
some.

Jim

------------------------------

message: 2
date: Fri, 15 Apr 2016 19:37:04 -0400
from: tim <iseriesstuff@xxxxxxxxx>
subject: swapping network info for 2 iseries

We have two iseries (520,720). 520 is production, 720 is TEST.

I need to swap network information for these two boxes since the 720 will

become the production box.

They are defined as:

520
System Name: PROD
default location name: PROD
Local control point name: PROD
Network server domain: PROD
TCP Interface: 11.0.0.2 / 255.255.255.0

720
System Name: TEST
default location name: TEST
Local control point name: TEST
Network server domain: TEST
TCP Interface: 11.0.0.93 / 255.255.255.0

is CHGTCP and CHGNETA the only things i need to do, or is there more?

also, does it make a difference which system i do first and do i have to

reboot to have change take effect?

------------------------------

message: 3
date: Fri, 15 Apr 2016 22:27:02 -0400
from: DrFranken <midrange@xxxxxxxxxxxx>
subject: Re: IFS "brick wall" limit on subdirectories

32K?

Wow that seems miniscule. I have one customer today with 15M in one
directory and many more in the tree.

Even back in 3.1 we had customer Waaaaay past 32K. Perhaps you're
thinking of inferior tools such as mapped drives to Windows not being
able to open the folder?

We use them primarily from Apache servers and just don't see issues.

- Larry "DrFranken" Bolhuis

www.Frankeni.com
www.iDevCloud.com - Personal Development IBM i timeshare service.
www.iInTheCloud.com - Commercial IBM i Cloud Hosting.

On 4/15/2016 3:09 PM, Joe Pluta wrote:

*This message was transferred with a trial version of CommuniGate(r)
Pro* There may be no technical limit on the number of stream files in
a directory, but there is a very real practical limit. After 32K
files (I think - could be 16K) most things start to fail. STRQSH
especially begins to fall over; you can't list using ls, you can't
move them with mv, it's quite the mess. Whenever it turns out I'm
going to need to handle many thousands of stream files, I go to a
CLOB field in a database. Then I can extract to a temporary file
whenever I need traditional stream file access.

Joe

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzamp/rzamp
filesys.htm?lang=en

Maximum cumulative number of objects across the "root" (/),
QOpenSys, and user-defined file systems of ASPs 1 through 32
--> 2 147 483 647
Maximum number of directories in one *TYPE1 directory in the "root"
(/), QOpenSys, or user-defined file systems
--> 32 765
Maximum number of directories in one *TYPE2 directory in the "root"
(/), QOpenSys, or user-defined file systems
--> 999 998

Don't see any limit for stream files in a directory....files in a
(QDLS?) folder, yes...65,510.

Charles

On Fri, Apr 15, 2016 at 2:43 PM, James H. H. Lampert <
jamesl@xxxxxxxxxxxxxxxxx> wrote:

Some years ago, we found out (the hard way) that there is a "brick

wall"

in the number of subdirectories possible in an IFS directory. And
we had to rework an application to use ZIP files where it had been
using subdirectories.

Would anybody happen to know what that limit is? And whether
there's a similar "brick wall" limit on the number of files in an IFS

directory?

Does the IFS use *nix-style "inodes"? Or something else?

------------------------------

message: 4
date: Fri, 15 Apr 2016 22:13:11 -0500
from: Kathryn Steinbrink <krs@xxxxxxxxxx>
subject: Re: BI / Reporting tools

Your comments here are a bit misleading.

a) DDS defined files are still relational files. Its very possible
that you don't have things like referential integrity (relationships
and
constraints) defined in "legacy" databases, but that implies the tool
(or preferably in meta data) you build those relationships. If you do
it in Meta data, every report can leverage that without having to
understand the relationships at the report authoring level. That's the
beauty of a true Business Intelligence tool with a meta data layer
like DB2 Web Query. It is not a difficult process to set up join
definitions over your data - as you are probably having to do this in
every report today anyway (and if you do it in meta data, you do it ONCE,

not in every report).

b) Why do you say its too expensive? IBM has made at least 1 core
licenses (sometimes more than 1 core) of DB2 Web Query Express Edition
available to EVERY customer who owns Query/400 (which is just about
EVERY customer) AT NO CHARGE. Yes, this is a license with minimal
users, but all the web based report authoring capabilities, security
center, all the output types (PDF, Excel, Auto Drill down, etc.) are

included in that Express Edition.

I don't know where you're getting your information but I would
encourage Jamal to download the DB2 Web Query redbook from IBM's wiki
http://ibm.co/db2wqwiki to get a feel for the product.

Regards,
Kathy Steinbrink

------------------------------

message: 5
date: Sat, 16 Apr 2016 04:46:46 -0500
from: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
subject: Re: IFS "brick wall" limit on subdirectories

I am pretty sure that WRKLNK has limits to how many it can display -
around 32K, maybe. The message saying there are too many objects to
display doesn't tell us what the limit is.

I think the ls QShell function can show more.

There's the ls function in PASE, as well, and that should have the
limits of AIX, not IBM i.

Then DSPF against an IFS directory is different from opt 5 in WRKLNK -
I don't know what limits it has. We have a directory with over 70,000
files in it - DSPF against it seems not limited - it is a somewhat
funky interface but worth a look, just for fun!

Now some functions in DSPF can stall out with that many objects - F16
can sort it but spins a long time, etc.

I sometimes go to Windows Explorer to work with all this stuff. Sorry
to say, right?

Cheers
Vern

On 4/15/2016 9:27 PM, DrFranken wrote:

32K?

Wow that seems miniscule. I have one customer today with 15M in one
directory and many more in the tree.

Even back in 3.1 we had customer Waaaaay past 32K. Perhaps you're
thinking of inferior tools such as mapped drives to Windows not being
able to open the folder?

We use them primarily from Apache servers and just don't see issues.

- Larry "DrFranken" Bolhuis

www.Frankeni.com
www.iDevCloud.com - Personal Development IBM i timeshare service.
www.iInTheCloud.com - Commercial IBM i Cloud Hosting.

On 4/15/2016 3:09 PM, Joe Pluta wrote:

*This message was transferred with a trial version of CommuniGate(r)
Pro*
There may be no technical limit on the number of stream files in a
directory, but there is a very real practical limit. After 32K
files (I think - could be 16K) most things start to fail. STRQSH
especially begins to fall over; you can't list using ls, you can't
move them with mv, it's quite the mess. Whenever it turns out I'm
going to need to handle many thousands of stream files, I go to a
CLOB field in a database. Then I can extract to a temporary file
whenever I need traditional stream file access.

Joe

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzamp/rzam
pfilesys.htm?lang=en

Maximum cumulative number of objects across the "root" (/),
QOpenSys, and user-defined file systems of ASPs 1 through 32
--> 2 147 483 647
Maximum number of directories in one *TYPE1 directory in the "root"
(/),
QOpenSys, or user-defined file systems
--> 32 765
Maximum number of directories in one *TYPE2 directory in the "root"
(/),
QOpenSys, or user-defined file systems
--> 999 998

Don't see any limit for stream files in a directory....files in a
(QDLS?)
folder, yes...65,510.

Charles

On Fri, Apr 15, 2016 at 2:43 PM, James H. H. Lampert <
jamesl@xxxxxxxxxxxxxxxxx> wrote:

Some years ago, we found out (the hard way) that there is a "brick
wall"
in the number of subdirectories possible in an IFS directory. And
we had to rework an application to use ZIP files where it had been
using subdirectories.

Would anybody happen to know what that limit is? And whether
there's a similar "brick wall" limit on the number of files in an IFS

directory?

Does the IFS use *nix-style "inodes"? Or something else?

------------------------------

Subject: Digest Footer

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

Please contact support@xxxxxxxxxxxx for any subscription related questions.