Re: [IBMiOSS] Queries over ODBC not always the same as in STRSQL?

I was trying to get the number of members in various source physical files.
So I whipped up a little Python script containing the following SQL:
select count(*) from qsys2.syspartitionstat
where table_schema = ? and table_name = ?

If you really want to use a Python script, then maybe your Python script could use SQL to call an IBMi SQL stored procedure over an ODBC connection. The stored procedure would contain the query you want to run.

The code below has not been tested and probably needs tweaking before it works. Treat it like pseudocode. The SQL stored procedure would look something like this:
CREATE OR REPLACE PROCEDURE MYSQLSP
(IN TABLE_SCHEMA_PARM CHAR (10),
IN TABLE_NAME_PARM CHAR (10))
RESULT SETS 1
LANGUAGE SQL
SET OPTION DBGVIEW = *SOURCE
BEGIN
DECLARE C1_GET_COUNT CURSOR WITH RETURN FOR
SELECT COUNT(*) FROM qsys2.syspartitionstat
WHERE table_schema = TABLE_SCHEMA_PARM
AND table_name = TABLE_NAME_PARM;
OPEN C1_GET_COUNT;
RETURN;
END;

The SQL statement to call the stored procedure would look something like this:
CALL MYLIB.MYSQLSP('MySchema','MyTable');
MYLIB is the library where the stored procedure resides. You may need to have MYLIB added as a schema in your ODBC connection. Here's a good Red Book to learn more about stored procedures if you have not used them before: https://www.redbooks.ibm.com/redbooks/pdfs/sg248326.pdf.

Your Python program will need to process the rowset returned by the SQL cursor (one row containing a count result).

Thanks,

Kelly Cookson
Senior Software Engineer II
Dot Foods, Inc.
1-217-773-4486 ext. 12676
www.dotfoods.com

On May 21, 2021, at 10:16 AM, John Yeung <gallium.arsenide@xxxxxxxxx> wrote:

I was trying to get the number of members in various source physical files.

So I whipped up a little Python script containing the following SQL:

select count(*) from qsys2.syspartitionstat
where table_schema = ? and table_name = ?


If there is any pattern to it, it seems the files with a lot of
members (say, over a couple thousand) come back as empty over ODBC.
Files with a smaller number of members work fine with both ODBC and
STRSQL.

Has anyone else noticed this? Is it a limitation of the ODBC driver?

John Y.
--
This is the IBMi Open Source Roundtable (OpenSource) mailing list To
post a message email: OpenSource@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinf
o/opensource__;!!BZnD2a2Aqg!QKCRG5dOMIWR9JfZsgKfdzcEI3CI4xERDYCKJ4yGRq
S8lTy12kUpVOFdYEc7GUxc-Q$ or email:
OpenSource-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.com/v3/__https://archive.midrange.com/opensource__;!!BZnD2a2Aqg!QKCRG5dOMIWR9JfZsgKfdzcEI3CI4xERDYCKJ4yGRqS8lTy12kUpVOFdYEe5znHpqQ$ .

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.com/v3/__https://amazon.midrange.com__;!!BZnD2a2Aqg
!QKCRG5dOMIWR9JfZsgKfdzcEI3CI4xERDYCKJ4yGRqS8lTy12kUpVOFdYEdTfUEhcg$

--
This is the IBMi Open Source Roundtable (OpenSource) mailing list To post a message email: OpenSource@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/opensource__;!!BZnD2a2Aqg!QKCRG5dOMIWR9JfZsgKfdzcEI3CI4xERDYCKJ4yGRqS8lTy12kUpVOFdYEc7GUxc-Q$
or email: OpenSource-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.com/v3/__https://archive.midrange.com/opensource__;!!BZnD2a2Aqg!QKCRG5dOMIWR9JfZsgKfdzcEI3CI4xERDYCKJ4yGRqS8lTy12kUpVOFdYEe5znHpqQ$ .

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://urldefense.com/v3/__https://amazon.midrange.com__;!!BZnD2a2Aqg!QKCRG5dOMIWR9JfZsgKfdzcEI3CI4xERDYCKJ4yGRqS8lTy12kUpVOFdYEdTfUEhcg$