On Fri, May 21, 2021 at 12:32 PM Jim Oberholtzer
<midrangel@xxxxxxxxxxxxxxxxx> wrote:
Is it a limitation of ODBC, or SQL. SQL ( keep in mind my SQL skills are limited) sees members as partitions of the table, does it not?
It's very clearly not a limitation of SQL. I already gave the SQL that
I used, and I already said it works in STRSQL.
Yes, members are partitions, but that's no more consequential than
"physical files" being called "tables" instead.
Looking for files with attribute ‘pf-src’ will find all the source files. Easily done in CL. Now you can use simple CL commands to get the member count.
Sounds more Like a CL problem than SQL to solve.
Well, you admitted your SQL skills are limited. If you were equally
comfortable with both, I would bet you find it easier with SQL.
Not only is the SQL shorter, in the lines-of-code sense, but it's also
easier to use from a client that's external to the IBM i (such as a
PC), if that's something you happen to want or need.
But, just for future reference, which "simple CL commands" get the member count?
As of 2012, the best Bob Cozzi could come up with was wrapping the
QDBRTVFD system API:
http://www.midrangenews.com/view?id=1876
Other options mentioned included DSPFD to an output file and the
QUSLMBR system API.
You are not going to convince me that any of those options are simpler
than the short SQL query I presented at the beginning of this thread.
But maybe there is something new available to CL by now.
Incidentally, for those following along who are more expert at the IBM
catalog views and services than I am (which is easy to be!):
I gravitated toward SYSPARTITIONSTAT because I've used that several
times in the past, but I have not used SYSTABLESTAT, which allows the
equivalent
select number_partitions from qsys2.systablestat
where table_schema = ? and table_name = ?
It really is equivalent to my earlier query: It returns 0 over ODBC
for the same tables that returned 0 for my earlier query, but works
correctly in STRSQL for all the tables I have tried it on.
John Y.
midrange.com
