Add support for SqlFetchScroll()/SQLExtendFetch() to query large rowsets

[davvitt]

Is your feature request related to a problem? Please describe.
When running ibm_db on z/OS and querying a large set of rows (in my case >25k rows) via a three-part-name SELECT, the query time is very bad (approx. 25 seconds).

Describe the solution you'd like
Based on suggestions by the IBM DB2 support team, ibm_db need support to retrieve result set into an array, which needs support SQLFetchScroll() with SQL_ATTR_ROW_ARRAY_SIZE or SQLExtendFetch() with SQL_SQL_ATTR_ROWSET_SIZE for fetchmany/fetchall functions.

[Earammak]

@davvitt Can you please share the sample example here to look into the issue on how it's impacting interms of performance.

Thanks

[davvitt]

Hi @Earammak,

here is a sample script. I hope that is what you are looking for :)

import os
from pathlib import Path

import ibm_db
import zos_util


def db2_connect(ssid: str) -> ibm_db.IBM_DBConnection:
    odbc_ini = Path(f"~/.odbc/odbc_{ssid}.ini").expanduser()
    odbc_ini.parent.mkdir(parents=True, exist_ok=True)
    odbc_ini.touch(exist_ok=True)
    zos_util.tag_binary(odbc_ini.as_posix())

    odbc_parms = [
        "[COMMON]",
        f"MVSDEFAULTSSID={ssid}",
        "CONNECTTYPE=1",
        "MULTICONTEXT=0",
        "CURRENTAPPENSCH=ASCII",
        "FLOAT=IEEE",
    ]
    with odbc_ini.open(mode="wb") as f:
        f.write("\n".join(odbc_parms).encode("cp1047"))
    os.environ["DSNAOINI"] = str(odbc_ini)

    return ibm_db.pconnect("", "", "")


def main() -> None:
    local_subsys = "DB2Q"
    remote_subsys = "DB2U"
    table = "myschema.mytable"
    select_stmt = f"select * from {remote_subsys}.{table}"

    con = db2_connect(local_subsys)
    stmt = ibm_db.prepare(con, select_stmt)
    ibm_db.execute(stmt)
    results = ibm_db.fetchall(stmt)
    print("no. of rows", len(results))
    ibm_db.close(con)


if __name__ == "__main__":
    main()

[bimalkjha]

Internal Jira Epic link: https://jira.rocketsoftware.com/browse/DBC-17268