For example, can it view the query string and send all such queries to the same server? This will allow the queries to hit the server that already has the cache for the query .If so, how wil the setup be....Please advice.
If an iRule can fix this request, could someone please share as to how would that irule syntax look like.
JDBC -- Java Database connectivity.JDBC is a set of API's that offer a database-independent means of extracting and/or inserting data to or from a database.To perform these insertions and extractions,SQL code also resides
Theoretically yes, F5 can load balance based on a SQL query provided you can write an iRule which can identify that query in the TCP payload.
You would then hash the SQL query and persist on that hash.
Bear in mind that some databases use encrypted communication so you won't be able to see the SQL query in the intercepted TCP stream for databases which use encryption (MS SQL Server 2005 for example). If your database connection is unencrypted (for example mySQL is plain text by default) then you should be able to write in iRule for SQL-query-based persistence.
I doubt you will be able to find a universal iRule which does the job for you, but if you have the skills you can program one yourself for your database based upon the database protocol (*not JDBC* which is an API, not a protocol) , but MS SQL, MySQL, Oracle etc...
A sample iRule on how to persist on a string found inside a TCP stream would be the one for FIX protocol persistence: