Jamal, per your comment here:
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.
Your database is DB2. Your USAGE of the database might be what we call
traditional or legacy usage - DDS defined Physical files and Logical files
(DDS is Data Description Specifications). Why is this there? Because the
relational database was commercially available before SQL was invented.
Over time, SQL was added, and in fact, has been the STRATEGIC database
interface for years now. But there is a ton of applications out there still
using DDS defined files.
The beauty of adding the SQL though, was that you can interchange usage. A
DDS Physical file is synonymous with an SQL Table. A Logical file is
synonymous with an SQL Index or SQL View. You can write an RPG program
accessing the database through native RPG commands or SQL, and it doesn't
matter how the table/file was created (DDS or SQL DDL).
So, you have DB2 I'm sure. Your application's USAGE of it....can't say for
sure. But it would be unlikely (but a possibility) that you have true
"flat" files where there are no columns/fields defined. Its most likely you
have DDS defined files with field/column descriptions. If you have System i
Navigator you can get a good view of what you have under the database
perspective.
You may want to look at this database modernization white paper at
https://ibm.biz/Bd4fFb (click on database modernization and then you'll
see the DDS to SQL white paper).
Doug Mack
Analytics Consultant, DB2 for i
IBM Systems Lab Services
Mobile: 360-481-1271
email: mackd@xxxxxxxxxx
twitter: @mckdrmoly
From: Kathryn Steinbrink/Rochester/IBM
To: Doug Mack/Seattle/IBM@IBMUS
Cc: Robert Bestgen/Rochester/IBM@IBMUS
Date: 04/18/2016 06:42 AM
Subject: Fw: BI / Reporting tools
fyi.
Regards,
Kathy Steinbrink
Dept ONB - DB2 Web Query for i
IBM Rochester: 015-3 C115
507-253-5545
----- Forwarded by Kathryn Steinbrink/Rochester/IBM on 04/18/2016 08:40 AM
-----
From: Rob Berendt <rob@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 04/18/2016 06:50 AM
Subject: Re: BI / Reporting tools
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
<snip>
Some say it's DB2 other say it's a file base system in a DB2 environment
</snip>
I'd like to know why they say this. I say the argument ranges from "they
might have a point" to "Some <expletive deleted> who will always prod IBM
to add some feature that he's heard about on some other database"
The "they might have a point" argument starts out with:
Someone running multiformat physical files that are only internally
defined and have no external definition, or, if any, a very limited one
with maybe two fields: Record type and Data.
Then some say that any file created with DDS instead of DDL doesn't count.
I much prefer DDL but I don't follow this argument.
Some say that any system without referential constraints showing the
relations between the different files doesn't count. This is a big help,
especially with many of the reporting tools. This can be done with DDS
accompanied with CL commands or DDL.
In that same vein, having decent views which also show the relationship
between files. Is a step in the right direction. Good views can also
rename many of the columns thrust upon us by vendor software and do some
of the simple calculations (like our vendor package which has no inventory
on hand column).
Some BI tools just require you rebuild everything into their "metadata".
Even if they don't copy the data you still have to redefine everything
into their metadata.
See also
http://dilbert.com/strip/1995-11-09
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "Aljahmi, Jamal" <Jamal.Aljahmi@xxxxxxxxxxxxxxxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 04/17/2016 11:18 AM
Subject: Re: BI / Reporting tools
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
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/rzampfilesys.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/rzampfilesys.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) digest 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.
------------------------------
End of MIDRANGE-L Digest, Vol 15, Issue 636
*******************************************
------------------------------------------------------------------------
This message and any attachments originated from a computer at the 36th
District Court in the city of Detroit and are intended only for the use of
the addressee(s) and may contain information that is privileged and
confidential. If the reader of the message is not the intended recipient
or an authorized representative of the intended recipient, you are hereby
notified that any dissemination of this communication is strictly
prohibited. If you received this communication in error, notify the
sender
immediately by return email and delete the message and any attachments
from
your system. Furthermore every effort has been taken to ensure protection
against virus infection; we cannot accept any responsibility for viruses.
Consequently, please ensure that all attachments are virus checked prior
to
opening.
------------------------------------------------------------------------
--
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.
--
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.
midrange.com
