How to execute SQL with paramter marker in Recommend index mode

[joydba]

sql = "SELECT id, breed, name, weight FROM animals WHERE id < ? AND weight > ?"

conn = ibm_db.connect(config.database, config.user, config.password)

if conn:
    stmt = ibm_db.prepare(conn, sql)

    if ibm_db.execute(stmt):  --- **throw's errors while executing it** 
        row = ibm_db.fetch_tuple(stmt)
        while ( row ):
            #row.each { |child| print child }
            for i in row:
                print(i)
            row = ibm_db.fetch_tuple(stmt)
    ibm_db.close(conn)
else:
    print("Connection failed.")

[jkyeung]

First of all, you should be using ibm_db_dbi, not ibm_db. Aside from that, your SQL statement looks OK to me, but I don't see where you're passing in values for the two parameter markers. The statement can't be executed successfully without them.

[joydba]

Here is my question !
why would you bind the variable when you are just trying to execute it explain mode as recommend index ?
To recommend index you dont need to bind the variable as you are not executing the actual query .

The query with ibm_db works when it is so ruling out you ibm_db_dbi approach for now .
"SELECT id, breed, name, weight FROM animals WHERE id < 3 AND weight > 10"

[jkyeung]

I don't understand what you mean by "explain mode" or "recommend index mode". I was not aware this package had anything like that. Even supposing it does, I don't see anything in your code that would indicate you are invoking such a mode, and I don't see anything in your code to receive the output it would give you. (In other words, even if it had some index to recommend to you, I don't see where that information is going.) There is nothing in your code to indicate you are trying to do anything other than execute the query. As such, having unbound variables is an error.

[joydba]

Apologies for the confusion .
I am just trying to give you an example of what i am trying .
So the code will be something like this .

I am also sharing the knowledge center link about this explain mode registry .

https://www.ibm.com/docs/en/ias?topic=registers-current-explain-mode

I can change the mode to any possible value during the flow of the code .
Let me know if you still have any doubts to understand my problem statement .

def changeMode(conn,mode):
    try:
      modechg=ibm_db.exec_immediate(conn,f"set current explain mode {mode}"
    except Exeption as e:
      error_message=  ("Error setting explain mode : {}").format(e)
    return modechg


conn = ibm_db.connect(config.database, config.user, config.password)
sql = "SELECT id, breed, name, weight FROM animals WHERE id < ? AND weight > ?"
    if conn:
        Ischnage=changeMode(conn,'recommend index')
        if Ischange:
            Print(f"Current explain mode is {Ischnage}")
        
        stmt = ibm_db.prepare(conn, sql)

        if ibm_db.exec_immediate(stmt):  --- tested both exec_immediate/excuete (no luck) 
            row = ibm_db.fetch_tuple(stmt)
            while ( row ):
                #row.each { |child| print child }
                for i in row:
                    print(i)
                row = ibm_db.fetch_tuple(stmt)
        ibm_db.close(conn)
    else:
        print("Connection failed.")

Error : CLI0100E wrong number of parameters SQLSTATE7001

[joydba]

This will eventually populate the ADVISE_INDEX table which is part of db2 explain utility .

[jkyeung]

Have you confirmed that everything works as you expect if you use other means, like ACS's Run SQL Scripts or an SQLRPGLE program?

[joydba]

yes , incase i change the sql where i pass actual values it works as expected .

"SELECT id, breed, name, weight FROM animals WHERE id < 3 AND weight > 10"

[jkyeung]

No, I mean with the parameter markers. If you are saying you can't do something in Python with ibm_db, then first confirm that you can do the very same thing using other languages or tools.

[joydba]

Yes this is working with perl DBI .

[kadler]

You need to pass the parameters on ibm_db.exec.

if conn:
    stmt = ibm_db.prepare(conn, sql)

    params = (100000, 1) # id, weight

    if ibm_db.execute(stmt, params):
        pass

[kadler]

I don't know anything about "recommended index mode" and it seems that your link points to IBM Integrated Analytics System, not IBM i. I have no idea what that product is and it's not compatible with this interface. Perhaps you meant to use https://github.com/ibmdb/python-ibmdb instead? This is a fork of that which is specific to the IBM i database.

[jkyeung]

But apparently they were able to use Perl DBI successfully. So there may be some difference in the primitive methods. It looks to me that Perl DBI is a little bit lower level and more granular, particularly compared to the API defined by Python's PEP 249.

I'm confident that in principle, the exact functionality of Perl DBI can be ported to Python, if not already available. But if what's working in Perl DBI isn't currently in ibm_db, I'm guessing it would be quite a project to add it.

[kadler]

This project is basically just in maintenance mode as we recommend everyone use PyODBC nowadays instead, so if there's changes that need to be made to make this use case work (which I still don't know if is even applicable here) then someone would have to step up to make those changes.

And personally, I'd rather gut all the ibm_db stuff and just rewrite this to be only PEP-249 compatible. I don't know why they thought shoving the PHP ibm_db2 interface in to Python made sense, maybe it did make sense at the time. But today I don't know why anyone would voluntarily choose to use the ibm_db interface, other than all the documentation for the project pushes you that way and basically neglects to mention PEP-249 whatsoever.

[jkyeung]

I agree that pyodbc is normally the way to go, so @joydba - that's another thing to try. I suspect the results will be the same as ibm_db, but I would be happy to be wrong.

From a purely practical standpoint, is using Perl an option?